探究MySQL中SQL查詢的成本

大隊長11發表於2022-05-24
MySql

成本

什麼是成本，即SQL進行查詢的花費的時間成本，包含IO成本和CPU成本。

IO成本：即將資料頁從硬碟中讀取到記憶體中的讀取時間成本。通常1頁就是1.0的成本。

CPU成本：即是讀取和檢測是否滿足條件的時間成本。0.2是每行的CPU成本。

單表查詢計算成本

我們對其進行分析的具體步驟如下：

  1. 根據搜尋條件找出可能使用到的索引。
  2. 計算全表掃描的需要執行的成本。
  3. 計算各個索引執行所需要執行的成本。
  4. 對各個索引所需要執行的成本，找出最低的那個方案。

全表掃描的成本

計算IO成本：

  • 我們首先從表的status中找出Data_Length的大小，就是整個聚簇索引的大小，然後計算它一共有多少頁。

Data_Length計算頁的方法：Data_Length / (頁的大小 = 16 * 1024 = 16KB)

  • 然後我們就可以直接計算出它的IO成本即 頁數 * 1.0 + 1.1。(1.1是一個微調值)

計算CPU成本：

  • 首先從表的status中找到Rows的大小，Rows是一個不準確值。
  • 找到行的大小，所以CPU成本為**行數 * 0.2 + 0.01。(0.01是微調值)

所以我們可以將其兩個成本相加就是全表掃描的總成本。

利用索引查詢的成本

區間的索引條件

如果我們選擇的索引執行的條件是區間。

where key1 > 10 and key1 < 1000  # 在計算單個索引的成本時對於其他條件直接為true。

就會進入以下步驟

  1. 我們需要對二級索引的IO成本進行計算，當然呢，在Mysql中它對於一個範圍查詢的二級索引直接粗暴的定義其IO成本為讀取一個頁面的成本，就是1 * 1.0 = 1
  2. 我們就要找到需要回表的記錄行，首先找出最左邊的區間的記錄所在的頁和最右邊區間所在的頁。
    1. 如果兩個在同一頁，直接計算中間隔了幾個資料行。
    2. 如果兩個不在同一頁，就找出其所在頁的父頁，在判斷兩個記錄的父頁是否在同一頁，在同一頁就計算中間隔了幾個頁，然後乘以相應每頁的資料行的數量。如果不在就是遞迴處理在不在的問題了。
  3. 我們找到了間隔的記錄行n，這個時候讓CPU從二級索引找到這n條資料行所需的成本就是n*0.2 + 0.01。
  4. 緊接著我們拿著主鍵值回表，在MySQL中設計者有直接粗暴的將回表操作的IO成本直接計算為一個頁面的IO成本，不需要計算別的比如索引頁面之類的。所以我們n條記錄回表的IO成本就是**n * 1 ** 。
  5. 然後我們需要計算每次回表後的CPU成本，我們需要對回表後完整的資料行對其進行其他條件的判斷，所以CPU成本為n * 0.2。

所以IO成本為1 + n * 1，CPU成本為n*0.2 + 0.01 + n * 0.2。

單點區間

where key1 in (a,b,c,...,z)

當我們選擇的索引的條件是上述的單點區間的情況時

我們查詢n個單點區間。

  • 首先需要進行n次的IO讀取單點範圍，就相當於最小左區間和最大右區間都是一個值。就需要n * 1 的IO成本。
  • 然後就是查詢記錄，CPU成本就是總的記錄數*0.2，後面的回表流程其實是和上面一樣的。不在贅述。

最後找出成本最小的，選擇對應方法執行SQL。

index dive

我們將這樣從索引中找到最小左邊界和最大右邊界的過程計算索引的數量稱為index dive。

當然我們找到一個大區間進行一次index dive，但是in(a,b,c...d)這樣每一個引數都是一個單點區間，就要進行多次index dive。in裡面的引數多起來，特別是in (sql) 巢狀子查詢，就會使引數爆炸了，單點區間是導致超出index dive上線的主要原因。

MySQL有一個index dive的上限，預設值為200。

mysql> show variables like '%dive%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| eq_range_index_dive_limit | 200   |
+---------------------------+-------+
1 row in set, 1 warning (0.00 sec)

