iT邦幫忙

2021 iThome 鐵人賽

DAY 20
0
自我挑戰組

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

Mysql執行成本-Part2(連接查詢的成本、調節成本常數)

方便後續解釋概念,我們在創建一個跟原先single_table一樣的表,叫single_table2(s2)。

mysql> create table single_table2(                                                           -> 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.44 sec)

連接查詢的成本

條件過濾

首先要知道什麼是條件過濾?
我們已經知道MySQL中連接查詢是巢狀結構迴圈,驅動表會被存取一次,被驅動表會被存取多次。
因此成本就是

  • 單次查詢驅動表的成本
  • 多次查詢被驅動表的成本

我們把查詢驅動表後的紀錄筆數稱為驅動表的扇出(fanout)。
有時候扇出值很好計算

select * from s1 inner join s2;

很明顯這是全表資料掃描,驅動表有多少紀錄,扇出值就是多少,以我們前文的數字就是10146

有時候沒那麼容易知道

select * from s1 inner join s2
    where s1.commom_field > 'xyz';

多了一個條件s1.commom_field > 'xyz',但由於最佳化器並不會真的去執行查詢,所以我們只能猜測有多少筆紀錄符合這條件,而這個猜測的過程也就是條件過濾。

兩表連接的成本分析

連接查詢總成本=單次存取驅動的成本+驅動表扇出值x單次存取被驅動表的成本。

對於左外連接和右外連接,驅動表與被驅動表是固定的,所以會單純直接計算成本最低的方式即可。

但對於內連接來說,驅動表與被驅動表的位置是可以互換的,因此需要考慮當不同的表做驅動表時,成本最低的連接順序是那一個。

再來實際運行的時候,比重較大的是驅動表扇出值x單次存取被驅動表的成本,所以我們要盡可能的降低這兩個值,
像是在被驅動表中建立索引,如果可以,被驅動表的連接列最好是該表的主鍵或是唯一二級索引,這樣就更快了。

多表連接的成本分析

對於n表連接會有nx(n-1)x(n-2)x...x1種的順序,也就是n階的連接順序。
而這麼多種變化一一計算成本想來就是個費時的工程,因此有一些優化的方式。

  • 提前結束某種連接順序的成本評估:MySQL會維護一個全域變數存放當前最小的連接查詢成本,當成本計算的過程中超過這個數值,即不會在繼續計算成本。
  • 系統變數optimizer_search_depth:為了防止無窮無盡的分析各種連接順序的成本,工程師設計了一個這樣的變數,只有連接表的數量小於等於該值才會繼續分析。
  • 某些規格直接不考慮某些連接順序:工程師直接根據過去的經驗訂定了一些啟發式規則,凡是不滿足規則的直接不分析,提供了一個系統變數optimizer_prune_level來控制是否使用這些規則。

調節成本常數

前文介紹了兩個成本常數:

  1. 讀取一個頁面花費的成本預設是1
  2. 讀取及檢查一筆紀錄是否符合條件的成本預設是0.2
    其實除了這兩個成本常數以外,還支持很多其他常數,存在mysql庫的兩個表中:
mysql> show tables from mysql like '%cost%';
+--------------------------+
| Tables_in_mysql (%cost%) |
+--------------------------+
| engine_cost              |
| server_cost              |
+--------------------------+
2 rows in set (0.46 sec)

前文提到再執行一筆敘述時,分為兩個部分server層跟儲存引擎層,server層負責連接管理、查詢快取、語法解析、查詢最佳化等,而儲存引擎層才是具體執行操作的地方,server層的成本常數都存在server_cost,而儲存引擎層則存在engine_cost。

接下來我們分別來看看這兩個表有那些成本常數
servercost

mysql> select * from mysql.server_cost;
+------------------------------+------------+---------------------+---------+---------------+
| cost_name                    | cost_value | last_update         | comment | default_value |
+------------------------------+------------+---------------------+---------+---------------+
| disk_temptable_create_cost   |       NULL | 2018-07-17 11:08:22 | NULL    |            20 |
| disk_temptable_row_cost      |       NULL | 2018-07-17 11:08:22 | NULL    |           0.5 |
| key_compare_cost             |       NULL | 2018-07-17 11:08:22 | NULL    |          0.05 |
| memory_temptable_create_cost |       NULL | 2018-07-17 11:08:22 | NULL    |             1 |
| memory_temptable_row_cost    |       NULL | 2018-07-17 11:08:22 | NULL    |           0.1 |
| row_evaluate_cost            |       NULL | 2018-07-17 11:08:22 | NULL    |           0.1 |
+------------------------------+------------+---------------------+---------+---------------+
6 rows in set (0.26 sec)

disk_temptable_create_cost:基於磁碟的臨時表成本
disk_temptable_row_cost:向基於磁碟的臨時表存取一筆紀錄的成本
key_compare_cost:兩筆紀錄比較操作的成本,多用在排序操作上
memory_temptable_create_cost:基於記憶體的臨時表成本
memory_temptable_row_cost:向基於記憶體的臨時表存取一筆紀錄的成本
row_evaluate_cost:讀取並檢測一筆紀錄是否符合搜索條件的成本

在執行語法像是distinct子句、group by子句、union子句,mysql都有可能在內部創建一個臨時表來輔助完成查詢。資料量大的時候可能創建基於磁碟的臨時表(使用myisam,innodb等引擎),而資料不多時可能創建基於記憶體的臨時表(使用memory引擎)。

這些常數預設值都是null,想要更新數值的話如下:

mysql> update mysql.server_cost set cost_value=0.4 where cost_name='row_evaluate_cost';
Query OK, 1 row affected (0.54 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush optimizer_costs;
Query OK, 0 rows affected (0.57 sec)

mysql> select * from mysql.server_cost;
+------------------------------+------------+---------------------+---------+---------------+
| cost_name                    | cost_value | last_update         | comment | default_value |
+------------------------------+------------+---------------------+---------+---------------+
| disk_temptable_create_cost   |       NULL | 2018-07-17 11:08:22 | NULL    |            20 |
| disk_temptable_row_cost      |       NULL | 2018-07-17 11:08:22 | NULL    |           0.5 |
| key_compare_cost             |       NULL | 2018-07-17 11:08:22 | NULL    |          0.05 |
| memory_temptable_create_cost |       NULL | 2018-07-17 11:08:22 | NULL    |             1 |
| memory_temptable_row_cost    |       NULL | 2018-07-17 11:08:22 | NULL    |           0.1 |
| row_evaluate_cost            |        0.4 | 2021-09-27 08:42:34 | NULL    |           0.1 |
+------------------------------+------------+---------------------+---------+---------------+
6 rows in set (0.00 sec)

enginecost

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 | 2018-07-17 11:08:22 | NULL    |             1 |
| default     |           0 | memory_block_read_cost |       NULL | 2018-07-17 11:08:22 | NULL    |          0.25 |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
2 rows in set (0.03 sec)

這邊特別說明一個列engine_name,表示成本常數適用的儲存引擎名稱,default表示都通用。

io_block_read_cost:從磁碟上讀取一個區塊對應的成本(對innodb來說,一頁就是一個區塊,不過對myisam來說預設為4096位元組作為一個區塊)
memory_block_read_cost:跟前面類似,只是變成從記憶體讀取。

一樣可以透過跟前述一樣的方式更新成本常數,也可以透過新增的方式來插入只符合特定引擎的成本常數。

耶~take a break


上一篇
Mysql執行成本-Part1(什麼是成本、單表查詢的成本)
下一篇
InnoDB統計資料是如何收集的
系列文
那些Mysql我不知道的事30

尚未有邦友留言

立即登入留言