MYSQL 执行计划分析三看， EXPLAIN,PROFILING,OPTIMIZER_TRACE

た入场券 2023-07-04 12:59 20阅读 0赞

## [MYSQL 执行计划分析三看， EXPLAIN,PROFILING,OPTIMIZER\_TRACE][MYSQL _ EXPLAIN_PROFILING_OPTIMIZER_TRACE] ##

**step 1**

使用explain 查看执行计划， 5.6后可以加参数 explain format=json xxx 输出json格式的信息

**step 2 **

使用profiling详细的列出在每一个步骤消耗的时间，**前提是先执行一遍语句**。

#打开profiling 的设置
    SET profiling = 1;
    SHOW VARIABLES LIKE '%profiling%';
    
    #查看队列的内容
    show profiles;  
    #来查看统计信息
    show profile block io,cpu for query 3;

**step 3  **

Optimizer trace是MySQL5.6添加的新功能，可以看到大量的内部查询计划产生的信息, 先打开设置，然后执行一次sql,最后查看\`information\_schema\`.\`OPTIMIZER\_TRACE\`的内容

#打开设置
    SET optimizer_trace='enabled=on';  
    #最大内存根据实际情况而定， 可以不设置
    SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;
    SET END_MARKERS_IN_JSON=ON;
    SET optimizer_trace_limit = 1;
    SHOW VARIABLES LIKE '%optimizer_trace%';
    
    #执行所需sql后，查看该表信息即可看到详细的执行过程
    SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`;

[MySQL索引选择不正确并详细解析OPTIMIZER\_TRACE格式][MySQL_OPTIMIZER_TRACE]

http://blog.csdn.net/melody\_mr/article/details/48950601

**一 表结构如下: **

