查询优化

# 查询优化

虽然现在很多云服务商都提供了慢SQL语句的监控，但是我们理解MySQL原生慢查询监控还是有必要的。

# 慢查询定位

# 开启慢查询日志

Selecting General Query Log and Slow Query Log Output Destinations (opens new window)

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

show variables like 'slow_query_log%';

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

SET global slow_query_log = ON;
# 默认为主机名称-slow.log
SET global slow_query_log_file = 'hostname-slow.log';
# 表示会记录没有使用索引的查询SQL。前提是slow_query_log的值为ON，否则不会奏效。
SET global log_queries_not_using_indexes = ON;
# 指定慢查询的阀值，单位秒。如果SQL执行时间超过阀值，就属于慢查询记录到日志文件中。
SET long_query_time = 10;

# 查看慢查询日志

mysqldumpslow — Summarize Slow Query Log Files (opens new window)

文本方式查看：直接使用文本编辑器打开slow.log日志即可。

参数说明：
- Time：日志记录的时间
- User@Host：执行的用户及主机
- Query_time：执行的时间
- Lock_time：锁表时间
- Rows_sent：发送给请求方的记录数，结果数量
- Rows_examined：语句扫描的记录条数
- SET timestamp：语句执行的时间点
- select....：执行的具体的SQL语句
使用mysqldumpslow查看，MySQL提供了的官方慢查询日志分析工具mysqldumpslow，可以通过该工具分析慢查询日志内容，具体使用查看--help命令。(win在MYSQL_HOME/bin目录下；Linux在/usr/bin目录下)
```
# win
perl mysqldumpslow.pl -t 5 -s at ABSOLUTE_PATH/hostname-slow.log
# linux
mysqldumpslow -t 5 -s at /var/lib/mysql/51928ba432b8-slow.log
```
第三方工具，比如pt-query-digest、mysqlsla等。

# 慢查询优化

如何判断是否为慢查询？

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

# 索引和慢查询

如何判断是否应用了索引？

SQL语句是否使用了索引，可根据SQL语句执行过程中有没有用到表的索引，可通过 explain命令分析查看，检查结果中的 key 值，是否为NULL，还要注意关注结果中的type、Extra信息。

应用了索引是否一定快？

下面我们来看看下面语句的 explain 的结果，你觉得这条语句有用上索引吗？比如

select * from user where id>0;

虽然使用了索引，但是还是从主键索引的最左边的叶节点开始向右扫描整个索引树，进行了全表扫描，此时索引就失去了意义。而像下面的语句：

select * from user where id=2;

这样的语句，才是我们平时说的使用了索引。它表示的意思是，我们使用了索引的快速搜索功能，并且有效地减少了扫描行数。

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

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

# 提高索引过滤性

假如有一个5000万记录的用户表，通过sex='男'索引过滤后，还需要定位3000万，SQL执行速度也不会很快。其实这个问题涉及到索引的过滤性，比如1万条记录利用索引过滤后定位10条、100条、1000条，那他们过滤性是不同的。索引过滤性与索引字段、表的数据量、表设计结构都有关系。

下面我们看一个案例：

表：student
字段：id,name,sex,age 
造数据：insert into student (name,sex,age) select name,sex,age from student; 
SQL案例：select * from student where age=18 and name like 'z%';（全表扫描）
观察explain的信息输出：row、key、type

优化方案：

# 优化1，追加name索引
alter table student add index(name); 
# 优化2，追加age,name索引
alter table student add index(age,name);
# 优化3，为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分析type属性为ALL
全索引扫描：explain分析type属性INDEX
索引过滤性不好：靠索引字段选型、数据量和状态、表设计
频繁的回表查询开销：尽量少用select *，使用覆盖索引

# 分页查询优化

# 一般性分页

一般的分页查询使用简单的 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;

思考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;

# 实验分析

这里使用上面建的student大表（数据量5w+，看自己的电脑性能照测试数据），分别进行上面的查询得到结果分析：

show variables like 'profiling'，开启set profiling=1。

思考1结果：在查询记录时，如果查询偏移量固定时，返回记录量低于1000条，查询时间基本没有变化，差距不大。但超过1000条（不同电脑，这个值可能不同）后时间查询时间急剧的增加。也就是说当偏移量固定时，随着查询记录量越大，所花费的时间会越来越多。

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

# 优化方案

利用覆盖索引优化：

select * from user limit 10000,100; 
# 改为
select id from user limit 10000,100;

利用子查询优化：

select * from user limit 10000,100; 
# 使用了id做主键比较(id>=)，并且子查询使用了覆盖索引进行优化。
select * from user where id>= (select id from user limit 10000,1) limit 100;

#MySQL查询优化

上次更新: 5/30/2023, 11:09:19 PM

MySQL性能优化手册→