神奇的 SQL 之性能优化 → 让 SQL 飞起来

环境准备

　　下文所讲的内容是从 SQL 层面展开的，而不是针对某种特性的数据库，也就是说，下文的内容基本上适用于任何关系型数据库

　　但是，关系型数据库那么多，逐一来演示示例了，显然不太现实；我们以常用的 MySQL 来进行示例的演示

　　MySQL 版本： 5.7.30-log ，存储引擎： 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());

　　后文中， EXPLAIN 谈的比较多，具体可查看：神奇的 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); 

　　　　IN 使用起来确实简单，也非常好理解；我们来看下它的执行计划

　　　　我们再来看看 EXISTS 的执行计划：

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

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

　　　　　　1、如果连接列（customer_id）上建立了索引，那么查询 tbl_recharge_record 时可以通过索引查询，而不是全表查询

　　　　　　2、使用 EXISTS，一旦查到一行数据满足条件就会终止查询，不用像使用 IN 时一样进行扫描全表（NOT EXISTS 也一样）

　　　　当 IN 的参数是子查询时，数据库首先会执行子查询，然后将结果存储在一张临时表里（内联视图），然后扫描整个视图，很多情况下这种做法非常耗费资源

　　　　使用 EXISTS 的话，数据库不会生成临时表

　　　　但是从代码的可读性上来看，IN 要比 EXISTS 好，使用 IN 时的代码看起来更加一目了然，易于理解

　　　　因此，如果确信使用 IN 也能快速获取结果，就没有必要非得改成 EXISTS 了

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

　　　　　　Oracle 数据库中，如果我们在有索引的列上使用 IN， 也会先扫描索引

　　　　　　PostgreSQL 从版 本 7.4 起也改善了使用子查询作为 IN 谓词参数时的查询速度

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

　　　　关于 EXISTS，更多详情可查看：神奇的 SQL 之谓词 → 难理解的 EXISTS

　　使用连接代替 IN

　　　　其实在平时工作当中，更多的是用连接代替 IN 来改善查询性能，而非 EXISTS，不是说连接更好，而是 EXISTS 很难掌握

　　　　回到问题：查询有充值记录的顾客信息，如果用连接来实现，SQL 改如何写？

　　　　这种写法能充分利用索引；而且，因为没有了子查询，所以数据库也不会生成中间表；所以，查询效率是不错的

　　　　至于 JOIN 与 EXISTS 相比哪个性能更好，不太好说；如果没有索引，可能 EXISTS 会略胜一筹，有索引的话，两者差不多

避免排序

　　说到 SQL 的排序，我们第一时间想到的肯定是： ORDER BY ，通过它，我们可以按指定的某些列来顺序输出结果

　　但是，除了 ORDER BY 显示的排序，数据库内部还有很多运算在暗中进行排序；会进行排序的代表性的运算有下面这些

　　如果只在内存中进行排序，那么还好；但是如果因内存不足而需要在硬盘上排序，那么性能就会急剧下降

　　因此，尽量避免（或减少）无谓的排序，能够大大提高查询效率

　　灵活使用集合运算符的 ALL 可选项

　　　　SQL 中有 UNION  、 INTERSECT  、 EXCEPT 三个集合运算符，分表代表这集合运算的 并集、交集、差集

　　　　默认情况下，这些运算符会为了排除掉重复数据而进行排序

　　　　 Using temporary 表示进行了排序或分组，显然这个 SQL 没有进行分组，而是进行了排序运算

　　　　如果我们不在乎结果中是否有重复数据，或者事先知道不会有重复数据，可以使用 UNION ALL 代替 UNION 

　　　　可以看到，执行计划中没有排序运算了

　　　　对于 INTERSECT 和 EXCEPT 也是一样的，加上 ALL 可选项后就不会进行排序了

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

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

　　使用 EXISTS 代替 DISTINCT

　　　　为了排除重复数据， DISTINCT 也会进行排序

　　　　还记得用连接代替 IN 的案例吗，如果不用 DISTINCT 

　　　　SQL： SELECT tc.* FROM tbl_recharge_record trr LEFT JOIN tbl_customer tc on trr.customer_id = tc.id 

　　　　那么查出来的结果会有很多重复记录，我们改进 SQL

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

　　　　会发现执行计划中有个 Using temporary ，表示用到了排序运算

　　　　我们使用 EXISTS 来进行优化

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

　　在极值函数中使用索引

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

　　　　例如： SELECT MAX(recharge_amount) FROM tbl_recharge_record 

　　　　会进行全表扫描，并会进行隐式的排序，找出单笔充值最大的金额

　　　　但是如果参数字段上建有索引，则只需要扫描索引，不需要扫描整张表

　　　　例如： SELECT MAX(customer_id) FROM tbl_recharge_record; 

