判斷問題sql
判斷SQL是否有問題時可以通過兩個表象進行判斷：
- 系統級別表象
- CPU消耗嚴重
- IO等待嚴重
- 頁面響應時間過長
- 應用的日志出現超時等錯誤
可以使用sar命令，top命令查看當前系統狀態。
也可以通過Prometheus、Grafana等監控工具觀察系統狀態。
- SQL語句表象
- 冗長
- 執行時間過長
- 從全表掃描獲取數據
- 執行計劃中的rows、cost很大
冗長的SQL都好理解，一段SQL太長閱讀性肯定會差，而且出現問題的頻率肯定會更高。更進一步判斷SQL問題就得從執行計劃入手，如下所示：
執行計劃告訴我們本次查詢走了全表掃描Type=ALL，rows很大(9950400)基本可以判斷這是一段”有味道”的SQL。
獲取問題SQL
不同數據庫有不同的獲取方法，以下為目前主流數據庫的慢查詢SQL獲取工具
- mysql
- 慢查詢日志
- 測試工具loadrunner
- Percona公司的ptquery等工具
- Oracle
- AWR報告
- 測試工具loadrunner等
- 相關內部視圖如v$sql、v$session_wait等
- GRID CONTROL監控工具
- 達夢數據庫
- AWR報告
- 測試工具loadrunner等
- 達夢性能監控工具（dem）
- 相關內部視圖如v$sql、v$session_wait等
SQL編寫技巧
SQL編寫有以下幾個通用的技巧：
? 合理使用索引
索引少了查詢慢；索引多了占用空間大，執行增刪改語句的時候需要動態維護索引，影響性能選擇率高（重復值少）且被where頻繁引用需要建立B樹索引；一般join列需要建立索引；復雜文檔類型查詢采用全文索引效率更好；索引的建立要在查詢和DML性能之間取得平衡；復合索引創建時要注意基于非前導列查詢的情況
? 使用UNION ALL替代UNION
UNION ALL的執行效率比UNION高，UNION執行時需要排重；UNION需要對數據進行排序
? 避免select * 寫法
執行SQL時優化器需要將 * 轉成具體的列；每次查詢都要回表，不能走覆蓋索引。
? JOIN字段建議建立索引
一般JOIN字段都提前加上索引
? 避免復雜SQL語句
提升可閱讀性；避免慢查詢的概率；可以轉換成多個短查詢，用業務端處理
? 避免where 1=1寫法
? 避免order by rand()類似寫法
RAND()導致數據列被多次掃描
SQL優化 ? ?執行計劃
完成SQL優化一定要先讀執行計劃，執行計劃會告訴你哪些地方效率低，哪里可以需要優化。我們以MYSQL為例，看看執行計劃是什么。（每個數據庫的執行計劃都不一樣，需要自行了解）
| 字段 | 解釋 |
|---|---|
| id | 每個被獨立執行的操作標識，標識對象被操作的順序，id值越大，先被執行，如果相同，執行順序從上到下 |
| select_type | 查詢中每個select 字句的類型 |
| table | 被操作的對象名稱，通常是表名，但有其他格式 |
| partitions | 匹配的分區信息(對于非分區表值為NULL) |
| type | 連接操作的類型 |
| possible_keys | 可能用到的索引 |
| key | 優化器實際使用的索引(最重要的列) 從最好到最差的連接類型為const、eq_reg、ref、range、index和ALL。當出現ALL時表示當前SQL出現了“壞味道” |
| key_len | 被優化器選定的索引鍵長度，單位是字節 |
| ref | 表示本行被操作對象的參照對象，無參照對象為NULL |
| rows | 查詢執行所掃描的元組個數（對于innodb，此值為估計值） |
| filtered | 條件表上數據被過濾的元組個數百分比 |
| extra | 執行計劃的重要補充信息，當此列出現Using filesort , Using temporary 字樣時就要小心了，很可能SQL語句需要優化 |
接下來我們用一段實際優化案例來說明SQL優化的過程及優化技巧。
優化案例
-
表結構
-
CREATE?TABLE?`a`?(?`id`?int(11)?NOT?NULLAUTO_INCREMENT,?`seller_id`?bigint(20)?DEFAULT?NULL,?`seller_name`?varchar(100)?CHARACTER?SET?utf8?COLLATE?utf8_bin?DEFAULT?NULL,?`gmt_create`?varchar(30)?DEFAULT?NULL,?PRIMARY?KEY?(`id`)?);?CREATE?TABLE?`b`?(?`id`?int(11)?NOT?NULLAUTO_INCREMENT,?`seller_name`?varchar(100)?DEFAULT?NULL,?`user_id`?varchar(50)?DEFAULT?NULL,?`user_name`?varchar(100)?DEFAULT?NULL,?`sales`?bigint(20)?DEFAULT?NULL,?`gmt_create`?varchar(30)?DEFAULT?NULL,?PRIMARY?KEY?(`id`)?);?CREATE?TABLE?`c`?(?`id`?int(11)?NOT?NULLAUTO_INCREMENT,?`user_id`?varchar(50)?DEFAULT?NULL,?`order_id`?varchar(100)?DEFAULT?NULL,?`state`?bigint(20)?DEFAULT?NULL,?`gmt_create`?varchar(30)?DEFAULT?NULL,?PRIMARY?KEY?(`id`)?);
-
三張表關聯，查詢當前用戶在當前時間前后10個小時的訂單情況，并根據訂單創建時間升序排列，具體SQL如下
-
select?a.seller_id,??????????a.seller_name,??????????b.user_name,??????????c.state???from?a,????????b,????????c???where?a.seller_name?=?b.seller_name?????and?b.user_id?=?c.user_id?????and?c.user_id?=?17?????and?a.gmt_create???????BETWEEN?DATE_ADD(NOW(),?INTERVAL?–?600?MINUTE)???????AND?DATE_ADD(NOW(),?INTERVAL?600?MINUTE)???order?by?a.gmt_create
-
查看數據量
-
原執行時間
-
原執行計劃
-
初步優化思路
-
SQL中 where條件字段類型要跟表結構一致，表中user_id 為varchar(50)類型，實際SQL用的int類型，存在隱式轉換，也未添加索引。將b和c表user_id 字段改成int類型。
-
因存在b表和c表關聯，將b和c表user_id創建索引
-
因存在a表和b表關聯，將a和b表seller_name字段創建索引
-
利用復合索引消除臨時表和排序
-
初步優化SQL
-
alter?table?b?modify?`user_id`?int(10)?DEFAULT?NULL;???alter?table?c?modify?`user_id`?int(10)?DEFAULT?NULL;???alter?table?c?add?index?`idx_user_id`(`user_id`);???alter?table?b?add?index?`idx_user_id_sell_name`(`user_id`,`seller_name`);???alter?table?a?add?index?`idx_sellname_gmt_sellid`(`gmt_create`,`seller_name`,`seller_id`);
-
查看優化后執行時間
-
查看優化后執行計劃
-
查看warnings信息
-
繼續優化
-
alter?table?a?modify?"gmt_create"?datetime?DEFAULT?NULL
-
查看執行時間
-
查看執行計劃
優化總結
- 查看執行計劃 explain
- 如果有告警信息，查看告警信息 show warnings;
- 查看SQL涉及的表結構和索引信息
- 根據執行計劃，思考可能的優化點
- 按照可能的優化點執行表結構變更、增加索引、SQL改寫等操作
- 查看優化后的執行時間和執行計劃
如果優化效果不明顯，重復第四步操作
推薦 《mysql視頻教程》??
