为什么MySQL查询优化器会选择聚集主索引上的二级索引？

Why does Mysql optimizer choose the secondary index when doing a 'select * from lookup' with no order by clause.

为什么Mysql优化器在执行'select * from lookup'而没有order by子句时选择二级索引。

Is it just a fluke or is this a behind the scenes optimization that assumes since you added a secondary index its more important than the primary key.

它只是一个侥幸，或者这是一个幕后优化，假设你添加了一个二级索引，它比主键更重要。

I would expect the results to be ordered by primary key as a scan of all the leaf nodes can provide all the data necessary to answer this query.

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

To reproduce I create a simple key/value pair table (note not auto_increment)

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

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

Insert some data in random non-alphabetical order

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

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

Query the data (this is where I would expect the data to be returned in order of primary key)

查询数据（这是我希望以主键的顺序返回数据的地方）

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)

Where as it is not - it appears that a scan of the k_name leaf nodes has been done. Shown here

但事实并非如此 - 似乎已经完成了对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)

To me this says Mysql is using k_name as a covering index to return the data. If I drop the k_name index then data is returned in primary key order. If I add another un-indexed column data is returned in primary key order.

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

Some basic information about my setup.

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

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)

4 个解决方案

#1

In reality, the clustered index (aka gen_clust_index) is populated in an order that has no rhyme or reason other than in rowid order. it is virtually impossible to order the rowids in id order.

实际上，聚集索引（aka gen_clust_index）的填充顺序除了以rowid顺序之外没有押韵或原因。几乎不可能按id顺序订购rowid。

In InnoDB, the records in nonclustered indexes (also called secondary indexes) contain the primary key columns for the row that are not in the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

在InnoDB中，非聚簇索引（也称为二级索引）中的记录包含不在二级索引中的行的主键列。 InnoDB使用此主键值来搜索聚簇索引中的行。

The secondary index governs order. However, each secondary index entry has a primary key entry to the correct row. Also, think of the covering index scenario you mentioned for k_name.

二级索引管理订单。但是，每个辅助索引条目都有一个指向正确行的主键。另外，请考虑您为k_name提到的覆盖索引方案。

Now, let's switch gears for a moment and discusss the PRIMARY KEY and k_name:

现在，让我们暂时切换一下，讨论PRIMARY KEY和k_name：

QUESTION : Whose has more columns requested by your original query, the Primary Key or k_name ?

问题：原始查询，主键或k_name请求的列数更多？

ANSWER : k_name, because it has both name and id in it (id being internal because it is the PRIMARY KEY). The covering index k_name fulfills the query better than the primary key.

答案：k_name，因为它同时包含name和id（id是内部的，因为它是PRIMARY KEY）。覆盖索引k_name比主键更好地满足查询。

Now if the query was SELECT * FROM ORDER BY id, your EXPLAIN PLAN should look like this:

现在，如果查询是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)

Without specfiying order, the MySQL Query Optimizer picks the index that best fulfills your query. Of course, k_name has the unfair advantage because

如果没有特定的顺序，MySQL Query Optimizer会选择最能满足您查询的索引。当然，k_name具有不公平的优势，因为

every column in the table is individually indexed
表中的每一列都是单独索引的
every column in the table is a Candidate Key
表中的每一列都是候选键
k_name IS NOT A SECONDARY INDEX because it is a Candidate Key just like the PRIMARY KEY.
k_name不是SECONDARY INDEX，因为它是一个候选键，就像PRIMARY KEY一样。
user-defined clustered indexes cannot have the row order altered once established
用户定义的聚簇索引一旦建立就不能更改行顺序

You cannot manipulate the order of the rows at all. Here is proof of that:

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

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)

4 个解决方案

#1

更多相关文章

随机推荐