MySQL(一)

MySQL(一)

1. MySQL事务

参考：事务隔离级别是怎么实现的？ | 小林coding (xiaolincoding.com)

• 事务的基本要素（ACID）

  • 原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位
  • 一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。
  • 隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
  • 持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。
    MySQL事务隔离级别：

• 事务隔离级别以及对并行事务的问题发生情况：

  事务隔离级别	含义	脏读	不可重复读	幻读
  读未提交（read-uncommitted）	指一个事务还没提交时，它做的变更就能被其他事务看到	✔	✔	✔
  读提交（read-committed）	指一个事务提交之后，它做的变更才能被其他事务看到	❌	✔	✔
  可重复读（repeatable-read）	指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别	❌	❌	✔
  串行化（serializable)	会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行	❌	❌	❌

• 事务的并发问题

  • 脏读：事务 A 读取了事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据。即一个事务「读到」了另一个「未提交事务修改过的数据」，就意味着发生了「脏读」现象。
  • 不可重复读：事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果不一致
  • 幻读：系统管理员 A 将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员 B 就在这个时候插入了一条具体分数的记录，当系统管理员 A 改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读，即在一个事务内多次查询某个符合查询条件的「记录数量」，如果出现前后两次查询到的记录数量不一样的情况，就意味着发生了「幻读」现象。

• 如何解决脏读、幻读、不可重复读

  • 脏读： 隔离级别为 读提交、可重复读、串行化可以解决脏读
  • 不可重复读：隔离级别为可重复读、串行化可以解决不可重复读
  • 幻读：隔离级别为串行化可以解决幻读、通过MVCC + 区间锁可以解决幻读

• 小结：

  • 不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

2. 悲观锁和乐观锁概念

悲观锁

• 认为线程安全问题一定会发生，因此在操作数据之前先获取锁，确保线程串行执行
• 例如 Synchornized、Lock 都属于悲观锁

乐观锁

• 认为线程安全问题不一定会发生，因此不加锁，只是在更新数据时去判断有没有其它线程对数据做了修改。
• 如果没有修改则认为是安全的，自己才更新数据。
• 如果已经被其它线程修改说明发生了安全问题，此时可以重试或异常

3. 悲观锁和乐观锁的怎么实现

• 悲观锁：select…for update是MySQL提供的实现悲观锁的方式。例如： select price from item where id=100 for update 此时在items表中，id为100的那条数据就被我们锁定了，其它的要执行select price from items where id=100 for update的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改。MySQL有个问题是select…for update语句执行中所有扫描过的行都会被锁上，因此在MySQL中用悲观锁务必须确定走了索引，而不是全表扫描，否则将会将整个数据表锁住。

• 乐观锁：乐观锁相对悲观锁而言，它认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回错误信息，让用户决定如何去做。利用数据版本号（version）机制是乐观锁最常用的一种实现方式。一般通过为数据库表增加一个数字类型的 “version” 字段，当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值+1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据，返回更新失败。

4. 为什么不建议查询的时候在条件上用in？

因为用 in 的话那么 mySQL 就不会走索引。所以一定要用 in 的话，最好是先用 in 查出数据，然后 join 到查询条件里。

举例：

SELECT t.*
FROM t
JOIN (
    SELECT city, name
    FROM t
    WHERE city IN ('杭州', '苏州')
) AS filtered_cities ON t.city = filtered_cities.city AND t.name = filtered_cities.name
LIMIT 100;

5. 如何避免查询时的排序

在查询时不可避免地会遇到排序的情况，就是order by，但是排序会比较浪费时间，如果可以避免的话就会大大提升查询效率。

答：可以使用索引，有了索引以后会自动排序。如果有多个条件，可以将几个条件组成联合索引。但需要注意的是，联合索引有最左前缀条件，因此这样的话可以多建几个索引，因为MySQL有覆盖索引机制，可以选择最合适的索引，这样的话会避免后面组装信息时查表的时间消耗。

6. MySQL的三种锁

1. 表级锁：锁定整个表，使得其他事务不能对该表进行读写操作。表级锁适用于对整个表进行批量操作的场景，如ALTER TABLE、DROP TABLE等。

2. 行级锁：锁定表中的某一行或多行数据，使得其他事务不能对这些行进行读写操作。行级锁适用于对表中部分数据进行修改的场景，如UPDATE、DELETE等。

3. 页面锁：锁定索引页或数据页，使得其他事务不能对这些页进行读写操作。页面锁是InnoDB存储引擎中的一种锁机制，主要用于提高并发性能。

另外MySQL还有 READ 锁和 WRITE 锁，READ 锁为共享锁，读共享。WRITE 锁为互斥锁。

7. 索引提示

有两种，分别是 USE INDEX 和 FORCE INDEX

• USE INDEX

  • 用于让 MySQL 使用建议的索引去查询，但 MySQL 有可能不会采纳。

  • 语法示例：

    SELECT column_list
    FROM table_name
    USE INDEX (index_list)
    WHERE condition;

• FORCE INDEX

  • 用于让 MySQL 使用建议的索引去查询，必定会采纳。

  • 语法示例：

    SELECT *
    FROM table_name
    FORCE INDEX (index_list)
    WHERE condition;

8. 复合索引使用条件

复合索引就是由多个列组成的索引，举例：CREATE INDEX index_name ON table_name(a, b, c);

必须满足最左前缀条件，即必须有 a=xxx

9. 隐藏索引

MySQL 8 引入了隐藏索引。隐藏索引是实际存在的，但是对 MySQL 查询优化器不可见的索引。即使使用 FORCE INDEX，优化器也不会使用隐藏索引。

创建隐藏索引语句：

CREATE INDEX index_name
ON table_name(c1, c2, ...) INVISIBLE;

修改索引可见性：

ALTER TABLE table_name
ALTER INDEX index_name [VISIBLE | INVISIBLE];	

10. 字符串前缀索引

相比于为整个字符串列创建索引，前缀索引能减少磁盘的使用量，并提高索引的写入速度。

创建索引：

CREATE INDEX index_name
ON table_name (column_name(length));

创建表时顺便创建索引：

CREATE TABLE table_name(
  column_list,
  INDEX(column_name(length))
);

说明：

• 如果列为 CHAR， VARCHAR 和 TEXT 等非二进制字符类型， length 指定用于前缀索引的字符数。
• 如果列为 BINARY， VARBINARY 和 BLOB 等二进制字符类型， length 指定用于前缀索引的字节数。

