MySQL慢查询优化

李素晴
551 阅读7分钟

慢查询的原因：①执行时间长；②等待时间长。

  1. 查询语句编写有问题。
  2. 索引失效。
  3. 连接查询过于复杂。
  4. 服务器参数设置有问题。

SQL机读次序

FROM <left table>
ON <join condition>
<join type> JOIN <right table>
WHERE <where condition>
GROUP BY <group by list>
HAVING <having condition>
SELECT
DISTINCT <select list>
ORDER BY <order by condition>
LIMIT <limit number>

索引

满足特定算法的有序的数据结构，以某种方式引用（指向）数据，有助于提高where子句（IO）和order by子句（CPU）的效率。

  1. 索引的缺点
  • 索引本身也很大，不可能全部存储在内存中，而是以索引文件的形式存储在磁盘上。
  • 数据的更新操作由于需要维护索引，因此会变慢。
  • 如果数据量很大，建立索引、优化查询语句需要花很多时间。
  1. 索引的结构
  • BTree索引
  • Hash索引
  • FullText全文索引
  • RTree索引
  1. BTree检索原理

多路搜索树降低了树的高度，也就减少了磁盘IO的次数，每经历树的一层就将相应磁盘块的数据读入内存，在内存中进行二分确定搜索路径。

  1. 创建索引的原则
  • 主键、唯一、外键自动建立索引。
  • 频繁查询的字段建立索引。
  • 高并发下倾向建立组合索引而非单值索引。
  • 统计或分组或排序字段建立索引。
  • 一个表的索引最好不要超过5个。
  • 表数据太少不需要建立索引。
  • 经常增删改的表不需要建立索引。
  • 重复且平均的字段，例如性别不需要建立索引。

性能分析

  1. MySQL优化器需要时间。
  2. CPU或IO或服务器硬件的性能瓶颈。

通过EXPLAIN可以分析MySQL查询优化器对SQL语句的理解，下面是对EXPLAIN各个字段的解释。

  1. id：id大的优先级高，id相同自上而下顺序执行。
  2. select_type：查询的类型。
    • SIMPLE：不包含子查询或UNION的简单查询。
    • PRIMARY：最外层的查询。
    • SUBQUERY：SELECT或WHERE中的子查询。
    • DERIVED：FROM中的子查询。
    • UNION：UNION后面的查询。
    • UNION RESULT：从UNION获取结果的查询。
  3. table：该行是关于哪张表。
  4. type：查询类型，下列性能由好到差。
    • system：表中只有一行数据，常见于系统表。
    • const：发生于主键或唯一索引上，字段与常数匹配。select * from t1 where id = 1;
    • eq_ref：发生于主键或唯一索引上，select * from t1,t2 where t1.id = t2.id;，对于t1中每一行，t2只会有1行匹配，因此找到就break不再向后寻找。
    • ref：发生于非唯一索引上，select * from t1,t2 where t1.name = t2.name;，对于t1中每一行，t2有多行匹配，因此必须找到表的结尾。
    • range：select * from t1 where id between 1 and 100;，索引上的范围扫描，比较运算符、IN也是范围扫描。
    • index：select id from t1;遍历索引树，数据从索引上就能拿到，因为索引在内存里，而且索引文件也比数据文件小。
    • ALL：select * from t1;数据文件上的全表扫描。
  5. possible_keys：理论上会用到的索引。
  6. key：实际上用到的索引。
  7. ken_len：使用的索引字段类型的最大字节总长度。
  8. ref：哪些列或常量被用于匹配索引列。
  9. rows：找到所需内容大致要读取的行数。
  10. Extra：额外信息。
    1. Using filesort：无法利用索引排序。
    2. Using temporary：对查询结果排序时使用了临时表保存中间结果，常见于排序和分组。
    3. Using index：SELECT的字段在索引上就可以获取，即索引覆盖。
CREATE INDEX idx_c1_c2 ON t1(c1,c2);
# 用到了复合索引上的c1查找，但是排序用不上索引，Using filesort;
SELECT c1 FROM t1 WHERE c1 = 1 ORDER BY c2; # order by c1[,c2]解决
# 用到了复合索引上的c1查找，但是分组用不上索引，Using temporary;
# 创建临时表用于分组后的排序;
SELECT c1 FROM t1 WHERE c1 = 1 GROUP BY c2; # group by c1[,c2]解决

