(十七)SQL优化篇:如何成为一位写优质SQL语句的绝顶高手!

21,959 阅读45分钟

引言

本文为掘金社区首发签约文章,14天内禁止转载,14天后未获授权禁止转载,侵权必究!

   (Structured Query Language)标准结构化查询语言简称SQL,编写SQL语句是每位后端开发日常职责中,接触最多的一项工作,SQL是关系型数据库诞生的产物,无论是什么数据库,MySQL、Oracle、SQL Server、DB2、PgSQL....,只要还处于关系型数据库这个范畴,都会遵循统一的SQL标准,这句话简单来说也就是:无论什么关系型数据库,本质上SQL的语法都是相同的,因为它们都实现了相同的SQL标准,不同数据库之间仅支持的特性不同而已

   写SQL语句不难,稍微系统学习过数据库相关技术的人都能做到,但想要写好SQL却也不是一件易事,在大多数编写SQL的时候,很多人都是以实现需求为原则去撰写的,当一条SQL写出来之后,只要能满足业务需求就行,不会考虑它有没有优化点,能不能让它跑的更快。

而所谓的SQL优化,就是指将一条SQL写的更加简洁,让SQL的执行速度更快,易读性与维护性更好。

但要记住!SQL优化是建立在不影响业务的前提之上的,毕竟技术是为业务提供服务,如果为了提高执行效率,把SQL改成了不符合业务需求的样子,这是不行的,这就好比一个流行的梗:

  • 记者:你有什么特长吗?
  • 路人:我心算特别快!
  • 记者:哪请问565848654224 * 415414141 / 5145 + 44456 - 6644546 = ?
  • 路人:51354545452314
  • 记者:(拿出计算器,算了一下)你这算的不对啊。
  • 路人:对啊,我也知道不对,但你就说快不快吧!

从这个经典的网络流行梗中,就能看出一个问题,如果一件事违背了初衷,就算再好也无济于事,比如心算特别快,但如果算的不准,再快也没意义,这个道理放在SQL优化中亦是同理,优化一定要建立在不违背业务需求的情况下进行

一、编写SQL的基本功

   对于简单的SQL语句编写工作,相信这点对于每位略有经验的程序员都是手到拈来的事情,但往往实际业务场景中,咱们需要编写一些逻辑较为复杂的SQL语句,有可能涉及很多表、很多字段的复杂运算,这时编写SQL时就会出现“卡壳”情况,包括我在内也不例外,日常开发中也会遇到这类情况。

那当遇到“卡壳”情况时,该如何处理才好呢?很多人在这种情况下,首先会试图在网上查找是否有类似业务的实现可参考,如果没有的情况下,会选择去问身边的同事或技术Leader,或者也会去技术交流群问问潜水大佬。但这种方式都属于借助外力来解决问题,一旦外力也无法提供帮助时,“卡壳情况”就会演变为“死机情况”,彻底的陷入僵局,最终导致项目进度无法继续推进。

在这里我教大家一个比较实用的SQL编写技巧,即:拆解业务需求,先以定值推导SQL。学习过算法的小伙伴应该知道有一种算法思想叫做分而治之,也包括之前聊《并发编程-ForkJoin线程池》时,该线程池就是分治思想的落地产物,当一个任务较为庞大且复杂时,在ForkJoin内部会对任务进行拆分,然后分别执行拆分后的小任务,最终将所有小任务结果合并,最终得出大任务的执行结果。

我所谓的SQL编写技巧亦是如此,面对一个较为复杂或较难实现的业务需求时,就可以按照需求进行逐步拆分,化繁为简后逐步实现。其实对于这个道理很多人都懂,但往往在实际编写SQL时却想着一步到位,这也是我接触很多程序员后发现的问题:经验尚未丰富的开发,面对一个需求时通常都想着从头写到尾。但这样写就很容易卡壳,对于简单的业务需求可以这样做,但面对复杂业务时一定要先拆解需求后再逐步实现。

同时前面还提到一句:先以定值推导SQL,这是啥意思呢?因为有些情况下,一个查询条件会依赖于另一条SQL的执行结果来决定,很多人在这种情况下会直接组合起来一起写,但这会导致编写SQL的复杂度再次提升,因此在这种情况下,可以先用指定值作为条件去查询,例如xx = "xxx",后面等整体SQL完成后,再套入SQL

当然,说了这么多都是理论,在编程中有句话叫做:扎实的基础理论知识,会决定一个人水平飞得有多高,但能够将相应的理论用于实践,这才能真正体现出一个人的水平有多牛,只懂理论不懂实践,这无异于纸上谈兵,所以下面上一个简单的SQL练习题,实践一下上述的理论:

select * from zz_users;
+---------+-----------+----------+----------+---------------------+
| user_id | user_name | user_sex | password | register_time       |
+---------+-----------+----------+----------+---------------------+
|       1 | 熊猫      || 6666     | 2022-08-14 15:22:01 |
|       2 | 竹子      || 1234     | 2022-09-14 16:17:44 |
|       3 | 子竹      || 4321     | 2022-09-16 07:42:21 |
|       4 | 黑熊      || 8888     | 2022-09-17 23:48:29 |
|       8 | 猫熊      || 8888     | 2022-09-27 17:22:29 |
|       9 | 棕熊      || 0369     | 2022-10-17 23:48:29 |
+---------+-----------+----------+----------+---------------------+

上面是本次练习题会用到的一张用户表,需求如下:

  • 基于性别字段分组,然后ID排序,最后显示各组中的所有姓名,每个姓名之间用隔开。

这里大家可以先自己动手实操一下这个练习题,然后再看文章后面的解析。

