[大数据]【MySQL】进阶——一起探究MySQL优化器如何选择索引的奥秘

🌟 前言

🐶 作者简介：大家好，我是周周，目前就职于国内短视频小厂BUG攻城狮一枚。
💻个人主页：程序猿周周
📖专题系列：Java面试总结
🤺 如果文章对你有帮助，记得👍点赞👍、👀关注👀?👌收藏👌，一键三连哦，你的支持将成为我最大的动力。

🚀 1 概述

MySQL 作为一种关系型数据库，在我们系统的持久化方案中占据着半壁江山，是一位熟悉又陌生的老朋友。因为虽然我们可以熟练的编写各种 SQL 语句进行业务上的 CRUD，但其底层一些设计实现却又一知半解。如果你也有这样的疑问，可以跟着周周一起重新认识一遍我们的好朋友。

🌈 1.1 索引为何不生效

首先抛出一个问题，我们面对系统出现慢 SQL 查询时，往往都是添加相关的索引处理，但有时候我们的字段明明加了索引，最后还是全表扫界？而一个字段存在于多个索引中，为什么最后选择了索引 A，而不是索引 B？

虽然网上有很多关于索引失效的文章，如聊聊索引失效的10种场景，太坑了 中提到使用 or 关键字 将导致 SQL 走不上索引。但真的如此吗？

所以，让我们回归到这个本质问题，MySQL 是如何选择索引的？

🌈 1.2 SQL 的执行过程

我们知道，MySQL 在执行一条 SQL 语句时，会经历一下几个过程：

1）客户端与 MySQL 服务器建立连接；

2）查询缓存（8.0 之后移除）；

3）分析器进行词法和语法解析；

4）优化器选择索引，生成执行计划；

5）执行器操作存储引擎，返回结果。

其中 MySQL 的连接管理以及 InnoDB 引擎想必大家已经足够了解，在此就不过都赘述，后面也会和大家一起学习探究，而 MySQL 索引选择的奥秘就隐藏在优化器中。

ref一条SQL语句的执行过程

🌈 1.3 MySQL 做了哪些优化

数据库性能取决于数据库级别的几个因素，如表、查询和配置设置。这些软件构造在硬件级别上导致 CPU 和 IO 操作，所以必须尽可能地最小化这些操作并使其尽可能高效。

即优化器可以从数据库应用程序本身以及硬件两个方向进行性能的优化，同时需要平衡可移植性和性能。

而如何选择一个何时的索引也仅仅是 MySQL 性能优化的一小部分，更多优化相关信息可以通过官方文档了解：

ref8.1 Optimization Overview

🚀 2 优化器

MySQL 查询优化器又叫成本优化器，使用基于成本的优化方式（Cost-based Optimization），而非基于查询时间。以 SQL 语句作为输入，利用内置的成本模型和数据字典信息以及存储引擎的统计信息决定使用哪些步骤执行查询语句。

从高层次来说，MySQL 服务器可以分为服务器层以及存储引擎层两部分。其中，优化器工作在服务器层，位于存储引擎 API 之上。而优化器的工作过程从语义上可以分为三个阶段：逻辑转换、基于成本优化、执行计划改进。

🌈 2.0 数据准备

在此之前，我们先来建一张测试表：

create table `users` (
  `id` int(11) not null auto_increment,
  `name` varchar(255) not null,
  `age` int(11) not null,
  primary key (`id`),
  key `index_name` (`name`) using btree,
  key `index_age` (`age`) using btree
) engine=InnoDB;

并批量插入 100 条记录：

delimiter $$
create procedure initUsers()
begin
declare n int default 1;
while n <= 100
do 
insert into users(`name`,age) values(concat('user_',n),n);
set n = n+1;
end while;
end $$

delimiter ;
call initUsers();

🌈 2.1 逻辑转换

MySQL 优化器首先可能会以不影响结果的方式对查询进行转换，转换的目标是尝试消除某些操作从而更快地执行查询。

主要包括否定消除、等值传递和常量传递、常量表达式求值、外连接转换为内连接、子查询转换、视图合并等；

mysql> explain select * from users where id > 1 and 1=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   99 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

显然，查询条件中 1=1 是完全多余的。没有必要为每一行数据都执行一次计算，删除这个条件也不会影响最终的结果。执行 EXPLAIN 语句之后，通过 SHOW WARNINGS 命令可以查看逻辑转换之后的 SQL 语句，从上面的结果可以看出 1=1 已经不存在了。

mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                             |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `test`.`users`.`id` AS `id`,`test`.`users`.`name` AS `name`,`test`.`users`.`age` AS `age` from `test`.`users` where (`test`.`users`.`id` > 1) |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

🌈 2.2 基于成本优化

在真正执行一条查询语句之前，MySQL 的优化器会找出所有可以用来执行该语句的方案，并在对比这些方案后找出成本最低的方案，过程大致如下：

1）根据搜索条件，找出所有可能使用的索引。

2）计算全表扫描的代价。

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

4）对比各种方案的代价，找出成本最低的方案。

🌈 2.3 执行计划改进

MySQL 再找出一个合适的执行计划后，依旧可对现有方案进行进一步优化，如表条件下推、访问方法调整、排序避免以及索引条件下推等等。

🚀 3 基于成本优化

🌈 3.1 什么是成本

前面小结我们提到，MySQL 优化是尽可能减少软件构造在硬件级别上导致 CPU 和 IO 操作消耗，即每种方案的执行成本实际上由 CPU 成本和 IO 成本两部分组成的：

• CPU 成本，数据的读取、比较、过滤、排序等这些操作所耗费的时间成本。

• IO 成本，数据从硬盘加载到内存所耗费的时间成本。

而 总成本 = IO成本 + CPU成本。从这个公式可以看出，我们需要三种数据来计算成本：

1）核算 IO 成本需要读取的页面数量；

2）核算 CPU 成本需要对比的记录数；

3）每种操作对应的成本常量系数。

🌈 3.2 关于成本常量

其中每种操作都有对应的成本常量系数，可以通过 mysql 系统数据库中的 server_cost 和 engine_cost 两个表进行查询和设置。

• server_cost

mysql> select * from mysql.server_cost;
+------------------------------+------------+---------------------+---------+---------------+
| cost_name                    | cost_value | last_update         | comment | default_value |
+------------------------------+------------+---------------------+---------+---------------+
| disk_temptable_create_cost   |       NULL | 2022-05-08 12:26:17 | NULL    |            20 |
| disk_temptable_row_cost      |       NULL | 2022-05-08 12:26:17 | NULL    |           0.5 |
| key_compare_cost             |       NULL | 2022-05-08 12:26:17 | NULL    |          0.05 |
| memory_temptable_create_cost |       NULL | 2022-05-08 12:26:17 | NULL    |             1 |
| memory_temptable_row_cost    |       NULL | 2022-05-08 12:26:17 | NULL    |           0.1 |
| row_evaluate_cost            |       NULL | 2022-05-08 12:26:17 | NULL    |           0.1 |
+------------------------------+------------+---------------------+---------+---------------+

• engine_cost

mysql> select * from mysql.engine_cost;
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| engine_name | device_type | cost_name              | cost_value | last_update         | comment | default_value |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| default     |           0 | io_block_read_cost     |       NULL | 2022-05-08 12:26:17 | NULL    |             1 |
| default     |           0 | memory_block_read_cost |       NULL | 2022-05-08 12:26:17 | NULL    |          0.25 |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+

总结一下：

🌈 3.3 如何计算成本

我们最常使用 EXPLAIN 命令方法是直接将它放在要分析的 SQL 的前面，如果加上 format=json 选项会输出 json 格式的分析数据，包含语句执行的成本信息。

mysql> explain format=json select * from users where age>=20;
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "10.25"
    },
    "table": {
      "table_name": "users",
      "access_type": "ALL",
      "filtered": "81.00",
      "cost_info": {
        "read_cost": "2.15",
        "eval_cost": "8.10",
        "prefix_cost": "10.25",
        "data_read_per_join": "81K"
      },
      // 省略
    }
  }
}

查询计划显示使用了全表扫描（access_type = ALL），而没有选择 index_age 索引。可以在上面看到全表扫描的成本是 10.25，那么这个值是怎么来的呢？

这就得提到 MySQL 为每个表维护的一系列的统计信息了。可以通过 SHOW TABLE STATUS 查看表的统计信息。

mysql> show table status like 'users';
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name  | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| users | InnoDB |      10 | Dynamic    |   99 |            165 |       16384 |               0 |        32768 |         0 |            100 | 2022-05-08 16:15:12 | 2022-05-08 16:16:30 | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.00 sec)

