为什么MySQL查询优化器选择辅助索引而不是聚集主索引？

Question

为什么Mysql优化器在执行不带order by子句的“select* from lookup”时选择二级索引。

这只是一个偶然的机会，还是这是幕后的优化，因为您添加了一个辅助索引，所以它比主键更重要。

我希望结果按主键排序，因为扫描所有叶节点可以提供回答此查询所需的所有数据。

为了重现，我创建了一个简单的键/值对表(注意，不是auto_increment)

create table lookup (
id int not null,
primary key (id),
name varchar(25),
unique k_name (name)
) engine=innodb;

按随机的非字母顺序插入一些数据

insert into lookup values(1, "Zebra"),(2, "Aardvark"),(3, "Fish"),(4,"Dog"),(5,"Cat"),(6,"Mouse");

查询数据(这是我期望数据按照主键的顺序返回的位置)

mysql> select * from lookup;
+----+----------+
| id | name     |
+----+----------+
|  2 | Aardvark |
|  5 | Cat      |
|  4 | Dog      |
|  3 | Fish     |
|  6 | Mouse    |
|  1 | Zebra    |
+----+----------+
6 rows in set (0.00 sec)

如果不是这样-似乎已经完成了对k_name叶节点的扫描。此处显示

mysql> explain select * from lookup;
+----+-------------+--------+-------+---------------+--------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key    | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+--------+---------+------+------+-------------+
|  1 | SIMPLE      | lookup | index | NULL          | k_name | 28      | NULL |    6 | Using index |
+----+-------------+--------+-------+---------------+--------+---------+------+------+-------------+
1 row in set (0.00 sec)

对我来说，这说明Mysql使用k_name作为覆盖索引来返回数据。如果我删除k_name索引，那么数据将按主键顺序返回。如果我添加另一个未索引的列，数据将按主键顺序返回。

关于我的设置的一些基本信息。

mysql> show table status like 'lookup'\G
*************************** 1. row ***************************
           Name: lookup
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 6
 Avg_row_length: 2730
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2011-11-15 10:42:35
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

 mysql> select version();
 +------------+
 | version()  |
 +------------+
 | 5.5.15-log |
 +------------+
 1 row in set (0.00 sec)

Answer 1

实际上，聚集索引(也称为)的填充顺序除了行In顺序外，没有任何其他原因。按id顺序排序rowids几乎是不可能的。

在InnoDB中，非聚集索引(也称为辅助索引)中的记录包含不在辅助索引中的行的主键列。InnoDB使用此主键值在聚集索引中搜索行。

二级索引控制顺序。但是，每个辅助索引条目都有一个指向正确行的主键条目。此外，请考虑您为k_name提到的覆盖索引场景。

现在，让我们转换一下话题，讨论一下主键和k_name：

query ：谁的原始查询、主键或k_name请求的列更多？

回答：k_name，因为其中既有名称又有id (id是内部的，因为它是主键)。覆盖索引k_name比主键更好地满足查询。

现在，如果查询是SELECT * FROM ORDER BY id，您的解释计划应该如下所示：

mysql> explain select * from lookup order by id;
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | lookup | index | NULL          | PRIMARY | 4       | NULL |    6 |       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------+

1 row in set (0.00 sec)

在不指定顺序的情况下，MySQL查询优化器会挑选最能满足查询的索引。当然，k_name拥有不公平的优势，因为

• 表中的每一列都是单独索引的
• 表中的每一列都是Candidate Key
• k_name不是辅助索引

，因为它是候选键，就像主索引聚集索引一样，一旦建立了

• ，就不能更改行顺序

您根本不能操纵行的顺序。以下是这方面的证据：

mysql> alter table lookup order by name;
Query OK, 6 rows affected, 1 warning (0.23 sec)
Records: 6  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------+
| Level   | Code | Message                                                                           |
+---------+------+-----------------------------------------------------------------------------------+
| Warning | 1105 | ORDER BY ignored as there is a user-defined clustered index in the table 'lookup' |
+---------+------+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table lookup order by id;
Query OK, 6 rows affected, 1 warning (0.19 sec)
Records: 6  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------+
| Level   | Code | Message                                                                           |
+---------+------+-----------------------------------------------------------------------------------+
| Warning | 1105 | ORDER BY ignored as there is a user-defined clustered index in the table 'lookup' |
+---------+------+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Answer 2

在获取该查询的数据方面，这两种索引的效率都是一样的，所以我猜优化器只是放弃了，说了一句“这就行了”。

添加另一个唯一的索引，这可能是因为它们都是同样有效的，一些"FindBestIndex“例程与它读取的最后一个例程一起丢失。

这也不是我期望的行为，但如果我关心顺序，我会添加一个按id排序，然后让优化器选择主键，而不是两次遍历并进行排序。

Answer 3

这是因为InnoDB辅助索引还包括主键列。因此，MySQL能够直接从二级索引获取所有相关数据，而无需接触数据行，从而节省了磁盘IO。

参考文献：

• Covering Index
• Clustered and Secondary Indexes
• Secondary Index
• Clustered Index (aka Index-Organized Table)