Contents
  1. 1. 本篇为执行计划错误系列第二篇,第一篇地址
  2. 2. 背景
    1. 2.1. MySQL版本
    2. 2.2. 表结构
    3. 2.3. index 选择性
    4. 2.4. 问题SQL
    5. 2.5. 问题再哪里?
    6. 2.6. 继续分析
    7. 2.7. 最后的优化建议:
    8. 2.8. 疑问
  3. 3. 最后总结
  4. 4. 详细的执行代价评估

本篇为执行计划错误系列第二篇,第一篇地址

背景


MySQL版本

社区版: MySQL 5.6.27 InnoDB

表结构

| test_table | CREATE TABLE test_table (
TagId int(11) NOT NULL AUTO_INCREMENT COMMENT ‘’,
TagType int(11) DEFAULT NULL COMMENT ‘’,
SubType int(11) DEFAULT NULL COMMENT ‘’,
CommId int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘’,
TagFlag int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘’,
TagName varchar(255) DEFAULT NULL COMMENT ‘’,
OrderId int(11) DEFAULT ‘0’ COMMENT ‘’,
Unum int(10) NOT NULL DEFAULT ‘0’ COMMENT ‘’,
IsBest int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘’,
BrokerId int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘’,
AddDate int(11) DEFAULT NULL COMMENT ‘’,
UpdateDate int(11) DEFAULT NULL COMMENT ‘’,
updatetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
tmpnum int(10) DEFAULT ‘0’ COMMENT ‘’,
cityid int(11) DEFAULT ‘0’ COMMENT ‘’,
PRIMARY KEY (TagId),
KEY idx_4 (IsBest,TagFlag,CommId),
KEY idx_1 (TagType,TagName,CommId),
KEY idxnew (UpdateDate),
KEY idx_lc (TagType,TagName,TagId)
KEY idx_lc_2 (TagName,TagType,TagId),
) ENGINE=InnoDB AUTO_INCREMENT=19387169 DEFAULT CHARSET=utf8 |

index 选择性

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
+-----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test_table | 0 | PRIMARY | 1 | TagId | A | 1128048 | NULL | NULL | | BTREE | | |
| test_table | 1 | idx_4 | 1 | IsBest | A | 2 | NULL | NULL | | BTREE | | |
| test_table | 1 | idx_4 | 2 | TagFlag | A | 4 | NULL | NULL | | BTREE | | |
| test_table | 1 | idx_4 | 3 | CommId | A | 56402 | NULL | NULL | | BTREE | | |
| test_table | 1 | idx_1 | 1 | TagType | A | 2 | NULL | NULL | YES | BTREE | | |
| test_table | 1 | idx_1 | 2 | TagName | A | 1128048 | NULL | NULL | YES | BTREE | | |
| test_table | 1 | idx_1 | 3 | TagId | A | 1128048 | NULL | NULL | | BTREE | | |
| test_table | 1 | idxnew | 1 | UpdateDate | A | 1128048 | NULL | NULL | YES | BTREE | | |
| test_table | 1 | idx_lc | 1 | TagType | A | 2 | NULL | NULL | YES | BTREE | | |
| test_table | 1 | idx_lc | 2 | TagName | A | 1128048 | NULL | NULL | YES | BTREE | | |
| test_table | 1 | idx_lc | 3 | TagId | A | 1128048 | NULL | NULL | | BTREE | | |
| test_table | 1 | idx_lc_2 | 1 | TagName | A | 1128048 | NULL | NULL | YES | BTREE | | |
| test_table | 1 | idx_lc_2 | 2 | TagType | A | 1128048 | NULL | NULL | YES | BTREE | | |
| test_table | 1 | idx_lc_2 | 3 | TagId | A | 1128048 | NULL | NULL | | BTREE | | |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+-----------------+

问题SQL

select * from test_db.test_table where TagType = ‘1’ and TagName = ‘采光充足’ order by TagId limit 1;

问题再哪里?

  • 执行计划

