SQL优化策略

SQL优化策略

---

常用优化

---

当我们执行 SQL 查询语句时，数据库的操作是先根据 SQL 语句从硬盘中获取数据，然后加载进内存中进行后续计算。由于 SQL 语句有执行的先后顺序，因此当 SQL 一开始查询的数据量越小，加载进内存时间越短，并且整个 SQL 执行也越快，基本优化如下所示。

• 明确字段，不取多余解释，只取需要的字段，不用的不取，不随意使用 “*” 来查询全部

  例如：假设 A 表总共有 10 个字段，只需要用到其中两个字段，那么查询语句中只取我们需要 的两个字段，速度比查询所有字段的速度快。

• 大表关联，先做过滤。

  解释：大表关联查询语句中，做到先过滤再关联。不允许使用 WHERE 来进行关联。提前 过滤好，在硬盘和内存交互上、内存计算上可以节省很多时间。

• 提取共性，减少重复

  解释：SQL 语句中有多处重复的子查询，可以将其提取出来，进行参数化，减少数据库查询次数。

• 减少不必要的扫描计算

  解释：如果 UNION ALL 可满足要求，就使用 UNION ALL，而不用 UNION，因为 UNION 会 有一个比较然后去重的过程，而 UNION ALL 没有。

• 经常查询的大数据量表，需要创建索引，过滤和排序尽量放在索引列上操作

  解释：索引会提高 SELECT 效率，但是会影响到 INSERT 及 UPDATE 的效率。

• 区间范围比较（特别是索引列比较）要有明确边界，降低比较时的计算精度

  解释：用>=、<=代替>、<，。在取值区间±0.00001（或者其他足够小的值）即可实现代替，例如：

  1. 数值比较，A>1 替换成 A>=1.00001
  2. 时间比较，A>2019-09-01 00:00:00 替换成 A>=2019-09-01 00:00:00+0.0001
  3. 日期比较，A>2019-09-01 替换成 A>=2019-09-02

  如果不做替换，数据库计算 A>1 时，可能会一直计算到 A>1.000000000000 才能得出 A>1 的结论，这是浪费计算时间。

• 避免显式转换、隐式转换导致索引失效 解释：避免数据库运算时对索引列进行转换

  例如：

  （1）显示转换举例，假设索引列为日期型数据，和字符串型日期比较。 错误写法：TO_CHAR(T.索列,‘YYYYMMDD’)>=‘20190715’，这里是用函数将索引列由日期类型转换为字符串类型，导致索引失效。正确写法：T.索引列 >=TO_DATE(‘20190715’,’YYYY-MM-DD’)

  （2）隐式转换举例，假设索引列是一串字符串型的数字，取索引列值为 1 的数据。 错误写法：T.索引列=1 这里是数据库用隐式转换将索引由字符串类型转换成数值类型，导致索引失效。 正确写法：T.索引列=’1’

• 大表查询时，避免子查询中的排序计算，排序需放在执行计划最后一步

  解释：尽量在子查询中避免 ORDER BY、DISTINCT 等语句。其中 ORDER BY 是对结果进行排序，而 DISTINCT 和 GROUP BY 是在计算过程中排序。子查询数据量较大时用 EXISTS 子句 代替 DISTINCT。

高阶优化

---

数据量非常大的情景下，SQL 优化已经无法满足性能要求时，可以考虑以下的方法

• 分区分表

  解释：将一个表分成若干部分，减少单次扫描的数据量，提升效率，例如将五六年的数据按年分表存储，单次查询只扫描一年的数据，但是跨年分析会有影响；

• 数据压缩

  解释：即做数据预处理，先定期在数据库里计算好，然后报表中直接取结果数据来展示， 例如将每分每秒的数据汇总成一天一条记录，这种就是做数仓/数据集市；

• 硬件提速

  解释：即用 GP、HANA 等高性能数据库，提高数据运算速度。