MySQL-面试题

逻辑架构

1 请你说一下InnoDB和MyISAM存储引擎？

• MySQL5.5之后，默认采用InnoDB存储引擎。InnoDB具备外键、事务、行锁的功能。对比MyISAM的存储引擎，InnoDB写的效率会差一些，并且会占用更多的磁盘空间去保存索引和数据。MyISAM只缓存索引不缓存真实的数据,InnoDB不仅要缓存索引还要缓存真实的数据，对内存要求比较高。
• MyISAM是5.5之前默认的存储引擎，不支持事务、行锁、外键，有一个毫无疑问的缺点就是在崩溃后无法安全恢复，应用场景是适合以读为主的业务。

2 一条SQL语句是如何执行的？

①. 连接器：连接器负责跟客户端建立连接、获取权限、维持和管理连接。所以第一步是客户端和服务器建立连接，完成TCP握手之后，连接器就要验证你的身份，这时候就要用到你输入的用户密码了，验证用户密码通过，就会去权限表里面查询出你拥有的权限。之后，权限里面的判断逻辑都会依赖以此时读到的权限。（这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。）

②. 查询缓存：然后就到查询缓存中去看看之前是否执行过这条语句，查询缓存中查询的语句以结果会以key-value形式存在，key就是查询语句，如果缓存中存在key，那么就把对应的value结果返回给客户端。查询缓存会失效非常平凡，只要对一个表更新，就会全部失效，所以查询缓存命中非常低，MySQL8.0就直接把查询缓存的整块功能删掉了。

③. 分析器：

1. 词法分析：分析器会先做词法分析主要是识别你输入的由多个字符串和空格组成的提条SQL语句，识别出里面的字符串是什么、分别代表什么。比如字符串T识别成表名T，字符串ID识别成列ID。
2. 语法分析：语法分析完毕就需要做词法分析，比如语句elect少打一个s就会报错、列名没对应上也会报错。

④. 优化器：在sql执行之前都要经过优化器的选择，优化器是在表里面有多个索引的时候，决定使用那个索引；或者在一个语句有多个关联表的时候，决定各个表的连接顺序。然后优化器就会选择一个最优的方案给执行器去执行。

⑤. 执行器：需要执行前需要判断一下你对这个表T是否有查询权限，如果没有就会返回权限错误。如果有权限，执行器会根据表的引擎定义，去使用这个引擎提供的接口，如果要查询的字段没有索引，就会一行一行的扫描，如果有索引那就走B+树扫描。

MYSQL一次更新事务是如何实现的？

3 MySQL8.0为什么不支持缓存查询了？

MySQL8.0以后就不支持缓存查询了，一旦数据表有更新，缓存就会被清空，因此只有数据是静态的时候 ，或者数据很少发生变化的时候，缓存查询才有价值，否则如果数据经常变更反而增加了SQL的查询时间。

4 MySQL数据缓存池（Buffer Pool）知道吗？

数据缓冲池是用来解决CPU直接与磁盘IO直接交互的问题，CPU执行速度与磁盘的速度是不匹配的，严重影响了性能。所以我们需要把数据先从磁盘放到内存中，这个内存指的就是缓冲池，让CPU与内存直接交互，效率就会高很多了。那么缓冲池的数据都是以页为单位的，那其实我们进行增删改查的操作其实就是（读页面、写页面、创建页面等操作）。

5 如果我们执行 SQL 语句的时候更新了缓存池中的数据，那么这些数据会马上同步到磁盘上吗？

当我们对数据库中的记录进行修改的时候，首先会修改缓存池页里面的记录数据，然后数据库会以一定的频率刷新到磁盘。（注意并不是每次发生更新操作，都会立即进行磁盘回写。缓冲池会采用一种叫做 checkpoint 的机制 将数据回写到磁盘上，这样做的好处就是提升了数据库的整体性能。）

索引

1 为什么要使用索引？

假如我们没有索引，那么我们就需要全表扫描，一条一条的查。索引就像是书的目录，用于快速找到数据记录的一种方式。而Innodb使用的索引是B+树，搜索数据的时间复杂度是O(logn) ，即2^x(树的深度)=N。

