最近博主看完了《SQL进阶教程》这本书，看完后给博主打开了SQL国际的新大门，关于 SQL 的了解不在局限于以前的常规用法。借用其他读者的谈论，

读完醍醐灌顶，对SQL做到了知其然更能知其所以然。全书从头到尾强调了 SQL的内涵逻辑是根据调集论和谓词逻辑，而着两条主线恰恰在运用SQL起到了至关重要的指导作用。

本文给大家总结怎么让SQL起飞（优化）

一、SQL写法优化

在SQL中，许多时分不同的SQL代码能够得出相同成果。从理论上来说，咱们以为得到相同成果的不同SQL之间应该有相同的功能，但惋惜的是，查询优化器生成的履行计划很大程度上受到SQL代码影响，有快有慢。因而假如想优化查询功能，咱们有必要知道怎么写出更快的SQL，才能使优化器的履行功率更高。

1.1 子查询用EXISTS替代IN

当IN的参数是子查询时，数据库首先会履行子查询，然后将成果存储在一张暂时的工作表里（内联视图），然后扫描整个视图。许多情况下这种做法都十分消耗资源。运用EXISTS的话，数据库不会生成暂时的工作表。可是从代码的可读性上来看，IN要比EXISTS好。运用IN时的代码看起来更加一目了然，易于了解。因而，假如坚信运用IN也能快速获取成果，就没有必要非得改成EXISTS了。

这儿用Class_A表和Class_B举例，

咱们试着从Class_A表中查出一起存在于Class_B表中的职工。下面两条SQL句子回来的成果是相同的，可是运用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时更快的原因有以下两个。

假如衔接列（id）上树立了索引，那么查询 tb_b 时不必查实际的表，只需查索引就能够了。（相同的IN也能够运用索引，这不是重要原因）
假如运用EXISTS，那么只要查到一行数据满足条件就会终止查询，不必像运用IN时相同扫描全表。在这一点上NOT EXISTS也相同。

实际上，大部分情况在子查询数量较小的场景下EXISTS和IN的查询功能平起平坐，由EXISTS查询更快第二点可知，子查询数量较大时运用EXISTS才会有明显优势。

1.2 避免排序并添加索引

在SQL语言中，除了ORDER BY子句会进行显示排序外，还有许多操作默许也会在私自进行排序，假如排序字段没有添加索引，会导致查询功能很慢。SQL中会进行排序的代表性的运算有下面这些。

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

如上列出的六种运算（除了调集运算符），它们后边跟从或许指定的字段都能够添加索引，这样能够加快排序。

实际上在DISTINCT关键字、GROUP BY子句、ORDER BY子句、聚合函数跟从的字段都添加索引，不仅能加快查询，还能加快排序。

1.3 用EXISTS替代DISTINCT

为了排除重复数据，咱们可能会运用DISTINCT关键字。如1.2中所说，默许情况下，它也会进行私自排序。假如需求对两张表的衔接成果进行去重，能够考虑运用EXISTS替代DISTINCT，以避免排序。这儿用Items表和SalesHistory表举例：

咱们考虑一下怎么从上面的产品表Items中找出一起存在于销售记录表SalesHistory中的产品。简而言之，便是找出有销售记录的产品。

在一（Items）对多（SalesHistory）的场景下，咱们需求对item_no去重，运用DISTINCT去重，因而SQL如下：

SELECT DISTINCT I.item_no
  FROM Items I INNER JOIN SalesHistory SH
    ON I. item_no = SH. item_no;
item_no
-------
    10
    20
    30

运用EXISTS替代DISTINCT去重，SQL如下：

SELECT item_no
  FROM Items I
 WHERE EXISTS
          (SELECT ＊
              FROM SalesHistory SH
            WHERE I.item_no = SH.item_no);
item_no
-------
    10
    20
    30

这条句子在履行过程中不会进行排序。并且运用EXISTS和运用衔接相同高效。

1.4 调集运算ALL可选项

SQL中有UNION、INTERSECT、EXCEPT三个调集运算符。在默许的运用办法下，这些运算符会为了排除去重复数据而进行排序。

