本文主要聊聊：MySQL 索引选择的一些细节。

优化器选择索引策略

在 MySQL 的架构中，优化器负责决定以什么作为索引进行查询，选择什么样的索引，就决定着要使用什么样的方案。

最优的方案就是用最小的代价去执行语句。

在 MySQL 中，决定“代价”的其中一个很重要的因素就是：扫描行数。

扫描行数越少，访问磁盘的次数就越少。

如何判断扫描行数？

索引的区分度

索引基数

索引的区分度计算公式：

索引基数=count(distinct col)

索引区分度=索引基数/count(*)

从上述公式可知，其表示不重复字段的比例。这个比例越大，我们扫描的行数就越少。

索引基数越大，索引区分度就越好，反之则越差。比如一张人口基本信息表，性别一栏，只有男和女，这个区分度就很低。

查看索引基数的命令

show index from t<表名>

问题来了，索引基数怎么得来？

暴力方法：拿来这张表一行一行统计，如果不重复，基数加一；如果重复，基数不变。

原因有二：

  1. 效率太低
  2. 生产环境中数据更新频繁，每次更新操作都会更新索引文件中的数据，如果每进行一次更新，都要统计一遍基数，系统压力太大

生成统计

实际中使用的是采样统计的方法。

如何采样统计呢？

以 InnoDB 存储引擎为例，默认情况下引擎会对8个叶子节点的信息进行统计，过程如下：

  1. 取得 B+ 树中所有的叶子节点总数，记为 A

  2. 随机取得B+树中的 N 个叶子节点，统计每个叶子节点的不同记录条数，分别记为P1,P2,…,PN

  3. 根据采样计算出基数值：

    基数值（Cardinality） = （P1+P2+…+PN）* A / N

现在我们知道了采样统计的方法了，那么这个基数值是如何更新的呢？毕竟不能每有一行数据的变更就抽样统计一次吧。

统计更新

当变更的数据行数超过常数 M 时，会自动触发重新做一次索引统计。

统计存储

我们已经知道了基数值是如何生成的，如何更新的，那么这个基数值存储在什么地方？

在 MySQL 中基数值有两种存储方式，可以通过设置参数 innodb_stats_persistent来进行选择：

  1. 设置为 on 的时候，表示统计信息会持久化存储。此时默认 N = 20,M=10
  2. 设置为 off的时候，表示统计信息只存储到内存中。此时默认 N = 8 , M = 16

索引策略错误

我们在前面介绍了 MySQL 是如何根据索引选择策略进行优化查询的。

那么索引选择策略有没有选择错误的时候？

例子

一张表，表中三个字段：id、a、b。这三个字段上都有索引。

在这张表中插入 10w 条数据，每条数据按照下面方式递增：（1,1,1）、（2,2,2），…

分析下面一条语句：

select * from t where a between 10000 and 20000;

查看语句执行

使用 explain命令可以查看 SQL 语句的执行情况。

语句执行

操作

SessionA SessionB
start transaction with consistent snapshot
delete from t；
插入 10w 行记录
explain select * from t where a between 10000 and 20000
commit

同时开启了两个事务：SessionA 和 SessionB。

这种场景下 SessionB 的查询语句还是以 a 为索引的么？

我们可以看下慢查询日志来了解情况。

慢查询日志(slow log)

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL。

long_query_time的默认值是：10s。

默认情况下，MySQL 语句并不开启慢查询日志。

如果查看日志路径？

使用如下命令：

1
show variables  like '%slow_query_log%';

作为对比，我们分别执行下面三行 SQL 语句：

1
2
3
set long_query_time=0;/*为了每条 SQL 语句的执行情况都打进日志 */
select * from t where a between 10000 and 20000; /*Q1*/
select * from t force index(a) where a between 10000 and 20000;/*Q2*/

三条语句执行后的 slow log 如下图

慢查询日志

Q1 执行了全表扫描，扫描行数为：10w。

Q2 扫描了 10001 行，执行时间是 Q1 的一半。

所以我们可以知道：在没有强制使用索引 a 的情况下，Q1 选择的索引不是 a。

也就是说，MySQL 中的优化器没有执行最优的选择策略。

why？

前面我们讲了“扫描行数”这个因素非常影响优化器对于策略的选择，莫不是优化器对于扫描行数预估有误？

我们可以看看它的预估结果。

预计扫描结果

Q1的预估行数为 104620，Q2 的预估行数为 37116。

两个问题：

  1. 预估行数偏差太大
  2. Q1 的预估行数明显小于 Q2，可是为什么执行器却执行 Q1？

先回答问题2.

如果使用索引 a，则每次从索引 a 上拿到一个值，都需要到主键索引上查出一整行数据，有“回表”操作。

优化器明显将这个回表的代价也算进去了。

而直接从主键索引上扫描，则没有回表代价。

优化器显然认为回表“更麻烦”，选择了直接从主键索引扫描数据。

可是回头想想：我们在最开始执行查询语句的时候，优化器选择的索引是 a，同样算进去了回表的代价呀。

看来问题1才是引起策略选择错误的主要因素。

再看问题1，为什么会偏差如此之大？

答案：。。。。

如何更正统计

（未完。。。）

参考资料

  1. 《MySQL 45讲》
  2. http://voidday.com/note/4fd09d4d.html
  3. https://www.cnblogs.com/kerrycode/p/5593204.html