2 InnoDB索引一个页为什么是16KB？

这个是默认规定的，InnoDB中页的默认大小就是16KB,以页作为磁盘和内存之间交互关系的基本单位，也就是最少一次从磁盘读取到内存中最少是16KB。

3 对mysql索引结构了解吗?讲一下B+树？

mysql索引其实底层就是一颗B+树。

• B+树
  

B+树叶子节点存储的数据页存放的都是普通的数据，而非叶子节点存放的是数据页的目录项。页内的记录是按照列的大小顺序排成的单向列表，页与页之间是是双向链表。

4 为什么MySQL选择B+树而不选择B树或者红黑树呢？

• 红黑树是需要把全部数据加载到内存中的，MySQL的数据是存放在磁盘中，当数据量很大时候一般是不能把全部数据加载到内存中的，并且B+树的高度一般只有2-4层，高度更低，效率更高，所以不会选择红黑树。
• B+树是叶子节点存储数据，其他非叶子存储的是目录和关键字；而B树是叶子节点和非叶子节点都存放数据的。那么B树就会存在查询效率不稳定的问题，有可能在查找到叶子节点的时候才查找到数据；并且B+树的磁盘读写更低，B+树只有叶子节点才有数据，所以每次读写非叶子节点关键字会比B树的多。

5 B+树索引和Hash索引各自优缺点？

Hash索引：
①. Hash等值查询很快，时间复杂都O(1)，但是是无序的，无法进行范围查询，自然就不支持索引进行排序，所以也就不支持不支持模糊查询和多列索引的最左前缀匹配了。
②. hash索引任何时候都避免不了回表查询数据。
③. hash等值查询很快，但是不稳定，有可能会发生hash碰撞的情况，此时性能就无法预测。

B+树：
①. B+树是多路平衡二叉树，叶子节点存放数据，非叶子节点存放数据页，树的高度一般只有2-4层，高度低，效率更高，并支持排序，查询稳定。

6 我想问下你了解最左匹配原则吗？

• 这个还是举一个例子比较容易说明。首先我们有索引[a,b,c,d]，查询条件a=1and b=2 and c>3 and d = 4，则每个节点依次命中a、b、c无法命中d。
• 最左匹配原则只能匹配左边的，索引只能用于查找key是否相等，当遇到范围查询（>，<，between、like）左匹配就不能进一步匹配了。

7 什么是聚簇索引和非聚簇索引？

• 聚簇索引底层是一颗B+树，存放了完整的记录在叶子节点中，并且不需要我们通过index语句去创建，Innodb存储引擎会自动为我们创建。
• 非聚簇索引（又称二级索引），是需要我们手动通过index去创建，并且他存储的数据不是完整的数据，只存储了你当前创建的索引字段和主键。并且在查找中如果使用到非聚簇索引，并且字段还涉及到除了当前索引字段和主键字段以外的字段，是需要涉及到回表操作。

8 什么是回表？

假如我们建了一个非聚簇索引，字段是C2，现在需要查找完整的用户记录的话，那么我们通过C2去查找到数据之后，我们还需要在C2里面拿到对应的主键，还需要在聚簇索引中查找一遍。

9 为什么我们还需要一次回表操作呢？直接把完整的用户记录放到叶子节点不OK吗？

如果把完整的用户记录放到叶子结点是可以不用回表。但是太占地方了，相当于每建立一颗B+树都需要把所有的用户记录再都拷贝一遍，这就有点太浪费存储空间了。

10 MyISAN和InnoDB 索引的区别？

• ① 在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在 MyISAM 中却需要进行一次回表操作，意味着MyISAM中建立的索引相当于全部都是二级索引 。
• ② InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是分离的 ，索引文件仅保存数据记录的地址。
• ③ InnoDB的非聚簇索引data域存储相应记录主键的值 ，而MyISAM索引记录的是地址 。换句话说，InnoDB的所有非聚簇索引都引用主键作为data域。
• ④ MyISAM的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通 过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。
• ⑤ InnoDB要求表必须有主键（ MyISAM可以没有 ）。如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

11 索引类型图