这个需求看起来不太复杂,但如果直接开写也会令人有些许懵逼,所以先来拆解一下这个需求:

  • ①首先要基于性别分组,因此需要对user_sex字段使用group by关键字。
  • ②要对ID字段做排序,因此需要对user_id字段使用order by关键字。
  • ③将排序语句应用于分组查询的结果中,然后再根据user_id排序输出姓名。

拆解明白了需求之后,接下来逐步实现每个小需求:

-- ①首先要基于性别分组,因此需要对`user_sex`字段使用`group by`关键字。
select user_sex,user_id from `zz_users` group by user_sex;
+----------+---------+
| user_sex | user_id |
+----------+---------+
||       1 |
||       3 |
+----------+---------+

上述这条SQLMySQL5.x版本会得到如上结果,放在MySQL8.x版本则会报错,但不管是任何版本,似乎都未曾得到咱们需要的数据,因为现在我们想要的是先根据性别对user_id做分组,那此时需要用到一个新的函数来辅助实现该功能,即group_concat(),它可以给我们返回指定字段分组组合返回的结果,如下:

select 
    user_sex as "性别",
    convert(group_concat(user_id) using utf8) as "ID"
from 
    `zz_users` group by user_sex;
-- 执行结果如下:
+------+---------+
| 性别 |  ID     |
+------+---------+
||     1,8 |
|| 2,3,4,9 |
+------+---------+

OK,在上面就基于性别实现了ID分组,接着是需要对ID做排序工作,排序其实比较简单,大家应该都学习过order by关键字,如下:

-- ②要对`ID`字段做排序,因此需要对`user_id`字段使用`order by`关键字。
select user_id from zz_users order by user_id desc;
+---------+
| user_id |
+---------+
|       9 |
|       8 |
|       4 |
|       3 |
|       2 |
|       1 |
+---------+

这个效果很容易理解,但问题在于如何套入到之前的分组语句中呢?这里会令人有些费脑,其实很简单,如下:

select 
    user_sex as "性别",
    convert(
        group_concat(user_id order by user_id desc separator ",") 
    using utf8) as "ID" 
from `zz_users` group by user_sex;
-- 执行结果如下:
+------+---------+
| 性别 |  ID     |
+------+---------+
||     8,1 |
|| 9,4,3,2 |
+------+---------+

直接把order by语句套入到group_concat()函数中即可,最后声明一下各个值之间的分隔符即可,到这一步为止已经实现了ID分组排序工作,接着是需要按照排序好的ID,将对应的姓名按顺序显示出来,在这里第一时间有小伙伴可能想到的是嵌套子查询,使用in来做,如下:

select user_name from zz_users where user_id in (8,1);
+-----------+
| user_name |
+-----------+
| 熊猫      |
| 猫熊      |
+-----------+

然后对两个不同的ID分组,分别in一次,然后使用union合并结果,再一次做分组,这样也可以,但实际上会复杂很多很多,其实实现远远没有那么复杂,只需要基于之前的SQL,换个字段即可,如下:

③将排序语句应用于分组查询的结果中,然后再根据`user_id`排序输出姓名。
select 
    user_sex as "性别",
    convert(
        group_concat(user_name order by user_id desc separator ",") 
    using utf8) as "姓名" 
from `zz_users` group by user_sex;
-- 执行结果如下:
+------+------------------------+
| 性别 |          姓名          |
+------+------------------------+
|| 猫熊,熊猫             |
|| 棕熊,黑熊,子竹,竹子 |
+------+------------------------+

此时一步步的推敲,就达到了最开始的需求:“基于性别字段分组,然后ID排序,最后显示各组中的所有姓名,每个姓名之间用隔开”:
对比
同时也可以根据上图中的完整数据,来对比看看查询出的是否正确,观察后会发现没有任何问题!

上面经过一个例子的熏陶后,咱们逐步拆解了需求,并且套入了实现,最终会发现编写SQL的过程异常顺利,这还仅仅只是一些简单的需求拆解,当业务需求越发复杂时,这套拆解法的作用越大,所以拆解实现法也是写SQL的一大基本功。

二、SQL优化的小技巧

   前面聊了一些写SQL的基本功后,接着来聊一聊本文的核心:SQL优化,所谓的高手和普通人之间,最大的不同在于能将相同的事情做到更好,比如送外卖,相同的时间内一个人能够送的更多,这是个送外卖的高手。比如玩游戏,相同的角色和装备,一个人的战绩能够更出色,那这是个打游戏的高手......。

上述的道理放在编程中同样适用,一个人代码敲得更快、代码敲的更多、执行效率越高,这也可以被称为是一个写代码的高手,俗称“码农Pro Max”,那作为一个普通码农,如何达到“码农Pro、码农Plus、码农Pro Max.....”的境界呢?首先你得能够写出一手好SQL

掌握了写SQL的基本功后,足以让你写代码的效率提升,但引言中就聊到过:写的快不代表写的好,就算你能够日码三万行,并且还能满足业务需求,这也不见得的能被称之为高手,真正的SQL高手除开编写效率够高之外,对于每条SQL的执行效率也要可控。如果写的多,但有些业务SQL在大数据的情况下,一跑就是十多秒,这是万万不可的!

那么问题又来了:如何让自己的SQL又快又好呢?答案其实非常简单,减小查询的数据量、提升SQL的索引命中率即可,接着先来说说撰写SQL时的一些注意点。