索引失效

  1. 筛选条件未满足左前缀法则。
  2. 索引列上有计算、函数或隐式类型转换。
  3. 范围查找会导致其后索引列失效。
  4. 使用了!=、<>比较运算符。
  5. 使用了is null、is not null。
  6. 使用了%like%、%like，但like%可以相当于范围查找。
  7. 字符串未加单引号，发生了隐式类型转换。
  8. 使用了or关键字。

总结：只要理解了索引是一个有序结构就可以。复合索引就是多字段排序，凡是无法利用有序数据结构提高效率的，都会导致索引失效。

举例：CREATE INDEX idx_t1_c1c2c3 ON t1(c1,c2,c3); 可以类比为多字段排序 (1,3,7) (1,5,3) (1,5,4) (2,1,4) (3,4,3) (3,5,6) (3,7,4) 查询单独的c1可以用到索引，c1c2也可以，c1c2c3也可以，但是c2或c3或c2c3或c1c3都不行。 假设where c1 = 1 and c2 > 1 and c3 = 1; 此时c1和c2索引有效，但是c3索引失效了。因为c2是一个范围查找，在此条件下c3字段是无序的。 相当于是对(1,3,7) (1,5,3) (1,5,4)的c3字段查找。

小表驱动大表

select * from A where id in (select id from B);
等价于
for select id from B
	for select * from A where A.id = B.id

select * from A where exists (select 1 from B where B.id = A.id);
等价于
for select * from A
	for select * from B where A.id = B.id

当B表数据集小于A表数据集时，使用IN更好，反之EXISTS更好。

排序

分析排序sql会有Using index和Using filesort，应当尽量使用索引上排序，需要注意的是，在索引上排序需要多个字段同升或同降，其余与索引失效大致类似。

文件排序分为双路排序和单路排序，双路排序在MySQL 4.1前，会两次扫描磁盘发生两次IO操作。而单路排序则是将排序在buffer中进行，IO操作只有一次。

但是如果排序数据量大于了buffer大小，反而会造成更多次的IO操作。因此排序的优化可以分成三点：①尽量避免select *多字段会导致缓存溢出；②增大服务器参数sort_buffer_size的大小；③增大服务器参数max_length_for_sort_data的大小（数据量超过该值使用双路排序）。

GROUP BY也类似，因为分组之前是需要排序的，还有就是能写在where子句里就不要写在having子句里。

慢查询日志

  1. 查看是否开启
SHOW VARIABLES LIKE '%slow_query_log%';
  1. 开启慢查询日志
SET GLOBAL slow_query_log = 1;
  1. 查看慢查询阈值
SHOW VARIABLES LIKE '%long_query_time%';
  1. 修改慢查询阈值
# 设置后新开一个会话才能看到改变
SET GLOBAL long_query_time = 3;
  1. 查看当前有多少条慢查询记录
SHOW GLOBAL STATUS LIKE '%Slow_queries%';
  1. 配置文件
# 只有写在配置文件里才是永久修改
[mysqld]
slow_query_log = 1;
slow_query_log_file = /var/lib/mysql/slow.log;
long_query_time = 3;
log_output = FILE;
  1. 日志分析工具mysqldumpslow
# 返回日志中最多的10个sql
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log
# 返回日志中访问次数最多的10个sql
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log
# 返回按时间排序的前10条里含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log

show profile

MySQL提供的用于分析当前会话中sql执行的资源消耗情况。

  1. 查看是否开启
SHOW VARIABLES LIKE 'profiling';
  1. 开启
SET profiling = ON;
# 开启后运行sql语句
  1. 查看结果
# 会显示开启后执行的所有sql语句
SHOW PROFILES;
  1. 诊断sql
SHOW PROFILE cpu,block io FOR query Query_ID; # Query_ID是SHOW PROFILES中得到的
# 其他参数
ALL | BLOCK IO | CONTEXT SWITCHES | CPU | IPC | MEMORY | PAGE FAULTS | SOURCE | SWAPS
# 若诊断结果包含下列条目，则需要优化。
converting HEAP to MyISAM 查询结果太大，内存不够数据放到了磁盘上
Creating tmp table 创建了临时表（新建拷贝再删除）
Copying to tmp table on disk 内存中的临时表复制到了磁盘
locked 锁了
avatar
文章
阅读
粉丝