x 个等值条件字段 1 个范围条件字段 or y 个排序字段 z 个其他需要获取的字段。(x >= 0, y >= 0, z >= 0)
例子：
代码语言：javascript复制SELECT a, b, c, d, e FROM table WHERE a = 1 AND b = 2 ORDER BY c DESC, d ASC;最佳索引为：
代码语言：javascript复制( a, b, c DESC, d ASC, e )
等值条件字段 | 排序条件字段 | 其他需要获取的字段等值条件字段
等值条件字段是指 WHERE 中使用 = 的字段。比如 WHERE class = 2 中的 class 就是等值条件字段。
范围条件字段
范围条件字段是指 WHERE 中使用 >、<、>=、<= 等操作符的字段。比如 WHERE score > 90 中的 score 就是范围条件字段。
范围条件对于查询效率的影响非常大，所以应该尽量减少范围条件的使用。在最佳多列索引公式中，最多有一个范围条件字段，且不能和排序字段并存。如果有排序需求，应优先考虑排序，想办法规避范围条件筛选。下面通过一个例子来说明为什么范围条件字段不能和排序字段并存，以及如何规避范围条件筛选。
假设有一个表 films，包含了电影的信息，其中包含了 name、release_date、rating、country 等字段。现在要查询评分大于 8.0 的中国电影并按上映日期排序，SQL 语句如下：
SELECT * FROM films WHERE rating > 8.0 AND country = 'China' ORDER BY release_date DESC;当索引为 (country, rating, release_date) 时：
index on (country, rating, release_date)
|
v ------------->
-------------- --------- --------- --------- ------- ------- ------- ------- ------- ------- -------
| country || America | America | America | China | China | China | China | Japan | Japan | Japan |
-------------- --------- --------- --------- ------- ------- ------- ------- ------- ------- -------
| rating || 6.0 | 8.5 | 9.0 | 7.0 | 8.5 | 9.0 | 9.5 | 8.0 | 8.5 | 9.0 |
-------------- --------- --------- --------- ------- ------- ------- ------- ------- ------- -------
| release_date || 2024 | 2021 | 2023 | 2023 | 2015 | 2022 | 2016 | 2023 | 2013 | 2015 |
-------------- --------- --------- --------- ------- ------- ------- ------- ------- ------- -------
✅ ✅ ✅看起来这个索引很完美，但实际上通过索引查找到的结果并不是按照 release_date 排序的，也就是说索引中的 release_date 是无效的。数据库还需要将所有符合条件的行按照 release_date 进行排序，如果数据量很大，这个排序操作会非常耗时。如果我们仅仅需要前几条数据，那么这个排序操作就是浪费的。这就是为什么范围条件字段不能和排序字段并存，且应优先考虑排序的原因。
这种场景的优化方法是将范围条件转换为等值条件。这需要根据实际需求来做优化。比如我们例子中的需求是查询评分大于 8.0 的电影，我们可以将评分大于 8.0 的电影定义为高分电影。增加一个字段 is_high_rating，当评分大于 8.0 时，is_high_rating 为 1，否则为 0。然后将索引改为 (country, is_high_rating, release_date)。
SELECT * FROM films WHERE is_high_rating = 1 AND country = 'China' ORDER BY release_date DESC;index on (country, is_high_rating, release_date)
|
<-------------- v
---------------- --------- --------- --------- ------- ------- ------- ------- ------- ------- -------
| country || America | America | America | China | China | China | China | Japan | Japan | Japan |
---------------- --------- --------- --------- ------- ------- ------- ------- ------- ------- -------
| is_high_rating || 0 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | 1 |
---------------- --------- --------- --------- ------- ------- ------- ------- ------- ------- -------
| release_date || 2024 | 2021 | 2023 | 2023 | 2015 | 2016 | 2022 | 2013 | 2015 | 2023 |
---------------- --------- --------- --------- ------- ------- ------- ------- ------- ------- -------
✅ ✅ ✅如果数据库支持，也可以使用函数索引 (country, IF(rating > 8, 1, 0), release_date)，或者使用虚拟列来实现。
注意：
!=、<>、IS NOT NULL等不等操作符也是范围条件。如有需要，也可以通过转换为等值条件来优化。
排序字段
排序字段是指 ORDER BY 中的字段。比如 ORDER BY release_date DESC 中的 release_date 就是排序字段。
排序字段的顺序应该和 ORDER BY 中的顺序一致，且升降序也应该一致或完全相反。例如 ORDER BY release_date DESC, rating ASC 对应的索引应该是 (release_date DESC, rating ASC) 或 (release_date ASC, rating DESC)。之所以完全相反是因为数据库可以倒序遍历索引。例如我们要将查询按照 release_date 升序、rating 降序排序的前五个结果：
SELECT * FROM films ORDER BY release_date DESC, rating ASC LIMIT 5;当索引为 (release_date DESC, rating ASC) 时：
index on (release_date DESC, rating ASC)
|
v -------------------------------->
-------------- --------- ------- ------- --------- ------- --------- ------- ------- ------- -------
| release_date || 2024 | 2023 | 2023 | 2023 | 2022 | 2021 | 2016 | 2015 | 2015 | 2013 |
-------------- --------- ------- ------- --------- ------- --------- ------- ------- ------- -------
| rating || 6.0 | 7.0 | 8.0 | 9.0 | 9.0 | 8.5 | 9.5 | 8.5 | 9.0 | 8.5 |
-------------- --------- ------- ------- --------- ------- --------- ------- ------- ------- -------
✅ ✅ ✅ ✅ ✅当索引为 (release_date ASC, rating DESC) 时：
index on (release_date ASC, rating DESC)
|
<------------------------------- v
-------------- --------- ------- ------- --------- ------- --------- ------- ------- ------- -------
| release_date || 2013 | 2015 | 2015 | 2016 | 2021 | 2022 | 2023 | 2023 | 2023 | 2024 |
-------------- --------- ------- ------- --------- ------- --------- ------- ------- ------- -------
| rating || 8.5 | 9.0 | 8.5 | 9.5 | 8.5 | 9.0 | 9.0 | 8.0 | 7.0 | 6.0 |
-------------- --------- ------- ------- --------- ------- --------- ------- ------- ------- -------
✅ ✅ ✅ ✅ ✅但是不能使用 (release_date DESC, rating DESC) 索引，因为这并不符合需求中的排序。
index on (release_date DESC, rating DESC)
|
v -------------------------------->
-------------- --------- ------- ------- --------- ------- --------- ------- ------- ------- -------
| release_date || 2024 | 2023 | 2023 | 2023 | 2022 | 2021 | 2016 | 2015 | 2015 | 2013 |
-------------- --------- ------- ------- --------- ------- --------- ------- ------- ------- -------
| rating || 6.0 | 9.0 | 8.0 | 7.0 | 9.0 | 8.5 | 9.5 | 9.0 | 8.5 | 8.5 |
-------------- --------- ------- ------- --------- ------- --------- ------- ------- ------- -------
✅ ✅ ✅ ✅ ✅可以看出数据库使用 (release_date DESC, rating DESC) 索引获得的结果并不符合 ORDER BY release_date DESC, rating ASC 的排序要求，还需要进行额外的排序操作。
其他需要获取的字段（索引覆盖）
其他需要获取的字段指的是需要被 SELECT 且还不在索引中的字段。如果索引中包含了所有需要获取的字段，那么数据库可以直接从索引中获取数据，而不需要再去表中查询数据。这样可以减少 I/O 操作，提高查询效率。但是如果索引中包含了太多字段，会导致索引变得过大，从而影响到插入、更新、删除等操作的性能，也会增加不必要的内存占用。所以并不是直接把所有字段都放到索引中就是最佳的，需要根据实际情况来做权衡。