2.1、编写SQL时的注意点

   在写SQL的时候,往往很多时候的细节不注意,就有可能导致索引失效,也因此会造成额外的资源开销,而我们要做的就是避开一些误区,确保自己的SQL在执行过程中能够最大程度上节省资源、缩短执行时间,下面罗列一些经典的SQL注意点。

2.1.1、查询时尽量不要使用*

一般在写SQL为了方便,所以通常会采用*来代替所有字段,毕竟用*号只要按键盘一下,写字段则需要一个个字段名去写。写*的确能让程序员更省力,但对机器就不太友好了,因此在写查询语句时一律不要使用*代替所有字段,这条准则相信大家都知道,但到底是为什么呢?

其实主要有如下几方面的原因:

  • ①分析成本变高。

《SQL执行篇》中聊过,一条SQL在执行前都会经过分析器解析,当使用*时,解析器需要先去解析出当前要查询的表上*表示哪些字段,因此会额外增加解析成本。但如果明确写出了查询字段,分析器则不会有这一步解析*的开销。

  • ②网络开销变大。

当使用*时,查询时每条数据会返回所有字段值,然后这些查询出的数据会先被放到结果集中,最终查询完成后会统一返回给客户端,但线上Java程序和MySQL都是分机器部署的,所以返回数据时需要经过网络传输,而由于返回的是所有字段数据,因此网络数据包的体积就会变大,从而导致占用的网络带宽变高,影响数据传输的性能和资源开销。但实际上可能仅需要用到其中的某几个字段值,所以写清楚字段后查询,能让网络数据包体积变小,从而减小资源消耗、提升响应速度。

  • ③内存占用变高。

《MySQL内存篇》中曾详细讲到了InnoDB引擎的工作原理,当查询一条数据时都会将其结果集放入到BufferPool的数据缓冲页中,如果每次用*来查询数据,查到的结果集自然会更大,占用的内存也会越大,单个结果集的数据越大,整个内存缓冲池中能存下的数据也就越少,当其他SQL操作时,在内存中找不到数据,又会去触发磁盘IO,最终导致MySQL整体性能下降。

  • ④维护性变差。

用过MyBatis框架的小伙伴应该都知道一点,一般为了对应查询结果与实体对象的关系,通常都需要配置resultMap来声明表字段和对象属性的映射关系,但如果每次使用*来查询数据,当表结构发生变更时,就算变更的字段结构在当前业务中用不到,也需要去维护已经配置好的resultMap,所以会导致维护性变差。但声明了需要的字段时,配置的resultMap和查询字段相同,因此当变更的表结构不会影响当前业务时,也无需变更当前的resultMap

综上所述,使用*的情况下反而会带来一系列弊端,所以能显示写明所需字段的情况下,尽量写明所需字段,除开上述原因外,还有一点最关键的原因:基于非主键字段查询可能会产生回表现象,如果是基于联合索引查询数据,需要的结果字段在联合索引中有时,可能通过索引覆盖原理去读数据,从而减少一次回表查询。但使用*查询所有字段数据时,由于联合索引中没有完整数据,因此只能做一次回表从聚簇索引中拿数据,对于索引覆盖感兴趣的可参考之前的《索引应用篇-索引覆盖机制》

2.1.2、连表查询时尽量不要关联太多表

对于这点的原因其实很简单,一旦关联太多的表,就会导致执行效率变慢,执行时间变长,原因如下:

  • 数据量会随表数量呈直线性增长,数据量越大检索效率越低。
  • 当关联的表数量过多时,无法控制好索引的匹配,涉及的表越多,索引不可控风险越大。

一般来说,交互型的业务中,关联的表数量应当控制在5张表之内,而后台型的业务由于不考虑用户体验感,有时候业务比较复杂,又需要关联十多张表做查询,此时可以这么干,但按照《高性能MySQL》上的推荐,最好也要控制在16~18张表之内(阿里开发规范中要求控制在3张表以内)。

2.1.3、多表查询时一定要以小驱大

所谓的以小驱大即是指用小的数据集去驱动大的数据集,说简单一点就是先查小表,再用小表的结果去大表中检索数据,其实在MySQL的优化器也会有驱动表的优化,当执行多表联查时,MySQL的关联算法为Nest Loop Join,该算法会依照驱动表的结果集作为循环基础数据,然后通过该结果集中一条条数据,作为过滤条件去下一个表中查询数据,最后合并结果得到最终数据集,MySQL优化器选择驱动表的逻辑如下:

  • ①如果指定了连接条件,满足查询条件的小数据表作为驱动表。
  • ②如果未指定连接条件,数据总行数少的表作为驱动表。

如果在做连表查询时,你不清楚具体用谁作为驱动表,哪张表去join哪张表,这时可以交给MySQL优化器自己选择,但有时候优化器不一定能够选择正确,因此写SQL时最好自己去选择驱动表,小表放前,大表放后!

举个例子感受一下两者之间的区别,假设zz_student学生表中有10000条数据,zz_class班级表中有100条数据,当需要关联这两张表查询数据时,SQL如下:

-- 大表在前,小表在后
select * from zz_student as s left join zz_class as c on s.class_id = c.class_id;
-- 小表在前,大表在后
select * from zz_class as c left join zz_student as s on c.class_id = s.class_id;

上述是两种联查的SQL语法,如果学生表在前作为驱动表,根据Nest Loop Join算法会循环一万次查询数据,而反之如果班级表在前,则只需要循环100次即可查询出数据,因此诸位在写SQL时一定要记得将小表作为驱动表。

这个道理不仅仅只存在于多表关联查询中,只要涉及到多表查询的情况,都需遵循该原则,比如使用子查询进行多表查询时,请确保结果集小的SQL先执行。

