索引失效会导致查询性能下降，因此了解并避免索引失效的情况是数据库性能优化的重要一环。下面详细介绍几种常见的索引失效原因及如何避免这些情况，并结合代码示例说明。
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。
小结
避免索引失效需要注意以下几点：
- 避免在索引列上使用函数和计算。
- 尽量使用等值条件，避免不等号操作。
- 避免在索引列前使用通配符。
- 避免过多的
OR条件，使用UNION替代。 - 确保查询参数类型与列类型一致，避免隐式类型转换。
- 避免在低选择性列上创建单独索引，考虑复合索引。
通过合理设计和使用索引，以及定期使用 EXPLAIN 分析查询性能，可以确保索引有效，提高数据库查询性能。