CREATE TABLE t\_audit\_operate\_log (  
  Fid bigint(16) AUTO\_INCREMENT,  
  Fcreate\_time int(10) unsigned NOT NULL DEFAULT '0',  
  Fuser varchar(50) DEFAULT '',  
  Fip bigint(16) DEFAULT NULL,  
  Foperate\_object\_id bigint(20) DEFAULT '0',  
  PRIMARY KEY (Fid),  
  KEY indx\_ctime (Fcreate\_time),  
  KEY indx\_user (Fuser),  
  KEY indx\_objid (Foperate\_object\_id),  
  KEY indx\_ip (Fip)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

**执行查询:**

[MySQL][]> explain select count(\*) from t\_audit\_operate\_log where [Fuser='XX@XX.com'][Fuser_XX_XX.com] and Fcreate\_time>=1407081600 and Fcreate\_time<=1407427199\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

id: 1

select\_type: SIMPLE

table: t\_audit\_operate\_log

type: ref

possible\_keys: indx\_ctime,indx\_user

key: indx\_user

key\_len: 153

ref: const

**rows: 2007326**

**Extra: Using where**

**发现,使用了一个不合适的索引, 不是很理想，于是改成指定索引：**

mysql> explain select count(\*) from t\_audit\_operate\_log use index(indx\_ctime) where Fuser='CY6016@cyou-inc.com' and Fcreate\_time>=1407081600 and Fcreate\_time<=1407427199\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

id: 1

select\_type: SIMPLE

table: t\_audit\_operate\_log

type: range

possible\_keys: indx\_ctime

key: indx\_ctime

key\_len: 5

ref: NULL

**rows: 670092**

**Extra: Using where**

实际执行耗时，后者比前者快了接近10

**问题: 很奇怪，优化器为何不选择使用 ****indx\_ctime ****索引，而选择了明显会扫描更多行的 ****indx\_user ****索引。**

分析2个索引的数据量如下:  两个条件的唯一性对比：

select count(\*) from t\_audit\_operate\_log where [Fuser='XX@XX.com'][Fuser_XX_XX.com];  
\+----------+  
| count(\*) |  
\+----------+  
| 1238382 |   
\+----------+

select count(\*) from t\_audit\_operate\_log where Fcreate\_time>=1407254400 and Fcreate\_time<=1407427199;  
\+----------+  
| count(\*) |  
\+----------+  
| 198920 |   
\+----------+

显然,使用索引indx\_ctime好于indx\_user,但MySQL却选择了indx\_user. 为什么?

于是,使用 **OPTIMIZER\_TRACE**进一步探索.

**二  OPTIMIZER\_TRACE的过程说明**

以本处事例简要说明**OPTIMIZER\_TRACE的过程.**

查看OPTIMIZER\_TRACE方法：

1.set optimizer\_trace='enabled=on';    --- 开启trace

2.set optimizer\_trace\_max\_mem\_size=1000000;    --- 设置trace大小

3.set end\_markers\_in\_json=on;    --- 增加trace中注释

4.select \* from information\_schema.optimizer\_trace\\G;

**\[plain\]** [view plain][] [copy][view plain]

1.  \{\\  
2.    "steps": \[\\  
3.      \{\\  
4.        "join\_preparation": \{\\  ---优化准备工作  
5.          "select\#": 1,\\  
6.          "steps": \[\\  
7.            \{\\  
8.              "expanded\_query": "/\* select\#1 \*/ select count(0) AS \`count(\*)\` from \`t\_audit\_operate\_log\` where ((\`t\_audit\_operate\_log\`.\`Fuser\` = 'XX@XX.com') and (\`t\_audit\_operate\_log\`.\`Fcreate\_time\` >= 1407081600) and (\`t\_audit\_operate\_log\`.\`Fcreate\_time\` <= 1407427199))"\\  
9.            \}\\  
10.         \] /\* steps \*/\\  
11.       \} /\* join\_preparation \*/\\  
12.     \},\\  
13.     \{\\  
14.       "join\_optimization": \{\\  ---优化工作的主要阶段,包括逻辑优化和物理优化两个阶段  
15.         "select\#": 1,\\  
16.         "steps": \[\\  ---优化工作的主要阶段, 逻辑优化阶段  
17.           \{\\  
18.             "condition\_processing": \{\\  ---逻辑优化,条件化简  
19.               "condition": "WHERE",\\  
20.               "original\_condition": "((\`t\_audit\_operate\_log\`.\`Fuser\` = 'XX@XX.com') and (\`t\_audit\_operate\_log\`.\`Fcreate\_time\` >= 1407081600) and (\`t\_audit\_operate\_log\`.\`Fcreate\_time\` <= 1407427199))",\\  
21.               "steps": \[\\  
22.                 \{\\  
23.                   "transformation": "equality\_propagation",\\  ---逻辑优化,条件化简,等式处理  
24.                   "resulting\_condition": "((\`t\_audit\_operate\_log\`.\`Fuser\` = 'XX@XX.com') and (\`t\_audit\_operate\_log\`.\`Fcreate\_time\` >= 1407081600) and (\`t\_audit\_operate\_log\`.\`Fcreate\_time\` <= 1407427199))"\\  
25.                 \},\\  
26.                 \{\\  
27.                   "transformation": "constant\_propagation",\\  ---逻辑优化,条件化简,常量处理  
28.                   "resulting\_condition": "((\`t\_audit\_operate\_log\`.\`Fuser\` = 'XX@XX.com') and (\`t\_audit\_operate\_log\`.\`Fcreate\_time\` >= 1407081600) and (\`t\_audit\_operate\_log\`.\`Fcreate\_time\` <= 1407427199))"\\  
29.                 \},\\  
30.                 \{\\  
31.                   "transformation": "trivial\_condition\_removal",\\  ---逻辑优化,条件化简,条件去除  
32.                   "resulting\_condition": "((\`t\_audit\_operate\_log\`.\`Fuser\` = 'XX@XX.com') and (\`t\_audit\_operate\_log\`.\`Fcreate\_time\` >= 1407081600) and (\`t\_audit\_operate\_log\`.\`Fcreate\_time\` <= 1407427199))"\\  
33.                 \}\\  
34.               \] /\* steps \*/\\  
35.             \} /\* condition\_processing \*/\\  
36.           \},\\  ---逻辑优化,条件化简,结束  
37.           \{\\  
38.             "table\_dependencies": \[\\  ---逻辑优化, 找出表之间的相互依赖关系. 非直接可用的优化方式.   
39.               \{\\  
40.                 "table": "\`t\_audit\_operate\_log\`",\\  
41.                 "row\_may\_be\_null": false,\\  
42.                 "map\_bit": 0,\\  
43.                 "depends\_on\_map\_bits": \[\\  
44.                 \] /\* depends\_on\_map\_bits \*/\\  
45.               \}\\  
46.             \] /\* table\_dependencies \*/\\  
47.           \},\\  
48.           \{\\  
49.             "ref\_optimizer\_key\_uses": \[\\   ---逻辑优化,  找出备选的索引  
50.               \{\\  
51.                 "table": "\`t\_audit\_operate\_log\`",\\  
52.                 "field": "Fuser",\\  
53.                 "equals": "'XX@XX.com'",\\  
54.                 "null\_rejecting": false\\  
55.               \}\\  
56.             \] /\* ref\_optimizer\_key\_uses \*/\\  
57.           \},\\  
58.           \{\\  
59.             "rows\_estimation": \[\\   ---逻辑优化, 估算每个表的元组个数. 单表上进行全表扫描和索引扫描的代价估算. 每个索引都估算索引扫描代价  
60.               \{\\  
61.                 "table": "\`t\_audit\_operate\_log\`",\\  
62.                 "range\_analysis": \{\\  
63.                   "table\_scan": \{\\---逻辑优化, 估算每个表的元组个数. 单表上进行全表扫描的代价  
64.                     "rows": 8150516,\\  
65.                     "cost": 1.73e6\\  
66.                   \} /\* table\_scan \*/,\\  
67.                   "potential\_range\_indices": \[\\ ---逻辑优化, 列出备选的索引. 后续版本字符串变为potential\_range\_indexes  
68.                     \{\\  
69.                       "index": "PRIMARY",\\---逻辑优化, 本行表明主键索引不可用  
70.                       "usable": false,\\  
71.                       "cause": "not\_applicable"\\  
72.                     \},\\  
73.                     \{\\  
74.                       "index": "indx\_ctime",\\---逻辑优化, 索引indx\_ctime  
75.                       "usable": true,\\  
76.                       "key\_parts": \[\\  
77.                         "Fcreate\_time",\\  
78.                         "Fid"\\  
79.                       \] /\* key\_parts \*/\\  
80.                     \},\\  
81.                     \{\\  
82.                       "index": "indx\_user",\\---逻辑优化, 索引indx\_user  
83.                       "usable": true,\\  
84.                       "key\_parts": \[\\  
85.                         "Fuser",\\  
86.                         "Fid"\\  
87.                       \] /\* key\_parts \*/\\  
88.                     \},\\  
89.                     \{\\  
90.                       "index": "indx\_objid",\\---逻辑优化, 索引  
91.                       "usable": false,\\  
92.                       "cause": "not\_applicable"\\  
93.                     \},\\  
94.                     \{\\  
95.                       "index": "indx\_ip",\\---逻辑优化, 索引  
96.                       "usable": false,\\  
97.                       "cause": "not\_applicable"\\  
98.                     \}\\  
99.                   \] /\* potential\_range\_indices \*/,\\  
100.                   "setup\_range\_conditions": \[\\ ---逻辑优化, 如果有可下推的条件,则带条件考虑范围查询  
101.                   \] /\* setup\_range\_conditions \*/,\\  
102.                   "group\_index\_range": \{\\---逻辑优化, 如带有GROUPBY或DISTINCT,则考虑是否有索引可优化这种操作. 并考虑带有MIN/MAX的情况  
103.                     "chosen": false,\\  
104.                     "cause": "not\_group\_by\_or\_distinct"\\  
105.                   \} /\* group\_index\_range \*/,\\  
106.                   "analyzing\_range\_alternatives": \{\\---逻辑优化,开始计算每个索引做范围扫描的花费(等值比较是范围扫描的特例)  
107.                     "range\_scan\_alternatives": \[\\  
108.                       \{\\  
109.                         "index": "indx\_ctime",\\ ---\[A\]  
110.                         "ranges": \[\\  
111.                           "1407081600 <= Fcreate\_time <= 1407427199"\\  
112.                         \] /\* ranges \*/,\\  
113.                         "index\_dives\_for\_eq\_ranges": true,\\  
114.                         "rowid\_ordered": false,\\  
115.                         "using\_mrr": true,\\  
116.                         "index\_only": false,\\  
117.                         "rows": 688362,\\  
118.                         "cost": 564553,\\ ---逻辑优化,这个索引的代价最小  
119.                         "chosen": true\\ ---逻辑优化,这个索引的代价最小,被选中. (比前面的table\_scan 和其他索引的代价都小)  
120.                       \},\\  
121.                       \{\\  
122.                         "index": "indx\_user",\\  
123.                         "ranges": \[\\  
124.                           "XX@XX.com <= Fuser <= XX@XX.com"\\  
125.                         \] /\* ranges \*/,\\  
126.                         "index\_dives\_for\_eq\_ranges": true,\\  
127.                         "rowid\_ordered": true,\\  
128.                         "using\_mrr": true,\\  
129.                         "index\_only": false,\\  
130.                         "rows": 1945894,\\  
131.                         "cost": 1.18e6,\\  
132.                         "chosen": false,\\  
133.                         "cause": "cost"\\  
134.                       \}\\  
135.                     \] /\* range\_scan\_alternatives \*/,\\  
136.                     "analyzing\_roworder\_intersect": \{\\  
137.                       "usable": false,\\  
138.                       "cause": "too\_few\_roworder\_scans"\\  
139.                     \} /\* analyzing\_roworder\_intersect \*/\\  
140.                   \} /\* analyzing\_range\_alternatives \*/,\\---逻辑优化,开始计算每个索引做范围扫描的花费. 这项工作结算  
141.                   "chosen\_range\_access\_summary": \{\\---逻辑优化,开始计算每个索引做范围扫描的花费. 总结本阶段最优的.  
142.                     "range\_access\_plan": \{\\  
143.                       "type": "range\_scan",\\  
144.                       "index": "indx\_ctime",\\  
145.                       "rows": 688362,\\  
146.                       "ranges": \[\\  
147.                         "1407081600 <= Fcreate\_time <= 1407427199"\\  
148.                       \] /\* ranges \*/\\  
149.                     \} /\* range\_access\_plan \*/,\\  
150.                     "rows\_for\_plan": 688362,\\  
151.                     "cost\_for\_plan": 564553,\\  
152.                     "chosen": true\\    -- 这里看到的cost和rows都比 indx\_user 要来的小很多---这个和\[A\]处是一样的,是信息汇总.  
153.                   \} /\* chosen\_range\_access\_summary \*/\\  
154.                 \} /\* range\_analysis \*/\\  
155.               \}\\  
156.             \] /\* rows\_estimation \*/\\ ---逻辑优化, 估算每个表的元组个数. 行估算结束  
157.           \},\\  
158.           \{\\  
159.             "considered\_execution\_plans": \[\\ ---物理优化, 开始多表连接的物理优化计算  
160.               \{\\  
161.                 "plan\_prefix": \[\\  
162.                 \] /\* plan\_prefix \*/,\\  
163.                 "table": "\`t\_audit\_operate\_log\`",\\  
164.                 "best\_access\_path": \{\\  
165.                   "considered\_access\_paths": \[\\  
166.                     \{\\  
167.                       "access\_type": "ref",\\ ---物理优化, 计算indx\_user索引上使用ref方查找的花费,  
168.                       "index": "indx\_user",\\  
169.                       "rows": 1.95e6,\\  
170.                       "cost": 683515,\\  
171.                       "chosen": true\\  
172.                     \},\\ ---物理优化, 本应该比较所有的可用索引,即打印出多个格式相同的但索引名不同的内容,这里却没有。推测是bug--没有遍历每一个索引.  
173.                     \{\\  
174.                       "access\_type": "range",\\---物理优化,猜测对应的是indx\_time（没有实例可进行调试，对比5.7的跟踪信息猜测而得）  
175.                       "rows": 516272,\\  
176.                       "cost": 702225,\\---物理优化，代价大于了ref方式的683515，所以没有被选择  
177.                       "chosen": false\\   -- cost比上面看到的增加了很多，但rows没什么变化 ---物理优化，此索引没有被选择  
178.                     \}\\  
179.                   \] /\* considered\_access\_paths \*/\\  
180.                 \} /\* best\_access\_path \*/,\\  
181.                 "cost\_for\_plan": 683515,\\ ---物理优化，汇总在best\_access\_path 阶段得到的结果  
182.                 "rows\_for\_plan": 1.95e6,\\  
183.                 "chosen": true\\   -- cost比上面看到的竟然小了很多？虽然rows没啥变化  ---物理优化，汇总在best\_access\_path 阶段得到的结果  
184.               \}\\  
185.             \] /\* considered\_execution\_plans \*/\\  
186.           \},\\  
187.           \{\\  
188.             "attaching\_conditions\_to\_tables": \{\\---逻辑优化，尽量把条件绑定到对应的表上  
189.               \} /\* attaching\_conditions\_to\_tables \*/\\  
190.           \},\\  
191.           \{\\  
192.             "refine\_plan": \[\\  
193.               \{\\  
194.                 "table": "\`t\_audit\_operate\_log\`",\\---逻辑优化，下推索引条件"pushed\_index\_condition"；其他条件附加到表上做为过滤条件"table\_condition\_attached"  
195.               \}\\  
196.             \] /\* refine\_plan \*/\\  
197.           \}\\  
198.         \] /\* steps \*/\\  
199.       \} /\* join\_optimization \*/\\ \\---逻辑优化和物理优化结束  
200.     \},\\  
201.     \{\\  
202.       "join\_explain": \{\} /\* join\_explain \*/\\  
203.     \}\\  
204.   \] /\* steps \*/\\

三 其他一个相似问题

单表扫描，使用ref和range从索引获取数据一例

http://blog.163.com/li_hx/blog/static/183991413201461853637715/

[MYSQL _ EXPLAIN_PROFILING_OPTIMIZER_TRACE]: https://www.cnblogs.com/mydriverc/p/7086542.html
[MySQL_OPTIMIZER_TRACE]: http://blog.csdn.net/melody_mr/article/details/48950601
[MySQL]: http://lib.csdn.net/base/mysql
[Fuser_XX_XX.com]: mailto:Fuser='%20rel=
[view plain]: http://blog.csdn.net/melody_mr/article/details/48950601#