資料庫最佳化技巧 - SQL語句最佳化

其實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、session_wait等
• GRID CONTROL監控工具
• 達夢資料庫
• AWR報告
• 測試工具loadrunner等
• 達夢效能監控工具（dem）
• 相關內部檢視如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為例，看看執行計劃是什麼。（每個資料庫的執行計劃都不一樣，需要自行了解）explain 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；
  

• 檢視資料量

• 原執行時間

• 原執行計劃

• 初步最佳化思路

1. SQL中 where條件欄位型別要跟表結構一致，表中 user_id 為varchar(50)型別，實際SQL用的int型別，存在隱式轉換，也未新增索引。將b和c表 user_id 欄位改成int型別。
2. 因存在b表和c表關聯，將b和c表 user_id建立索引
3. 因存在a表和b表關聯，將a和b表 seller_name欄位建立索引
4. 利用複合索引消除臨時表和排序

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`);