聊一下最近的故障——MySQL优化器的那点事 背景 问题 分

发表于

背景

最近在做一个资产迁移合并的工作，原先旧资产在A系统当中，要将这部分资产迁移至B系统。那么简单的做法就是B系统起一个任务，从A系统当中分页查询，得到结果然后插入到B系统当中。当然，双方系统背后的MySQL都是分库分表的。

问题

当然，这里我们不讨论数据丢失，事务一致性的问题。那么发生了什么问题呢？
在实际运行当中发现，部分用户的资产，查询会发生超时，导致这部分数据查询不出来。登上堡垒机，看到堆栈日志：

image.png
这个就跟明显，就是查询超时引起的。

分析

SQL查询超时问题，按照我的经验来看，一般都有三种思路：

  1. 数据库表数据量过大，导致SQL运行超时；
  2. 数据源配置文件配置的超时时间过短；
  3. SQL没有执行到索引；
    来分析分析：
  • 检查了一下库表，这个表的量级也就几百万，SQL本身也是比较简单，所以可以排除；
  • 数据源配置文件的超时时间是5s，鉴于其他业务查询倒是正常，那么也可以排除；
    这样一来，那么很明显，就是索引的缘故了。打开explain之后，发现走了另外一个索引！但是sql的写法是按照我们预期的索引来写的！

那么索引为什么会失效了？传统的排查方式，那就是看下SQL本身，是否符合最左匹配原则，另外就是查看下SQL索引字段是不是用了函数操作；当然，既然我在这里说了，肯定是排除了以上的情况。那么是什么原因呢？这也是要进入到我们这次的话题。

实验

