MySQL optimizer_trace cost量化分析
Tags： MySQLoptimizer_trace costtrace跟踪
👉 本文共约3624个字，系统预计阅读时间或需14分钟。
前言
我们在日常维护数据库的时候，经常会遇到查询慢的语句，这时候一般会通过执行EXPLAIN去查看它的执行计划，但是执行计划往往只给我们带来了最基础的分析信息，比如是否有使用索引，还有一些其他供我们分析的信息，比如使用了临时表、排序等等，却无法展示为什么一些其他的执行计划未被选择，比如说明明有索引，或者好几个索引，但是为什么查询时未使用到期望的索引等
1 2 3 4 5 6 7 8 | explain select * from basic_person_info t1 join basic_person_info2 t2 on t1.id_num=t2.id_num where t1.age >10 and t2.age<20; +----+-------------+-------+------------+--------+--------------------------------------+---------------+---------+----------------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+--------------------------------------+---------------+---------+----------------+------+----------+-----------------------+ | 1 | SIMPLE | t2 | NULL | range | id_num_unique,idx_age,idx_age_id_num | idx_age | 1 | NULL | 9594 | 100.00 | Using index condition | | 1 | SIMPLE | t1 | NULL | eq_ref | id_num_unique,idx_age | id_num_unique | 60 | test.t2.id_num | 1 | 50.00 | Using where | +----+-------------+-------+------------+--------+--------------------------------------+---------------+---------+----------------+------+----------+-----------------------+ 2 rows in set, 1 warning (0.01 sec) |
如上面这个例子，为什么t2表上列出了多个可能使用的索引，却选择了idx_age，优化器为什么选择了指定的索引，这时候并不能直观的看出问题，这时候我们就可以开启optimizer_trace跟踪分析MySQL具体是怎么选择出最优的执行计划的。
OPTIMIZER_TRACE：
optimizer_trace是什么：
optimizer_trace是一个具有跟踪功能的工具，可以跟踪执行的语句的解析优化执行过程，并将跟踪到的信息记录到INFORMATION_SCHEMA.OPTIMIZER_TRACE表中，但是每个会话都只能跟踪它自己执行的语句，并且表中默认只记录最后一个查询的跟踪结果
使用方法：
1 2 3 4 5 6 | ## 打开optimizer trace功能 (默认情况下它是关闭的): set optimizer_trace="enabled=on"; select ...; ## 这里输入你自己的查询语句 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; ## 当你停止查看语句的优化过程时，把optimizer trace功能关闭 set optimizer_trace="enabled=off"; |
相关参数：
1 2 3 4 5 6 7 8 9 10 | mysql> show variables like '%optimizer_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 | 1 | | optimizer_trace_max_mem_size | 1048576 | | optimizer_trace_offset | -1 | +------------------------------+----------------------------------------------------------------------------+ |
- optimizer_trace： enabled 开启/关闭optimizer_trace，one_line 是否单行显示，关闭为json模式，一般不开启
- optimizer_trace_features：跟踪信息中可打印的项，一般不调整默认打印所有项
- optimizer_trace_limit：存储的跟踪sql条数
- optimizer_trace_offset：开始记录的sql语句的偏移量，负数表示从最近执行倒数第几条开始记录
- optimizer_trace_max_mem_size：optimizer_trace内存的大小，如果跟踪信息超过这个大小，信息将会被截断
optimizer_trace表信息：
该表总共有4个字段
- QUERY 表示我们的查询语句。
- TRACE 表示优化过程的JSON格式文本。(重点关注)
- MISSING_BYTES_BEYOND_MAX_MEM_SIZE 由于优化过程可能会输出很多，如果超过某个限制时，多余的文本将不会被显示，这个字段展示了被忽略的文本字节数。
- INSUFFICIENT_PRIVILEGES 表示是否没有权限查看优化过程，默认值是0，只有某些特殊情况下才会是 1，我们暂时不关心这个字段的值。
信息解读：
通过 optimizer_trace表的query字段可以看到，一条语句的执行过程主要分为三个步骤：
1 2 3 | "join_preparation": {},（准备阶段） "join_optimization": {},（优化阶段） "join_execution": {},（执行阶段） |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
