浅谈SQL不走索引的几种常见情况

以下是几种常见的情况，结合原理和示例说明：

核心原则

数据库优化器（如MySQL的CBO）会选择它认为成本最低的执行路径。即使有索引，如果优化器估算使用索引的成本比全表扫描还高，它就会放弃索引。

常见情况分析

当在WHERE子句中对索引列进行运算、使用函数或类型转换时，索引通常会失效。

-- 情况1: 使用函数
SELECT * FROM users WHERE UPPER(name) = 'ALICE'; -- name列有索引，但失效
-- 应改为: SELECT * FROM users WHERE name = 'Alice';

-- 情况2: 列参与运算
SELECT * FROM orders WHERE total_amount * 1.1 > 1000; -- total_amount有索引，失效
-- 应改为: SELECT * FROM orders WHERE total_amount > 1000 / 1.1;

-- 情况3: 隐式类型转换 (常见陷阱!)
SELECT * FROM users WHERE phone = 13800138000; -- phone是varchar类型，索引失效
-- 数据库会将phone列逐行转换为数字再比较，相当于对列用了函数
-- 应改为: SELECT * FROM users WHERE phone = '13800138000';

最左前缀匹配原则：对于B+Tree索引，如果无法利用最左前缀，索引就失效。

SELECT * FROM articles WHERE title LIKE '%数据库%'; -- 无法使用title索引
SELECT * FROM articles WHERE title LIKE '数据库%'; -- 可以使用title索引 (范围查询)

对于组合索引 INDEX(a, b, c)，查询条件必须包含a，才能有效利用该索引。

-- 假设有联合索引 idx_name_age (name, age)
SELECT * FROM employees WHERE age = 30; -- 无法使用 idx_name_age，因为跳过了name
SELECT * FROM employees WHERE name = '张三' AND age = 30; -- 可以使用索引
SELECT * FROM employees WHERE age = 30 AND name = '张三'; -- 也可以！优化器会调整顺序

这些否定操作通常会导致优化器选择全表扫描，因为它需要检查几乎所有行。

SELECT * FROM products WHERE status != 'active'; -- 即使status有索引，也可能失效
-- 如果'active'占绝大多数，可以尝试改为：
-- SELECT * FROM products WHERE status IN ('inactive', 'pending');

如果OR的一侧没有可用索引，优化器可能选择全表扫描。

SELECT * FROM users WHERE mobile = '138' OR email = 'a@b.com';
-- 假设mobile有索引，email没有索引。优化器可能选择全表扫描，而不是对mobile走索引再合并。
-- 解决方案：为email也建立索引，或改用UNION：
-- SELECT * FROM users WHERE mobile = '138' 
-- UNION 
-- SELECT * FROM users WHERE email = 'a@b.com';

当某个值在表中占比非常高时，使用索引回表的成本可能高于直接全表扫描。

-- 例如，status字段有索引，但90%的记录都是 status = 'SUCCESS'
SELECT * FROM orders WHERE status = 'SUCCESS'; -- 优化器可能选择全表扫描
SELECT * FROM orders WHERE status = 'PENDING'; -- 只占1%，一定会用索引

原理：使用索引需要两次查找（索引查找+回表取数据），如果过滤掉的行很少，这个成本就不划算。

优化器认为需要回表查询的数据行数过多时，会放弃索引。

SELECT * FROM logs WHERE create_time > '2023-01-01'; -- 如果满足条件的数据超过表的20%-30%，可能全表扫描
-- 对于IN列表过长也一样
SELECT * FROM items WHERE id IN (1,2,3,...,10000); -- IN列表极大，可能全扫

当表的行数很少（例如小于1000行，具体阈值取决于配置）时，优化器认为直接全表扫描的I/O成本低于“计算索引+回表”的随机I/O成本。

SELECT * FROM config_table WHERE key_name = 'timeout'; -- 表只有100行，不会用索引

数据库依靠统计信息（如基数、直方图）来估算成本。如果统计信息过期，优化器可能会做出错误判断。

-- 解决方案：手动更新统计信息 (不同数据库命令不同)
-- MySQL: ANALYZE TABLE table_name;
-- PostgreSQL: ANALYZE table_name;
-- SQL Server: UPDATE STATISTICS table_name;

即使WHERE条件用到了索引，但如果需要返回的列很多且不在索引中，就需要大量的回表操作。当回表成本很高时，优化器可能选择直接全表扫描，避免随机I/O。

-- 假设在 age 上有索引，但查询需要所有列
SELECT * FROM students WHERE age > 18; -- 可能因为回表成本高而不走索引
-- 如果只需索引列和主键，则走索引可能性大
SELECT id, age FROM students WHERE age > 18; -- 覆盖索引，性能极佳

诊断与解决方法

EXPLAIN SELECT * FROM users WHERE name = 'Alice';

• 关注 type 列（ALL 表示全表扫描）、key 列（使用的索引）、rows 列（估算行数）。

理解这些场景背后的原理（B+Tree数据结构、成本模型、最左前缀原则、回表代价），才能更好地设计索引和编写高效的SQL语句。