神奇的SQL，如何让我们的 SQL 飞起来优化的核心思想只有一个，那就是找出性能瓶颈所在，然后解决它；常见的优化 Tip

开心一刻

今天肠胃不舒服，去看医生
医生：你一日三餐怎么样
我：每天我老婆吃剩的我吃，女儿吃剩的我吃，一日三餐挺好的
医生：家里没养狗吗
我：怎么，狗吃剩的也要我吃？
医生：不是，我是说剩下的给狗吃
我：给狗吃？那我吃什么？

提前声明

在像 Web 服务这样需要快速响应的应用场景中，SQL 的性能直接决定了系统是否可用；特别在一些中小型应用中，SQL 性能更是决定服务能否快速响应的唯一标准；严格地优化查询性能时，必须要了解所使用数据库的功能特点，此外，查询速度慢并不只是因为 SQL 语句本身，还可能是因为内存分配不佳、文件结构不合理、刷脏页等其他原因，因此后文即将介绍的优化 SQL 的方法不能解决所有的性能问题，但是却能处理很多因 SQL 写法不合理而产生的性能问题。下文将尽量介绍一些不依赖具体数据库实现，使 SQL 执行速度更快、消耗内存更少的优化技巧，只需调整 SQL 语句就能实现通用的优化 Tips

下文所讲的内容是从 SQL 层面展开的，而不是针对某种特定的数据库，也就是说，下文的内容基本上适用于任何关系型数据库；但是，关系型数据库那么多，逐一来演示示例了，显然不太现实；我们以常用的 MySQL 为例来进行讲解，MySQL 版本 5.7.30，存储引擎 InnoDB ，准备表 tbl_customer 和 tbl_recharge_record

