索引选择

选择索引是优化器的工作。优化器选择索引是为了找到一个最优的执行方案，并用最小的代价去执行语句。扫描行数是影响执行代价的因素之一，扫描行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。当然扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

1. 索引基数计算

MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。这个统计信息就是索引的区分度。一个索引上不同的值越多，这个索引的区分度就越好。

怎样查看索引的区分度呢？可以通过 SHOW INDEX 结果中的列 Cardinality 来观察。
image.png
Cardinality 值非常关键，它表示索引中不重复记录数量的预估值，我们一般称为基数。基数越大，索引的区分度越好。但需要注意，基数只是一个预估值，而不是一个准确值。在实际应用中，Cardinality/n_rows_in_table 应尽可能地接近 1。如果非常小，那么用户需要考虑是否还有必要创建这个索引。

因为 MySQL 数据库中有各种不同的存储引擎，而每种存储引擎对 B+ 树索引的实现又各不相同，所以对索引基数的统计是放在存储引擎层进行的。此外需要考虑到，在生产环境中，数据的更新操作可能是非常频繁的，如果把整张表取出来一行行统计，虽然可以得到精确的结果，但代价太高了。因此，MySQL 对于索引基数的统计都是通过采样统计（Sample）的方法来完成的。

在 InnoDB 存储引擎中，索引基数统计信息的更新发生在下面两个操作中：INSERT 和 UPDATE。由于不可能在每次发生 INSERT 和 UPDATE 时都去更新索引基数信息，因此 InnoDB 存储引擎内部对更新索引基数统计信息的策略为：

  • 自上次统计索引基数信息后，表中 1/16 的数据已经发生过变化，此时需要更新索引基数信息
  • InnoDB 存储引擎内部有一个计数器 stat_modified_counter，用来表示发生变化的次数，当 stat_modified_counter > 2,000,000,000 时需要更新索引基数信息

下面接着考虑 InnoDB 存储引擎内部是怎么来进行索引基数信息的统计和更新操作的。同样通过采样统计，默认InnoDB 存储引擎对 8 个叶子节点（Leaf Page）进行采样。可通过 innodb_stats_sample_pages 参数来设置每次页的数量，默认值为 8。具体采样过程如下：

  • 取得 B+ 树索引中叶子节点的数量，记为 A
  • 随机取得 B+ 树索引中的 8 个叶子节点，统计每个页不同记录的个数，记为 P1、P2 … P8
  • 计算索引基数的预估值：Cardinality = (P1 + P2 + … + P8) / 8 * A

由上面的执行流程可知，索引基数不是一个实际精确的值。再者，每次对索引基数值的统计，都是通过随机取 8 个叶子节点得到的，因此即使表中数据没有发生任何变化，多次执行 SHOW INDEX 语句得到的索引基数信息也可能是不一样的。

2. 优化器不走索引原因

在某些情况下，当执行 EXPLAIN 命令进行 SQL 语句的分析时，会发现优化器并没有选择索引去查找数据，而是直接进行全表扫描来得到数据。这种情况多发生与范围查找、JOIN 连接操作等情况下。如下图所示：
image.png
在字段 a 上有建立二级索引，rows 字段表示的是预计扫描行数。通过两条语句的对比，尽管使用索引 a 能够大幅减少扫描行数，但为什么优化器为什么放着扫描 37000 行的执行计划不用，却选择了扫描行数是 100000 的执行计划呢？

这是因为我们查询的数据是整行信息，如果使用索引 a，每次从索引 a 上拿到一个值，都要回到主键索引上查出整行数据，这个代价优化器也要算进去。而如果选择扫描 10 万行，是直接在主键索引上进行扫描的。优化器会估算这两个选择的代价，从结果看来，优化器认为直接扫描主键索引更快。但实际执行时间却不是最优的。如果确认使用二级索引可以带来更好的性能，则可以使用 FORCE INDEX 来强制使用某个索引。

3. 索引提示

MySQL 数据库支持索引提示（INDEX HINT）来显式地告诉优化器使用哪个索引，主要有以下两种方式：

