以下整理出了一些 MySQL 查询没有按照预想索引执行的场景。
我们先创建一个表：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE `sku` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`cat_1` int(11) NOT NULL,
`cat_2` int(11) NOT NULL,
`alias` varchar(255) DEFAULT NULL,
`remark` varchar(255) DEFAULT NULL,
`price` decimal(10,2) NOT NULL,
`supplier_id` int(11) NOT NULL,
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_uniq_name` (`name`),
KEY `idx_price` (`price`),
KEY `idx_cat` (`cat_1`,`cat_2`),
KEY `idx_alias` (`alias`),
FULLTEXT KEY `idx_remark` (`remark`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
并造一些测试数据：
1
2
3
4
5
6
7
8
9
INSERT INTO sku (name,cat_1,cat_2,alias,remark,price,supplier_id) VALUES('小白菜',1,1,null,null,3,1);
INSERT INTO sku (name,cat_1,cat_2,alias,remark,price,supplier_id) VALUES('黄叶白',1,1,null,'山东黄叶白，较小',3,1);
INSERT INTO sku (name,cat_1,cat_2,alias,remark,price,supplier_id) VALUES('黄瓜',1,2,null,'山东黄瓜，大',4,1);
INSERT INTO sku (name,cat_1,cat_2,alias,remark,price,supplier_id) VALUES('土豆',1,3,'山药蛋','甘肃山药蛋，黄，大',5,2);
INSERT INTO sku (name,cat_1,cat_2,alias,remark,price,supplier_id) VALUES('鸡蛋',2,1,'土鸡蛋',null,8,3);
INSERT INTO sku (name,cat_1,cat_2,alias,remark,price,supplier_id) VALUES('土鸡鸡胸',3,1,'鸡胸肉',null,8,3);
INSERT INTO sku (name,cat_1,cat_2,alias,remark,price,supplier_id) VALUES('土鸡鸡腿',3,1,'鸡腿肉',null,8,3);
INSERT INTO sku (name,cat_1,cat_2,alias,remark,price,supplier_id) VALUES('土鸡鸡翅',3,1,'鸡翅膀',null,6,3);
INSERT INTO sku (name,cat_1,cat_2,alias,remark,price,supplier_id) VALUES('猪肾',3,2,'猪腰子','成年黑猪腰子',20,3);
使用了 is not null
如：
1
2
3
4
5
6
7
mysql> explain select * from sku where alias is not null;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | sku | NULL | ALL | idx_alias | NULL | NULL | NULL | 9 | 66.67 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.02 sec)
从上我们可以看到，type 为 ALL，key 为 NULL 说明没有用到 idx_alias 索引，进行了全表扫描。
对比下使用等值查询的分析结果：
1
2
3
4
5
6
7
mysql> explain select * from sku where alias='土豆';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | sku | NULL | ref | idx_alias | idx_alias | 1023 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set (0.00 sec)
这时，就正常使用了 idx_alias 索引。
使用 is null，可以使用到索引：
1
2
3
4
5
6
7
mysql> explain select * from sku where alias is null;
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | sku | NULL | ref | idx_alias | idx_alias | 1023 | const | 3 | 100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
1 row in set (0.00 sec)
非主键使用了不等于操作
不等于操作，包括 !=、<>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> explain select * from sku where name!='萝卜';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | sku | NULL | ALL | idx_uniq_name | NULL | NULL | NULL | 9 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from sku where price<>1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | sku | NULL | ALL | idx_price | NULL | NULL | NULL | 9 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.01 sec)
可以看到，都进行了全表扫描。
如果使用主键呢？
1
2
3
4
5
6
7
mysql> explain select * from sku where id!=2;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | sku | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 8 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set (0.00 sec)
可以看到，使用到了索引。
模糊查询没有使用最左匹配原则
指的是 like 语句没有使用 like 'xxx%' 的形式，即明确模糊条件的左侧部分。
1
2
3
4
5
6
7
mysql> explain select * from sku where name like '%鸡%';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | sku | NULL | ALL | NULL | NULL | NULL | NULL | 9 | 11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.00 sec)
同样，我们看到，进行了全表查询，没有使用上 idx_uniq_name 索引。
我们使用最左匹配原则对比测试一下：
1
2
3
4
5
6
7
mysql> explain select * from sku where name like '鸡%';
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | sku | NULL | range | idx_uniq_name | idx_uniq_name | 1022 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
1 row in set (0.00 sec)
可以看到，这次查询使用到了 idx_unit_name 索引。
造成这个的原因在于，即使 MySQL 走 idx_unit_name 索引，MySQL 无法在索引树中明确 %鸡% 是哪个 name，需要遍历所有的索引节点，于是 MySQL 放弃了使用该索引，直接全表扫描。
1
2
3
4
5
6
7
mysql> explain select * from sku force index(idx_uniq_name) where name like '%鸡%' ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | sku | NULL | ALL | NULL | NULL | NULL | NULL | 9 | 11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.00 sec)
复合索引，没有按照最左匹配原则使用
如，我们跳过索引 idx_cat 中的 cat_1 字段：
1
2
3
4
5
6
7
mysql> explain select * from sku where cat_2=1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | sku | NULL | ALL | NULL | NULL | NULL | NULL | 9 | 11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.00 sec)
MySQL使用B+tree索引查询时，是按照从上往下的顺序逐层搜索的，上面层的值不确定，那么索引也就无法使用了。
使用范围查询可能造成遍历较多时
我们首先将数据量扩大，price 的范围情况如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select min(price),max(price) from sku;
+------------+------------+
| min(price) | max(price) |
+------------+------------+
| 0.00 | 20.00 |
+------------+------------+
1 row in set (0.01 sec)
mysql> select avg(price) from sku;
+------------+
| avg(price) |
+------------+
| 5.000521 |
+------------+
1 row in set (0.00 sec)
查询较大范围时
1
2
3
4
5
6
7
mysql> explain select * from sku where price>2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | sku | NULL | ALL | idx_price | NULL | NULL | NULL | 9 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.00 sec)
可以看到进行了全表扫描，因为这时全表扫描的效率比使用索引效率还高。
查询较小范围时：
1
2
3
4
5
6
7
mysql> explain select * from sku where price>10;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | sku | NULL | range | idx_price | idx_price | 5 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set (0.00 sec)
可以看到，这时使用了索引。
对值进行了动态计算操作
这里的动态计算包括函数、数学表达式
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> explain select * from sku where price/2=4;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | sku | NULL | ALL | NULL | NULL | NULL | NULL | 9 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.01 sec)
mysql> explain select * from sku where mod(id,3)=1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | sku | NULL | ALL | NULL | NULL | NULL | NULL | 9 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.01 sec)
mysql> explain select * from sku where left(name,2)='土鸡';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | sku | NULL | ALL | NULL | NULL | NULL | NULL | 9 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.01 sec)
原因在于，表达式操作是动态计算的，MySQL 只能全表扫描。
而如果我们转换下方式，如下，就能使用到索引：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> explain select * from sku where price=(8/2);
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | sku | NULL | ref | idx_price | idx_price | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set (0.00 sec)
mysql> explain select * from sku where price=4;
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | sku | NULL | ref | idx_price | idx_price | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set (0.00 sec)
or 语句中对非索引字段进行了过滤
1
2
3
4
5
6
7
mysql> explain select * from sku where name='土豆' or create_time>'2022-01-01';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | sku | NULL | ALL | idx_uniq_name | NULL | NULL | NULL | 9 | 40.74 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.00 sec)
create_time 么有索引，要查到数据，只能全表查询。
in 语句中候选值太多
我们先看下候选值不多的情况：
1
2
3
4
5
6
7
mysql> explain select * from sku where name in ('小白菜','黄瓜');
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | sku | NULL | range | idx_uniq_name | idx_uniq_name | 1022 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
1 row in set (0.00 sec)
我们可以看到，正常使用了索引 idx_uniq_name。
增加候选值：
1
2
3
4
5
6
7
mysql> explain select * from sku where name in ('小白菜','黄瓜','胡萝卜','苹果','香蕉','香梨','李子','草鱼','猪头肉','辣条','西瓜','笔记本');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | sku | NULL | ALL | idx_uniq_name | NULL | NULL | NULL | 9 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.02 sec)
我们看到，放弃了索引，使用了全表扫描。
# 指定了字符串的其他排序规则
name 字段的排序规则为 utf8mb4_bin，如果指定其他排序规则来查询呢？
1
2
3
4
5
6
7
mysql> explain select * from sku where name ='小白菜' collate utf8mb4_unicode_ci;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | sku | NULL | ALL | idx_uniq_name | NULL | NULL | NULL | 9 | 11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.01 sec)
我们看到了索引失效了，走了全表查询。原因在于排序规则决定了字符串的比较方式，比较方式不同，MySQL 不能直接确定匹配索引字段值，而是全表扫描动态匹配过滤。