為什麼SQL需要優化?
- 查詢性能低
- 執行時間過長
- 等待時間過長
- SQL寫的太差(尤其體現在多表查詢上)
- 索引失效
- 服務器參數(緩存,線程數)設置不合理
- 項目需求不合理
- ...等等
我們程序員寫代碼時能做的只有對寫SQL儘可能的做出優化,執行效率更高,有效的使用索引,重點放在寫SQL上
SQL的執行過程
MySQL在接收到客戶端傳入的SQL語句後並不能馬上對該SQL進行執行,是需要經過一系列複雜的流程,最終轉變成二進制的機器碼,才能被執行的,我們需要對執行的SQL進行優化,那麼就有必須先來了解下,一個SQL語句的執行有哪些主要環節,以查詢的SQL來舉例
查詢語句的執行過程:
SQL的執行順序
寫SQL時的順序
select ... from ... join ... on ... where ... group by ... having ... order by ... limit ...
解析SQL時的順序
from ... on ... join ... where ... group by ... having ... select ... order by ... limit ...
SQL優化的目標
在SQL語句執行之前,會專門有個叫查詢優化器的組件對我們寫的SQL進行優化和調整,然後生成執行計劃,也就是說,最終執行的SQL不一定是我們當初自己寫的SQL,如果出現這個情況大家不要驚訝
對於SQL優化來講重中之重就是優化索引的使用
數據庫索引
什麼是索引?
索引是一種幫助數據庫獲得高效查詢效率的數據庫對象,該數據庫對象使用了特殊的數據結構,以B樹和Hash樹最為常見,MySQL中索引默認使用的是B樹
通俗來講可以這樣理解,索引好比是字典的目錄,我們在查詢某個字時,可以先從目錄中查找,看看我們需要的找字在字典中具體頁碼是多少,然後再直接翻到對應的頁碼,從而快速的找我們需要的內容,如果沒有這個目錄,我們就只能從字典的第一頁開始,一頁一頁的往下翻,直到找到我們需要的內容,從這裡我們不難看出目錄對查詢數據的所提高的性能之大,作用非常關鍵,同樣索引也是一樣的
索引提高查詢效率的原理
我們對age列建立B樹(一般指B+樹)索引,遵循樹數據結構的特點,對比當前節點,小的放左邊,大的放右邊:
查詢age小於等於18的數據:
select * from user where age <= 18
沒有索引的時候
使用的全表檢索的方式,直接訪問文件中的數據,對該列的每一個值進行訪問,此時訪問文件中數據使用了大量的IO操作,而IO操作是要耗費大量性能
有索引的時候
索引文件已經使用數據結構對數據進行了排序和合並,當我們需要查詢age小於等於18的數據時,只要通過二分查找的方式,從索引中找18和18節點上掛載的左邊節點全都獲取出來,再根據ID獲取到對應的數據,這種方式能有效的減少IO操作
使用了索引後我們能感受到最直接的好處就是,通過減少IO操作的次數,從而提升查詢的性能
拓展:在BTree數據結構中,數據全都存放在葉子節點上,無論查找什麼數據都只跟樹的層數有關,一個3層結構的BTree能容納上百萬的數據,在上百萬的數據中查詢某個數據就只需要找3次,效率極高
索引的利
- 減少IO操作次數,提供查詢效率
- 降低CUP使用率(在排序操作中尤為明顯)
索引的弊
- 佔據大量的硬盤存儲空間
- 不適用索引的情況數據量小的表頻繁變動的字段不經常查詢的字段降低DML操作的效率
索引分類
一個表是可以有多個不同的索引,
- 主鍵索引只在建立主鍵約束時自動添加,特點:非空且唯一
- 單列索引單獨對錶中的某個列數據建立索引
- 唯一索引在該索引中沒有重複的數據,都是唯一的
- 複合索引多個列的值組成的索引,當第一個列的值重複時,按照後面的組合必須查找數據複合索引相當於字典的二級目錄,當前一個值一致時,再使用後一個值做篩選
項目中使用最多的是複合索引,在實際的需求中我們往往都是按照多個條件做查詢,而MySQL在做查詢時只能選擇1個索引來使用,因此複合索引比較有優勢,但是複合索引的使用限制比較多,後面在實際操作的時候再說
