方便後續解釋概念，我們在創建一個跟原先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