root:test_db> explain select * from test_db.test_table where TagType = 1 and TagName = ‘采光充足’ order by TagId limit 1;
+——+——————-+————————-+———-+———————-+————-+————-+———+———+——————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+——+——————-+————————-+———-+———————-+————-+————-+———+———+——————-+
| 1 | SIMPLE | test_table | index | idx_1,idx_lc,idx_lc_2 | PRIMARY | 4 | NULL | 386 | Using where |
+——+——————-+————————-+———-+———————-+————-+————-+———+———+——————-+
1 row in set (0.00 sec)

Time: 160313 0:07:16
User@Host: [] @ [] Id: 7123914939
Query_time: 0.246356 Lock_time: 0.000036 Rows_sent: 1 Rows_examined: 470135
SET timestamp=1457798836;
select TagId,TagType,SubType,CommId,TagFlag,TagName,OrderId,Unum,IsBest,BrokerId,AddDate,UpdateDate,updatetime,tmpnum,cityid from test_table where TagType = 1 and TagName = ‘采光充足’ order by TagId ASC limit 1 ;

  • 痛点分析

1)primary , type=index,表示全索引扫描。

2) 按照索引原理,这样的SQL语句应该使用的正确的索引是:idx_lc, type=ref。

root:test_db> explain select * from test_db.test_table force index(idx_lc) where TagType = 1 and TagName = ‘采光充足’ order by TagId limit 1;

1
2
3
4
5
6
+----+-------------+-----------------+------+---------------+--------+---------+-------------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------+---------------+--------+---------+-------------+------+------------------------------------+
| 1 | SIMPLE | test_table | ref | idx_lc | idx_lc | 773 | const,const | 2930 | Using index condition; Using where |
+----+-------------+-----------------+------+---------------+--------+---------+-------------+------+------------------------------------+
1 row in set (0.00 sec)

3)到这里,肯定大部分人问,既然force index可以解决问题,为啥不让开发修改SQL为force index呢?

a) 能不让开发修改,就不让开发修改,这样成本最低。

b)况且,这根本就不是SQL语句本身的问题,为什么要修改SQL呢?

c)如果以后不小心删掉了这个索引,那么force index的语句就会报错,会导致业务中断。

以上种种原因,force index 始终不是最终的积极方案。

那么问题来了

使用idx_lc代价最小,速度最快。 为什么MySQL不用呢?

继续分析

  • 如果观察仔细的小伙伴们会发现,以上的索引有很多是重复的

    PRIMARY KEY (TagId),
    KEY idx_4 (IsBest,TagFlag,CommId),
    KEY idx_1 (TagType,TagName,CommId),
    KEY idxnew (UpdateDate),
    KEY idx_lc (TagType,TagName,TagId)
    KEY idx_lc_2 (TagName,TagType,TagId),

  • possibles key 的选择问题

为什么在 idx_1,idx_lc,idx_lc_2 中 却选择的 primary key 呢?

  • 我试着删掉一个索引试试看

root:test_db> alter table test_table drop index idx_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
root:test_db> explain select * from test_db.test_table force index(idx_lc) where TagType = 1 and TagName = ‘采光充足’ order by TagId limit 1;
+——+——————-+————————-+———+———————-+————+————-+——————-+———+——————————————————+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+——+——————-+————————-+———+———————-+————+————-+——————-+———+——————————————————+
| 1 | SIMPLE | test_table | ref | idx_lc,idx_lc_2 | idx_lc | 773 | const,const | 2930 | Using index condition; Using where |
+——+——————-+————————-+———+———————-+————+————-+——————-+———+——————————————————+
1 row in set (0.00 sec)

这样,执行语句奇迹般的好了。让我开始对index 的顺序问题产生怀疑

  • ok,我在重建idx_1索引

    PRIMARY KEY (TagId),
    KEY idx_4 (IsBest,TagFlag,CommId),
    KEY idxnew (UpdateDate),
    KEY idx_lc (TagType,TagName,TagId)
    KEY idx_lc_2 (TagName,TagType,TagId),
    KEY idx_1 (TagType,TagName,CommId),

