当前位置:首页 > > 架构师社区
[导读]对于互联网公司来说,随着用户量和数据量的不断增加,慢查询是无法避免的问题。一般情况下如果出现慢查询,意味着接口响应慢、接口超时等问题。如果是高并发的场景,可能会出现数据库连接被占满的情况,直接导致服务不可用。慢查询的确会导致很多问题,我们要如何优化慢查询呢?

前言

对于互联网公司来说,随着用户量和数据量的不断增加,慢查询是无法避免的问题。一般情况下如果出现慢查询,意味着接口响应慢、接口超时等问题。如果是高并发的场景,可能会出现数据库连接被占满的情况,直接导致服务不可用。

慢查询的确会导致很多问题,我们要如何优化慢查询呢?

主要解决办法有:

  • 监控sql执行情况,发邮件、短信报警,便于快速识别慢查询sql
  • 打开数据库慢查询日志功能
  • 简化业务逻辑
  • 代码重构、优化
  • 异步处理
  • sql优化
  • 索引优化

其他的办法先不说,后面有机会再单独介绍。今天我重点说说索引优化,因为它是解决慢查询sql问题最有效的手段。

如何查看某条sql的索引执行情况呢?

没错,在sql前面加上explain关键字,就能够看到它的执行计划,通过执行计划,我们可以清楚的看到表和索引执行的情况,索引有没有执行、索引执行顺序和索引的类型等。

索引优化的步骤是:

  1. 使用 explain查看sql执行计划
  2. 判断哪些索引使用不当
  3. 优化sql,sql可能需要多次优化才能达到索引使用的最优值

既然索引优化的第一步是使用explain,我们先全面的了解一下它。

explain介绍

先看看mysql的官方文档是怎么描述explain的:

explain | 索引优化的这把绝世好剑,你真的会用吗?
  • EXPLAIN可以使用于 SELECT, DELETE, INSERT, REPLACE,和 UPDATE语句。
  • 当EXPLAIN与可解释的语句一起使用时,MySQL将显示来自优化器的有关语句执行计划的信息。也就是说,MySQL解释了它将如何处理该语句,包括有关如何连接表以及以何种顺序连接表的信息。
  • 当EXPLAIN与非可解释的语句一起使用时,它将显示在命名连接中执行的语句的执行计划。
  • 对于SELECT语句, EXPLAIN可以显示的其他执行计划的警告信息。

explain详解

explain的语法:

{EXPLAIN | DESCRIBE | DESC}
tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
[explain_type]
{explainable_stmt | FOR CONNECTION connection_id}

explain_type: {
EXTENDED
| PARTITIONS
| FORMAT = format_name
}

format_name: {
TRADITIONAL
| JSON
}

explainable_stmt: {
SELECT statement
| DELETE statement
| INSERT statement
| REPLACE statement
| UPDATE statement
}

用一条简单的sql看看使用explain关键字的效果:

explain  select * from test1;

执行结果:explain | 索引优化的这把绝世好剑,你真的会用吗?从上图中看到执行结果中会显示12列信息,每列具体信息如下:

explain | 索引优化的这把绝世好剑,你真的会用吗?

说白了,我们要搞懂这些列的具体含义才能正常判断索引的使用情况。

话不多说,直接开始介绍吧。

id列

该列的值是select查询中的序号,比如:1、2、3、4等,它决定了表的执行顺序。

某条sql的执行计划中一般会出现三种情况:

  1. id相同
  2. id不同
  3. id相同和不同都有

那么这三种情况表的执行顺序是怎么样的呢?

1.id相同

执行sql如下:

explain  select * from test1 t1 inner  join test1 t2 on t1.id=t2.id

结果:explain | 索引优化的这把绝世好剑,你真的会用吗?我们看到执行结果中的两条数据id都是1,是相同的。

这种情况表的执行顺序是怎么样的呢?

答案:从上到下执行,先执行表t1,再执行表t2。

