重現一條簡單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文件許可權解讀
    Linux 文件許可權 文件許可權和文件類型共有10個字元組成，這10個字元可以分成三部分 $$ d+rwx+rwx+rw-\d:表示文件類型\2-4位（第一組rwx）：表示文件所有者的對文件的許可權\5-7位（第二組rwx）：表示文件所有者所在組的用戶對文件的許可權\8-10位（rw-）：表示其他用戶對文 ...
  • CentOS7-自動化部署web集群
    一、項目要求 1、創建role，通過role完成項目（可能需要多個role） 2、部署nginx調度器（node2主機） 3、部署2台lnmp伺服器（node3，node4主機） 4、部署mariadb資料庫（node5主機） 主要用的ansible實現自動化部署，ansible的安裝教程省略，控制 ...
  • 為什麼建議主鍵整型自增？
    昨天看到一個MySQL資料庫設計原則：強烈建議表的主鍵使用整型自增主鍵。為啥呢？ 要弄明白這個問題首先需要瞭解MySQL是如何維護數據的，你需要知道以下幾點： MySQL的InnoDB存儲引擎是在B+樹上維護表數據的 B+樹是一種平衡樹 在這棵樹上，每個節點在電腦中叫做數據頁，預設16k 樹的葉子 ...
  • Mysql之SQL語句基礎1
    一、基本概念 ——後續的內容將會記錄作者在計科學習內容 DB(資料庫)：存儲數據的倉庫，數據是有組織進行存儲 DBMS（資料庫管理系統）：操縱和管理資料庫的大型軟體 SQL：操縱關係資料庫的編程語言，是一套標準 有Mysql,Oracle,SQLSever,PostgreSQl RDBMS(關係型數 ...
  • CloudCanal 落地 DB2 數據遷移同步功能
    簡述 Db2 是一款具有悠久歷史的關係型資料庫，由 IBM 公司開發和維護，廣泛應用於金融級業務場景。 CloudCanal 近期提供了 Db2 為源端的數據遷移同步 功能，用戶可以便利地將 Db2 中數據實時同步到其他資料庫，實現數據更廣泛、更實時的應用。 功能介紹 目標資料庫和能力 | 目標端數 ...
  • 過億雲資源運維管控難？華為雲CloudMap帶你喝著咖啡做運維
    摘要：華為雲站點數字化平臺CloudMap攜手華為雲圖引擎GES打造雲服務全棧拓撲，網路流量路徑和雲服務動態依賴等空間關係數據，支撐現網運行態風險識別和分鐘級定位定界，構建業界領先的數字化能力。 本文分享自華為雲社區《構建站點數字孿生，支撐確定性運維：華為雲九洲雲圖CloudMap》，作者：HWCl ...
  • DTALK直播預約 | 數據資產管理：金融機構數據價值釋放的必經之路
    當前，數據對金融機構業務和發展的重要性日益凸顯，釋放數據生產力已經成為金融機構進行全面數字化轉型的核心，這就要求金融機構以數據資產為綱不斷提升自身數據資產管理能力。 本期DTALK我們邀請到雅拓信息解決方案專家 尹曉中，為大家帶來《數據資產管理——金融機構數據價值釋放的必經之路》。 分享大綱劇透： ...
  • FlinkSQL自定義函數開發
    本次需求場景主要為實現將flinksql中collect()函數輸出的Mutiset(VARCHAR<100>)多行結果轉換為字元串。 一、FlinkSQL自定義函數分類 Flink SQL 的自定義函數是用戶可以自行編寫的一種函數，用於擴展 Flink SQL 的功能。自定義函數可以在 SQL 查 ...
一周排行
    -Advertisement-
    Play Games
  • 6.swagger完善：界面顯示註釋+多版本控制
    周末，寫點簡單的水一下。 新版本的vs創建項目的時候可以選擇自帶一個swagger。然而這隻是基本的swagger功能。 幾個介面無所謂啦，隨著介面越來越多，就這麼丟給你，一時間也會懵逼，所以這篇文章要做的有兩個功能。 給swagger文檔添加註釋 給swagger添加切換“版本”的功能(也可以理解 ...
  • 攔截|篡改|偽造.NET類庫中不限於public的類和方法
    大家好，我是沙漠盡頭的狼。 本文首發於Dotnet9，介紹使用Lib.Harmony庫攔截第三方.NET庫方法，達到不修改其源碼並能實現修改方法邏輯、預期行為的效果，並且不限於只攔截public訪問修飾的類及方法，行文目錄： 什麼是方法攔截？ 示常式序攔截 非public方法怎麼攔截？ 總結 1. ...
  • async/await 致WPF卡死問題
    問題代碼： xmal：一個按鈕+一個顯示框 1 <Button Width="100" Height="50" Margin="10" Click="Button_Click">test</Button> 2 <TextBox x:Name="display" Width="300" Height= ...
  • Linux下Java項目部署
    前置條件 ​ 阿裡雲伺服器一臺（可在購買伺服器時勾選安裝寶塔選項，免去後面的寶塔安裝） ​ 設置阿裡雲伺服器密碼並登陸伺服器 ​ 以下操作均在伺服器Linux中進行(使用遠程連接工具登錄) 寶塔登錄 登錄阿裡雲伺服器在Linux命令行中輸入bt，查看寶塔信息 ​ 根據寶塔信息提供的網站登陸寶塔服務( ...
  • 7.4 通過API枚舉進程許可權
    GetTokenInformation 用於檢索進程或線程的令牌(Token)信息。Token是一個數據結構，其包含有關進程或線程的安全上下文，代表當前用戶或服務的安全標識符和許可權信息。GetTokenInformation函數也可以用來獲取這些安全信息，通常用於在運行時檢查某個進程或線程的許可權或安... ...
  • 【matplotlib基礎】--3D圖形
    matplotlib 在1.0版本之前其實是不支持3D圖形繪製的。 後來的版本中，matplotlib加入了3D圖形的支持，不僅僅是為了使數據的展示更加生動和有趣。更重要的是，由於多了一個維度，擴展了其展示數據分佈和關係的能力，可以一次從三個維度來比較數據。 下麵介紹在matplotlib中繪製各類 ...
  • Flutter/Dart第01天：Dart安裝和初體驗
    編寫一個App就能編譯發佈到iOS、Android和Web等各大平臺的跨平臺技術，各大廠商一直都有研究和發佈對應技術產品，目前最熱門的莫過於Flutter框架了。而Dart作為其唯一的編程語言，今天我們開始來體驗一下…… ...
  • 實現簡單線程池
    實現基本的線程池 前提：我們要實現的線程池有如下功能： 基本的線程池模型 能提交和運行任務 能正常關閉線程池 線程的拒絕策略 線程池擴容 縮容線程池 代碼地址： 1、線程池的介紹？ 線程池是什麼？ 線程池是一種利用池化技術來管理線程的一種技術。 當沒有線程池的時候，我們如何創建線程？ 繼承Threa ...
  • 基於FPGA 的SDRAM控制器
    SDRAM基本信息 儲存能力計算 4X16X4=256（Mbit），註意不是MByte SDRAM控制 sdram包含兩個部分：sdram_ctrl、fifo_ctrl。 sdram_ctrl：其頂層為SDRAM的控制模塊內部實例化了5個模塊，有初始化、自刷新、寫和讀模塊，還有一個仲裁模塊對這四個不 ...
  • 快速搭建雲原生開發環境（k8s+pv+prometheus+grafana）
    歡迎訪問我的GitHub 這裡分類和彙總了欣宸的全部原創(含配套源碼)：https://github.com/zq2599/blog_demos 本篇概覽 欣宸正在為接下新的Java雲原生實戰系列原創做準備，既然是實戰，少不了一套雲原生環境，以下內容是必不可少的： linux操作系統 kuberne ...
所有分類