• Rows：表中的记录条数。对于 MyISAM 存储引擎，该值是准确的；而对于 InnoDB，该值是一个估值。

• Data_length：表占用的存储空间字节数。对于 MyISAM 存储引擎，该值就是数据文件的大小；而对于 InnoDB 引擎，该值就相当于聚簇索引占用的存储空间的大小。所以对于使用 InnoDB 引擎的表，Data_length = 聚簇索引的页面数量 * 每个页面的大小（默认16k）。

则该全表扫描的成本 = CPU 成本 + IO 成本 = 10 + 0.25 = 10.25，其中：

1）CPU 成本 = 表中的记录数 * 读取一条记录的成本 = 100 * 0.1 = 10；

2）IO 成本 = 聚簇索引占用的页面数 * 内存访问一个页面的成本 = (16384/(16 * 1024)) * 0.25 = 0.25。

🌈 3.3 如何控制成本

为了找到最佳执行计划，优化器需要比较不同的查询方案。随着查询中表的数量增加，可能的执行计划会呈现指数级增长，所以优化器不可能遍历所有的执行方案。

一种更灵活的优化方法是允许用户控制优化器在查找最佳查询计划时的遍历程度。一般来说，优化器评估的计划越少，则编译查询所花费的时间就越少；但另一方面，由于优化器忽略了一些计划，因此可能找到的不是最佳计划。

• 控制优化程度

MySQL 里限制一个查询的 join 表数目上限为 61，对于一个有 61 个表参与的 join 操作，理论上需要61!(阶乘)次的评估。

MySQL 也提供了两个系统变量，可以用于控制优化器的优化程度：

1）optimizer_search_depth（优化器查找的深度），如果该参数大于查询中表的数量，可以得到更好的执行计划，但是优化时间更长；如果小于表的数量，可以更快完成优化，但可能获得的不是最优计划。该参数的默认值为 62；如果不确定是否合适，可以将其设置为 0，让优化器自动决定搜索的深度。

2）optimizer_prune_level，告诉优化器根据对每个表访问的行数的估计跳过某些方案，这种启发式的方法可以极大地减少优化时间而且很少丢失最佳计划。因此，该参数的默认设置为 1（开启）；如果确认优化器错过了最佳计划，可以将该参数设置为 0，不过这样可能导致优化时间的增加。

• 控制优化行为

MySQL 提供了一个系统变量 optimizer_switch 用于控制优化器的优化行为，比如索引合并、索引下推等。

它的值由一组标识组成，每个标识的值都可以为 on 或 off，表示启用或者禁用了相应的优化行为。该变量支持全局和会话级别的设置，可以在运行时进行更改。

mysql-> select @@optimizer_switch;

mysql-> SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';

🌈 3.4 优化器和索引提示

还有一种控制优化器策略的方法就是优化器提示（Optimizer Hint）和索引提示（Index Hint），它们只对单个语句有效，而且优先级比 optimizer_switch 更高。

优化器提示使用 /*+ … */ 注释风格的语法，可以对连接顺序、表访问方式、索引使用方式、子查询、语句执行时间限制、系统变量以及资源组等进行语句级别的设置。

以及使用 USE INDEX 提示优化器使用某个索引，IGNORE INDEX 提示优化器忽略某个索引，FORCE INDEX 强制使用某个索引等等。

🚀 4 优化器跟踪

至于 3.3 小结中的 SQL 为何没有使用 index_age 索引，我们可以使用到 optimizer_trace 功能来查看具体原因。

Optimizer Trace（优化器跟踪）是 MySQL 5.6.3 里新加的一个特性，可以把 MySQL Optimizer 的决策和执行过程输出成文本，结果为 JSON 格式，兼顾了程序分析和阅读的便利。

🌈 4.1 开启跟踪功能

optimizer_trace 默认是关闭的，需要开启后才能使用：

-- 开启optimizer_trace
set [global] optimizer_trace="enabled=on";

-- 要分析的sql语句,可以有多个
select * from users where age>=20;

-- 查询优化跟踪过程
select * FROM information_schema.optimizer_trace;

-- 关闭optimizer_trace
set optimizer_trace="enabled=off";

🌈 4.2 输出内容

优化器跟踪输出主要包含三个部分，也可以说是优化器的执行过程分类：

1）join_preparation（准备阶段），这个过程展示了准备阶段的执行过程，返回字段名扩展之后的 SQL 语句。对于 1=1 这种多余的条件，也会在这个步骤被删除。

