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

写在前面在像 Web 服务这样需要快速响应的应用场景中， SQL 的性能直接决定了系统是否可以使用；特别在一些中小型应用中， SQL 性能更是决定服务能否快速响应的唯一标准
严格地优化查询性能时，必须要了解所使用数据库的功能特点，此外，查询速度慢并不只是因为 SQL 语句本身，还可能是因为内存分配不佳、文件结构不合理、刷脏页等其他原因
因此本文即将介绍的优化 SQL 的方法不能解决所有的性能问题，但是却能处理很多因 SQL 写法不合理而产生的性能问题
下文将尽量介绍一些不依赖具体数据库实现，使 SQL 执行速度更快、消耗内存更少的优化技巧，只需调整 SQL 语句就能实现的通用的优化Tip
环境准备下文所讲的内容是从 SQL 层面展开的，而不是针对某种特性的数据库，也就是说，下文的内容基本上适用于任何关系型数据库
但是，关系型数据库那么多，逐一来演示示例了，显然不太现实；我们以常用的 MySQL 来进行示例的演示
MySQL 版本： 5.7.30-log ，存储引擎： InnoDB
准备两张表： tbl_customer 和 tbl_recharge_record

文章插图

使用高效的查询针对某一个查询，有时候会有多种 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 使用起来确实简单，也非常好理解；我们来看下它的执行计划

文章插图
【神奇的 SQL 之性能优化 → 让 SQL 飞起来】
我们再来看看 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 一样的性能