1
2
3
4
5
6
7
root:test_db> explain select * from test_db.test_table force index(idx_lc) where `TagType` = 1 and `TagName` = '采光充足' order by TagId limit 1;
+----+-------------+-----------------+------+---------------+--------+---------+-------------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------+---------------+--------+---------+-------------+------+------------------------------------+
| 1 | SIMPLE | test_table | ref | idx_lc,idx_lc_2,idx_1 | idx_lc | 773 | const,const | 2930 | Using index condition; Using where |
+----+-------------+-----------------+------+---------------+--------+---------+-------------+------+------------------------------------+
1 row in set (0.00 sec)
  • 我再将idx_lc 删掉后再重建呢?

    PRIMARY KEY (TagId),
    KEY idx_4 (IsBest,TagFlag,CommId),
    KEY idxnew (UpdateDate),
    KEY idx_lc_2 (TagType,TagName,TagId)
    KEY idx_1 (TagType,TagName,CommId),
    KEY idx_lc (TagType,TagName,TagId)

1
2
3
4
5
6
7
root:test_db> explain select * from test_db.test_table force index(idx_lc) where `TagType` = 1 and `TagName` = '采光充足' order by TagId limit 1;
+----+-------------+-----------------+------+---------------+--------+---------+-------------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------+---------------+--------+---------+-------------+------+------------------------------------+
| 1 | SIMPLE | test_table | ref | idx_lc_2,idx_1,idx_lc | idx_lc | 773 | const,const | 2930 | Using index condition; Using where |
+----+-------------+-----------------+------+---------------+--------+---------+-------------+------+------------------------------------+
1 row in set (0.00 sec)

最后的优化建议:

1
2
删除掉 idx_1 干扰索引。

疑问

MySQL不至于这么傻吧?应该不是根据RBO哇,说好的CBO呢?

