個(gè)人中心
退出
MySQL 優(yōu)化器原來(lái)是這樣工作的
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫(kù)管理與開(kāi)發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
優(yōu)化器概述
邏輯轉(zhuǎn)換
基于成本的優(yōu)化
控制優(yōu)化程度
設(shè)置成本常量
數(shù)據(jù)字典與統(tǒng)計(jì)信息
控制優(yōu)化行為
優(yōu)化器和索引提示
總結(jié)
大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。我們?cè)?MySQL 體系結(jié)構(gòu)中介紹了 MySQL 的服務(wù)器邏輯結(jié)構(gòu)，其中查詢優(yōu)化器（optimizer）負(fù)責(zé)生成 SQL 語(yǔ)句的執(zhí)行計(jì)劃，是決定查詢性能的一個(gè)關(guān)鍵組件。本文將會(huì)深入分析 MySQL 優(yōu)化器工作的原理以及如何控制優(yōu)化器來(lái)實(shí)現(xiàn) SQL 語(yǔ)句的優(yōu)化。
優(yōu)化器概述
MySQL 優(yōu)化器使用基于成本的優(yōu)化方式（Cost-based Optimization），以 SQL 語(yǔ)句作為輸入，利用內(nèi)置的成本模型和數(shù)據(jù)字典信息以及存儲(chǔ)引擎的統(tǒng)計(jì)信息決定使用哪些步驟實(shí)現(xiàn)查詢語(yǔ)句，也就是查詢計(jì)劃。
查詢優(yōu)化和地圖導(dǎo)航的概念非常相似，我們通常只需要輸入想要的結(jié)果（目的地），優(yōu)化器負(fù)責(zé)找到最有效的實(shí)現(xiàn)方式（最佳路線）。需要注意的是，導(dǎo)航并不一定總是返回最快的路線，因?yàn)橄到y(tǒng)獲得的交通數(shù)據(jù)并不可能是絕對(duì)準(zhǔn)確的；與此類似，優(yōu)化器也是基于特定模型、各種配置和統(tǒng)計(jì)信息進(jìn)行選擇，因此也不可能總是獲得最佳執(zhí)行方式。
從高層次來(lái)說(shuō)，MySQL Server 可以分為兩部分：服務(wù)器層以及存儲(chǔ)引擎層。其中，優(yōu)化器工作在服務(wù)器層，位于存儲(chǔ)引擎 API 之上。優(yōu)化器的工作過(guò)程從語(yǔ)義上可以分為四個(gè)階段：
邏輯轉(zhuǎn)換，包括否定消除、等值傳遞和常量傳遞、常量表達(dá)式求值、外連接轉(zhuǎn)換為內(nèi)連接、子查詢轉(zhuǎn)換、視圖合并等；
優(yōu)化準(zhǔn)備，例如索引 ref 和 range 訪問(wèn)方法分析、查詢條件扇出值（fan out，過(guò)濾后的記錄數(shù)）分析、常量表檢測(cè)；
基于成本優(yōu)化，包括訪問(wèn)方法和連接順序的選擇等；
執(zhí)行計(jì)劃改進(jìn)，例如表?xiàng)l件下推、訪問(wèn)方法調(diào)整、排序避免以及索引條件下推。
邏輯轉(zhuǎn)換
MySQL 優(yōu)化器首先可能會(huì)以不影響結(jié)果的方式對(duì)查詢進(jìn)行轉(zhuǎn)換，轉(zhuǎn)換的目標(biāo)是嘗試消除某些操作從而更快地執(zhí)行查詢。例如（數(shù)據(jù)來(lái)源）：
mysql> explain
-> select *
-> from employee
-> where salary > 10000 and 1=1;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | employee | NULL | ALL | NULL | NULL | NULL | NULL | 25 | 33.33 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `hrdb`.`employee`.`emp_id` AS `emp_id`,`hrdb`.`employee`.`emp_name` AS `emp_name`,`hrdb`.`employee`.`sex` AS `sex`,`hrdb`.`employee`.`dept_id` AS `dept_id`,`hrdb`.`employee`.`manager` AS `manager`,`hrdb`.`employee`.`hire_date` AS `hire_date`,`hrdb`.`employee`.`job_id` AS `job_id`,`hrdb`.`employee`.`salary` AS `salary`,`hrdb`.`employee`.`bonus` AS `bonus`,`hrdb`.`employee`.`email` AS `email` from `hrdb`.`employee` where (`hrdb`.`employee`.`salary` > 10000.00)
1 row in set (0.00 sec)
顯然，查詢條件中的 1=1 是完全多余的。沒(méi)有必要為每一行數(shù)據(jù)都執(zhí)行一次計(jì)算；刪除這個(gè)條件也不會(huì)影響最終的結(jié)果。執(zhí)行EXPLAIN語(yǔ)句之后，通過(guò)SHOW WARNINGS命令可以查看邏輯轉(zhuǎn)換之后的 SQL 語(yǔ)句，從上面的結(jié)果可以看出 1=1 已經(jīng)不存在了。
??關(guān)于 MySQL 執(zhí)行計(jì)劃和 EXPLAIN 語(yǔ)句的詳細(xì)介紹可以參考這篇文章。
我們也可以通過(guò)優(yōu)化器跟蹤進(jìn)一步了解優(yōu)化器的執(zhí)行過(guò)程，例如：
mysql> SET optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.03 sec)
mysql> select * from employee where emp_id = 1 and dept_id = emp_id;
+--------+----------+-----+---------+---------+------------+--------+----------+----------+-------------------+
| emp_id | emp_name | sex | dept_id | manager | hire_date | job_id | salary | bonus | email |
+--------+----------+-----+---------+---------+------------+--------+----------+----------+-------------------+
| 1 | 劉備 | 男 | 1 | NULL | 2000-01-01 | 1 | 30000.00 | 10000.00 | liubei@shuguo.com |
+--------+----------+-----+---------+---------+------------+--------+----------+----------+-------------------+
1 row in set (0.00 sec)
mysql> select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
QUERY: select * from employee where emp_id = 1 and dept_id = emp_id
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `employee`.`emp_id` AS `emp_id`,`employee`.`emp_name` AS `emp_name`,`employee`.`sex` AS `sex`,`employee`.`dept_id` AS `dept_id`,`employee`.`manager` AS `manager`,`employee`.`hire_date` AS `hire_date`,`employee`.`job_id` AS `job_id`,`employee`.`salary` AS `salary`,`employee`.`bonus` AS `bonus`,`employee`.`email` AS `email` from `employee` where ((`employee`.`emp_id` = 1) and (`employee`.`dept_id` = `employee`.`emp_id`))"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`employee`.`emp_id` = 1) and (`employee`.`dept_id` = `employee`.`emp_id`))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(multiple equal(1, `employee`.`emp_id`, `employee`.`dept_id`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(multiple equal(1, `employee`.`emp_id`, `employee`.`dept_id`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "multiple equal(1, `employee`.`emp_id`, `employee`.`dept_id`)"
}
]
}
},
...
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)
優(yōu)化器跟蹤輸出主要包含了三個(gè)部分：
join_preparation，準(zhǔn)備階段，返回了字段名擴(kuò)展之后的 SQL 語(yǔ)句。對(duì)于 1=1 這種多余的條件，也會(huì)在這個(gè)步驟被刪除；
join_optimization，優(yōu)化階段。其中 condition_processing 中包含了各種邏輯轉(zhuǎn)換，經(jīng)過(guò)等值傳遞（equality_propagation）之后將條件 dept_id = emp_id 轉(zhuǎn)換為了 dept_id = 1。另外 constant_propagation 表示常量傳遞，trivial_condition_removal 表示無(wú)效條件移除
join_execution，執(zhí)行階段。
優(yōu)化器跟蹤還可以顯示其他基于成本優(yōu)化的過(guò)程，后續(xù)我們還會(huì)使用該功能。關(guān)閉優(yōu)化器跟蹤功能的方式如下：
SET optimizer_trace="enabled=off";
下表列出了一些邏輯轉(zhuǎn)換的示例：
基于成本的優(yōu)化
MySQL 優(yōu)化器采用基于成本的優(yōu)化方式，簡(jiǎn)化的步驟如下：
為每個(gè)操作指定一個(gè)成本；
計(jì)算每個(gè)可能的執(zhí)行計(jì)劃各個(gè)步驟的成本總和；
選擇總成本最小的執(zhí)行計(jì)劃。
為了找到最佳執(zhí)行計(jì)劃，優(yōu)化器需要比較不同的查詢方案。隨著查詢中表的數(shù)量增加，可能的執(zhí)行計(jì)劃會(huì)呈現(xiàn)指數(shù)級(jí)增長(zhǎng)；因?yàn)槊總€(gè)表都可能使用全表掃描或者不同的索引訪問(wèn)方法，連接查詢可能使用任意順序。對(duì)于少量表的連接查詢（通常少于 7 到 10 個(gè)）可能不會(huì)產(chǎn)生問(wèn)題，但是更多的表可能會(huì)導(dǎo)致查詢優(yōu)化的時(shí)間比執(zhí)行時(shí)間還要長(zhǎng)。
所以優(yōu)化器不可能遍歷所有的執(zhí)行方案，一種更靈活的優(yōu)化方法是允許用戶控制優(yōu)化器在查找最佳查詢計(jì)劃時(shí)的遍歷程度。一般來(lái)說(shuō)，優(yōu)化器評(píng)估的計(jì)劃越少，則編譯查詢所花費(fèi)的時(shí)間就越少；但另一方面，由于優(yōu)化器忽略了一些計(jì)劃，因此可能找到的不是最佳計(jì)劃。
控制優(yōu)化程度
MySQL 提供了兩個(gè)系統(tǒng)變量，可以用于控制優(yōu)化器的優(yōu)化程度：
optimizer_prune_level， 基于返回行數(shù)的評(píng)估忽略某些執(zhí)行計(jì)劃，這種啟發(fā)式的方法可以極大地減少優(yōu)化時(shí)間而且很少丟失最佳計(jì)劃。因此，該參數(shù)的默認(rèn)設(shè)置為 1；如果確認(rèn)優(yōu)化器錯(cuò)過(guò)了最佳計(jì)劃，可以將該參數(shù)設(shè)置為 0，不過(guò)這樣可能導(dǎo)致優(yōu)化時(shí)間的增加。
optimizer_search_depth，優(yōu)化器查找的深度。如果該參數(shù)大于查詢中表的數(shù)量，可以得到更好的執(zhí)行計(jì)劃，但是優(yōu)化時(shí)間更長(zhǎng)；如果小于表的數(shù)量，可以更快完成優(yōu)化，但可能獲得的不是最優(yōu)計(jì)劃。例如，對(duì)于 12、13 個(gè)或者更多表的連接查詢，如果將該參數(shù)設(shè)置為表的個(gè)數(shù)，可能需要幾小時(shí)或者幾天時(shí)間才能完成優(yōu)化；如果將該參數(shù)修改為 3 或者 4，優(yōu)化時(shí)間可能少于 1 分鐘。該參數(shù)的默認(rèn)值為 62；如果不確定是否合適，可以將其設(shè)置為 0，讓優(yōu)化器自動(dòng)決定搜索的深度。
設(shè)置成本常量
MySQL 優(yōu)化器計(jì)算的成本主要包括 I/O 成本和 CPU 成本，每個(gè)步驟的成本由內(nèi)置的“成本常量”進(jìn)行估計(jì)。另外，這些成本常量可以通過(guò) mysql 系統(tǒng)數(shù)據(jù)庫(kù)中的 server_cost 和 engine_cost 兩個(gè)表進(jìn)行查詢和設(shè)置。
server_cost 中存儲(chǔ)的是常規(guī)服務(wù)器操作的成本估計(jì)值：
select * from mysql.server_cost;
cost_name |cost_value|last_update |comment|default_value|
----------------------------|----------|-------------------|-------|-------------|
disk_temptable_create_cost | |2018-05-17 10:12:12| | 20.0|
disk_temptable_row_cost | |2018-05-17 10:12:12| | 0.5|
key_compare_cost | |2018-05-17 10:12:12| | 0.05|
memory_temptable_create_cost| |2018-05-17 10:12:12| | 1.0|
memory_temptable_row_cost | |2018-05-17 10:12:12| | 0.1|
row_evaluate_cost | |2018-05-17 10:12:12| | 0.1|
cost_value 為空表示使用 default_value。其中，
disk_temptable_create_cost 和 disk_temptable_row_cost 代表了在基于磁盤的存儲(chǔ)引擎（InnoDB 或 MyISAM）中使用內(nèi)部臨時(shí)表的評(píng)估成本。增加這些值會(huì)使得優(yōu)化器傾向于較少使用內(nèi)部臨時(shí)表的查詢計(jì)劃。
key_compare_cost 代表了比較記錄鍵的評(píng)估成本。增加該值將導(dǎo)致需要比較多個(gè)鍵值的查詢計(jì)劃變得更加昂貴。例如，執(zhí)行 filesort 排序的查詢計(jì)劃比通過(guò)索引避免排序的查詢計(jì)劃相對(duì)更加昂貴。
memory_temptable_create_cost 和 memory_temptable_row_cost 代表了在 MEMORY 存儲(chǔ)引擎中使用內(nèi)部臨時(shí)表的評(píng)估成本。增加這些值會(huì)使得優(yōu)化器傾向于較少使用內(nèi)部臨時(shí)表的查詢計(jì)劃。
row_evaluate_cost 代表了計(jì)算記錄條件的評(píng)估成本。增加該值會(huì)導(dǎo)致檢查許多數(shù)據(jù)行的查詢計(jì)劃變得更加昂貴。例如，與讀取少量數(shù)據(jù)行的索引范圍掃描相比，全表掃描變得相對(duì)昂貴。
engine_cost 中存儲(chǔ)的是特定存儲(chǔ)引擎相關(guān)操作的成本估計(jì)值：
select * from mysql.engine_cost;
engine_name|device_type|cost_name |cost_value|last_update |comment|default_value|
-----------|-----------|----------------------|----------|-------------------|-------|-------------|
default | 0|io_block_read_cost | |2018-05-17 10:12:12| | 1.0|
default | 0|memory_block_read_cost| |2018-05-17 10:12:12| | 0.25|
engine_name 表示存儲(chǔ)引擎，“default”表示所有存儲(chǔ)引擎，也可以為不同的存儲(chǔ)引擎插入特定的數(shù)據(jù)。cost_value 為空表示使用 default_value。其中，
io_block_read_cost 代表了從磁盤讀取索引或數(shù)據(jù)塊的成本。增加該值會(huì)使讀取許多磁盤塊的查詢計(jì)劃變得更加昂貴。例如，與讀取較少塊的索引范圍掃描相比，全表掃描變得相對(duì)昂貴。
memory_block_read_cost 與 io_block_read_cost 類似，但它表示從數(shù)據(jù)庫(kù)緩沖區(qū)讀取索引或數(shù)據(jù)塊的成本。
我們來(lái)看一個(gè)例子，執(zhí)行以下語(yǔ)句：
explain format=json
select *
from employee
where dept_id between 4 and 5;
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "2.75"
},
"table": {
"table_name": "employee",
"access_type": "ALL",
"possible_keys": [
"idx_emp_dept"
],
"rows_examined_per_scan": 25,
"rows_produced_per_join": 17,
"filtered": "68.00",
"cost_info": {
"read_cost": "1.05",
"eval_cost": "1.70",
"prefix_cost": "2.75",
"data_read_per_join": "9K"
},
"used_columns": [
"emp_id",
"emp_name",
"sex",
"dept_id",
"manager",
"hire_date",
"job_id",
"salary",
"bonus",
"email"
],
"attached_condition": "(`hrdb`.`employee`.`dept_id` between 4 and 5)"
}
}
}
查詢計(jì)劃顯示使用了全表掃描（access_type = ALL），而沒(méi)有選擇 idx_emp_dept。通過(guò)優(yōu)化器跟蹤可以看到具體原因：
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_emp_dept",
"ranges": [
"4 <= dept_id <= 5"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 17,
"cost": 6.21,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
}
使用全表掃描的總成本為 2.75，使用范圍掃描的總成本為 6.21。這是因?yàn)椴樵兎祷亓?employee 表中大部分的數(shù)據(jù)，通過(guò)索引范圍掃描，然后再回表反而會(huì)比直接掃描表更慢。
接下來(lái)我們將數(shù)據(jù)行比較的成本常量 row_evaluate_cost 從 0.1 改為 1，并且刷新內(nèi)存中的值：
update mysql.server_cost
set cost_value=1
where cost_name='row_evaluate_cost';
flush optimizer_costs;
然后重新連接數(shù)據(jù)庫(kù)，再次獲取執(zhí)行計(jì)劃的結(jié)果如下：
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "38.51"
},
"table": {
"table_name": "employee",
"access_type": "range",
"possible_keys": [
"idx_emp_dept"
],
"key": "idx_emp_dept",
"used_key_parts": [
"dept_id"
],
"key_length": "4",
"rows_examined_per_scan": 17,
"rows_produced_per_join": 17,
"filtered": "100.00",
"index_condition": "(`hrdb`.`employee`.`dept_id` between 4 and 5)",
"cost_info": {
"read_cost": "21.51",
"eval_cost": "17.00",
"prefix_cost": "38.51",
"data_read_per_join": "9K"
},
"used_columns": [
"emp_id",
"emp_name",
"sex",
"dept_id",
"manager",
"hire_date",
"job_id",
"salary",
"bonus",
"email"
]
}
}
}
此時(shí)，優(yōu)化器選擇的范圍掃描（access_type = range）。雖然它的成本增加為 38.51，但是使用全表掃描的代價(jià)更高。
最后，記得將 row_evaluate_cost 的還原成默認(rèn)設(shè)置并重新連接數(shù)據(jù)庫(kù)：
update mysql.server_cost
set cost_value= null
where cost_name='row_evaluate_cost';
flush optimizer_costs;
??不要輕易修改成本常量，因?yàn)檫@樣可能導(dǎo)致許多查詢計(jì)劃變得更糟！在大多數(shù)生產(chǎn)情況下，推薦通過(guò)添加優(yōu)化器提示（optimizer hint）控制查詢計(jì)劃的選擇。
數(shù)據(jù)字典與統(tǒng)計(jì)信息
除了成本常量之外，MySQL 優(yōu)化器在優(yōu)化的過(guò)程中還會(huì)使用數(shù)據(jù)字典和存儲(chǔ)引擎中的統(tǒng)計(jì)信息。例如表的數(shù)據(jù)量、索引、索引的唯一性以及字段是否可空都會(huì)影響到執(zhí)行計(jì)劃的選擇，包括數(shù)據(jù)的訪問(wèn)方法和表的連接順序等。
MySQL 會(huì)在日常操作過(guò)程中粗略統(tǒng)計(jì)表的大小和索引的基數(shù)（Cardinality），我們也可以使用 ANALYZE TABLE 語(yǔ)句手動(dòng)更新表的統(tǒng)計(jì)信息和索引的數(shù)據(jù)分布。
ANALYZE TABLE tbl_name [, tbl_name] ...;
這些統(tǒng)計(jì)信息默認(rèn)會(huì)持久化到數(shù)據(jù)字典表 mysql.innodb_index_stats 和 mysql.innodb_table_stats 中，也可以通過(guò) INFORMATION_SCHEMA 視圖 TABLES、STATISTICS 以及 INNODB_INDEXES 進(jìn)行查看。
另外，從 MySQL 8.0 開(kāi)始增加了直方圖統(tǒng)計(jì)（histogram statistics），也就是字段值的分布情況。用戶同樣可以通過(guò)ANALYZE TABLE語(yǔ)句生成或者刪除字段的直方圖：
ANALYZE TABLE tbl_name
UPDATE HISTOGRAM ON col_name [, col_name] ...
[WITH N BUCKETS];
ANALYZE TABLE tbl_name
DROP HISTOGRAM ON col_name [, col_name] ...;
其中，WITH N BUCKETS 用于指定直方圖統(tǒng)計(jì)時(shí)桶的個(gè)數(shù)，取值范圍從 1 到 1024，默認(rèn)為 100。
直方圖統(tǒng)計(jì)主要用于沒(méi)有創(chuàng)建索引的字段，當(dāng)查詢使用這些字段與常量進(jìn)行比較時(shí)，MySQL 優(yōu)化器會(huì)使用直方圖統(tǒng)計(jì)評(píng)估過(guò)濾之后的行數(shù)。例如，以下語(yǔ)句顯示了沒(méi)有直方圖統(tǒng)計(jì)時(shí)的優(yōu)化器評(píng)估：
explain analyze
select *
from employee
where salary = 10000;
-> Filter: (employee.salary = 10000.00) (cost=2.75 rows=3) (actual time=0.612..0.655 rows=1 loops=1)
-> Table scan on employee (cost=2.75 rows=25) (actual time=0.455..0.529 rows=25 loops=1)
由于 salary 字段上既沒(méi)有索引也沒(méi)有直方圖統(tǒng)計(jì)，因此優(yōu)化器評(píng)估返回的行數(shù)為 3，但實(shí)際返回的行數(shù)為 1。
我們?yōu)?salary 字段創(chuàng)建直方圖統(tǒng)計(jì)：
analyze table employee update histogram on salary;
Table |Op |Msg_type|Msg_text |
-------------|---------|--------|-------------------------------------------------|
hrdb.employee|histogram|status |Histogram statistics created for column 'salary'.|
然后再次查看執(zhí)行計(jì)劃：
explain analyze
select *
from employee
where salary = 10000;
-> Filter: (employee.salary = 10000.00) (cost=2.75 rows=1) (actual time=0.265..0.291 rows=1 loops=1)
-> Table scan on employee (cost=2.75 rows=25) (actual time=0.206..0.258 rows=25 loops=1)
此時(shí)，優(yōu)化器評(píng)估的行數(shù)和實(shí)際返回的行數(shù)一致，都是 1。
MySQL 使用數(shù)據(jù)字典表 column_statistics 存儲(chǔ)字段值分布的直方圖統(tǒng)計(jì)，用戶可以通過(guò)查詢視圖 INFORMATION_SCHEMA.COLUMN_STATISTICS 獲得直方圖信息：
select * from information_schema.column_statistics;
SCHEMA_NAME|TABLE_NAME|COLUMN_NAME|HISTOGRAM |
-----------|----------|-----------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
hrdb |employee |salary |{"buckets": [[4000.00, 0.08], [4100.00, 0.12], [4200.00, 0.16], [4300.00, 0.2], [4700.00, 0.24000000000000002], [4800.00, 0.28], [5800.00, 0.32], [6000.00, 0.4], [6500.00, 0.48000000000000004], [6600.00, 0.52], [6800.00, 0.56], [7000.00, 0.600000000000000|
刪除以上直方圖統(tǒng)計(jì)的命令如下：
analyze table employee drop histogram on salary;
索引和直方圖之間的區(qū)別在于：
索引需要隨著數(shù)據(jù)的修改而更新；
直方圖通過(guò)命令手動(dòng)更新，不會(huì)影響數(shù)據(jù)更新的性能。但是，直方圖統(tǒng)計(jì)會(huì)隨著數(shù)據(jù)修改變得過(guò)時(shí)。
相對(duì)于直方圖統(tǒng)計(jì)，優(yōu)化器會(huì)優(yōu)先選擇索引范圍優(yōu)化評(píng)估返回的數(shù)據(jù)行。因?yàn)閷?duì)于索引字段而言，范圍優(yōu)化可以獲得更加準(zhǔn)確的評(píng)估。
控制優(yōu)化行為
MySQL 提供了一個(gè)系統(tǒng)變量 optimizer_switch，用于控制優(yōu)化器的優(yōu)化行為。
select @@optimizer_switch;
@@optimizer_switch |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,
index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,
semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,
condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on|
它的值由一組標(biāo)識(shí)組成，每個(gè)標(biāo)識(shí)的值都可以為 on 或 off，表示啟用或者禁用了相應(yīng)的優(yōu)化行為。
該變量支持全局和會(huì)話級(jí)別的設(shè)置，可以在運(yùn)行時(shí)進(jìn)行更改。
SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';
其中，command 可以是以下形式：
default，將所有優(yōu)化行為設(shè)置為默認(rèn)值。
opt_name=default，將指定優(yōu)化行為設(shè)置為默認(rèn)值。
opt_name=off，禁用指定的優(yōu)化行為。
opt_name=on，啟用指定的優(yōu)化行為。
我們以索引條件下推（index_condition_pushdown）優(yōu)化為例，演示修改 optimizer_switch 的效果。首先執(zhí)行以下語(yǔ)句查看執(zhí)行計(jì)劃：
explain
select *
from employee e
where e.email like 'zhang%';
id|select_type|table|partitions|type |possible_keys|key |key_len|ref|rows|filtered|Extra |
--|-----------|-----|----------|-----|-------------|------------|-------|---|----|--------|---------------------|
1|SIMPLE |e | |range|uk_emp_email |uk_emp_email|302 | | 2| 100.0|Using index condition|
其中，Extra 字段中的“Using index condition”表示使用了索引條件下推。
然后禁用索引條件下推優(yōu)化：
set @@optimizer_switch='index_condition_pushdown=off';
然后再次查看執(zhí)行計(jì)劃：
id|select_type|table|partitions|type |possible_keys|key |key_len|ref|rows|filtered|Extra |
--|-----------|-----|----------|-----|-------------|------------|-------|---|----|--------|-----------|
1|SIMPLE |e | |range|uk_emp_email |uk_emp_email|302 | | 2| 100.0|Using where|
Extra 字段變成了“Using where”，意味著需要訪問(wèn)表中的數(shù)據(jù)然后再應(yīng)用該條件過(guò)濾。如果使用優(yōu)化器跟蹤，可以看到更詳細(xì)的差異。
優(yōu)化器和索引提示
雖然通過(guò)系統(tǒng)變量 optimizer_switch 可以控制優(yōu)化器的優(yōu)化策略，但是一旦改變它的值，后續(xù)的查詢都會(huì)受到影響，除非再次進(jìn)行設(shè)置。
另一種控制優(yōu)化器策略的方法就是優(yōu)化器提示（Optimizer Hint）和索引提示（Index Hint），它們只對(duì)單個(gè)語(yǔ)句有效，而且優(yōu)先級(jí)比 optimizer_switch 更高。
優(yōu)化器提示使用 /*+ … */ 注釋風(fēng)格的語(yǔ)法，可以對(duì)連接順序、表訪問(wèn)方式、索引使用方式、子查詢、語(yǔ)句執(zhí)行時(shí)間限制、系統(tǒng)變量以及資源組等進(jìn)行語(yǔ)句級(jí)別的設(shè)置。
例如，在沒(méi)有使用優(yōu)化器提示的情況下：
explain
select *
from employee e
join department d on d.dept_id = e.dept_id
where e.salary = 10000;
id|select_type|table|partitions|type |possible_keys|key |key_len|ref |rows|filtered|Extra |
--|-----------|-----|----------|------|-------------|-------|-------|--------------|----|--------|-----------|
1|SIMPLE |e | |ALL |idx_emp_dept | | | | 25| 4.0|Using where|
1|SIMPLE |d | |eq_ref|PRIMARY |PRIMARY|4 |hrdb.e.dept_id| 1| 100.0| |
優(yōu)化器選擇 employee 作為驅(qū)動(dòng)表，并且使用全表掃描返回 salary = 10000 的數(shù)據(jù)；然后通過(guò)主鍵查找 department 中的記錄。
然后我們通過(guò)優(yōu)化器提示 join_order 修改兩個(gè)表的連接順序：
explain
select /*+ join_order(d, e) */ *
from employee e
join department d on d.dept_id = e.dept_id
where e.salary = 10000;
id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra |
--|-----------|-----|----------|----|-------------|---|-------|---|----|--------|------------------------------------------|
1|SIMPLE |d | |ALL |PRIMARY | | | | 6| 100.0| |
1|SIMPLE |e | |ALL |idx_emp_dept | | | | 25| 4.0|Using where; Using join buffer (hash join)|
此時(shí)，優(yōu)化器選擇了 department 作為驅(qū)動(dòng)表；同時(shí)訪問(wèn) employee 時(shí)選擇了全表掃描。我們可以再增加一個(gè)索引相關(guān)的優(yōu)化器提示 index：
explain
select /*+ join_order(d, e) index(e idx_emp_dept) */ *
from employee e
join department d on d.dept_id = e.dept_id
where e.salary = 10000;
id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra |
--|-----------|-----|----------|----|-------------|------------|-------|--------------|----|--------|-----------|
1|SIMPLE |d | |ALL |PRIMARY | | | | 6| 100.0| |
1|SIMPLE |e | |ref |idx_emp_dept |idx_emp_dept|4 |hrdb.d.dept_id| 5| 10.0|Using where|
最終，優(yōu)化器選擇了通過(guò)索引 idx_emp_dept 查找 employee 中的數(shù)據(jù)。
需要注意的是，通過(guò)提示禁用某個(gè)優(yōu)化行為可以阻止優(yōu)化器使用該優(yōu)化；但是啟用某個(gè)優(yōu)化行為不代表優(yōu)化器一定會(huì)使用該優(yōu)化，它可以選擇使用或者不使用。
??開(kāi)發(fā)和測(cè)試過(guò)程可以使用優(yōu)化器提示和索引提示，但是生產(chǎn)環(huán)境中需要小心使用。因?yàn)閷?shí)際數(shù)據(jù)和環(huán)境會(huì)隨著時(shí)間發(fā)生變化，而且 MySQL 優(yōu)化器也會(huì)越來(lái)越智能，合理的參數(shù)配置定時(shí)的統(tǒng)計(jì)更新通常是更好地選擇。
索引提示為優(yōu)化器提供了如何選擇索引的信息，直接出現(xiàn)在表名之后：
tbl_name [[AS] alias]
USE {INDEX|KEY} [FOR {JOIN|ORDER BY|GROUP BY}] (index_name, ...)
| {IGNORE|FORCE} {INDEX|KEY} [FOR {JOIN|ORDER BY|GROUP BY}] (index_name, ...)
USE INDEX 提示優(yōu)化器使用某個(gè)索引，IGNORE INDEX 提示優(yōu)化器忽略某個(gè)索引，F(xiàn)ORCE INDEX 強(qiáng)制使用某個(gè)索引。
例如，以下語(yǔ)句使用了 USE INDEX 索引提示：
explain
select *
from employee e use index (idx_emp_job)
join department d on d.dept_id = e.dept_id
where e.salary = 10000;
id|select_type|table|partitions|type |possible_keys|key |key_len|ref |rows|filtered|Extra |
--|-----------|-----|----------|------|-------------|-------|-------|--------------|----|--------|-----------|
1|SIMPLE |e | |ALL | | | | | 25| 10.0|Using where|
1|SIMPLE |d | |eq_ref|PRIMARY |PRIMARY|4 |hrdb.e.dept_id| 1| 100.0| |
雖然我們使用了索引提示，但是由于索引 idx_emp_job 和查詢完全無(wú)關(guān)，優(yōu)化器最終還是沒(méi)有選擇使用該索引。
以下示例使用了 IGNORE INDEX 索引提示：
explain
select *
from employee e
join department d ignore index (PRIMARY)
on d.dept_id = e.dept_id
where e.salary = 10000;
id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra |
--|-----------|-----|----------|----|-------------|---|-------|---|----|--------|------------------------------------------|
1|SIMPLE |e | |ALL |idx_emp_dept | | | | 25| 10.0|Using where |
1|SIMPLE |d | |ALL | | | | | 6| 16.67|Using where; Using join buffer (hash join)|
IGNORE INDEX 使得優(yōu)化器放棄了 department 的主鍵查找，最終選擇了 hash join 連接兩個(gè)表。該示例也可以通過(guò)優(yōu)化器提示 no_index 實(shí)現(xiàn)：
explain
select /*+ no_index(d PRIMARY) */ *
from employee e
join department d
on d.dept_id = e.dept_id
where e.salary = 10000;
??從 MySQL 8.0.20 開(kāi)始，提供了等價(jià)形式的索引級(jí)別優(yōu)化器提示，將來(lái)可能會(huì)廢棄傳統(tǒng)形式的索引提示。
總結(jié)
MySQL 優(yōu)化器使用基于成本的優(yōu)化方式，利用數(shù)據(jù)字典和統(tǒng)計(jì)信息選擇 SQL 語(yǔ)句的最佳執(zhí)行方式。同時(shí)，MySQL 為我們提供了控制優(yōu)化器的各種選項(xiàng)，包括控制優(yōu)化程度、設(shè)置成本常量、統(tǒng)計(jì)信息收集、啟用/禁用優(yōu)化行為以及使用優(yōu)化器提示等。
如果覺(jué)得文章對(duì)你有用，歡迎訂閱我的專欄《MySQL性能優(yōu)化》！
分類導(dǎo)航