Mysql怎么开启慢查询（sql优化篇）

 

原因：在实际开发过程中我们经常会遇到sql处理时间太长问题，如果你想分析是什么sql执行的时候占用过多时间可以开启慢查询；

-- 启动慢查询日志
set global slow_query_log='ON';

-- 开启 记录没有使用索引查询语句
set global log-queries-not-using-indexes = on
-- 设置慢查询存储文件地址
set global slow_query_log_file='/usr/local/mysql/slowlog/log.log';

-- 设置储存sql条件，sql 执行时间少于0.001秒存入日志文件
set global long_query_time=0.001;

[root@iZbp17evxqwzph2rj3c22bZ slowlog]# pwd
/usr/local/mysql/slowlog
[root@iZbp17evxqwzph2rj3c22bZ slowlog]# 
[root@iZbp17evxqwzph2rj3c22bZ slowlog]# 
[root@iZbp17evxqwzph2rj3c22bZ slowlog]# 
[root@iZbp17evxqwzph2rj3c22bZ slowlog]# cat log.log 
1

/usr/local/mysql/bin/mysqld, Version: 5.6.44-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 200509 12:03:25
# User@Host: root[root] @ localhost [127.0.0.1]  Id:  5089
# Query_time: 2.000280  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1588997005;
select sleep(2);
# Time: 200509 16:06:01
# User@Host: root[root] @ localhost [127.0.0.1]  Id:  5106
# Query_time: 0.019607  Lock_time: 0.000130 Rows_sent: 1  Rows_examined: 1
use searchstatistics;
SET timestamp=1589011561;
SHOW TABLE STATUS;
# Time: 200509 16:06:03
# User@Host: root[root] @ localhost [127.0.0.1]  Id:  5106
# Query_time: 0.002363  Lock_time: 0.000100 Rows_sent: 13  Rows_examined: 13
SET timestamp=1589011563;
SELECT * FROM `searchstatistics`.`statistics` LIMIT 0, 1000;
[root@iZbp17evxqwzph2rj3c22bZ slowlog]#