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

ITPUB社群 發表於 2022-11-23
資料庫 SQL


拿到一段需要最佳化的慢查詢sql,很多人都感覺無從下手。

其實SQL最佳化是有技巧與套路的,閱讀完本文你將學會這些最佳化套路,讓你成為別人眼中的資料庫高手!

判斷問題SQL

判斷SQL是否有問題時可以透過兩個表象進行判斷:

  • 系統級別表象
    • CPU消耗嚴重
    • IO等待嚴重
    • 頁面響應時間過長
    • 應用的日誌出現超時等錯誤

可以使用 sar命令,top命令檢視當前系統狀態。資料庫最佳化技巧 - SQL語句最佳化

也可以透過 Prometheus、Grafana等監控工具觀察伺服器狀態。(感興趣的可以翻看我之前的文章)資料庫最佳化技巧 - SQL語句最佳化

  • SQL語句表象
    • 冗長
    • 執行時間過長
    • 從全表掃描獲取資料
    • 執行計劃中的rows、cost很大

冗長的SQL都好理解,一段SQL太長閱讀性肯定會差,而且出現問題的頻率肯定會更高。更進一步判斷SQL問題就得從執行計劃入手,如下所示:資料庫最佳化技巧 - SQL語句最佳化

執行計劃告訴我們本次查詢走了全表掃描 Type=ALL,rows很大(9950400)基本可以判斷這是一段"有味道"的SQL。

獲取問題SQL

不同資料庫有不同的獲取方法,以下為目前主流資料庫的慢查詢SQL獲取工具

  • MySQL
    • 慢查詢日誌
    • 測試工具loadrunner
    • Percona公司的ptquery等工具
  • Oracle
    • AWR報告
    • 測試工具loadrunner等
    • 相關內部檢視如vsession_wait等
    • GRID CONTROL監控工具
  • 達夢資料庫
    • AWR報告
    • 測試工具loadrunner等
    • 達夢效能監控工具(dem)
    • 相關內部檢視如vsession_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語句最佳化

欄位解釋
id每個被獨立執行的操作標識,標識物件被操作的順序,id值越大,先被執行,如果相同,執行順序從上到下
select_type查詢中每個select 字句的型別
table被操作的物件名稱,通常是表名,但有其他格式
partitions匹配的分割槽資訊(對於非分割槽表值為NULL)
type連線操作的型別
possible_keys可能用到的索引
key最佳化器實際使用的索引(最重要的列) 從最好到最差的連線型別為 consteq_regrefrangeindexALL。當出現 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語句最佳化

  • 原執行時間資料庫最佳化技巧 - SQL語句最佳化

  • 原執行計劃資料庫最佳化技巧 - SQL語句最佳化

  • 初步最佳化思路

  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. 利用複合索引消除臨時表和排序
  • 初步最佳化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`);
  • 檢視最佳化後執行時間資料庫最佳化技巧 - SQL語句最佳化

  • 檢視最佳化後執行計劃資料庫最佳化技巧 - SQL語句最佳化

  • 檢視warnings資訊資料庫最佳化技巧 - SQL語句最佳化

  • 繼續最佳化
    alter table a modify "gmt_create" datetime DEFAULT NULL;

  • 檢視執行時間資料庫最佳化技巧 - SQL語句最佳化

  • 檢視執行計劃資料庫最佳化技巧 - SQL語句最佳化透過觀察執行計劃,到了這一步已經不再需要最佳化!

  • 最佳化總結
    總結一下完成一段SQL最佳化的思路與過程:
    1、檢視執行計劃 explain
    2、如果有告警資訊,檢視告警資訊 show warnings;
    3、檢視SQL涉及的表結構和索引資訊
    4、根據執行計劃,思考可能的最佳化點
    5、按照可能的最佳化點執行表結構變更、增加索引、SQL改寫等操作
    6、檢視最佳化後的執行時間和執行計劃
    7、如果最佳化效果不明顯,重複第四步操作


  • 總結

    這篇文章首先讓你瞭解慢查詢的表象,讓你可以透過一些工具識別出慢查詢語句;
    然後告訴你SQL最佳化的一些常用套路技巧,掌握這些套路技巧至少可以解決80%的SQL最佳化問題;
    最後透過一個示例從分析開始一步一步完成慢查詢語句的最佳化,其中檢視執行計劃是最佳化過程中最終要的操作,大家一定要掌握。


    來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/70024420/viewspace-2924783/,如需轉載,請註明出處,否則將追究法律責任。

    相關文章