在 MySQL 中,你可以使用以下方法来诊断某个慢 SQL 的执行速度以及它命中的索引。
⸻
- 使用 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 数量是否过大?如果扫描行数过多,说明索引可能没起作用。
⸻
- 使用 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(默认索引类型)。
⸻
- 使用 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 操作、排序、锁等待等 详细信息。
⸻
- 使用 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 值较高,说明查询通过索引查找数据,性能较优。
⸻
- 使用 ANALYZE TABLE 更新索引统计信息
有时,索引统计信息过时会导致查询优化器选择错误的索引。可以执行:
ANALYZE TABLE tb_person_data;
这可以让 MySQL 重新计算索引统计信息,可能会改善查询性能。
⸻
- 可能的优化方法
如果 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 速度慢的原因,并优化查询效率! 🚀