可以在查询时使用 like 'XX%' 来使用索引。比如手机号的开头或者是其他的一些场景。

11. MySQL 连接类型

1. 内连接 INNER JOIN
   • 只保留满足条件的，相当于加了一层条件过滤
2. 左连接 LEFT JOIN
   • 保留左侧全部，右侧的满足条件的
3. 右连接 RIGHT JOIN
   • 保留右侧全部，左侧的满足条件的
4. 交叉连接 CROSS JOIN
   • 表 a 有 m 行数据，b 有 n 行数据，最后结果就是 m * n 行数据

12. UNION 和 UNION ALL

注意：

1. UNION 运算用于将两个结果集合成一个，是数据行维度的组合。
2. UNION 运算包括 UNION DISTINCT 和 UNION ALL 两种算法，其中 UNION DISTINCT 可以简写为 UNION。
3. UNION 会删除两个结果集中的重复记录行，而 UNION ALL 则保留全部记录行。
4. UNION 运算要求参与运算的两个结果集的列数必须一样。
5. UNION 运算取第一个参与运算的结果集的列名作为最终的列名。
6. 可以使用 ORDER BY 对 UNION 运算的结果进行排序。

• UNION
  • 是 UNION DISTINCT 的简写，不会返回重复的数据
• UNION ALL
  • 会返回全部的数据

13. COUNT(*) 和 COUNT(1) 区别

性能排序：COUNT(*)=COUNT(1)>COUNT(主键字段)>COUNT(字段)

COUNT(字段)：查询表中 字段 不为 NULL 的数量

COUNT(1)：查询表中所有数量

14. sql 的执行顺序

1. 先执行 from…join…on… 确定连接关系得到初步数据
2. where 实现对条件的筛选
3. group by 分组
4. 各组分别执行 having 中的普通筛选或者聚合筛选
5. 然后把再根据我们要的数据进行 select，可以是普通字段查询也可以是获取聚合函数的查询结果
6. 将查询结果去重 distinct
7. 最后合并各组的查询结果，按照 order by 的条件进行排序
8. 执行 limit 和 offset

15. 关于TIMESTAMP和DATETIME

1. TIMESTAMP值会根据时区变化而变化，而DATETIME不会。
2. TIMESTAMP会抖动

16. 关于使用内连接和’,’查询

select s.sid, s.sname, s.classid, r.cname, r.score from student s inner join result
    r on s.sid = r.sid where s.sname = '索隆';
    
select s.sid, s.sname, s.classid, r.cname, r.score from student s inner join result
    r where s.sid = r.sid where s.sname = '索隆';
    
select s.sid, s.sname, s.classid, r.cname, r.score from student s ,result
    r where s.sid = r.sid where s.sname = '索隆';

这三句SQL在执行效率和结果上的区别主要在于连接条件的位置和连接方式。

1. 第一句SQL使用了内连接（inner join），并且在连接条件中使用了on关键字，将连接条件放在了on后面。这种方式在执行时会先进行笛卡尔积操作，然后筛选出满足条件的记录。由于使用了内连接，所以只会返回两个表中匹配的记录。
2. 第二句SQL同样使用了内连接，但是在连接条件中使用了where关键字，将连接条件放在了where后面。这种方式在执行时也会先进行笛卡尔积操作，然后筛选出满足条件的记录。与第一句SQL相比，性能上没有明显差异。
3. 第三句SQL使用了逗号连接（cross join），它会先将student表和result表进行笛卡尔积操作，然后再筛选出满足条件的记录。这种方式会产生大量的中间结果，因此在执行效率上较低。

综上所述，第一句和第二句SQL在执行效率上没有太大差别，因为它们都使用了内连接。而第三句SQL使用了逗号连接，执行效率最低。在实际使用中，建议使用第一句或第二句SQL，因为它们更符合SQL的标准语法，且执行效率较高。

17. HAVING/WHERE/ON 之间的区别

在MySQL中，HAVING、WHERE 和 ON 都用于筛选数据，但它们的应用场景和作用有所不同。以下是它们的区别：

1. **WHERE**：

   • 作用：用于筛选单个表中的记录，或在JOIN之前筛选表中的数据。

   • 执行顺序：在数据分组之前执行，即GROUP BY之前。

   • 使用场景：适用于筛选原始数据行。

   • 示例：

     SELECT * FROM employees WHERE age > 30;

2. **HAVING**：

   • 作用：用于筛选分组后的数据。

   • 执行顺序：在GROUP BY之后执行，主要用于对聚合结果的筛选。

   • 使用场景：当你想对分组后的结果进行条件筛选时，使用HAVING。

   • 示例：

     SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5;

3. **ON**：

   • 作用：用于JOIN操作中指定联接条件，通常用于多表连接时，决定如何联接两个表。

   • 执行顺序：在JOIN操作时执行，用于确定哪些行进行联接。

   • 使用场景：多表查询时，指定两个表之间的联接条件。

   • 示例：

     SELECT * FROM employees e JOIN departments d ON e.department_id = d.id;

总结：

• WHERE 用于筛选原始表数据。
• HAVING 用于筛选分组后的数据。
• ON 用于定义多表连接条件。

18. 日期函数

1. 获取时间

   # 获取当前日期时间
   select NOW(); # 2021-04-02 09:25:29
   # 获取当前日期
   SELECT CURDATE(); # 2021-04-02
   # 获取当前时间
   SELECT CURTIME();  # 09:26:10
    
   # 对于时间2021-04-02 09:25:29，分别获取其年、月、日、时、分、秒
   SELECT EXTRACT(YEAR FROM NOW()); # 2021
   SELECT EXTRACT(MONTH FROM NOW()); # 4
   SELECT EXTRACT(DAY FROM NOW()); # 2
   SELECT EXTRACT(HOUR FROM NOW()); # 9
   SELECT EXTRACT(MINUTE FROM NOW()); # 25
   SELECT EXTRACT(SECOND FROM NOW()); # 29
    
   # 或者从日期格式字符串中获取
   SELECT EXTRACT(SECOND FROM '2021-04-02 10:37:14.123456');  # 14

2. 日期增加或减少

   # 时间减少1小时(前一小时)
   select date_sub(now(), INTERVAL 1 hour);
    
   # 日期增加1天
   select date_add(now(), INTERVAL 1 day);
    
   # 其他间隔
   INTERVAL 1 YEAR
   INTERVAL 1 MONTH
   INTERVAL 1 DAY
   INTERVAL 1 HOUR
   INTERVAL 1 MINUTE
   INTERVAL 1 SECOND

