MySQL實戰45講 18

来源:https://www.cnblogs.com/ydssx7/archive/2022/07/28/16528953.html
-Advertisement-
Play Games

18 | 為什麼這些SQL語句邏輯相同，性能卻差異巨大？ 在 MySQL 中，有很多看上去邏輯相同，但性能卻差異巨大的 SQL 語句。對這些語句使用不當的話，就會不經意間導致整個資料庫的壓力變大。 三個案例 案例一：條件欄位函數操作 假設你現在維護了一個交易系統，其中交易記錄表 tradelog 包 ...


18 | 為什麼這些SQL語句邏輯相同，性能卻差異巨大？

在 MySQL 中，有很多看上去邏輯相同，但性能卻差異巨大的 SQL 語句。對這些語句使用不當的話，就會不經意間導致整個資料庫的壓力變大。

三個案例

案例一：條件欄位函數操作

假設你現在維護了一個交易系統，其中交易記錄表 tradelog 包含交易流水號（tradeid）、交易員 id（operator）、交易時間（t_modified）等欄位，先忽略其他欄位。這個表的建表語句如下：

CREATE TABLE `tradelog` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `operator` int(11) DEFAULT NULL,
  `t_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`),
  KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

現在已經記錄了從 2016 年初到 2018 年底的所有數據，有一個需求是要統計發生在所有年份中 7 月份的交易記錄總數。SQL 語句可能會這麼寫：

select count(*) from tradelog where month(t_modified)=7;

發現執行了特別久，才返回了結果。

原因：如果對欄位做了函數計算，就用不上索引了，這是 MySQL 的規定。

Q：為什麼條件是 where t_modified='2018-7-1’的時候可以用上索引，而改成 where month(t_modified)=7 的時候就不行了？

A：

由於加了 month() 函數操作，MySQL 無法再使用索引快速定位功能，而只能使用全索引掃描。

下麵是這個 t_modified 索引的示意圖。方框上面的數字就是 month() 函數對應的值。

image-20220728142109346

如果你的 SQL 語句條件用的是 where t_modified='2018-7-1’的話，引擎就會按照上面綠色箭頭的路線，快速定位到 t_modified='2018-7-1’需要的結果。

B+ 樹提供的這個快速定位能力，來源於同一層兄弟節點的有序性。

但是，如果計算 month() 函數的話，傳入 7 的時候，在樹的第一層就不知道該怎麼辦了。

也就是說，對索引欄位做函數操作，可能會破壞索引值的有序性，因此優化器就決定放棄走樹搜索功能。

需要註意的是，優化器並不是要放棄使用這個索引。

在這個例子里，放棄了樹搜索功能，優化器可以選擇遍歷主鍵索引，也可以選擇遍歷索引 t_modified，優化器對比索引大小後發現，索引 t_modified 更小，遍歷這個索引比遍歷主鍵索引來得更快（註意是遍歷）。因此最終還是會選擇索引 t_modified。

使用 explain 命令，查看一下這條 SQL 語句的執行結果。

key="t_modified"表示的是，使用了 t_modified 這個索引；我在測試表數據中插入了 10 萬行數據，rows=100335，說明這條語句掃描了整個索引的所有值；Extra 欄位的 Using index，表示的是使用了覆蓋索引。

也就是說，由於在 t_modified 欄位加了 month() 函數操作，導致了全索引掃描。為了能夠用上索引的快速定位能力，我們就要把 SQL 語句改成基於欄位本身的範圍查詢。按照下麵這個寫法，優化器就能按照我們預期的，用上 t_modified 索引的快速定位能力了。

