开门见山

给定一条SQL语句，会遵照下面的流程

1. 判断该语句涉及的所有索引，即Explain中的possiable keys

• note：使用索引的前提是该索引与常数进行比较。举一个不成立的例子

where k2 > k1，就算k2列建立了索引，这个搜索语句也没能使用k2，因为它不是与常数进行比较

2. 计算全表扫描的成本

3. 计算使用各个索引的成本

4. 综合比较上述方案的成本，使用代价最小的方案进行实际查询

Mysql 搜索成本包括两部分：

• IO成本 前者对应将搜索的数据加载到内存中，因为页是内存与磁盘之间交换的基本单位，MySQL规定从磁盘读取一页的成本记为1
• CPU成本。 后者对应检索一条记录是否符合搜索条件（即读取一条记录），MySQL规定读取一条记录的成本为0.2

单表查询

计算全表扫描的代价

全表扫描的代价包括IO成本以及CPU成本，IO成本对应将聚簇索引整个从磁盘加载到内存的成本，CPU成本对应扫描所有记录的成本。

• 如何查看聚簇索引的大小以及表的记录数呢？

SHOW TABLE STATUS LIKE '表名'

• Rows表示记录数，这只是MySQL的统计信息，并不是确切值

• Data_length表示这个表对应的聚簇索引的大小

综上所述，可以分别计算IO成本与CPU成本：

• IO成本：$(Data\_length/16/1024)·1 + 1.1$

已知聚簇索引的大小，又每个页的大小固定，即可计算出页的数量；另外1.1是微调，MySQl未给出详尽的注释

• CPU成本：$Rows·0.2$

• $Total = IO + CPU$

计算使用索引的代价

拿一个常见的例子来说，二级索引+回表的操作。它的成本如何计算？

二级索引+回表操作

• 搜索条件为 key2 > 10 AND key2 < 1000

二级索引查询的成本：区间范围数和记录扫描数

1、 区间范围对应的成本：区间记录数 * 1

所谓区间范围数，就是扫描区间的个数。本例中只有(10, 1000)也就是一个，它的成本如何计算？MySQL很豪放，一个搜索区间的成本等于从磁盘加载一个页面到内存的成本，即1

2、 二级索引需要读取的记录对应的成本：二级索引需要读取的记录数 * 0.2

记录扫描数，就是所有扫描区间中满足搜索条件的记录个数。

对于一个区间来说，可以通过区间快速定位最左记录和最后记录，然后统计中间的记录数。

对于上面两个步骤，可能遇到对应的两个问题：

• 每个或者个别扫描区间需要扫描的记录数过多 如果最左与最右记录中间的页面少于10个，依次扫描统计记录数；如果最左右记录中间的页面多余10个，只向右扫描10页，得平均每页扫描的记录数，然后用平均数乘以页面数即得总记录数。

如何统计两个记录之间的页数呢？ 简单，如果你对B+索引很熟悉的话。 首先找到记录对应的目录项记录，然后查看两个目录项记录中间有多少个目录项记录，这个数就是两条记录之间的页子数。

• 扫描区间多 如果你的搜索区间很多，没准最后的结果是：搞不好计算这些单点区间对应的索引记录条数的成本比直接全表扫描的成本都大。

MySQL当然没有这么蠢，如果搜索区间过多，就会采用基于统计数据的成本估算。不过搜索区间到底多少算是多呢？详见eq_range_index_dive_limit变量的数值，超过这个数值，就不会巴巴的dive 到index实际计算各个区间的记录数。

综上：二级索引回表查询，如果查询区间过多、或者个别区间过大，MySQL都不会老老实实的实际加载页面式的统计，而是会基于统计数据的估计查询成本。

3、回表中聚簇索引的成本：加载记录所在的页和记录扫描数

通过第二步，得到二级索引需要读取的记录数N。在这一步，进行回表。MySQL规定，每条记录回表的IO操作就是1，即与将一个页从磁盘加载到内存中的成本相等。也就是说，MySQL依然很豪放，每次回表就都相当于加载磁盘一次，不管记录是否都在一个磁盘页中。然后从页定位记录的具体位置消耗时间不做计算，最后扫描这条记录是否符合搜索条件（二级索引条件之外的条件）。

3.1. 加载记录所在页的成本：回表的记录数 * 1

3.2. 记录扫描数的成本：回表的记录数 * 0.2

• 总成本：二级索引+回表聚簇索引的成本

• I/O成本：区间数 · 1 + 二级索引读取的记录数 · 1 （范围成本+回表加载磁盘成本）

• CPU成本：0.2 ·二级索引读取的记录数 + 0.2 · 二级索引读取的记录数（读取二级索引记录的成本 + 读取并检测回表后聚簇索引记录的成本）

连接查询

MySQL连接查询采用的是嵌套循环连接算法，驱动表会被访问一次，被驱动表可能会被访问多次，到底访问几次，由驱动表扇出的记录决定，这个也叫扇出数。

所以总结一下：两表连接查询的成本由以下两个部分组成：

1. 驱动表的单表查询成本

2. 扇出数*被驱动表的单表查询成本

单表查询的成本详见上一节的分析，所以为了优化连接查询的成本，可以有针对从以下两个方面进行：

1. 尽量减少扇出数

2. 尽量减少被驱动表的单表查询成本（如果可以，被驱动表的连接列最好使用主键或者唯一二级索引，ref的方式进行查询，那时相当的块啊~）

模拟面试问答

面：MySQL如何决定一条查询使用哪个索引？

我：优化器会解析查询语句，判断会所有可能用到的索引。然后依次统计使用每个索引以及全表扫描进行查询成本，最后选择成本最低的那个方案进行实际查询。

面：成本如何统计？

我：成本分为I/O成本，就是从磁盘加载数据也到buffer pool，MySQL默认该成本为1；CPU成本，就是扫描每条数据（判断是否符合where条件）的成本，MySQL默认该成本为0.2。对于全表扫描来说，通过统计信息获取数据页的个数以及记录条数，通过 N_leafs + N_rows * 0.2 得到全表扫描的成本。其他索引方案...按照上面的讲解

面：好，那这些统计信息是如何得来的？MySQL如何做信息的统计？

我：等下一篇文章...

参考资料

• MySQL是如何运行的
• MySQL官方文档