执行的表要怎么看呢?

答案:看table字段,这个字段后面会详细解释。

2.id不同

执行sql如下:

explain  select * from test1 t1 where t1.id = (select  id  from  test1 t2 where  t2.id=2);

结果:explain | 索引优化的这把绝世好剑,你真的会用吗?我们看到执行结果中两条数据的id不同,第一条数据是1,第二条数据是2。

这种情况表的执行顺序是怎么样的呢?

答案:序号大的先执行,这里会从下到上执行,先执行表t2,再执行表t1。

3.id相同和不同都有

执行sql如下:

explain 
select t1.* from test1 t1
inner join (select max(id) mid from test1 group by id) t2
on t1.id=t2.mid

结果:

explain | 索引优化的这把绝世好剑,你真的会用吗?

我们看到执行结果中三条数据,前面两条数据的的id相同,第三条数据的id跟前面的不同。

这种情况表的执行顺序又是怎么样的呢?

答案:先执行序号大的,先从下而上执行。遇到序号相同时,再从上而下执行。所以这个列子中表的顺序顺序是:test1、t1、

也许你会在这里心生疑问:<derived2> 是什么鬼?

它表示派生表,别急后面会讲的。

还有一个问题:id列的值允许为空吗?

答案在后面揭晓。

select_type列

该列表示select的类型。具体包含了如下11种类型:

explain | 索引优化的这把绝世好剑,你真的会用吗?

但是常用的其实就是下面几个:

类型 含义
SIMPLE 简单SELECT查询,不包含子查询和UNION
PRIMARY 复杂查询中的最外层查询,表示主要的查询
SUBQUERY SELECT或WHERE列表中包含了子查询
DERIVED FROM列表中包含的子查询,即衍生
UNION UNION关键字之后的查询
UNION RESULT 从UNION的表获取结果集


下面看看这些SELECT类型具体是怎么出现的:


  1. SIMPLE

    执行sql如下:

    explain select * from test1;

    结果:

    explain | 索引优化的这把绝世好剑,你真的会用吗?

    它只在简单SELECT查询中出现,不包含子查询和UNION,这种类型比较直观就不多说了。


  2. PRIMARY 和 SUBQUERY

    执行sql如下:

    explain select * from test1 t1 where t1.id = (select id from  test1 t2 where  t2.id=2);

    结果:

    explain | 索引优化的这把绝世好剑,你真的会用吗?

    我们看到这条嵌套查询的sql中,最外层的t1表是PRIMARY类型,而最里面的子查询t2表是SUBQUERY类型。


  3. DERIVED

    执行sql如下:

    explain
    select t1.* from test1 t1
    inner join (select max(id) mid from test1 group by id) t2
    on t1.id=t2.mid

    结果:

    explain | 索引优化的这把绝世好剑,你真的会用吗?

    最后一条记录就是衍生表,它一般是FROM列表中包含的子查询,这里是sql中的分组子查询。


  4. UNION 和 UNION RESULT

    执行sql如下:

    explain
    select * from test1
    union
    select* from test2

    结果:

    explain | 索引优化的这把绝世好剑,你真的会用吗?

     test2表是UNION关键字之后的查询,所以被标记为UNION,test1是最主要的表,被标记为PRIMARY。而表示id=1和id=2的表union,其结果被标记为UNION RESULT。


UNION 和 UNION RESULT一般会成对出现。


此外,回答上面的问题:id列的值允许为空吗?


如果仔细看上面那张图,会发现id列是可以允许为空的,并且是在SELECT类型为: UNION RESULT的时候。


table列

该列的值表示输出行所引用的表的名称,比如前面的:test1、test2等。

但也可以是以下值之一:

  • :具有和id值的行的M并集N。
  • :用于与该行的派生表结果id的值N。派生表可能来自(例如)FROM子句中的子查询 。
  • :子查询的结果,其id值为N

partitions列

该列的值表示查询将从中匹配记录的分区

