SQL索引失效的常见情况_sql索引失效有哪些情况会失效-优快云博客

本文链接：https://blog.youkuaiyun.com/TeAmo_Fa/article/details/146522402

索引（Index）是数据库优化中最重要的机制之一，它的作用主要包括：

加速数据查找
- 索引类似于书的目录，能够快速定位所需数据，而不需要扫描整个表。
- 示例：如果 employees 表 id 列有索引：
```
SELECT * FROM employees WHERE id = 123;
```
  - 无索引：全表扫描（O(N)）。
  - 有索引：二分查找或 B+ 树搜索（O(log N)）。
优化排序（ORDER BY）
- 索引列上的排序可以直接使用索引，加速查询：
```
SELECT * FROM employees ORDER BY salary;
```
  - 有索引（salary 列）：数据库可以直接按索引顺序返回数据，而不需要额外排序。
  - 无索引：数据库需要排序整个结果集（O(N log N)）。
优化分组（GROUP BY）
- 索引列上的 GROUP BY 计算更快：
```
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;
```
  - 有索引（department_id）：数据已按 department_id 排序，数据库可以直接进行分组。
  - 无索引：数据库可能需要全表扫描和排序。
提高关联查询效率（JOIN）
- 索引能加速表之间的关联：
```
SELECT e.name, d.name 
FROM employees e 
JOIN departments d ON e.department_id = d.id;
```
  - 有索引（e.department_id, d.id）：数据库可以使用索引快速匹配两张表。
  - 无索引：数据库可能需要嵌套循环（Nested Loop Join），大数据量时性能会很差。

尽管索引能优化查询，但某些情况下索引会失效，导致数据库退化为全表扫描，常见原因包括：

SELECT * FROM employees WHERE department_id = 3 OR salary > 5000;

索引失效原因：

✅ 优化方案

使用联合索引（department_id, salary）：

CREATE INDEX idx_dept_salary ON employees(department_id, salary);

拆分查询再 UNION ALL：

SELECT * FROM employees WHERE department_id = 3
UNION ALL
SELECT * FROM employees WHERE salary > 5000;

这样可以让 department_id 和 salary 各自利用索引。

SELECT * FROM employees WHERE name LIKE '%John%';

索引失效原因：

✅ 优化方案

避免前导 %：
```
SELECT * FROM employees WHERE name LIKE 'John%';
```
- 这样索引可以高效查找匹配的记录。

使用全文索引（Full-Text Index）（适用于 MySQL）：

ALTER TABLE employees ADD FULLTEXT(name);
SELECT * FROM employees WHERE MATCH(name) AGAINST('John');

SELECT * FROM employees WHERE YEAR(birth_date) = 1990;

索引失效原因：

✅ 优化方案

避免对索引列进行计算，改写 SQL：
```
SELECT * FROM employees WHERE birth_date BETWEEN '1990-01-01' AND '1990-12-31';
```
- 这样 birth_date 索引能直接生效。

SELECT * FROM employees WHERE department_id != 3;

索引失效原因：

✅ 优化方案

用 BETWEEN 或 IN 代替：

SELECT * FROM employees WHERE department_id IN (1, 2, 4, 5);

SELECT * FROM employees WHERE department_id IS NULL;

索引失效原因：

✅ 优化方案

使用默认值代替 NULL（如 0）：

ALTER TABLE employees MODIFY department_id INT DEFAULT 0;
UPDATE employees SET department_id = 0 WHERE department_id IS NULL;

建立索引时包含 NULL 值：

CREATE INDEX idx_department_id ON employees(department_id) WHERE department_id IS NOT NULL;

SELECT * FROM employees WHERE phone_number = 1234567890;

索引失效原因：

phone_number 是 VARCHAR，但查询值是 INT，数据库会进行类型转换：
```
SELECT * FROM employees WHERE CAST(phone_number AS INT) = 1234567890;
```
索引无法生效，导致全表扫描。

✅ 优化方案

保持数据类型一致：

SELECT * FROM employees WHERE phone_number = '1234567890';