12 那些情况适合创建索引？

1. 字段的数值有唯一性的限制
2. 频繁作为 WHERE 查询条件的字段
3. 经常 GROUP BY 和 ORDER BY 的列
4. UPDATE、DELETE 的 WHERE 条件列
5. DISTINCT 字段需要创建索引
6. 多表 JOIN 连接操作时，创建索引注意事项
7. 使用列的类型小的创建索引
8. 使用字符串前缀创建索引
9. 区分度高(散列性高)的列适合作为索引
10. 使用最频繁的列放到联合索引的左侧
11. 在多个字段都要创建索引的情况下，联合索引优于单值索引

13 那些情况不适合创建索引？

1. 在where中使用不到的字段，不要设置索引
2. 数据量小的表最好不要使用索引（比如少于1000个，那么是不需要创建索引的）
3. 有大量重复数据的列上不要建立索引
4. 避免对经常更新的表创建过多的索引
5. 不建议用无序的值作为索引
6. 删除不再使用或者很少使用的索引
7. 删除不再使用或者很少使用的索引

14 你谈一下sql如何优化？

1. 防止索引失效

• 如果遇到查询很慢，并且该字段经常用于查询，并且重复性不高，那么建议构建索引。
• 如果使用联合索引，那么要遵守最左前缀法则。
• 计算、函数、类型转换（手动或自动）也会导致索引失效。
• 范围条件右边的列索引失效（比如：(<) (<=) (>) (>=) 和 between 等）。
• 不等于(!= 或者<>)索引失效。
• is null可以使用索引，is not null无法使用索引。
• like以通配符%开头索引失效。
• OR 前后存在非索引的列，索引失效。

2. 关联表字段尽量创建索引。
3. 推荐少使用子查询，建议用联表查询替代子查询，连接查询不需要建立临时表，其速度比子查询更快。
4. order by字段建议加上索引
5. 可以优先考虑覆盖索引，避免回表。

15 那假设我不用MySQL自增的主键，你觉得会有什么问题呢？

那么主键需要考虑的是唯一性和空间尽可能短，这两点是需要考虑的，另外，索引是有序的，那么如果插入一条数据主键是无序的，那么如果当前页面满了，就需要移动数据，会影响效率。

事务

1 你是怎么理解InnoDB引擎中的事务的？

• 我认为事务是一组操作，要么一起成功要么一起失败，事务的目的是为了保证数据最终一致性。
• 举个列子假如我给你发了100块钱，你收到了一百块钱，我减了一百块钱。那么事务的目的就是保证我的余额扣减跟你的余额增加是同时成功或同步失败的，这样转账就正常啦。

2 事务的4个特性ACID?

1. 原子性（Atomic）
   原子性是不可分割，完整性，确保事务中的所有操作全部发生或全部不发生;如果所有的操作都成功了，事务也就成功了;如果任意一个操作失败了，整个事务也失败并回滚。
2. 一致性（Consistency）
   事务必须使数据库从一个一致性状态变换到另外一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。比如转账，转账前两个账户余额之和为2k，转账之后也应该是2K
3. 隔离性（Isolation）
   隔离性是指多个事务并发执行的时候，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
4. 持久性（Durability）
   事务一旦提交，就应该被永久保存起来

3 那么在MySQL在处理并发事务时会带来那些问题?

1. 脏写（ Dirty Write ）
对于两个事务 Session A、Session B，如果事务Session A 修改了 另一个 未提交 事务Session B 修改过 的数据，那就意味着发生了 脏写。

2. 脏读（ Dirty Read ）
   对于两个事务 Session A、Session B，Session A 读取 了已经被 Session B 更新 但还 没有被提交 的字段。 之后若 Session B 回滚 ，Session A 读取 的内容就是 临时且无效 的。
   
3. 不可重复读（ Non-Repeatable Read ）
   对于两个事务Session A、Session B，Session A 读取了一个字段，然后 Session B 更新了该字段。 之后 Session A 再次读取 同一个字段， 值就不同 了。那就意味着发生了不可重复读。
   