type列

该列的值表示连接类型,是查看索引执行情况的一个重要指标。包含如下类型:

explain | 索引优化的这把绝世好剑,你真的会用吗?

执行结果从最好到最坏的的顺序是从上到下。

我们需要重点掌握的是下面几种类型:

system > const > eq_ref > ref > range > index > ALL

在演示之前,先说明一下test2表中只有一条数据:

explain | 索引优化的这把绝世好剑,你真的会用吗?

并且code字段上面建了一个普通索引:

explain | 索引优化的这把绝世好剑,你真的会用吗?


下面逐一看看常见的几个连接类型是怎么出现的:

  1. system

    这种类型要求数据库表中只有一条数据,是const类型的一个特例,一般情况下是不会出现的。


  2. const

    通过一次索引就能找到数据,一般用于主键或唯一索引作为条件的查询sql中,执行sql如下:

    explain select * from test2 where id=1;

    结果:

    explain | 索引优化的这把绝世好剑,你真的会用吗?

  3. eq_ref

    常用于主键或唯一索引扫描。执行sql如下:

    explain select * from test2 t1 inner join test2 t2 on t1.id=t2.id;

    结果:

    explain | 索引优化的这把绝世好剑,你真的会用吗?


    此时,有人可能感到不解,const和eq_ref都是对主键或唯一索引的扫描,有什么区别?

     

     答:const只索引一次,而eq_ref主键和主键匹配,由于表中有多条数据,一般情况下要索引多次,才能全部匹配上。


  4. ref

    常用于非主键和唯一索引扫描。执行sql如下:

    explain select * from test2 where code = '001';

    结果:

    explain | 索引优化的这把绝世好剑,你真的会用吗?


  5. range

    常用于范围查询,比如:between ... and 或 In 等操作,执行sql如下:

    explain select * from test2 where id between 1 and 2;

    结果:


    explain | 索引优化的这把绝世好剑,你真的会用吗?


  6. index

    全索引扫描。执行sql如下

    explain select code from test2;

    结果:

    explain | 索引优化的这把绝世好剑,你真的会用吗?

  7. ALL

    全表扫描。执行sql如下

    explain select *  from test2;

    结果:


    explain | 索引优化的这把绝世好剑,你真的会用吗?

 

possible_keys列

该列表示可能的索引选择。


请注意,此列完全独立于表的顺序,这就意味着possible_keys在实践中,某些键可能无法与生成的表顺序一起使用。

explain | 索引优化的这把绝世好剑,你真的会用吗?

如果此列是NULL,则没有相关的索引。在这种情况下,您可以通过检查该WHERE 子句以检查它是否引用了某些适合索引的列,从而提高查询性能。


key列

该列表示实际用到的索引。

可能会出现possible_keys列为NULL,但是key不为NULL的情况。


演示之前,先看看test1表结构:

explain | 索引优化的这把绝世好剑,你真的会用吗?


test1表中数据:

explain | 索引优化的这把绝世好剑,你真的会用吗?


使用的索引:

explain | 索引优化的这把绝世好剑,你真的会用吗?


code和name字段使用了联合索引。

执行sql如下

explain select code  from test1;

结果:

explain | 索引优化的这把绝世好剑,你真的会用吗?


这条sql预计没有使用索引,但是实际上使用了全索引扫描方式的索引。

key_len列

该列表示使用索引的长度。上面的key列可以看出有没有使用索引,key_len列则可以更进一步看出索引使用是否充分。不出意外的话,它是最重要的列。

explain | 索引优化的这把绝世好剑,你真的会用吗?


有个关键的问题浮出水面:key_len是如何计算的?


决定key_len值的三个因素

  1.字符集

  2.长度

  3.是否为空 


常用的字符编码占用字节数量如下:

explain | 索引优化的这把绝世好剑,你真的会用吗?

目前我的数据库字符编码格式用的:UTF8占3个字节。


