经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置:技术经验 » 程序设计 » SQL语言 » 查看文章
私藏!资深数据专家SQL效率优化技巧 ?
来源:cnblogs  作者:ShowMeAI  时间:2022/12/2 11:02:20  对本文有异议

?? 作者:韩信子@ShowMeAI
?? 数据分析实战系列https://www.showmeai.tech/tutorials/40
?? 本文地址https://www.showmeai.tech/article-detail/391
?? 声明:版权所有,转载请联系平台与作者并注明出处
?? 收藏ShowMeAI查看更多精彩内容

所有的数据相关工作人员,包括数据开发、数据分析师、数据科学家等,多多少少会使用数据库,我们很多的业务数据也是存放在业务表中。但即使是同一个需求,不同人写出的 SQL 效率上也会有很大差别,而我们在数据岗位面试的时候,也会考察相关的技能和思考,在本篇文章中,ShowMeAI将给大家梳理 SQL 中可以用于优化效率和提速的核心要求。

关于 SQL 的基础技能知识,欢迎大家查阅ShowMeAI制作的速查表:

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

?? 1)使用正则regexp_like代替LIKE

如下例所示,当我们要进行模糊匹配的时候(尤其是匹配项很多的时候),我们使用regexp_like代替LIKE可以提高效率。

?? 低效代码

  1. SELECT *
  2. FROM phones
  3. WHERE
  4. lower(name) LIKE '%samsing&' OR
  5. lower(name) LIKE '%apple&' OR
  6. lower(name) LIKE '%htc&' OR

?? 高效代码

  1. SELECT *
  2. FROM phones
  3. WHERE
  4. REGEXP_LIKE(lower(name),'samsung|apple|htc')

?? 2)使用regexp_extract代替 Case-when Like

类似的,使用regexp_extract代替Case-when Like可以提高效率。

?? 低效代码

  1. SELECT *
  2. CASE
  3. WHEN concat(' ', name, ' ') LIKE '%acer%' then 'Acer'
  4. WHEN concat(' ', name, ' ') LIKE '%samsung%' then 'Samsung'
  5. WHEN concat(' ', name, ' ') LIKE '%dell%' then 'Dell'
  6. AS brand
  7. FROM laptops

?? 高效代码

  1. SELECT
  2. regexp_extract(name,'(acer|samsung|dell)')
  3. AS brand
  4. FROM laptops

?? 3)IN子句转换为临时表

但我们进行数据选择时候,有时候会用到in作为条件选择,如果我们的候选项非常多,那利用临时表可能会带来更好的效率。

?? 低效代码

  1. SELECT *
  2. FROM table1 as t1
  3. WHERE
  4. itemid in (3363134, 5343, 5555555)

?? 高效代码

  1. SELECT *
  2. FROM table 1 as t1
  3. JOIN (
  4. SELECT
  5. itemid
  6. FROM (
  7. SELECT
  8. split('3363134, 5343, 5555555') as bar
  9. )
  10. CROSS JOIN
  11. UNNEST(bar) AS t(itemid)
  12. ) AS table2 as t2
  13. ON
  14. t1.itemid = t2.itemid

?? 4)将 JOIN 的表从大到小排序

当我们要进行表关联(join)的时候,我们可以对表基于大小进行一个排序,把大表排在前面,小表排在后面,也会带来效率的提升。

?? 低效代码

  1. SELECT *
  2. FROM small_table
  3. JOIN large_table
  4. ON small_table.id = large_table.id

?? 高效代码

  1. SELECT *
  2. FROM large_table
  3. JOIN small_table
  4. ON small_table.id = large_table.id

?? 5)使用简单的表关联条件

如果我们要基于条件对两个表进行连接,那条件中尽量不要出现复杂函数,如果一定需要使用,那我们可以先用函数对表的数据处理产出用于连接的字段。

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

?? 低效代码

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

?? 高效代码

  1. SELECT *
  2. FROM table1 a
  3. JOIN (
  4. SELECT name, CONCAT(b.year, '-', b.month, '-', b.day) as date
  5. FROM table2 b
  6. ) new
  7. ON a.date = new.date

?? 6)分组的字段按照类别取值种类数排序

如果我们需要对数据按照多个字段分组,尤其是字段中有id类这种取值非常多的类别字段,我们应当把它排在最前面,这也可以对效率有一些帮助。

?? 低效代码

  1. SELECT
  2. main_category,
  3. sub_category,
  4. itemid
  5. sum(price)
  6. FROM
  7. table1
  8. GROUP BY
  9. main_category, sub_category, itemid

?? 高效代码

  1. SELECT
  2. main_category,
  3. sub_category,
  4. itemid
  5. sum(price)
  6. FROM
  7. table1
  8. GROUP BY
  9. itemid, sub_category, main_category

?? 7)避免 WHERE 子句中的子查询

当我们要查询的语句的where条件中包含子查询时,我们可以通过with语句构建临时表来调整连接条件,提升效率,如下:

?? 错误代码

  1. SELECT sum(price)
  2. FROM table1
  3. WHERE itemid in (
  4. SELECT itemid
  5. FROM table2
  6. )

?? 好代码

  1. WITH t2
  2. AS (SELECT itemid
  3. FROM table2)
  4. SELECT Sum(price)
  5. FROM table1 AS t1
  6. JOIN t2
  7. ON t1.itemid = t2.itemid

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

这一条很好理解,如果我们要取某字段最大取值,我们直接使用 max,而不要用 rank 排序后取第 1,如下代码所示:

?? 低效代码

  1. SELECt *
  2. FROM (
  3. SELECT userid, rank() over (order by prdate desc) as rank
  4. FROM table 1
  5. )
  6. WHERE ranking = 1

?? 高效代码

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

?? 9)其他优化点

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

参考资料

推荐阅读

原文链接:https://www.cnblogs.com/showmeai/p/16934844.html

 友情链接:直通硅谷  点职佳  北美留学生论坛

本站QQ群:前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

W3xue 的所有内容仅供测试,对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接(目前9元/月)请联系QQ:27243702 沸活量
皖ICP备17017327号-2 皖公网安备34020702000426号