mysql - 为什么 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

实际上，聚簇索引(又名 gen_clust_index )的填充顺序除了 rowid 顺序外没有任何韵律或原因。实际上不可能按 ID 顺序排列 rowid。

在 InnoDB 中，非聚集索引(也称为二级索引)中的记录包含不在二级索引中的行的主键列。 InnoDB 使用这个主键值来搜索聚集索引中的行。

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

现在，让我们换个话题，讨论 PRIMARY KEY 和 k_name:

问题:谁的原始查询请求的列更多，主键还是 k_name？

ANSWER :k_name，因为它同时包含名称和 id(id 是内部的，因为它是 PRIMARY KEY)。覆盖索引 k_name 比主键更好地完成查询。

现在，如果查询是 SELECT * FROM ORDER BY id，您的 EXPLAIN PLAN 应该如下所示:

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 IS NOT A SECONDARY INDEX 因为它和 PRIMARY KEY 一样都是候选键。
• 用户定义的聚簇索引一旦建立就不能改变行顺序

您根本无法操纵行的顺序。这是证明:

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)