sql優化書
Ⅰ ODPS sql 優化總結
優化總結：SQL使用技巧與性能提升
1、利用null處理：在判斷條件中處理null時，使用nvl或coalesce函數進行默認轉換，避免null影響判斷結果。
2、選擇性列查詢：在數據開發或線上任務中，應提前剪裁列，即使需要所有列，也明確列出，減少不必要的數據讀取，預防後期表結構變動導致的錯誤。
3、多表插入優化：讀取同一表但在不同粒度下插入多表時，採用from () tab insert overwrite A insert overwrite B方法，減少資源浪費。注意遵循團隊開發規范，合理復用公共數據，如通過臨時表存儲邏輯。
4、分區限定：ODPS表為分區表，應習慣性限定分區ds，避免因分區限定問題導致的任務資源浪費。
5、使用limit：在臨時查詢或數據探查時，加上limit快速獲取所需數據，減少資源消耗。
6、UDF函數下沉：將UDF函數下沉到子查詢中，提高效率。
7、行轉列與列轉行：利用collect_set、lateral view函數實現，參考大佬經驗。
8、窗口函數應用：使用row_number()或max(struct())等方法實現數據排序或計算。
9、關聯類型：掌握左關聯、內關聯、右關聯等，適應不同場景下的多表關聯，確保關聯欄位類型一致。
10、笛卡爾積處理：針對需求翻倍一行數據的場景，創建維表並通過笛卡爾積操作，或使用:LATERAL VIEW POSEXPLODE方法。
11、提高map數：通過調整split size hint優化商品表效率，使用/*+SPLIT_SIZE(8)*/調整，單位為MB。
數據傾斜優化策略：
1）大表關聯小表：使用mapjoin hint，調整中小表內存大小，通過set odps.sql.mapjoin.memory.max調整，單位M。
2）大表關聯大表：拆分熱點數據，使用普通join或skewjoin hint，針對分區與桶優化關聯。
3）count distinct問題：通過group by先去重再count解決。
4）ODPS新特性：關注MaxCompute(ODPS2.0)特性，性能優化顯著。
小表關聯大表優化：使用dynamic_filter，調整/*+dynamic_filter(A,B)*/。
參數設置優化：
1）Map設置：調整odps.sql.mapper.cpu、memory、merge.limit.size、split.size，根據任務特點合理調整。
2）Join設置：調整odps.sql.joiner.instances、cpu、memory，針對Join任務特性進行調整。
3）Rece設置：調整odps.sql.recer.instances、cpu、memory，優化任務性能。
4）小文件合並參數：設置odps.merge.cross.paths、filesize.threshold、maxmerged.filesize.threshold、max.filenumber.per.instance、max.filenumber.per.job，控制文件合並。
5）UDF相關參數：調整odps.sql.udf.jvm.memory、timeout、python.memory、optimize.reuse、strict.mode，優化UDF性能。
Mapjoin設置：使用odps.sql.mapjoin.memory.max調整小表最大內存。
動態分區設置：使用set odps.sql.reshuffle.dynamicpt控制動態分區優化。
數據傾斜設置：使用set odps.sql.groupby.skewindata、odps.sql.skewjoin優化Group By與Join操作。
SQL優化案例：
關聯與數據傾斜優化：發現數據傾斜，嘗試使用skewjoin、熱點數據分離等方法，最終通過隱式轉換問題解決，確保關聯操作的正確性。
分桶解決大表與大表關聯：使用hash clustering分散數據，減少資源消耗，提高查詢效率。
BitMap在多維匯總中的應用：利用BitMap實現去重匯總，解決數據傾斜問題，優化多維匯總模型性能。
總結：SQL優化是理解業務邏輯、合理利用工具、控制資源消耗的過程。在滿足業務需求的同時，追求效率與成本的優化。
Ⅱ SQL資料庫查詢的優化
這個問題太大了，不懂就加內存，加CPU，用RAID存儲。
Ⅲ Sql優化-多like模糊查詢及根據時間排序
2020-04-21
記錄一次sql優化記錄：
環境：用的mysql版本 select Version();
優化過程：
用的是兩張表聯查，四個條件like查詢 ，根據時間排序降序
其中A，B表沒有大欄位，A表20萬多數據，B表50萬多條數據。語句如下：
EXPLAIN
SELECT A.bondId,A.sname,A.cname,A.secuCode,A. ISSUER,A.guarantor,B.underwriter AS infoSource
FROM A
LEFT JOIN B ON B.bondId = A.bondId
WHERE B.agentType = 1
AND B.underwriter = '有限公司'
AND A.startDate <= '2020-04-21 18:02:10'
AND A.endDate >= '2020-04-21 18:02:10'
AND (
A.cname LIKE '%%' OR A.sname LIKE '%%' OR A.secuCode LIKE '%%'
OR A. ISSUER LIKE '%%'OR A.guarantor LIKE '%%')
AND A.isValid = 1
ORDER BY A.startDate DESC
LIMIT 0, 20
這是2個表都沒有加索引的情況，從explain來看結果非常糟糕，都是全表掃描，並且產生臨時表同時有文件排序，效率肯定非常低。
首先嘗試在B表上建立一個聯合索引
可以考慮從關聯欄位及where條件欄位考慮（bondId, underwriter, agentType）
建一個聯合索引，試試。
ALTER TABLE B ADD INDEX bua_index(bondId, underwriter, agentType)
再explain看：
可以看到B表用到了我們剛剛建的聯合索引，並且額外信息是Using index ，type是ref級別的，效果比較理想，再來看A表。
Where條件中有多個like，這種情況下一般索引都是不可用的，所以必須用覆蓋索引解決，
由於又根據startDate排序，所以嘗試根據如下欄位建立聯合索引，同時查詢的欄位就是索引中的欄位（startDate, endDate,cname, sname, secuCode, issuer, guarantor）
ALTER TABLE A ADD INDEX index_scssig(startDate, endDate,cname, sname, secuCode, issuer, guarantor)
再次explain看看效果：
這樣乍看上去A表也用到了剛剛建的聯合索引，並且type是range級別雖然比ref差點，按理說應該也還可以，但是我執行sql語句，效率還是非常差，查詢耗時達到8s，並且偶爾還不止這個時間
究其原因，雖然使用了索引，但是extra裡面是Using index condition&Using where
回表操作了，我在想如果將extra優化成Using index效率肯定沒問題
故再進一步優化，還是從索引入手
在聯合索引上添加2個欄位isValid, bondId 再試試
ALTER TABLE A ADD INDEX index_scssig(isvalid,startDate, endDate,cname, sname, secuCode, issuer, guarantor,bondId)
再次explain:
這個結果就是我想要的，然後執行sql看看效率：
已經提升了很多了，但是我試了別的查詢條件偶爾時間會到3，4s,懷疑和自己的機器有關
在這這種多個like的or查詢mysql本身並不擅長，無奈坑爹的需要需要這樣，可能效率並不是非常的高，優化成這樣可以接受了。
最近對以前項目的慢查詢進行sql調優，感覺性能的下降往往還是sql語句及索引的建立的問題，explain是很有幫助，正確優化還是能極大提升效率的。