在二级索引里，对于相同的索引键，附带的主键是有序的还是无序呢

柔情只为你懂 2022-02-20 03:51 195阅读 0赞

在设计索引的时候，有个问题卡住了：  
在二级索引里，索引块里除了索引键，后面还附带主键。而且我们都知道，等值查询可以利用索引上的索引键和附带的主键而避免回表。但是对于多个相同的索引键，在索引块里，后面附带的主键是有序的还是无序呢？

例如有如下一个表：

CREATE TABLE `test` (
      `id` int(11) NOT NULL,
      `col1` varchar(10) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_col1` (`col1`)
    ) ENGINE=InnoDB

test表以id为主键，col1字段上有二级索引，对于数据(1,‘a’),(2,‘a’)，在索引idx\_col1里的顺序是(‘a’,1)->(‘a’,2)还是无序呢？

以下做一个简单的证明：  
假设已有数据

mysql> select * from test;
    +----+------+
    | id | col1 |
    +----+------+
    |  1 | a    |
    |  2 | a    |
    |  4 | a    |
    +----+------+
    3 rows in set (0.00 sec)

通过idx\_col1全索引扫描查看数据的顺序，查出来的结果集顺序即是索引块里的排序顺序：

mysql> desc select col1,id from test force index(idx_col1);
    +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
    | id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | test  | NULL       | index | NULL          | idx_col1 | 43      | NULL |    3 |   100.00 | Using index |
    +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> select col1,id from test force index(idx_col1);
    +------+----+
    | col1 | id |
    +------+----+
    | a    |  1 |
    | a    |  2 |
    | a    |  4 |
    +------+----+
    3 rows in set (0.01 sec)

可以看到当前idx\_col1索引里的数据，对于相同的a，附带的主键id是按照主键值升序排序。  
那么我们插入一条数据(3,‘a’)，再通过idx\_col1全索引扫描查看数据的顺序，  
`select col1,id from test force index(idx_col1);`  
将可能有两种可能的输出结果：  
1.如果按照主键排序，那么(3,‘a’)将会在idx\_col1索引里插入到(4,‘a’)的前面；  
2.如果是无序的，那么(3,‘a’)将会在idx\_col1索引里直接插入到(4,‘a’)的后面。  
来看一下结果：

mysql> insert into test values (3,'a');
    Query OK, 1 row affected (0.01 sec)
    
    mysql> desc select col1,id from test force index(idx_col1);
    +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
    | id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | test  | NULL       | index | NULL          | idx_col1 | 43      | NULL |    4 |   100.00 | Using index |
    +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> select col1,id from test force index(idx_col1);
    +------+----+
    | col1 | id |
    +------+----+
    | a    |  1 |
    | a    |  2 |
    | a    |  3 |
    | a    |  4 |
    +------+----+
    4 rows in set (0.00 sec)

可以看到(3,‘a’)将会在idx\_col1索引里插入到(4,‘a’)的前面，所以我们得到结论：  
在二级索引里，对于相同的索引键值，索引行将会按照附带的主键值顺序存储。

也就是说，以下的查询可以利用二级索引消除排序和避免回表：

想到这个问题是因为，平常大多数情况都是在等值查询的情况下用到二级索引附带的主键值，但并不太确定二级索引附带的主键在二级索引里是否有序的，因为即使无需，也是可以用于等值查询的。  
也许有人会说，能不能利用附带主键消除排序，建一个索引试试不就行了。那么，对于一个几千万的大表，也是建一个索引试试？