mysql是如何选择索引的（为什么会选错索引）

什么是查询成本

查询成本 = I/O成本 + CPU成本

I/O 成本：将数据从磁盘加载到内存的损耗时间称为 I/O 成本（对 memory 引擎的表除外，使用与磁盘性的存储引擎，例如 myisam，innodb）

CPU成本：读取记录，检测记录是否满足条件，对结果集进行排序等这些操作的损耗时间称为 CPU 成本

对Innodb 存储引擎来说，读取一个数据页花费的成本默认是 1.0；读取记录、检测记录是否满足条件成本默认是 0.2。这里说的 “1.0” “0.2”，在mysql中是可以设置的

注意：在读取记录时，即使不需要检测记录是否满足条件，或者除了读取操作外没有其他操作，其成本也算作 0.2

基于成本的优化过程

1. 根据搜索条件，找出所有可能使用的索引
2. 计算全表扫描的代价
3. 计算使用不同索引执行查询的代价
4. 对比各种执行方案的代价，找出成本最低的那个方案

计算全表扫描的代价

代价由两部分组成

• 聚簇索引占用的页面数
• 表中的记录数

聚簇索引占用的页面数 = Data_length of show table status like '%TableName%' \ 16KB，mysql默认的一个数据页的大小是 16KB

表中的记录数 = Rows of show table status like '%TableName%'

计算使用不同索引执行查询的代价

代价由两部分组成

• 索引的扫描区间数量
• 所有扫描区间需要回表的记录数

注意

• mysql认为 无论某个扫描区间的二级索引到底占用了多少个页面（数据页），查询优化器粗暴的认为读取一个索引的扫描区间的 I/O 成本与读取一个页面的 I/O 成本是相同的。
• mysql认为 一次回表操作的成本与读取一个页面的 I/O 成本是相同的

某个扫描区间，回表的记录数的计算过程（这种方式称为 index dive）

为了方便描述，这里假设扫描区间是 10 < a < 1000，字段 a 是索引

1. 先根据 a > 10 在 a 索引树上找到满足 a > 10 的第一条记录，我们称为“最左记录”
2. 先根据 a > 1000 在 a 索引树上找到满足 a > 1000 的第一条记录，我们称为“最右记录”
3. 如果区间的 最左记录 和 最右记录 所在的页面相隔不是太远（在mysql 5.7.22 版本中，只要相隔不大于 10 个页面即可），会从左到右遍历数据页的Page header 中的 PAGE_N_RECS 属性，就可以精确统计出满足 10 < a < 1000 条件的二级索引记录的条数。如果相隔太远，就沿着 最左记录 向右读 10 个页面，计算每个页面平均包含多少记录，然后用这个平均值乘以 最左记录 和 最右记录 之间的页面数量就可以了（最左记录 和 最右记录 之间的页面如何计算，跟 B+树的数据结构有关的，这个后续再讲）

说明：数据页有一个 page header 部分，page header 中有一个名为 PAGE_N_RECS 的属性，该属性代表了该页面中目前有多少条记录

举例：如何计算查询成本

例子1：sql ：select * from t where a >10 and a < 100，全表记录为 1000 条，字段 a 是表 t 的非唯一二级索引。

计算全表扫描查询成本

show table status like 't'，显示的 Data_length 为 1589248，Rows 为 944

查询成本 = I/O成本 + CPU成本 = 聚簇索引占用的页面数 * 1.0 + 表中的记录数 * 0.2 = 1589248 / 16386 * 1.0 + 944 * 0.2 = 97 + 188.8 = 285.8

计算根据索引 a 进行查询的成本

注意这个例子中，索引 a 的扫描区间只有一个，就是（10，1000）

为了方便计算，这里假设索引 a 的扫描区间 (10，1000)，二级索引需要回表的记录数为 89

这里将查询成本，分成两部分计算，一部分是二级索引的查询成本，一部分是回表操作的成本

二级索引的查询成本 = I/O成本 + CPU成本 = 1.0 + 89 * 0.2 = 18.8

回表操作的查询成本 = I/O成本 + CPU成本 = 89 * 1.0 + 89 * 0.2 = 106.8

总的查询成本为 18.8 + 106.8 = 126.8

例子2：sql ：select * from t where a in (123，456)，全表记录为 1000 条，字段 a 是表 t 的非唯一二级索引。

计算全表扫描查询成本，与例子 1 计算过程一样

计算根据索引 a 进行查询的成本

注意，in 操作，a 索引的扫描区间就是两个，一个是（123），一个是（456）

基于索引统计数据的成本计算

使用 index dive 进行索引查询成本的计算是有条件的，为什么会有条件呢，我们假设一个 select * from t where a in (1,2,3,````)，in中的值有 1000 个，也就是有 1000 个扫描区间。那么就可能导致 mysql 在使用 index dive 进行索引查询成本计算的过程耗损可能比全表扫描的查询成本都高。

对此 mysql 引入了一个参数 eq_range_index_dive_limit 用于控制 index dive 的使用方式。默认值为 200，如果索引的扫描区间个数小于 200 个，使用 index dive 方式，否则使用 索引统计数据的方式 进行成本计算

统计数据是如何统计的（概要）

采用“采样统计”的方式统计，InnoDB 默认会选择 N（innodb_stats_persistent_sample_pages 参数控制 N 的大小，默认值为 20） 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。

在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：

设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10。

设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。

现在我们看看， select * from t where a in (1,2,3,````) 的索引 a 的查询成本计算

假设 通过 show table status 查看表 t 的 Rows 值为 10000

假设 通过 show index from 查看表 t 的 字段 a 的索引基数（Cardinality ）为 987

通过以上两个值，我们可以计算出 索引 a 数据的重复度约等于 10000 / 987 = 10

二级索引的查询成本 = I/O成本 + CPU成本 = 1000 * 1.0 + 1000 * 0.2 = 2400

回表操作的查询成本 = I/O成本 + CPU成本 = 1000 * 10 * 1.0 + 1000 * 10 * 0.2 = 12000

总的查询成本 = 2400 + 12000 = 16000

调节成本常数

server_cost

列说明

cost_name 成本评估的时候用的名字

cost_value 成本评估项的具体值。如果这个值是非NULL的，SQL服务就会使用这个值，否则SQL服务会使用默认值

last_update 该行数据的上次更新时间。

comment 该项评估信息的备注。数据库管理员可以在这一栏写备注，比如为什么要设置这么个值等等。

cost_name 值说明

engine_cost

列说明

cost_name 值说明