举个子查询的小表驱动大表的例子:

select * from xxx where yyy in (select yyy from zzz where ....);

MySQL在执行上述这条SQL时,会先去执行in后面的子查询语句,这时尽量要保证子查询的结果集小于in前面主查询的结果集,这样能够在一定程度上减少检索的数据量。通常使用in做子查询时,都要确保in的条件位于所有条件的最后面,这样能够在最大程度上减小多表查询的数据匹配量,如下:

- 优化前:select xxx,xxx,xxx from table where colum in(sql) and id = 10;
- 优化后:select xxx,xxx,xxx from table where id = 10 and colum in(sql);

以小驱大这个规则也可以进一步演化,也就是当查询多张表数据时,如果有多个字段可以连接查询,记得使用and来拼接多个联查条件,因为条件越精准,匹配的数据量就越少,查询速度自然会越快。

对于单表查询时也是如此,比如要对数据做分组过滤,可以先用where过滤掉一部分不需要的数据后,再对处理后的数据做分组排序,因为分组前的数据量越小,分组时的性能会更好!

可以把SQL当成一个链式处理器,每一次新的子查询、关联查询、条件处理....等情况时,都可以看成一道道的工序,我们在写SQL时要注意的是:在下一道工序开始前尽量缩小数据量,为下一道工序尽可能提供更加精准的数据。

2.1.4、不要使用like左模糊和全模糊查询

对于这点的原因十分明显,因为在之前《索引应用篇-索引失效场景》中聊到过,如若like关键字以%号开头会导致索引失效,从而导致SQL触发全表查询,因此需要使用模糊查询时,千万要避免%xxx、%xxx%这两种情况出现,实在需要使用这两类模糊查询时,可以适当建立全文索引来代替,数据量较大时可以使用ES、Solr....这类搜索引擎来代替。

2.1.5、查询时尽量不要对字段做空值判断

select * from xxx where yyy is null;
select * from xxx where yyy not is null;

当出现基于字段做空值判断的情况时,会导致索引失效,因为判断null的情况不会走索引,因此切记要避免这样的情况,一般在设计字段结构的时候,请使用not null来定义字段,同时如果想为空的字段,可以设计一个0、""这类空字符代替,一方面要查询空值时可通过查询空字符的方式走索引检索,同时也能避免MyBatis注入对象属性时触发空指针异常。

2.1.6、不要在条件查询=前对字段做任何运算

select * from zz_users where user_id * 2 = 8;
select * from zz_users where trim(user_name) = "熊猫";

zz_users用户表中user_id、user_name字段上都创建了索引,但上述这类情况都不会走索引,因为MySQL优化器在生成执行计划时,发现这些=前面涉及到了逻辑运算,因此就不会继续往下走了,会将具体的运算工作留到执行时完成,也正是由于优化器没有继续往下走,因此不会为运算完成后的字段选择索引,最终导致索引失效走全表查询。

从这里可以得出一点,千万不要在条件查询的=前,对字段做任何运算,包括了函数的使用也不允许,因为经过运算处理后的字段会变成一个具体的值,而并非字段了,所以压根无法使用到索引!

2.1.7、 !=、!<>、not in、not like、or...要慎用

这点可参考《索引应用篇-索引失效场景》中给出的示例,简单来说就是这类写法也可能导致索引失效,因此在实际过程中可以使用其他的一些语法代替,比如or可以使用union all来代替:

select user_name from zz_users where user_id=1 or user_id=2;
-- 可以替换成:
select user_name from zz_users where user_id=1
union all
select user_name from zz_users where user_id=2;

虽然这样看起来SQL变长了,但实际情况中查询效率反而更高一些,因为后面的SQL可以走索引(对于其他的一些关键字也一样,可以使用走索引的SQL来代替这些关键字实现)。

2.1.8、必要情况下可以强制指定索引

在表中存在多个索引时,有些复杂SQL的情况下,或者在存储过程中,必要时可强制指定某条查询语句走某个索引,因为MySQL优化器面对存储过程、复杂SQL时并没有那么智能,有时可能选择的索引并不是最好的,这时我们可以通过force index,如下:

select * from zz_users force index(unite_index) where user_name = "熊猫";

这样就能够100%强制这条SQL走某个索引查询数据,但这种强制指定索引的方式,一定要建立在对索引结构足够熟悉的情况下,否则效果会适得其反。

2.1.10、避免频繁创建、销毁临时表

临时表是一种数据缓存,对于一些常用的查询结果可以为其建立临时表,这样后续要查询时可以直接基于临时表来获取数据,MySQL默认会在内存中开辟一块临时表数据的存放空间,所以走临时表查询数据是直接基于内存的,速度会比走磁盘检索快上很多倍。但一定要切记一点,只有对于经常查询的数据才对其建立临时表,不要盲目的去无限制创建,否则频繁的创建、销毁会对MySQL造成不小的负担。

2.1.11、尽量将大事务拆分为小事务执行

经过之前《MySQL事务机制》《MySQL锁机制》《MySQL事务与锁实现原理》这几章的学习后,咱们应该会知道:一个事务在执行事,如果其中包含了写操作,会先获取锁再执行,直到事务结束后MySQL才会释放锁。

而一个事务占有锁之后,会导致其他要操作相同数据的事务被阻塞,如果当一个事务比较大时,会导致一部分数据的锁定周期较长,在高并发情况下会引起大量事务出现阻塞,从而最终拖垮整个MySQL系统。

  • show status like 'innodb_log_waits';查看是否有大事务由于redo_log_buffer不足,而在等待写入日志。

