数据库常见面试题（二）——索引额外问题和锁模块

客官°小女子只卖身不卖艺 2023-01-19 11:48 82阅读 0赞

### 数据库常见面试题（二）——索引额外问题和锁模块 ###

*   *  1. 索引额外问题
     *   *  问题1.1 索引额外问题——如何定位并优化慢查询sql
         *  问题1.2 索引额外问题——联合索引的最左匹配原则的成因
         *  问题1.3 索引额外问题——索引是键的越多越好吗？
     *  2. 锁模块
     *   *  问题2.1 MyISAM与InnoDB关于锁方面的区别是什么？
         *  问题2.2 数据库事务的四大特性
         *  问题2.3 事务隔离级别以及各级别下的并发访问问题
         *  问题2.4 InnoDB可重复读隔离级别下如何避免幻读
         *  问题2.5 RC、RR级别下的InnoDB的非阻塞读如何实现

## 1. 索引额外问题 ##

### 问题1.1 索引额外问题——如何定位并优化慢查询sql ###

**1. 根据慢日志定位慢查询SQL**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70]注意慢日志相关信息long\_query\_time、slow\_query\_log、slow\_query\_log\_file，若slow\_query\_log 显示off，则set globle slow\_query\_log=on; 可将long\_query\_time修改为1s，具体看自己的需求。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 1]  
当前慢查询数量为0。

这里没有实践慢查询的语句（数据量不够），只讲一讲原理。

**2. 使用explain等工具分析SQL**  
假设我们有一条慢查询语句, 在前面加一个explain 可以得到语句的执行情况。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 2]主要关注：type和extra。

*  type：system>const>eq\_ref>ref>fulltext>ref\_or\_null>index\_merge>unique\_subquery>index\_subquery>range>**index>all**， 当type为index或all时，则尽可能优化。
 *  extra：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 3]

1.  **（优化）** 修改sql或尽量让sql走索引  
    ①比如查name，时间太长，可改为有唯一键的accout？  
    ②给name加索引， alter table person\_info\_lage add index idx\_name(name);  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 4]  
    （**不实用，只是试一下调优**）

### 问题1.2 索引额外问题——联合索引的最左匹配原则的成因 ###

首先要知道联合索引和最左匹配是什么吧

*  联合索引：对多个字段同时建立的索引(有顺序，ABC，ACB是完全不同的两种联合索引。)
 *  最左匹配原则：假设我们有两列a,b，**a和b是联合索引（a,b）**，他的顺序是a,b，我们在where语句中调用a=? and b=?的时候就会走联合索引，如果调用where a = ?的时候也会走索引，但是当我们使用where b = ?的时候就不会走这个联合索引

![img][]

成因：

mysql创建复合索引的规则是首先会对复合索引的最左边，也就是索引中的第一个字段进行排序，在第一个字段排序的基础上，在对索引上第二个字段进行排序，其实就像是实现类似order by 字段1，字段2这样的排序规则，那么第一个字段是绝对有序的，而第二个字段就是无序的了，因此一般情况下直接只用第二个字段判断是用不到索引的，这就是为什么mysql要强调联合索引最左匹配原则的原因。

### 问题1.3 索引额外问题——索引是键的越多越好吗？ ###

答案是否定的

*  数据量小的表不需要建立索引，建立会增加额外的索引开销
 *  数据变更需要维护索引，意味着更多的索引意味着更多的维护成本
 *  更多的索引也需要跟多的存储空间

## 2. 锁模块 ##

### 问题2.1 MyISAM与InnoDB关于锁方面的区别是什么？ ###

*  MyISAM默认的用的是表级锁，不支持行级锁。
 *  MyISAM 的读锁是共享锁，写锁是排它锁。对表A而言，进程1给表A加了共享锁，进程2只能对表A加共享锁；若进程1加了排它锁，那进程2只能等待进程1解锁后才能查询或加锁。
 *  InnoDB默认的是行级锁，也支持表级锁。
 *  InnoDB采用的是**二段锁，即加锁和解锁**（commit，数据库默认打开自动提交）；但是在加共享锁时，若没改变某行数据，那另一进程亦可修改该行记录。InnoDB的SQL没用到索引时，用的是表级锁；用到时则是行级锁。  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 5]

**小知识环节**

