基于索引统计数据的成本计算 - 《MySQL 是怎样运行的》

index dive就是直接利用索引对应的B+树来计算某个范围区间对应的记录条数.

确定区间最左记录确定区间最右记录计算之间记录数 (少的花精确计算, 多的话估算).

eq_range_index_dive_limit 系统变量:

mysql> SHOW VARIABLES LIKE '%dive%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| eq_range_index_dive_limit | 200   |
+---------------------------+-------+
1 row in set (0.08 sec)

也就是说如果我们的IN语句中的参数个数小于200个的话，将使用index dive的方式计算各个单点区间对应的记录条数，如果大于或等于200个的话，可就不能使用index dive了，要使用所谓的索引统计数据来进行估算。

查看一下single_table的各个索引的统计数据:

mysql> SHOW INDEX FROM single_table;
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| single_table |          0 | PRIMARY      |            1 | id          | A         |       9693  |     NULL | NULL   |      | BTREE      |         |               |
| single_table |          0 | idx_key2     |            1 | key2        | A         |       9693  |     NULL | NULL   | YES  | BTREE      |         |               |
| single_table |          1 | idx_key1     |            1 | key1        | A         |        968 |     NULL | NULL   | YES  | BTREE      |         |               |
| single_table |          1 | idx_key3     |            1 | key3        | A         |        799 |     NULL | NULL   | YES  | BTREE      |         |               |
| single_table |          1 | idx_key_part |            1 | key_part1   | A         |        9673 |     NULL | NULL   | YES  | BTREE      |         |               |
| single_table |          1 | idx_key_part |            2 | key_part2   | A         |        9999 |     NULL | NULL   | YES  | BTREE      |         |               |
| single_table |          1 | idx_key_part |            3 | key_part3   | A         |       10000 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.01 sec)

属性名描述Table 索引所属表的名称。
Non_unique 索引列的值是否是唯一的，聚簇索引和唯一二级索引的该列值为0，普通二级索引该列值为1。
Key_name 索引的名称。
Seq_in_index 索引列在索引中的位置，从1开始计数。比如对于联合索引idx_key_part，来说，key_part1、key_part2和key_part3对应的位置分别是1、2、3。
Column_name 索引列的名称。
Collation 索引列中的值是按照何种排序方式存放的，值为A时代表升序存放，为NULL时代表降序存放。
Cardinality 索引列中不重复值的数量。后边我们会重点看这个属性的。
Sub_part 对于存储字符串或者字节串的列来说，有时候我们只想对这些串的前n个字符或字节建立索引，这个属性表示的就是那个n值。如果对完整的列建立索引的话，该属性的值就是NULL。
Packed 索引列如何被压缩，NULL值表示未被压缩。这个属性我们暂时不了解，可以先忽略掉。
Null 该索引列是否允许存储NULL值。
Index_type 使用索引的类型，我们最常见的就是BTREE，其实也就是B+树索引。
Comment 索引列注释信息。
Index_comment 索引注释信息。

Cardinality
- 就是基数.
- 对于InnoDB存储引擎来说，使用SHOW INDEX语句展示出来的某个索引列的Cardinality属性是一个估计值，并不是精确的

使用索引统计中的哪些数据:

使用SHOW TABLE STATUS展示出的Rows值，也就是一个表中有多少条记录
使用SHOW INDEX语句展示出的Cardinality属性
- 一个值的重复次数 ≈ Rows ÷ Cardinality

假设IN语句中有20000个参数的话，就直接使用统计数据来估算这些参数需要单点区间对应的记录条数了，每个参数大约对应10条记录，所以总共需要回表的记录数就是：

20000 x 10 = 200000

使用统计数据来计算单点区间对应的索引记录条数可比index dive的方式简单多了，但是它的致命弱点就是：不精确！。使用统计数据算出来的查询成本与实际所需的成本可能相差非常大。

小贴士：大家需要注意一下，在MySQL 5.7.3以及之前的版本中，eq_range_index_dive_limit的默认值为10，之后的版本默认值为200。所以如果大家采用的是5.7.3以及之前的版本的话，很容易采用索引统计数据而不是index dive的方式来计算查询成本。当你的查询中使用到了IN查询，但是却实际没有用到索引，就应该考虑一下是不是由于 eq_range_index_dive_limit 值太小导致的。