4. 幻读（ Phantom ）
   对于两个事务Session A、Session B, Session A 从一个表中 读取 了一个字段, 然后 Session B 在该表中 插 入了一些新的行。 之后, 如果 Session A 再次读取 同一个表, 就会多出几行。那就意味着发生了幻读。
   

4 那么MySQL并发产生的问题，如何解决？（事务的隔离级别）

为了解决并发事务带来的问题，所以产生了事务的4种隔离级别。

• READ UNCOMMITTED ：读未提交，在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。不能避免脏读、不可重复读、幻读。
• READ COMMITTED ：读已提交，它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。可以避免脏读，但不可 重复读、幻读问题仍然存在。
• REPEATABLE READ ：可重复读，事务A在读到一条数据之后，此时事务B对该数据进行了修改并提交，那么事务A再读该数据，读到的还是原来的内容。可以避免脏读、不可重复读，但幻读问题仍然存在。这是MySQL的默认隔离级别。
• SERIALIZABLE ：可串行化，确保事务可以从一个表中读取相同的行。在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作。所有的并发问题都可以避免，但性能十分低下。能避免脏读、不可重复读和幻读。
  

演示1. 读未提交之脏读

设置隔离级别为未提交读：

脏读就是指当前事务就在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问了这个数据，然后使用了这个数据。

演示2：读已提交

演示3. 不可重复读
设置隔离级别为可重复读，事务的执行流程如下：

当我们将当前会话的隔离级别设置为可重复读的时候，当前会话可以重复读，就是每次读取的结果集都相同，而不管其他事务有没有提交。但是在可重复读的隔离级别上会出现幻读的问题.

演示4：幻读

5 事务的ACID特性是如何实现的？

事务的四种特性大家都知道是原子性、一致性、隔离性和持久性，也就是ACID,那么这四种特性是如何实现的呢？

• 隔离性由锁机制实现
• 剩下的三种特性由事务的redo和undo日志实现：
  1.Redo Log是重做日志，可以再写入，恢复提交事务修改的数据页操作，来保证事务的持久性
  2.Undo Log是回滚日志，回滚行记录到某个保存点，来保证事务的原子性和一致性。

redo log 是存储引擎层（innodb）生成的日志，记录的是物理层面的对数据页的操作，比如在某数据页某偏移量下写入或删除了某数据。
undo log是存储引擎层（innodb）生成的日志，记录的是逻辑层面的操作，且记录的都是相反的操作，比如对某行数据进行了insert操作，uodo日志中就会记载相反的操作delete，主要用于事务的回滚。

• Redo和Undo本质上都是一种恢复操作。
  Undo是把对数据表的操作回滚回去的行为，而Redo操作是比如对表数据进行了操作，但是没有刷新到磁盘上时，数据库宕机了，在数据库重启后，可以去Redo日志中找回这种操作的记录，继续执行。

6 什么是Redo日志？

• 假如我们没有redo日志，为了保证事务的持久性，那么在事务提交之前，就需要刷新数据到磁盘中。
• 但是这样会有很大的问题，cpu是非常快的，需要量与磁盘的刷新量严重不匹配，那假如我们修改了一个字节，我们知道Innodb是以页为单位进行磁盘IO的，也就是说我们提交事务的时候不得不把一个完整的页面从内存刷新到磁盘中，而且默认页面大小是16kb，只修改一个字节就刷新16kb到数据到磁盘中显然是小题大做了。
• 随机IO刷新较慢，事务可能存在包含多个语句，即使一条语句可能需要修改好几个页面，假如这些页面并不是相邻的，那就意味着我们刷新磁盘时需要进行随机IO，随机IO是比顺序IO慢的，尤其是对于传统机械硬盘来说。
• 所以我们可以使用Redo日志来解决这个问题，我们在提交事务之前先把修改的信息做一个记录，然后记录到redo日志中进行记录，后期做定期刷盘就行了。

7 Redo日志的组成？

分为两个部分：缓冲区和日志文件区
缓冲区（redo log buffer）

• 缓冲区是内存层面的
• 缓冲区默认大小16M，最小是1M，最大4096M
• 缓冲区会将空间划分成一个个的缓冲块：
  
  查看缓冲池的大小：