像上面我們利用索引計算範圍的那種計算成本的方式，僅適用於區間範圍數量小的情況下，當大於index dive的上限，就不能使用index dive了，就得使用索引的資料進行估算。

如何估算？

show index from 表名;

我們首先獲得MySQL資料字典中統計的該表的Rows即行數，這個值是不準確的，是估計值。（後面解釋）

然後通過上面語句獲得的Cardinality列對應的索引的引數，即該索引列的基數，即索引列的值不重複的列的數量。

將Rows / Cardinality 就可以得到每個索引值重複行數的平均值。

我們根據每個值重複的數量，乘以單點區間的數量，就充當每個單點區間匹配的記錄數。

連線查詢計算成本

對於驅動表的查詢後的得到記錄條數就叫做驅動表的扇出。

對於驅動表來說計算其最後記錄的條數，當能用到索引直接使用索引計算其條數，對於用不到索引的情況呢，就只能進行猜，就是對其進行評估（啟發式規則），最後得到驅動表的扇出。

然後我們要計算連線的成本，就需要確定連線的方式。

  • 左，右連線。因為左右固定，所以驅動表和被驅動表是固定的。但是有時候是可以將外連線優化成內連線的。
  • 內連線。左右不固定，都可以作為驅動表，所以需要對其兩種進行成本的計算。

所以流程如下：

  1. 確定驅動表。
  2. 計算驅動表執行的最優計劃，即上文的單表查詢計算成本。
  3. 然後將驅動表的扇出 * 被驅動表的執行的最優成本。
  4. 將2，3步驟成本相加，即連線成本。

ps：內和外連線都是一樣的，區別內連線需要確定哪個作為驅動表成本更低。

我們會知道如果兩表連線時，驅動表的每一個結果行是作為一個常數傳入被驅動表進行查詢的。所以如果在連線條件上有索引的話，就可以加快連線，否則就要進行全表掃描。

當然了被驅動表的搜尋條件能有索引那更好了。也能加快其計算出最後結果。

我在之前的總結文章中，有一個錯誤，就是我提出一個能不能將被驅動表在自身搜尋條件篩選後應該快取起來這個觀點，其實是不對的，如果沒有被驅動表自身搜尋條件進行是沒有意義的。而且因為驅動表的結果行也是作為一個引數的搜尋條件連線的，然後一條一條的進行設定引數搜尋被驅動表符合的結果行。

調整成本常數

mysql.server_cost

我們知道的從磁碟從IO到記憶體的成本常數是1.0

mysql> select * from mysql.server_cost;
+------------------------------+------------+---------------------+---------+---------------+
| cost_name                    | cost_value | last_update         | comment | default_value |
+------------------------------+------------+---------------------+---------+---------------+
| disk_temptable_create_cost   |       NULL | 2020-12-17 14:54:07 | NULL    |            20 |
| disk_temptable_row_cost      |       NULL | 2020-12-17 14:54:07 | NULL    |           0.5 |
| key_compare_cost             |       NULL | 2020-12-17 14:54:07 | NULL    |          0.05 |
| memory_temptable_create_cost |       NULL | 2020-12-17 14:54:07 | NULL    |             1 |
| memory_temptable_row_cost    |       NULL | 2020-12-17 14:54:07 | NULL    |           0.1 |
| row_evaluate_cost            |       NULL | 2020-12-17 14:54:07 | NULL    |           0.1 |
+------------------------------+------------+---------------------+---------+---------------+
6 rows in set (0.00 sec)
  • disk_temptable_create_cost 磁碟中建立臨時表的成本引數
  • disk_temptable_row_cost 磁碟中的臨時表讀入頁的成本引數
  • key_compare_cost 鍵進行比較的成本引數
  • ...其他的就不介紹了差不多
  • row_evaluate_cost 這個就是CPU檢測一條記錄的成本引數，調高會讓優化器儘可能使用索引減少檢測的記錄條數。
如果更新直接使用update語句即可
然後讓系統重新整理以下這個值   flush optimizer_costs;

mysql.engine_cost