1）USE INDEX

  1. select * from t USE INDEX(a) where a=1 and b=2;

USE INDEX 只是告诉优化器可以选择该索引，实际上优化器还是会再根据自己的判断进行选择。

2）FORCE INDEX

  1. select * from t FORCE INDEX(a) where a=1 and b=2;

当使用 FORCE INDEX 的时候，优化器会强制使用该索引。MySQL 会根据词法解析的结果分析出可能可以使用的索引作为候选项，然后在候选列表中依次判断每个索引需要扫描多少行。如果 FORCE INDEX 指定的索引在候选索引列表中，就直接选择这个索引，不再评估其他索引的执行代价。

如果用户确定指定某个索引来完成查询，那么最可靠的是使用 FORCE INDEX，而不是 USE INDEX。

执行计划分析

1. EXPLAIN

使用 EXPLAIN 工具可以确认执行计划是否良好，查询是否走了合理的索引。不同版本的 MySQL 优化器各有不同，一些优化规则随着版本的发展可能会有变化，查询的执行计划随着数据的变化也可能会有变化。对于这类情况可以使用 EXPLAIN 来验证自己的判断。下面是一个显式执行计划的例子：
image.png
该例中 EXPLAIN 命令的输出信息可以告诉我们 MySQL 访问了哪些表，以及它是如何访问数据的。里面有很重要的索引使用信息，我们可以据此判断我们的索引是否需要优化。

1.1 id

id 包含一组数字，表示查询中执行 SELECT 子句或操作表的顺序。如果是子查询，id 的序号会递增，id 值越大则优先级越高，越先被执行。如果 id 相同，则可以认为它们是一组，从上往下顺序执行。在所有组中，id 值越大，优先级就越高，越先执行。
image.png

1.2 select_type

select_type 表示查询中每个 SELECT 子句的类型，是简单还是复杂。输出分类如下：

  • SIMPLE：查询语句中不包含子查询或 UNION 的查询，当然，连接查询也算 SIMPLE 类型。

image.png

  • PRIMARY：对于包含 UNION、UNION ALL 或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的 select_type 值就是 PRIMARY。

image.png

  • UNION：对于包含 UNION 或 UNION ALL 的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查询的 select_type 值就是 UNION。

image.png

  • UNION RESULT：MySQL 选择使用临时表来完成 UNION 查询的去重工作，针对该临时表的查询的 select_type 就是 UNION RESULT。

image.png

  • SUBQUERY：如果包含子查询的查询语句不能够转为对应的 semi-join 的形式，并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个 SELECT 关键字代表的那个查询的 select_type 就是 SUBQUERY。由于 select_type 为 SUBQUERY 的子查询会被物化，所以只需要执行一遍。

image.png

  • DEPENDENT SUBQUERY：如果包含子查询的查询语句不能够转为对应的 semi-join 的形式，并且该子查询是相关子查询，则该子查询的第一个 SELECT 关键字代表的那个查询的 select_type 就是 DEPENDENT SUBQUERY。需要注意的是，select_type 为 DEPENDENT SUBQUERY 的查询可能会被执行多次。


  • DERIVED：在 FROM 列表中包含的子查询将被标记为 DERIVED。

1.3 type

type 表示 MySQL 在表中找到所需行的方式，又称访问类型。常见的类型如下，排名由差依次到好：

  • ALL：Full Table Scan，MySQL 将遍历全表以找到匹配的行。

image.png

  • index：Full Index Scan，index 与 ALL 区别为 index 类型只遍历索引树。

image.png

  • range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于 between、<、> 等的查询。一般来说，索引范围扫描要检索的记录更少，因而成本也更低。

image.png

  • ref：非唯一性索引扫描，将返回匹配某个单独值的所有行。常见于使用非唯一索引或唯一索引的非唯一前缀进行的查找。

image.png

  • eq_ref：唯一性索引扫描，在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是 eq_ref。

