如何寫出高效能SQL

定量遍歷次數

在編寫一條查詢語句的時候，可以依據你要查詢資料表的資料總量，估算一下這條查詢大致需要遍歷多少行資料:

如果遍歷行數在百萬以內的，只要不是每秒鐘都要執行幾十上百次的頻繁查詢，可以認為是安全的。

遍歷資料行數在幾百萬的，查詢時間最少也要幾秒鐘，你就要仔細考慮有沒有優化的辦法。

遍歷行數達到千萬量級和以上的，我只能告訴你，這種查詢就不應該出現在你的系統中。當然我們這裡說的都是線上交易系統，離線分析類系統另說。

遍歷行數在千萬左右，是 MySQL 查詢的一個坎兒。MySQL 中單個表資料量，也要儘量控制在一千萬條以下，最多不要超過二三千萬這個量級。原因也很好理解，對一個千萬級別的表執行查詢，加上幾個 WHERE 條件過濾一下，符合條件的資料最多可能在幾十萬或者百萬量級，這還可以接受。但如果再和其他的表做一個聯合查詢，遍歷的資料量很可能就超過千萬級別了。所以，每個表的資料量最好小於千萬級別。

避免全表索引

絕大多數情況下，我們編寫的查詢語句，都應該使用索引，避免去遍歷整張表，也就是通常說的，避免全表掃描。你在每次開發新功能，需要給資料庫增加一個新的查詢時，都要評估一下，是不是有索引可以支撐新的查詢語句，如果有必要的話，需要新建索引來支援新增的查詢。

增加索引付出的代價是，會降低資料插入、刪除和更新的效能。這個也很好理解，增加了索引，在資料變化的時候，不僅要變更資料表裡的資料，還要去變更每個索引。所以，對於更新頻繁並且對更新效能要求較高的表，可以儘量少建索引。而對於查詢較多更新較少的表，可以根據查詢的業務邏輯，適當多建一些索引。

分析 SQL 執行計劃

在 MySQL 中使用執行計劃也非常簡單，只要在你的 SQL 語句前面加上 EXPLAIN 關鍵字，然後執行這個查詢語句就可以了。

比如有一個使用者表，包含使用者 ID、姓名、部門編號和狀態這幾個欄位：

我們希望查詢某個二級部門下的所有人，查詢條件就是，部門代號以 00028 開頭的所有人。下面這兩個 SQL，他們的查詢結果是一樣的，都滿足要求，但是，哪個查詢效能更好呢？

SELECT * FROM user WHERE left(department_code, 5) = ‘00028’;SELECT * FROM user WHERE department_code LIKE ‘00028%’;

我們分別檢視一下這兩個 SQL 的執行計劃：

row 列:

MySQL 預估執行這個 SQL 可能會遍歷的資料行數。第一個 SQL 遍歷了四千多行，這就是整個 User 表的資料條數；第二個 SQL 只有 8 行，這 8 行其實就是符合條件的 8 條記錄。顯然第二個 SQL 查詢效能要遠遠好於第一個 SQL。

type 列:

表示這個查詢的訪問型別。ALL 代表全表掃描，這是最差的情況。range 代表使用了索引，在索引中進行範圍查詢，因為第二個 SQL 語句的 WHERE 中有一個 LIKE 的查詢條件。如果直接命中索引，type 這一列顯示的是 index。如果使用了索引，可以在 key 這一列中看到，實際上使用了哪個索引。

總結

在開發階段，衡量一個 SQL 查詢語句查詢效能的手段是，估計執行 SQL 時需要遍歷的資料行數。遍歷行數在百萬以內，可以認為是安全的 SQL，百萬到千萬這個量級則需要仔細評估和優化，千萬級別以上則是非常危險的。為了減少慢 SQL 的可能性，每個資料表的行數最好控制在千萬以內。 索引可以顯著減少查詢遍歷資料的數量，所以提升 SQL 查詢效能最有效的方式就是，讓查詢儘可能多的命中索引，但索引也是一把雙刃劍，它在提升查詢效能的同時，也會降低資料更新的效能。 對於複雜的查詢，最好使用 SQL 執行計劃，事先對查詢做一個分析。在 SQL 執行計劃的結果中，可以看到查詢預估的遍歷行數，命中了哪些索引。執行計劃也可以很好地幫助你優化你的查詢語句。

 

 

轉自：https://sucaihu.top/187/