?? 作者：韓信子@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可以提高效率，

?? 低效代碼

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 速查表：https://www.showmeai.tech/article-detail/99

推薦閱讀

• ?? 資料分析實戰系列 ：https://www.showmeai.tech/tutorials/40
• ?? 機器學習資料分析實戰系列：https://www.showmeai.tech/tutorials/41
• ?? 深度學習資料分析實戰系列：https://www.showmeai.tech/tutorials/42
• ?? TensorFlow資料分析實戰系列：https://www.showmeai.tech/tutorials/43
• ?? PyTorch資料分析實戰系列：https://www.showmeai.tech/tutorials/44
• ?? NLP實戰資料分析實戰系列：https://www.showmeai.tech/tutorials/45
• ?? CV實戰資料分析實戰系列：https://www.showmeai.tech/tutorials/46
• ?? AI 面試題庫系列：https://www.showmeai.tech/tutorials/48

標籤：MySQL

上一篇：MySQL 間隙鎖導致的死鎖場景分析

下一篇：Oracle中ALTER TABLE的五種用法（四、五）