mysql> show variables like '%innodb_log_buffer_size%';
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| innodb_log_buffer_size | 1048576 |
+------------------------+---------+
1 row in set, 1 warning (0.12 sec)

日志文件区（redo log file）

• 日志文件是磁盘层面的
• 查看mysql目录，其中logfile0和logfile1就是redo日志的文件区：
  

8 Redo日志运行流程

• 过程：
  1.将原始数据从磁盘读入内存中，修改内存中的数据拷贝
  2.生成一条日志记入redo log buffer中，记录的是数据修改的值
  3.当事务commit提交时，将redo log buffer 中的内容刷新到redo log file ，对redo log file采取的是追加写的方式
  4.隔一段时间就将内存中修改的数据刷寻到磁盘中

9 了解Redo刷盘策略吗？

• redo log 是由redo log buffer 和 redo log file 组成，那么当一条数据写入redo log buffer的时候，那什么时候刷新到redo log file中呢？这就需要我们的刷盘策略。
• InnoDB刷盘策略是由innodb_flush_log_at_trx_commit这个参数去控制刷盘频率的，他默认是1，就是每次commit的时候就执行刷盘操作
• 0的时候表示每次事务提交时不进行刷盘操作，每隔一秒就进行重做日志的同步
• 2的时候每次事务提交的时候只把redo log buffer 写入到 page cache中，不进行同步，由操作系统自己来决定何时同步。
  

10 什么是Undo日志？

事务是保证原子性和一致性的，那么当事务在执行过程中发生错误，比如服务器本身的错误，操作系统的错误、或者突然断电导致的错误，或者手动的ROLLBACK回滚事务，那么此时我们是不是需要保证数据的原子性，那么需要把数据恢复到原来的样子，所以我们就需要undo日志记录相反的操作。

11 MVCC了解吗?(多版本并发控制)

• MVCC（Multiversion Concurrency Control），多版本并发控制，MVCC通过数据行的多版本管理来实现数据库的并发控制，可以用来解决在事务隔离级别下一致性读问题，可以解决脏读、不可重复读、(幻读问题)。
• 主要依赖以隐藏字段trx_id，记录修改某条记录的事务id，roll_pointer指向前一个版本的修改信息；undo log存在修改前的一个版本修改信息；ReadView会产生一个视图，用来记录当前系统活跃的事务ID。
• 对于读已提交（READ COMMITTED）隔离级别的事务来说，就是每次读取数据前的时候生成一个新的ReadView；
• 对于重复读（REPEATABLE READ）隔离级别的事务来说，只会在第一次执行查询语句的时候生成一个ReadView，之后的查询就不会在生成了。

• MVCC的实现主要依赖于：隐藏字段、Undo log、ReadView。

1. 隐藏字段:
   ①. trx_id：记录修改某条记录时的事务id
   ②. roll_pointer：指向前一个版本修改信息
2. Undo log 存放了修改前的历史版本信息
3. ReadView是事务在使用MVCC机制进行快照读操作时产生的读视图，当数据库启动时，会产生当前数据库的一个快照，用来记录并维护系统当前活跃事务的ID（活跃指的是启动了，但还未提交）

下面举个READ COMMITTED例子：
READ COMMITTED ：每次读取数据前都生成一个ReadView。
现在有两个 事务id 分别为 10 、 20 的事务在执行:

# Transaction 10
BEGIN;
UPDATE student SET name="李四" WHERE id=1;
UPDATE student SET name="王五" WHERE id=1;
# Transaction 20
BEGIN;
# 更新了一些别的表的记录
...

说明：事务执行过程中，只有在第一次真正修改记录时（比如使用INSERT、DELETE、UPDATE语句），才会被分配一个单独的事务id，这个事务id是递增的。所以我们才在事务2中更新一些别的表的记录，目的是让它分配事务id。

此刻，表student 中 id 为 1 的记录得到的版本链表如下所示：

假设现在有一个使用 READ COMMITTED 隔离级别的事务开始执行：

# 使用READ COMMITTED隔离级别的事务
BEGIN;

