SQL调优实战总结

前言

作为开发人员，我们免不了与sql打交道。有些sql可能在业务的最开始，执行是毫无问题的。但是随着业务量的提升以及业务复杂度的加深，可能之前的sql就会逐渐展现出疲惫之势了。这时就会面临sql调优。

那么调优到底如何调？不同的人有不同的姿势。可能大部分人首先想到的就是加索引。

在这里插入图片描述

没错，加索引是一种比较典型，也是一种比较廉价的手段。

但是，索引怎么加？加在哪里？加之后是否会对已有的其他sql产生影响？这些问题都是需要考虑的。

同时也应该意识到，索引是一把双刃剑，就像硬币的两面，加快查询速度的同时，也会拖慢我们插入删除的速度。

当然，除了加索引之外，在创造sql时，也可以借鉴一些成熟的经验总结，去预防一些问题。如遵循最左匹配的原则、select时指明具体字段、不建议使用函数、超过3张表不建议join等等。

但是实际上，不同业务场景面对的问题是不尽相同的。同一条sql在数据量、数据分布不同的情况下，其执行结果可能是截然不同的。当sql无法持续支撑业务继续发展时，我们就需要结合实际进行调优。

方法论

对于sql调优的整个过程，我有一套自己的方法论，可以大致分为以下四个阶段：

1.白盒分析

此阶段我们可以根据自己已有的知识积累和经验，猜想sql可能慢的原因。

2.执行计划解读

通过explain结果，解读并模拟出mysql服务器对sql的真实执行过程。

3.瓶颈点确定

白盒分析+执行计划解读之后，基本可以确定sql可能慢的原因。

4.对症下药

找到瓶颈点后，逐个击破。

接下来，我就通过两个实例（单表查询和连接查询）与大家一起交流下调优的一些心得。

以下的调优过程主要是站在sql层面进行优化。并非站在整个系统方面，如分表、切换存储媒介等。

实战一

主人公sql为单表查询，其执行时间可达到2.3s。

select
  很多字段
from
  t_voucher_header
where
  period_year = 2020
  and period_month = 10
  and user_je_source_name = 'xxx'
  and `status` in (10, 30, 50)
  and employee_number != '1000'
  and can_auto_push = 1
  and is_delete = 0
  and batch_id > xxx
limit
  200

sql背景

全表数据量在700w+，batch_id为主键字段。

已存在的相关索引为：

联合索引1：user_je_source_name、voucher_category、record_type、company_code、status 联合索引2：period_year、period_month、user_je_source_name、voucher_category、is_delete、company_code、status 联合索引3：period_year、period_month、user_je_source_name、status

白盒分析

对于单表查询比较简单，主要有两点可以考量。

1.单表查询，是否用到了索引？

2.单表查询，是否用对了索引？

执行计划解读

在这里插入图片描述

以上我们可以读到这些信息：

1.索引使用长度较低，仅仅使用了联合索引的第一个字段来加快查找速度。

2.Using index condition是在5.6之后引入的新特性，索引条件下推。出现情况是在搜索条件中虽出现了索引列，却并不能用到（前缀匹配嘛）。但是由于索引中包含了搜索条件，因此可利用索引进行过滤。以本sql看，查询条件为period_year、period_month、user_je_source_name、status、employee_number、can_auto_push和is_delete。最终走到的索引是user_je_source_name、voucher_category、record_type、company_code、status。也就是说，利用了索引的user_je_source_name列，找到对应记录后，利用索引的status和batch_id（batch_id是主键）先进行过滤下，然后得到满足条件的主键id，再根据主键id到一级索引进行查询。

3.Using where与Using index condition一同出现，表示按照主键id查询到数据返回给mysql服务器之后，会按照剩余条件进行过滤。以本sql来看，在Using index condition阶段对条件user_je_source_name和status进行了条件匹配，剩下的period_year、period_month、employee_number、can_auto_push和is_delete则是Using where阶段由mysql服务器进行过滤的。

如此，此sql的执行过程在我们脑海中有了一个大致的轮廓，如下：在这里插入图片描述

瓶颈点确定

单表查询sql的执行，大体上可以分为两步。“二级索引find”和“回表select”。

“二级索引find”。

有两层含义，一方面是利用二级索引去找，另一方面是利用索引进行过滤（即索引条件下推）。

因此正确利用索引也有两层含义，一是利用的索引长度越多越好，二是经过索引find之后，能过滤掉的数据越多越好。

“回表select”。