19. MySQL执行一条语句的过程

1. 连接器
   • 与客户端建立 TCP 三次握手
   • 检验用户名密码并读取权限
2. 查询缓存
   • 在MySQL8.0之前会默认开启缓存
3. 解析器解析SQL
   • 词法分析
   • 语法分析
4. 执行SQL
   • (prepare 阶段)预处理器：检验表名列名
   • (optimize 阶段)优化器：确定SQL查询的方案，如确定使用哪个索引
   • (execute 阶段)执行器：确定三种执行过程(主键索引查询、全表扫描、下推索引)

20. 为什么 InnoDB 代替了 MyISAM？

具体文章：(十三)MySQL引擎篇：半道出家的InnoDB为何能替换官方的MyISAM？MyISAM是MySQL引擎中的“亲生子” - 掘金 (juejin.cn)

1. 存储方式：MyISAM引擎会将表数据和索引数据分成两个文件存储。
2. 索引支持：因为MyISAM引擎的表数据和索引数据是分开的，因此不支持聚簇索引。
3. 事务支持：由于MyISAM引擎没有undo-log日志，所以不支持多条SQL组成事务并回滚。
4. 故障恢复：MyISAM引擎依靠bin-log日志实现，bin-log中未写入的数据会永久丢失。
5. 锁粒度支持：因为MyISAM不支持聚簇索引，因此无法实现行锁，所有并发操作只能加表锁。
6. 并发性能：MyISAM引擎仅支持表锁，所以多条线程出现读-写并发场景时会阻塞。
7. 内存利用度：MyISAM引擎过于依赖MySQL Server，对缓冲池、异步IO技术开发度不够。

21. 脏读、幻读和不可重复读

脏读

• 脏读是指读取到其他事务未提交的数据

不可重复读

• 不可重复读是指在同一次事务中前后查询不一致的问题
• 指在并发更新时，另一个事务前后执行相同条件的查询得到的数据不一致

幻读

• 幻读是指一次事务中前后数据量发生变化，产生不可预料的问题
• 指并发插入、删除时，另一个事务前后执行相同条件的查询得到的数据不一致

不可重复读和幻读的比较

1. 幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。
2. 不可重复读的重点是修改，幻读的重点在于插入或者删除
3. 但如果从控制的角度来看, 两者的区别就比较大：对于前者, 只需要锁住满足条件的记录；对于后者, 要锁住满足条件及其相近的记录

解决方法

• 如何解决以上问题，事务的隔离级别就派上用场了

  1. 禁止写时读，避免了“脏读”，对应隔离级别read committed。
  2. 禁止读时写，避免了“不可重复读”，对应隔离级别repeatable read。
  3. 而为了避免“幻读”，干脆把整个表给锁住了，只能是serialize了。

• 隔离级别越高，并行度越低，付出的代价越大。

• MySQL默认事务隔离级别为：可重复读（repeatable-read），因此当我们使用MySQL进行实际开发时一般不会发生“脏读”和“不可重复读”。现在可能遇到的问题就是“幻读”，不过MySQL通过多版本并发控制（MVCC）机制解决了该问题。

22. 索引是什么？MySQL用的是什么作为索引？

• 索引是提升数据查询速度的一种数据结构。

• MySQL 用的是 B+树 作为索引

• 索引三要素：B+树、索引分类、最左匹配原则

23. 索引的分类

参考：10｜数据库索引：为什么MySQL用B+树而不用B树？.md | Leon406资源站

1. 根据叶子节点是否存储数据来划分，可以分成 聚簇索引 和 非聚簇索引，如果索引叶子节点存储的是数据行，那么它就是聚簇索引，否则就是非聚簇索引。
2. 如果某个索引包含某个查询的所有列，那么这个索引就是 覆盖索引。
3. 如果索引的值必须是唯一的，不能重复，那么这个索引就是 唯一索引。
4. 如果索引的某个列，只包含该列值的前一部分，那么这个索引就是 前缀索引。比如说在一个类型是 varchar(128) 的列上，选择前 64 个字符作为索引。
5. 如果某个索引由多个列组成，那么这个索引就是 组合索引， 也叫做联合索引。
6. 全文索引 是指用于支持文本模糊查询的索引。
7. 哈希索引 是指使用哈希算法的索引，但是 MySQL 的 InnoDB 引擎并不支持这种索引。

24. 什么是回表？为什么会发生回表？怎么解决？

1. 什么是回表：回表就是在查询一次索引之后并没有直接找到数据，还需要再次查询数据表

2. 为什么会发生回表：是因为第一次查询并没有直接查到数据，而是查到了非聚簇索引，那么还需要回表查询数据

3. 怎么解决：建立覆盖索引即可，需要包含查询所需的所有列

25. 索引的最左匹配原则是什么？

• 在使用 sql 查询的时候，where 后的条件都是按顺序执行的，如果需要用到索引，那么查询条件应与索引设计的列顺序相一致

• 比如，我们建立了 a、b、c 列的组合索引，然后我们对如下的条件进行查询(连接用 AND )

  a	b	c	是否会使用索引	使用的列
  1	2	3	✔	a、b、c
  	2	3	❌
  1	2		✔	a、b
  1		3	✔	a

  因为索引是从左往右进行匹配的，相当于 for 循环，外层是 a 然后是 b 然后是 c 以此类推，只有前一个列是有序的才能继续使用索引的下一个列，因此当 b 列没有值的时候，最后一条查询只会用到 a 列，这就是最左匹配原则。

• 当查询时用到了某个列，但这个列是范围查询，那么组合索引只使用到这个列，这个列后面的列均无法使用组合索引。而 IN 和 NOT IN 是特殊的范围查询，是否会使用索引要看查询成本，如果查询走索引的(I/O)花费实践比直接从聚簇索引时间长，就不会走索引。但是，无论走不走索引，这个列后面的列也是无法使用组合索引的。

• 另外由于是前一个列有序，那么如果查询用 OR 的话，比如 a = 1 OR b = 2 这样也是不会用到索引，而像 a = 1 AND (b = 2 OR c = 3) 或者 a = 1 AND b > 2 AND c = 3 这样的只会用到 a 列，因为 b 列是无序的，没法使用索引，会出现索引失效。