# SELECT1：Transaction 10、20未提交
SELECT * FROM student WHERE id = 1; # 得到的列name的值为'张三'

之后，我们把 事务id 为 10 的事务提交一下：

# Transaction 10
BEGIN;
UPDATE student SET name="李四" WHERE id=1;
UPDATE student SET name="王五" WHERE id=1;
COMMIT;

然后再到 事务id 为 20 的事务中更新一下表 student 中 id 为 1 的记录：

# Transaction 20
BEGIN;
# 更新了一些别的表的记录
...
UPDATE student SET name="钱七" WHERE id=1;
UPDATE student SET name="宋八" WHERE id=1;

此刻，表student中 id 为 1 的记录的版本链就长这样：

然后再到刚才使用 READ COMMITTED 隔离级别的事务中继续查找这个 id 为 1 的记录，如下：

# 使用READ COMMITTED隔离级别的事务
BEGIN;

# SELECT1：Transaction 10、20均未提交
SELECT * FROM student WHERE id = 1; # 得到的列name的值为'张三'

# SELECT2：Transaction 10提交，Transaction 20未提交
SELECT * FROM student WHERE id = 1; # 得到的列name的值为'王五'

12 你来讲一下MVCC是如何解决脏读、不可重复读、幻读问题？

先来说一下MVCC的操作流程:
当查询一条记录的时候，系统是如何通过MVCC找到他的
1. 首先先获取自己事务的版本号，也就是事务id
2. 然后获取ReadView
3. 查询出来的数据与ReadView的事务版本号进行比较
4. 如果该数据不符合ReadView，就需要从Undo Log中获取历史数据
5. 直到知道符合的数据。

而ReadView规则主要是帮我们判断当前版本号数据是否可见。

• 隔离级别读已提交：可以解决脏读问题，其实就是一个事务在每次Select查询的时候，都会重新获取一次ReadView，但是这样同时又会产生不可重复读、幻读的情况。
  

• 隔离级别可重复读：就解决了不可重复读的问题，那是因为一个事务只在第一次Select的时候就获取了ReadView，而后面所有的Select都会复用这个ReadView
  

• 那么他能解决幻读问题吗？

• 在InnoDB引擎下的的repeatable read (可重复复读)隔离级别下，快照读MVCC影响下，已经解决了幻读的问题（因为它是读历史版本的数据）

• 而如果是当前读（指的是 select * from table for update），则需要配合间隙锁来解决幻读的问题。

锁

1 先展示一下锁的分类

1 你说一下MySQL中什么是行锁、表锁？

表锁：

• 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。

行锁：

• 行级锁是MySql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。
• 他的特点是开销大，加锁慢;会出现死锁;优点是锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
• InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据，InnoDB才使用行级锁,否则，InnoDB将使用表锁.

2 MySQL行锁有哪些？

• 记录锁(Record Lock)：只能锁住当前一行数据。例如下图：只能锁住8这条数据。
• 间隙锁(Gap Lock)：只能锁住当前id前面的间隙。例如下图：只能锁住（3，8）
• 临键锁(Next-Key Lock)：可以说是记锁和间隙锁的结合，锁住当前行和当前行前面间隙的数据。例如下图：可以锁住(3,8]

3 高并发下，如何做到安全的修改同一行数据？

1. 使用悲观锁
   悲观锁本质是当前只有一个线程执行操作，排斥外部请求的修改。遇到加锁的状态，就必须等待。结束了唤醒其他线程进行处理。虽然此方案的确解决了数据安全的问题，但是，我们的场景是“高并发”。也就是说，会很多这样的修改请求，每个请求都需要等待“锁”，某些线程可能永远都没有机会抢到这个“锁”，这种请求就会死在那里。同时，这种请求会很多，瞬间增大系统的平均响应时间，结果是可用连接数被耗尽，系统陷入异常。

2. 使用乐观锁
   这个时候，我们就可以讨论一下“乐观锁”的思路了。乐观锁，是相对于“悲观锁”采用更为宽松的加锁机制，大都是采用带版本号（Version）更新。实现就是，这个数据所有请求都有资格去修改，但会获得一个该数据的版本号，只有版本号符合的才能更新成功，其他的返回抢购失败。这样的话，我们就不需要考虑队列的问题，不过，它会增大CPU的计算开销。但是，综合来说，这是一个比较好的解决方案。

