数据库温故：Mysql底层原理起底

0、前言

SQL优化调优是体现程序员分析归纳能力的有效手段，虽然我们不是DBA，但是编码开发时也会涉及许多对数据库的CRUD需求。因此，通过理解Mysql数据库的底层原理，对我们的笔试面试，还有提高业务编码水平是有好处的。

1、Mysql 的底层结构

大体来说，MySQL 可以分为 Server 层 和 存储引擎层两部分：

• Server 层包括连接器、查询缓存、分析器、优化器、执行器等。
• 存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。

现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

下面是Mysql的架构图：

数据库的Server 层包括：连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

1、连接器：连接器负责跟客户端建立连接、获取权限、维持和管理连接。

数据库长连接：指连接成功后，如果客户端持续有请求，则一直使用同一个连接。 数据库短连接：则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。 

2、查询缓存：MySQL 拿到一个查询请求后，会先到查询缓存。

但是大多数情况下我会建议你不要使用查询缓存，为什么呢？因为查询缓存往往弊大于利（查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了）。

3、分析器：MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析。

4、优化器：优化器是在表里面有多个索引的时候，决定使用哪个索引。

5、执行器：通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。

数据库的存储引擎层：负责数据的存储和提取，其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。

2、Mysql 日志系统&两阶段提交

RedoLog 和 BinLog 区别

1、 redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

2、 redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。

3、redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

mysql的两阶段提交原理

阶段1：InnoDB redo log 写盘（引擎层），InnoDB 事务进入 prepare 状态；

阶段2：如果前面prepare成功，binlog 写盘（Server层），那么再继续将事务日志持久化到binlog，如果持久化成功，那么 InnoDB 事务 则进入 commit 状态(实际是在redo log里面写上一个commit记录)；

好处：

两阶段提交是跨系统维持数据逻辑一致性时常用的一个方案，这个方案也同时解决磁盘IO的性能。

3、Mysql 的事务隔离

数据库的事务：就是要保证一组数据库操作，要么全部成功，要么全部失败。

数据库执行引擎中的 InnoDB 支持事务，MySQL 原生的 MyISAM 引擎就不支持事务，这也是 MyISAM 被 InnoDB 取代的重要原因之一。

事务隔离特性

ACID：表示原子性（atomicity）、一致性（consistency）、隔离性（isolation）和持久性（durability）。

多事务并发执行问题

脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）。

事务隔离级别

读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。

事务隔离真理：隔离级别越高，数据库效率越低。

很多时候要在二者之间寻找一个平衡（跟代码加了重量级锁同理：利用 synchronized 锁住整个方法，效率比锁住方法的某个代码块是要低很多的）。

对于事务最重要的便是隔离级别了，但每种隔离级别都不是十全十美的，相对应的会引发匹配的问题，下面逐一解析。

3.1 读未提交

