MySQL 查询优化原理

Question

当我们向 MySQL 服务器发送一个 SQL 语句之后，服务器在真正执行前会对这条语句进行一系列操作，其中就包括查询优化，也就是选择这条语句的执行方案。这篇文章就来解释一下查询优化的原理。阅读这篇文章需要你已经掌握 InnoDB 索引的原理。

找出所有可能的执行方案

同一个查询语句可以有不同的执行方案，而 MySQL 的查询优化器会找出执行该语句所有可能使用的执行方案，找到其中成本最低的方案，这个成本最低的方案就是所谓的 执行计划 ，之后才会真正的执行查询。其中，所有可能使用的执行方案包括：

1. 全表扫描
2. 找出查询语句中所有可能使用的索引，使用不同索引进行查询

执行方案的成本计算

接下来就是计算所有可能的执行方案的成本然后进行比较。主要需要考虑的有两种成本：

1. IO 成本：从磁盘中加载数据页需要的成本。MySQL 中规定了成本常数，读取一个页的成本默认是1
2. CPU 成本：读取记录、将记录与条件进行比较、排序等操作都算作 CPU 成本，一条记录的读取或比较成本是0.2

接下来看一下成本是如何计算出来的

全表扫描

首先计算全表扫描的 IO 成本，全表扫描访问聚簇索引，因此要计算 IO 成本就需要估算出聚簇索引占用的页面数。这个信息可以从表的 统计信息 中得到，里面有一个 Data_length 字段表示聚簇索引的存储空间大小，用 Data_length 除以 MySQL 默认 16KB 的页面大小，就能得到聚簇索引占用的页面数了。用这个数乘以成本常数1再加上微调（忽略就行），就是 MySQL 计算的 IO 成本。

索引+回表

如果通过索引的方式来执行查询，那么一般来说需要计算下面这些成本：根据索引列的查询条件通过二级索引定位主键ID、对这些主键ID进行回表、回表得到的记录用其他查询条件进行过滤。下面分别看这些操作的成本是如何估算的。

1、根据索引列的查询条件定位主键ID

这步操作又根据查询条件的不同分为几种情况：

• 查询条件是范围查询，比如 key2 > 10 AND key2 < 100

IO 成本的计算比较简单，MySQL 认为一个范围区间的IO成本就是1。而计算CPU成本则需要估算出这个范围区间大概包含多少条记录，估算的方式是， 第一步 ，先找到第一条区间最左记录，再找到最后一条区间最右记录，这两个操作走的是索引所以都很快，性能消耗可以忽略不计。 第二步 估算出区间最左和区间最右记录之间相隔了多少个页面（根据B+树的结构，只需要看父节点对应的目录项之间隔着几条记录）。 第三步 ，看区间最左记录和区间最右记录之间如果相隔的页面数不大于某个值，则精确统计出区间范围内的记录条数；否则，沿着区间最左记录向右读10个页面，计算平均每个页中包含的记录条数，然后乘以相隔的页面数。得到记录条数之后，乘以CPU成本常数0.2就得到了CPU成本。

• 索引合并（略）

• 使用 IN 进行查询并且参数个数小于某个默认值（系统变量 eq_range_index_dive_limit ）

使用 IN 的查询条件可以看作很多个单点区间，比如 IN (111, 222) ，可以看作单点区间 [111, 111] 和 [222, 222] 。每个区间的IO成本和CPU成本如何计算上面已经说过了，计算出来之后将所有的单点区间的成本加起来就行。

• 使用 IN 进行查询并且参数个数大于某个默认值

显然如果IN中包含的参数太多，仅仅按照上面那样估算成本就会浪费掉很多时间，因此这时会直接使用统计数据来估算成本，MySQL 的索引也维护了统计数据，其中我们需要用到的就是 Cardinality 字段，表示索引列中不重复值的个数。用一条表中的总记录数（从表的统计数据中得到），除以 Cardinality 这个数，就能得到索引列单个值的平均重复次数了。用平均重复次数乘以IN中包含参数的数量，就能大概估计出记录条数。

2、回表

在上一步操作中已经估算出了记录条数，而回表可以看作是随机 IO，因此 MySQL 把每条记录的 IO 成本都看作是访问一个页面的成本，所以回表操作的IO成本就是记录条数乘以IO成本常数。

3、回表后的记录用其他条件过滤

这一步只考虑 CPU 成本，直接用之前预估的记录条数乘以 CPU 成本常数

表连接的成本计算

简单说一下查询语句里包含连接的话是如何计算成本的。表连接时，驱动表只需要访问一次，而被驱动表则可能访问多次（根据对驱动表查询得到的结果集中的记录数而定，这个数字也是估算，比较复杂）。因此连接查询的成本就是驱动表访问一次的成本加上被驱动表访问多次的成本。

对于内连接，驱动表和被驱动表的位置是可以互换的，而不同的表作为驱动表的查询成本可能是不同的，因此 MySQL 查询优化器会分别计算两种情况下的查询成本，然后选取成本更低的方案。

InnoDB 中表行数是如何统计的

上面说到，在计算查询成本时，有可能会使用到统计数据来估算，所以这里再简单说一下表中记录条数 n_rows 这个统计数据是怎么来的，为什么说它只是一个估计值。

在统计 n_rows 的时候，先按照一定的算法选取几个聚簇索引的叶子结点页面，计算出平均一个页面中的主键值的记录数量，然后用这个平均值乘以全部叶子结点的数量就是 n_rows 值。其中，采样的时候选取的叶子节点数量是可以设置的，越大则越精确，但耗时也更长。

统计数据的更新是有一定时机的，默认是 innodb_stats_auto_recalc 功能，当对某张表增删改的记录条数超过了表大小的10%时，就会异步重新进行一次统计数据的计算。