Почему оптимизатор 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: 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)