image.png

  • unique_subquery：类似于两表连接中被驱动表的 eq_ref 访问方法，unique_subquery 是针对在一些包含 IN 子查询的查询语句中，如果查询优化器决定将 IN 子查询转换为 EXISTS 子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的 type 列的值就是 unique_subquery。


  • const：当 MySQL 对查询的某部分进行优化，并转换为一个常量值时，可使用这个类型进行访问。如将主键置于 WHERE 列表中，MySQL 就能将该查询转换为一个常量。

image.png

  • system：当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如 MyISAM、Memory，那么对该表的访问方法就是 system，属于 const 的一种特例。


  • NULL：MySQL 在优化过程中分解语句，执行时甚至不用访问表或索引。

image.png

1.4 possible_keys

possible_keys 将指出 MySQL 能使用哪个索引在表中找到行，查询涉及的字段上若存在索引，则该索引将被列出，但不一定会被查询使用。另外，需要注意的是，possible_keys 列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引。

1.5 key

key 将显示 MySQL 在查询中实际使用到的索引，若没有使用索引则显示为 NULL。查询中若使用了覆盖索引，则该索引仅出现在 key 列表中。key_len 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确度的情况下，长度越短越好。key_len 显示的值为索引字段最大的可能长度，并非实际使用长度，即 key_len 是根据定义计算而得，不是通过表内检索出的。

1.6 ref

ref 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是：const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery 其中之一时，ref 列展示的就是与索引列作等值匹配的是什么，比如只是一个常数或者是某个列。

1.7 rows

rows 表示 MySQL 根据表统计信息及索引选用的情况，估算地找到所需的记录所需要读取的行数。

1.8 filtered

filtered 表示查询的表行占表的百分比。对于单表查询来说，这个 filtered 列的值没什么意义，我们更关注在连接查询中驱动表对应的执行计划记录的 filtered 值。

1.9 Extra

Extra 包含不适合在其他列中显示但十分重要的额外信息。它可能包含如下四种信息：

  • Using index：该值表示相应的 SELECT 操作中使用了覆盖索引而不需要回表。
  • Using where：该值表示 MySQL 服务器在存储引擎收到记录后进行 Post-filter。如果查询未能使用索引，则 Using where 的作用只是提醒我们 MySQL 将用 where 子句来过滤结果集。

image.png

  • Using index condition：该值表示相应的 SELECT 操作中使用了索引下推。


  • Using temporary：该值表示 MySQL 需要使用临时表来存储结果集，常见于排序和分组查询。

image.png

  • Using filesort：即文件排序，在 MySQL 中将无法利用索引完成的排序操作称为文件排序。

image.png

  • Using join buffer：在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL 一般会为其分配一块名叫 join buffer 的内存块来加快查询速度。

image.png

2. optimizer trace

我们介绍了 MySQL 会根据成本选择执行计划，也通过 EXPLAIN 知道了优化器最终会选择怎样的执行计划，但 MySQL 如何制定执行计划始终是一个黑盒。那有没有什么办法可以了解各种执行计划的成本，以及 MySQL 做出选择的依据呢？

在 MySQL 5.6 及之后的版本中，我们可以使用 optimizer trace 功能查看优化器生成执行计划的整个过程。有了这个功能，我们不仅可以了解优化器的选择过程，更可以了解每一个执行环节的成本，然后依靠这些信息进一步优化查询。

如下代码所示，打开 optimizer_trace 后，再执行 SQL 就可以查询 information_schema.OPTIMIZER_TRACE 表来查看执行计划了，最后可以再关闭 optimizer_trace 功能：

  1. SET optimizer_trace="enabled=on";
  2. SELECT * FROM person WHERE NAME>'name84059' AND create_time>'2020-01-24 05:00:00';
  3. SELECT * FROM information_schema.OPTIMIZER_TRACE;
  4. SET optimizer_trace="enabled=off";

