系統優化怎麼做-SQL優化

來自專欄聊聊系統優化

前言

資料庫很重要！很重要！很重要！ 重要的事情說三遍。所以單獨用一篇來講述SQL怎麼優化。不過這裡說到一點，不建議在業務代碼里寫很多複雜業務SQL，基本儘可能的減少 join,子查詢 等，也就說盡量在應用層來解決問題，降低產生低效SQL的概率，資料庫只是完成數據存儲及最簡單查詢的組件。

SQL優化

主要4個方向，以下4個方向儘可能達到了，SQL的執行效率就提高了。

1. 避免全表掃描
2. SQL中儘可能不使用臨時表
3. 減小查詢中間結果集大小
4. 儘可能命中索引

發現慢SQL

DBA開啟MySQL的慢查詢日誌，對每日資料庫慢查詢進行監控。慢查詢後每日匯總提供開發進行處理。DBA給出指導意見。

分析執行計劃

主要看對SQL的執行過程中

explain [extended] select … from … where …

得到結果是

+—-+————-+——-+——-+——————-+———+———+——-+——+——-+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+—-+————-+——-+——-+——————-+———+———+——-+——+——-+

其中 table 表示是哪個表的數據。

• type比較重要。表示鏈接的類型。鏈接類型由好到壞的，依次是 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 一般情況，至少要達到 range 級別，最好是 ref 級別。否則可能會有性能問題。
• possible_keys 是指可以應用到該表的索引，如果為NULL則沒有。
• key 是指用到的索引。
• key_len 是索引的長度，在不影響查詢精度的情況下，值越小越好。
• ref 是指索引的那一列被使用了。一般會是個常數。
• rows 是指有多少行。
• extra 是指額外的信息。也是比較重要的。如果值為 distinct ，說明mysql 找到了域行聯合匹配的行，就不再查找了。

1. 如果值為 not exits : mysql優化了 left join ，一旦找到了 left join 匹配的行，便不再進行搜索了。
2. 如果值為 rang checked for each ： 沒有找到理想的索引。
3. 如果為 using filesort ，則需要改進sql了。這說明 mysql執行 需要 文件排序。這是比較影響效率的。
4. 如果為 using temporary , 這是使用了 臨時表。 這種情況也比較影響效率，sql需要改進。或者從應用層進行改進。
5. 如果為 where used 說明使用了where語句。如果 type為 all 或者 index ，一般會出現這樣的結果。這樣的問題，一般是查詢需要改進。

SQL優化實例

• 分頁查詢

第一種分頁寫法

select * from t where thread_id = 771025 and deleted = 0 order by gmt_create asc limit 0, 15;

原理：

一次性根據過濾條件取出所有欄位進行排序返回。

數據訪問開銷=索引IO + 索引全部記錄結果對應的表數據IO

缺點：

該種寫法越翻到後面執行效率越差，時間越長，尤其表數據量很大的時候。適用場景：當中間結果集很小（10000行以下）或者查詢條件複雜（指涉及多個不同查詢欄位或者多表連接）時適用。

第二種分頁寫法：

select t.* from ( select id from t where thread_id = 771025 and deleted = 0 order by gmt_create asc limit 0, 15) a, t where a.id = t.id;

前提：

假設t表主鍵是id列，且有覆蓋索引secondary key:(thread_id, deleted, gmt_create)

原理：

先根據過濾條件利用覆蓋索引取出主鍵id進行排序，再進行join操作取出其他欄位。

數據訪問開銷=索引IO+索引分頁後結果對應的表數據IO

優點：

每次翻頁消耗的資源和時間都基本相同，就像翻第一頁一樣

適用場景：

當查詢和排序欄位（即where子句和order by子句涉及的欄位）有對應覆蓋索引時，且中間結果集很大的情況時適用

• 批量SQL

減少和資料庫交互次數

INSERT ... ON DUPLICATE KEY UPDATE REPLACE INTO INSERT IGNORE INSERT INTO VALUES()

• 對同一個表的多次alter操作必須合併為一次操作。

mysql對錶的修改絕大部分操作都需要鎖表並重建表,而鎖表則會對線上業務造成影響。為減少這種影響,必須把對錶的多次alter操作合併為一次操作。例如,要給表t增加一個欄位b,同時給已有的欄位aa建立索引, 通常的做法分為兩步:

alter table t add column b varchar(10);

然後增加索引:

alter table t add index idx_aa(aa);

正確的做法是:

alter table t add column b varchar(10),add index idx_aa(aa);

總結

資料庫是有狀態的服務,變更複雜而且速度慢,如果把業務邏輯放到資料庫中,將會限制業務的快速發展。建議把業務邏輯提前,放到前端或中間邏輯層,而把資料庫作為存儲層,實現邏輯與存儲的分離。

思考題

1. 萬一經過SQL優化後，還是達不到要求，還有什麼手段能進行優化呢？
2. 在一個既有系統前期業務快速迭代，導致系統很多業務已經寫了很多低效的SQL，導致系統運行緩慢，領導需要快速解決運行緩慢的問題，有哪些手段可以用呢？

推薦閱讀：