SQL優化主要包括:避免使用select *,使用JOIN代替;避免使用子查詢,使用UNION代替;避免使用OR查詢,使用UNION ALL代替;避免使用!
你好 我是田哥
這道面試題在面試中是一道高頻面試題,但是很多人都只停留在加索引的層面。
整體概覽
關于SQL優化,我們可以從幾個方面來說:
直觀SQL
我們遇到SQL需要優化,第一印象應該是看一下這條SQL有沒有什么致命的毛病,不需要借助任何工具。
第一條:避免使用select *
很多人可能不太理解為什么要避免使用select *,因為select *會把所有字段都查出來,那這里有兩個說服你的理由:
我們只需要查詢出我們需要的字段,減少數據量的傳輸,從而提高IO傳輸性能。
在索引優化環節中,我們可以利用索引優化規則,來提升整體的查詢效率。舉個簡單的場景:覆蓋索引。
第二條:避免使用子查詢,使用JOIN代替
因為子查詢都是嵌套查詢,而嵌套查詢就會創建一些臨時表,這樣就會增加性能的損耗。
況且使用JOIN,數據庫可以更有效的優化JOIN連接操作。
第三條:避免使用OR查詢,使用UNION或者UNION ALL代替
在MySQL5.0之前的版本,要盡量避免使用OR查詢,因為它可能會導致索引失效。
我們可以使用UNION或者UNION ALL代替,而UNION可以去重,UNION ALL是不會去重。
如果我們結果集里允許出現重復數據就是用UNION ALL,反之使用UNION。
第四條:避免使用!=、<>操作符,使用IN代替
因為在SQL中使用到了這些操作符,會導致查詢引擎放棄通過索引來查詢數據,轉而改成全表查詢。
我們使用!=時,就算你字段上加了索引,也可能會導致索引失效。
第五條:避免使用%開頭的LIKE查詢
這一點,我們上一篇文章中有專門說過,所以這里就不在贅述了。
第六點:避免查詢字段采用函數計算
因為索引是基于索引字段排好序的數據結構,而當我們使用函數后,這個字段的有序就被打破了,從而導致索引失效。
以上是我們拿到SQL后,不需要什么工具都應該能考慮的點。
索引
索引不是SQL優化唯一的點,但他是很重要的點,關于索引這一塊,那我們就需要借助于一些工具了。
第一條:確保我們查詢條件和連接條件的字段上建了索引
如果查詢條件和連接條件對應的列沒有建索引,我就應該考慮給其加上索引。
第二條:利用好覆蓋索引
關于覆蓋索引,如果還不清楚的話,建議先搞清楚什么是覆蓋索引。
前面我們說到避免使用select*,提到了覆蓋索引,假設我們需要查詢的字段剛好在二級索引里,這樣我們就在二級索引里找到了我們想要的數據,不需要再回表操作。我們查詢時就不寫select * ,改成select xx,這樣就可以減少回表的操作。
第三條:正確使用聯合索引,避免過多使用索引
因為聯合索引通常是由多個字段組成,如果聯合索引里的字段太多了,當我們沒插入一條數據,都會有很大的可能觸發索引樹的重構,也就是索引樹的分列,這里就涉及到一個常見的面試題:為什么我們的主鍵id需要自增?
第四條:更新頻率較高的列,慎用索引
和第三條有那么些管理,因為更新頻繁的列,就代表著它的樹的維護是非常頻繁的,可能我們沒插入一條數據就會進行分裂,然后再重構這個樹。
第五點:避免范圍查詢數據量過多
因為在MySQL中一條查詢SQL是否走索引,是取決與MySQL的執行計劃的成本來的。這個成本是MySQL優化器來進行選擇的。
比如我們當前一條SQL需要在二級索引上面找到大量的數據,然后再回到主鍵索引樹上,然后再返回數據,這里可能就會存在1+1=2的成本情況。如果直接全表掃描的成本是1,那這個時候優化器就會直接選擇全表掃描。
所以,我們盡量避免范圍查詢數據量過多的情況。
以上就是索引相關的優化點。
其實,很多時候,我們就算加了索引,SQL也不見得就會變快。站在開發者的角度,我們是需要把所有相關索引失效的場景給排除掉。
說白了,最終問題不是咱們的,把鍋給甩出去。
其他手段
第一條:善于利用EXPLAIN
充分理由好EXPLAIN,讓其幫忙我們去分析SQL的執行計劃。通過EXPLAIN我們可以知道是否走索引,掃描了多少數據,這也是我們每個程序員必備的SQL優化工具:EXPLAIN。
關于EXPLAIN這里就不做深入介紹了,還不會的,請自行差相關資料學習(網上的文章和視頻多多滴)。
第二條:分頁優化、排序優化、分組優化
三個優化中,后面兩個是基于索引來做的,就是你排序的字段需要加上索引,你分組的字段需要加上索引,并且索引要成功被利用到。
分頁優化就相對特殊,因為都是后面慢慢才會發現的。比如說現在要查詢第10010后面的10條數據,分頁查詢會先掃描10010前面的數據都掃描一遍,最后再返回,其實前面的數據,我們根本就不需要,但是分頁查詢會先掃描前面的數據的。這樣肯定會造成性能的浪費,因此我們需要深分頁優化。
這個感興趣的可以網上搜搜MySQL分頁優化相關視頻和文章來學習一下。
第三條:分解復雜查詢
這個相對來說比較好理解,但難度系數卻很高。
我們在實際開發中,可能會遇到一條SQL有幾十行幾百行甚至上千行。
如果你使用了前面相關的辦法,但這個SQL還是慢,那此時就可以考慮拆分。
第四條:批量插入
在大批量數據進行插入時,每插入一條數據,就去提交一次事務,面對大量數據插入時,這樣就會浪費性能。
我們可以每次插入500條,但是這個500條數據,我這里只是舉例,實際工作中遇到對其做一些壓測這類的,找到一個相關最優的提交數據量。
第五條:監控和分析工具
這里的監控和分析工具,并不是針對一條SQL,而是針對我們整個數據庫的性能。
第六條:硬件優化
這個是boss不想看到的,實在沒招了才用這招,也是所謂的絕招。
第七條:分庫分表、讀寫分離
關于分庫分表和讀寫分類,其實還是和硬件優化以及拆分思想有那么些關聯。
好了關于慢SQL優化,我們一共從三個大方向以及18條建議來說明,相信很多面試官都會很驚訝,居然總結了這么多。
我搜集了很多面試真題,目前已經整理了高頻面試110題,除了高頻面試題外,我還整理了一個《追問試面試》系列,本系列目前已有41節。
感興趣的朋友,加入我的知識星球即可免費獲取。
原文地址:https://mp.weixin.qq.com/s/H7doSy84bY94c4nvIr2SzQ
來源:本文內容搜集或轉自各大網絡平臺,并已注明來源、出處,如果轉載侵犯您的版權或非授權發布,請聯系小編,我們會及時審核處理。
聲明:江蘇教育黃頁對文中觀點保持中立,對所包含內容的準確性、可靠性或者完整性不提供任何明示或暗示的保證,不對文章觀點負責,僅作分享之用,文章版權及插圖屬于原作者。
Copyright?2013-2024 JSedu114 All Rights Reserved. 江蘇教育信息綜合發布查詢平臺保留所有權利
蘇公網安備32010402000125
蘇ICP備14051488號-3技術支持:南京博盛藍睿網絡科技有限公司
南京思必達教育科技有限公司版權所有 百度統計