*  行锁：对表操作时对操作行上锁。
 *  表锁：对表操作是锁上整个表的数据。
 *  共享锁：对于多个不同的事务，对同一个资源共享同一个锁。但是对于insert ，update，delete事务则会自动加上排它锁。在执行语句后面加上lock in share mode就代表对某些资源加上共享锁。 eg: 可同时执行多个select语句。
 *  排它锁：对于多个不同的事务，对同一个资源只能有一把锁。只需在执行语句后+for update 即可。eg：只能一个个进行操作。
 *  悲观锁：在操作数据的时候，认为此操作会发生数据冲突，所以在进行每次操作时都要通过获取锁才能进行相同数据的操作。与Java的synchronized很相似，所以悲观锁需要耗费较多的时间。
 *  乐观锁：操作数据库时，认为此操作不会导致数据冲突，在操作数据时，不进行加锁处理，在进行更新后，再去判断是否有冲突。例如hibernate中的乐观锁两种实现，就是分别基于version和timestamp来实现的。eg：  
    update user set name = ‘John’ ,version=version+1 where id =13 and version = \#\{version\};  
    **注：update语句完成后需要commit操作的，相当于一个update语句就是一个原子操作，所以不会出现两条update能同时更新的情况**
    
    **锁的分类**
 *  锁的粒度：表级锁，行级锁，页级锁；
 *  锁级别：共享锁，排它锁；
 *  加锁方式：自动锁（InnoDB自动加锁）、显示锁(人为在SQL后加的锁)；
 *  操作划分：DML锁（数据修改涉及的锁）、DDL锁（表结构修改涉及的锁）；
 *  使用方式：乐观锁、悲观锁。

MyISAM适用场景

*  频繁执行全表count语句。（MyISAM有一个记录行的变量）
 *  对数据进行增删改的频率不高，查询非常频繁。
 *  没有事务

InnoDB适用场景

*  数据库增删改查都相当频繁。
 *  可靠性要求比较高，要求支持事务。

### 问题2.2 数据库事务的四大特性 ###

ACID

*  A （Atomic）原子性：一个事务要么完全执行完，要么不执行。
 *  C （Consistency）一致性：满足完整性约束，例如转账，A、B在互相转账的过程中，总额应该保持一致。
 *  I （Isolation）隔离性：多个事务并发执行时，一个事务不会影响另一个事务的进行。
 *  D （Durability）持久性：一个事务一旦提交，它数据库的修改应该永久保存在数据库中，持久性主要用于DBMS的恢复。

innodb会对页面的修改写入一个专门文件，并在数据库启动时从此文件进行恢复操作。（redo\_log文件）

### 问题2.3 事务隔离级别以及各级别下的并发访问问题 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 6]  
先说明各个事务隔离级别：

*  更新丢失：mysql所有事务隔离级别在数据库层面上都可避免。
 *  脏读：一个事务读到另一个事务未提交的更新数据。

解决方法：将事务隔离级别改为read commited。（已提交度，只读已提交的数据）

*  可重复读：事务A多次读取同一数据，事务B对数据做了更新、提交，导致事务A多次读取同一数据不一致。（意思是重复读的数据不一致）

解决方法：将事务隔离级别改为repeatable read。

*  幻读：事务A读取与搜索条件相匹配的若干行，事务B以插入或删除行等方式修改事务A的结果集，导致事务A看起来像产生幻觉一样。

解决方法：将事务隔离级别改为serializable(串行化，最高级别）。

小知识：Oracle 默认read commited， mysql默认repeated read

### 问题2.4 InnoDB可重复读隔离级别下如何避免幻读 ###

*  表象：快照读（非阻塞读）——伪MVCC。在Repeatable-read隔离级别下，如果先在别的事务操作数据之前，创建了快照读，不管别的事务怎么操作数据，当前事务再次使用快照读时，查到的依旧是老版本的数据。
 *  内在：next-key锁（行锁+gap锁）。真正防止幻读发生是因为事务在Repeatable-read隔离级别以上（包括）对数据加了next-key锁。next-key锁由行锁和gap锁组成。

小知识：

1.  快照读和当前读

*  当前读：加了锁的增删改查语句 select… lock in share mode, select … for update, insert, delete, update.
 *  快照读：（事务隔离级别不为serializable下）select
 *  在RC级别下，当前读和快照读是同一版本的数据。
 *  在RR级别下，如果在进行增、删、改完成之后，再去查询快照读，则此时读取到的是最新版本的数据。如果是在增、删、改之前进行了快照读，在增、删、改之后继续快照读，则读到的就是旧版本数据。
    
     *  总结：**快照读取决于一开始快照读的时间。**

1.  next-key锁分为了（行锁 + gap锁）

*  行锁

行锁就是Record Lock，就是对单个行记录加的锁。X锁和S锁就是行锁。

*  Gap锁

Gap就是索引树中插入新数据的间隙。间隙锁即锁定一个记录的范围，但是不锁定记录本身。间隙锁是为了避免同一事务的两次当前读出现幻读的情况。需要注意的是，Gap锁在RU、RC隔离级别下时不存在的，在RR、Serializable隔离级别下都只支持Gap锁。这就是为什么RU、RC隔离级别下无法避免幻读，RR、Serializable能够避免幻读的原因。

**下面讨论的都是在RR隔离级别下出现Gap锁的场景。**

*  在RR隔离级别下，无论删、改、查，当前读若用到主键索引或者唯一键索引，会使用Gap锁吗？
    
    答：**如果where条件全部命中**，则不会用Gap锁，**只会加记录锁。**（记录锁的功能是：当一个进程正在读或者修改文件的某个部分时，它可以阻止其他进程修改同一文件区。）

怎么去理解where条件全部命中，不用加Gap锁只需要加记录锁就行了呢？

在RR隔离级别下，无论删、改、查，当前读若用到主键索引或者唯一键索引，且如果where条件部分命中或者全不命中，则会加Gap锁。对于这种情况，就包含了范围查询以及精确查询非全部命中的情况。

例子1：比如现在事务A要删除一条不存在的id为7的记录，此时事务B要新增一条id为8的记录，会发现事务B一直处于等待中，这是因为精准查询全部都不命中，会对该记录范围加Gap锁。

例子2：【tb\_student中存在id为5,6,9的学生】比如在事务A中使用语句select \* from tb\_student where id in (5,7,9) lock in share mode;使用当前读（共享锁）来查询学生信息。在另外一个事务B中去进行新增id为6,7,8的学生，发现事务一直在等待中。这里是因为where id in (5,7,9)部分命中，所以会为(5,9\]加Gap锁，锁的范围为左开右闭。因此事务B新增id为7,8的记录会被Gap锁锁住，这就是精准查询不全部命中的情况。

**Gap锁会用在非唯一索引或者不走索引的当前读中**

*  **非唯一索引**  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 7]  
    比如图中某一事务A执行delete from tb1 where id = 9，因为id是非唯一索引，如果没有加Gap锁，在事务B新增一条id为9的记录时，A事务执行完delete语句后，就会发现成功删除3条记录，出现了幻觉，所以给id为9的记录加上Gap锁来防止幻读的发生。  
    至于Gap锁的范围，如上为：（-∞,2\], (2, 6\], (6, 9\], (9, 11\], (11, 15\], (15, +∞）中的 (6, 9\], (9, 11\]
 *  **不走索引**  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 8]

