重現一條簡單SQL的優化過程

来源:https://www.cnblogs.com/greatsql/archive/2023/03/31/17275418.html
-Advertisement-
Play Games

GreatSQL社區原創內容未經授權不得隨意使用,轉載請聯繫小編並註明來源。 GreatSQL是MySQL的國產分支版本,使用上與MySQL一致。 作者: JennyYu 文章來源:GreatSQL社區投稿 背景 接到客戶訴求說一條SQL長時間運行不出結果,讓給看看怎麼回事,SQL不複雜,優化措施也 ...


  • GreatSQL社區原創內容未經授權不得隨意使用,轉載請聯繫小編並註明來源。
  • GreatSQL是MySQL的國產分支版本,使用上與MySQL一致。
  • 作者: JennyYu
  • 文章來源:GreatSQL社區投稿

背景

接到客戶訴求說一條SQL長時間運行不出結果,讓給看看怎麼回事,SQL不複雜,優化措施也不複雜,但是要想SQL達到最優狀態,也是需要經過一番考量並做出選擇的。下麵借實驗還原一下此SQL優化過程。

實驗:

資料庫環境:MySQL5.7.39

測試表結構如下:

mysql> show create table t_1\G
*************************** 1. row ***************************
       Table: t_1
Create Table: CREATE TABLE `t_1` (
  `w_id` int(11) DEFAULT NULL,
  `w_name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)


mysql> show create table t_2\G
*************************** 1. row ***************************
       Table: t_2
Create Table: CREATE TABLE `t_2` (
  `i_id` int(11) NOT NULL,
  `i_name` varchar(24) DEFAULT NULL,
  `i_price` decimal(5,2) DEFAULT NULL,
  `i_data` varchar(50) DEFAULT NULL,
  `i_im_id` int(11) NOT NULL,
  PRIMARY KEY (`i_im_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> show create table t_3\G
*************************** 1. row ***************************
       Table: t_3
Create Table: CREATE TABLE `t_3` (
  `s_w_id` int(11) NOT NULL,
  `s_i_id` int(11) NOT NULL,
  `s_quantity` int(11) DEFAULT NULL,
  `s_ytd` int(11) DEFAULT NULL,
  `s_order_cnt` int(11) DEFAULT NULL,
  `s_remote_cnt` int(11) DEFAULT NULL,
  `s_data` varchar(50) DEFAULT NULL,
  `s_dist_01` char(24) DEFAULT NULL,
  `s_dist_02` char(24) DEFAULT NULL,
  `s_dist_03` char(24) DEFAULT NULL,
  `s_dist_04` char(24) DEFAULT NULL,
  `s_dist_05` char(24) DEFAULT NULL,
  `s_dist_06` char(24) DEFAULT NULL,
  `s_dist_07` char(24) DEFAULT NULL,
  `s_dist_08` char(24) DEFAULT NULL,
  `s_dist_09` char(24) DEFAULT NULL,
  `s_dist_10` char(24) DEFAULT NULL,
  `t_2_id` int(11) DEFAULT NULL,
  `t_1_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`s_w_id`,`s_i_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

Create Table: CREATE TABLE `t_4` (
  `w_name` varchar(10) DEFAULT NULL,
  `s_i_id` int(11) NOT NULL,
  `s_quantity` int(11) DEFAULT NULL,
  `s_ytd` int(11) DEFAULT NULL,
  `s_order_cnt` int(11) DEFAULT NULL,
  `s_remote_cnt` int(11) DEFAULT NULL,
  `s_data` varchar(50) DEFAULT NULL,
  `t_2_id` int(11) DEFAULT NULL,
  `i_name` varchar(24) DEFAULT NULL,
  `i_price` decimal(5,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

其中t_1表25條記錄,t_2表100條記錄,t_3表500萬條數據。我這裡實驗數據量少些,客戶實際業務表數據量分別是(30,150,2700萬)。t_4表為一個歷史數據歸檔表,用於插入數據。

SQL文本展示如下:

insert into t_4
SELECT
  c.w_name,
  a.s_i_id,
  a.s_quantity,
  a.s_ytd,
  a.s_order_cnt,
  a.s_remote_cnt,
  a.s_data,
  a.t_2_id,
  b.i_name,
  b.i_price
FROM
 t_3 a,
 t_2 b,
 t_1 c
WHERE
 a.t_2_id = b.i_id
and a.t_1_id = c.w_id
and a.s_ytd = 0;

查看語句中select部分的執行計劃如下圖所示:

圖片

看到這個計劃,就想對資料庫說一句:"您辛苦了!"。

優化器選擇先對兩個小表c,b進行關聯,然後得到的結果集再與大表a進行關聯,因為語句中c,b兩個表沒有欄位進行直接關聯,所以這兩個表連接後的結果集是一個笛卡爾積25 *100=2500,因為大表的關聯欄位上沒有索引,所以需要對最內層的大表全表掃描2500次。

這是不是一個大工程呢?資料庫任勞任怨,你讓它乾,它就乾,只要你等得起就可以。事實上我們是沒有耐心等的。我本來還想看看資料庫到底用多久才能給出結果,等了10分鐘,實在沒有耐心繼續等下去了。

這條SQL不複雜吧,就是三張表進行關聯,但是關聯欄位上都沒有索引,都進行了全表掃描。那麼解決措施就是加索引,但是索引怎麼加就需要做出選擇了。

有同事就提出這個SQL在大表上全表掃描2500次,在大表的關聯欄位上加上索引就可以了,看到這裡,你有沒有認同這個見解呢?我想應該有很多小伙伴是認同的。

不錯,給大表加上索引就不用全表掃描了,首先大表加索引,會鎖表很長時間,這個索引在客戶的生產環境須等到變更視窗才能加,客戶等不及,其次你有考慮過這真的是最好的辦法嗎?

因為我這是實驗環境,可以隨時給大表加索引,那接下來我們就給大表加上索引試試效果。

mysql> alter table t_3 add key(t_1_id,t_2_id);
Query OK, 0 rows affected (28.35 sec)
Records: 0  Duplicates: 0  Warnings: 0

索引加好之後,執行計劃如下:

圖片

可以看出優化器並沒有選擇走索引,依然是使用BNL優化策略,進行全表掃描,為什麼不走索引呢?應該是優化器認為索引掃描的成本高於全表掃描的成本,因為這條語句最終結果要返回大表的90%以上的數據,走索引後回表代價是很高的。這一點我們是不認同優化器的,怎麼著2500次全表掃描也比每次通過索引範圍掃描的代價要高呀,好吧,既然不認同,那麼使用force index來干涉優化器決策,讓它使用索引。

執行計劃如下圖所示:

圖片

執行計劃中顯示索引用上了,那實際執行效果如何呢?

mysql> insert into t_4
    -> SELECT
    ->   c.w_name,
    ->   a.s_i_id,
    ->   a.s_quantity,
    ->   a.s_ytd,
    ->   a.s_order_cnt,
    ->   a.s_remote_cnt,
    ->   a.s_data,
    ->   a.t_2_id,
    ->   b.i_name,
    ->   b.i_price
    -> FROM
    ->  t_3 a force index(t_1_id),
    ->  t_2 b,
    ->  t_1 c
    -> WHERE
    ->  a.t_2_id = b.i_id
    -> and a.t_1_id = c.w_id
    -> and a.s_ytd = 0;
Query OK, 4800000 rows affected (4 min 43.57 sec)
Records: 4800000  Duplicates: 0  Warnings: 0

確實效率不錯,500萬數據需要4 min 43.57 sec,生產環境的2700萬數據大概需要半個小時左右。

但這是不是效率最高的辦法呢,因為最終結果集會返回大表的90%以上的數據,所以需要對大量的索引數據回表,因為回表是會產生隨機IO的,這個回表代價確實比較高,優化器預設也沒有選擇這種執行計劃。如果我們給小表的關聯欄位上加索引會是什麼效果呢?

接下來我給兩個小表的關聯欄位上加了索引。

mysql> alter table t_2 add key(i_id);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t_1 add key(w_id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

我們去掉大表的force index,不幹涉優化器,讓優化器自己做決策。執行計劃如下:

圖片

上圖的執行計劃顯示,優化器選擇了對大表全表掃描,大表做驅動表,驅動兩個小表。那這樣的實際效果如何呢?

mysql> insert into t_4
    -> SELECT
    ->   c.w_name,
    ->   a.s_i_id,
    ->   a.s_quantity,
    ->   a.s_ytd,
    ->   a.s_order_cnt,
    ->   a.s_remote_cnt,
    ->   a.s_data,
    ->   a.t_2_id,
    ->   b.i_name,
    ->   b.i_price
    -> FROM
    ->  t_3 a,
    ->  t_2 b,
    ->  t_1 c
    -> WHERE
    ->  a.t_2_id = b.i_id
    -> and a.t_1_id = c.w_id
    -> and a.s_ytd = 0;
Query OK, 4800000 rows affected (1 min 59.06 sec)
Records: 4800000  Duplicates: 0  Warnings: 0

這種方式耗時1min 59.06sec ,效率提高1倍多,生產環境的大數據量,效率提升應該更明顯。果然採用大表驅動小表這種方式效率提高了,優化器的選擇是對的。

選擇這種方式的好處:

1.SQL的執行效率高一倍

2.節省空間,因為大表的索引會占用很大的磁碟空間。

3.響應及時,避免了必須等到變更視窗才能加索引的麻煩。

4.不用修改SQL語句

該如何選擇是不是很清楚了呢?

到這裡似乎優化就結束了,但是如果想要精益求精,追求極致的話,小表上的索引可以建成覆蓋索引,防止小表回表取數據。

mysql> alter table t_1 drop key w_id;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t_2 drop key i_id;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t_2 add key(i_id,i_name,i_price);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t_1 add key(w_id,w_name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

執行效果如下:

mysql> insert into t_4
    -> SELECT
    ->   c.w_name,
    ->   a.s_i_id,
    ->   a.s_quantity,
    ->   a.s_ytd,
    ->   a.s_order_cnt,
    ->   a.s_remote_cnt,
    ->   a.s_data,
    ->   a.t_2_id,
    ->   b.i_name,
    ->   b.i_price
    -> FROM
    ->  t_3 a,
    ->  t_2 b,
    ->  t_1 c
    -> WHERE
    ->  a.t_2_id = b.i_id
    -> and a.t_1_id = c.w_id
    -> and a.s_ytd = 0;
Query OK, 4800000 rows affected (1 min 38.99 sec)
Records: 4800000  Duplicates: 0  Warnings: 0

可以看出,小表上的索引建成覆蓋索引,耗時又縮短了20秒,執行效率更高了。

至此該條SQL的優化結束。

總結

1.本條SQL的最終執行計劃是大表驅動小表,這也算是給上篇文章《NL連接一定是小表驅動大表效率高嗎》提供了一個案例。

2.優化措施可能有很多不同的選擇,要根據實際情況選擇最優的,不要草率做出決定。

3.精益求精是優化的極致,但是有時候也是需要做出折中選擇的,達到業務運行的要求是目的,這點以後遇到案例再說。


Enjoy GreatSQL

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Linux 文件許可權 文件許可權和文件類型共有10個字元組成,這10個字元可以分成三部分 $$ d+rwx+rwx+rw-\d:表示文件類型\2-4位(第一組rwx):表示文件所有者的對文件的許可權\5-7位(第二組rwx):表示文件所有者所在組的用戶對文件的許可權\8-10位(rw-):表示其他用戶對文 ...
  • 一、項目要求 1、創建role,通過role完成項目(可能需要多個role) 2、部署nginx調度器(node2主機) 3、部署2台lnmp伺服器(node3,node4主機) 4、部署mariadb資料庫(node5主機) 主要用的ansible實現自動化部署,ansible的安裝教程省略,控制 ...
  • 昨天看到一個MySQL資料庫設計原則:強烈建議表的主鍵使用整型自增主鍵。為啥呢? 要弄明白這個問題首先需要瞭解MySQL是如何維護數據的,你需要知道以下幾點: MySQL的InnoDB存儲引擎是在B+樹上維護表數據的 B+樹是一種平衡樹 在這棵樹上,每個節點在電腦中叫做數據頁,預設16k 樹的葉子 ...
  • 一、基本概念 ——後續的內容將會記錄作者在計科學習內容 DB(資料庫):存儲數據的倉庫,數據是有組織進行存儲 DBMS(資料庫管理系統):操縱和管理資料庫的大型軟體 SQL:操縱關係資料庫的編程語言,是一套標準 有Mysql,Oracle,SQLSever,PostgreSQl RDBMS(關係型數 ...
  • 簡述 Db2 是一款具有悠久歷史的關係型資料庫,由 IBM 公司開發和維護,廣泛應用於金融級業務場景。 CloudCanal 近期提供了 Db2 為源端的數據遷移同步 功能,用戶可以便利地將 Db2 中數據實時同步到其他資料庫,實現數據更廣泛、更實時的應用。 功能介紹 目標資料庫和能力 | 目標端數 ...
  • 摘要:華為雲站點數字化平臺CloudMap攜手華為雲圖引擎GES打造雲服務全棧拓撲,網路流量路徑和雲服務動態依賴等空間關係數據,支撐現網運行態風險識別和分鐘級定位定界,構建業界領先的數字化能力。 本文分享自華為雲社區《構建站點數字孿生,支撐確定性運維:華為雲九洲雲圖CloudMap》,作者:HWCl ...
  • 當前,數據對金融機構業務和發展的重要性日益凸顯,釋放數據生產力已經成為金融機構進行全面數字化轉型的核心,這就要求金融機構以數據資產為綱不斷提升自身數據資產管理能力。 本期DTALK我們邀請到雅拓信息解決方案專家 尹曉中,為大家帶來《數據資產管理——金融機構數據價值釋放的必經之路》。 分享大綱劇透: ...
  • 本次需求場景主要為實現將flinksql中collect()函數輸出的Mutiset(VARCHAR<100>)多行結果轉換為字元串。 一、FlinkSQL自定義函數分類 Flink SQL 的自定義函數是用戶可以自行編寫的一種函數,用於擴展 Flink SQL 的功能。自定義函數可以在 SQL 查 ...
一周排行
    -Advertisement-
    Play Games
  • 一個自定義WPF窗體的解決方案,借鑒了呂毅老師的WPF製作高性能的透明背景的異形視窗一文,併在此基礎上增加了滑鼠穿透的功能。可以使得透明窗體的滑鼠事件穿透到下層,在下層窗體中響應。 ...
  • 在C#中使用RabbitMQ做個簡單的發送郵件小項目 前言 好久沒有做項目了,這次做一個發送郵件的小項目。發郵件是一個比較耗時的操作,之前在我的個人博客裡面回覆評論和友鏈申請是會通過發送郵件來通知對方的,不過當時只是簡單的進行了非同步操作。 那麼這次來使用RabbitMQ去統一發送郵件,我的想法是通過 ...
  • 當你使用Edge等瀏覽器或系統軟體播放媒體時,Windows控制中心就會出現相應的媒體信息以及控制播放的功能,如圖。 SMTC (SystemMediaTransportControls) 是一個Windows App SDK (舊為UWP) 中提供的一個API,用於與系統媒體交互。接入SMTC的好 ...
  • 最近在微軟商店,官方上架了新款Win11風格的WPF版UI框架【WPF Gallery Preview 1.0.0.0】,這款應用引入了前沿的Fluent Design UI設計,為用戶帶來全新的視覺體驗。 ...
  • 1.簡單使用實例 1.1 添加log4net.dll的引用。 在NuGet程式包中搜索log4net並添加,此次我所用版本為2.0.17。如下圖: 1.2 添加配置文件 右鍵項目,添加新建項,搜索選擇應用程式配置文件,命名為log4net.config,步驟如下圖: 1.2.1 log4net.co ...
  • 之前也分享過 Swashbuckle.AspNetCore 的使用,不過版本比較老了,本次演示用的示例版本為 .net core 8.0,從安裝使用開始,到根據命名空間分組顯示,十分的有用 ...
  • 在 Visual Studio 中,至少可以創建三種不同類型的類庫: 類庫(.NET Framework) 類庫(.NET 標準) 類庫 (.NET Core) 雖然第一種是我們多年來一直在使用的,但一直感到困惑的一個主要問題是何時使用 .NET Standard 和 .NET Core 類庫類型。 ...
  • WPF的按鈕提供了Template模板,可以通過修改Template模板中的內容對按鈕的樣式進行自定義。結合資源字典,可以將自定義資源在xaml視窗、自定義控制項或者整個App當中調用 ...
  • 實現了一個支持長短按得按鈕組件,單擊可以觸發Click事件,長按可以觸發LongPressed事件,長按鬆開時觸發LongClick事件。還可以和自定義外觀相結合,實現自定義的按鈕外形。 ...
  • 一、WTM是什麼 WalkingTec.Mvvm框架(簡稱WTM)最早開發與2013年,基於Asp.net MVC3 和 最早的Entity Framework, 當初主要是為瞭解決公司內部開發效率低,代碼風格不統一的問題。2017年9月,將代碼移植到了.Net Core上,併進行了深度優化和重構, ...