对于按照 create_time > ‘2020-01-24 05:00:00’ 条件走全表扫描的 SQL，我们从 OPTIMIZER_TRACE 的执行结果中，摘出了几个重要片段来重点分析：

  • 使用 name_score 对 name84059 < name 条件进行索引扫描需要扫描 25362 行，成本是 30435，因此最终没有选择这个方案。这里的 30435 是查询二级索引的 IO 成本和 CPU 成本之和，再加上回表查询聚簇索引的 IO 成本和 CPU 成本之和：

    1. {
    2. "index": "name_score",
    3. "ranges": [
    4.   "name84059 < name"
    5. ],
    6. "rows": 25362,
    7. "cost": 30435,
    8. "chosen": false,
    9. "cause": "cost"
    10. },

  • 使用 create_time 进行索引扫描需要扫描 23758 行，成本是 28511，同样因为成本原因没有选择这个方案：

    1. {
    2. "index": "create_time",
    3. "ranges": [
    4.   "0x5e2a79d0 < create_time"
    5. ],
    6. "rows": 23758,
    7. "cost": 28511,
    8. "chosen": false,
    9. "cause": "cost"
    10. }

  • 最终选择了全表扫描方式作为执行计划。可以看到，全表扫描 100086 条记录的成本是 20306，显然是小于其他两个方案的 28511 和 30435：

    1. {
    2. "considered_execution_plans": [{
    3.   "table": "`person`",
    4.   "best_access_path": {
    5.     "considered_access_paths": [{
    6.       "rows_to_scan": 100086,
    7.       "access_type": "scan",
    8.       "resulting_rows": 100086,
    9.       "cost": 20306,
    10.       "chosen": true
    11.     }]
    12.   },
    13.   "rows_for_plan": 100086,
    14.   "cost_for_plan": 20306,
    15.   "chosen": true
    16. }]
    17. },

    把 SQL 中的 create_time 条件从 05:00 改为 06:00，再次分析 OPTIMIZER_TRACE 可以看到，这次执行计划选择的是走 create_time 索引。因为是查询更晚时间的数据，走 create_time 索引需要扫描的行数从 23758 减少到了 16588。这次走这个索引的成本 19907 小于全表扫描的 20306，更小于走 name_score 索引的 30435：

    1. {
    2. "index": "create_time",
    3. "ranges": [
    4.   "0x5e2a87e0 < create_time"
    5. ],
    6. "rows": 16588,
    7. "cost": 19907,
    8. "chosen": true
    9. }

    索引失效场景

    1）不在索引列上做任何操作（计算、函数、自动or手动类型转换），否则会导致索引失效而转向全表扫描。因为索引保存的是索引列的原始值，而不是经过函数计算后的值。如果需要针对函数调用走数据库索引的话，只能保存一份函数变换后的值，然后重新针对这个计算列做索引。

2）如果在索引列上使用不等于（ != 或 <> ）操作，则无法使用索引。

3）索引列上有 is not null、not in 的时候也无法使用索引。注意：is null、in 是可以使用索引的，如果 in 中包含的值是连续的，可以考虑用 between 代替 in。

4）索引列上使用 like 时，如果以通配符开头（’%XX’、’%XX%’）则索引会失效，因为索引只能匹配列前缀。

5）在 where 子句中进行匹配时，如果索引字段是字符串类型，而进行匹配的是数字类型的话，此时会进行隐式的类型转换，导致索引失效。在 MySQL 中，字符串和数字做比较的话，是将字符串转换成数字。因此这条语句触发了规则：对索引字段做函数操作，优化器会放弃走树搜索功能。

  1. SELECT * FROM employees WHERE name = 1000;
  2. // 相当于
  3. SELECT * FROM employees WHERE CAST(name AS signed int) = 1000;

同样，对两张表做联表查询时，如果这两个表的字符集不同，那么会进行隐式的编码转换，所以在进行连接查询的时候是用不上关联字段的索引。

6）尽量避免在 where 中用 or，因为如果一个字段有索引，一个字段没有索引，则会进行全表扫描。

7）使用联合索引时，必须考虑联合索引的最左前缀原则。原因很简单，在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。也就是说，如果我们想使用联合索引中尽可能多的列，查询条件中的各个列必须是联合索引中从最左边开始连续的列。如果我们仅仅按照第二列搜索，肯定无法走索引。