select count(*) from tradelog where
    -> (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or
    -> (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or 
    -> (t_modified >= '2018-7-1' and t_modified<'2018-8-1');

即使是對於不改變有序性的函數，也不會考慮使用索引。比如，對於 select * from tradelog where id + 1 = 10000 這個 SQL 語句，這個加 1 操作並不會改變有序性，但是 MySQL 優化器還是不能用 id 索引快速定位到 9999 這一行。所以，需要你在寫 SQL 語句的時候，手動改寫成 where id = 10000 -1 才可以。

案例二：隱式類型轉換

select * from tradelog where tradeid=110717;

交易編號 tradeid 這個欄位上，本來就有索引，但是 explain 的結果卻顯示，這條語句需要走全表掃描。

原因：tradeid 的欄位類型是 varchar(32)，而輸入的參數卻是整型，所以需要做類型轉換。

Q：數據類型轉換的規則是什麼？

A：

字元串和數字做比較的話，是將字元串轉換成數字。

看 select “10” > 9 的結果：

  1. 如果規則是“將字元串轉成數字”，那麼就是做數字比較，結果應該是 1；
  2. 如果規則是“將數字轉成字元串”，那麼就是做字元串比較，結果應該是 0。

Q：為什麼有數據類型轉換，就需要走全索引掃描？

A：

對於優化器來說，上面的SQL語句相當於：

select * from tradelog where  CAST(tradid AS signed int) = 110717;

也就是說，這條語句觸發了我們上面說到的規則：對索引欄位做函數操作，優化器會放棄走樹搜索功能。

案例三：隱式字元編碼轉換

假設系統里還有另外一個表 trade_detail，用於記錄交易的操作細節。往交易日誌表 tradelog 和交易詳情表 trade_detail 這兩個表裡插入一些數據。

CREATE TABLE `trade_detail` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `trade_step` int(11) DEFAULT NULL, /* 操作步驟 */
  `step_info` varchar(32) DEFAULT NULL, /* 步驟信息 */
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
insert into tradelog values(1, 'aaaaaaaa', 1000, now());
insert into tradelog values(2, 'aaaaaaab', 1000, now());
insert into tradelog values(3, 'aaaaaaac', 1000, now());
 
insert into trade_detail values(1, 'aaaaaaaa', 1, 'add');
insert into trade_detail values(2, 'aaaaaaaa', 2, 'update');
insert into trade_detail values(3, 'aaaaaaaa', 3, 'commit');
insert into trade_detail values(4, 'aaaaaaab', 1, 'add');
insert into trade_detail values(5, 'aaaaaaab', 2, 'update');
insert into trade_detail values(6, 'aaaaaaab', 3, 'update again');
insert into trade_detail values(7, 'aaaaaaab', 4, 'commit');
insert into trade_detail values(8, 'aaaaaaac', 1, 'add');
insert into trade_detail values(9, 'aaaaaaac', 2, 'update');
insert into trade_detail values(10, 'aaaaaaac', 3, 'update again');
insert into trade_detail values(11, 'aaaaaaac', 4, 'commit');

這時候，如果要查詢 id=2 的交易的所有操作步驟信息，SQL 語句可以這麼寫：

select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; /* 語句 Q1*/

語句 Q1 的 explain 結果如下

  1. 第一行顯示優化器會先在交易記錄表 tradelog 上查到 id=2 的行，這個步驟用上了主鍵索引，rows=1 表示只掃描一行；
  2. 第二行 key=NULL，表示沒有用上交易詳情表 trade_detail 上的 tradeid 索引，進行了全表掃描。

在這個執行計劃里，是從 tradelog 表中取 tradeid 欄位，再去 trade_detail 表裡查詢匹配欄位。

因此，我們把 tradelog 稱為驅動表，把 trade_detail 稱為被驅動表，把 tradeid 稱為關聯欄位。

語句 Q1 的執行過程如下：

  • 第 1 步，是根據 id 在 tradelog 表裡找到 L2 這一行；
  • 第 2 步，是從 L2 中取出 tradeid 欄位的值；
  • 第 3 步，是根據 tradeid 值到 trade_detail 表中查找條件匹配的行。explain 的結果裡面第二行的 key=NULL 表示的就是，這個過程是通過遍歷主鍵索引的方式，一個一個地判斷 tradeid 的值是否匹配。

第 3 步不符合我們的預期。因為表 trade_detail 里 tradeid 欄位上是有索引的，我們本來是希望通過使用 tradeid 索引能夠快速定位到等值的行。但，這裡並沒有。

原因：因為這兩個表的字元集不同，一個是 utf8，一個是 utf8mb4，所以做表連接查詢的時候用不上關聯欄位的索引。

Q：為什麼字元集不同就用不上索引呢？

A：如果單獨把第 3 步改成 SQL 語句的話，那就是：

select * from trade_detail where tradeid=$L2.tradeid.value;

其中，$L2.tradeid.value 的字元集是 utf8mb4。

字元集 utf8mb4 是 utf8 的超集，所以當這兩個類型的字元串在做比較的時候，MySQL 內部的操作是，先把 utf8 字元串轉成 utf8mb4 字元集，再做比較。

這個設定很好理解，utf8mb4 是 utf8 的超集。類似地，在程式設計語言裡面，做自動類型轉換的時候，為了避免數據在轉換過程中由於截斷導致數據錯誤，也都是“按數據長度增加的方向”進行轉換的。

因此， 在執行上面這個語句的時候，需要將被驅動數據表裡的欄位一個個地轉換成 utf8mb4，再跟 L2 做比較。

實際上這個語句等同於下麵這個寫法：

select * from trade_detail  where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;

再次觸發了上面說到的原則：對索引欄位做函數操作，優化器會放棄走樹搜索功能。

字元集不同只是條件之一，連接過程中要求在被驅動表的索引欄位上加函數操作，是直接導致對被驅動表做全表掃描的原因。

作為對比驗證，“查找 trade_detail 表裡 id=4 的操作，對應的操作者是誰”，再來看下這個語句和它的執行計劃。

select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;

這個語句里 trade_detail 表成了驅動表，但是 explain 結果的第二行顯示，這次的查詢操作用上了被驅動表 tradelog 里的索引 (tradeid)，掃描行數是 1。

Q：為什麼這次能用上被驅動表的 tradeid 索引呢？

A：

假設驅動表 trade_detail 里 id=4 的行記為 R4，那麼在連接的時候，被驅動表 tradelog 上執行的就是類似這樣的 SQL 語句：

select operator from tradelog  where traideid =$R4.tradeid.value;

這時候 $R4.tradeid.value 的字元集是 utf8, 按照字元集轉換規則，要轉成 utf8mb4，所以這個過程就被改寫成：

select operator from tradelog  where traideid =CONVERT($R4.tradeid.value USING utf8mb4);

這裡的 CONVERT 函數是加在輸入參數上的，這樣就可以用上被驅動表的 traideid 索引。

如果要優化語句

select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;

有兩種做法：

  • 比較常見的優化方法是，把 trade_detail 表上的 tradeid 欄位的字元集也改成 utf8mb4，這樣就沒有字元集轉換的問題了。
alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;
  • 但如果數據量比較大， 或者業務上暫時不能做這個 DDL 的話，那就只能採用修改 SQL 語句的方法了。
mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;

主動把 l.tradeid 轉成 utf8，就避免了被驅動表上的字元編碼轉換

小結

索引欄位不能進行函數操作，但是索引欄位的參數(=後面的值)可以用函數

補充：字元截斷

表結構如下：

CREATE TABLE `table_a` (
  `id` int(11) NOT NULL,
  `b` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `b` (`b`)
) ENGINE=InnoDB;

假設現在表裡面，有 100 萬行數據，其中有 10 萬行數據的 b 的值是’1234567890’， 假設現在執行語句是這麼寫的:

select * from table_a where b='1234567890abcd';

Q: 這時候，MySQL 會怎麼執行呢？

A: 最理想的情況是，MySQL 看到欄位 b 定義的是 varchar(10)，直接返回空呀。可惜，MySQL 並沒有這麼做。

那要不，就是把’1234567890abcd’拿到索引裡面去做匹配，肯定也沒能夠快速判斷出索引樹 b 上並沒有這個值，也很快就能返回空結果。但實際上，MySQL 也不是這麼做的。

這條 SQL 語句的執行很慢，流程是這樣的：

  1. 在傳給引擎執行的時候，做了字元截斷。因為引擎裡面這個行只定義了長度是 10，所以只截了前 10 個位元組，就是’1234567890’進去做匹配；
  2. 這樣滿足條件的數據有 10 萬行；
  3. 因為是 select *， 所以要做 10 萬次回表；
  4. 但是每次回表以後查出整行，到 server 層一判斷，b 的值都不是’1234567890abcd’;
  5. 返回結果是空。

雖然執行過程中可能經過函數操作，但是最終在拿到結果後，server 層還是要做一輪判斷的。


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

-Advertisement-
Play Games
更多相關文章
  • Centos7環境下 ventoy製作多系統自動化安裝
    前言 製作centos7無人值守安裝，在windows下也可以進行，但是由於U盤文件系統的問題，難免需要在windows和centos7下來回切換，進行配置，才能完成製作，比如主分區exfat格式用於存放ISO和ks.cfg文件通常情況下只能被windows讀取,centos7下讀取不到，掛載不上， ...
  • DxO PureRAW for mac(raw照片處理器)
    DxO PureRAW Mac版是發佈了，該軟體採用了智能技術，以解決影響所有RAW文件的七個問題：去馬賽克，降噪，波紋，變形，色差，不想要的漸暈，以及缺乏清晰度。 詳情：DxO PureRAW for mac(raw照片處理器) 功能特色 釋放Adobe Photoshop和Lightroom的R ...
  • DxO PhotoLab 5 for mac(raw圖片處理軟體)
    想給自己的照片變得高大上嗎？那就來試試這裡的DxO PhotoLab for Mac版raw圖片處理軟體吧。它可以隨時手動調整，控制照片的各個方面。dxo photolab mac版還能去除噪音有效地消除了光的限制，檢索顏色的細節，應用複雜的光學校正和增強細節。 詳情：DxO PhotoLab 5 ...
  • ubuntu安裝常用軟體
    換源(建議清華源或者阿裡源) sudo apt update sudo apt upgrade # 軟體升級 sudo apt dist-upgrade #內核更新 grub-customizer 引導控制工具 sudo add-apt-repository ppa:danielrichter200 ...
  • Linux（入門篇）
    一、電腦基礎和Linux基礎知識 1.馮諾依曼體繫結構 1946年美籍匈牙利數學家馮·諾依曼於提出存儲程式原理，把程式本身當作數據來對待，程式和該程式處理的數據用同樣的方式儲存。馮·諾依曼體系的要點是: 數字電腦的數制採用二進位，bit位, byte位元組1 byte = 8 bit 電腦應該按 ...
  • MySQL資料庫CRUD
    #資料庫的CRUD語句 ##INSERT語句 ###在指定列中插入數據 INSERT INTO 表名 (column1,column2,column3,...) VALUES (value1,value2,value3,...); INSERT INTO website (url,country) ...
  • Flink寫入Redis集群 重寫flink-connector-redis包
    使用flink的時候難免和redis打交道，相信大家都使用過flink-connector-redis來處理，但是當我想要使用RedisSink寫入集群時，發現居然不支持使用密碼，於是有了這篇筆記。 ...
  • MySQL實戰45講 19
    19 | 為什麼我只查一行的語句，也執行這麼慢？ 有些情況下，“查一行”，也會執行得特別慢。 需要說明的是，如果 MySQL 資料庫本身就有很大的壓力，導致資料庫伺服器 CPU 占用率很高或 ioutil（IO 利用率）很高，這種情況下所有語句的執行都有可能變慢，不屬於本章討論範圍。 為了便於描述， ...
一周排行
    -Advertisement-
    Play Games
  • （開源）都進來！簡單易懂、功能強大的許可權+可視化流程管理系統
    1、預覽地址：http://139.155.137.144:9012 2、qq群:801913255 一、前言 隨著網路的發展，企業對於信息系統數據的保密工作愈發重視，不同身份、角色對於數據的訪問許可權都應該大相徑庭。 列如 1、不同登錄人員對一個數據列表的可見度是不一樣的，如數據列、數據行、數據按鈕 ...
  • 設計模式-C#實現簡單工廠模式
    前言 上一篇文章寫瞭如何使用RabbitMQ做個簡單的發送郵件項目，然後評論也是比較多，也是準備去學習一下如何確保RabbitMQ的消息可靠性，但是由於時間原因，先來說說設計模式中的簡單工廠模式吧！ 在瞭解簡單工廠模式之前，我們要知道C#是一款面向對象的高級程式語言。它有3大特性，封裝、繼承、多態。 ...
  • Nodify學習 一：介紹與使用
    Nodify學習 一：介紹與使用 - 可樂_加冰 - 博客園 (cnblogs.com) Nodify學習 二：添加節點 - 可樂_加冰 - 博客園 (cnblogs.com) 介紹 Nodify是一個WPF基於節點的編輯器控制項，其中包含一系列節點、連接和連接器組件，旨在簡化構建基於節點的工具的過程 ...
  • 使用C#/.NET解析Wiki百科數據實現獲取歷史上的今天
    創建一個webapi項目做測試使用。 創建新控制器，搭建一個基礎框架，包括獲取當天日期、wiki的請求地址等 創建一個Http請求幫助類以及方法，用於獲取指定URL的信息 使用http請求訪問指定url，先運行一下，看看返回的內容。內容如圖右邊所示，實際上是一個Json數據。我們主要解析 大事記 部 ...
  • .NET科普：.NET簡史、.NET Standard以及C#和.NET Framework之間的關係
    最近在不少自媒體上看到有關.NET與C#的資訊與評價，感覺大家對.NET與C#還是不太瞭解，尤其是對2016年6月發佈的跨平臺.NET Core 1.0，更是知之甚少。在考慮一番之後，還是決定寫點東西總結一下，也回顧一下.NET的發展歷史。 首先，你沒看錯，.NET是跨平臺的，可以在Windows、 ...
  • Nodify學習 二：添加節點
    Nodify學習 一：介紹與使用 - 可樂_加冰 - 博客園 (cnblogs.com) Nodify學習 二：添加節點 - 可樂_加冰 - 博客園 (cnblogs.com) 添加節點（nodes） 通過上一篇我們已經創建好了編輯器實例現在我們為編輯器添加一個節點 添加model和viewmode ...
  • 論如何直接用EF Core實現創建更新時間、用戶審計，自動化樂觀併發、軟刪除和樹形查詢（上）
    前言 資料庫併發，數據審計和軟刪除一直是數據持久化方面的經典問題。早些時候，這些工作需要手寫複雜的SQL或者通過存儲過程和觸發器實現。手寫複雜SQL對軟體可維護性構成了相當大的挑戰，隨著SQL字數的變多，用到的嵌套和複雜語法增加，可讀性和可維護性的難度是幾何級暴漲。因此如何在實現功能的同時控制這些S ...
  • C# 使用模式匹配的好處,因為好用所以推薦~
    類型檢查和轉換：當你需要檢查對象是否為特定類型，並且希望在同一時間內將其轉換為那個類型時，模式匹配提供了一種更簡潔的方式來完成這一任務，避免了使用傳統的as和is操作符後還需要進行額外的null檢查。 複雜條件邏輯：在處理複雜的條件邏輯時，特別是涉及到多個條件和類型的情況下，使用模式匹配可以使代碼更 ...
  • 推薦一種批量判斷文件是否存在的快速辦法
    在日常開發中，我們經常需要和文件打交道，特別是桌面開發，有時候就會需要載入大批量的文件，而且可能還會存在部分文件缺失的情況，那麼如何才能快速的判斷文件是否存在呢？如果處理不當的，且文件數量比較多的時候，可能會造成卡頓等情況，進而影響程式的使用體驗。今天就以一個簡單的小例子，簡述兩種不同的判斷文件是否... ...
  • 論如何直接用EF Core實現創建更新時間、用戶審計，自動化樂觀併發、軟刪除和樹形查詢（中）
    前言 資料庫併發，數據審計和軟刪除一直是數據持久化方面的經典問題。早些時候，這些工作需要手寫複雜的SQL或者通過存儲過程和觸發器實現。手寫複雜SQL對軟體可維護性構成了相當大的挑戰，隨著SQL字數的變多，用到的嵌套和複雜語法增加，可讀性和可維護性的難度是幾何級暴漲。因此如何在實現功能的同時控制這些S ...
所有分類