私藏！資深資料專家SQL效率最佳化技巧 ⛵

ShowMeAI發表於2022-12-01
SQL
私藏！資深資料專家SQL效率最佳化技巧 ⛵

? 作者：韓信子@ShowMeAI
? 資料分析實戰系列：https://www.showmeai.tech/tutorials/40
? 本文地址：https://www.showmeai.tech/article-detail/391
? 宣告：版權所有，轉載請聯絡平臺與作者並註明出處
? 收藏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效率最佳化技巧 ⛵

相關文章