MySQL性能优化总结-03——慢sql定位分析

  优化器的作用是对我们的sql语句进行优化分析，生成执行计划。
  下面不废话，实用干货来了。

1.慢查询日志

1.1开启慢日志

  开启慢查询日志是有代价的（和binlog，optimizer-trace一样，所以它默认是关闭的：

show variables like 'slow_query%';

  除了这个开关，还有一个参数，控制执行超过多长时间的sql才记录到慢日志，默认是10秒。如果改成0秒的话，就是记录所有的sql。

show variables like  '%long_query%';

参数的两种修改方式：
1.set动态修改（重启后失效）

set @@glabal.slow_query_log=1;  --1开启，0关闭，重启后失效
set @@glabal.long_query_time=3;  --默认10秒，另开一个窗口后才会查到最新值

show variables like '%long_query%';
show variables like '%slow_query%';

2.修改配置文件 my.cnf
以下配置定义了慢查询日志的开关、慢查询的时间、日志文件的存放路径

slow_query_log=ON
long_query_time=2
slow_query_log_file=/var/lib/mysql/localhost-slow.sql

模拟慢查询

select sleep(10);

1.2慢日志分析

1.2.1日志内容

1.直接查看log文件：

less /var/lib/mysql/localhost-slow.log

  文件内容略。有了文件，可以分析了。
2.mysqldumpslow工具
  这个工具在mysql的bin目录下。

mysqldumpslow --help

  例如：查询用时最多的10条慢sql：

mysqldumpslow -s -t  10 -g 'select' /var/lib/mysql/localhost-slow.log

  Count 代表这个sql执行了多少次；
  Time代表执行的时间；
  Lock代表锁定的时间；
  Rows代表返回的行数，括号内是累计；
  有时候查询慢，并不是sql语句的问题，也有可能是服务器状态的问题。这时需要掌握一些查看服务器状态和存储引擎状态的命令。

1.3其他系统命令

show processlist 运行线程；

show full processlist;

  用于显示用户运行线程。可以根据id号kill线程。
也可以查表，效果一样：

select * from information_schema.processlist;

列	含义
id	线程唯一标志，可以用它kill线程
User	启动这个线程的用户，普通用户只能看到自己的线程
Host	哪个IP端口发起的连接
db	操作的数据库
Command	线程的命令，详情见 https://dev.mysql.com/doc/refman/5.7/en/thread-commands.html
Time	操作持续时间，单位秒
State	线程状态，比如有可能是：copying to tmp table,Sorting result,Sending data,详情见https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html
Info	sql的前100个字符，如果要看完整sql，用show full processlist

show status 服务器运行状态
  show satus 用于查看mysql服务器运行状态（重启会清空）。

show global status
  有session 和global 两种作用域。可以用like带通配符过滤。

show global status like ‘com_select’;    --查看select 次数

show engine 存储引擎运行信息
  show engine用来显示存储引擎的当前运行信息，包括事务持有的表锁，行锁信息；
事务的锁等待情况；线程信号量等待；文件io请求；buffer pool统计信息。
例如：

show engine innodb status;

开启innodb监控：

-- 开启InnoDB监控：
set global_status_output=ON;
set glabal_status_locks=ON;

下一篇就是 非常有用的执行计划分析。