Abel'Blog

我干了什么?究竟拿了时间换了什么?

0%

MySQL-慢查询诊断

在 MySQL 中,你可以使用以下方法来诊断某个慢 SQL 的执行速度以及它命中的索引。

  1. 使用 EXPLAIN 分析 SQL 执行计划

MySQL 提供了 EXPLAIN 关键字,它可以告诉你:
• 查询的执行方式
• 是否使用索引
• 索引的选择情况
• 是否存在全表扫描

🔹 语法

EXPLAIN SELECT * FROM tb_person_data WHERE symbol = ‘CNY’ AND kline_type = ‘1m’ ORDER BY open_time DESC LIMIT 1;

🔹 结果示例

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tb_person_data index idx_symbol_time idx_symbol_time 12 NULL 100 Using where; Using index

字段解析
• type:索引使用类型(ALL=全表扫描,index=索引扫描,range=范围扫描,ref=索引查找)
• possible_keys:查询可能使用的索引
• key:实际使用的索引
• key_len:索引的字节长度
• rows:MySQL 估算的扫描行数
• Extra:
• Using index:查询只使用索引,不需要回表,速度最快
• Using where:需要额外的 WHERE 过滤
• Using temporary; Using filesort:可能存在排序或临时表,性能较差

➡️ 重点关注:

1.    key 是否为空?如果为空,说明没有命中索引。
2.    rows 数量是否过大?如果扫描行数过多,说明索引可能没起作用。

  1. 使用 SHOW INDEX FROM table 查看索引

如果 EXPLAIN 发现索引未被使用,你可以查看表的索引结构:

SHOW INDEX FROM tb_person_data;

结果示例

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Index_type
tb_person_data 0 PRIMARY 1 id A 100000 BTREE
tb_person_data 1 idx_symbol_time 1 symbol A 10000 BTREE
tb_person_data 1 idx_symbol_time 2 open_time A 5000 BTREE

关键点
• Key_name 是索引的名称。
• Column_name 是索引包含的列。
• Cardinality 代表索引的唯一性,值越大越好。
• Index_type 通常是 BTREE(默认索引类型)。

  1. 使用 SHOW PROFILE 诊断 SQL 执行步骤

你可以开启 SQL 性能分析:

SET profiling = 1;

然后运行你的 SQL:

SELECT * FROM tb_person_data WHERE symbol = ‘CNY’ AND kline_type = ‘1m’ ORDER BY open_time DESC LIMIT 1;

再查看 SQL 详细执行情况:

SHOW PROFILES;

选取 Query_ID 后再执行:

SHOW PROFILE FOR QUERY 1;

可以看到 CPU 时间、I/O 操作、排序、锁等待等 详细信息。

  1. 使用 SHOW STATUS LIKE ‘Handler%’ 观察索引命中

执行 SQL 后运行:

SHOW STATUS LIKE ‘Handler%’;

你会看到类似结果:

变量名 值
Handler_read_rnd_next 120000
Handler_read_key 100
Handler_read_first 1

如何分析
• Handler_read_rnd_next 值过大,说明存在全表扫描(索引未生效)。
• Handler_read_key 值较高,说明查询通过索引查找数据,性能较优。

  1. 使用 ANALYZE TABLE 更新索引统计信息

有时,索引统计信息过时会导致查询优化器选择错误的索引。可以执行:

ANALYZE TABLE tb_person_data;

这可以让 MySQL 重新计算索引统计信息,可能会改善查询性能。

  1. 可能的优化方法

如果 EXPLAIN 发现索引未命中,可以尝试以下优化:

✅ 创建合适的索引

如果你的 WHERE 条件涉及多个字段,可以创建联合索引:

CREATE INDEX idx_symbol_time ON tb_person_data(symbol, open_time);

这样 MySQL 可以高效利用索引进行 ORDER BY 操作。

✅ 避免 WHERE 条件中的计算

MySQL 无法使用索引:

WHERE DATE(open_time) = ‘2024-01-01’

优化后:

WHERE open_time >= ‘2024-01-01 00:00:00’ AND open_time < ‘2024-01-02 00:00:00’

✅ 避免 SELECT *,只查询需要的字段

SELECT id, open_time FROM tb_person_data WHERE symbol = ‘CNY’ AND kline_type = ‘1m’ ORDER BY open_time DESC LIMIT 1;

这样可以减少 I/O 负担,加速查询。

✅ 分区表优化

如果 tb_person_data 是一个大表,并且 open_time 是查询的主要条件,可以使用分区表:

ALTER TABLE tb_person_data PARTITION BY RANGE (UNIX_TIMESTAMP(open_time)) (
PARTITION p202401 VALUES LESS THAN (UNIX_TIMESTAMP(‘2024-02-01’)),
PARTITION p202402 VALUES LESS THAN (UNIX_TIMESTAMP(‘2024-03-01’))
);

这样 WHERE open_time < ‘2024-02-01’ 只会扫描对应分区,而不是整个表,提高查询效率。

总结
• 使用 EXPLAIN 确定索引是否生效
• 用 SHOW INDEX FROM 检查索引结构
• 使用 SHOW PROFILE 诊断执行瓶颈
• 观察 Handler_read_rnd_next 来判断是否是全表扫描
• 用 ANALYZE TABLE 更新索引统计
• 优化索引结构,避免 WHERE 计算,减少 SELECT *

这样,你可以准确分析 SQL 速度慢的原因,并优化查询效率! 🚀