慢SQL优化学习 | 青训营笔记

用户5656141633707
54 阅读2分钟

这是我参与「第五届青训营 」伴学笔记创作活动的第 2 天，本次的学习是根据掘金资源中的文章进行学习和实际测试。 链接如下：juejin.cn/post/715154…

慢SQL优化学习

1.慢查询日志记录慢SQL（可以定位到执行效率较低的SQL语句）

1.png

slow_query_log = OFF表示慢查询关闭的状态

slow_query_log_file表示慢查询日志存放的状态

设置开启慢日志方法：

方法（1）：

全局变量设置（该方式下数据库重启全部失效，需要每次重启后重新配置）

mysql> set global slow_query_log='ON';

设置慢查询日志存放的位置：

mysql> set global slow_query_log_file='/usr/local/mysql/data/slow.log'; //linux mysql> set global slow_query_log_file='D:\mysq\data\slow.log'; //windows

方法（2）：

配置文件设置（服务器重启不影响）

修改配置文件my.cnf，在[mysqld]中加入或修改

[mysqld] slow_query_log = ON slow_query_log_file = /usr/local/mysql/data/slow.log //linux long_query_time = 1

（一般还需要重启MySQL服务------service mysqld restart）

使用命令--show variables like 'long_query_time'

2.png long_query_time表示查询超过多少秒才记录到慢查询日志

2.explain查看分析预估SQL的执行计划

explain与SQL一起使用时，MySQL将解释它将如何处理该语句等详细信息。

3.png

type--连接类型，以下性能从好到坏依次为：system>const>eq_ref>ref>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL

4.png

rows--需要读取的行数（对于InnoDB表，此数字是估计值，并非是一个准确值）

filtered--表里符合条件的记录数的百分比（这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例）

extra--包含有关MySQL如何解析查询的其他信息

5.png key--表示实际用到的索引

2.profile分析执行耗时---只能查看SQL的执行耗时，无法看到SQL真正执行的过程信息

profiling--能知道SQL真正的执行线程状态及消耗时间（记录其资源开销，包括IO，上下文切换，CPU，内存等）

查看profiling状态---show variables like '%profil%'

6.png

开启profiling---set profiling=ON

show profiles---显示最近发给服务器的多条语句，（条数由profiling_history_size定义）

show profile----查看最近一条SQL的分析

show profile for query id（id就是show profiles中的QUERY_ID）查看具体一条的SQL语句分析）

3.Optimizer Trace分析详情---可以跟踪执行语句的解析优化执行的全过程

打开开关(set optimizer_trace="enabled=on")---执行要跟踪的SQL(select * from information_schema.optimizer_trace)

包括三阶段：

  • join_preparation：准备阶段
  • join_optimization：分析阶段
  • join_execution：执行阶段
avatar
文章
阅读
粉丝