MySQL还没有实现INTERSECT和EXCEPT运算

假如不在乎成果中是否有重复数据，或许事前知道不会有重复数据，请运用UNION ALL替代UNION。这样就不会进行排序了。

1.5 WHERE条件不要写在HAVING字句

例如，这儿持续用SalesHistory表举例，下面两条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子句是针对聚合后生成的视图进行挑选的，可是许多时分聚合后的视图都没有承继原表的索引结构。

二、真的用到索引了吗

2.1 隐式的类型转换

如下，col_1字段是char类型：

SELECT * FROM SomeTable WHERE col_1 = 10; -- 走了索引
SELECT * FROM SomeTable WHERE col_1 ='10'; -- 没走索引
SELECT * FROM SomeTable WHERE col_1 = CAST(10, AS CHAR(2)); -- 走了索引

当查询条件左边和右边类型不一致时会导致索引失效。

2.2 在索引字段进步行运算

如下：

SELECT *
  FROM SomeTable
 WHERE col_1 * 1.1 > 100;

在索引字段col_1进步行运算会导致索引不收效，把运算的表达式放到查询条件的右侧，就能用到索引了，像下面这样写就OK了。

WHERE col_1 > 100 / 1.1

假如无法避免在左边进行运算，那么运用函数索引也是一种办法，可是不太引荐随意这么做。运用索引时，条件表达式的左边应该是原始字段请紧记，这一点是在优化索引时首要重视的地方。

2.3 运用否定方式

下面这几种否定方式不能用到索引。

这个是跟具体数据库的优化器有关，假如优化器觉得即使走了索引，还是需求扫描许多许多行的哈，他能够挑选直接不走索引。平时咱们用!=、<>、not in的时分，要注意一下。

2.4 运用OR查询前后没有一起运用索引

例如下表：

CREATE TABLE test_tb (
	id int(11) NOT NULL AUTO_INCREMENT, 
	name varchar(55) NOT NULL
	PRIMARY KEY (id)
) 
ENGINE=InnoDB DEFAULT CHARSET=utf8;

运用OR条件进行查询

SELECT *
FROM test_tb 
WHERE id = 1 OR name = 'tom'

这个SQL的履行条件下，很明显id字段查询会走索引，可是关于OR后边name字段的查询是需求进行全表扫描的。在这个场景下，优化器直接进行一遍全表扫描就完事了。

2.5 运用联合索引时，列的顺序错误

