【这篇文章最开始是在公司内部写的，后面抽了点时间稍微整理了一下，主要是把涉及到公司内部数据的部分去除，可能会导致一些阅读不畅】

本文的内容主要涉及到一些工具命令比如explain\optimize trace、mysql优化器的工作原理以及涉及到InnoDB的相关内容。

文章目录

mysql逻辑架构

优化分析: explain和optimize trace

一条sql语句通常都会有多种执行方案，mysql的优化器会选择其认为最优的方案进行执行，这里的最优其是指成本最低，在后面的优化器工作原理中会详细讲到。在和mysql打交道的过程中，面对一些复杂的查询语句或者slow query，我们经常会想要知道mysql选择了什么样的执行方案以及为什么这样选择，mysql提供了两个命令explain和optimize trace来帮助我们解决问题。

explain

如果我们想要查看某条查询语句具体的执行计划，可以在语句前加explain运行，输出的内容就是具体的执行计划。explain属于比较简单的内容，网上资料也非常多，在此就不深入讲解。

optimize trace

explain可以输出某条查询语句的具体的执行计划，但是有时候我们想更进一步知道优化器选择执行方案的过程，那就需要用到optimize trace。
optimize trace是mysql 5.6以后的版本中才提供的功能，该功能的开启和关闭是由系统变量optimize_trace来控制的。

# 查看变量optimize_tarce
mysql> SHOW VARIABLES LIKE 'optimizer_trace';
+-----------------+--------------------------+
| Variable_name   | Value                    |
+-----------------+--------------------------+
| optimizer_trace | enabled=off,one_line=off |
+-----------------+--------------------------+
1 row in set (0.02 sec)

# 开启该功能
mysql> SET optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.00 sec)

打开该功能后，执行想要查看的语句，然后到information_schema数据库下的OPTIMIZER_TRACE表查看具体的信息。

mysql> select * from `table_v1` where grade_id = '9149' and class_id = '4685' order by id desc limit 10;

mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;

表OPTIMIZER_TRACE有4个字段，其含义如下：
下面以上述语句的trace为例进行分析：

