在MySQL的查询场景中，等值查询和范围查询是最常用的两种查询类型，合理的索引设计能大幅提升这两类查询的执行效率，而不合理的索引安排则可能导致索引无法被使用，甚至引发全表扫描。
索引设计的核心原则
MySQL的B+树索引结构决定了联合索引的字段顺序会直接影响查询是否能命中索引。对于同时包含等值查询和范围查询的场景，需要遵循等值条件字段在前，范围条件字段在后的原则，这是因为联合索引会先按照最左字段排序，等值条件能快速定位到对应的索引区间，后续的范围查询可以在这个区间内继续筛选，避免扫描多余的索引节点。
等值查询的索引设计
等值查询是指查询条件中使用等于号判断字段值的场景，比如查询用户ID为1001的用户信息，查询订单状态为已支付的订单列表等。这类查询的索引设计相对简单，优先为查询条件中的字段创建索引即可，如果是多字段的等值查询，创建包含所有等值字段的联合索引效果最好。
假设我们有一个用户表，结构如下：
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `age` int(11) DEFAULT NULL, `city` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
如果经常执行以下等值查询：
SELECT * FROM `user` WHERE `name` = '张三' AND `city` = '北京';
那么可以创建联合索引idx_name_city，索引字段顺序为name在前，city在后，这样查询可以完全命中索引，不需要回表查询数据（如果查询的字段都在索引中，还可以实现索引覆盖）。
范围查询的索引设计
范围查询是指查询条件中使用大于、小于、大于等于、小于等于、BETWEEN、IN等符号判断字段范围的场景，比如查询年龄大于18岁的用户，查询创建时间在某个时间段内的订单等。范围查询的索引设计需要注意，范围条件之后的字段无法使用联合索引的有序性。
还是以上面的用户表为例，如果经常执行以下查询：
SELECT * FROM `user` WHERE `age` > 18 AND `city` = '北京';
如果创建联合索引idx_age_city，那么查询时只能用到age字段的索引部分，city字段的索引无法被使用，因为age是范围查询，后续的city字段在索引中不是有序排列的。正确的索引设计应该是将等值条件字段放在前面，范围条件字段放在后面，也就是创建idx_city_age索引，这样查询会先通过city = '北京'定位到索引区间，再在这个区间内筛选age > 18的记录，能充分利用索引的有序性。
常见场景的索引设计示例
场景一：等值+范围混合查询
假设订单表结构如下，经常需要查询用户ID为1001，且订单金额大于100的订单：
CREATE TABLE `order` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) DEFAULT NULL, `amount` decimal(10,2) DEFAULT NULL, `create_time` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
对应的查询语句为：
SELECT * FROM `order` WHERE `user_id` = 1001 AND `amount` > 100;
此时应该创建联合索引idx_user_id_amount，user_id是等值条件放在前，amount是范围条件放在后，查询可以高效命中索引。
场景二：多范围查询的索引设计
如果查询中包含多个范围条件，比如查询订单金额大于100且小于500，同时创建时间在2024年1月1日之后的订单：
SELECT * FROM `order` WHERE `amount` > 100 AND `amount` < 500 AND `create_time` > '2024-01-01 00:00:00';
这种情况下，联合索引只能用到最左的范围字段，建议选择区分度更高的范围字段放在索引最左位置，比如如果amount的区分度更高，就创建idx_amount_create_time索引，查询时先通过amount的范围定位区间，再筛选create_time的条件，尽量减少扫描的索引范围。
需要注意的索引失效问题
- 不要在索引字段上使用函数或者运算，比如
WHERE YEAR(create_time) = 2024，会导致索引失效，应该改成WHERE create_time >= '2024-01-01 00:00:00' AND create_time < '2025-01-01 00:00:00' - 范围查询之后的联合索引字段无法使用索引，所以联合索引中范围字段尽量放在最后
- 模糊查询中，以通配符开头的
LIKE '%xxx'无法使用索引，LIKE 'xxx%'可以使用索引 - 查询条件中使用不等于、NOT IN、IS NULL、IS NOT NULL时，可能导致索引失效，需要根据实际数据分布判断
索引设计验证方法
设计好索引之后，可以使用EXPLAIN命令查看查询的执行计划，判断索引是否被正确使用。重点关注type字段，如果是ref或者range说明索引被正常使用，如果是ALL说明是全表扫描，需要优化索引。同时关注key字段，确认是否使用了预期的索引，rows字段表示预计扫描的行数，数值越小说明索引效果越好。
EXPLAIN SELECT * FROM `order` WHERE `user_id` = 1001 AND `amount` > 100;
通过执行上面的语句，可以查看当前查询的索引使用情况，根据结果调整索引设计，直到查询性能达到预期。