一、慢查询定位

1 开启慢查询日志

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

  1. SHOW VARIABLES LIKE 'slow_query_log%'

image.png
2 开启慢查询日志

SET global slow_query_log = ON;
SET global slow_query_log_file = 'OAK-slow.log';
SET global log_queries_not_using_indexes = ON;
SET long_query_time = 10;

3 long_query_time：指定慢查询的阀值，单位秒。如果SQL执行时间超过阀值，就属于慢查询 记录到日志文件中。

2 log_queries_not_using_indexes

表示会记录没有使用索引的查询SQL。前提是slow_query_log 的值为ON，否则不会奏效。

3 查看慢查询日志

1 文本方式查看

直接使用文本编辑器打开slow.log日志即可。
1 time：日志记录的时间
2 User@Host：执行的用户及主机
3 Query_time：执行的时间
4 Lock_time：锁表时间
5 Rows_sent：发送给请求方的记录数，结果数量
6 Rows_examined：语句扫描的记录条数
7 SET timestamp：语句执行的时间点
8 select….：执行的具体的SQL语句

2 使用mysqldumpslow查看

1 MySQL 提供了一个慢查询日志分析工具mysqldumpslow，可以通过该工具分析慢查询日志 内容。

2 在 MySQL bin目录下执行下面命令可以查看该使用格式。

perl mysqldumpslow.pl --help

3 运行如下命令查看慢查询日志信息

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

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

二、慢查询优化

1 索引和慢查询

1 如何判断是否为慢查询？
MySQL判断一条语句是否为慢查询语句，主要依据SQL语句的执行时间，它把当前语句的执 行时间跟 long_query_time 参数做比较，如果语句的执行时间 > long_query_time，就会把 这条执行语句记录到慢查询日志里面。
long_query_time 参数的默认值是 10s，该参数值可 以根据自己的业务需要进行调整。

2 如何判断是否应用了索引？ S
QL语句是否使用了索引，可根据SQL语句执行过程中有没有用到表的索引，可通过 explain 命令分析查看，检查结果中的 key 值，是否为NULL。

3 应用了索引是否一定快？
1）下面语句的 explain 的结果，你觉得这条语句有用上索引吗？比如

select * from user where id>0;

2）虽然使用了索引，但是还是从主键索引的最左边的叶节点开始向右扫描整个索引树，进行了 全表扫描，此时索引就失去了意义。
3） select * from user where id = 2; 这样的语句，才是我们平时说的使用了索引。它表示 的意思是，我们使用了索引的快速搜索功能，并且有效地减少了扫描行数。

4 查询是否使用索引，只是表示一个SQL语句的执行过程；而是否为慢查询，是由它执行的时间决定 的，也就是说是否使用了索引和是否是慢查询两者之间没有必然的联系。

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

2 慢查询原因总结

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

三、分页查询优化

1 一般性分页

般的分页查询使用简单的 limit 子句就可以实现。limit格式

SELECT * FROM 表名 LIMIT [offset,] rows

第一个参数指定第一个返回记录行的偏移量，注意从0开始；
第二个参数指定返回记录行的最大数目；
如果只给定一个参数，它表示返回最大的记录行数目；

1 如果偏移量固定，返回记录量对执行时间有什么影响？

select * from user limit 10000,1;
select * from user limit 10000,10;
select * from user limit 10000,100;
select * from user limit 10000,1000;
select * from user limit 10000,10000;

结果：在查询记录时，返回记录量低于100条，查询时间基本没有变化，差距不大。随着查询记录 量越大，所花费的时间也会越来越多。

2 如果查询偏移量变化，返回记录数固定对执行时间有什么影响？

select * from user limit 1,100;
select * from user limit 10,100;
select * from user limit 100,100;
select * from user limit 1000,100;
select * from user limit 10000,100;

结果：在查询记录时，如果查询记录量相同，偏移量超过100后就开始随着偏移量增大，查询时间 急剧的增加。（这种分页查询机制，每次都会从数据库第一条记录开始扫描，越往后查询越慢，而 且查询的数据越多，也会拖慢总查询速度。）

2 分页优化方案

第一步：利用覆盖索引优化

select * from user limit 10000,100;
select id from user limit 10000,100;

第二步：利用子查询优化

select * from user limit 10000,100;
select * from user where id>= (select id from user limit 10000,1) limit 100;

原因：使用了id做主键比较(id>=)，并且子查询使用了覆盖索引进行优化。