私藏！资深数据专家SQL效率优化技巧
  • 作者：韩信子@ShowMeAI
  • 数据剖析实战系列：www.showmeai.tech/tutorials/4…
  • 本文地址：www.showmeai.tech/article-det…
  • 声明：版权一切，转载请联系平台与作者并注明出处
  • 收藏ShowMeAI检查更多精彩内容
私藏！资深数据专家SQL效率优化技巧

一切的数据相关工作人员，包括数据开发、数据剖析师、数据科学家等，多多少少会运用数据库，咱们很多的事务数据也是存放在事务表中。但即使是同一个需求，不同人写出的 SQL 功率上也会有很大不同，而咱们在数据岗位面试的时分，也会考察相关的技术和考虑，在本篇文章中，ShowMeAI将给咱们梳理 SQL 中能够用于优化功率和提速的中心要求。

私藏！资深数据专家SQL效率优化技巧

关于 SQL 的基础技术常识，欢迎咱们查阅ShowMeAI制作的速查表：

编程语言速查表 | SQL 速查表

1）运用正则regexp_like替代LIKE

如下例所示，当咱们要进行模糊匹配的时分（尤其是匹配项很多的时分），咱们运用regexp_like替代LIKE能够进步功率。

低效代码

SELECT *
FROM phones
WHERE
    lower(name) LIKE '%samsing&' OR
    lower(name) LIKE '%apple&' OR
    lower(name) LIKE '%htc&' OR

高效代码

SELECT *
FROM phones
WHERE
    REGEXP_LIKE(lower(name),'samsung|apple|htc')

2）运用regexp_extract替代 Case-when Like

相似的，运用regexp_extract替代Case-when Like能够进步功率。

低效代码

SELECT *
CASE
    WHEN concat(' ', name, ' ') LIKE '%acer%' then 'Acer' 
    WHEN concat(' ', name, ' ') LIKE '%samsung%' then 'Samsung'
    WHEN concat(' ', name, ' ') LIKE '%dell%' then 'Dell'
AS brand
FROM laptops

高效代码

SELECT
      regexp_extract(name,'(acer|samsung|dell)')
AS brand
FROM laptops

3）IN子句转换为暂时表

但咱们进行数据挑选时分，有时分会用到in作为条件挑选，假如咱们的候选项十分多，那运用暂时表可能会带来更好的功率。

低效代码

SELECT *
FROM table1 as t1
WHERE
     itemid in (3363134, 5343, 5555555)

高效代码

SELECT *
FROM table 1 as t1
JOIN (
      SELECT
           itemid
      FROM (
            SELECT
                 split('3363134, 5343, 5555555') as bar
           )
           CROSS JOIN
                   UNNEST(bar) AS t(itemid)
      ) AS table2 as t2
ON
  t1.itemid = t2.itemid

4）将 JOIN 的表从大到小排序

当咱们要进行表相关（join）的时分，咱们能够对表根据巨细进行一个排序，把大表排在前面，小表排在后边，也会带来功率的提升。

低效代码

SELECT *
FROM small_table
JOIN large_table
ON small_table.id = large_table.id

高效代码

SELECT *
FROM large_table
JOIN small_table
ON small_table.id = large_table.id

5）运用简略的表相关条件

假如咱们要根据条件对两个表进行衔接，那条件中尽量不要出现杂乱函数，假如一定需求运用，那咱们能够先用函数对表的数据处理产出用于衔接的字段。

如下例中，咱们对a和b表进行衔接，条件是b表的「年」「月」「日」拼接后和a表的日期一致，那粗糙的写法和优化的写法分别如下：

低效代码

SELECT *
FROM table1 a
JOIN table2 b
ON a.date = CONCAT(b.year, '-', b.month, '-', b.day)

高效代码

SELECT *
FROM table1 a
JOIN (
     SELECT name, CONCAT(b.year, '-', b.month, '-', b.day) as date
     FROM table2 b
) new
ON a.date = new.date

6）分组的字段依照类别取值品种数排序

假如咱们需求对数据依照多个字段分组，尤其是字段中有id类这种取值十分多的类别字段，咱们应当把它排在最前面，这也能够对功率有一些协助。

低效代码

SELECT
  main_category,
  sub_category,
  itemid
  sum(price)
FROM
  table1
GROUP BY
  main_category, sub_category, itemid

高效代码

SELECT
  main_category,
  sub_category,
  itemid
  sum(price)
FROM
  table1
GROUP BY
  itemid, sub_category, main_category

7）避免 WHERE 子句中的子查询

当咱们要查询的句子的where条件中包含子查询时，咱们能够经过with句子构建暂时表来调整衔接条件，提升功率，如下：

错误代码

SELECT sum(price)
FROM table1
WHERE itemid in (
         SELECT itemid
         FROM table2
)

好代码

WITH t2
     AS (SELECT itemid
         FROM   table2)
SELECT Sum(price)
FROM   table1 AS t1
       JOIN t2
         ON t1.itemid = t2.itemid

8）取最大直接用Max而非Rank后取第1

这一条很好理解，假如咱们要取某字段最大取值，咱们直接运用 max，而不要用 rank 排序后取第 1，如下代码所示：

低效代码

SELECt *
FROM (
     SELECT userid, rank() over (order by prdate desc) as rank
     FROM table 1
)
WHERE ranking = 1

高效代码

SELECT userid, max(prdate)
FROM table1
GROUP BY 1

9）其他优化点

  • 关于大表，运用approx_distinct()替代count(distinct)来计数。
  • 关于大表，运用approx_percentie(metric,0.5)替代median。
  • 尽可能避免运用UNION。

参考资料

  • 编程语言速查表 | SQL 速查表：www.showmeai.tech/article-det…

私藏！资深数据专家SQL效率优化技巧

本文正在参与「金石方案 . 瓜分6万现金大奖」