DROP TABLE IF EXISTS tbl_customer;
CREATE TABLE tbl_customer (
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  name VARCHAR(50) NOT NULL COMMENT '顾客姓名',
  age TINYINT(3) NOT NULL COMMENT '年龄',
  id_card CHAR(18) NOT NULL COMMENT '身份证',
  phone_number CHAR(11) NOT NULL COMMENT '手机号码',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='顾客表';

INSERT INTO tbl_customer(name, age,id_card,phone_number) VALUES
('张三',19,'430682198109129210','15174480311'),
('李四',21,'430682198109129211','15174480312'),
('王五',22,'430682198109129212','15174480313'),
('六一',23,'430682198109129213','15174480314'),
('六二',25,'430682198109129214','15174480315'),
('六三',27,'430682198109129215','15174480316'),
('六四',29,'430682198109129216','15174480317');

DROP TABLE IF EXISTS tbl_recharge_record;
CREATE TABLE tbl_recharge_record (
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  customer_id INT(11) NOT NULL COMMENT '顾客ID',
  recharge_type TINYINT(2) NOT NULL COMMENT '充值方式 1:支付宝, 2:微信,3:QQ,4:京东,5:银联,6:信用卡,7:其他',
  recharge_amount DECIMAL(15,2) NOT NULL COMMENT '充值金额, 单位元',
  recharge_time DATETIME NOT NULL COMMENT '充值时间',
  remark VARCHAR(500) NOT NULL DEFAULT 'remark' COMMENT '备注',
  PRIMARY KEY (id),
  KEY idx_c_id(customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='顾客充值记录表';

INSERT INTO tbl_recharge_record(customer_id,recharge_type,recharge_amount,recharge_time) VALUES
(1,1,10000,NOW()),
(2,2,20000,NOW()),
(1,2,10000,NOW()),
(1,3,10000,NOW()),
(2,7,20000,NOW()),
(3,3,15000,NOW()),
(4,1,10000,NOW()),
(5,1,10000,NOW()),
(6,1,10000,NOW()),
(7,1,10000,NOW()),
(7,1,10000,NOW()),
(6,1,10000,NOW()),
(5,1,10000,NOW()),
(4,1,10000,NOW()),
(3,1,10000,NOW()),
(2,1,10000,NOW()),
(1,1,10000,NOW()),
(2,1,10000,NOW()),
(3,1,10000,NOW()),
(2,1,10000,NOW()),
(3,1,10000,NOW()),
(4,1,10000,NOW()),
(2,1,10000,NOW()),
(2,1,10000,NOW()),
(1,1,10000,NOW());

因为要经常看执行计划，所以大家可以先去看看

神奇的 SQL 之 MySQL 执行计划 → EXPLAIN，让我们了解 SQL 的执行过程！

高效查询

针对某一个查询，有时候会有多种 SQL 实现，例如 IN、EXISTS、连接之间的互相转换；从理论上来讲，得到相同结果的不同 SQL 语句应该有相同的性能，但遗憾的是，查询优化器生成的执行计划很大程度上要受到外部结构的影响，因此，如果想优化查询性能，必须知道如何写 SQL 语句才能使优化器生成更高效的执行计划

EXISTS 代替 IN

关于 IN，你们肯定比较熟悉，使用方便，也容易理解，平时肯定用的非常多；虽说 IN 使用方便，但它却存在性能瓶颈，如果 IN 的参数是 1,2,3 这样的数值列表，一般还不需要特别注意，但如果参数是子查询，那么就需要注意了；在大多时候， [NOT] IN 和 [NOT] EXISTS 返回的结果是相同的，但是两者用于子查询时，EXISTS 的速度会更快一些，我们来看一个案例

假设我们要查询有充值记录的顾客信息，SQL 该怎么写？

我们第一时间想到的肯定是 IN

SELECT * FROM tbl_customer WHERE ID IN (SELECT customer_id FROM tbl_recharge_record);

执行计划如下

改用 EXISTS 实现

SELECT * FROM tbl_customer tc WHERE EXISTS (
    SELECT customer_id FROM tbl_recharge_record tr WHERE tc.id = tr.customer_id);

执行计划如下

可以看到，IN 的执行计划中新产生了一张临时表： <subquery2> ，这会导致效率变慢

通常来讲，EXISTS 比 IN 更快的原因有两个

如果连接列（customer_id）上建立了索引，那么查询 tbl_recharge_record 时，EXISTS 可以通过索引扫描，而 IN 是全表扫描
使用 EXISTS，一旦查到一行数据满足条件就会终止查询，不用像使用 IN 时一样进行全表扫描（NOT EXISTS 也一样）

当 IN 的参数是子查询时，数据库首先会执行子查询，然后将结果存储在一张临时表里（内联视图），然后扫描整个视图，很多情况下这种做法非常耗费资源，而使用 EXISTS 的话，数据库不会生成临时表；但是从代码的可读性上来看，IN 要比 EXISTS 好，使用 IN 时的代码看起来更加一目了然，易于理解，因此，如果确信使用 IN 也能快速获取结果，就没有必要非得改成 EXISTS 了

其实有很多数据库也尝试着改善了 IN 的性能

Oracle 数据库中，如果我们在有索引的列上使用 IN，也会先扫描索引
PostgreSQL 从版本 7.4 起也改善了使用子查询作为 IN 谓词参数时的查询速度

说不定在未来的某一天，无论在哪个关系型数据库上，IN 都能具备与 EXISTS 一样的性能

关于 EXISTS，更多详情可查看：神奇的 SQL 之谓词 → 尽量让你们理解 EXISTS

JOIN 代替 IN

其实在平时工作当中，基本是用 JOIN 代替 IN 来改善查询性能，而非 EXISTS，不是说连接更好，而是 EXISTS 很难掌握；回到案例

假设我们要查询有充值记录的顾客信息，SQL 该怎么写？

用 JOIN 实现如下

SELECT DISTINCT tc.* FROM tbl_recharge_record trr
LEFT JOIN tbl_customer tc ON trr.customer_id = tc.id;

执行计划如下

JOIN 也能能充分利用索引，并且因为没有了子查询，所以数据库也不会生成中间表，所以查询效率是不错的；至于 JOIN 与 EXISTS 相比哪个性能更好，不太好说，如果没有索引，可能 EXISTS 会略胜一筹，有索引的话，两者差不多

避免排序

说到 SQL 的排序，我们第一时间想到的肯定是 ORDER BY，通过它，我们可以按指定的某些列来顺序输出结果，但是除了 ORDER BY 显示的排序，数据库内部还有很多运算在暗中进行排序，例如下面这些

如果只在内存中进行排序，那么还好；但是如果因内存不足而需要在硬盘上排序，那么性能就会急剧下降，因此尽量避免（或减少）无谓的排序，能够大大提高查询效率

集合运算 ALL 选项

SQL 中有 UNION、INTERSECT、EXCEPT 三个集合运算符，分别代表这集合运算的并集、交集、差集，默认情况下，这些运算符会为了排除掉重复数据而进行排序，例如

SELECT * FROM tbl_customer WHERE id < 3
UNION
SELECT * FROM tbl_customer WHERE id > 15

执行计划如下

Extra 中的 Using temporary 表示进行了排序或分组，显然这个 SQL 没有进行分组，而是进行了排序运算；如果我们不在乎结果中是否有重复数据，或者事先知道不会有重复数据，可以给 UNION 补上 ALL 选项，例如

SELECT * FROM tbl_customer WHERE id < 3
UNION ALL
SELECT * FROM tbl_customer WHERE id > 15

执行计划如下

可以看到，执行计划中没有排序运算了；对于 INTERSECT 和 EXCEPT 也是一样的，加上 ALL 选项后就不会进行排序了

加上 ALL 选项是一个非常有效的优化手段，但各个数据库对它的实现情况却是参差不齐，如下图所示

注意：Oracle 使用 MINUS 代替 EXCEPT，而 MySQL 压根就没有实现 INTERSECT 和 EXCEPT 运算

EXISTS 代替 DISTINCT

为了去除重复数据，我们会使用 DISTINCT，但它会进行排序；还记得前面 JOIN 代替 IN 的 SQL 吗

SELECT DISTINCT tc.* FROM tbl_recharge_record trr
LEFT JOIN tbl_customer tc ON trr.customer_id = tc.id;

其执行计划如下

Using temporary 表示进行了排序，用 EXISTS 进行优化

SELECT * FROM tbl_customer tc WHERE EXISTS (SELECT customer_id FROM tbl_recharge_record trr WHERE tc.id = trr.customer_id);

执行计划如下

可以看到，已经规避了排序运算

极值函数使用索引列

SQL 语言里有两个极值函数：MAX 和 MIN，使用这两个函数时都会进行排序，例如

SELECT MAX(recharge_amount) FROM tbl_recharge_record;

会进行全表扫描，然后进行隐式的排序，找出单笔充值最大的金额；但是如果参数字段上建有索引，则只需要扫描索引，不需要扫描整张表，例如

SELECT MAX(customer_id) FROM tbl_recharge_record;

会通过索引 idx_c_id 进行扫描，找出充值记录中最大的顾客ID；这种方法并不是去掉了排序这一过程，而是优化了排序前的查找速度，从而减弱排序对整体性能的影响

过滤条件尽量放入WHERE子句

我们知道，不只 WHERE 子句中可以写过滤条件，HAVING 子句中也可以写过滤条件；在不影响过滤结果的情况下，一个过滤条件既可以放到 WHERE 子句，又可以放到 HAVING 子句，那么不要犹豫，将该过滤条件放入 WHERE 子句中，我们来看个案例

从结果上来看，两条 SQL 一样；但是从性能上来看，第二条语句写法效率更高，原因有两个

减少排序的数据量

GROUP BY 子句聚合时会进行排序，如果事先通过 WHERE 子句筛选出一部分行，那么参与聚合的行就变少了，也就减轻了排序负担
有效利用索引

WHERE 子句的条件里可以使用索引，而 HAVING 子句是针对聚合后生成的视图进行筛选的，很多时候聚合后的视图都没有继承原表的索引结构，所以使用不了索引