• 要想 OR 使用组合索引，那么 OR 的两侧应各自有单独的索引，这样可以进行索引合并。

26. MySQL 为什么用 B+树？

参考：10｜数据库索引：为什么MySQL用B+树而不用B树？.md | Leon406资源站

回答这个问题，你就不能仅仅局限在 B+ 树和 B 树上，你要连带着二叉树、红黑树、跳表一起讨论。总结起来，在用作索引的时候，其他数据结构都有一些难以容忍的缺陷。

• 与 B+ 树相比，平衡二叉树、红黑树在同等数据量下， 高度更高，性能更差，而且它们会频繁执行再平衡过程，来保证树形结构平衡。
• 与 B+ 树相比，跳表在极端情况下会退化为链表， 平衡性差，而数据库查询需要一个可预期的查询时间，并且跳表需要更多的内存。
• 与 B+ 树相比，B 树的数据存储在全部节点中， 对范围查询不友好。 非叶子节点存储了数据， 导致内存中难以放下全部非叶子节点。如果内存放不下非叶子节点，那么就意味着查询非叶子节点的时候都需要磁盘 IO。

27. 为什么数据库不使用索引？

参考：10｜数据库索引：为什么MySQL用B+树而不用B树？.md | Leon406资源站

可能不使用的几种情况：

• 使用了 !=、 LIKE 之类的查询。
• 字段区分度不大。比如说你的 status 列只有 0 和 1 两个值，那么数据库也有可能不用。
• 使用了特殊表达式，包括数学运算和函数调用。
• 数据量太小，或者 MySQL 觉得全表扫描反而更快的时候。

我稍微强调一下，这里说的是“ 可能 不使用索引”，不是说一定不使用索引。比如说 LIKE 查询，如果只是 LIKE abc% 这种前缀查询，那么还是可能用索引的。

你还可以进一步解释 FORCE INDEX（强迫使用索引）、USE INDEX（使用索引）或者 IGNORE INDEX（忽略索引）之类的 SQL 提示，关键词是 取决于数据库。

28. 组合索引与排序

• 当使用 order by 的时候 MySQL 内部会进行排序，但是这种排序有两种，分别是内存排序和外部文件排序，具体可以使用 explain 看看结果里 Extra 里有没有用到 Using filesort
• 内存排序：将数据放到内存临时表，然后根据需求进行快速排序
• 外部文件排序：内存放不下时会将部分排序结果放在磁盘上，空出内存空间方便继续排序，最后再合并到一起，这种使用的是归并排序
• 因此使用外部文件排序会影响查询性能，而这是排序的字段没有索引的情况，如果使用组合索引就可以避免这种情况的发生

29. 索引下推

• MySQL 5.6开始的，默认开启。如果条件判断字段在二级索引 b+树 里就会下推到 innodb 引擎层过滤
• 原先的数据过滤是在 server 里进行的，使用索引下推后会在 引擎层 进行，可以减少回表次数
• Extra 里的 Using index condition 就是使用了索引下推

30. 索引操作以及如何判断是否使用索引？

• 创建索引：create index 索引名 on 表名(列名);

• 查看索引：show index from 表名;

• 删除索引：drop index 索引名 on 表名;

• 使用 explain 解析语句后可以看出是否使用索引：

  • explain select * from student where classid = 1;

    

    可以看出 type 的值是 ref 并且 key 显示的是 idx_classe 由此可以看出用了索引

  • explain select * from student where classid > 3;

    

    可以看出 type 的值是 range 并且 key 显示的是 idx_classe 由此可以看出用了索引，并且还可以通过 Extra 里的 Using index condition 发现使用了索引下推

31. 索引失效的几种情况

参考文章：索引失效有哪些？ | 小林coding (xiaolincoding.com)

1. 对索引使用左或左右模糊匹配

   例：select * from db where name like '%W'

2. 对索引使用函数

   例：select * from db where length(name) = 6

3. 对索引进行表达式计算

   例：select * from db where age + 1 = 10;

4. 对索引进行隐式类型转换

   例：索引 name 类型是 varchar，而语句 select * from db where name = 1

   注意：如果 name 类型是整型，但输入的是字符串，还是会走索引，因为 MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较

5. 联合索引非最左匹配

   例：创建了 db(id, name, age) 的联合索引，但查询语句：select * from db where name = 'a'

6. where 子句中有 or ，但是 or 左右两侧有一个列没有索引

32. CBO 是什么

参考文章：11 索引出错：请理解 CBO 的工作原理 (lianglianglee.com)

• MySQL 分为两层 Server层 和 Engine层
  • Server层包括：SQL分析器(sql_parse.cc)、SQL优化器(sql_optimizer.cc)、SQL执行器(sql_executer.cc)
  • Engine层包括存储引擎比如常用的 InnoDB 还有用于在内存中存储临时结果的 TempTable
• SQL优化器会分析所有可能的执行计划，选择成本最低的执行，这种优化器被称为 CBO(Cost-based-Optimizer，基于成本的优化器)
• MySQL 中一条 SQL 的成本如下：Cost = Server Cost + Engine Cost = CPU Cost + I/O Cost
  • CPU Cost 表示计算的开销，比如索引键值的比较、记录值的比较、结果集的排序……这些操作都在 Server 层完成；
  • IO Cost 表示引擎层 IO 的开销，MySQL 8.0 可以通过区分一张表的数据是否在内存中，分别计算读取内存 IO 开销以及读取磁盘 IO 的开销。

33. CBO 判断不使用索引的情况

参考文章：11 索引出错：请理解 CBO 的工作原理 (lianglianglee.com)

1. 使用索引开销更大

   • 这种情况一般可以用 explain 发现，在列 possible_keys 里识别出了可用的索引，但并没有使用，原因可能是优化器认为使用索引后回表导致的开销更大

2. 索引创建在有限状态上

   • 这种一般是有数据倾斜的情况，MySQL 默认一个列的所有值的分布情况都是平均的，因此当一个列仅有 n 个值的时候，MySQL 就会因为默认每个值的概率都是 1/n 而导致使用全表扫描，但实际上可能有的值只有很少的数量，这就会导致 CBO 判断不使用索引

   • 对于这种情况我们可以利用 MySQL 8.0 的直方图功能，创建一个直方图，让优化器知道数据的分布，从而更好地选择执行计划

     ANALYZE TABLE 表名
     UPDATE HISTOGRAM ON 字段名;

   • 然后使用如下语句查询数值分布

     SELECT 
     v value, 
     CONCAT(round((c - LAG(c, 1, 0) over()) * 100,1), '%') ratio
     FROM information_schema.column_statistics, 
     JSON_TABLE(histogram->'$.buckets','$[*]' COLUMNS(v VARCHAR(60) PATH '$[0]', c double PATH '$[1]')) hist
     WHERE column_name = '字段名';