4 MySQL死锁怎么产生的？

两个事务都持有对方需要的锁，并且在等待对方释放，并且双方都不会释放自己的锁。

5 MySQL死锁如何解决？

1. 等待，直到超时，当一个事务等待时间超过指定的阈值就会回滚。（通过参数innodb_lock_wait_timeout=50s设置）缺点也是很明显的，对于线程来说，等待时间是不太能接受的，等待长了接受不了，等待时间短了又会误伤普通的锁等待。

2. 使用死锁检查处理死锁程序。
   死锁检测的原理是构建以事务为顶点，锁为边的有向图，判断有向图是否有环，有就存在锁。（innodb_deadlock_detect=on 表示开启这个检测）

缺点：每个新的被阻塞的线程，都要判断是不是由于自己的加入导致了死锁，这个操作时间复杂度是O(n)。如果100个并发线程同时更新同一行，意味着要检测100*100=1万次，1万个线程就会有1千万次检测。

如何解决？

• 方式1：关闭死锁检测，但意味着可能会出现大量的超时，会导致业务有损。
• 方式2：控制并发访问的数量。比如在中间件中实现对于相同行的更新，在进入引擎之前排队，这样在InnoDB内部就不会有大量的死锁检测工作。

主从复制

1 你说一下主从复制的原理？

主从复制会基于三个线程来操作，一个主库线程，二个从库线程。

• 二进制日志转储线程 （Binlog dump thread）是一个主库线程。当从库线程连接的时候， 主库可以将二进制日志发送给从库，当主库读取事件（Event）的时候，会在 Binlog 上 加锁 ，读取完成之后，再将锁释放掉。

• 从库 I/O 线程 会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志 （Relay log）。

• 从库 SQL 线程 会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。

总结：复制三步骤

• ①. 步骤1： Master 将写操作记录到二进制日志（ binlog ）。
• ②. 步骤2： Slave 将 Master 的binary log events拷贝到它的中继日志（ relay log ）；
• ③. 步骤3： Slave 重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的，而且重启后从 接入点 开始复制。

场景

1 你说一下慢查询如何排查？

• ①. 如果需要排查慢查询，首先需要开启慢查询日志，需要我们手动开启，通过命令 set global slow_query_log=‘ON’;开启，通过set global long_query_time可以设置时间阈值。
• ②. 开启慢查询之后，然后查询的sql语句超过阈值就会被记录到慢查询日志中，我们可以使用mysql提供的日志分析工具mysqldumpslow捞出sql，然后对sql进行排查，分析sql慢原因。

2 那你说一下，你拿到sql之后是如何排查这条慢sql的？

定位查询慢的sql之后，就可以使用EXPLAIN来分析这条查询语句的执行计划。

• ①. id相同，可认为是一组，从上往下执行；所有组中，id值越大，优先级越高，越先执行；每个id号，代表一个独立的查询，一个sql查询躺数越少越好。
• ②. select_type 查询类型。（例如：SIMPLE、PRIMARY、UNION、SUBQUERY……）
• ③. partitions 代表分区命中的情况。
• ④. type☆（访问类型），执行访问时访问的方法，有 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL,SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。
• ⑤. possible_keys和key，possible_keys可能使用的索引，key实际使用的索引。
• ⑥. key_len☆ 实际使用到的索引长度。
• ⑦. ref 显示索引的那一列被使用。
• ⑧. rows☆ 预计读取的记录条数，值越小越好。
• ⑨. filtered 在联表扫描的时候才有用。（查询优化器打算把s1作为驱动表，s2当做被驱动表。我们可以看到驱动表s1表的执行计划的rows列为9688，filtered列为10.00，这意味着驱动表s1的扇出值就是9688 x 10.00% = 968.8，这说明还要对被驱动表执行大约968次查询。）
• ⑩. extra☆ 一些额外信息

3 sql已经最优了？但是查询还是很慢？怎么解决？