读未提交是指：一个事务还没提交时，它做的变更就能被别的事务看到。(隔离性差，会出现脏读情况）

问题：产生了“脏读”数据，另一个未提交的事务，影响了数据居然也被读取到了。解决办法是设置隔离级别为“读提交”。

3.2 读提交

读提交是指：一个别的事务提交之后，它做的变更才会被别的事务看到。（oracle默认，隔离性还行，会出现不可重复读情况）

• 好处：解决了“脏读”问题。
• 问题：产生了“不可重复读”问题。

但正因为可以“及时的”读取到别的事务提交结果，出现了一个事务范围内同一个事务，但两个相同的查询却返回了不同数据，这就是不可重复读。

解决办法就是设置隔离级别为“可重复读”。解决不可重复读就需要锁行了。

3.3 可重复读

可重复读是指，一个当前事务执行过程中看到的数据，总是跟这个当前事务在启动时看到的数据是一致的。

当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。（Mysql默认）

• 好处：解决了“不可重复读”问题。

• 问题：产生了“幻读”问题。

可重复读：

1. 在开始读取数据（事务开启）时，update操作可以被隔离
2. 但是insert/delete操作除外，这会出现一个新问题(幻读)。解决幻读就需要锁表了。

如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。 但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会 发现莫名其妙多了一条之前没有的数据。

3.4 串行化

串行化：顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。

当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

• 好处：解决了“幻读”问题，因为可重复读是行级锁，而Serializable是表级锁，把整张表锁住了。
• 问题：性能大打折扣，不推荐。

总结：存在即合理，每种隔离级别都有自己的使用场景，你要根据自己的业务情况来定。

4、Mysql 的锁

数据库锁出现的原因是为了处理并发问题，因为数据库是一个多用户共享的资源，当出现并发的时候，就会导致出现各种各样奇怪的问题，就像程序代码一样，出现多线程并发的时候，如果不做特殊控制的话，就会出现意外的事情，比如“脏“数据、修改丢失等问题。

从程序员的角度，锁可以分为：“乐观锁” 和 “悲观锁”。

4.1 乐观锁

乐观锁：程序员设计表结构加字段&更新操作进行判断是否字段数值一直。

1. 加上version的版本控制（后续进行更新操作，先比较version是否一致，不一致就需要重新取一遍数据）
2. 也可以使用timestamp的控制（此处的时间戳应该使用数据库的而非业务系统的）。

4.2 悲观锁

悲观锁：（由系统提供的）表锁、行锁、间隙锁等。

1. 表锁(Table Lock)：对整个表进行加锁。
2. 行锁(Record Lock)：对索引记录加锁。
3. 间隙锁(Gap Lock)：锁住整个区间，包括：区间里具体的索引记录，不存在的空闲空间（可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引记录之后的空间）。
4. next-key锁：行锁和间隙锁组合起来。

针对悲观锁，其实日常的很多sql，我们已经不知不觉的使用到了：

4.2.1 行锁

对主键或者唯一索引进行增删改或显示的加锁，InnoDB会加行锁。

4.2.2 间隙锁

间隙锁的唯一目的就是阻止其他事务插入到间隙中。

一个表有id为1，2，3，5，6，9行数据，执行如下sql语句：

InnoDB不仅会锁住id为5和6两行的数据，也会锁住id为4(虽然该行并不存在)的纪录。

间隙锁的目的是为了防止幻读，其主要通过两个方面实现这个目的：  （1）防止间隙内有新数据被插入  （2）防止已存在的数据，更新成间隙内的数据

4.2.2 next-key lock

next-key lock=行锁+间隙锁；如果一个事务在记录R上的某个索引有共享/互斥锁,也会对其前面一个范围加锁。

锁定的区域：根据索引会形成一个个左开右闭的一个区间，根据查询的条件其所在的区间，并且包括其后的区间。

5、Mysql 的索引模型与应用（InnoDB）

索引目的：为了提高数据查询效率（减少磁盘查询次数）。

索引模型：

• 在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表
• Mysql的InnoDB使用的是B+ 树索引结构（当然也提供了Hash索引结构，但是使用的非常非常少）

5.1 Hash索引

哈希索引的示意图：

Hash索引的不足： 不支持联合索引，不支持排序和分组，不支持范围查询，甚至大规模数据情况下，会带来哈希冲突问题。

5.2 B+树索引

B+树索引数据结构见下图：

简要概述：

• B+树-采用平衡树结构，每个叶子节点到根的路径长度都相同
• B+树-叶子节点按键值大小顺序，放在同一层的叶子节点上

5.2.1 聚簇索引和非聚簇索引

主键索引：主键索引树，叶子结点包含整条数据内容。

非主键索引：非主键索引树，叶子结点仅包含数据的ID内容，因此需要回表获取更全面的数据（查询多一次主键树）。

6、Mysql的索引规范

下面总结一些索引使用的法则。

6.1 不推荐使用索引的场景

1. 表记录太少；
2. 数据重复且分布平均的字段（只有很少数据值字段或者枚举字段）；
3. 经常插入、删除、修改的表要减少索引；
4. text，image等类型不应该建立索引，这些列的数据量大（假如text前10个字符唯一，也可以对text前10个字符建立索引）；
5. MySQL能估计出全表扫描比使用索引更快时，不使用索引；

6.2 联合索引优于单索引的场景

1. 由于MySQL每次查询只使用一个索引。与其说是“数据库查询只能用到一个索引”，倒不如说，和全表扫描比起来，去分析两个索引B+树更加耗费时间。
2. 所以where A=a and B=b这种查询使用（A，B）的组合索引最佳，B+树根据（A，B）来排序。

6.3 推荐使用索引的场景

1. 主键，unique字段；
2. 联表的字段需要加索引；
3. 在where里使用＞，≥，＝，＜，≤，is null和between等字段；
4. 使用不以通配符开始的like，where A like 'China%'；
5. 聚集函数MIN()，MAX()中的字段；
6. order by和group by字段；

6.4 索引失效场景

1. 组合索引未使用最左前缀，例如组合索引（A，B），where B=b不会使用索引；
2. like未使用最左前缀，where A like '%China'；
3. 搜索一个索引而在另一个索引上做order by，where A=a order by B，（一个失败一个成功）只使用A上的索引，因为查询只使用一个索引 ；
4. or 可能使第一个索引失效。如果查询字段相同，也可以使用索引。例如where A=a1 or A=a2（生效），where A=a or B=b（失效）。index merge 技术可以优化这个问题。
5. 如果列类型是字符串，要使用引号。例如where A='China'，否则索引失效（会进行类型转换），见下图：

第1条sql 走索引，第2条sql则是全表扫描，非常慢。 6. 在索引列上进行计算、函数、手动或自动的类型转换会导致索引失效；见下图：

6.5 索引设计原则

1. 第一个索引原则：尽量使用主键索引原则。（主键索引是聚簇索引，叶子结点保留完整数据；非聚簇索引需要回表，多查一次B+树）
2. 第二个索引原则：控制查询字段（ID替代通配符*），覆盖索引的手段，能够大幅度提升性能。（只查询ID不用回表）
3. 第三个索引原则：最左前缀原则。（索引复用，索引项是按照索引定义里面出现的字段顺序排序的）
4. 第四个索引原则：联合索引，索引顺序往往就是需要优先考虑采用的。
5. 第五个索引原则：索引下推原则。（在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数）

7、总结

我们总结一下，上文主要归纳总结了Mysql的主要核心概念：日志系统&隔离级别&索引模型&索引使用经验等。

如果想学习的更加深入，推荐一门很好的课程给大家：《MySQL精讲45讲》，丁奇大大的优秀课程。

参考文章：

• https://blog.csdn.net/weixin_39420024/article/details/80040549
• https://www.cnblogs.com/zhangtianle/p/7474554.html
• MySQL精讲45讲-丁奇

我正在参与 腾讯云开发者社区数据库专题有奖征文。