卷首語
歡迎來到本期的《驅動力》雜志！這個月我想和大家分享一些關于SQL調優和SQL書寫技巧的知識。
作為一家互聯網公司數據部門的員工，我相信大家對SQL并不陌生。SQL（Structured Query Language）是一種用于管理和操作關系型數據庫的編程語言。在數據的處理過程中，優化SQL的性能是至關重要的。通過調優SQL語句，我們可以提高數據庫的查詢速度和效率，從而提升整個系統的性能。
在SQL調優方面，有一些常見的技巧和建議可以幫助我們優化查詢語句。首先，我建議大家利用在線學習資源來入門學習SQL。一些在線學習平臺如w3school、xuesql.cn自學SQL網、牛客網、sqlzoon等提供了豐富的學習材料和練習題，特別適合SQL的初學者。通過在線題庫的學習，你可以系統地了解SQL的基礎知識，并通過練習不斷提升自己的技能。
另外，在實際使用中，我們也可以注意一些SQL的書寫技巧。比如，在新建臨時表時，如果一次性插入數據量很大，可以使用SELECT INTO代替CREATE TABLE，以提高速度。此外，在編寫SQL語句時，注意使用合適的索引、避免不必要的數據類型轉換和重復的查詢操作，這些都能夠有效提升SQL的執行效率。
除了調優，我還想強調SQL的書寫技巧對于數據處理的重要性。準確、簡潔地書寫SQL語句能夠增加代碼的可讀性和可維護性。在編寫SQL語句時，我們可以通過使用別名、避免使用SELECT *、合理命名字段和表等方式來提高代碼的可讀性。此外，良好的注釋和文檔也是編寫SQL的重要概念，能夠提高代碼的可理解性和團隊之間的協作效率。
在本期的企業內部雜志中，我們收到了部門內小伙伴的熱情響應，分享他們的經驗和觀點。希望通過這些寶貴的經驗，能夠幫助大家更好地理解和應用SQL調優和SQL書寫技巧。
最后，我希望我們的讀者朋友們能夠從本期的電子雜志中收獲到有價值的知識和經驗。如果你對SQL調優和SQL書寫技巧有什么問題或者想法，歡迎線下與我繼續交流，如有必要可以考慮把SQL調優作為我們的固定板塊。我們期待聽到你們的聲音！
目錄
01‖ SQL調優技巧——知其所以然
02‖關于Left join，你可能不知道這些
03‖Spark SQL任務調優
04‖SQL常用函數及避坑點匯總『Hive系列』
05‖HiveSQL優化技巧
06‖SQL優化思路+經典案例分析
07‖MySQL常用30種SQL查詢語句優化方法
驅動力
? SQL調優技巧——知其所以然
? SQL調優技巧&知其所以然
我們在使用SQL的時候不僅僅要關注數據結果也要注意執行效率
一、首先知道一條SQL的執行順序，通常是按照如下的順序執行的
? 計算機屏幕保護
二、了解MySQL的運行結構
「原創作品」? ?作者：秦令達
- FROM：確定要查詢的表以及它們的連接方式。這個步驟決定了查詢的主要數據集。
- JOIN：如果查詢涉及多個表之間的連接，那么在這一步中會執行連接操作。連接操作基于連接條件將不同表的行匹配起來，生成一個合并的結果集。
- WHERE：應用查詢條件，過濾掉不符合條件的行。只有符合WHERE條件的行才會被保留下來。
- GROUP BY：如果查詢包含GROUP BY子句，那么在這一步中將按照指定的列對結果進行分組。相同值的行會被分為一組。
- HAVING：應用HAVING條件，過濾掉不符合條件的分組。只有符合HAVING條件的分組才會被保留下來。
- SELECT：選擇要返回的列。在這一步中，執行函數、計算表達式以及對列進行重命名等操作。
- DISTINCT：如果查詢包含DISTINCT關鍵字，那么在這一步中去除重復的行。
- ORDER BY：如果查詢包含ORDER BY子句，那么在這一步中按照指定的列對結果進行排序。
- LIMIT：如果查詢包含LIMIT子句，那么在這一步中限制返回的行數。
- connection也是采取線程方式執行sql
- 對sql進行解析、優化 （1: 查詢磁盤把mysql中的全部數據都找一遍 2:看是不是走索引）
三、存儲引擎
這里我們只說常用的InnoDB，提供讀寫接口，數據本身在磁盤上面
驅動力
? SQL調優技巧——知其所以然
? 計算機屏幕保護
1、查詢的時候：先將數據從磁盤（ibd）加載到內存（Buffer Pool），下次再查就再再內存中直接查詢了ibd文件如下
一條記錄就占用：128Byte，比如一次查詢就查詢一個block（一個磁盤中的扇區）
一共有100條記錄，就需要查詢：100*128/512=25次
但是如果我們創建索引的話：
2、修改的時候：先在內存（Buffer Pool）中修改，產生redo log 日志，當內存中的緩存空間不足時(LRU)，被標記的修改數據就同步到磁盤中去了（當需要騰空間時就從flush-list里把需要同步的數據同步到磁盤中 ）
下面是存儲引擎從磁盤查詢數據的一個過程
InnoDB 以page為單位，將數據從磁盤中讀取出來
- InnoDB中page大小默認為16KB
- 操作系統中的page大小為4KB
- 機械硬盤扇區512Byte
- SSD的page大小為4KB
通常我們為了查詢的更快進行創建索引：目的就是為了減少磁盤查詢，空間雖然占用了多，但是查詢次數變少，數據查詢速度變快
● 下面來解釋一下創建索引的必要性：
比如一張用戶表：?
| 字段? | 占用字節大小 |
| user_id | 10 |
| user_name | 20 |
| password | 20 |
| remark | 20 |
| nick_name | 58 |
驅動力
? SQL調優技巧——知其所以然
? 計算機屏幕保護
假設一條索引的大小為16B（真實可能比這還小），那么一個block就可以查詢 512/16=32個索引， 100條也就是查詢： 100/32 = 4次
在索引的情況下查詢最多次數： 4次索引磁盤的查詢+1次數據磁盤的查詢=5 次，與沒有加索引的25次來比快很多，數據量越大索引的優勢越明顯
當然也有一個問題，當索引數量越來越多的時候，看下InnoDB是怎么做的：
用了B+Tree來實現了給索引建立索引（這里就不詳細的展開說：二叉樹->多路查找樹->B-Tree->B+Tree的過程了，如果有興趣可以線下一起交流），直接來說B+Tree：
它是以block塊來建立索引的，第一次訪問的根節點之后就不會變了，當block滿了就開始進行升級：
這張表是336w條數據
查詢一條數據：
select * from operational_efficiency_access_ods where FEAT_PLATFORM_CUS_NO ='10025103944' and CUSTOMERNUMBER ='10089513727';
因為所有數據存在葉子結點，讀取的子節點可以存儲更多的索引key（線索），數據做順序讀取時性能也更好（葉子節點的數據是有鏈表關系）
四、查詢優化器
查詢優化器： 找到執行SQL查詢的最佳計劃，根據表、列、索引的詳細信息以及SQL語句的條件，讓SQL進行高效的工作，簡而言之就是影響where條件中的執行順序，SQL語句最終真實執行的就是執行計劃，通過執行計劃來看SQL查詢低效的原因
用我們現在正在做的CREM功能中涉及到的一張表來做例子：
驅動力
? SQL調優技巧——知其所以然
? 計算機屏幕保護
看這條SQL的執行的詳細信息：
select trace from information_schema.OPTIMIZER_TRACE;
看下面優化器的詳細信息：有3個重要的點
先進行了準備會將sql補全：join_preparation
再進行了優化會選擇最優的索引方式：join_optimization
具體執行：join_execution
優化器詳細信息如下：（點擊圖片放大預覽）
2、is null 和 is not null 會不會走索引？
不絕對，看優化器的成本分析來決定
1、like會不會走索引
不會，下面2張圖片對比
五、SQL優化
針對為什么直接說sql優化的例子沒有說explain（以后補充上，肝不完了，我認為其實了解了原理具體怎么做就大同小異了）
表的ddl:（點擊圖片放大預覽）
驅動力
? SQL調優技巧——知其所以然
? 計算機屏幕保護
3、索引函數計算會不會走索引？
需要看查詢字段是否為后面where中的索引字段
explain select CUSTOMERNUMBER from operational_efficiency_access_ods where FROM_UNIXTIME(FEAT_FINAL_XS_TIME, '%Y-%m-%d') = '2021-01-04';
5、多列索引，順序反了會不會走索引？
會， 但是需要遵循最左前綴
alter table operational_efficiency_access_ods add index idx_duo_index(CUSTOMERNUMBER,TASK_ID,FEAT_SASS_CUS_NO);
explain select * from operational_efficiency_access_ods where CUSTOMERNUMBER = '10088774372' and TASK_ID='TYSHRW20220901125821215126' and FEAT_SASS_CUS_NO ='10027250870';
4、類型不一致會走索引嗎
會走索引，但是還是需要看查詢字段 ，優化器的最終執行計劃
explain select FEAT_FINAL_XS_TIME from operational_efficiency_access_ods where FEAT_FINAL_XS_TIME > '1591025358';
explain select * from operational_efficiency_access_ods where TASK_ID='TYSHRW20220901125821215126' and FEAT_SASS_CUS_NO ='10027250870' ;
驅動力
? SQL調優技巧——知其所以然
? 計算機屏幕保護
6、查詢的數據太多會不會走索引？
explain select * from operational_efficiency_access_ods where FEAT_PLATFORM_CUS_NO ='10086249365';
?會走索引,具體還是看查詢優化器，因為查詢優化器是不斷的優化
explain select * from operational_efficiency_access_ods where FEAT_PLATFORM_CUS_NO ='10086249365'
union
select * from operational_efficiency_access_ods where TASK_ID = 'SHTYRW2493530732';
7、使用union還是OR好？
?union好一些，成本更低，具體看下面的展示
explain select * from operational_efficiency_access_ods where FEAT_PLATFORM_CUS_NO ='10086249365' OR TASK_ID = 'SHTYRW2493530732';
成本為：
參考資料：
官網地址 //dev.mysql.com/doc/
認為比較不錯的文章
//mp.weixin.qq.com/s/7UxDqwJi9ChR8OfIQwpWWw
驅動力
left join 通俗的解釋：以左表為主表，返回左表的所有行，如果右表中沒有匹配，則依然會有左表的記錄，右表字段用null填充。看起來非常好理解，但實際操作的過程中可能會有一些很容易被忽略的點。
一、left join 之后的記錄有幾條
關于這一點，是要理解left join執行的條件。在A join B的時候，我們在on語句里指定兩表關聯的鍵。只要是符合鍵值相等的，都會出現在結果中。這里面有一對一，一對多，多對多等幾種情況。我們用例子來說明。
1.一對一
這種情況最好理解。t_name表，有id，name(用戶名稱)，sex(性別)，dt(注冊日期)等字段。t_age表。有id，age(年齡)，province(省份)，dt(更新日期)等字段。表中包含的信息如下：
現在我們進行t_name(左表，別名a)和t_age(右表，別名b)的left join 操作，關聯鍵為id。a表有6條記錄，b表有3條記錄，且關鍵的鍵是唯一的，因此最終結果以a表為準有6條記錄，b表有3條關聯不上，相應的記錄中，b表所有的字段都為空。
2.一對多
這回我們用t_age作為左表，關聯條件為dt。重點看dt為20190905的記錄。由于右表有3條20190905，這三條在關聯的時候都滿足關聯條件，因此最終的結果會有3條記錄是20190905。
? 關于Left join，你可能不知道這些
文章來源：知乎 作者：超哥要努力
原文鏈接：//zhuanlan.zhihu.com/p/85856388
join 是 SQL查詢中很常見的一種操作，具體來講有join，left join, right join，full join等很多形式。具體的原理如下圖所示。但其中最常見的還是使用left join 。
本文代碼在mysql和hive中均測試通過，代碼本身難度和長度都不大，我準備了測試數據的mysql和hive代碼，如果覺得有必要，你可以在公眾號后臺回復“left”獲取，方便自己修改和練習。
驅動力
2和3中我們看到了一對多和多對多的情況，其實前者是后者的特例。我們只是很簡要的把兩個表關聯之后所有的字段都列出來了，但實際中可能需要做一些統計，聚合等。這里提醒大家在寫關聯條件之前，最好思考一下最終的結果是什么樣的，最終可能有幾行，會不會在計數的時候多統計，哪些行可能會存在空值，哪些字段可能會存在空值等。不要因為想當然而犯了錯誤。這里算是拋磚引玉，感興趣的同學可以看看這篇博客，進一步學習，
//www.cnblogs.com/qdhxhz/p/10897315.html
二、left join 的執行原理
接下來我們進一步看一下連接條件寫在on里和寫在where里的區別。在這之前，我們可以看看left join的具體執行邏輯。我參考了網上以為大神的博客：
//developer.aliyun.com/article/718897，總結如下
mysql采用嵌套循環的方式處理left join。
SELECT * FROM LT LEFT JOIN RT ON P1(LT,RT)) WHERE P2(LT,RT)
其中P1是on過濾條件，缺失則認為是TRUE，P2是where過濾條件，缺失也認為是TRUE，該語句的執行邏輯可以描述為：
FOR each row lt in LT {// 遍歷左表的每一行
BOOL b = FALSE;
FOR each row rt in RT such that P1(lt, rt) {// 遍歷右表每一行，找到滿足join條件的行
IF P2(lt, rt) {//滿足 where 過濾條件
t:=lt||rt;//合并行，輸出該行
}
b=TRUE;// lt在RT中有對應的行
}
IF (!b) { // 遍歷完RT，發現lt在RT中沒有有對應的行，則嘗試用null補一行
IF P2(lt,NULL) {// 補上null后滿足 where 過濾條件
t:=lt||NULL; // 輸出lt和null補上的行
}
}
}
如果代碼看不懂，直接看結論就好：
這回為準的表是t_age表，但顯然結果并不是原本的3條記錄，而是7條：20190905 3條，20190906 4條。如果你不太理解，可以繼續往下看。
3.多對多
上面例子中，20190906的記錄最終有4條，同樣是因為滿足了關聯條件，是一種2對2的情況。這里我們還是回到t_name表做主表的情況，用dt來關聯。可以預見，與2中相比，這次結果中會多一行20190907的，而b表相應的字段依然為空。
驅動力
2.有2個on條件
- 如果想對右表進行限制，則一定要在on條件中進行，若在where中進行則可能導致數據缺失，導致左表在右表中無匹配行的行在最終結果中不出現，違背了我們對left join的理解。因為對左表無右表匹配行的行而言，遍歷右表后b=FALSE,所以會嘗試用NULL補齊右表，但是此時我們的P2對右表行進行了限制，NULL若不滿足P2(NULL一般都不會滿足限制條件，除非IS NULL這種)，則不會加入最終的結果中，導致結果缺失。
- 如果沒有where條件，無論on條件對左表進行怎樣的限制，左表的每一行都至少會有一行的合成結果，對左表行而言，若右表若沒有對應的行，則右表遍歷結束后b=FALSE，會用一行NULL來生成數據，而這個數據是多余的。所以對左表進行過濾必須用where。
我們再來看看實例，返回來研究這段話可能更好理解一些。
1.只有1個on條件
這里可以直接看第一部分中的例子。最終會輸出以左表為準，右表匹配不上補null的結果，但可能會有多對多的情況。
上圖是在關聯條件中增加了b.age=24之后的輸出結果。由于對b表進行了限制，滿足條件的只有一個，但是由于沒有where條件，因此依然要以左表為準，又因為是一對一，所以輸出還是左表的記錄數。更極端的，我們可以“清空”b表。
以上兩種情況，在b表中都沒有符合條件的結果，因此在以左表為準的基礎上，右邊的所有字段都為空。
3.有where的情況
驅動力
當條件寫在where 中：
將b.age=24寫到where里，發現結果中只有這一行，打破了“left join”以左表為主的限制。同樣再來看下后兩種情況寫到where里會發生什么：
沒錯，結果全部是為空的。因為where 在 on 后面執行，而on生成的結果里沒有滿足條件的記錄！
這里給出兩個結論：
- on條件是在生成臨時表時使用的條件，它不管on中的條件是否為真，都會返回左邊表中的記錄。
- where條件是在臨時表生成好后，再對臨時表進行過濾的條件。這時已經沒有left join的含義（必須返回左邊表的記錄）了，條件不為真的就全部過濾掉。
4.有is null 或者有 is not null的情況
當條件寫在on中：
直觀的我們理解，WHERE … IS NULL 子句將從匹配階段后的數據中過濾掉不滿足匹配條件的數據行。對于條件寫在on中的情況，又可以說，is null是否定匹配條件，is not null是肯定匹配條件。對于條件寫在where中的，其實相比之下更容易理解，要看已有的where條件產生的結果是什么。讀者可以從上面的例子中思考一下。
三、看兩個實際案例
經過上面的討論，我們來看兩個案例，進一步理解和思考一下left join 的用法。
1.案例1
這個案例來自于一篇網絡博客，前文有提到。鏈接：
//developer.aliyun.com/article/718897
驅動力
對于這個需求，我們可以使用left join進行自關聯，用之前活躍的天作為左表，最終期望計算的天作為右表，計算日期差，并進行左右表分別計數。初步的SQL如下：(數據是自己編的)
在往下看之前請確認你理解了需求目標，并先思考下，以上的寫法有問題嗎？能否得到上面期望的結果？
原始數據和這段SQL運行的結果如下：
大家可以先思考一下怎么寫再到原文看答案。事實上，每個需求都很容易有兩種寫法，區別就在于條件是寫在where中還是寫在on中。判斷的原則就是我們需要保證結果中數據不缺失也不多余。需求1的條件需要寫在on中(保證結果不缺失)，需求2的條件需要寫在where中(保證結果不多余)。
2.案例2
假設現在有一個用戶活躍表t_active，記錄了每天活躍的uid和相應的活躍日期。現在想要看距離某一天日期差為0天，1天，2天，3天…活躍的用戶在當天還有多少活躍(也就是一個留存的概念)。期望得到的如下表所示：
對于表中數據，我們可以這樣理解。距離2018-09-29 0天(也就是2018-09-29)的活躍人數為100,2018-09-29當天活躍的還剩100，距離2018-09-29 1天(也就是2018-09-28)的活躍人數為80,2018-09-29當天還剩60。以此類推。
驅動力
從上面的結果我們可以推演出最開始的SQL運行結果。例如，datediff=5的時候，共兩條記錄，左表右表的count(distinct uid)都為2。datediff為null的時候，左表結果為7，右表為0，其他的以此類推，和前面的結果是一樣的。這樣我們就知道了，沒有達到預期的根源在于存在空的日期。那么怎么解決這個問題呢，顯然就是把空日期填補上就可以了。可以使用case when 當右表日期關聯不上的時候，用相應日期補足。代碼如下：
可以看到最終得到了想要的結果，以最后一行為例，它表示，距離2018-09-29 5天的那天(也就是2018-09-24)活躍的人數有5個，那些人在2018-09-29仍然活躍的有2人，你可以看一下明細數據核對一下。其余的以此類推。我們使用case when 把日期寫死了，這個是建立在我們知道是哪天的基礎上的。實際中可能是一個變量，但一定也是一個固定的值，需要具體情況具體分析。
四、總結
本文我們學習了left join的原理和實踐中可能會遇到的問題。包括關聯時結果中的記錄數，關聯條件寫在on和where中的區別，where語句中存在is null的時候如何理解，最后用實例幫助大家進行理解。在此過程中參考了網上的一些博客，大家可以在閱讀本文的基礎上進行查閱。希望對你有所幫助！公眾號后臺回復“left”，可以獲取本文測試所用的數據集合代碼。
運行結果中出現了dt和datediff為null的情況，你能想象的到這是為什么嗎？而且當dt不為null的時候，最后兩列的數據是相同的，顯然和我們的預期不符。這是什么原因呢？我們來逐步看一下。
首先，我們使用left join 的方式應該是沒有問題的，我們先看看不加任何計算的，select * 的結果是啥。
可以看到，這相當于是前文提到的不加where 條件的一對一關聯，結果會以左表為準，關聯不上的用null補齊。值得注意的是，關聯不上的日期是null值，而null值在參與datediff的計算時，結果會是null。到這里你是不是明白一點了。由于null值參與計算，導致最終datediff 有null值，并且計數的時候，由于null值存在，最終用日期差作為維度的時候，導致左表和右表的數量是一樣的。如下面代碼所示：
驅動力
reference：
//developer.aliyun.com/article/718897
//blog.csdn.net/u013630349/article/details/71123409
//www.cnblogs.com/qdhxhz/p/10897315.html
//www.oschina.net/question/89964_65912
//blog.csdn.net/muxiaosha
? Spark SQL任務調優
「原創文章」 作者：何劍
關于優化前面想說的
在實際生產優化中，很多開發同學一上來就看sql執行資源分配夠不夠，數據有沒傾斜，與業務脫離的技術層通常摸不到業務的核心需要，這使得實際優化受限；比如一條任務跑了六七個小時，實際業務設計就是不合理的或者有更好更快的實現方式。
舉個例子，在解決一實際生產問題時，曾苦惱調整沒有好的實現方法，經溝通，該需求可以下線，無須處理；需求是指向人的，需求背后有更深層次的需求，作為一名資深的開發跟業務協調，更應該去琢磨業務需要，深層考量，而非僅僅是開發實現層面。
反過來，也會遇到這樣一類業務，做需求只講輸出不言背景。這樣一方面開發成長受限，協作表現可能不理想，一方面不能很好的調動開發資源，僅受限局限視角的具體實現。
1、對于基礎表結構，一般是事先設定好的；但對于數倉表設計也是我們需要考量的一個點，比如數倉設計，全量增量、不同顆粒度，良好的設計能節約很多計算存儲資源；
2、其次再就是語句合理性分析，這塊可以結合執行計劃讀，比如實現該需求，使用怎樣的邏輯處理會更快些，資源消耗會更少些；
3、最后再就是資源、參數優化。
如果表設計、語句、業務邏輯都沒啥問題的話，則重點考慮數據傾斜，資源配置。
* 本內容僅針對yarn調度環境，spark版本：2.4
Spark任務調優策略
現象一：運行時長 > 60min 的任務，或是資源消耗最多的 Top50 任務，或 spark web ui 上某個階段 Task 數特別多
驅動力
1、時間分區過濾條件：判斷任務的 sql 中是否有 hive 分區表，若有的話，判斷是否增加了正確的時間分區過濾條件。
2、縮小查詢的時間范圍：確認一下 sql 中查詢的時間范圍過濾條件是否過大，
若是的話，是否可以在符合業務需求的前提下，縮小查詢的時間范圍。
3、增量計算：確認一下 sql 是否是基于多天數據的全量計算，思考是否可改為基于時間的增量計算。
比如要拉取最近30天日活用戶和最后活躍日期。
當前數據倉庫有一張日活躍表，日分區；每次作業需要拉取近30天分區數據，數據量極大。
這時候可以建一張中間表，記錄近30天活躍用戶以及用戶最后活躍日期，庫表名：user_last_active_date
然后該中間表的修復任務，當天分區為，昨日數據剔除最后活躍日期在29天 + 當天活躍信息，按用戶去重
4、調整參數：根據 task 的數據字節大小，調整和設置 executor 的資源配置、調整最大并發數。
現象二：所有Task啟動時間差很多
如果你的任務本身并不是很大，SparkUI顯示Task任務啟動時間卻相差較大。這個現象說明Spark Job分配了很多Task, 但卻沒有足夠的并發能夠同時啟動這些Task執行。 由于分批啟動，即等部分executor中的Task釋放后，才會啟動等待的新一批Task，所以導致Spark執行時間較長。
可以的優化方向，通過調整Spark的并發，即 spark.executor.cores * num-executors。
現象三：大量的Executor中 GC 占比較高
1、先判斷 sql 的過濾條件是否有按業務需求正確添加。
2、判斷任務是否出現了數據傾斜嚴重，數據傾斜可能會導致某些任務消耗大量內存，進而引發頻繁的 GC。
3、如果資源配置的內存使用不合理，就有可能會導致頻繁的垃圾回收（GC）現象，從而降低應用程序的性能。一般來說，我們可以通過調整 spark.executor.memory 參數來增加每個 Executor 的內存分配。
現象四：多張表 Join， Join 后的中間階段的記錄條數仍然很多、基本沒有減少
1、調整join關聯的順序： 用其中一個條數最多的表，先跟其中一個條數最少的表做 Join；然后再跟其他表做 Join。
假設我們有這樣一條sql：
- t1表10億，
- t2表用戶維度維表：1億；
- t3表用于關聯過濾：20萬；
- t1表與t2表join的記錄行10億，再與t3表關聯的記錄行是1千萬；
查看執行計劃，計算順序是從上往下走，這時候可以調整join中表順序，先t1與t3表join，再與 t2 join。
驅動力
查看執行計劃，計算順序是從上往下走，這時候可以調整join中表順序，先t1與t3表join，再與 t2 join。
2、數據預聚合：對于在join過程中，子表如果提前聚合能顯著減少后續操作的記錄條數或數據量，應事先按 key 聚合。
現象五：任務每天跑多次，但又不是增量處理類的任務
通過 梳理數據處理鏈路、業務邏輯，整改為 增量處理類任務 或 低調度頻次的任務。
現象六：出現數據傾斜
判斷數據傾斜的方法非常簡單，通過Spark UI Stage的Summary的統計信息，是否小部分Task的shuffle writer和shuffle read數據量比較大。
在Spark2.4中， 嘗試開啟AQE 來解決。
Spark關鍵參數配置
1、Spark資源參數調優
（1）driver 資源配置
在開啟動態申請executor資源后，Spark會根據當前任務負載自動增加或減少Executor實例數量，以優化資源利用率。maxExecutors參數定義了分配給應用程序的Executor實例的上限數量，在申請資源時不會超過這個設定的上限。
推薦的 資源參數：
（2）executor 資源配置
- 單個executor資源配置
配置推薦的 資源參數后，任務運行不正常的，需要看下錯誤信息、具體任務具體分析。
如果判斷是因為 executor 內存不足，推薦用下一組 資源參數：
2、Spark性能參數調優
（1）join策略的參數
spark.sql.autoBroadcastJoinThreshold 參數用于控制 Spark SQL 中自動廣播連接（join）的閾值，默認是10MB。這意味著當一個表的大小小于該閾值時，Spark SQL 將自動選擇廣播連接，使用廣播連接可以大大的提升計算性能。如果你的小表大于10M，但又不是非常大的情況下，可以調整spark.sql.autoBroadcastJoinThreshold的數值，讓其大于小表的大小，那么在join計算時就會走廣播連接。
驅動力
但是有些情況下，即使你的小表較小但出現了大量的網絡連接失敗的日志或 broadcastTimeout，這時可能由于網絡擁塞或其他原因導致任務失敗，可以將spark.sql.autoBroadcastJoinThreshold=-1設置為-1禁用自動廣播join操作，提升計算任務的穩定性。
（2）啟動 Spark AQE （自適應查詢執行） 功能
Spark AQE功能，它將更好的提升Spark運行穩定性和性能。
他們細節配置如下所示。
AQE中實際上是包含了三大特性：
1. 動態合并 Shuffle Partitions：在 Shuffle 過后，Reduce Task 數據分布參差不齊，AQE 將自動合并過小的數據分區。
2. 動態調整Join策略：如果某張表在過濾之后，Byte Size 小于廣播變量閾值，這張表參與的數據 Join 就會從 Shuffle Sort Merge Join 降級 為執行效率更高的 Broadcast Hash Join。
3. 動態優化傾斜Join：結合配置項，AQE 自動拆分 Reduce 階段過大的數據分區，降低單個 Reduce Task 的工作負載。
00-序言
SQL是數據分析同學日常用到的查詢語言，Hive是基于Hadoop的數據倉庫工具，Hive提供了SQL的查詢功能，可將SQL轉化為MapReduce任務來執行。本文匯總了小火龍在工作中常用的Hive SQL函數，以及其中可能涉及到的一些坑，供大家參考學習。函數類型如下圖：
本文主要匯總「內置函數」的幾種類型，對于「用戶自定義函數」的創建，會在后面的文章中進行講解。
01--數值函數
「數值函數」主要是對int和float數據類型進行的處理。常用函數匯總如下：
? SQL常用函數及避坑點匯總『Hive系列1』
文章來源：小火龍說數據
原文鏈接：
//mp.weixin.qq.com/s/rixtQ9AcS-gQ9CtqCxBSCA
驅動力
避坑點
1、rand(x)：相同“種子”多次結果均一致；無“種子”任何一次結果均不一致。
02-字符串函數
「字符串函數」主要是對char數據類型進行的處理。常用函數匯總如下：
03-條件函數
「條件函數」主要是對字段進行判斷。常用函數匯總如下：
04-日期函數
「日期函數」主要是對日期進行處理，以及加減操作，在計算留存時經常使用。常用函數匯總如下：
05-關系函數
「關系函數」主要是對字段進行匹配。常用函數匯總如下：
驅動力
避坑點
1、「NULL」和「空字符串」是不一樣的，一般在數據庫中表現為，「NULL」顯示NULL，而「空字符串」顯示為空，在匹配的時候需要注意。
舉例：如果拿捏不好字段是哪種類型，并且需要去掉的情況，建議兩者一起去掉。A is not null and A !=''
2、 Like、Rlike、Regexp的區別：
- Like：通配符，不是正則。通配符涵蓋 % 和 _。
- Rlike：正則表達式，寫法與java一樣。
- Regexp：基本同Rlike。
「聚合函數」多行轉一行。常用函數匯總如下：
07-分拆函數
「分拆函數」一行轉多行。常用函數匯總如下：
08-窗口函數
「窗口函數」又稱OLAP函數（online analytical processing），完成類似聚合函數的計算效果，但是又保持每行的數據，不被聚合到一起。常用函數匯總如下：
避坑點
1、 rank( )over( )、dense_rank( )over( )、row_number( )over( )的區別
驅動力
2、sum( )over( )函數中有order by 和 無order by的區別
HQL語句優化
1、使用分區剪裁、列剪裁
在分區剪裁中，當使用外關聯時，如果將副表的過濾條件寫在Where后面，那么就會先全表關聯，之后再過濾。
select a.*
from test1 a
left join test2 b on a.uid = b.uid
where a.ds='2019-08-10'
and b.ds='2019-08-10'
上面這個SQL主要是犯了兩個錯誤：
- 副表的過濾條件寫在where后面，會導致先全表關聯在過濾分區；
- on的條件沒有過濾null值的情況，如果兩個數據表存在大批量null值的情況，會造成數據傾斜。
老生常談。hive的優化主要分為：配置優化、SQL語句優化、任務優化等方案。
其中在開發過程中主要涉及到的可能是SQL優化這塊。
優化的核心思想是：
- 減少數據量（例如分區、列剪裁）；
- 避免數據傾斜（例如加參數、Key打散）；
- 避免全表掃描（例如on添加加上分區等）；
- 減少job數（例如相同的on條件的join放在一起作為一個任務）。
以上就是本期的內容? ? ? ? ? ? ?分享，希望可以幫助你理清Hive SQL常用函數。碼字不? ? ? ? ? ? ? ? ? 易，如果覺得對你有一點點幫助，歡迎「關注」「點贊」? ? ? ? ? ? ? ? ?「分享」哦，我會持續為大家輸出優質的「原創內容」
? HiveSQL優化技巧
文章來源：數據倉庫與python大數據
原文鏈接：
//mp.weixin.qq.com/s/ZVXTratXxP7MWJntv0IGew
驅動力
select a.*
from test1 a
left join test2 b on (d.uid is not null and a.uid = b.uid and b.ds='2019-08-10')
where a.ds='2019-08-10'
如果null值也是需要的，那么需要在條件上轉換，或者單獨拿出來
select a.*
from test1 a
left join test2 b on (a.uid is not null and a.uid = b.uid and b.ds='2019-08-10')
where a.ds='2019-08-10'
union all
select a.* from test1 a where a.uid is null
或者
select a.*
from test1 a
left join test2 b on?
case when a.uid is null then concat("test",RAND()) else a.uid end = b.uid and b.ds='2019-08-10'
where a.ds='2019-08-10'
2、盡量不要用COUNT DISTINCT，因為COUNT DISTINCT操作需要用一個Reduce Task來完成，這一個Reduce需要處理的數據量太大，就會導致整個Job很難完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替換，雖然會多用一個Job來完成，但在數據量大的情況下，這個絕對是值得的。
select count(distinct uid)
from test
where ds='2019-08-10' and uid is not null
轉換為
select count(a.uid)
from?
(select uid from test where uid is not null and ds = '2019-08-10' group by uid) a
3、使用with as，因為拖慢hive查詢效率出了join產生的shuffle以外，還有一個就是子查詢，在SQL語句里面盡量減少子查詢。with as是將語句中用到的子查詢事先提取出來（類似臨時表），使整個查詢當中的所有模塊都可以調用該查詢結果。使用with as可以避免Hive對不同部分的相同子查詢進行重復計算。
或者（子查詢）
select a.*
from test1 a
left join?
(select uid from test2 where ds = '2019-08-10' and uid is not null) b on a.uid = b.uid
where a.uid is not null
and a.ds='2019-08-10'
驅動力
select a.*
from test1 a
left join test2 b on a.uid = b.uid
where a.ds='2019-08-10'
and b.ds='2019-08-10'
可以轉化為
with b?
as?
select uid
from test2
where ds = '2019-08-10' and uid is not null
select a.*
from test1 a
left join b on a.uid = b.uid
where a.ds='2019-08-10' and a.uid is not null
4、大小表的join，寫有Join操作的查詢語句時有一條原則：應該將條目少的表/子查詢放在Join操作符的左邊。原因是在Join操作的Reduce階段，位于Join操作符左邊的表的內容會被加載進內存，將條目少的表放在左邊，可以有效減少發生OOM錯誤的幾率。
但新版的hive已經對小表JOIN大表和大表JOIN小表進行了優化。小表放在左邊和右邊已經沒有明顯區別。
不過在做join的過程中通過小表在前可以適當的減少數據量，提高效率。
5、數據傾斜，數據傾斜的原理都知道，就是某一個或幾個key占據了整個數據的90%，這樣整個任務的效率都會被這個key的處理拖慢，同時也可能會因為相同的key會聚合到一起造成內存溢出。
數據傾斜只會發生在shuffle過程中。這里給大家羅列一些常用的并且可能會觸發shuffle操作的算子：distinct、 groupByKey、reduceByKey、aggregateByKey、join、cogroup、repartition等。出現數據傾斜時， 可能就是你的代碼中使用了這些算子中的某一個所導致的。
hive的數據傾斜一般的處理方案：
常見的做法，通過參數調優：
set hive.map.aggr=true;
set hive.groupby.skewindata = ture;
當選項設定為true時，生成的查詢計劃有兩個MapReduce任務。
在第一個MapReduce中，map的輸出結果集合會隨機分布到reduce中，每個reduce做部分聚合操作，并輸出結果。
這樣處理的結果是，相同的Group By Key有可能分發到不同的reduce中，從而達到負載均衡的目的；
第二個MapReduce任務再根據預處理的數據結果按照Group By Key分布到reduce中（這個過程可以保證相同的Group By Key分布到同一個reduce中），最后完成最終的聚合操作。
但是這個處理方案對于我們來說是個黑盒，無法把控。
一般處理方案是將對應的key值打散即可。
例如：
select a.*
from test1 a
left join test2 b on a.uid = b.uid
where a.ds='2019-08-10'
and b.ds='2019-08-10'
如果有90%的key都是null，這樣不可避免的出現數據傾斜。
驅動力
前言
大家好，撿田螺的小男孩。
SQL調優這塊呢，大廠面試必問的。最近金九銀十嘛，所以整理了SQL的調優思路，并且附幾個經典案例分析。
? SQL優化思路+經典案例分析
文章來源：數據倉庫與python大數據
原文鏈接：
//mp.weixin.qq.com/s/ZVXTratXxP7MWJntv0IGew
1.慢SQL優化思路。
慢查詢日志記錄慢SQL
- explain分析SQL的執行計劃
- profile 分析執行耗時
- Optimizer Trace分析詳情
- 確定問題并采用相應的措施
如果有90%的key都是null，這樣不可避免的? ? ? ? ? ? 出現數據傾斜。
select a.uid
from test1 as a
join(
? ?select case when uid is null then? ? ? ? ? ? ? ?cast(rand(1000000) as int)
? ?else uid
? ?from test2 where ds='2019-08-10') b?
on a.uid = b.uid
where a.ds='2019-08-10'
當然這種只是理論上的處理方案。
正常的方案是null進行過濾，但是日常情況下不是這中特殊的key。
那么在日常需求的情況下如何處理這種數據傾斜的情況呢：
1、sample采樣，獲取哪些集中的key；
2、將集中的key按照一定規則添加隨機數；
3、進行join，由于打散了，所以數據傾斜避免了；
4、在處理結果中對之前的添加的隨機數進行切分，變成原始的數據；
當然這些優化都是針對SQL本身的優化，還有一些是通過參數設置去調整的，這里面就不再詳細描述了。
但是優化的核心思想都差不多：
- 減少數據量；
- 避免數據傾斜；
- 減少JOB數；
- 虛核心點：根據業務邏輯對業務實現的整體進行優化；
- 虛解決方案：采用presto、impala等專門的查詢引擎，采用spark計算引擎替換MR/TEZ；
驅動力
1.1 慢查詢日志記錄慢SQL
如何定位慢SQL呢、我們可以通過慢查詢日志來查看慢SQL。默認的情況下呢，MySQL數據庫是不開啟慢查詢日志（slow query log）呢。所以我們需要手動把它打開。
查看下慢查詢日志配置，我們可以使用show variables like 'slow_query_log%'命令，如下：
一般來說，我們需要重點關注type、rows、filtered、extra、key。
1.2.1 type
type表示連接類型，查看索引執行情況的一個重要指標。以下性能從好到壞依次：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
- system：這種類型要求數據庫表中只有一條數據，是 const類型的一個特例，一般情況下是不會出現的。
- const：通過一次索引就能找到數據，一般用于主鍵或唯一索引作為條件，這類掃描效率極高，，速度非常快。
- eq_ref：常用于主鍵或唯一索引掃描，一般指使用主鍵的關聯查詢
- ref : 常用于非主鍵和唯一索引掃描。
- ref_or_null：這種連接類型類似于 ref，區別在于 MySQL會額外搜索包含 NULL值的行
- index_merge：使用了索引合并優化方法，查詢使用了兩個以上的索引。
- unique_subquery：類似于 eq_ref，條件用了 in子查詢
- index_subquery：區別于 unique_subquery，用于非唯一索引，可以返回重復值。
- range：常用于范圍查詢，比如：between ... and 或 In 等操作
- index：全索引掃描
- ALL：全表掃描
1.2.2 rows
該列表示MySQL估算要找到我們所需的記錄，需要讀取的行數。對于InnoDB表，此數字是估計值，并非一定是個準確值。
1.2.3 filtered
該列是一個百分比的值，表里符合條件的記錄數的百分比。簡單點說，這個字段表示存儲引擎返回的數據在經過過濾后，剩下滿足條件的記錄數量的比例。
- slow query log表示慢查詢開啟的狀態
- slow_query_log_file表示慢查詢日志存放的位置
我們還可以使用show variables like 'long_query_time'命令，查看超過多少時間，才記錄到慢查詢日志，如下：
long_query_time表示查詢超過多少秒才記錄到慢查詢日志。
我們可以通過慢查日志，定位那些執行效率較低的SQL語句，重點關注分析。
1.2 explain查看分析SQL的執行計劃
當定位出查詢效率低的SQL后，可以使用explain查看SQL的執行計劃。
當explain與SQL一起使用時，MySQL將顯示來自優化器的有關語句執行計劃的信息。即MySQL解釋了它將如何處理該語句，包括有關如何連接表以及以何種順序連接表等信息。
一條簡單SQL，使用了explain的效果如下：
驅動力
1.2.4 extra
該字段包含有關MySQL如何解析查詢的其他信息，它一般會出現這幾個值：
- Using filesort：表示按文件排序，一般是在指定的排序和索引排序不一致的情況才會出現。一般見于order by語句
- Using index ：表示是否用了覆蓋索引。
- Using temporary: 表示是否使用了臨時表,性能特別差，需要重點優化。一般多見于group by語句，或者union語句。
- Using where : 表示使用了where條件過濾.
- Using index condition：MySQL5.6之后新增的索引下推。在存儲引擎層進行數據過濾，而不是在服務層過濾，利用索引現有的數據減少回表的數據。
1.2.5 key
該列表示實際用到的索引。一般配合possible_keys列一起看。
1.3 profile 分析執行耗時
explain只是看到SQL的預估執行計劃，如果要了解SQL真正的執行線程狀態及消耗的時間，需要使用profiling。開啟profiling參數后，后續執行的SQL語句都會記錄其資源開銷，包括IO，上下文切換，CPU，內存等等，我們可以根據這些開銷進一步分析當前慢SQL的瓶頸再進一步進行優化。
profiling默認是關閉，我們可以使用show variables like '%profil%'查看是否開啟，如下：
可以使用set profiling=ON開啟。開啟后，可以運行幾條SQL，然后使用show profiles查看一下。
show profiles會顯示最近發給服務器的多條語句，條數由變量profiling_history_size定義，默認是15。如果我們需要看單獨某條SQL的分析，可以show profile查看最近一條SQL的分析，也可以使用show profile for query id（其中id就是show profiles中的QUERY_ID）查看具體一條的SQL語句分析。
驅動力
除了查看profile ，還可以查看cpu和io，如上圖。
1.4 Optimizer Trace分析詳情
profile只能查看到SQL的執行耗時，但是無法看到SQL真正執行的過程信息，即不知道MySQL優化器是如何選擇執行計劃。這時候，我們可以使用Optimizer Trace，它可以跟蹤執行語句的解析優化執行的全過程。
我們可以使用set optimizer_trace="enabled=on"打開開關，接著執行要跟蹤的SQL，最后執行select * from information_schema.optimizer_trace跟蹤，如下：
1.5 確定問題并采用相應的措施
最后確認問題，就采取對應的措施。
多數慢SQL都跟索引有關，比如不加索引，索引不生效、不合理等，這時候，我們可以 優化索引。
我們還可以優化SQL語句，比如一些in元素過多問題（分批），深分頁問題（基于上一次數據過濾等），進行時間分段查詢
SQl沒辦法很好優化，可以改用ES的方式，或者數倉。
如果單表數據量過大導致慢查詢，則可以考慮分庫分表
如果數據庫在刷臟頁導致慢查詢，考慮是否可以優化一些參數，跟DBA討論優化方案
如果存量數據量太大，考慮是否可以讓部分數據歸檔
我之前寫了一篇文章，有關于導致慢查詢的12個原因，大家看一看一下哈:盤點MySQL慢查詢的12個原因
2. 慢查詢經典案例分析
2.1 案例1：隱式轉換
我們創建一個用戶user表
CREATE TABLE user (
? id int(11) NOT NULL AUTO_INCREMENT,
? userId varchar(32) NOT NULL,
? age varchar(16) NOT NULL,
? name varchar(255) NOT NULL,
? PRIMARY KEY (id),
? KEY idx_userid (userId) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
userId字段為字串類型，是B+樹的普通索引，如果查詢條件傳了一個數字過去，會導致索引失效。如下：
大家可以查看分析其執行樹，會包括三個階段：
- join_preparation：準備階段
- join_optimization：分析階段
- join_execution：執行階段
驅動力
如果給數字加上'',也就是說，傳的是一個字符串呢，當然是走索引，如下圖：
explain select * from user where name ='撿田螺的小男孩';
為什么第一條語句未加單引號就不走索引了呢？這是因為不加單引號時，是字符串跟數字的比較，它們類型不匹配，MySQL會做隱式的類型轉換，把它們轉換為浮點數再做比較。隱式的類型轉換，索引會失效。
2.2 案例2：最左匹配
MySQl建立聯合索引時，會遵循最左前綴匹配的原則，即最左優先。如果你建立一個（a,b,c）的聯合索引，相當于建立了(a)、(a,b)、(a,b,c)三個索引。
假設有以下表結構：
CREATE TABLE user (
? id int(11) NOT NULL AUTO_INCREMENT,
? user_id varchar(32) NOT NULL,
? age varchar(16) NOT NULL,
? name varchar(255) NOT NULL,
? PRIMARY KEY (id),
? KEY idx_userid_name (user_id,name) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
假設有一個聯合索引idx_userid_name，我們現在執行以下SQL，如果查詢列是name，索引是無效的：
因為查詢條件列name不是聯合索引idx_userid_name中的第一個列，不滿足最左匹配原則，所以索引不生效。在聯合索引中，只有查詢條件滿足最左匹配原則時，索引才正常生效。如下，查詢條件列是user_id
2.3 案例3：深分頁問題
limit深分頁問題，會導致慢查詢，應該大家都司空見慣了吧。
limit深分頁為什么會變慢呢？ 假設有表結構如下：
CREATE TABLE account (
? id int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵Id',
? name varchar(255) DEFAULT NULL COMMENT '賬戶名',
? balance int(11) DEFAULT NULL COMMENT '余額',
? create_time datetime NOT NULL COMMENT '創建時間',
? update_time datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
? PRIMARY KEY (id),
? KEY idx_name (name),
? KEY idx_create_time (create_time) //索引
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='賬戶表';
驅動力
以下這個SQL，你知道執行過程是怎樣的呢？
select id,name,balance from account where create_time> '2019-09-19' limit 100000,10;
這個SQL的執行流程醬紫：
通過普通二級索引樹 idx_create_time，過濾 create_time條件，找到滿足條件的主鍵 id。
通過主鍵 id，回到 id主鍵索引樹，找到滿足記錄的行，然后取出需要展示的列（回表過程）
掃描滿足條件的 100010行，然后扔掉前 100000行，返回。
因此，limit深分頁，導致SQL變慢原因有兩個：
limit語句會先掃描 offset+n行，然后再丟棄掉前 offset行，返回后 n行數據。也就是說 limit 100000,10，就會掃描 100010行，而 limit 0,10，只掃描 10行。
limit 100000,10 掃描更多的行數，也意味著回表更多的次數。
如何優化深分頁問題?
我們可以通過減少回表次數來優化。一般有標簽記錄法和延遲關聯法。
標簽記錄法
就是標記一下上次查詢到哪一條了，下次再來查的時候，從該條開始往下掃描。就好像看書一樣，上次看到哪里了，你就折疊一下或者夾個書簽，下次來看的時候，直接就翻到啦。
假設上一次記錄到100000，則SQL可以修改為：
select id,name,balance FROM account where id > 100000 limit 10;
這樣的話，后面無論翻多少頁，性能都會不錯的，因為命中了id索引。但是這種方式有局限性：需要一種類似連續自增的字段。
延遲關聯法
延遲關聯法，就是把條件轉移到主鍵索引樹，然后減少回表。如下
select acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN (SELECT a.id FROM account a WHERE a.create_time > '2019-09-19' limit 100000, 10) AS acct2 on acct1.id= acct2.id;
優化思路就是，先通過idx_create_time二級索引樹查詢到滿足條件的主鍵ID，再與原表通過主鍵ID內連接，這樣后面直接走了主鍵索引了，同時也減少了回表。
2.4 案例4：in元素過多
如果使用了in，即使后面的條件加了索引，還是要注意in后面的元素不要過多哈。in元素一般建議不要超過200個，如果超過了，建議分組，每次200一組進行哈。
反例:
select user_id,name from user where user_id in (1,2,3...1000000);?
如果我們對in的條件不做任何限制的話，該查詢語句一次性可能會查詢出非常多的數據，很容易導致接口超時。尤其有時候，我們是用的子查詢，in后面的子查詢，你都不知道數量有多少那種，更容易采坑.如下這種子查詢：
select * from user where user_id in (select author_id from artilce where type = 1);
如果type = 1有1一千，甚至上萬個呢？肯定是慢SQL。索引一般建議分批進行，一次200個，比如：
select user_id,name from user where user_id in (1,2,3...200);
in查詢為什么慢呢？
這是因為in查詢在MySQL底層是通過n*m的方式去搜索，類似union。
in查詢在進行cost代價計算時（代價 = 元組數 * IO平均值），是通過將in包含的數值，一條條去查詢獲取元組數的，因此這個計算過程會比較的慢，所以MySQL設置了個臨界值(eq_range_index_dive_limit)，5.6之后超過這個臨界值后該列的cost就不參與計算了。因此會導致執行計劃選擇不準確。默認是200，即in條件超過了200個數據，會導致in的代價計算存在問題，可能會導致Mysql選擇的索引不準確。
驅動力
2.5 order by 走文件排序導致的慢查詢
如果order by 使用到文件排序，則會可能會產生慢查詢。我們來看下下面這個SQL：
select name,age,city from staff where city = '深圳' order by age limit 10;
它表示的意思就是：查詢前10個，來自深圳員工的姓名、年齡、城市，并且按照年齡小到大排序。
查看explain執行計劃的時候，可以看到Extra這一列，有一個Using filesort，它表示用到文件排序。
order by文件排序效率為什么較低
大家可以看下這個下面這個圖:
2、從索引樹 idx_city， 找到第一個滿足 city='深圳’條件的 主鍵id,假設 id為 X；
3、到主鍵 id索引樹拿到 id=X的這一行數據， 取age和主鍵id的值，存到 sort_buffer；
4、從索引樹 idx_city拿到下一個記錄的 主鍵id，假設 id=Y；
5、重復步驟 3、4 直到 city的值不等于深圳為止；
6、前面5步已經查找到了所有 city為深圳的數據，在 sort_buffer中，將所有數據根據 age進行排序；遍歷排序結果，取前10行，并按照id的值回到原表中，取出 city、name 和 age三個字段返回給客戶端。
order by排序，分為全字段排序和rowid排序。它是拿max_length_for_sort_data和結果行數據長度對比，如果結果行數據長度超過max_length_for_sort_data這個值，就會走rowid排序，相反，則走全字段排序。
2.5.1 rowid排序
rowid排序，一般需要回表去找滿足條件的數據，所以效率會慢一點。以下這個SQL，使用rowid排序，執行過程是這樣：
select name,age,city from staff where city = '深圳' order by age limit 10;
1、MySQL為對應的線程初始化 sort_buffer，放入需要排序的 age字段，以及 主鍵id；
2.5.2 全字段排序
同樣的SQL，如果是走全字段排序是這樣的：
select name,age,city from staff where city = '深圳' order by age limit 10;
- MySQL 為對應的線程初始化 sort_buffer，放入需要查詢的 name、age、city字段；
- 從索引樹 idx_city， 找到第一個滿足 city='深圳’條件的主鍵 id，假設找到 id=X；
- 到主鍵id索引樹拿到 id=X的這一行數據， 取 name、age、city三個字段的值，存到 sort_buffer；
- 從索引樹 idx_city 拿到下一個記錄的主鍵 id，假設 id=Y；
- 重復步驟 3、4 直到 city的值不等于深圳為止；
- 前面5步已經查找到了所有 city為深圳的數據，在 sort_buffer中，將所有數據根據age進行排序；
- 按照排序結果取前10行返回給客戶端。
驅動力
sort_buffer的大小是由一個參數控制的：sort_buffer_size。
- 如果要排序的數據小于 sort_buffer_size，排序在 sort_buffer內存中完成
- 如果要排序的數據大于 sort_buffer_size，則借助磁盤文件來進行排序。
借助磁盤文件排序的話，效率就更慢一點。因為先把數據放入sort_buffer，當快要滿時。會排一下序，然后把sort_buffer中的數據，放到臨時磁盤文件，等到所有滿足條件數據都查完排完，再用歸并算法把磁盤的臨時排好序的小文件，合并成一個有序的大文件。
2.5.3 如何優化order by的文件排序
order by使用文件排序，效率會低一點。我們怎么優化呢？
- 因為數據是無序的，所以就需要排序。如果數據本身是有序的，那就不會再用到文件排序啦。而索引數據本身是有序的，我們通過建立索引來優化 order by語句。
- 我們還可以通過調整 max_length_for_sort_data、sort_buffer_size等參數優化；
2.6 索引字段上使用is null， is not null，索引可能失效
表結構:
單個name字段加上索引，并查詢name為非空的語句，其實會走索引的，如下:
單個card字段加上索引，并查詢name為非空的語句，其實會走索引的，如下:
但是它兩用or連接起來，索引就失效了，如下：
很多時候，也是因為數據量問題，導致了MySQL優化器放棄走索引。同時，平時我們用explain分析SQL的時候，如果type=range,要注意一下哈，因為這個可能因為數據量問題，導致索引無效。
2.7 索引字段上使用（！= 或者 < >），索引可能失效
假設有表結構：
雖然age加了索引，但是使用了！= 或者< >，not in這些時，索引如同虛設。如下：
驅動力
如果把它們的name字段改為編碼一致，相同的SQL，還是會走索引。
user表的name字段編碼是utf8mb4，而user_job表的name字段編碼為utf8。
2.9 group by使用臨時表
group by一般用于分組統計，它表達的邏輯就是根據一定的規則，進行分組。日常開發中，我們使用得比較頻繁。如果不注意，很容易產生慢SQL。
2.9.1 group by執行流程
假設有表結構：
執行左外連接查詢,user_job表還是走全表掃描，如下：
其實這個也是跟mySQL優化器有關，如果優化器覺得即使走了索引，還是需要掃描很多很多行的哈，它覺得不劃算，不如直接不走索引。平時我們用！= 或者< >，not in的時候，留點心眼哈。
2.8 左右連接，關聯的字段編碼格式不一樣
新建兩個表，一個user，一個user_job
- Extra 這個字段的 Using temporary表示在執行分組的時候使用了臨時表
- Extra 這個字段的 Using filesort表示使用了文件排序
group by是怎么使用到臨時表和排序了呢？我們來看下這個SQL的執行流程
驅動力
- 方向1：既然它默認會排序，我們不給它排是不是就行啦。
- 方向2：既然臨時表是影響group by性能的X因素，我們是不是可以不用臨時表？
我們一起來想下，執行group by語句為什么需要臨時表呢？group by的語義邏輯，就是統計不同的值出現的個數。如果這個這些值一開始就是有序的，我們是不是直接往下掃描統計就好了，就不用臨時表來記錄并統計結果啦?
可以有這些優化方案：
- group by 后面的字段加索引
- order by null 不用排序
- 盡量只使用內存臨時表
- 使用SQL_BIG_RESULT
2.10 delete + in子查詢不走索引！
之前見到過一個生產慢SQL問題，當delete遇到in子查詢時，即使有索引，也是不走索引的。而對應的select + in子查詢，卻可以走索引。
MySQL版本是5.7，假設當前有兩張表account和old_account,表結構如下：
臨時表的排序是怎樣的呢？
就是把需要排序的字段，放到sort buffer，排完就返回。在這里注意一點哈，排序分全字段排序和rowid排序
如果是全字段排序，需要查詢返回的字段，都放入sort buffer，根據排序字段排完，直接返回
如果是rowid排序，只是需要排序的字段放入sort buffer，然后多一次回表操作，再返回。
2.9.2 group by可能會慢在哪里？
group by使用不當，很容易就會產生慢SQL問題。因為它既用到臨時表，又默認用到排序。有時候還可能用到磁盤臨時表。
- 如果執行過程中，會發現內存臨時表大小到達了上限（控制這個上限的參數就是 tmp_table_size），會把內存臨時表轉成磁盤臨時表。
- 如果數據量很大，很可能這個查詢需要的磁盤臨時表，就會占用大量的磁盤空間。
2.9.3 如何優化group by呢
從哪些方向去優化呢？
select city ,count(*) as num from staff group by city;
1、創建內存臨時表，表里有兩個字段 city和num；
2、全表掃描staff的記錄，依次取出city = 'X'的記錄。
- 判斷臨時表中是否有為 city='X'的行，沒有就插入一個記錄 (X,1);
- 如果臨時表中有 city='X'的行，就將X這一行的num值加 1；
3、遍歷完成后，再根據字段 city做排序，得到結果集返回給客戶端。這個流程的執行圖如下：
執行的SQL如下：
驅動力
可以發現，實際執行的時候，MySQL對select in子查詢做了優化，把子查詢改成join的方式，所以可以走索引。但是很遺憾，對于delete in子查詢，MySQL卻沒有對它做這個優化。
日常開發中，大家注意一下這個場景哈
參考資料
慢SQL優化一點小思路: //juejin.cn/post/7048974570228809741#heading-7 SQL優化萬能公式：5 大步驟 + 10 個案例: //developer.aliyun.com/article/980780
為什么select + in子查詢會走索引，delete + in子查詢卻不會走索引呢？
我們執行以下SQL看看：
查看執行計劃，發現不走索引：
但是如果把delete換成select，就會走索引。如下：
驅動力
引言
在開發和維護MySQL數據庫時，優化SQL查詢語句是提高數據庫性能和響應速度的關鍵。通過合理優化SQL查詢，可以減少數據庫的負載，提高查詢效率，為用戶提供更好的用戶體驗。本文將介紹常用的30種MySQL SQL查詢優化方法，并通過實際案例演示它們的應用。
3、使用EXPLAIN分析查詢計劃
使用EXPLAIN命令可以分析查詢的執行計劃，幫助優化查詢語句，查找潛在的性能問題。
EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';
第二部分：優化查詢條件
4、使用WHERE子句過濾數據
在查詢數據時，盡量使用WHERE子句對數據進行過濾，減少返回的數據量。
-- 不推薦
SELECT * FROM table_name;
-- 推薦
SELECT * FROM table_name WHERE column_name = 'value';
5、使用索引覆蓋查詢
索引覆蓋查詢是指查詢的字段都包含在索引中，不需要回表查詢數據。這樣可以減少IO操作，提高查詢效率。
-- 創建索引
CREATE INDEX idx_column ON table_name(column_name);
-- 索引覆蓋查詢
SELECT column1, column2 FROM table_name WHERE column_name = 'value';
6、避免在WHERE子句中使用函數
在WHERE子句中使用函數會導致索引失效，需要全表掃描。盡量避免在WHERE子句中使用函數。
-- 不推薦
SELECT * FROM table_name WHERE DATE_FORMAT(date_column, '%Y-%m-%d') = '2022-01-01';
-- 推薦
SELECT * FROM table_name WHERE date_column = '2022-01-01';
? MySQL常用30種SQL查詢語句優化方法
作者：good7ob
原文鏈接：
//mp.weixin.qq.com/s/VPE-mCkpWa-w0Imw7YDINg
第一部分：基礎優化方法
1、使用索引
索引是提高數據庫查詢性能的基礎，通過為查詢字段添加合適的索引，可以加快查詢速度。在創建索引時，需要考慮查詢的頻率和數據的更新頻率，避免過度索引或不必要的索引。
-- 創建索引
CREATE INDEX idx_column ON table_name(column_name);
2、避免使用SELECT *?
在查詢數據時，盡量避免使用SELECT *，而是明確指定需要查詢的字段。這樣可以減少返回的數據量，提高查詢效率。
-- 不推薦
SELECT * FROM table_name;
-- 推薦
SELECT column1, column2 FROM table_name;
驅動力
7、使用合適的數據類型
選擇合適的數據類型可以減少存儲空間和查詢時間，提高數據庫性能。
-- 不推薦
CREATE TABLE table_name (id VARCHAR(100), name VARCHAR(100));
-- 推薦
CREATE TABLE table_name (id INT, name VARCHAR(100));
第三部分：連接查詢優化
8、使用INNER JOIN代替WHERE子句連接
使用INNER JOIN可以更好地表達表之間的關系，提高查詢的可讀性和性能。
-- 不推薦
SELECT * FROM table1, table2 WHERE table1.id = table2.id;
-- 推薦
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;
9、使用JOIN ON代替WHERE子句過濾連接
在連接查詢時，盡量使用JOIN ON對連接進行過濾，而不是在WHERE子句中過濾連接。
-- 不推薦
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id WHERE table2.name = 'value';
-- 推薦
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id AND table2.name = 'value';
10、使用合適的連接類型
根據實際業務需求選擇合適的連接類型，包括INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL JOIN。
-- INNER JOIN（默認連接類型）
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;
-- LEFT JOIN
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;
-- RIGHT JOIN
SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;
-- FULL JOIN
SELECT * FROM table1 FULL JOIN table2 ON table1.id = table2.id;
第四部分：子查詢優化
11、使用EXISTS代替IN
在使用子查詢時，盡量使用EXISTS代替IN，EXISTS只關心是否存在記錄，而IN會將子查詢的結果集加載到內存中，可能導致性能問題。
-- 不推薦
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
-- 推薦
SELECT * FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.id = table2.id);
12、使用JOIN代替子查詢
在查詢中使用JOIN可以更好地表達查詢的邏輯，避免使用復雜的子查詢。
驅動力
-- 不推薦
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
-- 推薦
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;
第五部分：LIMIT優化
13、使用LIMIT限制返回的記錄數
在查詢大量數據時，使用LIMIT可以限制返回的記錄數，避免查詢過多的數據。
-- 返回前10條記錄
SELECT * FROM table_name LIMIT 10;
14、使用分頁查詢
在查詢分頁數據時，可以使用LIMIT結合OFFSET實現分頁查詢。
-- 返回第1頁的數據，每頁10條記錄
SELECT * FROM table_name LIMIT 0, 10;
-- 返回第2頁的數據，每頁10條記錄
SELECT * FROM table_name LIMIT 10, 10;
第六部分：排序優化
15、使用合適的排序字段
在排序查詢時，選擇合適的排序字段可以減少排序的時間和開銷。通常應該選擇已經建立了索引的字段進行排序，避免對大量數據進行排序操作。
- 不推薦
SELECT * FROM table_name ORDER BY name;
-- 推薦
SELECT * FROM table_name ORDER BY indexed_column;
16、使用覆蓋索引減少排序
如果查詢中只需要排序字段，并且該字段已經建立了索引，可以使用覆蓋索引來減少排序的時間。
-- 創建索引
CREATE INDEX idx_name ON table_name(name);
-- 使用覆蓋索引
SELECT name FROM table_name ORDER BY name;
17、使用DESC進行降序排序
在進行降序排序時，使用DESC關鍵字可以明確排序方式，避免不必要的排序操作。
-- 降序排序
SELECT * FROM table_name ORDER BY column_name DESC;
第七部分：避免使用通配符
18、避免使用%通配符
在查詢數據時，盡量避免使用%通配符在查詢字段的開頭，這會導致索引失效，需要進行全表掃描。
-- 不推薦
SELECT * FROM table_name WHERE column_name LIKE '%value';
-- 推薦
SELECT * FROM table_name WHERE column_name LIKE 'value%';
19、使用前綴索引
如果需要在查詢中使用通配符%在字段的結尾，可以使用前綴索引來優化查詢性能。
驅動力
-- 創建前綴索引
CREATE INDEX idx_column ON table_name(column_name(10));
-- 使用前綴索引
SELECT * FROM table_name WHERE column_name LIKE 'value%';
第八部分：聯合查詢優化
20、使用UNION ALL代替UNION
在使用聯合查詢時，如果不需要去重操作，應該使用UNION ALL，可以減少查詢的開銷。
-- 不推薦
SELECT * FROM table1 WHERE condition
UNION
SELECT * FROM table2 WHERE condition;
-- 推薦
SELECT * FROM table1 WHERE condition
UNION ALL
SELECT * FROM table2 WHERE condition;
21、使用EXISTS代替IN和UNION
在查詢中使用EXISTS代替IN和UNION，可以更好地表達查詢邏輯，提高查詢性能。
-- 不推薦
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2 WHERE condition)
UNION
SELECT * FROM table1 WHERE id IN (SELECT id FROM table3 WHERE condition);
-- 推薦
SELECT * FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.id = table2.id AND condition)
OR EXISTS (SELECT 1 FROM table3 WHERE table1.id = table3.id AND condition);
第九部分：使用子查詢優化
22、使用內連接代替子查詢
在使用子查詢時，盡量使用內連接代替，可以減少查詢的開銷。
-- 不推薦
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
-- 推薦
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;
23、使用EXISTS代替IN
在使用子查詢時，盡量使用EXISTS代替IN，EXISTS只關心是否存在記錄，而IN會將子查詢的結果集加載到內存中，可能導致性能問題。
-- 不推薦
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
-- 推薦
SELECT * FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.id = table2.id);
第十部分：數據表設計優化
24、使用合適的數據類型
在創建數據表時，選擇合適的數據類型可以減少存儲空間和查詢時間，提高數據庫性能。
驅動力
-- 不推薦
CREATE TABLE table_name (id VARCHAR(100), name VARCHAR(100));
-- 推薦
CREATE TABLE table_name (id INT, name VARCHAR(100));
25、垂直拆分表
在數據表中包含大量冗余數據時，可以考慮對表進行垂直拆分，將不同的數據拆分到不同的表中，提高查詢性能。
第十一部分：其他優化方法
26、使用連接池
使用連接池可以減少連接數據庫的開銷，提高數據庫的并發性能。
27、合理配置緩沖區
根據數據庫的實際情況，合理配置緩沖區大小，加快數據的讀寫速度。
28、使用延遲關聯
在進行關聯查詢時，可以考慮使用延遲關聯，將關聯查詢放在最后執行，減少關聯操作的次數。
29、避免使用臨時表
在查詢中盡量避免使用臨時表，臨時表會增加查詢的開銷。
30、定期優化數據表
定期對數據表進行優化，包括重新建立索引、壓縮表等操作，可以提高數據庫的性能。
結語
本文介紹了常用的30種MySQL SQL查詢優化方法，并通過實際案例演示了它們的應用。優化SQL查詢是提高數據庫性能和響應速度的關鍵，通過合理優化SQL查詢可以減少數據庫的負載，提高查詢效率。讀者可以根據自己的實際情況和業務需求，選擇合適的優化方法，提高數據庫的性能和可用性。同時，我們也要不斷學習和實踐，在數據庫領域不斷提高自己的知識水平和技術能力，成為一名優秀的數據庫工程師。
易寶支付數據部
聯系電話：1580-136-5057
地址：北京市朝陽區朝外大街甲6號
投稿地址：kai.zhao@yeepay.com
增值電信業務經營許可證：陜B2-20210327 |