mysql中避免全表扫描的优化技巧

type=ALL 表示全表扫描，是性能严重隐患，主因是索引未被有效使用，常见于函数操作、隐式转换、OR 多非索引字段、范围查询后索引列失效、排序字段未覆盖、大偏移分页及统计信息过期等。

为什么 EXPLAIN 看到 type=ALL 就该警惕

MySQL 执行计划中出现 type=ALL，代表正在走全表扫描——哪怕表只有几万行，只要查询频繁，I/O 和 CPU 压力也会快速上升。这不是“慢一点”的问题，而是并发一高就卡死的信号。关键不是“有没有索引”，而是“索引是否被真正用上”。常见诱因包括：查询条件用了函数（如 WHERE YEAR(create_time) = 2024）、 隐式类型转换 （WHERE user_id = '123' 但 user_id 是 INT）、或 OR 连接多个非索引字段。

WHERE 条件里别对索引列做运算或转换

索引是按原始值有序存储的，一旦在列上套函数或强制转换，优化器就无法做 B+ 树范围查找，只能退化为全表扫。比如：

SELECT * FROM orders WHERE DATE(create_time) = '2024-05-01';  -- ❌ 全表扫 SELECT * FROM orders WHERE create_time >= '2024-05-01' AND create_time <'2024-05-02';  -- ✅ 走索引 

其他典型陷阱：

• WHERE status + 0 = 1 → 改成 WHERE status = 1
• WHERE phone LIKE '%138%' → 左模糊无法用索引，考虑全文索引或前置冗余字段
• WHERE user_id = '1001'（user_id 是 INT）→ 改成 WHERE user_id = 1001

联合索引的最左前缀原则不是“从左开始用”，而是“连续匹配前缀”

建了 (a, b, c) 联合索引，并不意味着 WHERE b = 2 AND c = 3 能用上索引——它连最左列 a 都没出现，直接失效。能走索引的组合只有：

• WHERE a = 1
• WHERE a = 1 AND b = 2
• WHERE a = 1 AND b = 2 AND c = 3
• WHERE a = 1 AND c = 3（b 被跳过，但 c 仍可用，仅限于 a 等值时）

注意：WHERE a > 1 AND b = 2 中，b 可能无法用于索引过滤（范围查询后列失效），而 WHERE a IN (1,2,3) AND b = 2 则 b 仍有效——IN 在 MySQL 5.7+ 中被优化为等值列表处理。

ORDER BY 和 LIMIT 配合不当会绕过索引排序

即使 WHERE 走了索引，如果 ORDER BY 字段不在索引覆盖范围内，MySQL 仍要回表后做 filesort。例如：

SELECT id, name FROM users WHERE city = 'Beijing' ORDER BY age LIMIT 10;

若索引是 (city)，则 age 排序需额外排序；应建 (city, age) 覆盖索引。更隐蔽的问题是 LIMIT 偏移量过大：

• LIMIT 10000, 20 仍要扫描前 10020 行 → 改用游标分页（WHERE id > last_seen_id ORDER BY id LIMIT 20）
• ORDER BY 含多字段且方向不一致（如 ORDER BY a ASC, b DESC）→ MySQL 8.0+ 才支持混合方向索引，旧版本需统一方向或放弃索引排序

真正难排查的是“索引存在却不用”：比如统计信息过期（ANALYZE TABLE 可刷新）、或优化器误判行数（FORCE INDEX 可临时干预，但属权宜之计）。