当前位置：首页 > ai >正文

数据库索引失效的原因+示例

ai 2025/9/2 21:42:28

数据库索引失效的核心原因

查询条件无法触发索引的有序性或选择性，导致数据库放弃索引转而执行全表扫描。

索引本质是按 “索引列原始值” 排序的结构，若查询中对索引列进行函数、算术等运算，数据库无法直接匹配索引值，只能全表计算后筛选。

表结构：user(id, age, name)，age 列建立普通索引 idx_age。
失效 SQL：查询年龄大于 30 的用户，对 age 做加法运算。
```
SELECT * FROM user WHERE age + 1 > 31; 
```
数据库需先计算每一行的 age + 1，再判断是否大于 31，无法使用 idx_age，触发全表扫描。
生效 SQL：将运算移到条件值侧，保留索引列原始值。
```
SELECT * FROM user WHERE age > 31 - 1; -- 等价于 age > 30
```

与运算类似，函数会改变索引列的原始值，导致索引的有序结构失效，数据库必须全表执行函数后筛选。

表结构：order(id, create_time, amount)，create_time 建立索引 idx_create_time。
失效 SQL：查询 2024 年 1 月的订单，对 create_time 使用 DATE_FORMAT 函数。
```
SELECT * FROM order WHERE DATE_FORMAT(create_time, '%Y-%m') = '2024-01';
```
数据库需对所有行的 create_time 执行函数转换，无法利用索引的时间排序，导致全表扫描。

生效 SQL：用范围查询匹配索引列原始值（需确保 create_time 格式正确）。

SELECT * FROM order WHERE create_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59';

索引的优势是 “快速定位符合条件的行”，而 !=、<>、NOT IN、IS NOT NULL 等否定条件，会导致符合条件的行分散在全表，索引无法高效筛选，通常触发全表扫描。

表结构：product(id, status, price)，status 建立索引 idx_status（status 取值：0 - 下架，1 - 上架）。
失效 SQL：查询非上架状态的商品（否定判断）。
```
SELECT * FROM product WHERE status != 1; -- 等价于 status = 0
```
即使结果集很小，数据库也可能放弃索引（不同引擎优化策略有差异，但若 status 取值更多，失效概率极高）。
生效 SQL：用正向条件明确匹配索引值。
```
SELECT * FROM product WHERE status = 0;
```

若索引列是字符串类型（如 varchar），查询时条件值不加引号，数据库会将索引列隐式转换为数值类型（如 varchar -> int），相当于对索引列执行 “隐形函数”，导致索引失效。

表结构：user(id, phone, address)，phone 建立索引 idx_phone（phone 类型：varchar(20)）。
失效 SQL：查询手机号为 13800138000 的用户，条件值不加引号。
```
SELECT * FROM user WHERE phone = 13800138000; -- 数值类型 vs 字符串索引列
```
数据库会执行 CAST(phone AS UNSIGNED) 转换，破坏索引结构，触发全表扫描。
生效 SQL：条件值加引号，确保类型匹配。
```
SELECT * FROM user WHERE phone = '13800138000';
```

字符串索引默认是 “前缀索引”（按字符串前 N 个字符排序），若模糊查询用 % 开头（如 %abc），索引无法定位到起始字符，只能全表匹配。

表结构：article(id, title, content)，title 建立索引 idx_title（title 类型：varchar(100)）。
失效 SQL：查询标题包含 “数据库” 的文章，% 开头。
```
SELECT * FROM article WHERE title LIKE '%数据库%';
```
索引按 title 前缀排序，无法确定 “数据库” 在标题中的位置，只能全表扫描。

生效 SQL：% 仅在末尾（前缀匹配），利用索引有序性。

SELECT * FROM article WHERE title LIKE '数据库%'; -- 匹配“数据库入门”“数据库优化”等

联合索引（如 idx_a_b_c）的排序逻辑是 “先按 A 排，A 相同再按 B 排，B 相同再按 C 排”，查询时必须从最左列 A 开始匹配，否则索引无法生效（部分场景可能触发 “索引覆盖”，但非完整索引利用）。

表结构：order(id, user_id, product_id, create_time)，建立联合索引 idx_user_product(user_id, product_id)。
失效 SQL：跳过最左列 user_id，直接查询 product_id。
```
SELECT * FROM order WHERE product_id = 1001;
```
联合索引 idx_user_product 以 user_id 为排序基础，无 user_id 条件时，无法定位 product_id 的位置，索引失效。
生效 SQL：包含最左列 user_id，符合最左前缀原则。
```
SELECT * FROM order WHERE user_id = 501 AND product_id = 1001;
```