34. 范围查询的索引使用情况

场景：有表 table ，表中有列 a, b ，有组合索引 (a, b)

1. select * from table where a > 1 and b = 2

   b无法使用索引：由于 a 是范围查询，因此 b 无法使用到索引，因为 a > 1 的情况下 b 是无序的，因此只有 a 使用到了索引

2. select * from table where a >= 1 and b = 2

   b部分能使用索引：当 a = 1 的时候 b 是有序的，因此只有当 a = 1 这个情况 b 可以使用到索引，而 a > 1 的部分是使用不到的

3. select * from table where a between 2 and 5 and b = 2

   b无法使用索引：a 是范围查询，因此 b 无法使用到索引

35. InnoDB 引擎通过什么技术来保证事务的特性？

• 持久性：通过 redo log (重做日志) 来保证的
• 原子性：通过 undo log (回滚日志) 来保证的
• 隔离性：通过 MVCC (多版本并发控制) 或锁机制来保证的
• 一致性：通过持久性+原子性+隔离性来保证

36.当前读和快照读

• 当前读：
  • 当前读是指读取最新的、已经提交或者正在修改的数据，并且对数据有修改的可能。因为它涉及数据的修改或更新，所以需要使用锁来保证数据的一致性和并发控制。当前读会使用MVCC和锁机制。
  • 常见的需要当前读的操作包括：
    • SELECT ... FOR UPDATE
    • SELECT ... LOCK IN SHARE MODE
    • UPDATE
    • DELETE
    • INSERT
  • 这些操作会对数据加锁，以防止其他事务同时修改数据。例如，SELECT ... FOR UPDATE 会对读取的行加排他锁（exclusive lock），防止其他事务修改相同的数据行。
• 快照读：
  • 快照读是指读取事务开始时的一致性快照，而不是当前最新的值。快照读只读取历史版本的数据，而不对其进行加锁。快照读完全依赖 MVCC 机制来控制隔离级别。
  • 常见的快照读操作是：
    • 普通的 SELECT 语句，不带锁的读取操作。
  • 在 REPEATABLE READ(可重复读) 隔离级别下，事务的每次快照读都会看到事务开始时的一致性快照，无论其他事务是否已经修改或提交了数据。这是通过 MVCC 保持多个版本的历史记录实现的，MySQL 会在需要时从 undo log 中读取旧版本数据。在 READ COMMITTED(读提交) 隔离级别下，每次读取时会生成新的快照，所以读到的可能是其他事务已经提交的最新数据。
  • 注意：快照读的前提是隔离级别不是串行级别，在串行级别下，事务之间完全串行执行，快照读会退化为当前读
• 对比总结
  • 当前读：使用 MVCC 和锁 机制，以确保数据的准确性和一致性，防止并发冲突，适用于需要修改数据或需要强一致性的场景。当前读实际上是一种加锁的操作，是悲观锁的实现。
  • 快照读：仅使用 MVCC，依赖数据的多个版本来实现并发控制，不对数据加锁，适用于只读查询的场景。MVCC 主要就是为了实现读-写冲突不加锁，而这个读指的就是快照读，是乐观锁的实现。

37. MySQL 使用的 MVCC 是什么？

参考文章：全网最详细MVCC讲解，一篇看懂-腾讯云开发者社区-腾讯云 (tencent.com)、事务隔离级别是怎么实现的？ | 小林coding (xiaolincoding.com)、13｜MVCC协议：MySQL在修改数据的时候，还能不能读到这条数据？.md | Leon406资源站

1. MVCC 全称 Multi-Version Concurrency Control，即多版本并发控制

2. MVCC机制具有以下优点：

   • 提高并发性能：读操作不会阻塞写操作，写操作也不会阻塞读操作，有效地提高数据库的并发性能。
   • 降低死锁风险：由于无需使用显式锁来进行并发控制，MVCC可以降低死锁的风险。

3. 隐式字段：MySQL中的行数据，除了我们肉眼能看到的字段之外，其实还包含了一些隐藏字段，它们在内部使用，默认情况下不会显示给用户。

   字段名	含义
   DB_ROW_ID	隐含的自增ID（隐藏主键），用于唯一标识表中的每一行数据，如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引。
   DB_TRX_ID	该字段存储了当前行数据所属的事务ID。每个事务在数据库中都有一个唯一的事务ID。通过 DB_TRX_ID 字段，可以追踪行数据和事务的所属关系。
   DB_ROLL_PTR	该字段存储了回滚指针（Roll Pointer），它指向用于回滚事务的Undo日志记录。

4. Undo Log：

   • 上文提到了 Undo 日志，这个 Undo 日志是 MVCC 能够得以实现的核心所在。

   • Undo日志（Undo Log）是MySQL中的一种重要的事务日志，Undo日志的作用主要有两个方面：

     • 事务回滚：当事务需要回滚时，MySQL可以通过Undo日志中的旧值将数据还原到事务开始之前的状态，保证了事务回滚的一致性。

     • MVCC实现：MVCC 是InnoDB存储引擎的核心特性之一。通过使用Undo日志，MySQL可以为每个事务提供独立的事务视图，使得事务读取数据时能看到一致且符合隔离级别要求的数据版本

