具體細(xì)節(jié) 請(qǐng)去掘金購(gòu)買《MySQL 是怎樣運(yùn)行的：從根兒上理解 MySQL》

查詢成本組成有哪些。

• 1.I/O成本
• 2.CPU成本
• 3.Mysql規(guī)定讀取一個(gè)頁(yè)面花費(fèi)的成本默認(rèn)是1.0，讀取以及檢測(cè)一條記錄是否符合搜索條件的成本默認(rèn)是0.2。1.0、0.2這些數(shù)字稱之為成本常數(shù)
• 4.需要注意的是，不管讀取記錄時(shí)需不需要檢測(cè)是否滿足搜索條件，其成本都算是0.2。

單表查詢的成本

基于成本的優(yōu)化步驟

• 1.在一條單表查詢語(yǔ)句真正執(zhí)行之前，MySQL的查詢優(yōu)化器會(huì)找出執(zhí)行該語(yǔ)句所有可能使用的方案，對(duì)比之后找出成本最低的方案--這個(gè)成本最低的方案就是所謂的執(zhí)行計(jì)劃
• 2.具體流程如下：
• 3.根據(jù)搜索條件，找出所有可能使用的索引
• 4.計(jì)算全表掃描的代價(jià)
• 5.計(jì)算使用不同索引執(zhí)行查詢的代價(jià)
• 6.對(duì)比各種執(zhí)行方案的代價(jià)，找出成本最低的那一個(gè)

根據(jù)搜索條件，找出所有可能使用的索引

• 1.一個(gè)查詢中可能使用到的索引稱之為possible keys。

計(jì)算全表掃描的代價(jià)

• 1.聚簇索引占用的頁(yè)面數(shù)
• 2.該表中的記錄數(shù)
• 3.MySQL為每個(gè)表維護(hù)了一系列的統(tǒng)計(jì)信息,上述1和2的信息就在這里面。
• 4.可以通過(guò)SHOW TABLE STATUS LIKE tableName 來(lái)查詢
• 5.Rows代表記錄--在innodb下只是一個(gè)概數(shù)。
• 6.Data_length--表示表占用的存儲(chǔ)空間字節(jié)數(shù)。
• 7.Data_length = 聚簇索引的頁(yè)面數(shù)量 x 每個(gè)頁(yè)面的大?。?6KB）
• 8.I/O成本==頁(yè)面數(shù)量*1.0+1.1
• 9.CPU成本=ROWS*0.2+1.0--0.2指的是訪問(wèn)一條記錄所需的成本常數(shù)
• 10.總成本=I/O成本+CPU成本。
• 11.我們前邊說(shuō)過(guò)表中的記錄其實(shí)都存儲(chǔ)在聚簇索引對(duì)應(yīng)B+樹(shù)的葉子節(jié)點(diǎn)中，所以只要我們通過(guò)根節(jié)點(diǎn)獲得了最左邊的葉子節(jié)點(diǎn)，就可以沿著葉子節(jié)點(diǎn)組成的雙向鏈表把所有記錄都查看一遍
  。也就是說(shuō)全表掃描這個(gè)過(guò)程其實(shí)有的B+樹(shù)內(nèi)節(jié)點(diǎn)是不需要訪問(wèn)的.上述計(jì)算全表掃描是個(gè)比較粗略的計(jì)算。

計(jì)算使用不同索引執(zhí)行查詢的代價(jià)

• 1.要分別分析單獨(dú)使用這些索引執(zhí)行查詢的成本，最后還要分析是否可能使用到索引合并
• 2.MySQL查詢優(yōu)化器先分析使用唯一二級(jí)索引的成本，再分析使用普通索引的成本---當(dāng)然這個(gè)前提是無(wú)法直接走主鍵索引。
• 3.對(duì)于使用二級(jí)索引 + 回表方式的查詢：主要考慮范圍區(qū)間數(shù)量和需要回表的記錄數(shù)。
• 4.范圍區(qū)間數(shù)量：不論某個(gè)范圍區(qū)間的二級(jí)索引到底占用了多少頁(yè)面，查詢優(yōu)化器粗暴的認(rèn)為讀取索引的一個(gè)范圍區(qū)間的I/O成本和讀取一個(gè)頁(yè)面是相同的
• 5.需要回表的記錄數(shù):首先通過(guò)左右區(qū)間可以在常數(shù)范圍找到臨界點(diǎn)記錄。然后只要從區(qū)間最左記錄沿著鏈表方向走向區(qū)間最右記錄。如果兩者相隔不超過(guò)10個(gè)頁(yè)面
  是可以得到回表的準(zhǔn)確數(shù)據(jù)。如果超過(guò)則統(tǒng)計(jì)10個(gè)頁(yè)面情況下平均每個(gè)頁(yè)面包含的記錄數(shù)，然后再乘以頁(yè)面?zhèn)€數(shù)。
• 6.尋找頁(yè)面數(shù)，只要去記錄也得父層尋找即可。
• 7.CPU成本主要是讀取二級(jí)索引記錄的成本 + 讀取并檢測(cè)回表后聚簇索引記錄的成本

index dive

