MySQL 之前已经接触过非常多了，只是将MySQL当作一个 excel 文档，需要取数据了通过命令获取。 但是实际上 MySQL 内部还是有很多奥秘的，一条语句的执行也有很多有意思的设计逻辑。

下面对自己学习的MySQL的执行过程做一个学习记录。通过把MySQL 拆解一下，来对MySQL做更深层次的理解。再遇到一些异常或者问题时，可以直戳本质，更为快速定位并解决问题。

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

Server 层包括 连接器、查询缓存、分析器、优化器、执行器等，涵盖了 MySQL 大多数核心服务功能，以及所有的内置函数，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、试图等等。

存储引擎负责的是数据的存储和提取，其架构是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎，最常用的存储引擎就是 InnoDB 。

由于查询缓存的失效比较频繁，弊端多多，MySQL 8.0 版本直接将查询缓存的整块功能删掉了。

什么是索引

提到索引肯定不会陌生，一般我们遇到查询慢的时候，第一反应就是加索引了吗？索引就像是书的目录一样，快速找到想要的内容。

索引其本质就是快速找到自己想要数据入口的方式，常见的索引的数据结构有 哈希表、有序数组和搜索树。

MySQL 的索引是 B+ 树实现的

• 哈希表

哈希表是一种以 键-值 存储的数据结构。哈希表的思路很简单，把值放到数组中。Key 是哈希函数算出确定的位置，Value 就是数组的下标Index。

但哈希的方式有个明显的缺陷，当我需要的查询语句如下时。

SELECT * FROM table WHERE number > 10 and number < 100

这就需要全表扫描了。所以 哈希表这种结构只适用于等值查询的场景。

• 有序数组

有序数组解决等值查询和范围查询都很优秀。但问题就是有序数组不适合内容的频繁更新，如中间插入一个记录时必须挪动后面的全部记录，所以有序数组只适合静态存储引擎

• 二叉搜索树

二叉搜索树的特点是左子树小于父节点，而右子树大于父节点。其查询复杂度是 O(log(N)) ，且其更新的时间复杂度也是 O(log(N)) 。

实际上，因为索引大多时候是保存在磁盘上，而N叉树由于在读写上的性能优点，以及适配磁盘的访问模式，是最广泛被应用的数据库引擎。

在 MySQL 中，索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同的存储引擎的索引工作方式并不一样，其底层实现也不一样。而当前最受欢迎的 InnoDB 所使用的是 B+ 树。

InnoDB 索引模型

在 InnoDB 中，表是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。InnoDB 使用的是 B+ 树索引模型，所以数据都是存储在 B+ 树中的。

每个索引在InnoDB中都是对应一个 B+ 树

一个简单的例子，假设有一个主键列为 id 的表和字段 k，并且 k 上有索引。

1
2
3
4
5

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

如果我们插入了 6 组数据 （1,100）、（2,200）、（3,300）、（4,400）、（5,500）、（6,600）

其本质是建立了两个索引，如下图

上图本质上是个 B+ 树，只不过这里被我画成了数组方便理解。可以看出根据叶子节点的内容，索引类型分为主键索引和普通索引。

• 主键索引的叶子节点存的是正行数据记录（Record）。在 InnoDB 中，主键索引也被称为聚簇索引（clustered index）

• 非主键索引的叶子节点存的是主键的值。在 InnoDB 中，非主键索引被称为二级索引（secondary index）

基于主键索引的查找是最直接的，所以主键查询最快，效率最高。

比如 SELECT * FROM T WHERE ID=1 ，即主键查询的方式，只需要搜索 ID 这棵 B+ 树

而 SELECT * FROM T WHERE k=100 ，是先通过搜索K的索引树，找到k=100时的主键是1，再到 ID 索引树搜索一次，这个过程称为回表。

也就是说，基于非主键索引的查询需要多扫描一颗索引树。

索引维护

索引的维护这里我们讨论几个问题。

主键索引应该怎么选？

先说答案：最好是自增ID

1. 如果业务逻辑的字段做主键，不能保证有序插入，自增ID可以有效保证递增插入效率。每次插入一条新记录，都是追加操作，不涉及挪动其他记录，也不会触发叶子节点的分裂。
2. 从存储空间的角度讲，二级索引存放的是主键索引，如果用身份证号这种数据当作主键索引，那么二级索引需要多出很多空间来存放18位的身份证号。而长整型（bigint）也就8个字节。主键长度越小，二级索引的叶子节点就越小，二级索引所占的空间也就越小

为什么要重建索引？

直接说答案：索引可能因为删除或者页分裂等原因导致数据页有空洞，B+ 树高度虚高。

重建索引的过程会创建一个新的索引，把数据顺序插入，这样的页面利用率最高，也就是索引更紧凑、更省空间。

但是！！ 重建主键索引是不合理的，无论是删除主键还是创建主键，都会将整个表重建。

索引优化

用好索引可以让查询速度成倍数的提高性能，用不好则劳民伤财。

覆盖索引