5. Read View：

   • 一致性视图，全称 Read View ，是用来判断版本链中的哪个版本对当前事务是可见的

   • Read View 只用于已提交读和可重复读 两个隔离级别，它用于这两个隔离级别的不同点就在于 什么时候生成 Read View。

     • 已提交读：事务每次发起查询的时候，都会重新创建一个新的 Read View。
     • 可重复读：事务开始的时候，创建出 Read View。

   • Read View 有四个重要的字段：

     字段	含义
     creator_trx_id	创建该 Read View 的事务的事务 id
     m_ids	创建 Read View 时，当前数据库中「活跃且未提交」的事务id列表，即启动还未提交的事务
     min_trx_id	创建Read View时当前数据库中活跃且未提交的事务中最小事务的事务 id，也就是 m_ids 的最小值
     max_trx_id	创建 Read View 时当前数据库中应该给下一个事务的id值。这个并不是 m_ids 的最大值，而是是全局事务中最大的事务 id 值 + 1

   • 因此可以根据 trx_id 划分三种情况

     	情况	含义
     记录的 trx_id	小于 min_trx_id	已提交的事务
     记录的 trx_id	在 min_trx_id 和 max_trx_id 之间	已启动但未提交的事务
     记录的 trx_id	大于 max_trx_id	还没有开始的事务

     • 如果是记录的 trx_id 小于 min_trx_id 的情况，表示这个版本的记录是在创建 Read View 前已经提交的事务生成的，所以该版本的记录对当前事务可见。
     • 如果是记录的 trx_id 大于 min_trx_id 的情况，表示这个版本的记录是在创建 Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见。
     • 如果是记录的 trx_id 在 min_trx_id 和 max_trx_id 之间的情况，需要判断 trx_id 是否在 m_ids 列表中：
       1. 如果记录的 trx_id 在 m_ids 列表中，表示生成该版本记录的事务还未提交，所以该版本的记录对当前事务不可见。
       2. 如果记录的 trx_id 不在 m_ids 列表中，表示生成该版本记录的事务已经提交，所以该版本的记录对当前事务可见。

6. 这种通过「版本链」来控制并发事务访问同一个记录时的行为就叫 MVCC（多版本并发控制）。

7. 面试回答：MVCC 是 MySQL InnoDB 引擎用于控制数据并发访问的协议。MVCC 主要是借助于版本链来实现的。在 InnoDB 引擎里面，每一行都有两个额外的列，一个是 trx_id，代表的是修改这一行数据的事务 ID。另外一个是 roll_ptr，代表的是回滚指针。InnoDB 引擎通过回滚指针，将数据的不同版本串联在一起，也就是版本链。这些串联起来的历史版本，被放到了 undolog 里面。当某一个事务发起查询的时候，MVCC 会根据事务的隔离级别来生成不同的 Read View，从而控制事务查询最终得到的结果。

38. MySQL 对可重复读隔离级别幻读问题的处理(MVCC和锁)

参考文章：MySQL 可重复读隔离级别，完全解决幻读了吗？ | 小林coding (xiaolincoding.com)

• 首先需要知道 MySQL 对于这个问题的两种处理方式，分别是MVCC和next-key lock(间隙锁+记录锁)
  • MVCC：见 37
  • next-key lock(间隙锁+记录锁)：这里提到了两个概念，间隙锁和记录锁，简单介绍一下
    • 记录锁：当我们对于唯一性的索引（包括唯一索引和主键索引）使用等值查询，精准匹配到一条记录的时候，这个时候使用的就是记录锁，锁住的是一行数据，防止其他事务对该记录进行修改。
    • 间隙锁：当我们查询的记录不存在，无论是用等值查询还是范围查询的时候，它使用的都是间隙锁，锁住的是多行数据，防止其他事务在该范围内插入新的记录。
    • next-key lock(间隙锁+记录锁)：它是 记录锁 和 间隙锁 的结合，锁定的是某条记录加上它之前的间隙，既防止其他事务修改这条记录，也防止在该记录前面的间隙中插入新数据。
• MySQL 的 InnoDB 引擎的可重复读隔离级别对于不同的查询方式，分别提出了避免幻读的方案：
  • 针对快照读(普通 select 语句)：通过使用 MVCC 的方式解决了幻读。
  • 针对当前读(select … for update 等语句)：通过 next-key lock(间隙锁+记录锁) 的方式解决了幻读。
• 但是，MySQL 可重复读隔离级别并没有彻底解决幻读，只是很大程度上避免了幻读现象的发生。
  • 针对快照读，MVCC 并不能完全避免幻读现象，当事务 A 更新了一条事务 B 插入的记录之后，事务 A 再次查询就能够查到这条记录，发生幻读。
  • 针对当前读，如果事务 A 开启后，并没有直接执行当前读，而是先执行了一条快照读，然后事务 B 执行了一条插入并提交，那么当事务 A 再进行当前读的时候，就会查到这条刚插入的记录，发生幻读。
  • 如果要避免这类问题，就只能尽量在开启事务之后马上执行当前读语句，因为会加 next-key lock 从而避免其他事务插入记录。

39. 为什么有了锁，还需要 MVCC？

参考文章：13｜MVCC协议：MySQL在修改数据的时候，还能不能读到这条数据？.md | Leon406资源站

• 回答：避免读写阻塞。单纯使用锁的时候，并发性能会比较差。即便是在读写锁这种机制下，读和写依旧是互斥的。而数据库是一个性能非常关键的中间件，如果某个线程修改某条数据就让其他线程都不能读这条数据，这种性能损耗是无法接受的。所以 InnoDB 引擎引入了 MVCC，就是为了减少读写阻塞。

40. MySQL 中的锁

参考文章：MySQL 有哪些锁？ | 小林coding (xiaolincoding.com)

1. 全局锁

   • 作用：执行后，整个数据库就处于只读状态了，此时对数据的增删改查和对表结构的修改都会被阻塞

   • 执行命令：

     # 开启全局锁
     flush tables with read lock;
     # 释放全局锁
     unlock tables;

   • 应用场景：全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。