各位发现，在上面的描述中，我都没有贴出相关的SQL以及库表结构，这个还是为了保护公司相关的业务以及代码吧。但是这个不妨碍我继续给各位准备下面的实验数据(MySQL是基于5.7版本）。
以下是我的步骤：

  1. 首先创建一张表
1
2
3
4
5
6
7
8
9
10
sql复制代码CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT '',
  `sex` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `stuNo` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_stuNo` (`stuNo`)
) ENGINE=InnoDB AUTO_INCREMENT=20001 DEFAULT CHARSET=utf8;

这里呢，创建了一张student的表，里面建立了两个索引idx_name和idx_stuNo

  1. 为这张表插入数据，这里我插入了20000条数据。（这里我就不演示怎么插入数据了，显得有点蠢）
  2. 准备一条sql
1
sql复制代码select stuNo from student  where stuNo > '100' and stuNo < '30000000'

那么我们来看下他的explain执行计划

1
sql复制代码explain select stuNo from student  where stuNo > '100' and stuNo < '30000000';

结果如下：

image.png
可以发现，这个sql是执行到idx_stuNo这个索引，好像也没有什么大毛病。接下来我们修改一下sql

1
sql复制代码select * from student  where stuNo > '100' and stuNo < '30000000'

我们再来看下他的explain执行计划

1
sql复制代码explain select * from student  where stuNo > '100' and stuNo < '30000000';

image.png
我的天，这里竟然走了全表的查询，没有走索引！

成本模型

索引的作用，就是用来帮我们提高查询效率的。那么当一张表当中有多个索引的时候，MySQL是如何选择正确的搜索路径呢？这里就会引入一个成本模型的概念，这里只探讨一下SQL查询的情况。

SQL查询成本 = IO成本 + CPU成本

全表扫描

首先我们从最简单的情况，全表扫描开始说起。为什么说全表扫描最简单呢，这是因为全表扫描就是基于源表依次遍历，那么这里的IO成本就是计算获取了多少页，CPU成本就是读取了多少行。
于是可得

IO成本 = 查询的页数 * 1.0 + 1.1

CPU成本 = 行数 * 0.2 + 1.0

这里的0.2和1.0都是计算常数（怎么决定的这个肯定是经验值），这里的+1.0和+1.1也是微调值。
第二条SQL，因为是走了全表扫描，说明全表扫描的成本最低，那我们来计算一下：

  1. 首选是获取查询的页数，这个可以根据查询表自身信息可得

show table status like ‘student’

image.png
可以看到Data_length为1589248，那么1589248 / 16 / 1024 = 97（因为默认页大小就是16k），可以得到总页数为97页，需要执行97次IO。总行数就是19980。于是可以得到

IO成本 = 97 * 1.0 + 1.1 = 98.1

CPU成本 = 19980 * 0.2 + 1 = 3997

总成本 = 98.1 + 3997 = 4095.1

索引扫描

索引扫描表查询的时候，它的成本计算，总的来说也是按照IO成本+CPU成本，只是可以分为两个阶段：索引成本 + 数据成本。索引成本指的是获取索引所付出的成本，而数据成本，是在需要回表的时候才需要涉及。我们先来探讨下索引成本：

IO成本 = 索引的扫描区间 * 1.0

CPU成本 = 索引记录数 * 0.2 + 0.01

由于是范围查找，MySQL会认为读取索引的一个扫描区间的I/O成本和读取一个页面的I/O成本是相同的，所以这里的IO成本就是1 * 1.0 = 1；那么这里怎么计算出索引记录数的呢？

  1. 先根据stuNo > 100这个条件访问一下idx_stuNo对应的B+树索引，找到满足stuNo > 100这个条件的第一条记录（我们把这条记录称之为区间最左记录）。在B+数树中定位一条记录是非常快的，是常数级别的，这个过程的性能消耗是可以忽略不计。
  2. 再根据stuNo < 30000000这个条件继续从idx_stuNo对应的B+树索引中找出最后一条满足这个条件的记录（我们把这条记录称之为区间最右记录），这个过程的性能消耗同样可以忽略不计的。
  3. 如果区间最左记录和区间最右记录相隔不太远（在MySQL 5.7.22这个版本里，只要相隔不大于10个页面即可），就可以精确统计出满足100 < stuNo < 30000000条件的二级索引记录条数。否则需要沿着区间最左记录向右读10个页面，计算每个页面平均包含多少记录，然后用这个平均值乘以区间最左记录和区间最右记录之间的页面数量就可以了。

根据上面的操作可以知道这里的索引记录数为5119。那么关于索引成本为

IO成本 = 1 * 1.0 = 1

CPU成本 = 5119 * 0.2 + 0.01 = 1023.81

总成本 = 1 + 1023.81 = 1024.81

我们在使用索引的时候经常会听到一种说法——回表，没错。上面的成本是只索引的成本，当我们从索引当中获取数据的时候并不能满足SQL查询的要求，就会进行回表操作，而这里的回表操作就有点类似于上面的全表扫描

IO成本 = 索引对应数据条数 * 1.0

CPU成本 = 索引对应数据条数 * 0.2

MySQL评估回表操作的I/O成本依旧很粗略，MySQL认为每次回表操作都相当于访问一个页面。所以这里可以计算出成本为：

IO成本 = 5119 * 1.0 = 5119

CPU成本 = 5119 * 0.2 = 1023.8

总成本 = 5119 + 1023.8 = 6142.8

最后，两部分加起来的总成本为：1024.81 + 6142.8 = 7167.61。

小结

由此可以看出，在这种情况下，全表扫描的成本更低。当然我们从这里也可以看到，回表操作带来的代价还是比较高的。各位也可以打开optimizer_trace查看最详细的结果。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
sql复制代码mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE \G;
*************************** 1. row ***************************
                            QUERY: select * from student  where stuNo > '100' and stuNo < '30000000'
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `student`.`id` AS `id`,`student`.`name` AS `name`,`student`.`sex` AS `sex`,`student`.`age` AS `age`,`student`.`stuNo` AS `stuNo` from `student` where ((`student`.`stuNo` > '100') and (`student`.`stuNo` < '30000000'))"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`student`.`stuNo` > '100') and (`student`.`stuNo` < '30000000'))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`student`.`stuNo` > '100') and (`student`.`stuNo` < '30000000'))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`student`.`stuNo` > '100') and (`student`.`stuNo` < '30000000'))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`student`.`stuNo` > '100') and (`student`.`stuNo` < '30000000'))"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`student`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`student`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 19980,
                    "cost": 4095.1
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_name",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_stuNo",
                      "usable": true,
                      "key_parts": [
                        "stuNo",
                        "id"
                      ]
                    }
                  ],
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "idx_stuNo",
                        "ranges": [
                          "100 < stuNo < 30000000"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 5119,
                        "cost": 6143.8,  -- 这里是索引成本
                        "chosen": false,
                        "cause": "cost"
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`student`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 19980,
                      "access_type": "scan",
                      "resulting_rows": 19980,
                      "cost": 4093,
                      "chosen": true
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 19980,
                "cost_for_plan": 4093,
                "chosen": true
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`student`.`stuNo` > '100') and (`student`.`stuNo` < '30000000'))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`student`",
                  "attached": "((`student`.`stuNo` > '100') and (`student`.`stuNo` < '30000000'))"
                }
              ]
            }
          },
          {
            "refine_plan": [
              {
                "table": "`student`"
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

最后

尽管MySQL的优化器给我们还是带来极大的帮助，但是在某些情况下，计算模型不一定会覆盖到我们的真实效果，所以在这类事情发生的时候，也多了一种排查思路。某些情况下，也可以使用force index的方式来实现~

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%