mysql> select * from mysql.engine_cost;
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| engine_name | device_type | cost_name              | cost_value | last_update         | comment | default_value |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| default     |           0 | io_block_read_cost     |       NULL | 2020-12-17 14:54:07 | NULL    |             1 |
| default     |           0 | memory_block_read_cost |       NULL | 2020-12-17 14:54:07 | NULL    |          0.25 |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
2 rows in set (0.00 sec)
  • io_block_read_cost 從磁碟IO一個塊block同樣就是頁到記憶體的成本引數，提高就會讓優化器儘量減少IO即從磁碟讀的條數，即儘可能使用索引。就是我們上面計算的IO成本。
  • memory_block_read_cost 從記憶體讀塊即頁的成本引數。

MySQL統計資料

我們在上面所過全表掃描計算成本時我們需要拿出表的Rows即行數這個引數，這一些關於表的，索引的行數等等被叫做統計資料。

MySQL有兩種統計資料儲存方式

  • 基於磁碟的永久性統計資料
  • 基於記憶體的非永久統計資料

兩種模式，記憶體需要每次啟動MySQL進行資料統計，然後關閉統計資料就消失了。預設還是磁碟的永久儲存。

基於磁碟的統計資料

統計資料可以分為兩個，一個是表的統計資料，一個是索引的統計資料。

mysql> show tables from mysql like '%innodb%';
+----------------------------+
| Tables_in_mysql (%innodb%) |
+----------------------------+
| innodb_index_stats         |  // 索引的統計資料
| innodb_table_stats         |  // 表的統計資料
+----------------------------+
2 rows in set (0.13 sec)

innodb_table_stats表

mysql> select * from mysql.innodb_table_stats;
+---------------+-----------------------------------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name                              | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+-----------------------------------------+---------------------+--------+----------------------+--------------------------+
| mall          | cms_help                                | 2022-04-14 15:26:26 |      0 |                    1 |                        0 |
  • database_name 資料庫名
  • table_name 表名
  • last_update 上次更新的時間
  • n_rows 即錶行數
  • clustered_index_size 聚簇索引佔的頁面數
  • sum_of_other_index_sizes 其他索引佔用總的頁面數

n_rows統計方式

先取出幾個葉子頁面，然後計算這幾個葉子節點行數的平均值。

然後乘以全部葉子的頁面，就是全部的葉子節點數。這就是為什麼不準確。

clustered_index_size 統計方式

統計頁面數，分為兩個段，一個葉子段，一個非葉子段，從索引根節點找到兩個段，然後從段的結構找出佔用的頁面數，流程如下。

  • 首先統計碎片區，碎片區佔滿了就是32個頁，每個碎片區會佔用一頁，沒有佔滿32個就按碎片區的數量為頁面數。
  • 然後統計專屬段的區，就是直接計算連結串列中鏈的區數，然後區數直接*64頁。不管有沒有用滿，都直接算用滿了。這也是不準確的原因。

sum_of_other_index_sizes 統計類似

innodb_index_stats表

image

統計項有如下：

  • n_leaf_pages: 表示該索引的葉子節點佔用多少個頁面。
  • size: 表示該索引一共佔用的頁面數
  • n_diff_pfxNN: 表示對應索引列不重複的值有多少，其中的NN對於聯合索引來說就是前01就是前一個列組合有幾個不重複值，02就是前兩個列組合有幾個不重複值。

對於NULL的定義

在MySQL中，跟null的任何表示式都為null。

null值對於二級索引的不重複值來說有很大影響。對於index dive 來說就需要用到不重複值來作為評估成本的引數。

複習：當in(...)裡面的引數太多，就不會執行index dive而是直接估計，查詢不重複值然後除以總的記錄數，就可以得到每個單點區間的大概值數。

mysql> show variables like 'innodb_stats_method';
+---------------------+-------------+
| Variable_name       | Value       |
+---------------------+-------------+
| innodb_stats_method | nulls_equal |
+---------------------+-------------+
1 row in set, 1 warning (0.08 sec)

對於null值來說，預設是認為所有的null都是相等的。

nulls_unequal : 所有null都不為相等的。

nulls_ignored : 直接把null忽略掉。

相關文章