因为二级索引毕竟包含的字段列有限，如果我们select的字段不能被索引全包含，在“二级索引find”结束后，根据得到的主键id，需要去一级索引上查询所需的列。这个过程叫做回表。而“二级索引find”阶段得到的主键id并非有序的，意味着回表是一个随机IO的过程，也就注定了回表是一个成本较高的操作。这也就是为什么有时候mysql宁愿全表扫描也不愿走索引的原因。

索引条件下推的出现就是为了节省回表的成本。

当然，如果我们最终select出来的字段能够被二级索引完全包含，“二级索引find”阶段之后，就不需要进行回表操作，这就是索引覆盖。当出现索引覆盖时，执行计划中Extra列将会通过Using index告诉我们。

回到我们的sql中，表中明明存在与查询条件极度匹配的联合索引（period_year、period_month、user_je_source_name、status），但是mysql却不用，这说明了什么？

说明了period_year、period_month、user_je_source_name、status这些条件并不具备很高的区分度，真正具有区分度的其实是在employee_number、can_auto_push和is_delete当中！

通过控制变量法，对employee_number、can_auto_push和is_delete取不同值时的数据量对比，发现当is_delete分别取0和1时，数据量差别极大。在这里插入图片描述

可以看到，对于is_delete字段，取值1时，数据量在50w+，取值0时，数据量在1000+。

而我们所要查询的正是is_delete=0的数据。

结合我们第二阶段对执行计划的解读。最终可以确定此sql执行时间过长的瓶颈点就在回表。

该sql只想查询is_delete=0对应的1000+条数据，获取这些数据可能几次回表就能得到。但是通过“索引find”阶段，我们并不能对is_delete字段进行过滤，导致“索引find”阶段会得到50w+的id，然后对这50w+的id进行回表。回表后通过Using where对is_delete字段进行过滤。

整个过程慢就慢在回表。

对症下药

以上一通分析，已经确定了瓶颈点。

即表所建立的索引没有能够高效地把数据在回表之前过滤掉，导致回表耗费了大量时间。因此我们在已有的period_year、period_month、user_je_source_name和status组成的联合索引之上，附加一列is_delete。这样，即能解决当前sql的问题，也不会对已有其他sql产生影响。

经过此番调整后，sql查询正确走到了period_year、period_month、user_je_source_name、status和is_delete索引，时间也从2.3秒降到了毫秒级，药到病除。

优化前后对比示意图：在这里插入图片描述

小结

该条sql的业务场景决定了最终需要查询的数据固定为is_delete=0的。那如果我们需要查询is_delete=1的数据，以上添加is_delete到既有索引列上的方法，并不能解决问题，因为无法减少对is_delete=1的50w+数据进行回表。在这里插入图片描述

那这时我们就要换个思考方向了，为何业务上会出现如此多被物理删除的数据（is_delete=1）?是偶发还是常态？如果是常态，是否我们的表结构设计出现了问题，需不需要拆表？等等。

而这也正是我想说的，sql调优=业务场景+sql执行分析，两者缺一不可。脱离任何一方去谈sql调优都是不可取的。

实战二

第二条主人公sql为：

SELECT
  b.很多字段
FROM
  t_voucher_line b
  INNER JOIN (
    SELECT
      batch_id batchId
    FROM
      t_voucher_header
    WHERE
      combine_batch_id = 2007044062
      AND is_delete = 0
  ) a ON b.batch_id = a.batchId
  AND b.is_delete = 0
WHERE
  b.segment3 LIKE '1002%'
  and b.id > 18496282
ORDER BY
  b.id ASC
LIMIT
  300

其执行时间在1s+。

这是一个涉及到多表的连接查询，在进行调优之前，想先简单说一下连接的基本原理。

连接查询的过程

连接本身是一个求笛卡尔积的过程。在这里插入图片描述

对于一个简单的连接查询sql：

SELECT * FROM t1, t2 WHERE t1.c1 > 1 AND t1.c1 = t2.c1 AND t2.c2 < 'd';

可将其条件分为

单表查询条件：t1.c1 > 1和 t2.c2 < 'd'

多表查询条件：t1.c1 = t2.c1

整个连接的过程大致分为以下：

1.确定驱动表，假定t1表为驱动表。应用驱动表t1的单表条件查询出满足条件的记录。在这里插入图片描述

2.针对步骤1所匹配到的记录，分别去被驱动表t2中查找数据。因为从驱动表t1中查找到了2条记录，因此会对被驱动表t2执行两次单表查询。涉及多表查询的条件t1.c1 = t2.c1此时登场。