大事务也会导致日志写入时出现阻塞,这种情况下会强制触发刷盘机制,大事务的日志需要阻塞到有足够的空间时,才能继续写入日志到缓冲区,这也可能会引起线上出现阻塞。

因此基于上述原因,在面对一个较大的事务时,能走异步处理的可以拆分成异步执行,能拆分成小事务的则拆成小事务,这样可以在很大程度上减小大事务引起的阻塞。

2.1.12、从业务设计层面减少大量数据返回的情况

之前在做项目开发时碰到过一些奇葩需求,就是要求一次性将所有数据全部返回,而后在前端去做筛选展现,这样做虽然也可以,但如果一次性返回的数据量过于巨大时,就会引起网络阻塞、内存占用过高、资源开销过大的各类问题出现,因此如果项目中存在这类业务,一定要记住拆分掉它,比如分批返回给客户端。

分批查询的方式也被称之为增量查询,每次基于上次返回数据的界限,再一次读取一批数据返回给客户端,这也就是经典的分页场景,通过分页的思想能够提升单次查询的速度,以及避免大数据量带来的一系列后患问题。

2.1.13、尽量避免深分页的情况出现

前面刚刚聊过分页,分页虽然比较好,但也依旧存在问题,也就是深分页问题,如下:

select xx,xx,xx from yyy limit 100000,10; 

上述这条SQL相当于查询第1W页数据,在MySQL的实际执行过程中,首先会查询出100010条数据,然后丢弃掉前面的10W条数据,将最后的10条数据返回,这个过程无异极其浪费资源。

哪面对于这种深分页的情况该如何处理呢?有两种情况。

如果查询出的结果集,存在递增且连续的字段,可以基于有序字段来进一步做筛选后再获取分页数据,如下:

select xx,xx,xx from yyy where 有序字段 >= nnn limit 10; 

也就是说这种分页方案是基于递增且连续字段来控制页数的,如下:

-- 第一页
select xx,xx,xx from yyy where 有序字段 >= 1 limit 10; 
-- 第二页
select xx,xx,xx from yyy where 有序字段 >= 11 limit 10; 
-- 第N页.....

-- 第10000页
select xx,xx,xx from yyy where 有序字段 >= 100001 limit 10; 

这种情况下,MySQL就会先按where条件筛选到数据之后,再获取前十条数据返回,甚至还可以通过between做优化:

select xx,xx,xx from yyy where 有序字段 between 1000000 and 1000010; 

这种方式就完全舍弃了limit关键字来实现分页,但这种方式仅适合于基于递增且连续字段分页。

那么例如搜索分页呢?这种分页情况是无序的,因为搜索到的数据可以位于表中的任意行,所以搜索出的数据中,就算存在有序字段,也不会是连续的,这该如何是好?这种情况下就只能在业务上限制深分页的情况出现了,以百度为例:
百度搜索分页
虽然搜索mysql关键字之后,显示大约搜索到了一亿条数据,但当咱们把分页往后拉就会发现,最大只能显示76页,当你再尝试往后翻页时就会看到一个提示:“限于网页篇幅,部分结果未予显示”。

上述百度的这个例子中,就从根源上隔绝了深分页的出现,毕竟你都没给用户提供接下来的分页按钮了,这时自然也就无法根据用户操作生成深分页的SQL

但上述这种思想仅局限于业务允许的情况下,以搜索为例,一般用户最多看前面30页,如果还未找到他需要的内容,基本上就会换个更精准的关键词重新搜索。

哪如果业务必须要求展现所有分页数据,此时又不存在递增的连续字段咋办?哪这种情况下要么选择之前哪种很慢的分页方式,要么就直接抛弃所有!每次随机十条数据出来给用户,如果不想重复的话,每次新的分页时,再对随机过的数据加个标识即可。

2.1.14、SQL务必要写完整,不要使用缩写法

很多开发者,包含我在内,往往都喜欢缩写语法,能够简写的绝不写全,比如:

-- 为字段取别名的简单写法
select user_name "姓名" from zz_users;
-- 为字段取别名的完整写法
select user_name as "姓名" from zz_users;

-- 内连表查询的简单写法
select * from1,表2... where1.字段 =2.字段 ...; 
-- 内连表查询的完整写法
select * from1 别名1 inner join2 别名2 on 别名1.字段 = 别名2.字段;

......

这类情况下还有很多,在写的时候为了图简单,都会将一些能简写的SQL就简写,但其实这种做法也略微有些问题,因为隐式的这种写法,在MySQL底层都需要做一次转换,将其转换为完整的写法,因此简写的SQL会比完整的SQL多一步转化过程,如果你考虑极致程度的优化,也切记将SQL写成完整的语法。

2.1.15、基于联合索引查询时请务必确保字段的顺序性

在之前聊到过《联合索引的最左前缀原则》,想要基于建立的联合索引查询数据,就必须要按照索引字段的顺序去查询数据,否则可能导致所以完全利用联合索引,虽然MySQL8.0版本中推出了《索引跳跃扫描机制》,但这种方案也会存在较大的开销,同时还有很强的局限性,所以最好在写SQL时,依旧遵循索引的最左前缀原则撰写。

2.1.16、客户端的一些操作可以批量化完成

批量新增某些数据、批量修改某些数据的状态.....,这类需求在一个项目中也比较常见,一般的做法如下:

for (xxObject obj : xxObjs) {
    xxDao.insert(obj);
}

/**
 * xxDao.insert(obj)对应的SQL如下:
 * insert into tb_xxx values(......);
**/

这种情况确实可以实现批量插入的效果,但是每次都需要往MySQL发送SQL语句,这其中自然会带来额外的网络开销以及耗时,因此上述实现可以更改为如下:

