后端程序员必备：SQL高性能优化指南！35+条优化建议立马GET!

虽然组织工作须要，前段时间做了许多 BI 取数的组织工作，须要加进许多较为高阶的 SQL 基本功，归纳了呵呵组织工作中加进的许多较为骚的高阶基本功，电魂网络历史记录呵呵，以方便快捷他们翻查，主要就产品目录如下表所示：

SQL 的手写规范化SQL 的许多高阶采用基本功SQL 的强化方式其它有关写作：

后端开发人员必不可少：手写高效率SQL的30条提议

MySQL高效能强化规范化提议,速率珍藏

SQL 的手写规范化

在如是说许多基本功以后，有必要性特别强调呵呵规范化，这一点儿我辨认出组织工作中时常被人忽视，只不过遵从好的规范化时效性会好许多，如果遵从什么样规范化呢

1、表名要有象征意义，且国际标准 SQL 中明确规定表名的第二个字符串如果是拉丁字母。

2、注解，有单行线注解和二百一十三注解，如下表所示

— 单行线注解— 从SomeTable中翻查col_1SELECT

col_1

FROM

SomeTable;

二百一十三注解

从 SomeTable 中翻查 col_1

*/ SELECT

col_1

FROM

SomeTable;

二百一十三注解很多人不知道，这种写法不仅可以用来添加真正的注解，也可以用来注解代码，非常方便快捷

3、缩进

就像写 Java，Python 等编程语言一样，SQL 也如果有缩进，良好的缩进对提升代码的时效性帮助很大，以下分别是好的缩进与坏的缩进示例

— 好的缩进SELECT

col_1,

col_2,

col_3,

COUNT

(*)

FROM

tbl_A

WHERE col_1 = a AND col_2 = ( SELECT MAX

(col_2)

FROM

tbl_B

WHERE col_3 = 100

)

GROUP BY

col_1,

col_2,

col_3

— 坏的示例SELECT col1_1, col_2, col_3, COUNT

(*)

FROM

tbl_A

WHERE col1_1 = aAND

col1_2 = (

SELECT MAX

(col_2)

FROM

tbl_B

WHERE col_3 = 100) GROUP BY col_1, col_2, col_3

4、空格

代码中如果适当留有许多空格，如果一点儿不留，代码都凑到一起，逻辑单元不明确，写作的人也会产生额外的压力，以下分别是是好的与坏的示例

— 好的示例SELECT

col_1

FROM

tbl_A A, tbl_B B

WHERE ( A.col_1 >= 100 OR A.col_2 IN ( a, b

) )

AND

A.col_3 = B.col_3;

— 坏的示例SELECT

col_1

FROM

tbl_A A,tbl_B B

WHERE (A.col_1>=100 OR A.col_2 IN (a,b

))

AND A.col_3=B.col_3;

5、大小写

关键字采用大小写，表名列名采用小写，如下表所示

SELECT

col_1, col_2, col_3,

COUNT

(*)

FROM

tbl_A

WHERE col_1 = a AND col_2 = ( SELECT MAX

(col_2)

FROM

tbl_B

WHERE col_3 = 100

)

GROUP BY

col_1, col_2, col_3

花了这么多时间特别强调规范化，有必要性吗，有！好的规范化让代码的时效性更好，更有利于团队合作，之后的 SQL 示例都会遵从这些规范化。

SQL 的许多高阶采用基本功

1、巧用 CASE WHEN 进行统计

来看看如何巧用 CASE WHEN 进行定制化统计,假设我们有如下表所示的需求，希望根据左边各个市的人口统计每个省的人口

采用 CASE WHEN 如下表所示

SELECT CASE

pref_name

WHEN 长沙 THEN 湖南 WHEN 衡阳 THEN 湖南 WHEN 海口 THEN 海南 WHEN 三亚 THEN 海南 ELSE 其它 END AS

district,

SUM

(population)

FROM

PopTbl

GROUP BY

district;

2、巧用 CASE WHEN 进行更新

现在某公司员人工资信息表如下表所示:

现在公司出台了一个奇葩的明确规定

对当前工资为 1 万以上的员工，降薪 10%。对当前工资低于 1 万的员工，加薪 20%。

一些人不假思索可能写出了以下的 SQL:

–条件1UPDATE

Salaries

SET salary = salary * 0.9 WHERE salary >= 10000

;

–条件2UPDATE

Salaries