2. 表级锁

   1. 表锁

      • 读锁，也就是共享锁

        • 作用：如果本线程对 A 表加了共享表锁，那么本线程和其他线程接下来如果要对 A 表执行写操作的语句会被阻塞，直到锁被释放

        • 执行命令：

          # 对 test 表加共享锁
          lock tables test read;

      • 写锁，也就是独占锁

        • 作用：如果本线程对 A 表加了独占表锁，那么只有本线程能对 A 表进行读写操作，其他线程对 A 表执行的读写操作会被阻塞，直到锁释放

        • 执行命令：

          # 对 test 表加独占锁
          lock tables test write;

      • 释放表锁的命令：

        # 释放表锁
        unlock tables;

      • 注意：尽量避免在使用 InnoDB 引擎的表使用表锁，因为表锁的颗粒度太大，会影响并发性能。

   2. 元数据锁

      • 作用：MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。

      • 我们不需要显示的使用 MDL，因为当我们对数据库表进行操作时，会自动给这个表加上 MDL：

        • 对一张表进行 CRUD 操作时，加的是 MDL 读锁；

        • 对一张表做结构变更操作的时候，加的是 MDL 写锁；

      • 释放时机：MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。

      • 引发问题：长事务会引发后续申请锁的阻塞。对于长事务，需要注意的是如果线程 A 启用了事务并执行的是读操作但未提交，那么接下来的其他线程的读操作都可以执行，直到下一个其他线程的写操作出现，这时会因为获取不到 MDL 写锁而阻塞，从而获取锁的操作会形成一个队列；又因为队列中写锁获取优先级高于读锁，后续的无论读写操作都会被阻塞在队列里。

      • 解决办法：为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务，是否有事务已经对表加上了 MDL 读锁，如果可以考虑 kill 掉这个长事务，然后再做表结构的变更。

   3. 意向锁

      • 在使用 InnoDB 引擎的表里对某些记录加上 共享锁 之前，需要先在表级别加上一个 意向共享锁；
      • 在使用 InnoDB 引擎的表里对某些记录加上 独占锁 之前，需要先在表级别加上一个 意向独占锁；
      • 而普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。
      • 作用：意向锁的目的是为了快速判断表里是否有记录被加锁。如果没有意向锁，那么在加独占表锁时就需要遍历表里的所有记录，查看是否有记录存在独占锁，这样效率还会很慢。有了意向锁，那么在对记录加独占表锁前，会先加上意向独占锁，只需要判断是否有意向独占锁即可，不用遍历表中的记录，提高效率。

   4. AUTO-INC 锁

      • 作用：表里的主键通常都会设置成自增的，数据库会自动给主键赋值递增的值，这主要是通过 AUTO-INC 锁实现的。
      • 加锁时机：在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉。
      • 释放时机：AUTO-INC 锁是特殊的表锁机制，锁不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放。
      • 产生问题：面对大量数据插入时就会影响插入性能。
      • 解决：在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增。一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。
      • InnoDB 提供了 innodb_autoinc_lock_mode 系统变量用于选择锁，具体选择和会产生的主从不一致问题详情：MySQL 有哪些锁？ | 小林coding (xiaolincoding.com)

3. 行级锁

   • InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。

   • 普通的 select 语句是不会对记录加锁的，因为它属于快照读。但可以使用特殊的锁定读语句：

     //对读取的记录加共享锁
     select ... lock in share mode;
     
     //对读取的记录加独占锁
     select ... for update;

     需要注意的是，以上语句必须在一个事务中才能生效，因外当事务提交了，所就会被释放。

   • 同样的，也分为共享锁(S锁)和独占锁(X锁)，共享锁满足读读共享，读写互斥，独占锁满足写写互斥，读写互斥。

   1. Record Lock(记录锁)

      • 锁住的是一行数据，防止其他事务对该记录进行修改。

   2. Gap Lock(间隙锁)

      • 锁住的是多行数据，当我们查询的记录不存在，无论是用等值查询还是范围查询的时候，它使用的都是间隙锁，防止其他事务在该范围内插入新的记录。

   3. Next-Key Lock(临键锁)

      • 它是 记录锁 和 间隙锁 的结合，锁定的是某条记录加上它之前的间隙，既防止其他事务修改这条记录，也防止在该记录前面的间隙中插入新数据。

   4. 插入意向锁

      • 一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁(即包含在 Next-Key Lock中的)，如果有的话，就会被阻塞，直到拥有间隙锁的事务提交为止(间隙锁释放)。此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。
      • 插入意向锁名字虽然有意向锁，但是它并不是意向锁，它是一种特殊的间隙锁，属于行级别锁。

41. update 的 where 没用到索引列会锁全表吗？

参考文章：update 没加索引会锁全表？ | 小林coding (xiaolincoding.com)

• 是的，确实是这样。如果update 的 where 没用到索引列会发生全表扫描，不仅会给每行记录加上行锁，还会在每行记录之间加上间隙锁，相当于锁住了整个表，直到事务提交才会释放锁。
• 在实际生产中需要注意不要执行这样的语句，会导致业务停滞！

42. MySQL 如何避免死锁？

参考文章：MySQL 死锁了，怎么办？ | 小林coding

1. 设置事务等待锁的超时时间。当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。
2. 开启主动死锁检测。主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认就开启。

43. MySQL日志：undo log、redo log、 binlog

参考：MySQL 日志：undo log、redo log、binlog 有什么用？ | 小林coding

undo log(回滚日志)：

1. undo log 记录的是每一个事务中的所有操作，插入删除记录等，方便事务回滚

2. 是 Innodb 存储引擎层生成的日志，实现了事务的原子性，主要用于事务回滚和 MVCC

redo log(重做日志)：

1. redo log 是物理日志，记录了某个数据页做了什么修改，方便持久化数据
2. 是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于防止断电导致数据丢失等故障恢复
3. InnoDB 使用的 WAL(Write-Ahead Logging) 技术是指：让后台线程将缓存在 Buffer Pool 的脏页刷新到磁盘里。因此 MySQL 的写操作不是直接写到磁盘上，而是先写日志，在合适的时间在写道磁盘上。

binlog(归档日志)：

1. 是 Server 层生成的日志，主要用于数据备份和主从复制

44. Buffer Pool 是什么，有什么用？

1. InnoDB 引擎为了提高查询能力，设计了一个**缓冲池(Buffer Pool)**，来提高数据库的读写性能
2. 当读取数据时，如果数据存在 Buffer Pool 中，就直接读取，否则再去磁盘里查询
3. 当修改数据时，如果数据存在 Buffer Pool 中，直接修改 Buffer Pool 中的数据，并把该页设置为脏页(该页的的内存数据与磁盘上的数据不一致)，后续由后台的线程选择合适的时机将脏页写入磁盘，可以减少磁盘 I/O
4. 在 MySQL 启动时，InnoDB 会为 Buffer Pool 申请一片连续的空间，然后按照默认的 16KB 大小划分出一个个的页，Buffer Pool 中的页就叫做缓存页。但是一开始这些页都是空闲的，后面使用到数据后磁盘上的页才会被写到这些缓存页中，这也是 MySQL 刚启动时申请的虚拟内存很大，但实际使用的物理内存却很小，后面使用到后才会逐渐变大。
5. Buffer Pool 的组成有：索引页、数据页、插入缓存页、undo页、自适应哈希索引、锁信息等
6. 查询一条记录，会将整个页都加载到 Buffer Pool 中