"join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `users`.`id` AS `id`,`users`.`name` AS `name`,`users`.`age` AS `age` from `users` where (`users`.`age` >= 20)"
          }
        ]
      }

2）join_optimization（优化阶段），这一段展示了优化以及最优执行方案的决策过程，是分析 optimizer_trace 的重点内容。

"join_optimization": {
  "select#": 1,
  "steps": [
    // 省略
  ]
}

3）join_execution（执行阶段），优化跟踪的最后一步，执行选出的计划，调用存储引擎层 API。

 "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }

🌈 4.2 优化阶段过程

optimizer 优化阶段共分为 9 个子过程，而且会随着 SQL 语句的复杂性的增加而增加。

• condition_processing（条件优化），对 where 或者 having 条件进行优化。

"condition_processing": {
  "condition": "WHERE",
  "original_condition": "(`users`.`age` >= 20)",
  "steps": [
    {
      // 等值传播
      "transformation": "equality_propagation",
      "resulting_condition": "(`users`.`age` >= 20)"
    },
    {
      // 常量传播
      "transformation": "constant_propagation",
      "resulting_condition": "(`users`.`age` >= 20)"
    },
    {
      // 无效条件移除 如条件中有 1=1 之类的恒等式将会被移除
      "transformation": "trivial_condition_removal",
      "resulting_condition": "(`users`.`age` >= 20)"
    }
  ]
}

• substitute_generated_columns，替代生成的列

"substitute_generated_columns": {
}

• table_dependencies，分析表之间的依赖关系

"table_dependencies": [{
  "table": "`users`",
  "row_may_be_null": false,
  "map_bit": 0,
  "depends_on_map_bits": [
  ]
}]

• ref_optimizer_key_uses，列出所有可用的ref类型的索引

"ref_optimizer_key_uses": [
]

• rows_estimation

估算各种执行计划需要扫描的记录数和成本，这一步是优化器的核心部分，这一步的分析数据会作为选出最佳执行计划的依据。

"rows_estimation": [{
  "table": "`users`", # 表名
  "range_analysis": { # 扫描范围分析
    "table_scan": { #  全表扫描分析
    },
    "potential_range_indexes": [], 
    # 如果有可下推的条件，则带条件考虑范围查询
    "setup_range_conditions": [],
    # 分组索引分析，分析group by和distinct使用索引的情况
    "group_index_range": {},
    "skip_scan_range": {},
    "analyzing_range_alternatives": {
      # 索引扫描分析
      "range_scan_alternatives": [
        {
          "index": "index_age", # 索引名
          "ranges": ["20 <= age"], # 扫描的条件范围
          "index_dives_for_eq_ranges": true, # 是否使用了index
          "rowid_ordered": false, # 扫描的结果集是否根据PK值进行排序
          "using_mrr": false,
          "index_only": false, # 表示是否使用了覆盖索引
          "in_memory": 1, # 
          "rows": 81, # 扫描的行数
          "cost": 28.61, # 索引的使用成本
          "chosen": false, # 是否使用了该索引
          "cause": "cost" # 没有使用的原因
        }
      ],
      # 分析是否使用了索引合并（index merge）
      "analyzing_roworder_intersect": {}
    }
  }
}]

• considered_execution_plans

综合考虑各个计划，并选出最终的执行计划。

• attaching_conditions_to_tables

这一步会根据前面选出的最佳执行计划情况在表上附加一些条件。

• refine_plan，精炼执行计划

🚀 5 总结

MySQL 优化器采用的是基于成本的优化方式，利用数据字典和统计信息选择最佳执行方式，即哪个成本最低选哪个。

同时，MySQL 为我们提供了控制优化器的各种选项，包括控制优化程度、设置成本常量、统计信息收集、启用/禁用优化行为以及使用优化器提示等。

1）成本由 CPU 成本和 IO 成本组成，每个成本常数值可以自己调整。

2）通过开启 optimizer_trace 可以跟踪优化器的各个环节的分析步骤。

3）EXPLAIN 加上 format=json 选项后可以查看成本信息，执行后通过 show warnings 可以查看被改写后的语句。

🎉 Reference

• 使用 optimizer_trace 查看执行流程，分析、验证优化思路
• Using EXPLAIN EXTENDED
• OPTIMIZER_TRACE详解
• Chapter 7 The Optimizer
• Chapter 8 Tracing the Optimizer

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