性能文章>Mysql的复合索引,生效了吗?来篇总结文章>

Mysql的复合索引,生效了吗?来篇总结文章原创

2147132

背景

最近频繁出现慢SQL导致系统性能问题,于是决定针对索引进行一些优化。一些表结构本身已经有了不少索引,如果再继续添加索引,势必会影响到插入数据的性能。那么,是否可以使用组合索引来达到目的呢?这篇文章咱们来一探究竟。

认识复合索引

如果where条件中使用到多个字段,并且需要对多个字段建立索引,此时就可以考虑采用复合索引(组合索引)。比如查询地址时需要输入省、市,那么在省、市上建立索引,当数据量大时会明显提高查询速度。

组合索引有啥优势呢?

  • 减少查询开销:建立复合索引(c1,c2,c3),实际上相当于建立了(c1),(c1,c2),(c1,c2,c3)三个索引。对于大表来说,可以极大减少开销。
  • 覆盖索引:MySQL可以直接通过遍历索引取得数据,而无需回表,减少了很多的随机io操作。
  • 效率高:索引列越多,通过索引筛选出来的数据就越少,从而提升查询效率。

缺点:

  • 索引字段越多,创建的索引越多,每个索引都会增加磁盘空间的开销;
  • 索引越多对查询效率提升越高,但对需要更新索引的增删改操作会有效率影响;

复合索引使用建议:单表最好不要超过1个复合索引,单个复合索引最好不超过3个字段。一旦超过,就需要考虑必要性和是否有其他替代方案。

最左匹配原则

复合索引遵从最左匹配原则,顾名思义,在组合索引中,最左侧的字段优先匹配。因此,在创建组合索引时,where子句中使用最频繁的字段放在组合索引的最左侧。

辅助索引是B+树实现的,虽然可以指定多个列,但是每个列的比较优先级不一样,写在前面的优先比较高。一旦出现遗漏,在B+树上就无法继续搜索了(通过补齐等措施解决的除外),因此是按照最左连续匹配来的。既然是在B+树上搜索,对于条件的比较自然是要求精确匹配(即"="和"IN")。

在where子句中用到两个字段c1和c2,那么创建索引时,两个字段的顺序应该是(c1,c2)还是(c2,c1)呢?

正确的做法是:把重复值最少的放前面。比如,95%的值都不重复,则可考虑放最前面。

字段顺序的影响

复合索引遵从最左匹配原则,那么在where查询条件中的字段是否也需要按照索引的顺序来写呢?

比如,复合索引为(c1,c2,c3),下面两个查询条件是否会对索引有影响呢?

select * from t_user where c1 = 1 and c2 = 4;
select * from t_user where c2 = 4 and c1 = 1;

看到有文章提出第一条SQL语句的效率更高,是否可信?两种查询方式条件一样,结果也应该一样,正常来说Mysql也会让它们走同样的索引。

通过Mysql的查询优化器explain分析上述两个条语句,会发现执行计划完全相同。也就是说:SQL语句中的字段顺序并不需要与复合索引字段顺序一致,查询优化器会自动调整顺序

如果说有效率影响,那么也就是查询优化器矫正顺序的影响吧,几乎可以忽略不计。

单字段是否可以触发索引?

对于复合索引为(c1,c2,c3),相当于(c1),(c1,c2),(c1,c2,c3)三个索引,如果查询条件中只有c1,很显然是会走索引的。

但如果where条件如下呢:

from t_user where c2 = 4;

上述语句是否会走索引呢?这得分几种情况来说明。

执行explan查询c1为条件的SQL语句:

explain select * from t_user where c1 = 1;

上述语句走的索引类型为:ref。ref类型表示Mysql会根据特定的算法快速查找到符合条件的索引,而不会对索引中每一个数据都进行扫描判断。这种类型的索引为了快速查出数据,索引就需要满足一定的数据结构。

执行explan查询c2为条件的SQL语句:

explain select c2 from t_user where c2 = 4;

