分享一個將MySQL資料庫查詢速度提升300倍的方法

在進行性能分析前,需要先定位耗時查詢。

MySQL 提供瞭內建的耗時查詢日志。使用前,打開 my.cnf 文件,將slow_query_log 設置為”On”。 同時將 long_query_time 設置為一個對一次查詢來說比較慢的時間(秒數),比如 0.2。slow_query_log_file 設置為你想保存日志文件的路徑。然後運行代碼,執行時間超過設定上限的查詢將會被記錄到日志中。

一旦定位這些煩人的查詢,就可以探究慢的原因。MySQL提供瞭EXPLAIN關鍵字。可以與SELECT、DELETE、INSERT、REPLACE、UPDATE語句一起使用。隻需要如下一樣添加到查詢開始處:

EXPLAINSELECTpicture.id,picture.title

FROMpicture

LEFTJOINalbumONpicture.album_id=album.idWHEREalbum.user_id=1;

結果是對數據訪問過程的解析。每行代表一個查詢相關的數據表。

重點是表名、使用的key、查詢執行時所掃描的行數。

這個查詢掃描瞭picture表2百萬行,對每個picture記錄掃描瞭album表2萬行。意味著,該查詢實際掃描瞭album表400億行。這個過程可以更高效。

索引

使用索引,可以極大提升性能。比如地址簿中的名字。找一個名字,你可以從頭翻到尾,也可以找到相應的字符標簽頁來快速定位。

使用索引可以避免無必要的表掃描。比如在picture.album_id上添加索引:

ALTER TABLE picture ADD INDEX(album_id);

現在執行查詢,就不會掃描整個picture列表瞭。首先,所有的album表會被掃描以找到屬於該用戶的album,然後,使用album_id列索引快速定位對應的picture。掃描的行數減少到瞭20萬,比原始查詢快瞭317倍。

按照以下方式添加索引可以確保兩張表都能使用對應鍵:

ALTER TABLE album ADD INDEX(user_id);

這次,album表不會被全部掃描,通過user_id鍵就可以精確定位相應的album。然後這100個album被掃描後,通過album_id鍵相關的picture也可以快速找到。每張表都用到瞭一個鍵進行查詢性能優化,結果比原始查詢快瞭380倍。

但是並不意味著可以隨意添加索引,因為索引會導致寫資料庫用時增長。所以使用索引導致讀用時縮短,寫用時增長。因此,如果能提升讀性能時再添加索引。使用EXPLAIN來確認索引是否被查詢用到,沒有就刪除。

You May Also Like