SET salary = salary * 1.2WHERE salary < 10000

;

这么做只不过是有问题的，什么问题，对小明来说，他的工资是 10500，执行第二个 SQL 后，工资变为 10500 * 0.9 = 9450, 紧接着又执行条件 2，工资变为了 9450 * 1.2 = 11340，反而涨薪了！

如果用 CASE WHEN 可以解决此类问题，如下表所示：

UPDATE

Salaries

SET salary = CASE WHEN salary >= 10000 THEN salary * 0.9WHEN salary < 10000 THEN salary * 1.2ELSE salary END

;

3、巧用 HAVING 子句

一般 HAVING 是与 GROUP BY 结合采用的，但只不过它是可以独立采用的，假设有如下表所示表，第一列 seq 叫连续编号，但只不过有些编号是缺失的，怎么知道编号是否缺失呢，

用 HAVING 表示如下表所示:

SELECT 存在缺失的编号 AS

gap

FROM

SeqTbl

HAVING COUNT(*) <> MAX

(seq);

4、自连接

针对相同的表进行的连接被称为“自连接”(self join)，这个基本功常常被人们忽视，只不过是有挺多妙用的

1、删除重复行

上图中有三个橘子，须要把这些重复的行给删掉，用如下表所示自连接可以解决：

DELETE FROM

Products P1

WHERE id < ( SELECT MAX

(P2.id)

FROM

Products P2

WHERE

P1.name = P2.name

AND

P1.price = P2.price );

2、排序

在 db 中，我们时常须要按分数，人数，销售额等进行排名，有 Oracle, DB2 中可以采用 RANK 函数进行排名，不过在 MySQL 中 RANK 函数未实现，这种情况我们可以采用自连接来实现,如对以下 Products 表按价格高低进行排名

采用自连接可以这么写:

— 排序从 1 开始。如果已出现相同位次，则跳过之后的位次 SELECT

P1.name,

P1.price,

(SELECT COUNT

(P2.price)

FROM

Products P2

WHERE P2.price > P1.price) + 1 AS

rank_1

FROM

Products P1

ORDER BY

rank_1;

结果如下表所示:

name price rank

—– —— ——

橘子 100 1

西瓜 80 2

苹果 50 3

葡萄 50 3

香蕉 50 3

柠檬 30 6

5、巧用 COALESCE 函数

此函数作用返回参数中的第二个非空表达式，假设有如下表所示商品，我们重新格式化一样，如果 city 为 null，代表商品不在此城市发行，但我们在展示结果的时候不想展示 null，而想展示 N/A, 可以这么做:

SELECT COALESCE(city, N/A

)

FROM

customers;

SQL 性能强化基本功

1、参数是子翻查时，采用 EXISTS 代替 IN

如果 IN 的参数是（1，2，3）这样的值列表时，没啥问题，但如果参数是子翻查时，就须要注意了。比如，现在有如下表所示两个表：

现在我们要查出同时存在于两个表的员工，即田中和铃木，则以下用 IN 和 EXISTS 返回的结果是一样，但是用 EXISTS 的 SQL 会更快:

— 慢SELECT

FROM

Class_A

WHERE id IN (SELECT id FROM

CLASS_B);

— 快SELECT

FROM

Class_A A

WHERE EXISTS(SELECT

FROM

Class_B B

WHERE

A.id = B.id);

为啥采用 EXISTS 的 SQL 运行更快呢，有两个原因