上述语句走的索引类型为:index。index类型表示Mysql会对整个索引进行扫描,只要是索引或索引的一部分Mysql就可能会采用index方类型的方式扫描。由于此种方式是一条数据一条数据查找,性能并不高。

在这个例子中,对查询的字段有一定的要求,where中条件为c2,select中查询出的字段也只能是c2,才会走index类型的索引

如果将c2换成*或其他字段:

explain select * from t_user where c2 = 4;

上述语句会发现,不再走index索引,而是走全表扫描了。这也从侧面说明了Mysql为什么要讲最左匹配原则了。

所以结论是:如果单个字段为复合索引的首个字段,则会正常走索引;如果单个字段是复合索引的其他字段,且仅有该字段出现在select后面,则会走index类型索引;而其他情况,则走全表扫描

复合索引可以替代单一索引吗?

单一索引:(c1),复合索引:(c1,c2)。

当c1作为查询条件时,单一索引和复合索引查询速度几乎一样,甚至比复合索引还要略快。

如果仅用复合聚集索引的非起始列(c2)作为查询条件的话,复合索引是不起任何作用的。

对于一张表来说,如果有复合索引(c1,c2),则无需再建单一索引(c1)。

如果已经存在单一索引(c1),因查询所需,可添加复合索引(c1,c2)来提升效率。

小结

本篇文章整理了Mysql复合索引使用时所需注意的一些知识点,在使用时可以通过explain来查看一下你的SQL语句是否走了索引,走了什么索引。

但还要了解的是:Mysql的执行计划和查询的实际执行过程并不完全吻合。

别问我为什么知道,因为在实践中遇到过。同一条SQL语句,查询条件不同,有可能会走索引,也有可能不会走索引。

请先登录,再评论

666

3月前

为你推荐

5G时代,如何彻底搞定海量数据库的设计与实践
5G时代,业务数据越来越丰富,业务使用MySQL数据库作为后台存储,存储引擎使用InnoDB,会带来哪些挑战?如何针对公司业务特点及MySQL数据库特性,制定若干数据库使用规范供一线RD在设计业务时参
MySQL之KEY分区引发的血案
需求背景业务表tb_image部分数据如下所示,其中id唯一,image_no不唯一。image_no表示每个文件的编号,每个文件在业务系统中会生成若干个文件,每个文件的唯一ID就是字段id:业务表t
Prometheus时序数据库-数据的插入
前言在之前的文章里,笔者详细的阐述了Prometheus时序数据库在内存和磁盘中的存储结构。有了前面的铺垫,笔者就可以在本篇文章阐述下数据的插入过程。 监控数据的插入在这里,笔者并不会去讨论Promt
Prometheus时序数据库-数据的查询
前言在之前的博客里,笔者详细阐述了Prometheus数据的插入过程。但我们最常见的打交道的是数据的查询。Prometheus提供了强大的Promql来满足我们千变万化的查询需求。在这篇文章里面,笔者
解Bug之路-主从切换"未成功"?
前言数据库主从切换是个非常有意思的话题。能够稳定的处理主从切换是保证业务连续性的必要条件。今天笔者就来讲讲主从切换过程中一个小小的问题。 故障场景最近线上进行主从切换,大部分应用都切过去了,但是某些应
MySQL 死锁套路:一次诡异的批量插入死锁问题分析
线上最近出现了批量insert的死锁,百思不得解。死锁记录如下:```2018-10-26T11:04:41.759589Z 8530809 [Note] InnoDB: (1) TRANSACTI
MySQL 死锁套路:唯一索引下批量插入顺序不一致
死锁的本质是资源竞争,批量插入如果顺序不一致很容易导致死锁,我们来分析一下这个情况。为了方便演示,把批量插入改写为了多条 insert。先来做几个小实验,简化的表结构如下:```CREATE TABL
MySQL 死锁套路:再来看一例走不同索引更新的例子
前面有文章介绍了利用调试MySQL源码的方式来调试锁相关的信息,这里利用这个工具来解决一个比较简单的问题,线上的表字段较多,这里简单成为了一个表:```CREATE TABLE `t3` ( `id