MySQL 索引在特定查询场景下可能失效，导致 MySQL 不使用索引，进而增加查询的时间和资源消耗。理解索引失效的场景和原因，有助于避免不必要的全表扫描并提升查询性能。下面详细讲解 MySQL 索引失效的常见场景及具体原因。
1. 不符合最左前缀原则
MySQL 在使用复合索引（即包含多个列的索引）时，必须遵循最左前缀原则，即查询条件必须从索引的最左列开始并按顺序进行匹配。如果查询跳过了最左列，或者查询中间的列缺失，索引会失效。
示例
假设我们有如下表结构和复合索引：
sqlCopy codeCREATE TABLE employees (
id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
age INT,
department VARCHAR(50),
INDEX idx_name_age_department (first_name, age, department)
);
查询示例：
- 索引有效：sqlCopy code
SELECT * FROM employees WHERE first_name = 'Alice'; -- 使用索引 SELECT * FROM employees WHERE first_name = 'Alice' AND age = 30; -- 使用索引 SELECT * FROM employees WHERE first_name = 'Alice' AND age = 30 AND department = 'HR'; -- 使用索引 - 索引失效：sqlCopy code
SELECT * FROM employees WHERE age = 30; -- 跳过了最左列 first_name，索引失效 SELECT * FROM employees WHERE age = 30 AND department = 'HR'; -- 跳过了最左列 first_name，索引失效
原因：复合索引必须从最左列开始依次匹配才能生效。未按照顺序使用索引列时，MySQL 无法按顺序扫描索引，导致索引失效。
2. 在索引列上使用函数或表达式
如果在查询条件中对索引列使用了函数、运算符或表达式，索引会失效，因为 MySQL 无法对索引列的变换结果进行优化。
示例
假设我们有一个表 employees，索引创建如下：
sqlCopy codeCREATE INDEX idx_age ON employees(age);
查询示例：
- 索引有效：sqlCopy code
SELECT * FROM employees WHERE age = 30; -- 使用索引 - 索引失效：sqlCopy code
SELECT * FROM employees WHERE YEAR(birth_date) = 1990; -- 假设 age 是通过 birth_date 计算得来 SELECT * FROM employees WHERE age + 1 = 31; -- age 列使用了表达式，索引失效
原因：MySQL 索引结构只存储字段原始值，如果在条件中使用函数或表达式，优化器无法直接定位到符合条件的索引项，必须进行全表扫描。
3. 对字符串使用不带通配符前缀的 LIKE 查询
在字符串类型的索引列上使用 LIKE 模糊匹配时，如果查询模式以通配符 % 开头，则 MySQL 无法利用索引。
示例
假设有如下索引：
sqlCopy codeCREATE INDEX idx_firstname ON employees(first_name);
查询示例：
- 索引有效：sqlCopy code
SELECT * FROM employees WHERE first_name LIKE 'Ali%'; -- 使用索引 - 索引失效：sqlCopy code
SELECT * FROM employees WHERE first_name LIKE '%ice'; -- 索引失效 SELECT * FROM employees WHERE first_name LIKE '%li%'; -- 索引失效
原因：MySQL 索引是按顺序存储的，当 LIKE 模式以 % 开头时，无法确定字符串的起始部分，从而无法使用索引加速查询。
4. 对索引列使用不等于（!= 或 <>）或 NOT IN
在使用不等于（!= 或 <>）或 NOT IN 运算符时，MySQL 可能会放弃使用索引，因为这些运算符会导致匹配结果不连续，无法通过索引快速筛选出符合条件的行。
示例
假设有如下索引：
sqlCopy codeCREATE INDEX idx_age ON employees(age);
查询示例：
- 索引有效：sqlCopy code
SELECT * FROM employees WHERE age = 30; -- 使用索引 SELECT * FROM employees WHERE age IN (25, 30, 35); -- 使用索引 - 索引失效：sqlCopy code
SELECT * FROM employees WHERE age != 30; -- 索引失效 SELECT * FROM employees WHERE age NOT IN (25, 30, 35); -- 索引失效
原因：不等于或 NOT IN 会导致 MySQL 需要检查大量不连续的值，索引扫描的效率低，因此放弃索引。
5. 在索引列上使用 IS NULL 或 IS NOT NULL
在某些版本的 MySQL 中（尤其是较旧的版本），对索引列进行 IS NULL 或 IS NOT NULL 判断可能会导致索引失效，特别是在复合索引的非最左列上。
示例
假设有如下索引：
sqlCopy codeCREATE INDEX idx_department_age ON employees(department, age);
查询示例：
- 索引有效：sqlCopy code
SELECT * FROM employees WHERE department = 'HR' AND age IS NULL; -- 使用索引 - 索引失效：sqlCopy code
SELECT * FROM employees WHERE age IS NULL; -- 索引失效
原因：在一些情况下，MySQL 对 IS NULL 或 IS NOT NULL 判断不支持索引优化，必须对每行进行扫描。
6. 范围条件后面的列索引失效
在复合索引中，当 WHERE 子句包含范围条件（如 >, <, BETWEEN）时，索引会在范围条件后中断，导致后续列无法使用索引。
示例
假设有如下索引：
sqlCopy codeCREATE INDEX idx_firstname_age_department ON employees(first_name, age, department);
查询示例：
- 索引有效：sqlCopy code
SELECT * FROM employees WHERE first_name = 'Alice' AND age = 30 AND department = 'HR'; -- 使用索引 - 索引失效：sqlCopy code
SELECT * FROM employees WHERE first_name = 'Alice' AND age > 30 AND department = 'HR'; -- department 列无法使用索引
原因：范围条件会导致索引扫描停止在该列，后续列无法再使用索引优化。
7. 数据分布不均，使用索引反而影响性能
如果某列的数据分布极不均匀，例如在布尔型或状态型字段中，大部分行的值相同（如 status 字段值 90% 都是 0），使用索引反而可能导致 MySQL 优化器放弃索引。
示例
假设有一个布尔型字段 status，其值在大多数行中都为 0，很少为 1。此时即使为 status 建立了索引，查询条件如下：
sqlCopy codeSELECT * FROM employees WHERE status = 0;
原因：如果索引中包含大量重复值，查询引擎使用索引可能需要扫描大量记录，不如直接进行全表扫描效率高。在这种情况下，优化器会选择全表扫描而非使用索引。
8. 隐式类型转换
当查询条件的数据类型与索引列的数据类型不一致时，MySQL 会进行隐式类型转换。此转换可能导致索引失效。
示例
假设 employees 表中 age 字段为 INT 类型：
sqlCopy codeCREATE INDEX idx_age ON employees(age);
查询示例：
- 索引有效：sqlCopy code
SELECT * FROM employees WHERE age = 30; -- 使用索引 - 索引失效：sqlCopy code
SELECT * FROM employees WHERE age = '30'; -- 字符串会隐式转换为整数，索引失效
原因：由于 age 是 INT 类型，而查询条件是字符串 '30'，MySQL 会进行隐式类型转换。这会导致 MySQL 不能直接使用索引，而是对每行数据进行转换后再比较，从而导致索引失效。
总结
MySQL 索引失效的原因主要包括：不符合最左前缀原则、对索引列使用函数或表达式、LIKE 模糊查询、使用不等于、范围条件、隐式类型转换等情况。理解这些场景有助于合理编写 SQL 查询和设计索引结构，以充分发挥 MySQL 索引的性能。