SQL优化篇：如何成为一位写优质SQL语句的绝顶高手！

我就是我 2023-10-12 20:18 14阅读 0赞

写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 |
    +----------+---------+

上述这条SQL在MySQL5.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 * from 表1,表2... where 表1.字段 = 表2.字段 ...; 
    -- 内连表查询的完整写法
    select * from 表1 别名1 inner join 表2 别名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会对应语句的执行计划列出，比如：

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

# 3.1MySQL如何使用索引 #

索引用于快速查找具有特定列值的行。如果没有索引，MySQL必须从第一行开始，然后遍历整个表以找到相关行。表越大，成本越高。如果表中有相关列的索引，MySQL可以快速确定在数据文件中间找到的位置，而不需要查看所有的数据。这比按顺序读取每一行要快得多。

大多数MySQL索引（主键、唯一、索引和全文）都存储在b-tree中。例外：空间数据类型的索引使用r-tree；内存表也支持散列索引。InnoDB对全文索引使用倒置的列表。

**MySQL使用索引来执行以下操作：**

①快速找到符合where子句的行。

②如果你可以在多个索引之间进行选择，MySQL通常使用找到最少行数的索引（最具选择性的索引）。

③如果有多个列索引（也称为 "复合索引 "或 "联合索引"），优化器可以使用索引的任何最左边的前缀来查找记录。例如，如果有一个关于（col1, col2, col3）的三列索引，就有一个关于（col1）、（col1, col2）和（col1, col2, col3）的索引搜索函数。

④当使用连接查询从其他表中检索记录时，如果MySQL声明了相同的类型和大小，它可以更有效地使用列上的索引。在这种情况下，如果varchar和char被声明为相同的大小，则被认为是相同的。

例如，varchar（10）和char（10）是相同的大小，但是varchar（10）和char（15）是不同的大小。

⑤对于非二进制字符串列之间的比较，这两列应该使用相同的字符集。

⑥如果在可用索引的最左边的前缀上进行排序或分组（例如，按key\_part1，key\_part2排序），则表被排序或分组。如果desc后面是所有的键部分，那么键将以相反的顺序被读取。

⑦在某些情况下，MySQL可以使用索引来满足order by子句，并避免执行文件排序操作时涉及的额外排序。

⑧在某些情况下，查询可以被优化，以检索值而不查询数据行。(为查询提供所有必要结果的索引被称为覆盖索引)如果查询只使用表中某些索引所包含的列，那么可以从索引树中检索所选的值，以提高速度。

最后，索引对于小表的查询并不重要。当查询需要访问大多数行时，顺序读取比处理索引要快。

# 3.2. 避免全表扫描 #

当MySQL使用全表扫描来解析查询时，解释的输出全部显示在类型列中。这通常发生在以下情况。

①表太小，执行全表扫描比索引查找快得多。这对于少于10行和行长较短的表来说是很常见的。

②索引列不用于on或where句子中。

③将索引列与常量值进行比较，MySQL已经计算出（基于索引树）常量覆盖了表的很大一部分，表的扫描会更快。

④你正在通过另一列使用一个具有低cardinality的键（许多行与键值相匹配）。在这种情况下，MySQL假定通过使用这个键，可以执行许多键的查找，表的扫描会更快。

对于小表，表扫描通常是合适的，对性能的影响可以忽略不计。

对于大表，你可以尝试以下技术来避免优化器错误地选择表扫描。

①使用分析TABLE tbl\_ Name来更新键的分布。

②使用强制索引来告诉MySQL，与使用给定的索引相比，表扫描非常昂贵。

# 3.3. 列索引 #

b-tree数据结构使索引能够快速找到特定的值、一组值或与where子句中的运算符（如=、>、≤、between、in等）相对应的一系列值。

每个存储引擎都定义了索引的最大数量和每个表的最大索引长度。所有的存储引擎都支持每个表至少有16个索引，而且索引的总长度至少为256字节。

# 3.3.1索引前缀 #

使用col\_ Name（n）可以创建一个只使用列的前n个字符的索引。在InnoDB表中，前缀的长度最多为767字节。

# 3.3.2全文索引 #

全文索引是用于全文搜索的。只有InnoDB和MyISAM存储引擎支持全文本索引，并且只支持char、varchar和text列。索引总是在整个列上，并且不支持列前缀索引。

# 3.3.3空间索引 #

是指根据空间对象的位置和形状或空间对象之间的某种空间关系，按一定顺序排列的数据结构。

# 3.3.4内存存储引擎上的索引 #

默认情况下，内存存储引擎使用哈希索引，但它也支持b-tree索引。

# 3.4. 多列索引 #

MySQL可以创建复合索引（即关于多列的索引）。一个索引最多可以包含16列。

假设有一张图片表示这个定义。

CREATE TABLE test (
      id INT NOT NULL,
      last_name CHAR(30) NOT NULL,
      first_name CHAR(30) NOT NULL,
      PRIMARY KEY (id),
      INDEX idx_name (last_name,first_name)
     );

