背景
MySQL在執(zhí)行一個(gè)查詢時(shí)库快，可以有不同的執(zhí)行方案，MySQL的執(zhí)行成本由I/O和CPU成本兩個(gè)方便組成盒至。
- I/O成本
存儲(chǔ)引擎將數(shù)據(jù)和索引存儲(chǔ)在磁盤，當(dāng)查詢時(shí)士修，需要現(xiàn)將數(shù)據(jù)或索引加載到內(nèi)存中，人后在進(jìn)行操作樱衷。這個(gè)從磁盤到內(nèi)存的加載過程損耗的時(shí)間成為I/O成本棋嘲。 - CPU成本
讀取記錄以及檢測(cè)記錄是否滿足對(duì)應(yīng)的檢索條件、對(duì)結(jié)果集進(jìn)行排序等矩桂，這些操作損耗的時(shí)間成為CPU成本沸移。
對(duì)InnoDB存儲(chǔ)引擎來說，頁是磁盤和內(nèi)存進(jìn)行交互的基本單位（MySQL默認(rèn)頁面大小為16KB）。
MySQL規(guī)定讀取一個(gè)頁面花費(fèi)的成本默認(rèn)為1.0雹锣，讀取以及檢測(cè)一條記錄是夠符合檢索條件的成本默認(rèn)為0.2网沾。1.0和0.2這些數(shù)字成為成本常數(shù)。
計(jì)算成本流程
* 根據(jù)檢索條件蕊爵，找出所有可能使用的索引辉哥；
* 計(jì)算全表掃描的代價(jià)；
* 計(jì)算使用不同索引執(zhí)行查詢的代價(jià)攒射；
* 對(duì)比各種執(zhí)行方案的代價(jià)醋旦，找出成本最低的那個(gè)方案馏谨。
成本計(jì)算示例
數(shù)據(jù)庫表示例和檢索語句示例
數(shù)據(jù)庫示例
CREATE TABLE `t_test_table` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
`key1` varchar(20) NOT NULL COMMENT 'key1',
`key2` varchar(11) NOT NULL COMMENT 'key2',
`key3` varchar(200) DEFAULT NULL COMMENT 'key3',
`key4` varchar(32) NOT NULL COMMENT 'key4',
PRIMARY KEY (`id`)
KEY `idx_key1` (`key1`),
KEY `idx_key2` (`key2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='測(cè)試表'
檢索語句示例
select * from where t_test_table where key1 = '104' and key2 = '50' and key3 = '1';
根據(jù)檢索條件蛀醉，找出所有可能使用的索引
* key1 = '104' ：存在二級(jí)索引“idx_key1”
* key2 = '50' ： 存在二級(jí)索引“idx_key2”
* key3 = '1'：不存在索引；
計(jì)算全表掃描的代價(jià)
全表掃描的意思吧局促索引中的記錄都一次與給定的檢索條件進(jìn)行比較腐魂，并把符合檢索條件的記錄加入到檢索結(jié)果集中咧最。所以需要將居所索引的對(duì)用頁面加載到內(nèi)存中捂人，注意檢測(cè)記錄是否符合檢索條件。由于查詢成本=I/O成本 + CPU成本矢沿，所以計(jì)算全表掃描的代價(jià)時(shí)需要聚簇索引占用的頁面數(shù)及查詢表中的記錄數(shù)先慷。
MySQL為每個(gè)表維護(hù)了一系列的統(tǒng)計(jì)信息，并提供了“show tables staus”語句來查看表的統(tǒng)計(jì)信息咨察。如查看表t_biz_order的統(tǒng)計(jì)信息
參數(shù)說明:
- Rows：表中的記錄條數(shù)论熙；
- Data_length：表占用存儲(chǔ)控件字節(jié)數(shù)，Data_length=頁面數(shù)量*每個(gè)頁面的大猩阌脓诡；
根據(jù)上述信息，及表的頁面默認(rèn)大小為16KB媒役，計(jì)算聚簇索引的頁面數(shù)量：
聚簇索引的頁面數(shù)量 = 491520 / (16 * 1024) = 30
MySQL在真正計(jì)算成本時(shí)會(huì)進(jìn)行一些微調(diào)祝谚，這些微調(diào)至是直接硬編碼到代碼中。微調(diào)值十分小酣衷，不影響分析過程交惯，本文計(jì)算過程中直接忽略，采用約等于（≈）方式展示
- I/O成本：30 * 1.0 + 微調(diào)值 ≈ 30
- CPU成本：1414 * 0.2 + 微調(diào)值 ≈ 282.8
- 總成本：30 + 282.8 ≈ 312.8
計(jì)算說明：上述計(jì)算過程中30為聚簇索引占用的頁面數(shù)穿仪，1.0表示加載一個(gè)頁面所用的成本常數(shù)席爽，1414表示統(tǒng)計(jì)表中的記錄數(shù)，0.2是指訪問一條記錄所需的成本常數(shù)啊片，微調(diào)值則為微調(diào)常數(shù)只锻。
計(jì)算使用不同索引執(zhí)行查詢的代價(jià)
- 前文已列舉查詢可能使用索引的全部情況，錢數(shù)查詢可能使用到“idx_key1”和“idx_key2”兩個(gè)索引紫谷。MySQL查詢優(yōu)化器先分析使用唯一二級(jí)索引的成本齐饮，在分析普通索引的成本捐寥。但本文示例中沒有唯一二級(jí)索引，所以直接分析“idx_key1”索引的成本
“idx_key1”索引的成本
- 對(duì)于二級(jí)索引的查詢時(shí)祖驱，方式為先查詢二級(jí)索引再回表的方式握恳。計(jì)算這種查詢的成本時(shí)需要知道二級(jí)索引掃描區(qū)間數(shù)量及需要回表的記錄數(shù)。
掃描區(qū)間數(shù)量
MySQL查詢優(yōu)化器認(rèn)為讀取索引的一個(gè)掃描區(qū)間的I/O成本與讀取一個(gè)頁面的I/O成本是相同的捺僻。本例中乡洼，使用“idx_key1”索引掃描區(qū)間只有一個(gè)[104, 104]，所以相當(dāng)于掃描這個(gè)區(qū)間的二級(jí)索引付出的I/O成本就是1 * 1.0 = 1.0
需要回表的記錄數(shù)
二級(jí)索引掃描區(qū)間包含多少記錄并非直接從記錄的左側(cè)直接遍歷到區(qū)間的最右側(cè)陵像，計(jì)算具體數(shù)量過程如下：
- 找到檢索條件的最左側(cè)記錄就珠，常數(shù)級(jí)別，消耗忽略不計(jì)醒颖；
- 找到檢索條件的最右側(cè)記錄妻怎，常數(shù)級(jí)別，消耗忽略不計(jì)泞歉；
- 區(qū)間最左側(cè)記錄和最右側(cè)記錄逼侦，頁面號(hào)相隔不遠(yuǎn)的情況下，計(jì)算兩個(gè)頁面之間的頁面記錄總數(shù)并相加腰耙；
- 區(qū)間最左側(cè)記錄和最右側(cè)記錄榛丢，頁面號(hào)相隔較遠(yuǎn)的情況下，根據(jù)B+樹的特性挺庞，分別查找左側(cè)記錄和右側(cè)記錄的父節(jié)點(diǎn)晰赞，計(jì)算兩個(gè)頁面之間的記錄數(shù)
根據(jù)記錄的主鍵值到聚簇索引中執(zhí)行回表操作
- MySQL認(rèn)為每次回表操作都相當(dāng)于訪問一次頁面，也就是說二級(jí)索引掃描區(qū)間有多少記錄选侨，就需要進(jìn)行多少次回表操作掖鱼，也就是要進(jìn)行多少次頁面I/O。所以回表操作帶來的I/O成本就是301 * 1.0 = 301.0援制。
回表操作戏挡，獲取完整記錄，再檢測(cè)其他條件是夠成立
回表操作的本質(zhì)就是通過二級(jí)索引記錄的主鍵值到聚簇索引中找到完整的記錄晨仑，然后再檢測(cè)“unit_id = 147”這個(gè)搜索條件以外的其他條件是否都成立褐墅。已知滿足的條件的二級(jí)索引記錄是301條，所以讀取并檢測(cè)這些完整記錄所需要的CPU成本為301 * 0.2 = 60.2 洪己。
所以本例中使用“key1”索引查詢的成本如下：
- I/O成本：1.0 + 301 * 1.0 = 302.0 （掃描區(qū)間的數(shù)量 + 預(yù)估的二級(jí)索引記錄條數(shù)）
- CPU成本：301 * 0.2 + 301 * 0.2 = 120.4
- 總成本：302.0 + 120.4 = 422.4
計(jì)算說明：上述計(jì)算過程中301為滿足檢索條件的二級(jí)索引的記錄數(shù)量妥凳，1.0表示加載一個(gè)頁面所用的成本常數(shù)，0.2是指訪問一條記錄所需的成本常數(shù)码泛。
“key2”索引的成本
“key2”索引與“idx_biz_order_unit”索引一樣猾封，均為普通二級(jí)索引，所以其計(jì)算索引成本與之前計(jì)算方式一致噪珊，這里直接給出結(jié)論晌缘。
- I/O成本：1.0 + 8 * 1.0 = 9.0 （掃描區(qū)間的數(shù)量 + 預(yù)估的二級(jí)索引記錄條數(shù)）
- CPU成本：8 * 0.2 + 8 * 0.2 = 3.2
- 總成本：9.0 + 3.2 = 12.2
計(jì)算說明：上述計(jì)算過程中8為滿足檢索條件的二級(jí)索引的記錄數(shù)量，1.0表示加載一個(gè)頁面所用的成本常數(shù)痢站，0.2是指訪問一條記錄所需的成本常數(shù)磷箕。
對(duì)比各種執(zhí)行方案的代價(jià)，找出成本最低的那個(gè)方案阵难。
- 全表掃描成本：312.8
- 使用索引“key1”成本：422.4
- 使用索引“key2”成本：12.2
很顯然使用索引“key2”成本最低岳枷，所以選擇“key2”索引執(zhí)行查詢。
使用explain查詢執(zhí)行計(jì)劃
explain select * from where t_test_table where key1 = '104' and key2 = '50' and key3 = '1';
Intersection索引合并簡介
- 細(xì)心的小伙伴已經(jīng)發(fā)現(xiàn)呜叫，我們自己計(jì)算成本的結(jié)果與使用explain執(zhí)行計(jì)劃所得到的結(jié)果不一致空繁。我們分析得到的成本為12.2，而explain執(zhí)行計(jì)劃得到的成本為9.6朱庆。這是因?yàn)镸ySQL優(yōu)化器在優(yōu)化SQL時(shí)使用了Intersection索引合并盛泡。
我們知道示例的SQL語句可能執(zhí)行的索引有兩個(gè)“key2”和“key1”。上述索引執(zhí)行過程中均需要查詢二級(jí)索引然后再根據(jù)二級(jí)索引記錄的主鍵進(jìn)行一次回表娱颊。Intersection索引合并指的是從不同索引中掃描到的記錄的id值取交集傲诵，只為這些id值執(zhí)行回表操作。
Intersection索引合并條件：每個(gè)索引中獲取到的二級(jí)索引記錄都是按主鍵值排序的箱硕。
不同索引命中的主鍵id如下：
- I/O成本：1.0 + 8 * 1.0 = 9.0 （掃描區(qū)間的數(shù)量 + 預(yù)估的二級(jí)索引記錄條數(shù)）
- CPU成本：8 * 0.2 + 4 * 0.2 = 2.4
- 總成本：9.0 + 2.4 = 11.4
計(jì)算成本也不完全一致拴竹，猜測(cè)MySQL在針對(duì)索引合并優(yōu)化時(shí)有其他的成本計(jì)算方式？
