MYSQL-索引-索引常见失效场景

MYSQL-索引-索引常见失效场景

索引常见失效情况

1、查询条件不当

创建测试用表

-- 创建员工表
CREATE TABLE employees (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(50) NOT NULL,
	age INT,
	department VARCHAR(50),
	salary DECIMAL(10,2),
	hire_date DATE,
	email VARCHAR(100),
	INDEX idx_name (name),
	INDEX idx_age (age),
	INDEX idx_department_salary (department, salary),
	INDEX idx_hire_date (hire_date)
);

-- 插入测试数据
INSERT INTO employees (name, age, department, salary, hire_date, email)
VALUES 
	('张三', 28, '技术部', 8500.00, '2020-05-15', 'zhangsan@example.com'),
	('李四', 35, '市场部', 9200.00, '2018-11-22', 'lisi@example.com'),
	('王五', 42, '技术部', 10500.00, '2016-03-10', 'wangwu@example.com'),
	('赵六', 30, '人事部', 7800.00, '2019-07-05', 'zhaoliu@example.com'),
	('钱七', 45, '市场部', 11500.00, '2015-09-18', 'qianqi@example.com'),
	('孙八', 33, '技术部', 9500.00, '2017-12-01', 'sunba@example.com'),
	('周九', 29, '财务部', 8200.00, '2021-02-14', 'zhoujiu@example.com'),
	('吴十', 38, '技术部', 9800.00, '2018-08-27', 'wushi@example.com'),
	('郑十一', 31, '市场部', 8900.00, '2019-04-03', 'zhengshiyi@example.com'),
	('王十二', 40, '人事部', 10200.00, '2017-06-19', 'wangshier@example.com');

---

案例1：使用不等于操作符：如 !=、<>、NOT IN、NOT LIKE

EXPLAIN SELECT * FROM employees WHERE name != '张三';

分析结果:

可能显示 type: ALL (全表扫描 全表扫描是指数据库在执行查询时，逐行检查表中的所有记录，而不是使用索引快速定位数据。在EXPLAIN结果中，当type列为ALL时，表示查询使用了全表扫描。)

原因: != 或 <> 操作通常无法使用索引

案例2：对索引列使用函数或运算：如 WHERE YEAR(create_time) = 2023 或 WHERE price*2 > 100

EXPLAIN SELECT * FROM employees WHERE YEAR(hire_date) = 2020;

---

分析结果:

可能显示 type: ALL

原因: 对索引列使用函数会导致索引失效

案例3：不符合最左前缀原则（最左前缀原则（Leftmost Prefix Principle）是MySQL复合索引（组合索引）使用的核心规则，决定了复合索引能否被有效利用。复合索引按照从左到右的顺序使用，查询条件必须从索引的最左列开始，并且不能跳过中间的列。）

EXPLAIN SELECT * FROM employees WHERE salary > 9000;

---

分析结果:

可能显示 type: ALL

原因: 复合索引 idx_department_salary 是 (department, salary)，单独使用 salary 时不满足最左前缀原则

案例4：使用 LIKE 以通配符开头

EXPLAIN SELECT * FROM employees WHERE name LIKE '%三';

---

分析结果:

可能显示 type: ALL

原因: LIKE 以通配符开头时无法使用索引

案例5：隐式类型转换

EXPLAIN SELECT * FROM employees WHERE name = 123;

---

分析结果:

可能显示 type: ALL

原因: 字符串列与数字比较导致隐式类型转换，索引失效

案例6：使用 OR 条件

EXPLAIN SELECT * FROM employees WHERE name = '张三' OR email = '30';

---

分析结果:

可能显示 type: ALL

原因: name 列有索引 (idx_name),email 列没有索引,MySQL 无法对无索引列使用索引合并优化

案例7：IS NULL/IS NOT NULL

EXPLAIN SELECT * FROM employees WHERE name IS NULL;

---

可能显示 type: NULL

原因:当 MySQL 优化器能在解析阶段就确定查询不需要真正执行时,特别是当条件永远不可能满足时。如果 name 列被定义为 NOT NULL（没有 NULL 值），查询 WHERE name IS NULL 永远返回空结果集，优化器会跳过执行计划生成

---

EXPLAIN SELECT * FROM employees WHERE name IS NOT NULL;

---