t1.c=2时，t1.c1 = t2.c1等价于t2.c1=2，此时对t2表的单表查询条件为t2.c1=2，t2.c2 < 'd'

t1.c=3时，t1.c1 = t2.c1等价于t2.c1=3，此时对t2表的单表查询条件为t2.c1=3，t2.c2 < 'd'

在这里插入图片描述

3.将步骤2查询到的结果合并即为最终结果

对于连接查询来说，驱动表只会访问一次，被驱动表将会访问多次，具体由驱动表执行单表查询后的结果集中的记录条数决定。

以上属于比较直接的方法，遍历步骤1的结果，一次一次去对被驱动表进行查询（虽然查询被驱动表时可以利用被驱动表的索引加快），称之为嵌套循环连接（Nested-Loop Join）。

类似如下的伪代码

for each row in t1 {   #此处表示遍历满足对t1单表查询结果集中的每一条记录
    for each row in t2 {   #此处表示对于某条t1表的记录来说，遍历满足对t2单表查询结果集中的每一条记录       
            if row satisfies join conditions, send to client
        }
    }
}

假如从驱动表中读到了m条记录，那么对被驱动表的访问次数为m次。每一次访问被驱动表，都是多次地从硬盘中读取数据页。若被驱动表被分为了n个数据页，那么对于被驱动表的访问，共需要经历m*n次数据页读取，每一次的数据页读取都是IO操作，这在数据量较大情况下是极度耗时的。

基于嵌套循环连接，Mysql对其进行了横向优化，提出了join buffer的概念。

在这里插入图片描述

利用join buffer可以一次将从驱动表得到的多条记录与被驱动表进行连接，这样便可减少被驱动表的访问次数。这种方式称之为基于块的嵌套连接（Block Nested-Loop Join）。如果join buffer足够大，大到能够容纳从驱动表得到的所有记录，这样对被驱动表的访问只需一次即可。

当然纵向扩展，还有其他连接方式，比如合并连接(Merge Join)，哈希连接(Hash Join)。他们各有各的用武之地。

嵌套循环连接适用于，外层循环小，内存循环条件列有序，这种方方式对于CPU和内存的要求较低，但是对于IO要求很高。合并连接比较适用于连接两端都有序的场景，（有点归并排序的味道），对于CPU和内存要求一般，IO的要求也相对较低。哈希连接比较适用于数据量大，且没有索引的情况，对于CPU和内存要求都比较高，对于IO的要求可能高也可能低。

基于以上分析，继续按照既定的方法论对该条sql开始进行优化。

sql背景

t_voucher_header表在第一条sql优化时已经介绍过了。

t_voucher_line表数据量在1700w+，id为主键。

已存在的相关索引为：以batch_id列所建立的索引。t_voucher_header和t_voucher_line通过batch_id字段进行关联，一条t_voucher_header记录对应多条t_voucher_line记录。

白盒分析

1.sql中出现了子查询，随之带来的就是临时表的消耗。子查询和连接查询的替代关系是，连接查询一定能用子查询替代，子查询不一定能用连接查询替代。对于该条sql，是否能用连接查询替换掉子查询？

2.对于t_voucher_header的查询，涉及到combine_batch_id和is_delete，连接时利用batch_id（主键）字段。是否有包含combine_batch_id和is_delete的索引，从而减少回表？

3.select出来的字段均来自此b表，对b的回表不可避免。对于t_voucher_line的查询，涉及到is_delete、segment3、id（主键），连接时利用了batch_id字段。连接字段batch_id是否具备能够被索引到的条件？查询是否能有效利用到索引？是否能尽量全覆盖查询字段？

4.驱动表和被驱动表的选择是怎样的？

5.order by排序操作能否利用到索引的顺序，从而避免大数据量的排序耗时？

执行计划解读

带着以上的分析，看一下MySQL的执行计划在这里插入图片描述

1.t_voucher_header驱动表，t_voucher_line被驱动表。

2.t_voucher_header的查询，Using Index表明查询条件和select字段都能命中索引覆盖，无需回表。

3.t_voucher_line的查询，Using index condition+Using where表明仅用索引进行部分过滤，可能存在多次回表，有可能是慢的原因。

4.t_voucher_header的查询，扫描到了10w+行，并全部返回。后续还有order by操作，Using temporary和Using filesort出现，说明用到临时表进行子查询的存储以及排序，这往往是耗时的操作，极有可能是此sql慢的原因。

