最近博主看完了《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起飞（优化）