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

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

我们来演示两个案例:

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

(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)

但这里另一个索引(效率较低)被选中了:

(Tue Sep 26 10:27:33 2023)[root@GreatSQL][andy]>drop table if exists test;
Query OK, 0 rows affected (0.01 sec)

(Tue Sep 26 10:27:37 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_asc (col1,col2,id),
    -> KEY ix_only_sec (col1,col2),
    -> key ix_desc (col1, col2 desc, id desc) -- <<<<<<<<<<<<<<<<<<<<<<< not used
    -> ) ENGINE=InnoDB;

Query OK, 0 rows affected (0.02 sec)

(Tue Sep 26 10:27:37 2023)[root@GreatSQL][andy]>
(Tue Sep 26 10:27:37 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:27:37 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.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

(Tue Sep 26 10:27:37 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.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

(Tue Sep 26 10:27:37 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:27:37 2023)[root@GreatSQL][andy]>
(Tue Sep 26 10:27:37 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_asc,ix_only_sec,ix_desc | ix_asc | 10      | NULL |    1 |   100.00 | Using where; Backward index scan; Using index |
+----+-------------+-------+------------+-------+----------------------------+--------+---------+------+------+----------+-----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

删除并添加了一个索引，因此表声明中的位置发生了变化。

https://bugs.mysql.com/bug.php?id=36817

- NON OPTIMAL INDEX CHOICE, DEPENDING ON INDEX CREATION ORDER

临时解决方案是使用 FORCE INDEX 来告诉优化器使用更好的索引。