在mysql的查询场景中，WHERE子句是最常用的过滤条件载体，当数据量达到一定规模时，没有合理索引支撑的WHERE查询会触发全表扫描，导致查询耗时急剧上升。合理的索引设计能让WHERE子句的查询直接定位到目标数据行，大幅降低IO消耗。
mysql索引的基本类型
mysql中常用的索引类型包括普通索引、唯一索引、主键索引、联合索引和全文索引，其中优化WHERE子句查询最常用的是普通索引和联合索引。
- 普通索引：最基本的索引类型，没有任何约束，仅用于加速查询
- 联合索引：基于多个列创建的索引，遵循最左前缀匹配原则
- 主键索引：特殊的唯一索引，不允许空值，每张表只能有一个
WHERE子句与索引的匹配规则
等值查询场景
当WHERE子句使用等值条件查询时，对应列的普通索引会直接生效。比如我们有一张用户表，在user_id列上创建了普通索引：
-- 创建用户表
CREATE TABLE user_info (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id VARCHAR(32) NOT NULL,
user_name VARCHAR(50) NOT NULL,
age INT NOT NULL,
create_time DATETIME NOT NULL,
INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 等值查询，会命中idx_user_id索引
EXPLAIN SELECT * FROM user_info WHERE user_id = '10001';
通过EXPLAIN分析可以看到，查询的type为ref，说明索引生效，查询效率很高。
范围查询场景
当WHERE子句使用范围条件（如>、<、BETWEEN、LIKE '前缀%'）时，索引依然可以生效，但范围条件后的列无法使用联合索引的后续部分。
-- 创建联合索引，包含user_name和age两列 CREATE INDEX idx_name_age ON user_info (user_name, age); -- 范围查询，命中联合索引的user_name部分 EXPLAIN SELECT * FROM user_info WHERE user_name LIKE '张%' AND age = 20;
上述查询中，user_name的范围查询会让age列的索引失效，查询只会用到联合索引的user_name部分。
多条件查询场景
多条件查询时，联合索引的最左前缀原则生效，即查询条件必须包含联合索引的最左列，否则索引无法生效。
-- 联合索引是(user_name, age)，以下条件无法命中索引 EXPLAIN SELECT * FROM user_info WHERE age = 20; -- 以下条件可以命中联合索引 EXPLAIN SELECT * FROM user_info WHERE user_name = '张三' AND age = 20;
导致WHERE子句索引失效的常见情况
即使创建了合适的索引，部分写法也会导致WHERE子句的查询无法使用索引，常见情况如下：
- 对索引列进行函数操作，比如
WHERE DATE(create_time) = '2024-01-01' - 索引列参与运算，比如
WHERE age + 1 = 21 - 使用
LIKE '%后缀'的模糊查询，前缀通配符会让索引失效 - 查询条件中使用
OR，且OR两侧的列不是都有索引 - 字符串类型的索引列查询时未加引号，导致隐式类型转换
比如以下查询会导致idx_user_id索引失效：
-- user_id是VARCHAR类型，查询时未加引号，发生隐式类型转换，索引失效 EXPLAIN SELECT * FROM user_info WHERE user_id = 10001;
索引优化WHERE子句的实践建议
结合上述规则，优化WHERE子句查询时可以参考以下建议：
- 优先为WHERE子句中高频出现的过滤列创建索引，避免全表扫描
- 多条件查询时优先创建联合索引，遵循最左前缀原则排列索引列顺序
- 避免在索引列上做函数操作、运算或类型转换，保持列原本的形态
- 模糊查询尽量使用前缀匹配，避免
%内容%或%内容的写法 - 定期通过EXPLAIN分析慢查询，调整不合理的索引设计
通过合理的索引设计，大部分WHERE子句的查询性能都能得到显著提升，同时也要注意不要过度创建索引，因为索引会增加写操作的开销，需要在查询性能和写性能之间做好平衡。