通过上面表格,我们会发现,复合索引( name
, age
, nickname
)和它们三列的单个索引是有区别的(该案例不做复合索引和单列索引的性能分析)主要区别有以下几点:
-
复合索引中,只有最左边的一列单独使用才会触发索引,其他的列单个使用无法触发索引。
-
复合索引中,从最左边开始,相连的两个或多个会触发索引(相连和不相连的性能不同),如果没有最左边的列,后面的无论是否相连都不会触发索引。
-
通过分析我们可以发现,几个列的复合索引,就相当于 有几个索引,如复合索引(
name
,age
,nickname
)相当于name
索引、(name
,age
)索引以及(name
,age
,nickname
) 索引(注意,后面两个索引不能再按复合索引算,只是为了解释说明)。 -
where 条件后面的顺序不影响复合索引的触发如
age=21andname='test1'
一样会触发复合索引(MySQL会对查询条件顺序进行优化,我们无需担心顺序问题,但是为了更好理解,建议合理安排顺序)。
单表复合索引的性能分析
===========
上面表格中,第一行和第二行都走了索引,但是第一行是相连的两列,rows是1,这里我们可以说是使用了( name
, age
)索引(该索引并发真实存在,只是为了区分效果);第二行是不相连的两列rows是2,然后第四行是使用了复合索引的第一列 name 和非复合索引中的列作为查询条件,rows 同样是2,非相连的两列作为查询条件时,复合索引相当于使用了第一列作为查询条件。
为什么会这样呢?MySQL 在进行查询时,会根据索引筛选出复合索引的行,如果存在查询条件不在索引中的列,会进行二次筛选(即根据筛选出来的行进行二次查询),导致遍历的行数增加。
部分查询条件会导致全表扫描
特殊注意:
-
使用MySQL的CONCAT函数拼接条件一样会使用索引。
-
在使用 in 时,如果只有一个值,则等价于使用 =符号,会触发索引,包含两个或多个值,则索引失效。
-
在使用 not in 时,无论多少个值,索引都会失效。
-
使用 null 关键字查询时,无论值是否有为空的,都会触发索引。
-
在使用 like 关键字时,只要使用了%号进行模糊匹配,就会使索引失效。
-
网上说使用 is null 会使索引失效,我测试的结果是,使用 is null ,无论是复合索引还是单列索引都能触发索引。
总结
–
在我们使用单列索引和复合索引时,需要注意以下几点:
-
常用的字段放在第一列,经常和第一列一起使用的字段放在第二列,如用户表的电话和姓名,身份证表的身份照号和姓名,如果超过两列,则注意其顺序。
-
条件查询时,尽可能所有字段都有索引(如sex这种情况例外,因为sex的值只有三个,冗余性太高,定位比较差,不如全表检索快),这样能提高很多效率。
-
查询时避免会使索引失效的情况发生,如or条件,可以使用union或者union all来达到相同效果。
-
索引能提高查询效率,但是过多的索引,同样会降低我们的修改操作效率,对此,我们创建索引需要合理,在使用频率较低的情况下,尽量不要创建索引。
-
select*
或许性能和指定字段相差不是非常大,但是代码的可读性降低了很多,不推荐使用。
关注牧码小子,后台回复 Java ,领取松哥为你精心准备的Java干货!
往期文章一览
独家面经总结,超级精彩
本人面试腾讯,阿里,百度等企业总结下来的面试经历,都是真实的,分享给大家!
Java面试准备
准确的说这里又分为两部分:
- Java刷题
- 算法刷题
Java刷题:此份文档详细记录了千道面试题与详解;
面试准备
准确的说这里又分为两部分:
- Java刷题
- 算法刷题
Java刷题:此份文档详细记录了千道面试题与详解;
[外链图片转存中…(img-VJyyjHhZ-1719168215528)]
[外链图片转存中…(img-Ukfr6oON-1719168215528)]