还是上面那张表 T （id 为主键索引，k为二级索引），我们执行语句 SELECT ID FROM T WHERE k BETWEEN 300 AND 500 ，这时因为要查找的就是 ID 的值，而ID的值已经在 K 的索引树上了，可以直接提供查询结果，不需要回表。这种情况称之为覆盖索引。

举个例子🌰：

假设现在设计了一个市民信息登记表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`),
  KEY `id_card_name` (`id_card`,`name`)
) ENGINE=InnoDB

我们需要根据身份证号查询市民信息的需求，我们在身份证号上建立了一个索引。

如果我们现在的高频需求是根据 身份证号查姓名，那么建立一个 （身份证号、姓名）的联合索引是十分有必要的。这个高频请求可以用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。

最左前缀原则

还是上面市民信息登记表这个例子。

如 B+ 树索引这种结构，可以利用索引的 “最左前缀” 来定位记录。

假设（name，age） 这个联合索引

索引在 B+ 树里其实也是按照索引定义出现的字段顺序排序的。那么在查找名字为“张三”的人时，就可以快速定位到 id4 。然后向后遍历得到所有需要的结果。

即便SQL语句是 “WHERE NAME LIKE ‘张%'” ，也可以利用到最左前缀的优化。

所以在建立联合索引的时候，需要合理安排索引内字段的顺序。

同理。因为支持了最左前缀，当已经有了 （a,b）这个联合索引后，一般就不需要再单独给 a 建立索引了。因此就可以通过调整顺序，少维护一个索引。

如果只有 b 的查询，那么是无法使用 （a,b）这个联合索引的，这时可以再维护一个 （b）的普通索引。

索引下推

假设这样一个查询语句

SELECT \* FROM tuser WHERE name LIKE '张%' AND age=23 AND ismale=1

这里知道了前缀索引的匹配规则，找到了 ID3。但是在 MySQL 5.6 之前，只能从 ID3 开始一个一个回表。到主键上再找到具体的行数。再对比字段值。

如下图所示，这里故意去掉了 age 的值因为 InnoDB不会去看 age 的值。只是按顺序把“name”第一个字是“张”的记录一条条取出来再回表。因此，需要回表4次。

MySQL5.6 之后引入的索引下推优化（index condition pushdown），可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

如下图所示，InnoDB在（name,age）索引内部就判断了 age 是否等于 23。对于不等于的就直接跳过了，减少了3次回表。

磁盘IO顺序访问

这个看似和索引优化没有关系，其实是有关联的，索引建立的好，就可以利用磁盘IO顺序访问的特性，加速数据删改等相关操作。没错这里指的是索引对数据的删改的影响。

为什么数据删改的性能还和索引有关系呢？

先引入一个概念：change buffer。

当需要更新一个数据页时，如果数据页在内存中，就直接更新。如果数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB会将更新操作缓存在 change buffer 中，在下次查询访问这个数据时，将内存页读入内存，然后执行 change buffer 中与这个页相关的操作。

需要说明的是，虽然名字叫作 change buffer ，实际上它是可以持久化的数据，也就是 change buffer 在内存中有拷贝，也会被写入到磁盘。

将 change buffer 中的操作应用到原始数据页，得到新结果的过程叫做 merge，除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。数据库正常关闭（shutdown）的过程，也会执行 merge 操作。

显然。如果能够将跟新语句先记录到 change buffer 上，就可以减少磁盘读。尤其是机械磁盘，减少了随机读磁盘的 IO 消耗。具体可参考知乎-机械磁盘的随机IO慢的超乎想象

这里说个题外话，InnoDB 使用 B+树当索引，而不是其他树的原因是因为B+树的节点更大，IO起来会让磁盘工作的更舒服一些。

好了，现在可以说为什么索引选择会影响 数据更新了：

对于 唯一索引 来说，所有的更新操作都需要先判断这个操作是否违反了唯一性约束。比如，要插入 （4,400）这样一个记录，就要先判断表中是否已经存在 k=4 的记录，而这必须将数据页读入内存才能判断。但是已经读入内存，那更新内存会更快，就没必要用 change buffer 了。

这就是如果索引选择是 唯一索引 和 普通索引的区别。一条常规的更新语句。如果这个要修改的目标页不在内存中，此时，InnoDB 的处理流程如下：

• 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束。
• 对于普通索引来说，则是将更新记录在 change buffer 中，语句执行结束。

对于写多读少的业务来说，页面在写完以后被马上访问到的概率较小，此时的 change buffer 的使用效果最好。这种业务模型常见的是帐单类、日志类的数据存储。

对于更新完毕立马就会做查询的业务。那么即使满足了条件，将更新先记录到 change buffer 中，但是之后由于立马又要访问这个数据页，立即出发 merge 过程。这样的随机访问 IO 的次数不会减少，反而会增加了 change buffer 的维护代价。所以对于这种业务来说，change buffer 是无意义的。

唯一索引和普通索引查询能力上没有差别，考虑到更新性能，优先选普通索引。

前缀索引

这里的前缀索引不再是优化查询速度或者更新速度了，而是优化存储的空间。

没错，索引是可以只对一个字段的部分来加索引的。如邮箱地址，如果尾坠都一样的话，我们只需要对名字部分加索引。

1
2
3

mysql> alter table SUser add index index1(email);
或
mysql> alter table SUser add index index2(email(6));

第二条语句就是只对前6个字符添加索引。

但是我们知道索引的区分度越高，意味着重复的键值越少，索引的效果就越好，回表次数也会降低，那么使用前缀肯定会损失部分区分度。比如我们定的是前6个字符，但是 “zhangsan” 和 “zhangsanfeng” 的前留个字符都是 “zhansa” 这就区分度不够了。可以使用下面的语句统计一下前缀长度和可能损失的区分度。

1
2
3
4
5
6

mysql> select 
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7,
from SUser;

索引选择原理

在 MySQL 中选择索引是优化器的工作，我们在查询前会有优化器根据 SQL 语句选择一个最佳的索引进行扫描，用最小的代价去执行语句，但是MySQL 也有选错索引的时候。

优化器选择索引的主要原则是扫描行数越少，意味着访问磁盘数据的次数越少，消耗CPU的资源越少。当然还会结合是否使用临时表、是否排序等因素进行综合判断。

扫描行数如何判断

MySQL 在真正开始执行之前，并不能精确地知道满足该条件的记录有多少个，而只能根据统计信息来估算记录数。

而这个统计信息就是索引的“区分度”，显然，一个索引上不同的值越多，那么这个索引的区分度就越好，说明不需要来回的回表。而一个索引上不同值的个数，成为“基数”(cardinality)。也就是这个基数越大，索引的区分度越好。

那么，基数是怎样来的呢? MySQL 采取了采样统计的方法。InnoDB 默认选择 N 个数据页，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

以下面的表为例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- 新建一个 t 表
CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB;

-- 存储过程，插入10w条数据
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

按理，a 和 b 两个索引的基数都是 10W，但是我们执行 show index 发现，2个索引的基数不仅都不是10w，而且 a 和 b 索引的个数还不相等（分别是 92956 和 92735）。

1
2
3
4
5
6
7
8

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t     | 0          | PRIMARY  | 1            | id          | A         | 94472       | <null>   | <null> |      | BTREE      |         |               | YES     | <null>     |
| t     | 1          | a        | 1            | a           | A         | 92956       | <null>   | <null> | YES  | BTREE      |         |               | YES     | <null>     |
| t     | 1          | b        | 1            | b           | A         | 92735       | <null>   | <null> | YES  | BTREE      |         |               | YES     | <null>     |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

这正是上面提到的基数采样统计，但是数据表是会持续更新的，索引统计信息也不会一直固定不变，当变更的行数超过 1/M 的时候，就会触发一次索引的统计。

M 和 N 是 MySQL 内置的，比如持久化存储时，这时的 N 是20，M是10

排序对索引选择的影响

上面说到，除了扫描行数以外，还有其他的一些影响优化器索引选择的因素。这里就需要说到排序了。

以一个栗子 🌰 来说明。

还是上面的那张表。此时我们输入下面这条查询语句。

SELECT * FROM t WHERE (a BETWEEN 1 AND 1000) AND (b bewteen 50000 and 100000) ORDER BY b LIMIT 1

我们从筛选条件上来看，肯定是查不到符合条件的记录了。

• 如果选 a 索引，那么需要扫描 1000 行，回表到主键再拿b字段来过滤（需要1000行）
• 如果选 b 索引，那么需要扫描 50000行，回表到主键，再那a字段过滤（需要扫描 50000行）

显然索引 a 更划算。但是我们通过 EXPLAIN 命令来看看。

1
2
3
4
5
6

> mysql explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
+----+-------------+-------+------------+-------+---------------+-----+---------+--------+-------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key | key_len | ref    | rows  | filtered | Extra                              |
+----+-------------+-------+------------+-------+---------------+-----+---------+--------+-------+----------+------------------------------------+
| 1  | SIMPLE      | t     | <null>     | range | a,b           | b   | 5       | <null> | 47236 | 1.06     | Using index condition; Using where |
+----+-------------+-------+------------+-------+---------------+-----+---------+--------+-------+----------+------------------------------------+

MySQL 竟然选择了 b 索引。这是因为优化器认为使用索引b可以避免排序（b本身是索引，已经是有序的了，如果选择索引b的话，就不需要再做一次 order by b 排序了，直接遍历即可），所以即使扫描行数多，但是MySQL认为也是值得的。

如何使MySQL正确使用索引呢?

1. 可以强制选择一个索引

SELECT * FROM t FORCE INDEX(a) WHERE a BETWEEN 1000 AND 2000

2. 如果是扫描行不准确的话，则可以重新计算索引

ANALYZE TABLE t

3. 修改SQL语句，引导MySQL使用我们期望的索引。

如上面的例子， ORDER BY b LIMIT 1 改成 ORDER BY b,a LIMIT 1 ，这里的语意是一致的，但是这意味着两个索引都需要排序，因此扫描行成了影响决策的主要条件，于是优化器选了只需要扫描行数少的a。