大家好,我是
方圆
,看到这两种(排序
和查询
)不会用到索引的情况,一下让我想到了之前的小米面试官,接着向下看吧~
1. 说在前头
这篇博客《Mysql是怎样运行的》
,将参考书中P123 - P125,它列举的就是在排序
时不使用索引的情况,这让我回想起在面试的时候,被问到在查询
时不使用索引的情况,所以我想将这两个问题汇总在一起,以便供大家参考。
2. 说前咱得准备准备
来,我们先建个表
create table single_table(
id int not null auto_increment,
key1 varchar(100),
key2 int,
key3 varchar(100),
key_part1 varchar(100),
key_part2 varchar(100),
key_part3 varchar(100),
common_field varchar(100),
primary key (id),
key idx_key1(key1),
unique key uk_key2(key2),
key idx_key3 (key3),
key idx_key_part(key_part1, key_part2, key_part3)
)engine=InnoDB charset=utf8;
哟!你给我写这么长的代码,你给我解释解释:
主要知道下面这些信息就行
id列
为聚簇索引
key1列
为二级索引
key2列
唯一(unique)二级索引
key3列
为二级索引
key_part1、key_part2、key_part3
为三列的联合索引(二级索引)
再插入10条数据,如下
这下准备差不多了,我们步入正题
!
3. 重头戏一,在排序时用不到索引的情况
ASC、DESC混用
以下这条代码想让key_part1列升序排列
,key_part2降序排列
是不会用到索引的
select key_part1, key_part2 from single_table
order by key_part1, key_part2 desc;
区别于以下代码,这种情况会使用到我们的联合索引
select key_part1, key_part2 from single_table
order by key_part1, key_part2;
注:这种情况适用于mysql8.0之前的版本,而在8.0版本会用到索引,分析语句和分析结果如下
explain select key_part1, key_part2 from single_table order by key_part1, key_part2 desc;
排序列包含非用一个索引的列
因为key1和key2为非同一列的索引,在key1相同的情况下,是不会按照key2列进行排序,所以用不到索引
(explain)select id, key1, key2 from single_table order by key1, key2;
分析结果如下
排序列是某个联合索引的索引列,但是这些排序列在联合索引中并不连续
上代码,一看就能明白!
select key_part1, key_part3 from single_table order by key_part1, key_part3;
联合索引在key_part1相同的时候,并不会按照key_part3排序,中间还有key_part2,所以不会用到联合索引(但是mysql8.0会用到)
用来形成扫描区间的索引列与排序列不同
先上代码,咱再解释
(explain) select id, key1, key2 from single_table where key1 < 'e' order by key2 desc;
该语句形成的扫描区间是key < ‘e’,需要用到的索引是key1列的idx_key1,并不会用到key2列的索引uk_key2进行排序
分析结果如下
排序列不是以单独的列名出现在order by语句中
代码哥哥!
(explain) select id, key1, key2 from single_table order by upper(key1);
我们可以发现,在排序列中使用了函数upper()
,所以排序时不会用到idx_key1索引
分析结果如下
哎哎哎,我可是讲完这节课了,数数一共几种情况啊?
“五种!五种!”
行,那还不错,喝口水咱接着看!
4. 重头戏二,在查询时用不到索引的情况
mysql认为使用索引相比于全表扫描更慢,则不使用索引
这种情况非常简单,我一说大家就懂,写条代码
explain select * from single_table where key2 > 1;
这条代码,我们可能以为它会使用到idx_key2索引,其实不然,它采用的是全表扫描,看分析结果
如果查询条件中用使用了or,即便其中某一列含有索引也不会用到
这个在网上查阅的资料,大多会说:用or分隔开的条件,如果or前的条件中的列有索引,而后面的列没有索引,那么涉及到的索引都不会被用到
但是事实上,并不受在or前后的影响,看如下两条语句
explain select * from single_table where id < 5 or common_field < 'f';
分析结果为all,全表扫描
我们把or前后的条件换一下再试
explain select * from single_table where common_field < 'f' or id < 5;
仍然为全表扫描,所以索引列在or前后并无影响,都不走索引
注:也有特例,大家可以了解一下Union索引合并,这个并不会执行全表扫描
对于联合索引,如果没用使用索引的第一部分,则不使用索引
简单,上了代码就能看明白
explain select * from single_table where key_part2 = 'e';
联合索引的第一部分为 key_part1,因为没有使用到,所以不会用到联合索引,对照如下分析结果
若模糊查询以%开头,不使用索引(太简单,记住就行)
如果列为字符串,则where条件中的字符常量必须加引号,否则用不到索引
我们写两个代码对比一下就知道了
explain select * from single_table where key3 = 1;
没加引号的,分析结果如下,全表扫描
我们给常量加上引号
再写一个
explain select * from single_table where key3 = '1';
这下分析结果中,就显示用引号了
okk,同学们,可以下课了!
祝大家生活愉快!