xxDao.insertBatch(xxObjs);

/**
 * xxDao.insertBatch(xxObjs)对应的SQL如下:
 * insert into tb_xxx values(......),(......),(......),(......),.....;
**/

这样会组合成一条SQL发送给MySQL执行,能够在很大程度上节省网络资源的开销,提升批量操作的执行效率。

这样的方式同样适用于修改场景,如果一个业务会出现批量修改的情况时,也切记不要用for循环来调用update语句对应的接口,而是应该再写一个update/replace语句的批量修改接口。

2.1.17、明确仅返回一条数据的语句可以使用limit 1

select * from zz_users where user_name = "竹子";
select * from zz_users where user_name = "竹子" limit 1;

上述这两条SQL语句都是根据姓名查询一条数据,但后者大多数情况下会比前者好,因为加上limit 1关键字后,当程序匹配到一条数据时就会停止扫描,如果不加的情况下会将所有数据都扫描一次。所以一般情况下,如果确定了只需要查询一条数据,就可以加上limit 1提升性能。

但在一些极端情况下,性能可能相差不大,比如要查询的数据位于表/索引文件的最后面,那么依旧会全部扫描一次。还有一种情况是基于主键/唯一索引字段查询数据时,由于这些字段值本身具备唯一性,因此MySQL在执行时,当匹配到第一个值时就会自动停止扫描,因此上述这个方案只适用于普通索引字段、或表中的普通字段。

2.2、SQL优化的业内标准

   评判任何一件事情到底有没有做好都会有标准,而SQL语句的执行时间也一样,业内也早有了相应的标准,相信大家一定都听说过下述这个用户体验原则:

客户端访问时,能够在1s内得到响应,用户会觉得系统响应很快,体验非常好。
客户端访问时,1~3秒内得到响应,处于可以接受的阶段,其体验感还算不错。
客户端访问时,需要等待3~5秒时才可响应,这是用户就感觉比较慢了,体验有点糟糕。
客户端访问时,一旦响应超过5秒,用户体验感特别糟糕,通常会选择离开或刷新重试。

上述这四条是用户体验感的四个等级,一般针对于C端业务而言,基本上都需要将接口响应速度控制到第二等级,即最差也要三秒内给用户返回响应,否则会导致体验感极差,从而让用户对产品留下不好的印象。

所谓的三秒原则通常是基于C端业务而言的,对于B端业务来说,通常用户的容忍度会高一些,也包括B端业务的业务逻辑会比C端更为复杂一些,所以可将响应速度控制到第三等级,也就是5s内能够得到响应。针对于一些特殊类型的业务,如后台计算型的业务,好比跑批对账、定时调度....等,这类因为本身业务就特殊,因此可不关注其响应速度。

回归前面的用户三秒体验原则,似乎三秒也不难做到对嘛?基本上SQL语句在1~3秒内都能执行完成呀,但请牢记:这个三秒并不能全部分配给SQL执行,为什么呢?因为用户感受到的响应速度会由多方面的耗时组成,如下:
接口响应时间
从上图观察中可得知,所谓给用户的响应时间其实会包含各方面的耗时,也就是这所有的过程加一块儿,必须要在1~3s内给出响应,而SQL耗时属于「系统耗时→数据操作耗时」这部分,因此留给SQL语句执行的时间最多只能有500ms,一般在用户量较大的门户网站中,甚至要求控制在10ms、30ms、50ms以内。

三、MySQL索引优化

   10~50ms听起来是个很难抵达的标准,但实际大部分走索引查询的语句基本上都能控制在该标准内,那又该如何判断一条SQL会不会走索引呢?这里需要使用一个工具:explain,下面一起来聊一聊。

3.1、explain分析工具

在之前的《索引应用篇》中曾简单聊到过ExPlain这个工具,它本身是MySQL自带的一个执行分析工具,可使用于select、insert、update、delete、repleace等语句上,需要使用时只需在SQL语句前加上一个explain关键字即可,然后MySQL会对应语句的执行计划列出,比如:
explain工具

上述这些字段在之前也简单提到过,但并未展开细聊,所以在这里就先对其中的每个字段做个全面详解(MySQL8.0版本中才有12个字段,MySQL5.x版本只有10个字段)。

3.1.1、id字段

这是执行计划的ID值,一条SQL语句可能会出现多步执行计划,所以会出现多个ID值,这个值越大,表示执行的优先级越高,同时还会出现四种情况:

  • ID相同:当出现多个ID相同的执行计划时,从上往下挨个执行。
  • ID不同时:按照ID值从大到小依次执行。
  • ID有相同又有不同:先从大到小依次执行,碰到相同ID时从上往下执行。
  • ID为空:ID=null时,会放在最后执行。

3.1.2、select_type字段

当前执行的select语句其具体的查询类型,有如下取值:

  • SIMPLE:简单的select查询语句,不包含union、子查询语句。
  • PRIMARYunion或子查询语句中,最外层的主select语句。
  • SUBQUEPY:包含在主select语句中的第一个子查询,如select ... xx = (select ...)
  • DERIVED:派生表,指包含在from中的子查询语句,如select ... from (select ...)
  • DEPENDENT SUBQUEPY:复杂SQL中的第一个select子查询(依赖于外部查询的结果集)。
  • UNCACHEABLE SUBQUERY:不缓存结果集的子查询语句。
  • UNION:多条语句通过union组成的查询中,第二个以及更后面的select语句。
  • UNION RESULTunion的结果集。
  • DEPENDENT UNION:含义同上,但是基于外部查询的结果集来查询的。
  • UNCACHEABLE UNION:含义同上,但查询出的结果集不会加入缓存。
  • MATERIALIZED:采用物化的方式执行的包含派生表的查询语句。