mysql常用字段占用字节数:

字段类型 占用字节数
char(n) n
varchar(n)  n + 2
tinyint 1
smallint 2
int 4
bigint 8
date 3
timestamp 4
datetime 8


此外,如果字段类型允许为空则加1个字节。


上图中的 184是怎么算的?


184 = 30 * 3 + 2 + 30 * 3 + 2


  再把test1表的code字段类型改成char,并且改成允许为空:

explain | 索引优化的这把绝世好剑,你真的会用吗?

执行sql如下

explain select code  from test1;

结果:

explain | 索引优化的这把绝世好剑,你真的会用吗?

怎么算的?

183 = 30 * 3 + 1 + 30 * 3 + 2


还有一个问题:为什么这列表示索引使用是否充分呢,还有使用不充分的情况?

执行sql如下

explain select code  from test1 where code='001';

结果:

explain | 索引优化的这把绝世好剑,你真的会用吗?

上图中使用了联合索引:idx_code_name,如果索引全匹配key_len应该是183,但实际上却是92,这就说明没有使用所有的索引,索引使用不充分。

ref列

该列表示索引命中的列或者常量。

执行sql如下

explain select *  from test1 t1 inner join test1 t2 on t1.id=t2.id where t1.code='001';

结果:

explain | 索引优化的这把绝世好剑,你真的会用吗?

我们看到表t1命中的索引是const(常量),而t2命中的索引是列sue库的t1表的id字段。

rows列

该列表示MySQL认为执行查询必须检查的行数。

explain | 索引优化的这把绝世好剑,你真的会用吗?

对于InnoDB表,此数字是估计值,可能并不总是准确的。

filtered列

该列表示按表条件过滤的表行的估计百分比。最大值为100,这表示未过滤行。值从100减小表示过滤量增加。

explain | 索引优化的这把绝世好剑,你真的会用吗?

rows显示了检查的估计行数,rows× filtered显示了与下表连接的行数。例如,如果 rows为1000且 filtered为50.00(50%),则与下表连接的行数为1000×50%= 500。

Extra列

该字段包含有关MySQL如何解析查询的其他信息,这列还是挺重要的,但是里面包含的值太多,就不一一介绍了,只列举几个常见的。

  1. Impossible WHERE

    表示WHERE后面的条件一直都是false,

    执行sql如下

    explain select code  from test1 where 'a' = 'b';

    结果:

    explain | 索引优化的这把绝世好剑,你真的会用吗?


  2. Using filesort

    表示按文件排序,一般是在指定的排序和索引排序不一致的情况才会出现。

    执行sql如下

    explain select code  from test1 order by name desc;

    结果:

    explain | 索引优化的这把绝世好剑,你真的会用吗?

    这里建立的是code和name的联合索引,顺序是code在前,name在后,这里直接按name降序,跟之前联合索引的顺序不一样。

  3. Using index

    表示是否用了覆盖索引,说白了它表示是否所有获取的列都走了索引。

    explain | 索引优化的这把绝世好剑,你真的会用吗?

    上面那个例子中其实就用到了:Using index,因为只返回一列code,它字段走了索引。

  4. Using temporary

    表示是否使用了临时表,一般多见于order by 和 group by语句。

    执行sql如下

    explain select name  from test1 group by name;

    结果:

    explain | 索引优化的这把绝世好剑,你真的会用吗?


  5. Using where

    表示使用了where条件过滤。

  6. Using join buffer

   表示是否使用连接缓冲。来自较早联接的表被部分读取到联接缓冲区中,然后从缓冲区中使用它们的行来与当前表执行联接。


索引优化的过程


   1.先用慢查询日志定位具体需要优化的sql


   2.使用explain执行计划查看索引使用情况


   3.重点关注:

       key(查看有没有使用索引)


       key_len(查看索引使用是否充分)


       type(查看索引类型)

 

       Extra(查看附加信息:排序、临时表、where条件为false等)


   一般情况下根据这4列就能找到索引问题。


   4.根据上1步找出的索引问题优化sql


   5.再回到第2步


