您的位置: 首页  >  文章  >  面试总结-数据库编

面试总结-数据库编

分类: 文章 • 2024-03-05 11:42:54

  1. 基础相关
    关键字：left join ，right join ，inner join , in , not in, exits , not exits

  2. sql优化

  3. 索引（explain）

  4. 事务的的特性

  5. limit的优化

Sql优化

关于日常工作中，会让索引失效的操作有：

  1. 在where条件有索引的字段使用 or 。
  2. 使用in
  3. like

等等，关于让索引失效的操作，其实直接使用explain去看就行了。

索引

关于索引的理解，建立所以后查询的会比较快，不会走全表查询，在mysql中常用的算法有两种，Btree，hash两种算法，当建立索引之后。比如假如使用的是Btree的算法，那么当查询的时候，在where条件上通过，建立的索引作为查询条件的时候，内部就会通过Btree算法不会走全表，从而达到查询快的效果。

提到索引必须要提的 explain 关键字

explain  sql 语句
例如：explain select * from user where name='';

下面解释一下具体的含义
面试总结-数据库编
explain解释详情参考地址
id：当出现多个查询的时候对应的查询的id
select_type:

  • SIMPLE： 简单的sql查询
  • PRIMARY： 最外层的select
  • UNION： UNION中的第二个或后面的SELECT语句
  • DEPENDENT： UNION中的第二个或后面的SELECT语句，取决于外面的查询
  • UNION RESULT： UNION的结果。
  • SUBQUERY： 子查询中的第一个SELECT.
  • DEPENDENT SUBQUERY： 子查询中的第一个SELECT，取决于外面的查询
  • DERIVED：派生表的SELECT(FROM子句的子查询)

table:显示这一行的数据是关于哪张表的.
有时不是真实的表名字,看到的是derivedx(x是个数字,我的理解是第几步执行的结果)
partitions:（分区的意思）
type:这列很重要,显示了连接使用了哪种类别,有无使用索引.
从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL
possible_keys: possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意，该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询
key:key列显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
key_len:key_len列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。
使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref:ref列显示使用哪个列或常数与key一起从表中选择行。
rows:rows列显示MySQL认为它执行查询时必须检查的行数。
extra:该列包含MySQL解决查询的详细信息,下面详细.

事务的的特性

  • A(原子性)：一个事务必须视为一个不可分割的最小单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行成功其中的一部分操作，这就是事务的原子性。
    总结：就是平常业务中常理解的一个业务逻辑应该控制在一个业务中
  • C(一致性):　一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。
    　　拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。
    总结:从物理学角度讲，总能量守恒定律
  • I(隔离性)
    隔离级别从高到低有：
    可串行化
    可重复读
    读已提交
    读未提交

下面从低到高来解释一下这四个隔离级别：

读未提交（read uncommitted）：这种隔离级别会出现脏读的情况，比如，两个事物A，事物B，当事物A进行新增的时候，但是还没有commit提交事物，这个时候事物B来查询，就会查询到事物A还没有提交的数据，从而出现脏读。

测试：首先把mysql设置成不是自动提交
（记住分别设置两个窗口的）

-- 查看提交方式
show variables like '%autocommit%';
-- 设置不是自动提交
set @@autocommit=0 ;

设置隔离级别为读未提交（记住分别设置两个窗口的）

-- 查看当前事物隔离级别
 select @@tx_isolation;
 -- 设置事物隔离级别为read uncommitted
  set session transaction isolation level read uncommitted;

先查看下表中的初始数据
面试总结-数据库编
分别开启两个窗口A，B，用来表示两个请求。然后再窗口A更新记录。

update employees set last_name='zhang' where last_name='qiang';

这时在窗口B查询该表中的数据，就会查询到last_name 变成了zhang，注意此时A还没有提交事物，如图
面试总结-数据库编
此时，就出现了脏读。

读已提交(read committed)：假如设置成这个隔离级别的话。这种隔离级别会出现不可重复读的情况；对于同一张表中的数据，只有当事物A提交事物后，事物B才可以读取。
接下来测试下这种情况:
分别设置两个窗口的隔离级别为read committed

 set session transaction isolation level read committed;

先来查看一下此时窗口A现在的数据情况
面试总结-数据库编
把窗口A的zhang再设置成qaing

update employees set last_name='qiang' where last_name='zhang';

此时查看窗口B该字段的值还是zhang，注意此时窗口A还没有提交事物
面试总结-数据库编
到这可以看出，当设置隔离级别为read committed的时候，只有当A提交完事物之后，B才能查看到。接下来A提交事物，B再查看就会查询到为last_name 变为了 qiang 。但是会造成不可重复读的情况（事物B再读取同一个数据时，得到不同的结果，在事物A提交数据前后),可不重复读是oracle的默认隔离级别。

可重复读（repeatable read）：
分别修改两个窗口的隔离级别为可重复读

 set session transaction isolation level repeatable read;

接下来为了更能直观的看这个流程，就不截图了。
过程： 在窗口B中查看表中数据，此时数据为qiang，那么此时在窗口A中把该数据修改成zhang，并提交事物，此时再在B窗口查看数据，数据还是qiang，只有当窗口B的查询操作提交事物之后才会查询到zhang这个数据。这个就是可重复读。但是同时也会出现幻读的情况。Mysql的默认隔离级别是repeatable read 。
幻读：正是由于可重复读的情况，当窗口A插入一条数据为id=2 ,当窗口A提交事物后，再窗口B查询不会查询到id=2的这条数据，那么此时再窗口B同样也插入一条id=2的数据，就会产生主键冲突的错误，这个就是幻读。

可串行化（serializable）
分别设置A，B两个窗口的隔离级别

set session transaction isolation level serializable;

可解决幻读的情况，上方的幻读情况，当再次插入id=3的数据时，会锁表。

D(持久性)：持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

相关推荐