索引在表定义中的位置对是否选择它有影响？
我们来演示两个案例:
这里选择了最佳索引，因为它是首先声明的:
(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 来告诉优化器使用更好的索引。
