mysql单索引和联合索引_MySQL单列索引和联合索引

2022-09-02

本文通过一个案例，介绍优化器对单列索引和联合索引的选择。
order表的ord_seq字段上有2个索引，单列索引(
【mysql单索引和联合索引_MySQL单列索引和联合索引】order_seq)和联合索引(order_seq,order_type)
MySQL > explain select * from `order` where order_seq = 1502131212577;
+----+-------------+-------+------------+------+-------------------------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys| key| key_len | ref| rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------------------+------------+---------+-------+------+----------+-------+
|1 | SIMPLE| order | NULL| ref| idx_ordseq,idx_ordseq_ordtype | idx_ordseq | 8| const |1 |100.00 | NULL|
+----+-------------+-------+------------+------+-------------------------------+------------+---------+-------+------+----------+-------+
可以看到优化器选择了单列索引idx_ordseq(
order_seq)，而不是联合索引。因为该索引的叶子节点包含单个键值，所以理论上一个页可以存放更多的记录。
如果换种场景：
MySQL > explain select * from `order` where order_seq = 1502131212577 order by order_type desc;
+----+-------------+-------+------------+------+-------------------------------+--------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys| key| key_len | ref| rows | filtered | Extra|
+----+-------------+-------+------------+------+-------------------------------+--------------------+---------+-------+------+----------+-------------+
|1 | SIMPLE| order | NULL| ref| idx_ordseq,idx_ordseq_ordtype | idx_ordseq_ordtype | 8| const |1 |100.00 | Using where |
+----+-------------+-------+------------+------+-------------------------------+--------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
where条件字段和排序字段添加联合索引，解决了filesort的问题。

推荐阅读

上一篇：mysql单列索引和联合索引

下一篇：{调取该文章的TAG关键词}|讲透数据创新、对接真实需求，2022全球数字价值峰会深圳站报名正式开启