　　　　会通过索引： idx_c_id 进行扫描，找出充值记录中最大的顾客ID

　　　　这种方法并不是去掉了排序这一过程，而是优化了排序前的查找速度，从而减弱排序对整体性能的影响

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

　　　　我们来看两个 SQL 以及其执行结果

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

　　　　减少排序的数据量

　　　　　　GROUP BY 子句聚合时会进行排序，如果事先通过 WHERE 子句筛选出一部分行，就能够减轻排序的负担

　　　　有效利用索引

　　　　　　WHERE 子句的条件里可以使用索引

　　　　　　HAVING 子句是针对聚合后生成的视图进行筛选的，但是很多时候聚合后的视图都没有继承原表的索引结构

　　　　关于 HAVING，更多详情可查看：神奇的 SQL 之 HAVING → 容易被轻视的主角

　　在 GROUP BY 子句和 ORDER BY 子句中使用索引

　　　　一般来说，GROUP BY 子句和 ORDER BY 子句都会进行排序

　　　　如果 GROUP BY 和 ORDER BY 的列有索引，那么可以提高查询效率

　　　　特别是在一些数据库中，如果列上建立的是唯一索引，那么排序过程本身都会被省略掉

使用索引

　　使用索引是最常用的 SQL 优化手段，这个大家都知道，怕就怕大家不知道：明明有索引，为什么查询还是这么慢（为什么索引没用上）

　　关于索引未用到的情况，可查看：神奇的 SQL 之擦肩而过 → 真的用到索引了吗，本文就不做过多阐述了

　　总之就是：查询尽量往索引上靠，规避索引未用上的情况

减少临时表

　　在 SQL 中，子查询的结果会被看成一张新表（临时表），这张新表与原始表一样，可以通过 SQL 进行操作

　　但是，频繁使用临时表会带来两个问题

　　　　1、临时表相当于原表数据的一份备份，会耗费内存资源

　　　　2、很多时候（特别是聚合时），临时表没有继承原表的索引结构

　　因此，尽量减少临时表的使用也是提升性能的一个重要方法

　　灵活使用 HAVING 子句

　　　　对聚合结果指定筛选条件时，使用 HAVING 子句是基本原则

　　　　但是如果对 HAVING 不熟，我们往往找出替代它的方式来实现，就像这样

　　　　然而，对聚合结果指定筛选条件时不需要专门生成中间表，像下面这样使用 HAVING 子句就可以

　　　　HAVING 子句和聚合操作是同时执行的，所以比起生成临时表后再执行 WHERE 子句，效率会更高一些，而且代码看起来也更简洁

　　需要对多个字段使用 IN 谓词时，将它们汇总到一处

　　　　SQL-92 中加入了行与行比较的功能，这样一来，比较谓词 = 、< 、> 和 IN 谓词的参数就不再只是标量值了，而应是值列表了

　　　　我们来看一个示例，多个字段使用 IN 谓词

　　　　这段代码中用到了两个子查询，我们可以进行列汇总优化，把逻辑写在一起

　　　　这样一来，子查询不用考虑关联性，而且只执行一次就可以

　　　　还可以进一步简化，在 IN 中写多个字段的组合

　　　　简化后，不用担心连接字段时出现的类型转换问题，也不会对字段进行加工，因此可以使用索引

　　先进行连接再进行聚合

　　　　连接和聚合同时使用时，先进行连接操作可以避免产生中间表

　　合理地使用视图

　　　　视图是非常方便的工具，我们在日常工作中经常使用

　　　　但是，如果没有经过深入思考就定义复杂的视图，可能会带来巨大的性能问题

　　　　特别是视图的定义语句中包含以下运算的时候，SQL 会非常低效，执行速度也会变得非常慢

总结

　　文中虽然列举了几个要点，但其实优化的核心思想只有一个，那就是找出性能瓶颈所在，然后解决它

　　其实不只是数据库和 SQL，计算机世界里容易成为性能瓶颈的也是对硬盘，也就是文件系统的访问（因此可以通过增加内存，或者使用访问速度更快的硬盘等方法来提升性能）

　　不管是减少排序还是使用索引，亦或是避免临时表的使用，其本质都是为了减少对硬盘的访问

　　小结下文中的 Tips

　　　　1、参数是子查询时，使用 EXISTS 或者 JOIN 代替 IN

　　　　2、在 SQL 中，很多运算都会暗中进行排序，尽量规避这些运算

　　　　3、SQL 的书写，尽量往索引上靠，避免用不上索引的情况

　　　　4、尽量减少使用中间表

参考

　　《SQL进阶教程》

神奇的 SQL 之性能优化 → 让 SQL 飞起来

标签：性能优化   logs   innodb   table   values   查找   har   集合运算   drop