这个字段主要是说明当前查询语句所属的类型,以及在整条大的查询语句中,当前这个查询语句所属的位置。

3.1.3、table字段

表示当前这个执行计划是基于哪张表执行的,这里会写出表名,但有时候也不一定是物理磁盘中存在的表名,还有可能出现如下格式:

  • <derivenN>:基于id=N的查询结果集,进一步检索数据。
  • <unionM,N>:会出现在查询类型为UNION RESULT的计划中,表示结果由id=M,N...的查询组成。
  • <subqueryN>:基于id=N的子查询结果,进一步进行数据检索。
  • <tableName>:基于磁盘中已创建的某张表查询。

一句话总结就是:这个字段会写明,当前的这个执行计划会基于哪个数据集查询,有可能是物理表、有可能是子查询的结果、也有可能是其他查询生成的派生表。

3.1.4、partitions字段

这个字段在早版本的explain工具中不存在,这主要是用来显示分区的,因为后续版本的MySQL中支持表分区,该列的值表示检索数据的分区。

3.1.5、type字段

该字段表示当前语句执行的类型,可能出现的值如下:

  • all:全表扫描,基于表中所有的数据,逐行扫描并过滤符合条件的数据。
  • index:全索引扫描,和全表扫描类似,但这个是把索引树遍历一次,会比全表扫描要快。
  • range:基于索引字段进行范围查询,如between、<、>、in....等操作时出现的情况。
  • index_subquery:和上面含义相同,区别:这个是基于非主键、唯一索引字段进行in操作。
  • unique_subquery:执行基于主键索引字段,进行in操作的子查询语句会出现的情况。
  • index_merge:多条件查询时,组合使用多个索引来检索数据的情况。
  • ref_or_null:基于次级(非主键)索引做条件查询时,该索引字段允许为null出现的情况。
  • fulltext:基于全文索引字段,进行查询时出现的情况。
  • ref:基于非主键或非唯一索引字段查找数据时(即次级索引),会出现的情况。
  • eq_ref:连表查询时,基于主键、唯一索引字段匹配数据的情况,会出现多次索引查找。
  • const:通过索引一趟查找后就能获取到数据,基于唯一、主键索引字段查询数据时的情况。
  • system:表中只有一行数据,这是const的一种特例。
  • null:表中没有数据,无需经过任何数据检索,直接返回结果。

这个字段的值很重要,它决定了MySQL在执行一条SQL时,访问数据的方式,性能从好到坏依次为:

  • 完整的性能排序:null → system → const → eq_ref → ref → fulltext → ref_or_null → index_merge → unique_subquery → index_subquery → range → index → all
  • 常见的性能排序:system → const → eq_ref → ref → fulltext → range → index → all

一般在做索引优化时,一般都会要求最好优化到ref级别,至少也要到range级别,也就是最少也要基于次级索引来检索数据,不允许出现index、all这类全扫描的形式。

3.1.6、possible_keys字段

这个字段会显示当前执行计划,在执行过程中可能会用到哪些索引来检索数据,但要注意的一点是:可能会用到并不代表一定会用,在某些情况下,就算有索引可以使用,MySQL也有可能放弃走索引查询。

3.1.7、key字段

前面的possible_keys字段表示可能会用到的索引,而key这个字段则会显示具体使用的索引,一般情况下都会从possible_keys的值中,综合评判出一个性能最好的索引来进行查询,但也有两种情况会出现key=null的这个场景:

  • possible_keys有值,key为空:出现这种情况多半是由于表中数据不多,因此MySQL会放弃索引,选择走全表查询,也有可能是因为SQL导致索引失效。
  • possible_keys、key都为空:表示当前表中未建立索引、或查询语句中未使用索引字段检索数据。

默认情况下,possible_keys有值时都会从中选取一个索引,但这个选择的工作是由MySQL优化器自己决定的,如果你想让查询语句执行时走固定的索引,则可以通过force index、ignore index的方式强制指定。

3.1.8、key_len字段

这个表示对应的执行计划在执行时,使用到的索引字段长度,一般情况下都为索引字段的长度,但有三种情况例外:

  • 如果索引是前缀索引,这里则只会使用创建前缀索引时,声明的前N个字节来检索数据。
  • 如果是联合索引,这里只会显示当前SQL会用到的索引字段长度,可能不是全匹配的情况。
  • 如果一个索引字段的值允许为空,key_len的长度会为:索引字段长度+1

3.1.9、ref字段

显示索引查找过程中,查询时会用到的常量或字段:

  • const:如果显示这个,则代表目前是在基于主键字段值或数据库已有的常量(如null)查询数据。
    • select ... where 主键字段 = 主键值;
    • select ... where 索引字段 is null;
  • 显示具体的字段名:表示目前会基于该字段查询数据。
  • func:如果显示这个,则代表当与索引字段匹配的值是一个函数,如:
    • select ... where 索引字段 = 函数(值);

3.1.10、rows字段

这一列代表执行时,预计会扫描的行数,这个数字对于InnoDB表来说,其实有时并不够准确,但也具备很大的参考价值,如果这个值很大,在执行查询语句时,其效率必然很低,所以该值越小越好。

3.1.11、filtered字段

这个字段在早版本中也不存在,它是一个百分比值,意味着表中不会扫描的数据百分比,该值越小则表示执行时会扫描的数据量越大,取值范围是0.00~100.00