对于不走索引的情况，InnoDB会为所有的Gap加锁，相当于锁表。

### 问题2.5 RC、RR级别下的InnoDB的非阻塞读如何实现 ###

*  底层实现离不开数据行里的**DB\_TRX\_ID、DB\_ROLL\_PTR、DB\_ROW\_ID**字段，除此之外还需要**undo日志**，以及**read view。**
 *  原理实现就是下列几个关键内容：
    
     *  数据行里的DB\_TRX\_ID、DB\_ROLL\_PTR、DB\_ROW\_ID
     *  undo日志
     *  read view机制
 *  说起DB\_TRX\_ID、DB\_ROLL\_PTR、DB\_ROW\_ID，那就要先知道**MySQL一条记录是由记录的额外信息部分和记录的真实数据两部分组成。记录的额外记录部分存有变长字段长度列表、NULL值列表**等，而记录的**真实数据部分又由真实数据以及DB\_TRX\_ID、DB\_ROLL\_PTR、DB\_ROW\_ID**这三个隐藏列组成。
 *  ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NvZGVyQnJ1aXM_size_16_color_FFFFFF_t_70]
 *  ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NvZGVyQnJ1aXM_size_16_color_FFFFFF_t_70 1]
 *  比如现在有一个记录Field1、Field2、Field3数据分别为11、12、13，现在事务要修改该记录，将Field2修改为32。则这条记录首先会加载X锁，首先undo log中会拷贝一条修改前的记录，并赋值DB\_ROW\_ID。此时被X锁锁住的记录的DB\_TRX\_ID、DB\_ROLL\_PTR、DB\_ROW\_ID分别进行赋值，并且DB\_ROLL\_PTR的记录会指向undo log中的DB\_ROW\_ID的值。
 *  如果此时又有一个事务对该记录进行了修改，则undo log日志中又会增加一条日志。  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NvZGVyQnJ1aXM_size_16_color_FFFFFF_t_70 2]
    
     *  read view 进行可见性判断，当我们执行快照读，会创建一个read view 决定当前事务能看到哪个版本的数据。
    
    **这样就是快照读版本的实现了。**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70]: /images/20221021/c5d2f2959c2c408bac0a6aae29232799.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 1]: /images/20221021/2e5689a00e674e9082b8b9e2e05a7fc8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 2]: /images/20221021/82caa5c5888f4e94af8e72eb500665fd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 3]: /images/20221021/158ecf27cc9244af9ad75b27d0461730.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 4]: /images/20221021/a44649f5502347dbb41833547152c9a5.png
[img]: /images/20221021/74bbb2e57be145c6ac232bbc382b5a96.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 5]: /images/20221021/3d4d5e4f7ccc40e78d4511d0643b246d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 6]: /images/20221021/435a59d4e83142f0a614b97f3de34595.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 7]: /images/20221021/a9182e0dea7545a4918268e8ff549d57.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 8]: /images/20221021/4952effad8fe43b988f84a88dca23fb2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NvZGVyQnJ1aXM_size_16_color_FFFFFF_t_70]: /images/20221021/7c88b5f6abb04006965f6ebafd7daf83.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NvZGVyQnJ1aXM_size_16_color_FFFFFF_t_70 1]: /images/20221021/42c202570454496cbf89a1159d4146f1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NvZGVyQnJ1aXM_size_16_color_FFFFFF_t_70 2]: /images/20221021/c8a99328c5014811ac41de71f65258ab.png