面试题：MySQL中为何要避免多个范围条件在索引中的使用

Question

在MySQL索引优化中，常提到要避免多个范围条件的情况。请阐述在什么场景下会出现多个范围条件，以及为什么多个范围条件会对索引性能产生不良影响。

Answer 1

出现多个范围条件的场景

1. 多字段不同范围查询：例如在电商系统中，要查询价格在某个区间，同时销量也在某个区间的商品。SQL 语句可能类似 SELECT * FROM products WHERE price BETWEEN 100 AND 200 AND sales_count BETWEEN 1000 AND 2000; 这里 price 和 sales_count 两个字段都有范围查询条件。
2. 单字段不同范围组合：比如在日志表中，要查询时间既不在某个特定短时间段，又不在另一个特定短时间段的记录。SELECT * FROM logs WHERE log_time NOT BETWEEN '2023 - 01 - 01 08:00:00' AND '2023 - 01 - 01 09:00:00' AND log_time NOT BETWEEN '2023 - 01 - 01 14:00:00' AND '2023 - 01 - 01 15:00:00';

多个范围条件对索引性能产生不良影响的原因

1. 索引使用限制：MySQL 的索引使用规则通常是对于多列索引，只有当索引的最左前缀连续匹配时，索引才能有效使用。多个范围条件可能导致索引无法按照预期方式使用。例如对于联合索引 (price, sales_count)，当有 price BETWEEN 100 AND 200 AND sales_count BETWEEN 1000 AND 2000 这样的条件时，在 price 范围查询后，sales_count 的范围查询不能很好地利用索引，因为索引是有序的，范围查询后无法再精准定位 sales_count 的索引区间，只能进行部分索引扫描甚至全表扫描。
2. 索引合并效率低：MySQL 可以尝试使用索引合并策略来处理多个范围条件，但这种策略通常效率不高。索引合并需要在多个索引之间来回切换和扫描，增加了额外的 I/O 和 CPU 开销。例如对于上述电商商品查询，如果 price 和 sales_count 分别有单独的索引，MySQL 尝试索引合并时，需要多次从不同索引中获取数据并合并，这比使用一个高效的联合索引性能要差很多。