MySQL优化器bug 根据索引声明顺序选择但非最佳索引

索引在表定义中的位置对是否选择它有影响?

我们来演示两个案例:

这里选择了最佳索引,因为它是首先声明的:

<code>(Tue Sep 26 10:23:38 2023)[root@GreatSQL][andy]>select version(); +-----------+ | version() | +-----------+ | 8.0.32-24 | +-----------+ 1 row in set (0.00 sec) (Tue Sep 26 10:23:44 2023)[root@GreatSQL][andy]>drop table if exists test; CREATE TABLE test ( Query OK, 0 rows affected (0.02 sec) (Tue Sep 26 10:23:45 2023)[root@GreatSQL][andy]>CREATE TABLE test ( -> id int unsigned NOT NULL AUTO_INCREMENT, -> col1 int DEFAULT NULL, -> col2 int DEFAULT NULL, -> PRIMARY KEY (id), -> key ix_desc (col1, col2 desc, id desc), -- <<<<<<<<<<<<<<<<<<<<<<< used -> KEY ix_asc (col1,col2,id), -> KEY ix_only_sec (col1,col2) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.03 sec) (Tue Sep 26 10:23:45 2023)[root@GreatSQL][andy]> (Tue Sep 26 10:23:45 2023)[root@GreatSQL][andy]>insert into test(id,col1,col2) values (1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),(6,6,6),(7,7,7); Query OK, 7 rows affected (0.01 sec) Records: 7 Duplicates: 0 Warnings: 0 (Tue Sep 26 10:23:45 2023)[root@GreatSQL][andy]>insert into test(id,col1,col2) values (8,8,8),(9,9,9),(10,10,10),(11,11,11),(12,12,12),(13,13,13),(14,14,14); Query OK, 7 rows affected (0.01 sec) Records: 7 Duplicates: 0 Warnings: 0 (Tue Sep 26 10:23:45 2023)[root@GreatSQL][andy]>insert into test(id,col1,col2) values (15,15,15),(16,16,16),(17,17,17),(18,18,18),(19,19,19),(20,20,20),(21,21,21); Query OK, 7 rows affected (0.01 sec) Records: 7 Duplicates: 0 Warnings: 0 (Tue Sep 26 10:23:45 2023)[root@GreatSQL][andy]>analyze table test; +-----------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------+---------+----------+----------+ | andy.test | analyze | status | OK | +-----------+---------+----------+----------+ 1 row in set (0.01 sec) (Tue Sep 26 10:23:45 2023)[root@GreatSQL][andy]> (Tue Sep 26 10:23:45 2023)[root@GreatSQL][andy]>explain select * from test where col1=10 and col2 between 1 and 10 order by col2 desc, id desc; +----+-------------+-------+------------+-------+----------------------------+---------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+----------------------------+---------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | test | NULL | range | ix_desc,ix_asc,ix_only_sec | ix_desc | 10 | NULL | 1 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+----------------------------+---------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)