3.1.12、extra字段

该字段会包含MySQL执行查询语句时的一些其他信息,这个信息对索引调优而言比较重要,可以带来不小的参考价值,但这个字段会出现的值有很多种,如下:

  • Using index:表示目前的查询语句,使用了索引覆盖机制拿到了数据。
  • Using where:表示目前的查询语句无法从索引中获取数据,需要进一步做回表去拿表数据。
  • Using temporary:表示MySQL在执行查询时,会创建一张临时表来处理数据。
  • Using filesort:表示会以磁盘+内存完成排序工作,而完全加载数据到内存来完成排序。
  • Select tables optimized away:表示查询过程中,对于索引字段使用了聚合函数。
  • Using where;Using index:表示要返回的数据在索引中包含,但并不是索引的前导列,需要做回表获取数据。
  • NULL:表示查询的数据未被索引覆盖,但where条件中用到了主键,可以直接读取表数据。
  • Using index condition:和Using where类似,要返回的列未完全被索引覆盖,需要回表。
  • Using join buffer (Block Nested Loop):连接查询时驱动表不能有效的通过索引加快访问速度时,会使用join-buffer来加快访问速度,在内存中完成Loop匹配。
  • Impossible WHEREwhere后的条件永远不可能成立时提示的信息,如where 1!=1
  • Impossible WHERE noticed after reading const tables:基于唯一索引查询不存在的值时出现的提示。
  • const row not found:表中不存在数据时会返回的提示。
  • distinct:去重查询时,找到某个值的第一个值时,会将查找该值的工作从去重操作中移除。
  • Start temporary, End temporary:表示临时表用于DuplicateWeedout半连接策略,也就是用来进行semi-join去重。
  • Using MRR:表示执行查询时,使用了MRR机制读取数据。
  • Using index for skip scan:表示执行查询语句时,使用了索引跳跃扫描机制读取数据。
  • Using index for group-by:表示执行分组或去重工作时,可以基于某个索引处理。
  • FirstMatch:表示对子查询语句进行Semi-join优化策略。
  • No tables used:查询语句中不存在from子句时提示的信息,如desc table_name;
  • ......

除开上述内容外,具体的可参考《explain-Extra字段详解》,其中介绍了Extra字段可能会出现的所有值,最后基于Extra字段做个性能排序:

  • Using index → NULL → Using index condition → Using where → Using where;Using index → Using join buffer → Using filesort → Using MRR → Using index for skip scan → Using temporary → Strart temporary,End temporary → FirstMatch

上面这个排序中,仅列出了一些实际查询执行时的性能排序,对于一些不重要的就没有列出了。

3.2、索引优化参考项

   在上面咱们简单介绍了explain工具中的每个字段值,字段数量也比较多,但在做索引优化时,值得咱们参考的几个字段为:

  • key:如果该值为空,则表示未使用索引查询,此时需要调整SQL或建立索引。
  • type:这个字段决定了查询的类型,如果为index、all就需要进行优化。
  • rows:这个字段代表着查询时可能会扫描的数据行数,较大时也需要进行优化。
  • filtered:这个字段代表着查询时,表中不会扫描的数据行占比,较小时需要进行优化。
  • Extra:这个字段代表着查询时的具体情况,在某些情况下需要根据对应信息进行优化。

PS:在explain语句后面紧跟着show warings语句,可以得到优化后的查询语句,从而看出优化器优化了什么。

3.3、索引优化实践

   上面了解了索引优化时的一些参考项,接着来聊聊索引优化的实践,不过在优化之前要先搞清楚什么是索引优化,其实无非就两点:

  • SQL的写法进行优化,对于无法应用索引,或导致出现大数据量检索的语句,改为精准匹配的语句。
  • 对于合适的字段上建立索引,确保经常作为查询条件的字段,可以命中索引去检索数据。

总归说来说去,也就是要让SQL走索引执行,但要记住:并非走了索引就代表你的执行速度就快,因为如果扫描的索引数据过多,依旧可能会导致SQL执行比较耗时,所以也要参考type、rows、filtered三个字段的值,来看看一条语句执行时会扫描的数据量,判断SQL执行时是否扫描了额外的行记录,综合分析后需要进一步优化到更细粒度的检索。

索引优化其实本质上,也就是遵循前面第二阶段提出的SQL小技巧撰写语句,以及合理的使用与建立索引,对于索引怎么建立和使用才最好,具体可参考《索引应用篇-建立与使用索引的正确姿势》

一般来说,SQL写好了,索引建对了,基本上就已经优化到位了,对于一些无可避免的慢SQL执行,比如复杂SQL的执行、深分页等情况,要么就从业务层面着手解决,要么就接受一定的耗时,毕竟凡事不可能做到十全十美。

四、SQL优化篇总结

   到这里《SQL优化篇》又接近尾声了,其实所谓的SQL优化,本质上是改善SQL的写法,理解一些SQL导致索引失效的场景,以及撰写SQL时的一些技巧,就能写出一手优质SQL,当你写的所有语句执行效率都还不错,那你就能够被称得上是一位写SQL的高手。

不过做过SQL优化的小伙伴,其实应该能够发现这里还少写了一个十分重要的内容,也就是慢查询语句优化,这里是刻意为之,对于慢查询语句的优化,本质上脱离了SQL优化的范畴,更多属于线上问题的一种情况,有些SQL在开发环境中执行时,可能效率并不算低,但放到线上时可能会偶尔出现的执行缓慢的情况,因此对于这类SQL语句该如何排查呢?具体的方法会放到下篇文章:《MySQL线上排查篇》来详细阐述~