不是所有超过 500 万人口的国家都在亚洲，所以人口和大洲这两个条件的组合能让索引减少工作量。即在这些数据中，复合索引能进一步改善选择性。
这里的复合索引有两个选择：
- 索引 p_c（人口，大洲）
- 索引 c_p（大洲，人口）
复合索引的顺序有显著影响。事实上，人口条件是范围查询的，优化器只能用到索引（人口，大洲）的第一部分，因此不会在只用人口索引上带来提升。我们可以通过强制使用这个复合索引清晰地看到这一点。
例子10：复合索引（人口，大洲）是个不好的选择
ALTER TABLE Country ADD INDEX p_c (Population, Continent);
EXPLAIN FORMAT=JSON
SELECT * FROM Country FORCE INDEX (p_c) WHERE continent='Asia' and population
{
"query_block": {
"select_id": 1,
"cost_info": { # 强制使用复合索引
"query_cost": "152.21" # 代价比全表扫描还高
},
"table": {
"table_name": "Country",
"access_type": "range",
"possible_keys": [
"p_c"
],
"key": "p_c",
"used_key_parts": [ # 只用到了人口这一列
"Population"
],
"key_length": "4", # 人口列的类型长度是 4 字节
"rows_examined_per_scan": 108,
"rows_produced_per_join": 15,
"filtered": "14.29",
"index_condition": "((`world`.`Country`.`Continent` = 'Asia') and (`world`.`Country`.`Population` > 5000000))",
"cost_info": {
"read_cost": "149.12",
"eval_cost": "3.09",
"prefix_cost": "152.21",
"data_read_per_join": "3K"
},
"used_columns": [
...
]
}
}
}
这种限制源于 B+树 的结构。对于复合索引的设计，一个速记方法是“范围在右”。考虑这点后，例子11 演示了（大洲，人口）索引，这样的两列组合比只用大洲索引有更好的选择性，查询代价进一步下降。因为两列被紧密地组合，访问方式是范围的（range）。
例子11：更合适的复合索引（大洲，人口）
ALTER TABLE Country ADD INDEX c_p (Continent, Population);
EXPLAIN FORMAT=JSON
SELECT * FROM Country WHERE continent='Asia' and population > 5000000;
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "24.83" # 查询代价比（人口，大洲）小得多
},
"table": {
"table_name": "Country",
"access_type": "range",
"possible_keys": [
"p",
"c",
"p_c",
"c_p"
],
"key": "c_p",
"used_key_parts": [ # 两个列都用上了
"Continent", # 大洲 1字节 的枚举
"Population" # 人口 4字节 的整型
],
"key_length": "5", # 等于 5字节
"rows_examined_per_scan": 32,
"rows_produced_per_join": 15,
"filtered": "100.00",
"index_condition": "((`world`.`Country`.`Continent` = 'Asia') and (`world`.`Country`.`Population` > 5000000))",
"cost_info": {
"read_cost": "18.00",
"eval_cost": "3.09",
"prefix_cost": "24.83",
"data_read_per_join": "3K"
},
"used_columns": [
...
]
}
}
}
考虑复合索引的顺序
决定正确的复合索引顺序是很有技巧的。以下是几个考虑：
-
用得最多放左边。索引（
名字，姓）可以用来满足查询需要名字索引的查询，但不能满足需要姓索引的查询。设计复合索引要尽可能满足更多的查询。 -
范围放右边。索引（
年龄，名字）在查询年龄范围和名字时，无法使用到两列。更确切地说，复合索引首次用到范围查询后，后面的列就无法应用了。 - 最具选择性的放左边。这能让索引最快地减少工作量。通常这能够改善内存使用，因为需要访问的页更少了。
-
谨慎改变索引的排序。混合用
ASC、DESC可能影响复合索引的可用程度。
译自：
Composite Indexes - The Unofficial MySQL 8.0 Optimizer Guide
