SQL優化工作記錄

www.Rui發表於2020-11-18

關鍵詞:SQL優化

 


激動呀,沒想到自己會在工作中遇到SQL調優的工作。由於我所在的專案組是開發一個管理人員發系統,該系統是以公司為單位設計資料庫的,即不同公司的資料放到不同的庫中,自然也就不會放到同一張表中。這樣做的話會讓單張表的資料量不會太大。所以在大大大大大部分情況下,是不會出現這種從肉眼能明顯感覺到介面返回結果很慢的情況。

但是,也存在一個極小部分的資料,一個公司的賬號管理著二百多個公司的資料,員工總人數將近3000,這個時候這些資料就會出現在同一張表中了。於是,在生產環境,該公司的一個檢視人員請假記錄的頁面就出現了響應很慢的情況。該頁面已經分頁查詢了,每頁只查詢10條結果,介面的響應時間卻足足要50多秒。這.....也.....太.....慢.....了!

於是乎,經過一個星期的調優,無論是程式碼層面,還是SQL層面,將該公司的好多載入慢的頁面提高了明顯的效能,比如上述的50多秒響應的介面提高到了2秒內返回結果。當資料量越來越大的時候,資料庫查詢真的是影響效能的一個關鍵因素,尤其是一個很爛的SQL。

其實關於SQL調優,一般開發的時候很少會考慮效能和優化這些方面,專案前期資料量都很少,開發和測試環境一般也不會提供大資料量來使用。只有當資料量大出現問題了才會去做調優。可以說是有針對性的調優吧。

在調優之前,還需要了解下SQL執行的順序,即一條SQL,先執行哪部分,再執行哪部分。具體請戳《Select語句執行順序》


 █ 總結

(1)不要在程式碼中迴圈查詢資料庫

正所謂,"前人埋坑,後人涼涼"。在這個專案中,以前別人寫的程式碼很多會在迴圈中去查詢資料庫,比如對於請假列表,申請表中儲存的是員工ID,但是返回給前端需要員工的姓名,這個時候需要關聯申請表和員工表了。以前的程式是這樣寫的,先查10條申請表記錄,然後再迴圈10條記錄,再根據記錄中的申請者ID去員工表中查詢姓名。這樣查詢10條記錄,與資料庫就互動了11次。其實對於這樣的需求,完全可以一次性的查詢出需要的資料的。上面的例子比較簡單,一般人也不會這麼幹。對於有些複雜的需求,儘量能夠減少與資料庫互動的次數,可以將資料都拿到記憶體中,接著再進行處理。以空間換時間,可以大大提高效率。

(2)多表連線查詢時,查詢條件請使用索引

當初第一批開發人員為了讓功能能夠實現使用者定製化,將一塊功能的資料存放在了多個表中,所以在SQL語句中會用到大量的連線查詢。在進行多表連線查詢的時候,ON後面的條件包含了索引列的話,查詢速度也是非常快的。比如 tableA LEFT JOIN tableB on tableA.columnA = tableB.columnB,如果columnA是tableA的索引列的話,查詢會非常快。

(3)SELECT欄位能少則少

在本次調優中,也發現,當SELECT 的欄位很多時,也會嚴重影響查詢的效能,尤其是在多張表連線查詢中。但其實有些欄位根本就用不上,所以不需要查詢返回結果。所以,儘量SELECT只返回需要的欄位,能少則少。

(4)對於帶有分頁的連線查詢,若能夠先從主表中篩選出分頁資料,再連線其他表查詢的話,請這麼做

專案上的申請相關的表有四五個,主表是APPLY表,用於存放主要資訊,還有一些人員表,配置表等等。其實APPLY表有四萬多條資料吧。所以,對於分頁查詢的功能,如果能夠先從APPLY表分頁,比如先找出10條資料,再和其他表做連線查詢。

(5)當查詢條件需要用到一個重複值很多的列時,可以將其與一個唯一列做一個組合索引

當根據部門編號去員工表查詢員工的時候,部門編號的重複值一定是非常多的。當查詢條件寫成where deptId = '' 時,及時單獨設定部門列為索引列,但MySQL優化器有可能也不會使用,因為該列的重複值太多了。此時可以將該列與一個唯一列做組合索引,比如(deptId, id)。這樣在根據部門編號查詢的時候,也會使用到索引。

(6)學會使用EXPLAIN查詢查詢SQL的執行計劃

根據EXPLAIN的執行計劃,建立合適的索引以及調整SQL。關於EXPLAIN的詳細介紹,請戳《MySQL之EXPLAIN》

 

相關文章