iT邦幫忙

2021 iThome 鐵人賽

DAY 19
0
自我挑戰組

那些Mysql我不知道的事系列 第 19

Mysql執行成本-Part1(什麼是成本、單表查詢的成本)

Mysql執行成本是什麼呢?
主要就兩個

  1. I/O成本:我們都已經知道儲存引擎將資料存在磁碟中,而運算時我們需要把資料取放到磁碟中做運算,這資料從磁碟取出到放到磁碟所耗費的時間就是I/O成本。
  2. CPU成本:判斷紀錄是否滿足搜尋條件及對結果進行排序等...所耗費的時間都是CPU成本。

成本常數
什麼是成本常數呢?
對於Mysql來說頁是磁碟與記憶體的基本互動單位,設計Mysql的工程師規定讀取一個頁面的預設成本是1.0,讀取及判斷紀錄條件是否滿足的成本是0.2。這1.0及0.2稱為成本常數。

一樣用之前的例子來說明

mysql> create table single_table(
    -> id int not null auto_increment,
    -> key1 varchar(10),
    -> key2 int,
    -> key3 varchar(100),
    -> key_part1 varchar(100),
    -> key_part2 varchar(100),
    -> key_part3 varchar(100),
    -> common_field varchar(100),
    -> primary key (id),
    -> key idx_key1 (key1),
    -> unique key uk_key2 (key2),
    -> key idx_key3 (key3),
    -> key idx_key_part(key_part1, key_part2, key_part3)
    -> ) engine=InnoDB charset=utf8;
Query OK, 0 rows affected, 1 warning (0.05 sec)

為id列建立的聚簇索引
為key1列建立的idx_key1二級索引
為key2列建立的uk_key2二級索引,且是唯一
為key3列建立的idx_key3二級索引
為key_part1、key_part2、key_part3列建立的idx_key_part二級索引,這也是個聯合索引

在真正執行一筆語法前,Mysql會找出所有可以執行這個語法的方案,並比對找出耗費時間最少的成本,這也就是最終的執行計畫,決定計畫後才會真正呼叫儲存引擎提供的介面來執行語法。

過程一步步來看會像是下面這樣:

  1. 根據搜索條件找出所有可能的索引
  2. 計算全表掃描的代價
  3. 計算是用不同索引掃描的代價
  4. 找出成本最低的方案
    直接看例子比較清楚
select * from single_table where
    key1 in ('a','b','c') and
    key2 > 10 and key2 < 1000 and
    key3 > key2 and
    key_part1 like '%hello%' and
    common_field = '123';

key1 in ('a','b','c') => idx_key1二級索引
key2 > 10 and key2 < 1000 => uk_key2唯一二級索引
key3 > key2 => 由於沒有與常數比較,所以無法形成合適的掃描區間
key_part1 like '%hello%' => 由於是like%開頭的字串,一樣無法形成合適的掃描區間
common_field = '123' => 無索引
所以
第一步:根據搜索條件找出所有可能的索引
這語法可能會使用的索引就是idx_key1二級索引或uk_key2唯一二級索引

第二步:計算全表掃描的代價
全表掃描的意思就是把聚簇索引(幫大家複習一下聚簇索引的條件就是,第一照主鍵值去排序頁和紀錄,第二葉子節點存放的是完整的使用者紀錄)中的資料都依照搜尋條件一一比較,我們已經知道查詢成本是I/O成本+CPU成本。
所以要先知道兩個數字:

  1. 表中有多少筆紀錄?
  2. 聚簇索引佔了幾頁?

這些資料可以透過統計資訊來得到,如下:

mysql> show table status like 'single_table'\G
*************************** 1. row ***************************
           Name: single_table
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 10146
 Avg_row_length: 156
    Data_length: 1589248
Max_data_length: 0
   Index_length: 2359296
      Data_free: 4194304
 Auto_increment: 10001
    Create_time: 2021-09-10 10:18:09
    Update_time: 2021-09-10 13:56:04
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.22 sec)

第一個數字可以看到Rows:10146,對於MyISAM來說數字是準確的,但對Innodb來說只是個估計值。
第二個數字可以看到Data_length:1589248,它表示表佔用的儲存空間位元數。
對於MyISAM來說,該值就是資料檔案的大小,對於Innodb來說該值就是聚簇索引佔用的儲存空間大小,也就是說可以由以下公式來計算:
Data_length = 聚簇索引的頁面數量 x 每個頁面的大小
1589248 = 聚簇索引的頁面數量 x 16(預設16kb)
聚簇索引的頁面數量 = 1589248 / 16 / 1024 = 97頁

數子都有了我們就可以來計算成本:
I/O成本:97(頁數)x1.0(載入一頁的成本常數)+1.1(MySQL工程師多加入的微調值,不必特別在意) = 98.1
CPU成本:10146(紀錄數)x0.2(存取一筆紀錄的成本常數)+1.0(MySQL工程師多加入的微調值,不必特別在意) = 2030.2
總成本:98.1+2030.2=2128.3

第三步:計算使用不同索引執行查詢的代價
在第一步我們知道了可能會使用的索引是idx_key1二級索引或uk_key2唯一二級索引。
Mysql查詢最佳化工具會優先分析唯一二級索引的成本,再來才是普通二級索引的成本,所以我們也照這個順序來分析。