运用联合索引需求满足最左匹配准则，即最左优先。假如你树立一个（col_1, col_2, col_3）的联合索引，相当于树立了 (col_1)、(col_1,col_2)、(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 AND col_2 = 100 ;
-- 没走索引
SELECT * FROM SomeTable WHERE col_1 = 10 AND col_3 = 500 ;
-- 没走索引
SELECT * FROM SomeTable WHERE col_2 = 100 AND col_3 = 500 ;
-- 没走索引
SELECT * FROM SomeTable WHERE col_2 = 100 AND col_1 = 10 ;

联合索引中的第一列（col_1）有必要写在查询条件的最初，并且索引中列的顺序不能倒置。

2.6 运用LIKE查询

并不是用了like通配符，索引一定会失效，而是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%';

2.7 衔接字段字符集编码不一致

假如两张表进行衔接，相关字段编码不一致会导致相关字段上的索引失效，这是博主在线上经历一次SQL慢查询后的得到的成果，举例如下，有如下两表，它们的name字段都建有索引，可是编码不一致，user表的name字段编码是utf8mb4，user_job表的name字段编码是utf8，

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER
  SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `age` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
CREATE TABLE `user_job` (
  `id` int NOT NULL,
  `userId` int NOT NULL,
  `job` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

进行SQL查询如下：

EXPLAIN
SELECT * 
from `user` u 
join user_job j on u.name = j.name

由成果可知，user表的查询没有走索引。想要user表也走索引，那就需求把user表name字段的编码改成utf8即可。

三、削减中心表

在SQL中，子查询的成果会被看成一张新表，这张新表与原始表相同，能够通过代码进行操作。这种高度的相似性使得SQL编程具有十分强的灵活性，可是假如不加约束地很多运用中心表，会导致查询功能下降。

频繁运用中心表会带来两个问题，一是打开数据需求消耗内存资源，二是原始表中的索引不容易运用到（特别是聚合时）。因而，尽量削减中心表的运用也是提升功能的一个重要办法。

3.1 运用HAVING子句

对聚合成果指定挑选条件时，运用HAVING子句是基本准则。不习惯运用HAVING子句的人可能会倾向于像下面这样先生成一张中心表，然后在WHERE子句中指定挑选条件。例如下面：

SELECT *
  FROM (
    SELECT sale_date, MAX(quantity) max_qty
      FROM SalesHistory
       GROUP BY sale_date
     ) tmp
 WHERE max_qty >= 10

但是，对聚合成果指定挑选条件时不需求专门生成中心表，像下面这样运用HAVING子句就能够。

SELECT sale_date, MAX(quantity)
  FROM SalesHistory
 GROUP BY sale_date
HAVING MAX(quantity) >= 10;

HAVING子句和聚合操作是一起履行的，所以比起生成中心表后再履行的WHERE子句，功率会更高一些，并且代码看起来也更简洁。

3.2 对多个字段运用IN

当咱们需求对多个字段运用IN条件查询时，能够通过 || 操作将字段衔接在一起变成一个字符串处理。

SELECT *
  FROM Addresses1 A1
 WHERE id || state || city
    IN (SELECT id || state|| city
          FROM Addresses2 A2);

这样一来，子查询不必考虑相关性，并且只履行一次就能够。

3.3 先进行衔接再进行聚合

衔接和聚合一起运用时，先进行衔接操作能够避免发生中心表。原因是，从调集运算的角度来看，衔接做的是“乘法运算”。衔接表两边是1对1、一对多的联系时，衔接运算后数据的行数不会添加。并且，因为在许多设计中多对多的联系都能够分解成两个一对多的联系，因而这个技巧在大部分情况下都能够运用。

到此本文解说完毕，感谢大家阅览，感兴趣的朋友能够点赞加重视，你的支撑将是我更新动力。

大众号【waynblog】每周更新博主最新技术文章，欢迎大家重视

本文正在参与「金石计划」

让SQL起飞（优化）

一、SQL写法优化

1.1 子查询用EXISTS替代IN

1.2 避免排序并添加索引

1.3 用EXISTS替代DISTINCT

1.4 调集运算ALL可选项

1.5 WHERE条件不要写在HAVING字句

二、真的用到索引了吗

2.1 隐式的类型转换

2.2 在索引字段进步行运算

2.3 运用否定方式

2.4 运用OR查询前后没有一起运用索引

2.5 运用联合索引时，列的顺序错误

2.6 运用LIKE查询

2.7 衔接字段字符集编码不一致

三、削减中心表

3.1 运用HAVING子句

3.2 对多个字段运用IN

3.3 先进行衔接再进行聚合

作者信息

推广

让SQL起飞（优化）

一、SQL写法优化

1.1 子查询用EXISTS替代IN

1.2 避免排序并添加索引

1.3 用EXISTS替代DISTINCT

1.4 调集运算ALL可选项

1.5 WHERE条件不要写在HAVING字句

二、真的用到索引了吗

2.1 隐式的类型转换

2.2 在索引字段进步行运算

2.3 运用否定方式

2.4 运用OR查询前后没有一起运用索引

2.5 运用联合索引时，列的顺序错误

2.6 运用LIKE查询

2.7 衔接字段字符集编码不一致

三、削减中心表

3.1 运用HAVING子句

3.2 对多个字段运用IN

3.3 先进行衔接再进行聚合

相关文章

iOS启动优化看我就够了

Alfred WorkFlow的使用-一键打开iTerm2并自动登录VPS

Metal 初见系列（2）- 使用 Metal 绘制视图的内容

生成kubeconfig并通过kubectl连接Kubernetes集群

作者信息

推广