索引失效的场景分析
引言 创建了索引不一定会使用。理解索引失效的场景，才能写出高效的SQL。 一、违反最左前缀原则 1.1 联合索引规则 -- 索引 CREATE INDEX idx_a_b_c ON table(a, b, c); -- ✅ 使用索引 WHERE a = 1; WHERE a = 1 AND b = 2; WHERE a = 1 AND b = 2 AND c = 3; -- ❌ 不使用索引 WHERE b = 2; -- 缺少a WHERE c = 3; -- 缺少a和b WHERE b = 2 AND c = 3; -- 缺少a 1.2 跳跃列 -- 索引 CREATE INDEX idx_a_b_c ON table(a, b, c); -- ⚠️ 部分使用索引 WHERE a = 1 AND c = 3; -- 只使用a，c失效 二、索引列上使用函数 2.1 函数导致失效 -- ❌ 失效：YEAR函数 SELECT * FROM orders WHERE YEAR(created_at) = 2024; -- ✅ 改进：使用范围查询 SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31 23:59:59'; 2.2 常见函数失效案例 -- ❌ 失效 WHERE DATE(created_at) = '2024-11-21'; WHERE LEFT(name, 3) = 'abc'; WHERE UPPER(email) = 'USER@EXAMPLE.COM'; WHERE price * 0.8 > 100; -- ✅ 改进 WHERE created_at >= '2024-11-21' AND created_at < '2024-11-22'; WHERE name LIKE 'abc%'; WHERE email = 'user@example.com'; -- 应用层转小写 WHERE price > 125; -- 100 / 0.8 三、隐式类型转换 3.1 字符串与数字 -- 表结构 CREATE TABLE users ( id INT, phone VARCHAR(20) -- 字符串类型 ); CREATE INDEX idx_phone ON users(phone); -- ❌ 失效：数字查询字符串列 SELECT * FROM users WHERE phone = 13800138000; -- 数字 -- MySQL转换为：WHERE CAST(phone AS SIGNED) = 13800138000 -- 索引失效 -- ✅ 使用索引 SELECT * FROM users WHERE phone = '13800138000'; -- 字符串 3.2 反向情况 -- 表结构 CREATE TABLE users ( id INT, age INT -- 数字类型 ); CREATE INDEX idx_age ON users(age); -- ✅ 使用索引：字符串查询数字列 SELECT * FROM users WHERE age = '20'; -- MySQL转换为：WHERE age = CAST('20' AS SIGNED) -- 索引仍然有效 规则：字符串列查询时必须用字符串类型。 ...