可以`加进索引，如果连接列 (id) 上建立了索引，那么查询 Class_B 时不用查实际的表，只需查索引就可以了。如果采用 EXISTS，那么只要查到一行数据满足条件就会终止翻查，不用像采用 IN 时一样扫描全表。在这一点儿上 NOT EXISTS 也一样

另外如果 IN 后面如果跟着的是子翻查，虽然 SQL 会先执行 IN 后面的子翻查，会将子翻查的结果保存在一张临时的组织工作表里（内联视图），然后扫描整个视图，显然扫描整个视图这个组织工作许多时候是非常耗时的，而用 EXISTS 不会生成临时表。

当然了，如果 IN 的参数是子翻查时，也可以用连接来代替，如下表所示：

— 采用连接代替 IN SELECT A.id, A.name

FROM Class_A A INNER JOIN Class_B B ON A.id = B.id;

加进了「id」列上的索引，而且虽然没有子翻查，也不会生成临时表

2、避免排序

SQL 是声明式语言，即对用户来说，只关心它能做什么，不用关心它怎么做。这样可能会产生潜在的性能问题：排序，会产生排序的代表性运算有下面这些

GROUP BY 子句ORDER BY 子句聚合函数(SUM、COUNT、AVG、MAX、MIN)DISTINCT集合运算符(UNION、INTERSECT、EXCEPT)窗口函数(RANK、ROW_NUMBER 等)

如果在内存中排序还好，但如果内存不够导致须要在硬盘上排序上的话，性能就会急剧下降，所以我们须要减少不必要性的排序。怎样做可以减少排序呢。

1、采用集合运算符的 ALL 可选项

SQL 中有 UNION，INTERSECT，EXCEPT 三个集合运算符，默认情况下，这些运算符会为了避免重复数据而进行排序，对比呵呵采用 UNION 运算符加和不加 ALL 的情况:

注意：加 ALL 是强化性能非常有效的手段，不过前提是不在乎结果是否有重复数据。

2、采用 EXISTS 代表 DISTINCT

为了排除重复数据， DISTINCT 也会对结果进行排序，如果须要对两张表的连接结果进行去重，可以考虑用 EXISTS 代替 DISTINCT，这样可以避免排序。

如何找出有销售历史记录的商品，采用如下表所示 DISTINCT 可以：

SELECT DISTINCT

I.item_no

FROM Items I INNER JOIN

SalesHistory SH

I. item_no = SH. item_no;

不过更好的方式是采用 EXISTS:

SELECT item_no FROM

Items I

WHERE EXISTS (SELECT

FROM

SalesHistory SH

WHERE

I.item_no = SH.item_no);

既用到了索引，又避免了排序对性能的损耗。

2、在极值函数中采用索引（MAX/MIN）

采用 MAX/ MIN 都会对进行排序，如果参数字段上没加索引会导致全表扫描，如果建有索引，则只须要扫描索引即可，对比如下表所示

— 这样写须要扫描全表 SELECT MAX

(item)

FROM

Items;

— 这样写能加进索引 SELECT MAX

(item_no)

FROM

Items;

注意：极值函数参数推荐为索引列中并不是不须要排序，而是强化了排序前的查找速率（毕竟索引本身就是有序排列的）。

3、能写在 WHERE 子句里的条件不要写在 HAVING 子句里

下列 SQL 语句返回的结果是一样的:

— 聚合后采用 HAVING 子句过滤SELECT sale_date, SUM

(quantity)

FROM SalesHistory GROUP BY

sale_date

HAVING sale_date = 2007-10-01

;

— 聚合前采用 WHERE 子句过滤SELECT sale_date, SUM

(quantity)

FROM

SalesHistory

WHERE sale_date = 2007-10-01 GROUP BY

sale_date;

采用第二条语句效率更高，原因主要就有两点

采用 GROUP BY 子句进行聚合时会进行排序，如果事先通过 WHERE 子句能筛选出一部分行，能减轻排序的负担在 WHERE 子句中可以采用索引，而 HAVING 子句是针对聚合后生成的视频进行筛选的，但许多时候聚合后生成的视图并没有保留原表的索引结构

4、在 GROUP BY 子句和 ORDER BY 子句中采用索引

GROUP BY 子句和 ORDER BY 子句一般都会进行排序，以对行进行排列和替换，不过如果指定带有索引的列作为这两者的参数列，虽然加进了索引，可以实现高速翻查，虽然索引是有序的，排序本身都会被省略掉

5、采用索引时，条件表达式的左侧如果是原始字段

假设我们在 col 列上建立了索引，则下面这些 SQL 语句无法加进索引

SELECT

FROM

SomeTable

WHERE col * 1.1 > 100

;

SELECT

FROM

SomeTable

WHERE SUBSTR(col, 1, 1) = a

;

以上第二个 SQL 在索引列上进行了运算, 第二个 SQL 对索引列采用了函数，均无法加进索引，正确方式是把列单独放在左侧,如下表所示:

SELECT

FROM

SomeTable

WHERE col_1 > 100 / 1.1

;

当然如果须要对此列采用函数，则无法避免在左侧运算，可以考虑采用函数索引，不过一般不推荐随意这么做。

6、尽量避免采用否定形式

如下表所示的几种否定形式不能加进索引：

<>!=NOT IN

所以以下了SQL 语句会导致全表扫描

SELECT

FROM

SomeTable

WHERE col_1 <> 100

;

可以改成以下形式

SELECT

FROM

SomeTable

WHERE col_1 > 100 or col_1 < 100

;

7、进行默认的类型转换

假设 col 是 char 类型，则推荐采用以下第二，三条 SQL 的写法，不推荐第一条 SQL 的写法

× SELECT * FROMSomeTableWHERE col_1 = 10

;

○ SELECT * FROM SomeTable WHERE col_1 = 10

;

○ SELECT * FROM SomeTable WHERE col_1 = CAST(10,AS CHAR(2

));

虽然第一条 SQL 会默认把 10 转成 10，但这种默认类型转换不仅会增加额外的性能开销，还会导致索引不可用，所以提议采用的时候进行类型转换。

8、减少中间表

在 SQL 中，子查询的结果会产生一张新表，不过如果不加限制大量采用中间表的话，会带来两个问题，一是展示数据须要消耗内存资源，二是原始表中的索引不容易加进，所以尽量减少中间表也可以提升性能。

9、灵活采用 HAVING 子句

这一点儿与上面第八条相呼应，对聚合结果指定筛选条件时，采用 HAVING 是基本的原则，可能许多工程师会倾向于采用下面这样的写法:

SELECT

FROM (SELECT sale_date, MAX(quantity)AS

max_qty

FROM

SalesHistory

GROUP BY

sale_date) TMP

WHERE max_qty >= 10

;

虽然上面这样的写法能达到目的，但会生成 TMP 这张临时表，所以如果采用下面这样的写法:

SELECT sale_date, MAX

(quantity)

FROM

SalesHistory

GROUP BY

sale_date

HAVING MAX(quantity) >= 10

;

HAVING 子句和聚合操作是同时执行的，所以比起生成中间表后再执行 HAVING 子句，效率会更高，代码也更简洁

10、须要对多个字段采用 IN 谓词时，将它们汇总到一处

一个表的多个字段可能都采用了 IN 谓词，如下表所示:

SELECT id

, state, city

FROM

Addresses1 A1

WHERE state IN (SELECT

state

FROM

Addresses2 A2

WHERE

A1.id = A2.id)

AND city IN (SELECT

city

FROM

Addresses2 A2

WHERE

A1.id = A2.id);

这段代码加进了两个子翻查，也就产生了两个中间表，可以像下面这样写

SELECT

FROM

Addresses1 A1

WHERE id

|| state || city

IN (SELECT id

|| state|| city

FROM

Addresses2 A2);

这样子翻查不用考虑关联性，没有中间表产生，而且只执行一次即可。

11、采用延迟翻查强化 limit [offset], [rows]

时常出现类似以下的 SQL 语句:

SELECT * FROM film LIMIT 100000, 10

offset 特别大!

这是我司出现许多慢 SQL 的主要就原因之一，尤其是在跑任务须要分页执行时，时常跑着跑着 offset 就跑到几十万了，导致任务越跑越慢。

LIMIT 能很好地解决分页问题，但如果 offset 过大的话，会造成严重的性能问题，原因主要就是因为 MySQL 每次会把一整行都扫描出来，扫描 offset 遍，找到 offset 之后会抛弃 offset 以后的数据，再从 offset 开始读取 10 条数据，显然，这样的读取方式问题。

可以通过延迟翻查的方式来强化

假设有以下 SQL,有组合索引（sex, rating）

SELECT <cols> FROM profiles wheresex=M order by rating limit 100000, 10

;

则上述写法可以改成如下表所示写法

SELECT FROM profilesinner join(SELECT id form FROM profiles where x.sex=M order by rating limit 100000, 10

)

as x using(id

);

，保留最后 10 个 id 即可，丢掉 100000 条 id 不是什么大的开销，所以这样可以显著提升性能

12、利用 LIMIT 1 取得唯一行

数据库引擎只要辨认出满足条件的一行数据则立即停止扫描，，这种情况适用于只需查找一条满足条件的数据的情况

13、注意组合索引，要符合最左匹配原则才能生效

假设存在这样顺序的一个联合索引“col_1, col_2, col_3”。这时，指定条件的顺序就很重要。

○SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 AND col_3 = 500

;

○ SELECT * FROM SomeTable WHERE col_1 = 10 ANDcol_2 =100

;

× SELECT * FROM SomeTable WHERE col_2 = 100 AND col_3 = 500

;

前面两条会命中索引，第三条虽然没有先匹配 col_1，导致无法命中索引，另外如果无法保证翻查条件里列的顺序与索引一致，可以考虑将联合索引拆分为多个索引。

14、采用 LIKE 谓词时，只有前方一致的匹配才能加进索引（最左匹配原则）

× SELECT * FROM SomeTable WHERE col_1 LIKE %a

;

× SELECT * FROM SomeTable WHERE col_1 LIKE %a%

;

○ SELECT * FROM SomeTable WHERE col_1 LIKE a%

;

上例中，只有第三条会命中索引，前面两条进行后方一致或中间一致的匹配无法命中索引

15、简单字符串串表达式

模型字符串串可以采用 _ 时，尽可能避免采用 %, 假设某一列上为 char(5)

不推荐

SELECT

first_name,

last_name,

homeroom_nbr

FROM

Students

WHERE homeroom_nbr LIKE A-1%

;

推荐

SELECT

first_name, last_name

homeroom_nbr

FROM

Students

WHERE homeroom_nbr LIKE A-1__; –模式字符串串中包含了两个下划线

16、尽量采用自增 id 作为主键

比如现在有一个用户表，有人说身份证是唯一的，也可以用作主键，理论上确实可以，不过用身份证作主键的话，一是占用空间相对于自增主键大了许多，二是很容易引起频繁的页分裂，造成性能问题（什么是页分裂，请参考这篇文章）

主键选择的几个原则：自增，尽量小，不要对主键进行修改

17、在无 WHERE 条件下要计算表的行数，优先采用 count(*)

优先采用以下语句来统计行数, innoDB 5.6之后已经对此语句进行了强化

SELECT COUNT(*) FROM

SomeTable

按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)，count(*) 会选用性能最好的索引来进行排序

18、避免采用 SELECT * ，尽量利用覆盖索引来强化性能

SELECT *会提取出一整行的数据，如果翻查条件中用的是组合索引进行查找，还会导致回表（先根据组合索引找到叶子节点，再根据叶子节点上的主键回表翻查一整行），降低性能，而如果我们所要的数据就在组合索引里，只需读取组合索引列，这样网络带宽将大大减少,假设有组合索引列 (col_1, col_2)

推荐用

SELECT

col_1, col_2

FROM

SomeTable

WHEREcol_1 = xxxAND

col_2 = xxx

不推荐用

SELECT

FROM

SomeTable

WHERE col_1 = xxx AND

col_2 = xxx

19、如有必要性，采用 force index() 强制走某个索引

业务团队曾经出现类似以下的慢 SQL 翻查

SELECT

FROM

SomeTable

WHERE `status` = 0 AND `gmt_create` > 1490025600 AND `gmt_create` < 1490630400 AND `id` > 0 AND `post_id` IN (67778, 67811, 67833, 67834, 67839, 67852, 67861, 67868, 67870, 67878, 67909, 67948, 67951, 67963, 67977, 67983, 67985, 67991, 68032, 68038/*… omitted 480 items …*/

)

order by id asc limit 200

;

post_id 也加了索引，理论上走 post_id 索引会很快翻查出来，但实现了通过 EXPLAIN 辨认出走的却是 id 的索引（这里隐含了一个常见考点，在多个索引的情况下, MySQL 会如何选择索引），而 id > 0 这个翻查条件没啥用，直接导致了全表扫描，所以在有多个索引的情况下一定要慎用，可以采用 force index 来强制走某个索引，以这个例子为例，可以强制走 post_id 索引，效果立杆见影。

这种虽然表中有多个索引导致 MySQL 误选索引造成慢翻查的情况在业务中也是非常常见，一方面是表索引太多，另一方面也是虽然 SQL 语句本身太过复杂导致，针对本例这种复杂的 SQL 翻查，只不过用 ElasticSearch 搜索引擎来查找更合适，有机会到时出一篇文章说说。

20、采用 EXPLAIN 来查看 SQL 执行计划

上个点说了，可以采用 EXPLAIN 来分析 SQL 的执行情况，如怎么辨认出上文中的最左匹配原则不生效呢，执行「EXPLAIN + SQL 语句」可以辨认出 key 为 None ,说明确实没有命中索引

我司在提供 SQL 翻查的同时，也贴心地加了一个 EXPLAIN 功能及 sql 的强化提议，提议各大公司效仿 ^_^,如图示

21、批量插入，速率更快

当须要插入数据时，批量插入比逐条插入性能更高

推荐用