先使用uk_key2唯一二級索引來計算成本:
對於使用二級索引+回表方式執行的查詢,我們要注意的數字是兩個:

第一個:掃描區間數量
uk_key2查詢的成本如下:
I/O成本:1+95x1.0=96對應的搜索條件是key2 > 10 and key2 < 1000,掃描區間也就是(10,1000)。
而MySQL工程師設計查詢最佳化工具,不論索引佔用了多少頁,其會粗暴的認為讀取索引的掃描區間I/O成本跟讀取一個頁面的I/O成本是一樣的。而在本例中掃描區間只有一個(10,1000),所以I/O成本就是1。

第二個:需要回表的紀錄數
查詢最佳化工具需要知道二級索引在掃描區間內有幾筆紀錄,以此例就是uk_key2在(10,1000)有幾筆紀錄。
計算過程如下:
步驟1:先找到key2 > 10的第一筆紀錄(稱為區間最左邊紀錄),在B+樹內定位一筆紀錄是很快的,是常數等級的,所以此過程性能消耗可忽略不計。
步驟2:再找到key2 < 1000的第一筆紀錄(稱為區間最右邊紀錄),在B+樹內定位一筆紀錄是很快的,是常數等級的,所以此過程性能消耗可忽略不計。
步驟3:如果最左邊與最右邊紀錄相隔不太遠(在MySQL5.7.22中不要超過10個頁面即可),就可以精確地計算出key2 > 10 and key2 < 1000的紀錄數。
大家還記得資料頁的Page Header部分嗎?
裡面有一個參數是PAGE_N_RECS該頁中使用者紀錄的數量,所以如果最左邊與最右邊紀錄相隔不太遠,我們可以直接歷遍這些頁面,加總PAGE_N_RECS的數字即可。
那問題來了我們要怎麼知道最左邊與最右邊紀錄有幾個頁面?
大家還記得前面提到的目錄項紀錄對應頁面吧?
假設最左邊紀錄在頁b,最右邊紀錄在頁c,我們想知道頁b與頁c之間到底有幾個頁面?
因為每一筆目錄項紀錄都是對應一個頁面,所以我們只要往上一層看到他們的父節點(頁a)中對應的目錄項紀錄隔了幾筆,就等於是知道有幾頁了呀!而在一個頁面(頁a)統計兩筆紀錄(頁b與頁c在頁a中對應的目錄項紀錄)之間有幾筆紀錄的成本就很低了。

我們已經知道如何查詢的細節,假設最終得到的紀錄數是95
(1)讀取這95筆紀錄的cpu成本就是95(紀錄數)x0.2(讀取1筆紀錄的成本常數)+0.01(微調值) = 19.01
(2)再來要去回表操作,MySQL工程師評估回表操作的I/O成本依然很粗暴,認為每次回表操作相當於存取一個頁面,所以95筆紀錄就是95x1(存取一個頁面的I/O成本常數)
(3)前面回表得到完整的使用者紀錄後,最後就是要檢測其他搜索條件是否符合。
也就是95x0.2(檢測是否符合其他條件的成本常數)=19

所以使用uk_key2查詢的成本如下:
I/O成本:1(掃描區間數量)+95x1.0(二級索引紀錄筆數)=96
CPU成本:95(紀錄數)x0.2(讀取1筆紀錄的成本常數)+0.01(微調值)+95x0.2(檢測是否符合其他條件的成本常數)=
38.01
總成本就是:96+38.01=134.01

再來使用idx_key1索引來計算成本:
key1 in ('a','b','c')對應的掃描區間是['a','a'],['b','b'],['c','c']
一樣先了解
第一個:掃描區間數量
很明顯有三個單點掃描區間,所以付出的I/O成本就是3x1.0=3.0

第二個:需要回表的紀錄數
計算方法跟前面一樣就不贅述
['a','a']是35
['b','b']是44
['c','c']是39
所以需要回表的總紀錄數就是35+44+39=118
讀取這些紀錄的CPU成本就是118x0.2+0.01=23.61
再來回表操作所需的I/O成本是118x1.0=118.0
判斷其他條件是否成立118x0.2=23.6

所以使用idx_key1查詢的成本如下:
I/O成本:3(掃描區間數量)+118x1.0(二級索引紀錄筆數)=121.0
CPU成本:118(紀錄數)x0.2(讀取1筆紀錄的成本常數)+0.01(微調值)+118x0.2(檢測是否符合其他條件的成本常數)=47.21
總成本就是:121+47.21=168.21

判斷是否使用索引合併:
本例中key1和key2是用and連接起來,而對於idx_key1和uk_key2都是範圍查詢,也就是不會按照主鍵值排序,不符合索引合併的條件,因此不會使用。

比較方案
全資料:2128.3
使用uk_key2查詢。:134.01
使用idx_key1查詢:168.21
因此最終會使用uk_key2查詢。


上一篇
連接的原理(基本概念、內連接與外連接)
下一篇
Mysql執行成本-Part2(連接查詢的成本、調節成本常數)
系列文
那些Mysql我不知道的事30

尚未有邦友留言

立即登入留言