数据库优化 - 慢查询 (Slow query)

提示

慢查询是指在 MySQL 数据库中执行时间超过预设阈值的 SQL 语句。这些查询可能会影响数据库的整体性能，导致用户体验下降。本文将详细介绍慢查询的相关知识及优化方法。

慢查询是指执行时间超过指定阈值的 SQL 语句。在 MySQL 中，我们可以通过设置long_query_time参数来定义慢查询的时间阈值。默认情况下，这个值设置为 10 秒，但根据实际业务需求，我们可以调整这个值。

慢查询的危害

慢查询会对数据库系统造成多方面的影响：

  1. 占用数据库连接资源，导致其他查询需要等待
  2. 增加服务器负载，影响整体性能
  3. 可能导致数据库响应变慢，影响用户体验
  4. 在极端情况下，可能造成数据库服务不可用

慢查询的常见原因

  • 索引是提高查询效率的重要手段，但不恰当的索引使用反而会降低查询性能。
  • 使用了不当的查询方式（如使用 SELECT *）,或者查询条件过于复杂,使用了不必要的表链接,或者查询了不需要的的字段都可能导致查询性能下降。
  • 当表中的数据量过大时，即使使用了索引，查询性能也可能下降。这时需要考虑：
    • 数据分表
    • 历史数据归档
    • 使用分区表

如何发现慢查询

在 MySQL 中，我们可以通过以下方式开启慢查询日志：

注意

重启 mysql 客户端后,客户端设置和统计慢查询日志条数就会清零，即所有配置修改会还原。 在配置文件(通常是 my.cnf 或 my.ini)修改才能永久改变，否则重启数据库就还原了

开启慢查询日志

开启慢查询日志，由参数 slow_query_log 决定是否开启，在 MySQL 命令行下输入下面的命令

set global slow_query_log=on;

设置慢查询阈值

set global long_query_time=1;

只要你的 SQL 实际执行时间超过了这个阈值，就会被记录到慢查询日志里面。这个阈值默认是 10s，线上业务一般建议把 long_query_time 设置为 1s，如果某个业务的 MySQL 要求比较高的 QPS，可设置慢查询为 0.1s。发现慢查询及时优化或者提醒开发改写。一般测试环境建议 long_query_time 设置的阀值比生产环境的小，比如生产环境是 1s，则测试环境建议配置成 0.5s。便于在测试环境及时发现一些效率的 SQL。

查看慢查询全局变量

show global variables like '%quer%';

hsv

变量解释:

变量名默认值说明
binlog_rows_query_log_eventsOFF是否在二进制日志中记录查询语句，开启后可以记录导致数据变更的 SQL 语句
ft_query_expansion_limit20全文索引查询扩展时返回的最大记录数
have_query_cacheYES是否支持查询缓存功能
long_query_time10.000000慢查询的时间阈值，单位秒，超过此时间的查询会被记录到慢查询日志
query_alloc_block_size8192为查询分配内存块的大小
query_cache_limit1048576查询缓存中单个查询结果的最大值，超过此值不会被缓存
query_cache_min_res_unit4096查询缓存分配内存块的最小单位
query_cache_size1048576查询缓存的总大小
query_cache_typeOFF查询缓存的类型：OFF(0)表示关闭，ON(1)表示开启，DEMAND(2)表示按需缓存
query_cache_wlock_invalidateOFF当表被写锁定时，是否使查询缓存失效
query_prealloc_size8192查询预分配内存的大小
slow_query_logOFF是否开启慢查询日志
slow_query_log_file/var/lib/mysql/duke-slow.log慢查询日志文件的位置
log_queries_not_using_indexesOFF是否记录未使用索引的查询到慢查询日志

注意事项

  1. query_cache_type 和 query_cache_size 在 MySQL 8.0 中已被移除，建议使用其他缓存方案
  2. long_query_time 建议根据业务实际情况调整，一般建议设置为 1-3 秒
  3. slow_query_log 开启后会对性能有一定影响，建议在需要排查问题时临时开启
  4. log_queries_not_using_indexes 开启后可能会产生大量日志，建议谨慎使用

使用性能分析工具(EXPLAIN)

EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，获取 SQL 的执行信息。通过分析这些信息，我们可以了解查询的执行计划，从而优化查询性能。

基本用法

-- 在 SELECT 语句前加上 EXPLAIN 关键字
EXPLAIN SELECT * FROM users WHERE age > 18;

-- 也可以使用 EXPLAIN FORMAT=JSON 获取更详细的信息
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 18;

EXPLAIN 输出字段说明

字段名说明
id查询的序号，表示查询中执行 SELECT 子句或操作表的顺序
select_type查询类型，如 SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等
table输出行所引用的表名
partitions匹配的分区信息，对于非分区表该值为 NULL
type访问类型，表示 MySQL 在表中找到所需行的方式，从最好到最差依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >ALL
possible_keys可能使用的索引列表
key实际使用的索引
key_len使用的索引长度
ref显示哪些列或常量被用于查找索引列上的值
rowsMySQL 估计要检查的行数
filtered表示返回结果的行数占需读取行数的百分比
Extra包含 MySQL 解决查询的详细信息

重要字段详解

type 字段（访问类型）
  • system：表中只有一行记录，这是 const 类型的特例
  • const：通过主键或唯一索引查询，最多只返回一行数据
  • eq_ref：使用主键或唯一索引进行连接查询
  • ref：使用非唯一索引进行查询
  • range：使用索引进行范围查询
  • index：全索引扫描
  • ALL：全表扫描，性能最差
Extra 字段（额外信息）
  • Using index：使用覆盖索引
  • Using where：使用 WHERE 条件过滤
  • Using temporary：使用临时表
  • Using filesort：需要额外的排序操作
  • Using join buffer：使用连接缓冲

优化建议

  1. 避免全表扫描

    • 确保查询条件中的字段都建立了适当的索引
    • 避免使用 SELECT *，只查询需要的字段

  2. 优化索引使用

    • 检查 possible_keys 和 key 字段，确保使用了正确的索引
    • 注意 key_len 字段，过长的索引可能影响性能

  3. 减少临时表和文件排序

    • 优化 ORDER BY 和 GROUP BY 语句
    • 使用覆盖索引避免回表操作

  4. 控制查询范围

    • 使用 LIMIT 限制返回结果数量
    • 避免使用 SELECT DISTINCT

实际案例分析

-- 示例1：简单的索引查询
EXPLAIN SELECT * FROM users WHERE id = 1;
-- 预期结果：type = const，表示通过主键查询

-- 示例2：范围查询
EXPLAIN SELECT * FROM users WHERE age BETWEEN 18 AND 30;
-- 预期结果：type = range，表示使用索引进行范围查询

-- 示例3：连接查询
EXPLAIN SELECT u.name, o.order_no
FROM users u
JOIN orders o ON u.id = o.user_id;
-- 检查连接查询的索引使用情况

使用建议

  1. 在开发环境中经常使用 EXPLAIN 分析 SQL 语句
  2. 关注 type 字段，尽量避免出现 ALL 类型
  3. 注意 Extra 字段中的警告信息
  4. 定期检查慢查询日志中的 SQL 语句

总结

慢查询优化是一个持续的过程，需要从多个方面进行考虑和优化。通过合理的索引设计、SQL 优化和数据库架构调整，我们可以显著提升数据库性能，为用户提供更好的服务体验。

最近更新：:
Contributors: Duke