idx\_ The name index是基于last\_ Name和first\_ name列上面的索引，它可以用来指定last\_ Name和first\_ name值组合的查询，也可以用来只指定last\_ Name值，因为该索引匹配最左边的前缀。

因此，idx\_ The name索引可以用于以下查询。

SELECT * FROM test WHERE last_name='Jones';
      
     SELECT * FROM test WHERE last_name='Jones' AND first_name='John';
      
     SELECT * FROM test WHERE last_name='Jones' AND (first_name='John' OR first_name='Jon');
      
     SELECT * FROM test WHERE last_name='Jones' AND first_name >='M' AND first_name < 'N';

然而，idx\_名字索引不能用于以下查询。

SELECT * FROM test WHERE first_name='John';
      
     SELECT * FROM test WHERE last_name='Jones' OR first_name='John';

考虑下面的SQL语句。

SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

如果col1和col2上有一个多列索引，你可以直接抓取相应的记录。如果col1和col2上有单独的单列索引，优化器会尝试使用索引合并优化，或者通过确定哪个索引需要排除更多的记录来找到限制性最强的索引，并使用该索引来获取记录。

如果表有一个多列索引，优化器可以使用索引的任何最左边的前缀来寻找行。例如，如果有一个三列索引（col1，col2，col3），那么在（col1），（col1，col2），（col1，col2，col3）上有一个索引搜索函数。

如果该列不构成索引的最左边的前缀，MySQL则不能使用索引执行查找。

看看下面的SQL语句。

SELECT * FROM tbl_name WHERE col1=val1;
    SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
     
    SELECT * FROM tbl_name WHERE col2=val2;
    SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

如果在(col1, col2, col3)上有一个复合索引，只有前两个查询将被使用。那么后两个查询将不会使用索引来执行查询，因为（col2）和（col2，col3）不是（col1，col2，col3）的最左边的前缀。

# 3.5. b-tree索引和哈希索引的比较 #

# 3.5.1b-tree索引的特点 #

b-tree索引可以在表达式中使用=, >, > =, <, < =, 操作符在列之间进行比较。如果like的参数是一个不以通配符开头的常数字符串，那么索引也可以用于like的比较。

下面的子句不使用索引。

/* LIKE值以通配符开始 */
    SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
    /* 该LIKE值不是一个常数 */
    SELECT * FROM tbl_name WHERE key_col LIKE other_col;

任何没有覆盖where子句中所有和级别的索引都不会被用来优化查询。换句话说，为了能够使用索引，必须在每个和组中使用索引的前缀。

下面的where子句使用了索引。

... WHERE index_part1=1 AND index_part2=2 AND other_column=3
     
     /* index = 1 OR index = 2 */
    ... WHERE index=1 OR A=10 AND index=2
     
     /* 经过优化 "index_part1='hello'" */
    ... WHERE index_part1='hello' AND index_part3=5
     
     /* 可以在index1上使用index，但不能在index2或index3上使用index*/
    ... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

下面的where子句不使用索引。

/* 未使用index_part1 */
    ... WHERE index_part2=1 AND index_part3=2
     
     /* 在WHERE子句的两个部分都不使用索引 */
    ... WHERE index=1 OR A=10
     
     /* 没有索引横跨所有的行 */
    ... WHERE index_part1=1 OR index_part2=10

有时，即使索引可用，MySQL也不使用索引。一个可能的原因是，优化器估计使用索引将需要访问表中很大一部分的行。(在这种情况下，表扫描可能会更快，因为它需要更少的查找)。

然而，如果这样的查询只使用限制来检索一些行，MySQL仍然使用索引，因为它可以更快地找到返回结果中的几行。

# 3.5.2哈希索引的特点 #

哈希索引有一些与刚才讨论的索引不同的特点。

①哈希索引只用于=或<=>运算符的等量比较（但非常快），而不用于查找数值范围的比较运算符。依靠这种单值查找的系统被称为 "键值对存储"。为了在这种应用中使用MySQL，请尽量使用哈希索引。

②优化器不能使用哈希索引来加速逐个操作。(哈希类型的索引不能用于搜索顺序的下一个条目)

③MySQL不能确定两个值之间有多少行。（范围优化器用它来决定使用哪个索引）

④只有整个键可以用来搜索行。(对于B树索引，键的任何最左边的前缀都可以用来搜索行)

# 3.5.3b-tree索引 #

b-tree数据结构在数据库索引中被广泛使用。该结构始终是有序的，因此你可以快速找到精确匹配（等于运算符）和范围（例如，大于、小于和介于运算符之间）。这种索引可用于大多数存储引擎，如InnoDB和MyISAM。

因为B-tree节点可以有很多子节点，所以B-tree与二叉树不同。后者的每个节点最多只能有两个子节点。

术语b-tree是用来指索引设计的一般类别。由于经典的b-tree设计没有复杂性，MySQL存储引擎使用的B-tree结构可以被看作是一种变体。

# 3.5.4哈希索引 #

