MySQL(58)如何避免索引失效？

最新推荐文章于 2025-06-06 09:02:42 发布

辞暮尔尔-烟火年年

最新推荐文章于 2025-06-06 09:02:42 发布

阅读量891

点赞数 25

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_43012298/article/details/139229783

版权

MySQL 专栏收录该内容

67 篇文章

订阅专栏

索引失效会导致查询性能下降，因此了解并避免索引失效的情况是数据库性能优化的重要一环。下面详细介绍几种常见的索引失效原因及如何避免这些情况，并结合代码示例说明。

1. 避免在索引列上使用函数

在索引列上使用函数会导致索引失效，因为数据库无法直接使用索引来处理这种情况。

示例：

假设 emp_name 列上有一个索引：

CREATE INDEX idx_emp_name ON employees(emp_name);

以下查询会导致索引失效：

SELECT emp_id FROM employees WHERE UPPER(emp_name) = 'JOHN DOE';

解决方法：

将函数应用到查询参数上，而不是索引列上：

SELECT emp_id FROM employees WHERE emp_name = UPPER('john doe');

2. 避免在索引列上进行计算

在索引列上进行计算也会导致索引失效，因为数据库无法使用索引来处理计算结果。

示例：

假设 salary 列上有一个索引：

CREATE INDEX idx_salary ON employees(salary);

以下查询会导致索引失效：

SELECT emp_id FROM employees WHERE salary * 1.1 > 55000;

解决方法：

将计算移到比较值上：

SELECT emp_id FROM employees WHERE salary > 55000 / 1.1;

3. 避免使用不等号操作

不等号操作（!= 或 <, >, <=, >= 等）在索引列上会导致索引失效，尤其是复合索引的后续列。

示例：

假设在 department_id 和 salary 列上有一个复合索引：

CREATE INDEX idx_dept_salary ON employees(department_id, salary);

以下查询会导致索引失效：

SELECT emp_id FROM employees WHERE department_id != 2 AND salary > 50000;

解决方法：

尽量使用等值条件替代不等值条件：

SELECT emp_id FROM employees WHERE department_id IN (1, 3, 4) AND salary > 50000;

4. 避免在索引列前使用通配符

在索引列上使用前置通配符（如 % 开头的 LIKE 查询）会导致索引失效，因为数据库无法使用索引来加速前置通配符的匹配。

示例：

假设 emp_name 列上有一个索引：

CREATE INDEX idx_emp_name ON employees(emp_name);

以下查询会导致索引失效：

SELECT emp_id FROM employees WHERE emp_name LIKE '%Doe';

解决方法：

尽量避免使用前置通配符：

SELECT emp_id FROM employees WHERE emp_name LIKE 'John%';

5. 避免过多的 OR 条件

在 OR 条件中的列没有索引时，会导致索引失效。

示例：

假设 emp_name 和 department_id 列上分别有索引：

CREATE INDEX idx_emp_name ON employees(emp_name);
CREATE INDEX idx_department_id ON employees(department_id);

以下查询会导致索引失效：

SELECT emp_id FROM employees WHERE emp_name = 'John Doe' OR department_id = 2;

解决方法：

将 OR 条件拆分为两个独立的查询，并使用 UNION 合并结果：

SELECT emp_id FROM employees WHERE emp_name = 'John Doe'
UNION
SELECT emp_id FROM employees WHERE department_id = 2;

6. 避免隐式类型转换

隐式类型转换会导致索引失效，因为数据库需要将列进行类型转换，无法直接利用索引。

示例：

假设 emp_id 列是 INT 类型并有索引：

CREATE INDEX idx_emp_id ON employees(emp_id);

以下查询会导致索引失效：

SELECT emp_id FROM employees WHERE emp_id = '123';

解决方法：

确保查询参数的类型与列的类型一致：

SELECT emp_id FROM employees WHERE emp_id = 123;

7. 避免使用低选择性列

低选择性的列（如布尔值、性别等）即使创建索引，也可能失效，因为数据库会认为全表扫描更有效。

示例：

假设 gender 列有一个索引：

CREATE INDEX idx_gender ON employees(gender);

以下查询可能会导致索引失效：

SELECT emp_id FROM employees WHERE gender = 'M';

解决方法：

尽量避免在低选择性列上创建单独索引，可以结合其他高选择性列创建复合索引：

CREATE INDEX idx_gender_dept ON employees(gender, department_id);

使用 `EXPLAIN` 验证索引是否失效

通过使用 EXPLAIN 分析查询的执行计划，可以验证索引是否被使用。

验证示例

EXPLAIN SELECT emp_id FROM employees WHERE emp_name = UPPER('john doe');

假设 EXPLAIN 输出如下：

+----+-------------+----------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key      | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | employees| NULL       | ref   | idx_emp_name  | idx_emp_name| 101   | const| 100   |   100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+

在 key 列中显示 idx_emp_name 表示查询使用了索引 idx_emp_name。