MySQL資料庫SQL語句最佳化

判斷問題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 MySQL資料庫SQL語句最佳化sort , 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
1. 如果有警告訊息，查看警告訊息show warnings;
2. #查看SQL涉及的表格結構和索引訊息
3. 根據執行計劃，思考可能的最佳化點
4. 依照可能的最佳化點執行表格結構變更、增加索引、SQL改寫等操作
5. 查看最佳化後的執行時間和執行計畫

如果最佳化效果不明顯，重複第四步驟操作

推薦《

mysql影片教學》  #

以上是MySQL資料庫SQL語句最佳化的詳細內容。更多資訊請關注PHP中文網其他相關文章！