45. undo log 和 redo log 区别

1. redo log 是在事务提交后但是发生崩溃，用于恢复数据
2. undo log 是在事务执行过程中发生崩溃，用于回滚事务
3. 在 redo log 刷新到磁盘之前，都是回滚，用 undo log。
4. 如果 redo log 刷新到了磁盘，那么就是重放 redo log。

46. redo log 刷盘的时机

1. MySQL 正常关闭时
2. 当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时，会触发刷盘
3. InnoDB 的后台线程每隔 1 秒，将 redo log buffer 持久化到磁盘
4. 每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘

47. redo log 和 binlog 有什么区别

1. 使用对象不同：
   • binlog 是 MySQL 的 Server 层的日志，所有存储引擎都可以使用
   • redo log 是 InnoDB 存储引擎实现的日志
2. 写入方式不同
   • binlog 是追加写，写满一个文件就新建一个文件继续写，不会覆盖以前的日志，保存的是全量的日志
   • redo log 是循环写，日志空间大小是固定的，全部写满就从头开，保存未被刷入磁盘的脏页日志
3. 用途不同
   • binlog 用于备份恢复、主从复制
   • redo log 用于掉电等故障恢复

48. 整个数据库的数据被删除了，能使用 redo log 恢复数据吗？

• 不可以，只能用 binlog 恢复
• 因为 redo log 是循环写，是会编写边擦除日志的，只记录未被刷入磁盘的数据的物理日志，已经刷入磁盘的数据都会从 redo log 文件里擦除
• binlog 文件保存的是全量的日志，也就是保存了所有数据变更的情况，理论上只要记录在 binlog 上的数据，都可以恢复，所以如果不小心整个数据库的数据被删除了，得用 binlog 文件恢复数据

49. binlog 什么时候刷盘？

• 事务执行过程中，先把日志写到 binlog cache（Server 层的 cache），事务提交的时候，再把 binlog cache 写到 binlog 文件中。

50. 主从复制是怎么实现的？

• MySQL 的主从复制依赖于 binlog，复制的过程就是将 binlog 中的数据从主库传输到从库上，这个过程一般是异步的，也就是主库上执行事务操作的线程不会等待复制 binlog 的线程同步完成
• 主从复制三个阶段：
  1. 写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据
  2. 同步 Binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中
  3. 回放 Binlog：回放 binlog，并更新存储引擎中的数据

51. 从库是不是越多越好？

• 不是

• 因为从库数量增加，从库连接上来的 I/O 线程也比较多，主库也要创建同样多的 log dump 线程来处理复制的请求，对主库资源消耗比较高，同时还受限于主库的网络带宽

• 所以在实际使用中，一个主库一般跟 2～3 个从库（1 套数据库，1 主 2 从 1 备主），这就是一主多从的 MySQL 集群结构。

52. MySQL 主从复制还有哪些模型？

主要有三种：

• 同步复制：MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回客户端结果。这种方式在实际项目中，基本上没法用，原因有两个：一是性能很差，因为要复制到所有节点才返回响应；二是可用性也很差，主库和所有从库任何一个数据库出问题，都会影响业务。
• 异步复制（默认模型）：MySQL 主库提交事务的线程并不会等待 binlog 同步到各从库，就返回客户端结果。这种模式一旦主库宕机，数据就会发生丢失。
• 半同步复制：MySQL 5.7 版本之后增加的一种复制方式，介于两者之间，事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行，比如一主二从的集群，只要数据成功复制到任意一个从库上，主库的事务线程就可以返回给客户端。这种半同步复制的方式，兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险

53. 为什么需要两阶段提交？

• 因为事务提交后，redo log 和 binlog 都要持久化到磁盘，但是这两个是独立的逻辑，可能出现半成功的状态，这样就造成两份日志之间的逻辑不一致
• 会出现两种情况：
  1. 在将 redo log 刷入到磁盘之后， MySQL 突然宕机了，而 binlog 还没有来得及写入。会导致主库更新了操作，但是从库同步的 binlog 并没有更新操作，导致主从不同步。
  2. 在将 binlog 刷入到磁盘之后， MySQL 突然宕机了，而 redo log 还没有来得及写入。会导致从库更新了操作，但是主库的 redo log 并没有这个更新操作，导致主从不同步。
• MySQL 为了避免出现两份日志之间的逻辑不一致的问题，使用了「两阶段提交」来解决。

54. 两阶段提交是什么？

• 两阶段提交其实是分布式事务一致性协议，它可以保证多个逻辑操作要不全部成功，要不全部失败，不会出现半成功的状态。

• 两阶段提交把单个事务的提交拆分成了 2 个阶段，分别是「准备（Prepare）阶段」和「提交（Commit）阶段」。

• 当客户端执行 commit 语句或者在自动提交的情况下，MySQL 内部开启一个 XA 事务，事务的提交过程有两个阶段，将 redo log 的写入拆成了两个步骤：prepare 和 commit，中间再穿插写入binlog

  • prepare 阶段：将 XID（内部 XA 事务的 ID） 写入到 redo log，同时将 redo log 对应的事务状态设置为 prepare，然后将 redo log 持久化到磁盘
  • commit 阶段：把 XID 写入到 binlog，然后将 binlog 持久化到磁盘，接着调用引擎的提交事务接口，将 redo log 状态设置为 commit，此时该状态并不需要持久化到磁盘，因为只要 binlog 写磁盘成功，就算 redo log 的状态还是 prepare 也没有关系，一样会被认为事务已经执行成功

55. 两阶段提交遇到异常重启

• 在 MySQL 重启后会按顺序扫描 redo log 文件，碰到处于 prepare 状态的 redo log，就拿着 redo log 中的 XID 去 binlog 查看是否存在此 XID：

  • 如果 binlog 中没有当前内部 XA 事务的 XID，说明 redolog 完成刷盘，但是 binlog 还没有刷盘，则回滚事务。

  • 如果 binlog 中有当前内部 XA 事务的 XID，说明 redolog 和 binlog 都已经完成了刷盘，则提交事务。

• 对于处于 prepare 阶段的 redo log，提交和回滚事务取决于是否能在 binlog 中查找到与 redo log 相同的 XID，如果有就提交事务，如果没有就回滚事务。保证了 binlog 和 redo log 的一致性。

• 所以说，两阶段提交是以 binlog 写成功为事务提交成功的标识。