MySQL 索引失效的 12 种场景与解决方案（附执行计划分析）

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `age` int NOT NULL,
  `city` varchar(20) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_age` (`name`, `age`),
  KEY `idx_phone` (`phone`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB;

-- phone 是 varchar，用数字查询导致全表扫描
EXPLAIN SELECT * FROM user WHERE phone = 13800138000;  -- ❌ type=ALL

-- 正确
EXPLAIN SELECT * FROM user WHERE phone = '13800138000'; -- ✅ type=ref

SELECT * FROM user WHERE YEAR(created_at) = 2026;  -- ❌

-- 改写成范围查询
SELECT * FROM user
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';  -- ✅

-- 联合索引 idx_name_age (name, age)
WHERE age = 18                -- ❌ 跳过 name
WHERE name LIKE '%Tom'        -- ❌ 左模糊
WHERE name = 'Tom' AND age > 10 -- ✅

WHERE name = 'Tom' OR city = '北京'  -- city 无索引 → 全表
-- 解决：给 city 加索引，或改成 UNION

WHERE age != 18   -- 通常不走索引
-- 改写：age > 18 OR age < 18（仍需评估选择性）

WHERE name IS NOT NULL  -- 数据量大时不走索引
-- 建议：用默认值 '' 代替 NULL

WHERE name LIKE '%abc'   -- ❌
WHERE name LIKE 'abc%'   -- ✅
-- 需要全文检索请用 FULLTEXT 或 ES

-- idx(a, b, c)
WHERE a = 1 AND b > 10 AND c = 5
-- c 无法用于索引过滤（只能用到 a, b）

-- 两表 JOIN 字段字符集不同 → 索引失效
ALTER TABLE t1 CONVERT TO CHARACTER SET utf8mb4;

-- 当匹配行数超过总数约 30% 时，优化器可能选全表
-- 可用 FORCE INDEX 强制，但应先确认统计信息准确
ANALYZE TABLE user;

-- 表 utf8mb4，连接用 utf8 → 转换导致失效
SET NAMES utf8mb4;

SELECT * FROM user WHERE name = 'Tom';
-- 需回表。改成覆盖索引可避免：
-- KEY idx_name_age_city (name, age, city)
SELECT name, age FROM user WHERE name = 'Tom';  -- Using index

-- 查看执行计划
EXPLAIN FORMAT=JSON SELECT ...;

-- 查看实际执行
EXPLAIN ANALYZE SELECT ...;

-- 慢查询
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;