目录
在使用 mysql 进行数据存储时,经常用到联合索引,但是使用联合索引有一些注意点,在此记录一下。
使用的 mysql 版本为 8.0.28
定义表结构如下
CREATE TABLE test
(
id INT,
a CHAR(1),
b CHAR(1),
c CHAR(1),
PRIMARY KEY(id),
INDEX idx_union(a,b,c)
);
INSERT INTO test(id,a,b,c) VALUES(1,1,1,1);
INSERT INTO test(id,a,b,c) VALUES(2,2,2,2);
INSERT INTO test(id,a,b,c) VALUES(3,3,3,3);
INSERT INTO test(id,a,b,c) VALUES(4,4,4,4);
INSERT INTO test(id,a,b,c) VALUES(5,5,5,5);
INSERT INTO test(id,a,b,c) VALUES(6,6,6,6);
INSERT INTO test(id,a,b,c) VALUES(7,7,7,7);
可以看到,这里的类型为数字,但是存储的类型为 char,在插入过程中会进行转化。
查询条件对应的列值的类型与列对应的类型不一致
这种情况会有索引无法充分利用的情况,在优化器执行时会有警告。
mysql> EXPLAIN SELECT * FROM test WHERE a = 1 and b = 1 and c = 1;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | test | NULL | index | idx_union | idx_union | 15 | NULL | 7 | 14.29 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 5 warnings (0.01 sec)
mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1739 | Cannot use ref access on index 'idx_union' due to type or collation conversion on field 'a' |
| Warning | 1739 | Cannot use range access on index 'idx_union' due to type or collation conversion on field 'a' |
| Warning | 1739 | Cannot use range access on index 'idx_union' due to type or collation conversion on field 'b' |
| Warning | 1739 | Cannot use range access on index 'idx_union' due to type or collation conversion on field 'c' |
| Note | 1003 | /* select#1 */ select `ican`.`test`.`id` AS `id`,`ican`.`test`.`a` AS `a`,`ican`.`test`.`b` AS `b`,`ican`.`test`.`c` AS `c` from `ican`.`test` where ((`ican`.`test`.`a` = 1) and (`ican`.`test`.`b` = 1) and (`ican`.`test`.`c` = 1)) |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM test WHERE a = 1 and b = 1;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | test | NULL | index | idx_union | idx_union | 15 | NULL | 7 | 14.29 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 4 warnings (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1739 | Cannot use ref access on index 'idx_union' due to type or collation conversion on field 'a' |
| Warning | 1739 | Cannot use range access on index 'idx_union' due to type or collation conversion on field 'a' |
| Warning | 1739 | Cannot use range access on index 'idx_union' due to type or collation conversion on field 'b' |
| Note | 1003 | /* select#1 */ select `ican`.`test`.`id` AS `id`,`ican`.`test`.`a` AS `a`,`ican`.`test`.`b` AS `b`,`ican`.`test`.`c` AS `c` from `ican`.`test` where ((`ican`.`test`.`a` = 1) and (`ican`.`test`.`b` = 1)) |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM test WHERE a = 1 and c = 1;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | test | NULL | index | idx_union | idx_union | 15 | NULL | 7 | 14.29 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 4 warnings (0.01 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1739 | Cannot use ref access on index 'idx_union' due to type or collation conversion on field 'a' |
| Warning | 1739 | Cannot use range access on index 'idx_union' due to type or collation conversion on field 'a' |
| Warning | 1739 | Cannot use range access on index 'idx_union' due to type or collation conversion on field 'c' |
| Note | 1003 | /* select#1 */ select `ican`.`test`.`id` AS `id`,`ican`.`test`.`a` AS `a`,`ican`.`test`.`b` AS `b`,`ican`.`test`.`c` AS `c` from `ican`.`test` where ((`ican`.`test`.`a` = 1) and (`ican`.`test`.`c` = 1)) |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.01 sec)
mysql> EXPLAIN SELECT * FROM test WHERE b = 1 and c = 1;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | test | NULL | index | NULL | idx_union | 15 | NULL | 7 | 14.29 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 3 warnings (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1739 | Cannot use range access on index 'idx_union' due to type or collation conversion on field 'b' |
| Warning | 1739 | Cannot use range access on index 'idx_union' due to type or collation conversion on field 'c' |
| Note | 1003 | /* select#1 */ select `ican`.`test`.`id` AS `id`,`ican`.`test`.`a` AS `a`,`ican`.`test`.`b` AS `b`,`ican`.`test`.`c` AS `c` from `ican`.`test` where ((`ican`.`test`.`b` = 1) and (`ican`.`test`.`c` = 1)) |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM test WHERE a = 1;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | test | NULL | index | idx_union | idx_union | 15 | NULL | 7 | 14.29 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 3 warnings (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1739 | Cannot use ref access on index 'idx_union' due to type or collation conversion on field 'a' |
| Warning | 1739 | Cannot use range access on index 'idx_union' due to type or collation conversion on field 'a' |
| Note | 1003 | /* select#1 */ select `ican`.`test`.`id` AS `id`,`ican`.`test`.`a` AS `a`,`ican`.`test`.`b` AS `b`,`ican`.`test`.`c` AS `c` from `ican`.`test` where (`ican`.`test`.`a` = 1) |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM test WHERE b = 1;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | test | NULL | index | NULL | idx_union | 15 | NULL | 7 | 14.29 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 2 warnings (0.01 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1739 | Cannot use range access on index 'idx_union' due to type or collation conversion on field 'b' |
| Note | 1003 | /* select#1 */ select `ican`.`test`.`id` AS `id`,`ican`.`test`.`a` AS `a`,`ican`.`test`.`b` AS `b`,`ican`.`test`.`c` AS `c` from `ican`.`test` where (`ican`.`test`.`b` = 1) |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)
mysql> EXPLAIN SELECT * FROM test WHERE c = 1;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | test | NULL | index | NULL | idx_union | 15 | NULL | 7 | 14.29 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 2 warnings (0.01 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1739 | Cannot use range access on index 'idx_union' due to type or collation conversion on field 'c' |
| Note | 1003 | /* select#1 */ select `ican`.`test`.`id` AS `id`,`ican`.`test`.`a` AS `a`,`ican`.`test`.`b` AS `b`,`ican`.`test`.`c` AS `c` from `ican`.`test` where (`ican`.`test`.`c` = 1) |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
修改类型为一致,则正常执行
mysql> EXPLAIN SELECT * FROM test WHERE a = '1' and b = '1' and c = '1';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+--------------------------+
| 1 | SIMPLE | test | NULL | ref | idx_union | idx_union | 15 | const,const,const | 1 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)
mysql> show warnings;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `ican`.`test`.`id` AS `id`,`ican`.`test`.`a` AS `a`,`ican`.`test`.`b` AS `b`,`ican`.`test`.`c` AS `c` from `ican`.`test` where ((`ican`.`test`.`c` = '1') and (`ican`.`test`.`b` = '1') and (`ican`.`test`.`a` = '1')) |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> EXPLAIN SELECT * FROM test WHERE a = '1';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+
| 1 | SIMPLE | test | NULL | ref | idx_union | idx_union | 5 | const | 1 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `ican`.`test`.`id` AS `id`,`ican`.`test`.`a` AS `a`,`ican`.`test`.`b` AS `b`,`ican`.`test`.`c` AS `c` from `ican`.`test` where (`ican`.`test`.`a` = '1') |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> EXPLAIN SELECT * FROM test WHERE b = '1';
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | test | NULL | index | idx_union | idx_union | 15 | NULL | 7 | 14.29 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `ican`.`test`.`id` AS `id`,`ican`.`test`.`a` AS `a`,`ican`.`test`.`b` AS `b`,`ican`.`test`.`c` AS `c` from `ican`.`test` where (`ican`.`test`.`b` = '1') |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> EXPLAIN SELECT * FROM test WHERE c = '1';
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | test | NULL | index | idx_union | idx_union | 15 | NULL | 7 | 14.29 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)
mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `ican`.`test`.`id` AS `id`,`ican`.`test`.`a` AS `a`,`ican`.`test`.`b` AS `b`,`ican`.`test`.`c` AS `c` from `ican`.`test` where (`ican`.`test`.`c` = '1') |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
尤其是最左列的类型一致后,type 列从 index 变为 ref。
只有一个联合索引且包含非主键外全部列
查询条件全部为等值查询
sql查询条件 | type | Extra | ref | rows | filtered | 示例sql | 说明 |
符合索引中全部列查询 或者按照索引定义的最左列顺序查询 | ref | Using where; Using index | 对应值为const 对应的数量为等值查询列数量 | 符合要求的行数 | 100% | EXPLAIN SELECT * FROM test WHERE a = '1' and b = '1' and c = '1'; EXPLAIN SELECT * FROM test WHERE a = '1' and b = '1'; EXPLAIN SELECT * FROM test WHERE a = '1'; | 充分利用了索引 |
符合索引中部分列查询 且包含索引定义的最左列 | ref | Using where; Using index | 对应值为const 对应的数量为等值查询列数量 | 符合要求的行数 | 非100% | EXPLAIN SELECT * FROM test WHERE a = '1' and c = '1'; | 部分利用了索引 |
符合索引中部分列查询 且不包含索引定义的最左列 | index | Using where; Using index | NULL | 索引中数据总行数 | 非100% | EXPLAIN SELECT * FROM test WHERE b = '1' and c = '1'; EXPLAIN SELECT * FROM test WHERE b = '1'; EXPLAIN SELECT * FROM test WHERE c = '1'; | 部分利用了索引 |
可以看到,在使用联合索引的时候有一种情况特殊
联合索引非首列进行查询, type 为 index。即无论如何都能进行使用索引,但是 ref 使用索引的效率更高。
查询条件有范围查询
sql查询条件 | type | Extra | ref | rows | filtered | 示例sql | 说明 |
符合索引中全部列查询或者按照索引定义的最左列顺序查询 且只有一个范围查询 | range | Using where; Using index | NULL | 符合要求的行数 | 100% | EXPLAIN SELECT * FROM test WHERE a = '1' and b = '1' and c > '1'; EXPLAIN SELECT * FROM test WHERE a = '1' and b > '1'; EXPLAIN SELECT * FROM test WHERE a > '1'; | 利用了索引,完全使用了最左匹配原则 |
符合索引中全部列查询或者按照索引定义的最左列顺序查询 且不止一个范围查询 | range | Using where; Using index | NULL | 符合要求的行数 | 非100% | EXPLAIN SELECT * FROM test WHERE a = '1' and b > '1' and c > '1'; EXPLAIN SELECT * FROM test WHERE a > '1' and b > '1' and c > '1'; EXPLAIN SELECT * FROM test WHERE a > '1' and c > '1'; | 利用了索引,部分使用了最左匹配原则 |
符合索引中部分列查询 且包含索引定义的最左列 且最少一个范围查询 | ref | Using where; Using index | 对应值为const 对应的数量为等值查询列数量 | 符合要求的行数 | 非100% | EXPLAIN SELECT * FROM test WHERE a = '1' and c > '1'; | 利用了索引,部分使用了最左匹配原则 |
符合索引中部分列查询 且不包含索引定义的最左列 | index | Using where; Using index | NULL | 索引中数据总行数 | 非100% | EXPLAIN SELECT * FROM test WHERE b = '1' and c > '1'; EXPLAIN SELECT * FROM test WHERE b > '1' and c > '1'; EXPLAIN SELECT * FROM test WHERE c > '1'; | 利用了索引,未使用最左匹配原则 |
可以看到,在使用联合索引的时候有两种情况特殊
- 通过最左列进行等值查询,间隔的列执行范围查询,最终 type 为 ref。
- 不包含最左列,使用了等值或者范围查询或者两者的组合。
针对索引使用,执行计划中 type 最差的为 index,尽量能做到 ref 或者 range,这样可以做到扫描索引的范围小。
有联合索引未包含全部列
ALTER TABLE test ADD COLUMN d CHAR(1);
sql查询条件 | type | Extra | ref | rows | filtered | 示例sql | 说明 |
符合索引中全部列查询 或者按照索引定义的最左列顺序查询 且查询条件为全部列 | ref | Using index condition | 对应值为const 对应的数量为等值查询列数量 | 符合要求的行数 | 100% | EXPLAIN SELECT * FROM test WHERE a = '1' and b = '1' and c = '1'; EXPLAIN SELECT * FROM test WHERE a = '1' and b = '1'; EXPLAIN SELECT * FROM test WHERE a = '1'; | 部分利用了索引,部分数据需要通过回表查询缺失的数据 |
符合索引中全部列查询 或者按照索引定义的最左列顺序查询 且查询条件为联合索引全部列 | ref | Using where; Using index | 对应值为const 对应的数量为等值查询列数量 | 符合要求的行数 | 100% | EXPLAIN SELECT a,b,c FROM test WHERE a = '1' and b = '1' and c = '1'; EXPLAIN SELECT a,b,c FROM test WHERE a = '1' and b = '1'; EXPLAIN SELECT a,b,c FROM test WHERE a = '1'; | 充分利用了索引 |
符合索引中部分列查询 且包含索引定义的最左列 且查询条件为全部列 | ref | Using index condition | 对应值为const 对应的数量为等值查询列数量 | 符合要求的行数 | 非100% | EXPLAIN SELECT * FROM test WHERE a = '1' and c = '1'; | 部分利用了索引,部分数据需要通过回表查询缺失的数据 |
符合索引中部分列查询 且包含索引定义的最左列 且查询条件为联合索引全部列 | ref | Using where; Using index | 对应值为const 对应的数量为等值查询列数量 | 符合要求的行数 | 非100% | EXPLAIN SELECT a,b,c FROM test WHERE a = '1' and c = '1'; | 部分利用了索引 |
符合索引中部分列查询 且不包含索引定义的最左列 且查询条件为全部列 | ALL | Using where | NULL | 索引中数据总行数 | 非100% | EXPLAIN SELECT * FROM test WHERE b = '1' and c = '1'; EXPLAIN SELECT * FROM test WHERE b = '1'; EXPLAIN SELECT * FROM test WHERE c = '1'; | 未利用索引 |
符合索引中部分列查询 且不包含索引定义的最左列 且查询条件为联合索引全部列 | index | Using where; Using index | NULL | 索引中数据总行数 | 非100% | EXPLAIN SELECT a,b,c FROM test WHERE b = '1' and c = '1'; EXPLAIN SELECT a,b,c FROM test WHERE b = '1'; EXPLAIN SELECT a,b,c FROM test WHERE c = '1'; | 部分利用了索引 |
有的 Extra 信息为 Using index condition,表明使用了回表查询。
从 mysql 5.6 开始添加的功能,但是看官网链接里 5.6 相关的删除了,最老的是 5.7。
https://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html
https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html
5.6 的链接重定向到 8.0 的链接。
https://dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html
查询时不包含首列,查询结果查询全部会直接全表扫描。
针对联合索引等值查询乱序的情况,优化器在执行 sql 之前会将查询条件进行调整来适应索引定义的列的顺序,通过 cbo 计算查询的代价,从而匹配适合的索引,如果一个都没匹配到,则执行全表扫描。
参考链接
https://www.cnblogs.com/ql211lin/p/11124574.html