• 1.Mysql把這種通過(guò)直接訪問(wèn)索引對(duì)應(yīng)的B+樹(shù)來(lái)計(jì)算某個(gè)范圍區(qū)間對(duì)應(yīng)的索引記錄條數(shù)的方式稱之為index dive
• 2.當(dāng)我們使用二級(jí)索引in的時(shí)候，由于不是唯一的，所以在in中的每一個(gè)參數(shù)都需要index dive
• 3.mysql通過(guò)eq_range_index_dive_limit參數(shù)，如果是in的話 參數(shù)數(shù)量大于這個(gè)參數(shù) 則采用索引統(tǒng)計(jì)數(shù)據(jù)來(lái)進(jìn)行估算記錄數(shù)量。
• 4.主要使用ROWS 和Cardinality，兩者可以計(jì)算出一個(gè)值得重復(fù)次數(shù)==一個(gè)值的重復(fù)次數(shù) ≈ Rows ÷ Cardinality
• 5.所以一個(gè)in中一個(gè)參數(shù)代表10個(gè)記錄

mysql為索引的統(tǒng)計(jì)數(shù)據(jù)

• 1.MySQL也會(huì)為表中的每一個(gè)索引維護(hù)一份統(tǒng)計(jì)數(shù)據(jù)---- SHOW INDEX FROM TABLENAME
• 2.關(guān)鍵的屬性：Cardinality，Sub_part
• 3.Cardinality：表示索引列中不重復(fù)值的個(gè)數(shù)，這是一個(gè)估數(shù)。 值越大代表該列重復(fù)值越少。值越大重復(fù)越小，則可區(qū)分度就很大，進(jìn)而建立索引的意義不大。
• 4.Sub_part：對(duì)于存儲(chǔ)字符串或者字節(jié)串的列來(lái)說(shuō)，有時(shí)候我們只想對(duì)這些串的前n個(gè)字符或字節(jié)建立索引，這個(gè)屬性表示的就是那個(gè)n值。如果對(duì)完整的列建立索引的話，該屬性的值就是NULL。

連接查詢的成本

• 1.MySQL中連接查詢采用的是嵌套循環(huán)連接算法，驅(qū)動(dòng)表會(huì)被訪問(wèn)一次，被驅(qū)動(dòng)表可能會(huì)被訪問(wèn)多次
• 2.查詢成主要是 ：?jiǎn)未尾樵凃?qū)動(dòng)表的成本和多次查詢被驅(qū)動(dòng)表的成本（具體查詢多少次取決于對(duì)驅(qū)動(dòng)表查詢的結(jié)果集中有多少條記錄）
• 3.驅(qū)動(dòng)表進(jìn)行查詢后得到的記錄條數(shù)稱之為驅(qū)動(dòng)表的扇出（英文名：fanout）

Condition filtering

• 1.當(dāng)使用的是全表掃描的方式執(zhí)行的單表查詢，那么計(jì)算驅(qū)動(dòng)表扇出時(shí)需要猜滿足搜索條件的記錄到底有多少條。
• 2.如果使用的是索引執(zhí)行的單表掃描，那么計(jì)算驅(qū)動(dòng)表扇出的時(shí)候需要猜滿足除使用到對(duì)應(yīng)索引的搜索條件外的其他搜索條件的記錄有多少條。

兩表連接的成本分析

• 1.連接查詢總成本 = 單次訪問(wèn)驅(qū)動(dòng)表的成本 + 驅(qū)動(dòng)表扇出數(shù) x 單次訪問(wèn)被驅(qū)動(dòng)表的成本
• 2.對(duì)于左（外）連接和右（外）連接查詢來(lái)說(shuō)，它們的驅(qū)動(dòng)表是固定的，所以想要得到最優(yōu)的查詢方案只需要：分別為驅(qū)動(dòng)表和被驅(qū)動(dòng)表選擇成本最低的訪問(wèn)方法。
• 3.對(duì)于內(nèi)連接來(lái)說(shuō)，驅(qū)動(dòng)表和被驅(qū)動(dòng)表的位置是可以互換的,因此算出最優(yōu)的方式執(zhí)行。

多表連接的成本分析

• 1.有n個(gè)表進(jìn)行連接，MySQL查詢優(yōu)化器要每一種連接順序的成本都計(jì)算一遍么？
• 2.提前維護(hù)一個(gè)最小成本值，每個(gè)連接順序查詢成本計(jì)算時(shí)只要大于這個(gè)的直接跳過(guò)。
• 3.系統(tǒng)變量optimizer_search_depth，可以規(guī)定最多窮舉多少種查詢方式。
• 4.根據(jù)某些規(guī)則壓根兒就不考慮某些連接順序--這些規(guī)則叫啟發(fā)規(guī)則，可以通過(guò)系統(tǒng)變量optimizer_prune_level來(lái)控制到底是不是用這些啟發(fā)式規(guī)則。

調(diào)節(jié)成本常數(shù)

• 1.讀取一個(gè)頁(yè)面花費(fèi)的成本默認(rèn)是1.0
• 2.測(cè)一條記錄是否符合搜索條件的成本默認(rèn)是0.2
• 3.其實(shí)除了這兩個(gè)成本常數(shù)，MySQL還支持好多呢，它們被存儲(chǔ)到了mysql數(shù)據(jù)庫(kù)的兩個(gè)表中：engine_cost 和server_cost 。
• 4.一條語(yǔ)句的執(zhí)行其實(shí)是分為兩層的：server層，存儲(chǔ)引擎層
• 5.通過(guò)update 修改常數(shù)，然后通過(guò)FLUSH OPTIMIZER_COSTS;生效