恐怖解谜密室逃脱
109.73M · 2026-03-09
要优化慢查询，首先必须理解索引为何能提速、以及它的 “短板”—— 这是所有优化方案的底层逻辑。
MySQL 中默认的索引结构是 B + 树（哈希索引仅适用于 Memory 引擎，且不支持范围查询），其核心特性：
非叶子节点：仅存索引键和子节点指针，不存数据，能让树的高度极低（百万级数据的 B + 树高度通常≤3），实现 “快速定位”；
叶子节点：
首先要明确 “哪些 SQL 慢、慢在哪”，而非盲目加索引。
开启慢查询日志（临时生效，重启失效）：
# 开启慢查询日志
set global slow_query_log = ON;
# 设置慢查询阈值（单位：秒，建议设0.1秒，捕捉潜在慢查询）
set global long_query_time = 0.1;
# 指定慢查询日志存储路径
set global slow_query_log_file = '/var/lib/mysql/slow.log';
# 记录未使用索引的查询（辅助定位）
set global log_queries_not_using_indexes = ON;
分析慢查询日志：
用 mysqldumpslow 快速统计：
# 按执行次数排序，显示前10条慢查询
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log
# 按耗时排序，显示前10条
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log
用 explain 分析 SQL 执行计划（核心）：
-- 分析目标SQL
EXPLAIN SELECT * FROM order_info WHERE user_id = 123 AND create_time > '2026-01-01';
重点关注 explain 结果中的字段：
| 字段 | 核心含义 |
|---|---|
| type | 访问类型（ALL/INDEX/RANGE/REF/eq_ref/CONST），ALL（全表扫描）是优化重点 |
| key | 实际使用的索引（NULL 表示未用索引） |
| rows | MySQL 预估要扫描的行数（越大越慢） |
| Extra | 额外信息（Using filesort/Using temporary 是性能杀手，需重点优化） |
索引失效的本质是：SQL 条件破坏了 B + 树的 “有序性”，导致 MySQL 无法通过索引快速定位，只能全表 / 全索引扫描。常见索引失效场景 + 原理 + 解决方案：
表格
| 失效场景 | 底层原理 | 解决方案 |
|---|---|---|
| 索引列做函数 / 运算 | 函数 / 运算会改变索引键的有序性，MySQL 无法匹配索引树 | 避免索引列运算：WHERE id+1=10 → WHERE id=9 |
| 使用模糊查询前缀 % | LIKE '%abc' 无法利用 B + 树的有序性（前缀无序） | 仅用后缀模糊：LIKE 'abc%'；或用全文索引 |
| 用 OR 连接非索引列 | OR 两边有一个无索引，MySQL 会放弃索引（无法同时匹配两个树） | 给 OR 两边字段加联合索引；或拆分为两个查询 |
| 联合索引不满足最左前缀 | 联合索引 (a,b,c) 的 B + 树按 a→b→c 排序，跳过 a 查 b/c 会破坏有序性 | 遵循 “最左前缀原则”，查询条件包含联合索引的左侧列 |
| 索引列用 NULL/NOT NULL | NULL 会破坏索引键的比较逻辑，MySQL 可能放弃索引 | 字段设为 NOT NULL，用默认值替代 NULL |
| 字符串不加引号 | WHERE phone=13800138000 会隐式转换，破坏索引有序性 | 字符串条件加引号：WHERE phone='13800138000' |
核心原则：用最少的索引，覆盖最多的高频查询；减少回表和索引维护代价。
单值索引 vs 联合索引：
user_id + create_time），单值索引（user_id、create_time）会触发 “索引合并”（效率低），而联合索引（user_id, create_time）可直接按有序性匹配。覆盖索引（减少回表） ：
Using index）。SELECT id, order_no, amount FROM order_info WHERE user_id=123优化：创建联合索引 idx_userid_orderno_amount (user_id, order_no, amount)，实现覆盖索引，避免回表。主键索引的优化：
冗余索引清理：
idx_a_b (a,b)，则删除idx_a (a)。** 避免 SELECT ***：
限制结果集大小：
原理：LIMIT 可让 MySQL 提前终止索引扫描，减少 IO。
实践：分页查询必须加 LIMIT，且避免LIMIT 10000, 10（需扫描 10010 行），优化为：
-- 优化前（慢）
SELECT * FROM order_info LIMIT 10000, 10;
-- 优化后（用主键定位，仅扫描10行）
SELECT * FROM order_info WHERE id > 10000 LIMIT 10;
JOIN 查询优化：
原理：小表驱动大表（减少循环次数），且 JOIN 字段加索引（避免笛卡尔积）。
-- 优化前（大表驱动小表）
SELECT * FROM big_table b JOIN small_table s ON b.id = s.big_id;
-- 优化后（小表驱动大表）
SELECT * FROM small_table s JOIN big_table b ON s.big_id = b.id;
同时给 big_table.id 和 small_table.big_id 加索引。
避免子查询：
分库分表：
user_id % 100分 100 表），或按时间范围分表（如订单表按月份分表）。查询缓存（按需使用） ：
调整 MySQL 参数：
innodb_buffer_pool_size：设置为物理内存的 50%-70%，让索引和数据尽可能缓存在内存，减少磁盘 IO；query_cache_size：MySQL5.7 及以下可用，设置合理缓存大小（避免过大导致内存浪费）。优化后需验证效果，避免 “越优化越慢”：
explain 结果：type 是否从 ALL 变为 RANGE/REF，rows 是否减少，Extra 是否消除 Using filesort/Using temporary；SELECT SQL_NO_CACHE ... 避免查询缓存干扰，统计执行时间；show processlist 查看慢查询是否减少，show engine innodb status 查看 IO 和锁等待情况。
