Mysql慢查询

慢查询定位

查看Mysql数据库是否开启了慢查询日志和日志文件存储位置的命令如下

	show variables like 'slow_query_log%'

通过如下命令开启慢查询日志：

	SET global slow_query_log = ON; //开启命令
	SET global slow_query_log_file = 'OAK-slow.log'; //日志文件名
	SET global log_queries_not_using_indexes = ON; //记录没有使用所用的查询sql,前提是 slow_query_log 的值为 ON;
	SET long_query_time = 10; //指定慢查询阀值,单位:秒

• 文本方式查看
  

  time：日志记录的时间
  User@Host：执行的用户及主机
  Query_time：执行的时间
  Lock_time：锁表时间
  Rows_sent：发送给请求方的记录数，结果数量
  Rows_examined：语句扫描的记录条数
  SET timestamp：语句执行的时间点
  select…：执行的具体的SQL语句

• 使用mysqldumpslow查看
  MySQL 提供了一个慢查询日志分析工具mysqldumpslow，可以通过该工具分析慢查询日志内容。
  在 MySQL bin目录下执行下面命令可以查看该使用格式。

	perl mysqldumpslow.pl -t 5 -s at C:\ProgramData\MySQL\Data\OAK-slow.log

除了使用mysqldumpslow工具，也可以使用第三方分析工具，比如pt-query-digest、mysqlsla等。

[参考]https://www.cnblogs.com/moss_tan_jun/p/8025504.html

慢查询优化

我们在使用索引时，不要只关注是否起作用，应该关心索引是否减少了查询扫描的数据行数，如果扫描行数减少了，效率才会得到提升。对于一个大表，不止要创建索引，还要考虑索引过滤性，过滤性好，执行速度才会快。

提高索引过滤性

• 查询时尽量使用等值查询,范围查询的性能较差
• 创建索引
• 创建组合索引实现索引覆盖
  只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做索引覆盖。
  实现索引覆盖最常见的方法就是：将被查询的字段，建立到组合索引。
• 使用 MySQL 5.7 引入的虚拟列来进一步优化。
  例如将名字的第一个字和年龄做一个组合索引

	//为user表添加first_name虚拟列，以及联合索引(first_name,age) 
	alter table student add first_name varchar(2) generated always as (left(name, 1)), add index(first_name, age); 
	explain select * from student where first_name='张' and age=18;

慢查询原因总结
全表扫描：explain分析type属性all
全索引扫描：explain分析type属性index
索引过滤性不好：靠索引字段选型、数据量和状态、表设计
频繁的回表查询开销：尽量少用select *，使用覆盖索引

mysql show profiles
显示sql执行过程中各个环节的消耗情况，例如cpu使用情况，打开表、检查权限、执行优化器、返回数据等分别用了多长时间，可以分析语句执行慢的瓶颈在哪。
查看当前profiling的值

	select @@profiling

设置打开profiling

	set profiling =1;

show profiles 默认显示最近15条的sql执行情况，15这个数字由profiling_history_size常量决定，你可以配置为0到100的数字最大支持100，如果设置为0则类似于关闭profiling选项。

除了show profile 和show profiles 两个命令不会被记录之外，其余的语句都会被记录，即使是语法错误的sql也会被记录。

show profile 命令默认获取最新一条执行的sql的消耗分析，如果想指定获取某一条sql的profile 用如下命令：

	show profiles; //先获取要分析的query_id，然后执行
	show profile for query query_id;

show profiles以及show profile 命令同时也支持 limit语句。

show profile 命令 默认只显示Duration 列总消耗时间，如果要显示更多可以设置 all参数
show profile [type];

type 参数支持如下：
ALL ：表示所有的列
BLOCK IO ：数据块的输出出入操作次数
CONTEXT SWITCHES ： 上下文切换次数
CPU：cpu使用时长 包括系统时长和用使用时长
IPC ：发送和接收数据的 次数
PAGE FAULTS：重要的和次要的错误次数
SOURCE：执行相应操作的mysql源文件，包括源文件方法名，地址、所在行；
SWAPS：swap次数

提示：实际上这些数据全都存在mysql的表里，你可以执行如下查询一样可以获取相同的信息。

	SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = query_id;