所以,我又在test库中创新创建了一张表,表结构和原来一样,数据完全导入到过来

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
use test;
create table lc_test like xx_db.test_table;
insert into lc_test select * from xx_db.test_table;
Query OK, 1137914 rows affected (25.30 sec)
Records: 1137914 Duplicates: 0 Warnings: 0
| lc_test | CREATE TABLE `lc_test` (
`TagId` int(11) NOT NULL AUTO_INCREMENT COMMENT '',
`TagType` int(11) DEFAULT NULL COMMENT '',
`SubType` int(11) DEFAULT NULL COMMENT '',
`CommId` int(11) NOT NULL DEFAULT '0' COMMENT '',
`TagFlag` int(11) NOT NULL DEFAULT '0' COMMENT '',
`TagName` varchar(255) DEFAULT NULL COMMENT '',
`OrderId` int(11) DEFAULT '0' COMMENT '',
`Unum` int(10) NOT NULL DEFAULT '0' COMMENT '',
`IsBest` int(11) NOT NULL DEFAULT '0' COMMENT '0',
`BrokerId` int(11) NOT NULL DEFAULT '0' COMMENT '',
`AddDate` int(11) DEFAULT NULL COMMENT '',
`UpdateDate` int(11) DEFAULT NULL COMMENT '',
`updatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`tmpnum` int(10) DEFAULT '0' COMMENT '',
`cityid` int(11) DEFAULT '0' COMMENT '',
PRIMARY KEY (`TagId`),
KEY `idx_4` (`IsBest`,`TagFlag`,`CommId`),
KEY `idx_1` (`TagType`,`TagName`,`CommId`),
KEY `idxnew` (`UpdateDate`),
KEY `idx_lc` (`TagType`,`TagName`,`TagId`),
KEY `idx_lc_2` (`TagName`,`TagType`,`TagId`)
) ENGINE=InnoDB AUTO_INCREMENT=19387105 DEFAULT CHARSET=utf8 |
root:test> explain select * from lc_test where `TagType` = 1 and `TagName` = '采光充足' order by TagId limit 1;
+----+-------------+---------+------+-----------------------+--------+---------+-------------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+-----------------------+--------+---------+-------------+------+------------------------------------+
| 1 | SIMPLE | lc_test | ref | idx_1,idx_lc,idx_lc_2 | idx_lc | 773 | const,const | 2928 | Using index condition; Using where |
+----+-------------+---------+------+-----------------------+--------+---------+-------------+------+------------------------------------+
1 row in set (0.00 sec)
这里,虽然顺序是 idx_1,idx_lc,idx_lc_2, 但是它选择了 idx_lc 正确的索引。
这又说明,并不是我们想象的按照索引顺序选择。

最后总结

  1. 从上述测试来看,MySQL执行计划应该是 CBO + CRO的结合体 ? 待确认
  2. 一般情况下,MySQL可以很好的选择执行计划,但是收到重复索引,数据分布等的干扰后,可能不准确
  3. 我们能做的就是
    • 等待官方优化器的改进
    • 尽量避开干扰项,如:主动删除重复性索引等

详细的执行代价评估

  • 错误的选择
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
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
{\
"steps": [\
{\
"join_preparation": {\
"select#": 1,\
"steps": [\
{\
"expanded_query": "/* select#1 */ select `test_table`.`TagId` AS `TagId`,`test_table`.`TagType` AS `TagType`,`test_table`.`SubType` AS `SubType`,`test_table`.`CommId` AS `CommId`,`test_table`.`TagFlag` AS `TagFlag`,`test_table`.`TagName` AS `TagName`,`test_table`.`OrderId` AS `OrderId`,`test_table`.`Unum` AS `Unum`,`test_table`.`IsBest` AS `IsBest`,`test_table`.`BrokerId` AS `BrokerId`,`test_table`.`AddDate` AS `AddDate`,`test_table`.`UpdateDate` AS `UpdateDate`,`test_table`.`updatetime` AS `updatetime`,`test_table`.`tmpnum` AS `tmpnum`,`test_table`.`cityid` AS `cityid` from `test_table` where ((`test_table`.`TagType` = 1) and (`test_table`.`TagName` = '采光充足')) order by `test_table`.`TagId` limit 1"\
}\
]\
}\
},\
{\
"join_optimization": {\
"select#": 1,\
"steps": [\
{\
"condition_processing": {\
"condition": "WHERE",\
"original_condition": "((`test_table`.`TagType` = 1) and (`test_table`.`TagName` = '采光充足'))",\
"steps": [\
{\
"transformation": "equality_propagation",\
"resulting_condition": "((`test_table`.`TagName` = '采光充足') and multiple equal(1, `test_table`.`TagType`))"\
},\
{\
"transformation": "constant_propagation",\
"resulting_condition": "((`test_table`.`TagName` = '采光充足') and multiple equal(1, `test_table`.`TagType`))"\
},\
{\
"transformation": "trivial_condition_removal",\
"resulting_condition": "((`test_table`.`TagName` = '采光充足') and multiple equal(1, `test_table`.`TagType`))"\
}\
]\
}\
},\
{\
"table_dependencies": [\
{\
"table": "`test_table`",\
"row_may_be_null": false,\
"map_bit": 0,\
"depends_on_map_bits": [\
]\
}\
]\
},\
{\
"ref_optimizer_key_uses": [\
{\
"table": "`test_table`",\
"field": "TagType",\
"equals": "1",\
"null_rejecting": false\
},\
{\
"table": "`test_table`",\
"field": "TagName",\
"equals": "'采光充足'",\
"null_rejecting": false\
},\
{\
"table": "`test_table`",\
"field": "TagType",\
"equals": "1",\
"null_rejecting": false\
},\
{\
"table": "`test_table`",\
"field": "TagName",\
"equals": "'采光充足'",\
"null_rejecting": false\
},\
{\
"table": "`test_table`",\
"field": "TagName",\
"equals": "'采光充足'",\
"null_rejecting": false\
},\
{\
"table": "`test_table`",\
"field": "TagType",\
"equals": "1",\
"null_rejecting": false\
}\
]\
},\
{\
"rows_estimation": [\
{\
"table": "`test_table`",\
"range_analysis": {\
"table_scan": {\
"rows": 1132201,\
"cost": 233202\
},\
"potential_range_indices": [\
{\
"index": "PRIMARY",\
"usable": false,\
"cause": "not_applicable"\
},\
{\
"index": "idx_4",\
"usable": false,\
"cause": "not_applicable"\
},\
{\
"index": "idx_1",\
"usable": true,\
"key_parts": [\
"TagType",\
"TagName",\
"CommId",\
"TagId"\
]\
},\
{\
"index": "idxnew",\
"usable": false,\
"cause": "not_applicable"\
},\
{\
"index": "idx_lc",\
"usable": true,\
"key_parts": [\
"TagType",\
"TagName",\
"TagId"\
]\
},\
{\
"index": "idx_lc_2",\
"usable": true,\
"key_parts": [\
"TagName",\
"TagType",\
"TagId"\
]\
}\
],\
"setup_range_conditions": [\
],\
"group_index_range": {\
"chosen": false,\
"cause": "not_group_by_or_distinct"\
},\
"analyzing_range_alternatives": {\
"range_scan_alternatives": [\
{\
"index": "idx_1",\
"ranges": [\
"1 <= TagType <= 1 AND 采光充足 <= TagName <= 采光充足"\
],\
"index_dives_for_eq_ranges": true,\
"rowid_ordered": false,\
"using_mrr": false,\
"index_only": false,\
"rows": 2924,\
"cost": 3509.8,\
"chosen": true\
},\
{\
"index": "idx_lc",\
"ranges": [\
"1 <= TagType <= 1 AND 采光充足 <= TagName <= 采光充足"\
],\
"index_dives_for_eq_ranges": true,\
"rowid_ordered": true,\
"using_mrr": false,\
"index_only": false,\
"rows": 2924,\
"cost": 3509.8,\
"chosen": false,\
"cause": "cost"\
},\
{\
"index": "idx_lc_2",\
"ranges": [\
"采光充足 <= TagName <= 采光充足 AND 1 <= TagType <= 1"\
],\
"index_dives_for_eq_ranges": true,\
"rowid_ordered": true,\
"using_mrr": false,\
"index_only": false,\
"rows": 2924,\
"cost": 3509.8,\
"chosen": false,\
"cause": "cost"\
}\
],\
"analyzing_roworder_intersect": {\
"intersecting_indices": [\
{\
"index": "idx_lc",\
"index_scan_cost": 266.73,\
"cumulated_index_scan_cost": 266.73,\
"disk_sweep_cost": 2771.5,\
"cumulated_total_cost": 3038.2,\
"usable": true,\
"matching_rows_now": 2924,\
"isect_covering_with_this_index": false,\
"chosen": true\
},\
{\
"index": "idx_lc_2",\
"cumulated_total_cost": 3038.2,\
"usable": false,\
"cause": "does_not_reduce_cost_of_intersect"\
}\
],\
"clustered_pk": {\
"clustered_pk_added_to_intersect": false,\
"cause": "no_clustered_pk_index"\
},\
"chosen": false,\
"cause": "too_few_indexes_to_merge"\
}\
},\
"chosen_range_access_summary": {\
"range_access_plan": {\
"type": "range_scan",\
"index": "idx_1",\
"rows": 2924,\
"ranges": [\
"1 <= TagType <= 1 AND 采光充足 <= TagName <= 采光充足"\
]\
},\
"rows_for_plan": 2924,\
"cost_for_plan": 3509.8,\
"chosen": true\
}\
}\
}\
]\
},\
{\
"considered_execution_plans": [\
{\
"plan_prefix": [\
],\
"table": "`test_table`",\
"best_access_path": {\
"considered_access_paths": [\
{\
"access_type": "ref",\
"index": "idx_1",\
"rows": 2924,\
"cost": 3508.8,\
"chosen": true\
},\
{\
"access_type": "ref",\
"index": "idx_lc",\
"rows": 2924,\
"cost": 3508.8,\
"chosen": false\
},\
{\
"access_type": "ref",\
"index": "idx_lc_2",\
"rows": 2924,\
"cost": 3508.8,\
"chosen": false\
},\
{\
"access_type": "range",\
"cause": "heuristic_index_cheaper",\
"chosen": false\
}\
]\
},\
"cost_for_plan": 3508.8,\
"rows_for_plan": 2924,\
"chosen": true\
}\
]\
},\
{\
"attaching_conditions_to_tables": {\
"original_condition": "((`test_table`.`TagType` = 1) and (`test_table`.`TagName` = '采光充足'))",\
"attached_conditions_computation": [\
],\
"attached_conditions_summary": [\
{\
"table": "`test_table`",\
"attached": "(`test_table`.`TagName` = '采光充足')"\
}\
]\
}\
},\
{\
"clause_processing": {\
"clause": "ORDER BY",\
"original_clause": "`test_table`.`TagId`",\
"items": [\
{\
"item": "`test_table`.`TagId`"\
}\
],\
"resulting_clause_is_simple": true,\
"resulting_clause": "`test_table`.`TagId`"\
}\
},\
{\
"refine_plan": [\
{\
"table": "`test_table`",\
"pushed_index_condition": "(`test_table`.`TagName` = '采光充足')",\
"table_condition_attached": null\
}\
]\
},\
{\
"added_back_ref_condition": "((`test_table`.`TagType` <=> 1) and (`test_table`.`TagName` <=> '采光充足'))"\
},\
{\
"reconsidering_access_paths_for_index_ordering": {\
"clause": "ORDER BY",\
"index_order_summary": {\
"table": "`test_table`",\
"index_provides_order": true,\
"order_direction": "asc",\
"disabled_pushed_condition_on_old_index": true,\
"index": "PRIMARY",\
"plan_changed": true,\
"access_type": "index_scan"\
}\
}\
}\
]\
}\
},\
{\
"join_execution": {\
"select#": 1,\
"steps": [\
]\
}\
}\
]\
}
  • 正确的选择
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
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
{\
"steps": [\
{\
"join_preparation": {\
"select#": 1,\
"steps": [\
{\
"expanded_query": "/* select#1 */ select `test_table`.`TagId` AS `TagId`,`test_table`.`TagType` AS `TagType`,`test_table`.`SubType` AS `SubType`,`test_table`.`CommId` AS `CommId`,`test_table`.`TagFlag` AS `TagFlag`,`test_table`.`TagName` AS `TagName`,`test_table`.`OrderId` AS `OrderId`,`test_table`.`Unum` AS `Unum`,`test_table`.`IsBest` AS `IsBest`,`test_table`.`BrokerId` AS `BrokerId`,`test_table`.`AddDate` AS `AddDate`,`test_table`.`UpdateDate` AS `UpdateDate`,`test_table`.`updatetime` AS `updatetime`,`test_table`.`tmpnum` AS `tmpnum`,`test_table`.`cityid` AS `cityid` from `test_table` FORCE INDEX (`idx_lc`) where ((`test_table`.`TagType` = 1) and (`test_table`.`TagName` = '采光充足')) order by `test_table`.`TagId` limit 1"\
}\
]\
}\
},\
{\
"join_optimization": {\
"select#": 1,\
"steps": [\
{\
"condition_processing": {\
"condition": "WHERE",\
"original_condition": "((`test_table`.`TagType` = 1) and (`test_table`.`TagName` = '采光充足'))",\
"steps": [\
{\
"transformation": "equality_propagation",\
"resulting_condition": "((`test_table`.`TagName` = '采光充足') and multiple equal(1, `test_table`.`TagType`))"\
},\
{\
"transformation": "constant_propagation",\
"resulting_condition": "((`test_table`.`TagName` = '采光充足') and multiple equal(1, `test_table`.`TagType`))"\
},\
{\
"transformation": "trivial_condition_removal",\
"resulting_condition": "((`test_table`.`TagName` = '采光充足') and multiple equal(1, `test_table`.`TagType`))"\
}\
]\
}\
},\
{\
"table_dependencies": [\
{\
"table": "`test_table` FORCE INDEX (`idx_lc`)",\
"row_may_be_null": false,\
"map_bit": 0,\
"depends_on_map_bits": [\
]\
}\
]\
},\
{\
"ref_optimizer_key_uses": [\
{\
"table": "`test_table` FORCE INDEX (`idx_lc`)",\
"field": "TagType",\
"equals": "1",\
"null_rejecting": false\
},\
{\
"table": "`test_table` FORCE INDEX (`idx_lc`)",\
"field": "TagName",\
"equals": "'采光充足'",\
"null_rejecting": false\
}\
]\
},\
{\
"rows_estimation": [\
{\
"table": "`test_table` FORCE INDEX (`idx_lc`)",\
"range_analysis": {\
"table_scan": {\
"rows": 1131547,\
"cost": 2e308\
},\
"potential_range_indices": [\
{\
"index": "PRIMARY",\
"usable": false,\
"cause": "not_applicable"\
},\
{\
"index": "idx_4",\
"usable": false,\
"cause": "not_applicable"\
},\
{\
"index": "idxnew",\
"usable": false,\
"cause": "not_applicable"\
},\
{\
"index": "idx_lc",\
"usable": true,\
"key_parts": [\
"TagType",\
"TagName",\
"TagId"\
]\
}\
],\
"setup_range_conditions": [\
],\
"group_index_range": {\
"chosen": false,\
"cause": "not_group_by_or_distinct"\
},\
"analyzing_range_alternatives": {\
"range_scan_alternatives": [\
{\
"index": "idx_lc",\
"ranges": [\
"1 <= TagType <= 1 AND 采光充足 <= TagName <= 采光充足"\
],\
"index_dives_for_eq_ranges": true,\
"rowid_ordered": true,\
"using_mrr": false,\
"index_only": false,\
"rows": 2931,\
"cost": 3518.2,\
"chosen": true\
}\
],\
"analyzing_roworder_intersect": {\
"usable": false,\
"cause": "too_few_roworder_scans"\
}\
},\
"chosen_range_access_summary": {\
"range_access_plan": {\
"type": "range_scan",\
"index": "idx_lc",\
"rows": 2931,\
"ranges": [\
"1 <= TagType <= 1 AND 采光充足 <= TagName <= 采光充足"\
]\
},\
"rows_for_plan": 2931,\
"cost_for_plan": 3518.2,\
"chosen": true\
}\
}\
}\
]\
},\
{\
"considered_execution_plans": [\
{\
"plan_prefix": [\
],\
"table": "`test_table` FORCE INDEX (`idx_lc`)",\
"best_access_path": {\
"considered_access_paths": [\
{\
"access_type": "ref",\
"index": "idx_lc",\
"rows": 2931,\
"cost": 3517.2,\
"chosen": true\
},\
{\
"access_type": "range",\
"cause": "heuristic_index_cheaper",\
"chosen": false\
}\
]\
},\
"cost_for_plan": 3517.2,\
"rows_for_plan": 2931,\
"chosen": true\
}\
]\
},\
{\
"attaching_conditions_to_tables": {\
"original_condition": "((`test_table`.`TagType` = 1) and (`test_table`.`TagName` = '采光充足'))",\
"attached_conditions_computation": [\
],\
"attached_conditions_summary": [\
{\
"table": "`test_table` FORCE INDEX (`idx_lc`)",\
"attached": "(`test_table`.`TagName` = '采光充足')"\
}\
]\
}\
},\
{\
"clause_processing": {\
"clause": "ORDER BY",\
"original_clause": "`test_table`.`TagId`",\
"items": [\
{\
"item": "`test_table`.`TagId`"\
}\
],\
"resulting_clause_is_simple": true,\
"resulting_clause": "`test_table`.`TagId`"\
}\
},\
{\
"refine_plan": [\
{\
"table": "`test_table` FORCE INDEX (`idx_lc`)",\
"pushed_index_condition": "(`test_table`.`TagName` = '采光充足')",\
"table_condition_attached": null\
}\
]\
},\
{\
"added_back_ref_condition": "((`test_table`.`TagType` <=> 1) and (`test_table`.`TagName` <=> '采光充足'))"\
},\
{\
"reconsidering_access_paths_for_index_ordering": {\
"clause": "ORDER BY",\
"index_order_summary": {\
"table": "`test_table` FORCE INDEX (`idx_lc`)",\
"index_provides_order": true,\
"order_direction": "asc",\
"index": "idx_lc",\
"plan_changed": false\
}\
}\
}\
]\
}\
},\
{\
"join_execution": {\
"select#": 1,\
"steps": [\
]\
}\
}\
]\
}
Contents
  1. 1. 本篇为执行计划错误系列第二篇,第一篇地址
  2. 2. 背景
    1. 2.1. MySQL版本
    2. 2.2. 表结构
    3. 2.3. index 选择性
    4. 2.4. 问题SQL
    5. 2.5. 问题再哪里?
    6. 2.6. 继续分析
    7. 2.7. 最后的优化建议:
    8. 2.8. 疑问
  3. 3. 最后总结
  4. 4. 详细的执行代价评估

幸福,不在于得到的多

而在于计较的少