{
  "steps": [
    {   
      // preparation阶段
      "join_preparation": {
        "select#": 1,
        "steps": [
          {  
            // 因为语句中用到select * ，此处先进行展开
            "expanded_query": "省略"
          }
        ]
      }
    },
    { 
      // 优化阶段
      "join_optimization": {
        "select#": 1,
        "steps": [
          {   // 条件处理
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "grade_id = '9149' and class_id = '4685' ",
              "steps": [
                {  
                  // 等值传递：a = b and b = 5 --> a = 5 and b = 5
                  "transformation": "equality_propagation",
                  "resulting_condition": "grade_id = '9149' and class_id = '4685' "
                },
                {
                  // 常量传递： a = 5 and b > a --> a = 5 and b > 5
                  "transformation": "constant_propagation",
                  "resulting_condition": "grade_id = '9149' and class_id = '4685' "
                },
                {
                  // 去除无用的条件： a > b and 15 > 5 --> a > b
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "grade_id = '9149' and class_id = '4685' "
                }
              ]
            }
          },
          {
            // 替换虚拟列
            "substitute_generated_columns": {
            }
          },
          { 
            // 表依赖信息
            "table_dependencies": [
              {
                "table": "`table_v1`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            // 可使用索引的字段
            "ref_optimizer_key_uses": [
              {
                "table": "`table_v1`",
                "field": "grade_id",
                "equals": "'9149'",
                "null_rejecting": true
              },
              {
                "table": "`table_v1 `",
                "field": "class_id",
                "equals": "'4685'",
                "null_rejecting": true
              }
            ]
          },
          {
            // 预估单表访问的成本
            "rows_estimation": [
              {  
                //  全表扫描的成本
                "table": "`table_v1`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 3271649,
                    "cost": 364606
                  },
                  // 可能使用的索引分析
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_grade_class_student",
                      "usable": true,
                      "key_parts": [
                        "grade",
                        "class",
                        "student",
                        "id"
                      ]
                    }
                  ],
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  },
                  // mysql8.0的新特性
                  "skip_scan_range": {
                    "potential_skip_scan_indexes": [
                      {
                        "index": "idx_grade_class_student",
                        "usable": false,
                        "cause": "query_references_nonkey_column"
                      }
                    ]
                  },
                  // 分析各种可能的索引的成本
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "idx_grade_class_student",
                        "ranges": [
                          "9149 <= grade_id <= 9149 AND 4685 <= class_id <= 4685"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 166880,
                        "cost": 183569,
                        "chosen": true
                      }
                    ],
                    // 分析使用索引合并的成本
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  // 上述单表的最优访问方法
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "idx_grade_class_student",
                      "rows": 166880,
                      "ranges": [
                        "9149 <= grade_id <= 9149 AND 4685 <= class_id <= 4685"
                      ]
                    },
                    "rows_for_plan": 166880,
                    "cost_for_plan": 183569,
                    "chosen": true
                  }
                }
              }
            ]
          },
          {    
            //分析执行计划
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`table_v1`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "idx_grade_class_student",
                      "rows": 166880,
                      "cost": 129005,
                      "chosen": true
                    },
                    {
                      "access_type": "range",
                      "range_details": {
                        "used_index": "idx_grade_class_student"
                      },
                      "chosen": false,
                      "cause": "heuristic_index_cheaper"
                    }
                  ]
                },
                "condition_filtering_pct": 0.9,
                "rows_for_plan": 1501.9,
                "cost_for_plan": 129005,
                "chosen": true
              }
            ]
          },
          {    
            // 添加附加的条件
            "attaching_conditions_to_tables": {
              "original_condition": "grade_id = '9149' and class_id = '4685'",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`table_v1`",
                  "attached": "grade_id = '9149' and class_id = '4685'"
                }
              ]
            }
          },
          {
            // 优化group_by和order_by
            "optimizing_distinct_group_by_order_by": {
              "simplifying_order_by": {
                "original_clause": "`table_v1`.`id` desc",
                "items": [
                  {
                    "item": "`table_v1`.`id`"
                  }
                ],
                "resulting_clause_is_simple": true,
                "resulting_clause": "`table_v1`.`id` desc"
              }
            }
          },
          { 
            // 重新考虑索引选择情况
            "reconsidering_access_paths_for_index_ordering": {
              "clause": "ORDER BY",
              "steps": [
              ],
              "index_order_summary": {
                "table": "`table_v1`",
                "index_provides_order": true,
                "order_direction": "desc",
                "index": "PRIMARY",
                "plan_changed": true, // 考虑到排序进行了执行计划更换
                "access_type": "index"
              }
            }
          },
          {
            "finalizing_table_conditions": [
              {
                "table": "`table_v1`",
                "original_table_condition": "grade_id = '9149' and class_id = '4685'",
                "final_table_condition   ": "grade_id = '9149' and class_id = '4685'"
              }
            ]
          },
          {
            "refine_plan": [
              {
                "table": "`table_v1`"
              }
            ]
          },
          {
            "considering_tmp_tables": [
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}

以上是下面语句的优化过程，可以看到最开始选择了索引 idx_grade_class_student，但最后出于排序的考虑选择了primary。

而下面这条语句开始同样选择了索引 idx_grade_class_student，但最后没有因为排序而改用primary。（具体的trace就不贴了）

两者最大的区别就是使用索引 idx_grade_class_student后筛选的条目数不同，所以这里做一些定性的推断：mysql的优化器衡量了扫描和排序的成本，当 idx_grade_class_student筛选出的记录数很少，排序的成本很小，就会选择使用 idx_grade_class_student；当 idx_grade_class_student筛选出的记录很多，排序成本就会上升，就会选择primary排序加全表扫描。

// 使用idx_project_group_employee的语句
"range_scan_alternatives": [
                      {
                        "index": " idx_grade_class_student",
                        "ranges": [
                          "9149 <= grade_id <= 9149 AND 5816 <= group_id <= 5816"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 3,
                        "cost": 4.31,
                        "chosen": true
                      }
                    ]
                    
// 使用primary的语句    
"range_scan_alternatives": [
                      {
                        "index": " idx_grade_class_student",
                        "ranges": [
                          "9149 <= grade_id <= 9149 AND 4685 <= class_id <= 4685"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 166880,
                        "cost": 183569,
                        "chosen": true
                      }
                    ]

做一些进一步的验证：

  • Limit 数量的影响
    随着limit数量的增加，rows数量随之增加，当limit数量增加至6000时，其执行方案由使用primary转换为使用idx_project_group_employee。rows数量可以一定程度上代表语句执行的成本，也就是随着limit数量的增加，使用primary的成本逐渐增加最终超过使用idx_project_group_employee的成本，方案转换。

这里其实是一个mysql优化器对order by limit优化导致的“错误”，只是在我们的业务场景下不会导致slow query，比如我们把sql稍微改动一下，将排序方式改变，explain结果不变，也就是说优化器的选择不会变，但是实际语句的执行时间从479ms涨为19.3s。

通过查阅资料发现这也是一个比较典型的问题，原因是mysql对limit的成本计算非常粗糙，导致优化器的计算结果和实际差异巨大，关于mysql对limit的成本计算在下文会讲到。

优化器的工作原理

前面提到过mysql优化器会选择的最优的方案其实是成本最低的执行方案。一条语句的查询是由I/O成本和CPU成本组成：

  • I/O成本： mysql中大多数存储引擎都是将数据和索引保存着磁盘中，当进行查询时需要将数据或者索引加载到内存中再进行操作。这个磁盘I/O的耗时称为I/O成本。
  • CPU成本：数据和索引加载到内存中以后需要进行一系列的操作，比如判断数据是否符合条件、某些情况下还需要对结果集进行排序等，这些操作的耗时成为CPU成本。

成本常数

mysql设定了一些基本操作的成本，我们称为成本常数，可以在mysql数据库中查看。
(后续内容都是基于版本：mysql 8.0.23，存储引擎：InnoDB)

mysql> use mysql;
Database changed

mysql> show tables like "%cost%";
+--------------------------+
| Tables_in_mysql (%cost%) |
+--------------------------+
| engine_cost              |
| server_cost              |
+--------------------------+
2 rows in set (0.00 sec)

mysql> select * from engine_cost;
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| engine_name | device_type | cost_name       | cost_value | last_update     | comment | default_value |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| default     |           0 | io_block_read_cost   |    NULL | 2021-03-09 14:06:10 | NULL  |       1 |
| default     |           0 | memory_block_read_cost |    NULL | 2021-03-09 14:06:10 | NULL  |     0.25 |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
2 rows in set (0.01 sec)

mysql> select * from server_cost;
+------------------------------+------------+---------------------+---------+---------------+
| cost_name          | cost_value | last_update     | comment | default_value |
+------------------------------+------------+---------------------+---------+---------------+
| disk_temptable_create_cost  |    NULL | 2021-03-09 14:06:10 | NULL  |      20 |
| disk_temptable_row_cost   |    NULL | 2021-03-09 14:06:10 | NULL  |      0.5 |
| key_compare_cost       |    NULL | 2021-03-09 14:06:10 | NULL  |     0.05 |
| memory_temptable_create_cost |    NULL | 2021-03-09 14:06:10 | NULL  |       1 |
| memory_temptable_row_cost  |    NULL | 2021-03-09 14:06:10 | NULL  |      0.1 |
| row_evaluate_cost      |    NULL | 2021-03-09 14:06:10 | NULL  |      0.1 |
+------------------------------+------------+---------------------+---------+---------------+
6 rows in set (0.00 sec)

可以看到和成本相关的有两个表，分别是server_cost和engine_cost两个表，其中server_cost表中是service层的成本参数，主要对应CPU成本；engine_cost表中是存储引擎的成本参数，主要对应I/O成本。其解释如下表。

基于成本的优化过程

mysql的优化过程其实就是在一条sql语句中真正地执行之前，mysql会找出该语句所有可能的执行方案，计算每个执行方案的执行成本然后选择其中成本最低的方案执行。
接下来我们以单表查询为例来讲解mysql的优化过程是如何进行的。

假设现在有一个表example_table，表信息如下，表中有10000条数据。

CREATE TABLE example_table (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2)
) Engine=InnoDB CHARSET=utf8;

执行的语句如下：

SELECT * FROM example_table WHERE 
    key1 IN ('a', 'b') AND 
    key2 > 10 AND key2 < 1000 AND 
    common_field = '123';

该语句可以使用的索引为idx_key1和idx_key2，那么执行方案就有三种：

  1. 全表扫描；
  2. 使用索引idx_key1；
  3. 使用索引idx_key2；

下面具体分析三种方案的执行成本。

全表扫描

全表扫描的过程很简单，就是做两件事：

  1. 将目标表的全部数据读取到内存中；
  2. 遍历筛选出符合条件的记录；

对InnoDB来说，就是将聚簇索引加载到内存中，然后进行遍历。那么对应的I/O成本就是将聚簇索引加载到内存中所产生的耗时，CPU成本就是遍历全部记录产生的耗时。

通过前面成本常数部分我们知道InnoDB将一页加载到内存中的成本为1，扫描一条记录的成本为0.1，只要知道该表占的页数m和该表的记录数n，就能得到全表扫描的执行成本。

那么这两个数据从哪里得到呢？mysql为每个表维护了一系列的统计信息，可通过以下命令进行查看。

mysql> show table status like "example_table"\G;

*************************** 1. row ***************************
      Name: student
     Engine: InnoDB
    Version: 10
   Row_format: Dynamic
      Rows: 9785
 Avg_row_length: 5461
  Data_length: 1376256
Max_data_length: 0
  Index_length: 1952512
   Data_free: 0
 Auto_increment: 10000
  Create_time: 2021-03-09 14:56:48
  Update_time: 2021-03-10 17:56:48
   Check_time: NULL
   Collation: utf8mb4_0900_ai_ci
    Checksum: NULL
 Create_options: 
    Comment: 
1 row in set (0.01 sec)

这里对我们有用的字段就是 Rows 和 Data_length。

  • Rows 表示目标表的行数，对于MyIASM来说该值是准确的，对InnoDB来说该值是近似值，这和具体的组织存储数据的方式有关。对InnoDB来说，mysql会选取一定数量的叶子结点计算得到每个叶子结点包含记录的平均数，然后乘以叶子结点的总数。
  • Data_length 表示该表所占空间的字节数，对InnoDB来说就相当于聚簇索引所占的字节数。

所以聚簇索引所占页面数为 1376256 ÷ 16 ÷ 1024 = 84 ；
I/O成本为 84 x 1 + 1.1 = 85.1 ，其中1.1为微调参数，不用关心；
CPU成本为 9785 * 0.1 + 1 = 979.5 ，其中1为微调参数，不用关心；
全表扫描的执行成本为 85.1 + 979.5 = 1064.6

使用索引idx_key1

索引idx_key1对应的筛选条件为 key1 IN (‘a’, ‘b’) ，mysql会将其转换为两个范围区间，即：

key1 IN ('a', 'b')  ==> 
"a" <= key1 <= "a" and "b" <= key1 <= "b"

对每个区间，mysql会做以下几件事，以区间"a" <= key1 <= "a"为例：

  1. 计算范围区间的记录数：
    根据索引idx_key1找到小于"a"的第一条记录，称为区间最左记录，再找到大于"a"的的第一条记录，称为区间最右记录；

    找到区间最左记录和区间最右记录中间的记录数，如果左右记录中间间隔的页面数小于等于10，那么mysql就会精确扫描得到记录数；如果左右记录中间间隔的页面数大于10，会根据区间最左记录向右扫描10个页面，计算得到平均每个页面的记录数，然后乘以左右记录之间的页面数得到估算值；

    以上的耗时都是忽略不计的；
  2. 将范围区间的二级索引读取到内存中并进行扫描：
    在这里mysql做了比较粗暴地简化，其认为不管范围区间多大，将范围区间读取到内存中成本相当于一个页面的I/O成本，即I/O成本为 1；
    对范围区间内的二级索引进行扫描，其会产生CPU成本，即 记录数*0.1 ；
  3. 根据二级索引进行回表：
    对于回表操作的成本，同样做了比较粗暴地简化，mysql认为每回表一次产生的I/O成本和访问一个页面相同，即回表的I/O成本为 记录数*1 ；
    如果覆盖索引的话就省掉回表的成本；
  4. 扫描回表数据：
    对回表得到的完整的记录进行其他条件的筛选，产生CPU成本 记录数*0.1 ；

可以得到使用索引idx_key1的执行为成本：1 + 350.1 + 351 + 350.1 + 1 + 440.1 + 441 + 440.1 = 97.8。(这里我把微调参数都省略了)

使用索引idx_key2

索引idx_key2对应的筛选条件为 key2 > 10 AND key2 < 1000，其成本计算过程的过程和上面一样，可以得到使用索引idx_key12的执行为成本：1 + 970.1 + 971 + 97*0.1 = 117.4

综合比较三种方案的成本得到成本最低的执行方案为 使用索引idx_key1 。

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

在前面所讲的使用索引的成本计算过程中，第一步会根据索引来计算范围区间：找到区间最左记录、区间最右记录，再得到区间中的记录数。mysql把这种方式称为index dive，除此之外还有一种方式 index statistics。

the optimizer can estimate the row count for each range using dives
into the index or index statistics.

index statistics只能用在非唯一二级索引列的筛选条件为单点区间的情况下使用，其利用mysql为每个索引维护的统计数据来估算单点区间的记录数。
我们先看下InnoDB为每个索引维护的统计数据。

mysql> show index from example;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| student |         0 | PRIMARY |      1        | id          | A         |     9785    |   NULL   |  NULL |       | BTREE      |         |               | YES     | NULL    |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| student |         1 | idx_key1 |      1       | key1        | A         |      1967    |   NULL   |  NULL |       | BTREE      |         |               | YES     | NULL    |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| student |         0 | idx_key2 |      1       | key2        | A         |      9785   |   NULL   |  NULL |       | BTREE      |         |               | YES     | NULL    |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)

在这些字段中我们比较关心的是Cardinality字段，这个字段表示的是索引列有多少不重复的值。对主键索引和唯一索引来说，cardinality的值和表统计数据中的rows相等。对非唯一的二级索引，以索引idx_key1为例，可以计算出每个值平均有多少条记录，即 rows ÷ cardinality 。
使用index statistics来计算单点区间中的记录也就非常简单粗暴了：
(单点区间数量) x (rows ÷ cardinality )

比较index dive和index statistics的优缺点：

  • index dive：耗时较多，但是数据相对精确；
  • index statistics：耗时较少，但是数据精确性很差；
    那mysql的优化器怎么判断什么时候用index dive，什么时候用index statistics呢？mysql提供了一个系统变量 eq_range_index_dive_limit ，当单点区间数量超过该值就使用index statistics。所以我们查询语句中，对于非唯一二级索引的筛选条件中，单点区间(一般都出现在 in 中)的数量不要超过200个，否则优化器的成本误差会很大。
mysql> SHOW VARIABLES LIKE '%dive%';
+---------------------------+-------+
| Variable_name       | Value |
+---------------------------+-------+
| eq_range_index_dive_limit | 200  |
+---------------------------+-------+
1 row in set (0.02 sec)
  1. 连接查询的成本
  • 两表连接：我们都知道mysql是通过nested loop的方式来实现两个表的连接，即驱动表扫描一次，被驱动表扫描n次。所以两表连接的成本 = 驱动表单次扫描的最小成本 + 被驱动表单次扫描的最小成本*n。
    对两表连接，左连接或者右连接的情况下驱动表是确定的，内连接的情况下mysql会分别以每个表作为驱动表计算成本，选取成本最低的执行方案。
  • 多表连接：多表连接的成本计算要考虑多表的连接顺序，对n个表的连接会有n！种情况，如果要对n！种情况的成本都进行计算那损耗会很大，所以mysql做了一系列的优化，例如提前结束某种顺序的计算，通过系统变量optimizer_search_depth来限制计算的表的个数等，这里就不展开。
  1. 其他成本
    这里补充下上文说到的limit命令的成本计算方式，后续可能会陆续补充些其他的命令比如order by等。

    • limit
      mysql对limit的成本计算非常粗糙，以下面的这条语句为例。

      mysql首先会查询该表的统计信息得到总行数rows，该表为3271652条。

      然后走索引拿到符合条件的记录数，该语句中为 166880。

之后mysql会非常粗糙地认为目标记录是均匀的分布在表中，即每 3271652 ÷ 166880 ≈ 19.6 条记录中存在一条目标记录，那么 limit 10的话就需要扫描 196 条记录，成本即为扫描196条记录。

# mysql
Logo
开放原子开发者工作坊

开放原子开发者工作坊旨在鼓励更多人参与开源活动，与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动，如meetup、训练营等，主打技术交流，干货满满，真诚地邀请各位开发者共同参与！

更多推荐

第二届开放原子大赛首批创新成果集结武汉，诚邀广大开发者共鉴开源技术盛宴

第二届开放原子大赛首批创新成果集结武汉，诚邀广大开发者共鉴开源技术盛宴

avatar 开放原子开发者工作坊

诚邀报名 | 开源基础设施能力建设分论坛：打造开源生态的“心脏”

诚邀报名 | 开源基础设施能力建设分论坛：打造开源生态的“心脏”

avatar 开放原子开发者工作坊

诚邀报名 | 编程语言分论坛：AI时代的技术革新与开源实践

诚邀报名 | 编程语言分论坛：AI时代的技术革新与开源实践

avatar 开放原子开发者工作坊