MySQL之定位慢查询 慢查询日志 explain prof

发表于

「这是我参与11月更文挑战的第4天，活动详情查看：2021最后一次更文挑战」。

慢查询日志

  1. 修改配置文件（一般为/etc/my.cnf），开启慢查询日志
1
2
3
4
5
ini复制代码[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow-query.log   # 若没有指定，默认名字为hostname_slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
  1. 配合mysqldumpslow使用

如何开启MySQL慢查询日志 - 阿里云云栖号的文章 - 知乎

explain

原理

MySQL会在查询上设置一个标记。当执行查询时，这个标记会使其返回关于在执行计划中每一步的信息，而不是执行它。它会返回一行或多行信息，显示出执行计划中的每一部分和执行的次序。

explain会执行吗

如果查询在FROM子句中包括子查询，那么MySQL实际上会执行子查询，将其结果放在一个临时表中，然后完成外层查询优化。它必须在可以完成外层查询优化前处理所有类似的子查询，这对于explain来说是必须要做的（这个限制在MySQL5.6中将被取消）。

限制

  • 不会告知触发器、存储过程、UDF会如何影响查询
  • 不会告知查询执行中所作的特定优化

详见高性能MySQL附录D

5.6中的改进

能对类似UPDATE、INSERT等的查询进行解释
允许匿名表尽可能晚的具体化，而不总是在优化和执行使用到此临时表的部分查询时创建并填充它们。这将允许MySQL可以直接解释带子查询的查询语句，而不是要先实际地执行子查询。

profile

具体使用

MySQL查询优化(4)-show profile - 很six的文章 - 知乎
zhuanlan.zhihu.com/p/58387072

优化器跟踪

  1. 查看优化器状态
* show variables like 'optimizer\_trace';
  1. 会话级别临时开启
* set session optimizer\_trace="enabled=on",end\_markers\_in\_json=on;
  1. 设置优化器追踪的内存大小
* set OPTIMIZER\_TRACE\_MAX\_MEM\_SIZE=1000000;
  1. 执行自己的SQL
* select host,user,plugin from user;
  1. information_schema.optimizer_trace表
* SELECT trace FROM information\_schema.OPTIMIZER\_TRACE;
  1. 导入到一个命名为xx.trace的文件，然后用JSON阅读器来查看（如果没有控制台权限，或直接交由运维，让他把该 trace 文件，输出给你就行了。 ）。
* `SELECT TRACE INTO DUMPFILE "E:\\test.trace" FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;`

注意：不设置优化器最大容量的话，可能会导致优化器返回的结果不全。

本文转载自: 掘金

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

0%