特别推荐一个分享架构+算法的优质内容,还没关注的小伙伴,可以长按关注一下:

explain | 索引优化的这把绝世好剑,你真的会用吗?

explain | 索引优化的这把绝世好剑,你真的会用吗?

explain | 索引优化的这把绝世好剑,你真的会用吗?

长按订阅更多精彩▼

explain | 索引优化的这把绝世好剑,你真的会用吗?

如有收获,点个在看,诚挚感谢

免责声明:本文内容由21ic获得授权后发布,版权归原作者所有,本平台仅提供信息存储服务。文章仅代表作者个人观点,不代表本平台立场,如有问题,请联系我们,谢谢!

本站声明: 本文章由作者或相关机构授权发布,目的在于传递更多信息,并不代表本站赞同其观点,本站亦不保证或承诺内容真实性等。需要转载请联系该专栏作者,如若文章内容侵犯您的权益,请及时联系本站删除。
换一批
延伸阅读

LED驱动电源的输入包括高压工频交流(即市电)、低压直流、高压直流、低压高频交流(如电子变压器的输出)等。

关键字: 驱动电源

在工业自动化蓬勃发展的当下,工业电机作为核心动力设备,其驱动电源的性能直接关系到整个系统的稳定性和可靠性。其中,反电动势抑制与过流保护是驱动电源设计中至关重要的两个环节,集成化方案的设计成为提升电机驱动性能的关键。

关键字: 工业电机 驱动电源

LED 驱动电源作为 LED 照明系统的 “心脏”,其稳定性直接决定了整个照明设备的使用寿命。然而,在实际应用中,LED 驱动电源易损坏的问题却十分常见,不仅增加了维护成本,还影响了用户体验。要解决这一问题,需从设计、生...

关键字: 驱动电源 照明系统 散热

根据LED驱动电源的公式,电感内电流波动大小和电感值成反比,输出纹波和输出电容值成反比。所以加大电感值和输出电容值可以减小纹波。

关键字: LED 设计 驱动电源

电动汽车(EV)作为新能源汽车的重要代表,正逐渐成为全球汽车产业的重要发展方向。电动汽车的核心技术之一是电机驱动控制系统,而绝缘栅双极型晶体管(IGBT)作为电机驱动系统中的关键元件,其性能直接影响到电动汽车的动力性能和...

关键字: 电动汽车 新能源 驱动电源

在现代城市建设中,街道及停车场照明作为基础设施的重要组成部分,其质量和效率直接关系到城市的公共安全、居民生活质量和能源利用效率。随着科技的进步,高亮度白光发光二极管(LED)因其独特的优势逐渐取代传统光源,成为大功率区域...

关键字: 发光二极管 驱动电源 LED

LED通用照明设计工程师会遇到许多挑战,如功率密度、功率因数校正(PFC)、空间受限和可靠性等。

关键字: LED 驱动电源 功率因数校正

在LED照明技术日益普及的今天,LED驱动电源的电磁干扰(EMI)问题成为了一个不可忽视的挑战。电磁干扰不仅会影响LED灯具的正常工作,还可能对周围电子设备造成不利影响,甚至引发系统故障。因此,采取有效的硬件措施来解决L...

关键字: LED照明技术 电磁干扰 驱动电源

开关电源具有效率高的特性,而且开关电源的变压器体积比串联稳压型电源的要小得多,电源电路比较整洁,整机重量也有所下降,所以,现在的LED驱动电源

关键字: LED 驱动电源 开关电源

LED驱动电源是把电源供应转换为特定的电压电流以驱动LED发光的电压转换器,通常情况下:LED驱动电源的输入包括高压工频交流(即市电)、低压直流、高压直流、低压高频交流(如电子变压器的输出)等。

关键字: LED 隧道灯 驱动电源
关闭