5.执行计划中并未出现Using join buffer (Block Nested Loop)，说明对于被驱动表的查询可利用索引进行加速的。

瓶颈点确定

到此地步，基本可以模拟出mysql的执行过程，大致如下：

在这里插入图片描述

1.子查询占用了额外的临时表去存储，空间的开辟也是需要时间的。

2.访问被驱动表时的回表成本没有降到最低。

3.大数据量的排序很耗时。

对症下药

1.sql等价改写，子查询变连接查询

SELECT
    b.很多字段
FROM
    t_voucher_line b
    INNER JOIN t_voucher_header a
    ON b.batch_id = a.batch_id
WHERE
    a.combine_batch_id = 2007044062
    AND a.is_delete = 0
    AND b.is_delete = 0
    AND b.segment3 LIKE '1002%'
    AND b.id > 18496282
ORDER BY
    b.id ASC
    LIMIT 300

2.对于被驱动表t_voucher_line的查询，涉及到的字段比较少，除主键id外，有batch_id、segment3、is_delete字段，而目前已存在的索引idx_batch_id仅仅覆盖了batch_id。对于segment3、is_delete则必须通过回表进行判断。那第一步，我们先通过扩宽索引，把回表的多余消耗给避免了。将batch_id扩充到batch_id、segment3、is_delete。再次看看sql的执行计划在这里插入图片描述

没问题的，对于被驱动表t_voucher_line的查询，Using where已经不见了，即回表消耗已经降到最低，执行时间也降到了0.6s。

3.解决排序

对于连接查询出来的结果集，其是以驱动表的索引字段天然有序的。

此sql对驱动表的查询是通过combine_batch_id索引，对于combine_batch_id字段，其访问类型为const，而batch_id为驱动表的主键字段，因此，最终得到的结果集必然是依照batch_id有序的。而两表的连接正是通过batch_id。是否能够利用batch_id的有序性来避免排序呢？

由于业务上驱动表和被驱动表的连接字段是一对多的，这就导致我们无法利用驱动表的索引顺序进行排序。在这里插入图片描述

那么看起来似乎避免不了排序了？

回过头再看下该sql，通过（b.id > 18496282）+（ORDER BY b.id ASC）+（limit 300）可以判定，其排序的目的是为了通过（id>?）进行分页。既然无法利用驱动表batch_id的天然有序，不妨换一种分页方法。即通过（get 全量ids）+（select by id）的方式。最终的sql变为：

SELECT
    b.id
FROM
    t_voucher_line b
    INNER JOIN t_voucher_header a ON b.batch_id = a.batch_id
WHERE
    a.combine_batch_id = 2007044062
    AND a.is_delete = 0
    AND b.is_delete = 0
    AND b.segment3 LIKE '1002%'

同时对应的sql使用方式也同步修改。最终，sql也是从1s降到了毫秒级，药到病除。

当然此种方式我们需要关注的一个点是max_allowed_packet，它限定了服务端和客户端之间返回数据包的大小。以32MB为例，主键为bigint型，那么一次大约能返回3210241024/8≈419w个id。

小结

该sql的业务场景对应为跑批任务，我们通过（get 全量ids）+（select by id）的方式并无大碍，那如果对应的是前端分页查询呢？如仅需要查询具体某一页的数据时，（get 全量ids）+（select by id）的方式该如何适配？

还是之前的那句话，sql调优=业务场景+sql执行分析，两者缺一不可。脱离任何一方去谈sql调优都是不可取的。

总结

经过对以上两条sql的优化，也总结了一些经验，希望能够帮到大家:

1.sql的优化，如果可能，回表的消耗需要降到最低。

2.在进行连表查询时，如果需要排序操作，尽量按照驱动表的索引字段进行排序，因为其天然有序，可以避免大数据量下，mysql创建临时表去排序，这是非常耗资源的。

3.当遇到慢sql后，执行计划是我们进行优化的切入点，我们能清楚了解整个sql的具体执行过程，就能从中找到瓶颈点，从而对症下药。

4.彼时的调优可以解决彼时的问题，随着业务的发展，数据量会上来，数据分布也会变得更加不均匀，待到那时，可能会再次面临调优。

5.在写sql时可以遵从一些成熟的经验总结，提前避免一些问题。但是系统总是向前发展的，不会说可以用一条sql应对各种场景，调优是个持续的过程。

6.细节很重要，单条sql执行时间过慢的原因可能是多个细节累加造成。10个0.1s就是1s。