一种为使用平等运算符而不是范围运算符的查询而设计的索引类型。它可以用于内存表。尽管由于历史原因，哈希索引是内存表的默认索引，但存储引擎也支持B-树索引，这通常是通用查询的更好选择。

# 3.6. 优化数据大小 #

设计表，使其在磁盘上占用最少的空间。这可以通过减少写入磁盘和从磁盘中读取的数据量而带来重大改进。较小的表通常需要较少的主内存来在查询执行期间处理其内容。表数据空间的任何减少都会导致索引变小，因此索引的处理速度可以更快。

MySQL支持许多不同的存储引擎（表类型）和行格式。对于每个表，你可以决定使用哪种存储和索引方法。为你的应用选择适当的表格式可以大大改善性能。

# 3.6.1表的列 #

①尽可能使用最有效的（最小的）数据类型。MySQL有许多特殊的类型，它们可以节省磁盘空间和内存。例如，如果可能的话，使用较小的整数类型，以获得一个较小的表。Mediumint通常比int好，因为Mediumint列使用的空间少25%。

②如果可能的话，声明该列不为空。它可以通过更好地使用索引和消除测试每个值是否为空的开销而使SQL操作更快。它还可以节省一些存储空间，每列一个比特。如果你真的需要表中的空值，就使用它们。只要避免默认设置，它允许每一列中的空值。

# 3.6.2行格式 #

为了通过以压缩的形式存储表数据来进一步减少空间，在创建InnoDB表时指定行\_ FORMAT=COMPRESSED。

# 6.3索引 #

**①表的主键索引应该尽可能的短。**

这使得识别每一行变得简单而有效。对于InnoDB表，主键列在每个二级索引条目中都是重复的，所以如果你有很多二级索引，一个较短的主键可以节省很多空间。

**②只创建需要提高查询性能的索引。**

索引非常适用于检索，但它会降低插入和更新操作的速度。如果你主要通过搜索列的组合来访问一个表，那么在表上创建一个单一的复合索引，而不是为每个列创建单独的索引。索引的第一部分应该是最常用的列。如果你在从表中进行查询时总是使用很多列，那么索引中的第一列应

**③该是重复次数最多的列，以便更好地压缩索引。**

如果是一个长的字符串列，它很可能在第一个字符上有一个唯一的前缀。在这种情况下，最好使用MySQL前缀进行索引（PS：只对前几个字符进行索引）。索引更短更快，不仅因为它们需要更少的磁盘空间，而且还因为它们增加了索引缓存中的命中率，从而减少了磁盘查找的次数。

# 3.6.4连接 #

①在具有相同数据类型的不同表中声明具有相同信息的列，以加快基于相应列的连接。

②保持列名简单，这样你就可以在不同的表中使用相同的名称，简化连接查询。

例如，在一个名为customer的表中，使用name列名，而不是customer\_ name。 为了使你的名字可以移植到其他SQL服务器，请考虑将名字长度限制在18个字符。

# 3.3.6.5正常化 #

一般来说，尽量保持所有数据的非冗余性（在数据库理论中称为第三种正常形式）。赋予它们唯一的ID，以取代重复和冗长的值，根据需要在多个小表中重复这些IDS，并在查询中通过在连接子句中引用IDS连接这些表。

# 7. 优化数据类型 #

# 3.7.1数值类型 #

使用数值而不是字符串来进行行的唯一标识比较好，因为大数值比相应的字符串占用的存储字节数少，所以它们的传输和比较速度更快，占用的内存更少。

# 3.7.2字符和字符串类型 #

当比较不同列的值时，尽可能用相同的字符集和排序来声明这些列，以避免运行查询时的字符串转换。

对于小于8KB的列值，使用二进制varchar而不是blob。group by和order by子句可以生成临时表，如果原始表不包含任何blob列，这些临时表可以使用内存存储引擎。

如果一个表包含字符串列，如姓名和地址，但许多查询并不检索这些列，你可以考虑将字符串列划分为独立的表，必要时使用带有外键的连接查询。

当MySQL从行中检索任何值时，它读取一个包含该行所有列（可能还有其他相邻行）的数据块。保持每一行的大小并只包含最常用的列，使每个数据块可以容纳更多的行。这种紧凑的表格减少了常见查询的磁盘i/o和内存使用。

当使用随机生成的值作为InnoDB表中的主键时，最好在它前面加上一个升序值，例如当前的日期和时间（如果可能的话）。当连续的主键值在物理上彼此相邻存储时，InnoDB可以更快地插入和检索它们。

好了，今天就分享这么多。

SQL优化篇：如何成为一位写优质SQL语句的绝顶高手！

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 SQL优化篇：如何成为一位写优质SQL语句的绝顶高手！

相关 03年一位技术大牛写的如何迅速成为Java高手

相关如何优化sql语句

相关 sql 语句优化

相关优化SQL查询：如何写出高性能SQL语句

相关 sql语句优化

相关如何优化sql语句

相关 SQL 语句效率问题（如何写出高性能的SQL语句）

相关 SQL语句优化

相关 sql语句优化