MySQL optimizer_trace的用法 _Optimizer

博观而约取，厚积而薄发。这篇文章主要讲述MySQL optimizer_trace的用法相关的知识，希望能为你提供帮助。
【MySQL optimizer_trace的用法】?? https://dev.mysql.com/doc/internals/en/system-variables-controlling-trace.html ??
?? https://dev.mysql.com/doc/internals/en/tracing-example.html ??

> show global variables like optim%trace%-- 默认配置
+------------------------------+----------------------------------------------------------------------------+
| Variable_name| Value|
+------------------------------+----------------------------------------------------------------------------+
| optimizer_trace| enabled=off,one_line=off|
| optimizer_trace_features| greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |
| optimizer_trace_limit| 1|
| optimizer_trace_max_mem_size | 1048576|
| optimizer_trace_offset| -1|
+------------------------------+----------------------------------------------------------------------------+

> SET OPTIMIZER_TRACE="enabled=on";
> SET END_MARKERS_IN_JSON=on;
> set optimizer_trace_max_mem_size=1000000;
> SET optimizer_trace_offset=-5, optimizer_trace_limit=5; -- 显示最后五个trace
> show variables like %optimize%trace%;
+------------------------------+----------------------------------------------------------------------------+
| Variable_name| Value|
+------------------------------+----------------------------------------------------------------------------+
| optimizer_trace| enabled=on,one_line=off|
| optimizer_trace_features| greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |
| optimizer_trace_limit| 5|
| optimizer_trace_max_mem_size | 1000000|
| optimizer_trace_offset| -5|
+------------------------------+----------------------------------------------------------------------------+

然后再模拟些业务数据（这段来自官方文档https://dev.mysql.com/doc/internals/en/tracing-example.html）

CREATE TABLE t1 (
pk INT,
col_int_key INT,
col_varchar_key VARCHAR (1),
col_varchar_nokey VARCHAR (1)
);

INSERT INTO t1 VALUES
(10,7,v,v),(11,0,s,s),(12,9,l,l),(13,3,y,y),(14,4,c,c),
(15,2,i,i),(16,5,h,h),(17,3,q,q),(18,1,a,a),(19,3,v,v),
(20,6,u,u),(21,7,s,s),(22,5,y,y),(23,1,z,z),(24,204,h,h),
(25,224,p,p),(26,9,e,e),(27,5,i,i),(28,0,y,y),(29,3,w,w);

CREATE TABLE t2 (
pk INT, col_int_key INT,
col_varchar_key VARCHAR(1), col_varchar_nokey VARCHAR(1),
PRIMARY KEY (pk)
);

INSERT INTO t2 VALUES
(1,4,b,b),(2,8,y,y),(3,0,p,p),(4,0,f,f),(5,0,p,p),
(6,7,d,d),(7,7,f,f),(8,5,j,j),(9,3,e,e),(10,188,u,u),
(11,4,v,v),(12,9,u,u),(13,6,i,i),(14,1,x,x),(15,5,l,l),
(16,6,q,q),(17,2,n,n),(18,4,r,r),(19,231,c,c),(20,4,h,h),
(21,3,k,k),(22,3,t,t),(23,7,t,t),(24,6,k,k),(25,7,g,g),
(26,9,z,z),(27,4,n,n),(28,4,j,j),(29,2,l,l),(30,1,d,d),
(31,2,t,t),(32,194,y,y),(33,2,i,i),(34,3,j,j),(35,8,r,r),
(36,4,b,b),(37,9,o,o),(38,4,k,k),(39,5,a,a),(40,5,f,f),
(41,9,t,t),(42,3,c,c),(43,8,c,c),(44,0,r,r),(45,98,k,k),
(46,3,l,l),(47,1,o,o),(48,0,t,t),(49,189,v,v),(50,8,x,x),
(51,3,j,j),(52,3,x,x),(53,9,k,k),(54,6,o,o),(55,8,z,z),
(56,3,n,n),(57,9,c,c),(58,5,d,d),(59,9,s,s),(60,2,j,j),
(61,2,w,w),(62,5,f,f),(63,8,p,p),(64,6,o,o),(65,9,f,f),
(66,0,x,x),(67,3,q,q),(68,6,g,g),(69,5,x,x),(70,8,p,p),
(71,2,q,q),(72,120,q,q),(73,25,v,v),(74,1,g,g),(75,3,l,l),
(76,1,w,w),(77,3,h,h),(78,153,c,c),(79,5,o,o),(80,9,o,o),
(81,1,v,v),(82,8,y,y),(83,7,d,d),(84,6,p,p),(85,2,z,z),
(86,4,t,t),(87,7,b,b),(88,3,y,y),(89,8,k,k),(90,4,c,c),
(91,6,z,z),(92,1,t,t),(93,7,o,o),(94,1,u,u),(95,0,t,t),
(96,2,k,k),(97,7,u,u),(98,2,b,b),(99,1,m,m),(100,5,o,o);

模拟个业务查询

SELECT
SUM(alias2.col_varchar_nokey),
alias2.pk AS field2
FROM
t1 AS alias1
STRAIGHT_JOIN t2 AS alias2
ON alias2.pk = alias1.col_int_key
WHERE alias1.pk
GROUP BY field2
ORDER BY alias1.col_int_key,
alias2.pk;

看下捕获到的sql的trace情况

> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE \\G

注意：
每个记住的trace都是一个字符串。它随着优化的进行而扩展（使用 realloc()）并向其追加数据。
optimizer_trace_max_mem_size 变量对所有当前记住的trace使用的内存总量设置限制：如果达到此限制，则不会扩展当前跟踪（因此trace的结果显式将不完整）。

还可以把结果导出到文件（使用 INTO DUMPFILE 而不是 INTO OUTFILE ，因为后者会转义换行符。需要关闭end_markers_in_json，不然导出结果可能会不符合标准json）