为什么Mysql优化器在执行’select * from lookup’而没有order by子句时选择二级索引.
它只是一个侥幸,或者这是一个幕后优化,假设你添加了一个二级索引,它比主键更重要.
我希望通过主键对结果进行排序,因为扫描所有叶节点可以提供回答此查询所需的所有数据.
要重现我创建一个简单的键/值对表(注意不是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: -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)
解决方法:
实际上,聚集索引(也称为gen_clust_index)的填充顺序除了以rowid顺序之外没有押韵或原因.几乎不可能按id顺序订购rowid.
在InnoDB中,非聚簇索引(也称为二级索引)中的记录包含不在二级索引中的行的主键列. InnoDB使用此主键值来搜索聚簇索引中的行.
二级索引管理订单.但是,每个辅助索引条目都有一个指向正确行的主键.另外,请考虑您为k_name提到的覆盖索引方案.
现在,让我们暂时切换一下,讨论PRIMARY KEY和k_name：
问题：原始查询,主键或k_name请求的列数更多？
答案：k_name,因为它同时包含name和id(id是internal,因为它是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 Query Optimizer会选择最能满足您查询的索引.当然,k_name具有不公平的优势,因为
>表中的每一列都是单独编制索引的
>表中的每一列都是Candidate Key
> k_name不是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)
标签：mysql,innodb
来源： https://codeday.me/bug/0518/1126810.html
