1. 事件背景

周五下班后，或是DBA同學(xué)已下班沒(méi)找到，或是考慮到我在公司維護(hù)著數(shù)據(jù)庫(kù)中間件，對(duì)數(shù)據(jù)庫(kù)這類(lèi)問(wèn)題會(huì)有一些經(jīng)驗(yàn)，邢老師找來(lái)說(shuō)是討論一個(gè)奇怪的sql執(zhí)行計(jì)劃問(wèn)題，我本是稍有自信，但經(jīng)過(guò)簡(jiǎn)單上下文同步和一番操作演示討論后，我也覺(jué)得這個(gè)情況挺奇怪，讓人有點(diǎn)懵。。。

原始案例完整同步的性價(jià)比不高，我簡(jiǎn)單描述一下，能跟讀者認(rèn)知對(duì)齊就好；情況大概是這樣：一個(gè)表里除了有主鍵，還涉及到另外3個(gè)索引，A索引、B索引、A+B組合索引，使用不同的索引explain中顯示的預(yù)估行數(shù)rows的結(jié)果是不同的，情況如下，其中第3條很讓人疑惑：

索引情況	查詢計(jì)劃	實(shí)際結(jié)果行數(shù)	預(yù)估掃描行數(shù)
存在A、B兩個(gè)字段的獨(dú)立索引	僅命中A索引檢索（where a= xxx）	26	26
存在A、B兩個(gè)字段的獨(dú)立索引	僅命中B索引（where b=yyy）	256	255
存在A、B兩個(gè)字段的獨(dú)立索引	命中A索引和B索引（where a= xxx and b = yyy）	9	4
有A+B兩個(gè)字段的組合索引	命中A+B組合索引（where a= xxx and b = yyy）	9	9

已經(jīng)好久沒(méi)專(zhuān)門(mén)研究數(shù)據(jù)庫(kù)底層的東西了，多年前曾對(duì)《SQL Server技術(shù)內(nèi)幕》系列叢書(shū)有潛心研讀，略有積累，這幾本書(shū)分別是：T-SQL程序設(shè)計(jì)，T-SQL查詢 ，存儲(chǔ)引擎，查詢調(diào)整及優(yōu)化（如果用到SQL Server的話，這些書(shū)推薦去看看）；雖然對(duì)SQL Server執(zhí)行計(jì)劃調(diào)優(yōu)這方面有一些認(rèn)知儲(chǔ)備，但當(dāng)天討論的畢竟是MySQL，張冠李戴并不是技術(shù)人的作風(fēng)，原理及現(xiàn)象不敢太肯定。

2. 相關(guān)技術(shù)簡(jiǎn)述

2.1 B+樹(shù)組織結(jié)構(gòu)

這種索引情況MySQL 是以B+樹(shù)結(jié)構(gòu)來(lái)組織管理索引頁(yè)和數(shù)據(jù)頁(yè)

• 數(shù)據(jù)頁(yè)是說(shuō)包含完整行記錄的頁(yè)（如上圖左下側(cè)的 黃框圈注的4個(gè)頁(yè)），索引頁(yè)是說(shuō)只包含索引記錄的頁(yè)（上圖剩余的其他頁(yè)）
• 索引是排序的，頁(yè)的組織管理也依賴于這個(gè)有序性
• 聚簇索引（左邊）的葉子節(jié)點(diǎn)是數(shù)據(jù)頁(yè)，非聚簇索引（右邊）的葉子節(jié)點(diǎn)不是數(shù)據(jù)頁(yè)，
• 在非聚簇索引中檢索的最終結(jié)果是聚簇索引的key，而不是數(shù)據(jù)頁(yè)的rowID；這樣低耦合設(shè)計(jì)是有好處的，比如當(dāng)空間壓縮時(shí)，會(huì)避免很多頁(yè)內(nèi)記錄的變更。

2.2 執(zhí)行計(jì)劃

• 執(zhí)行計(jì)劃是什么

執(zhí)行計(jì)劃是數(shù)據(jù)庫(kù)的查詢優(yōu)化器根據(jù)用戶輸入的SQL語(yǔ)句，以及其內(nèi)部的執(zhí)行策略和統(tǒng)計(jì)信息選擇出一個(gè)其認(rèn)為執(zhí)行效率最優(yōu)的計(jì)劃，然后使用這個(gè)計(jì)劃獲取數(shù)據(jù)。我們通常借助執(zhí)行計(jì)劃查看數(shù)據(jù)庫(kù)如何處理SQL語(yǔ)句，分析性能瓶頸。

• 查看執(zhí)行計(jì)劃：

在select前面加explain關(guān)鍵字，執(zhí)行后可看到下圖中的執(zhí)行計(jì)劃信息

下表是對(duì)執(zhí)行計(jì)劃信息各字段的簡(jiǎn)單介紹，本文的重點(diǎn)是其中的rows字段。

3. rows官網(wǎng)怎么解釋

3.1 資料顯示

從官網(wǎng)可看到以下描述

• rows? (JSON name: rows)

Therows column indicates the number of rows MySQL believes it must examine to execute the query.For InnoDB tables,   this number is an estimate, and may not always be exact.

漢化：rows 列表示MySQL認(rèn)為執(zhí)行查詢必須檢查的行數(shù)。對(duì)于InnoDB，這個(gè)數(shù)字是一個(gè)估計(jì)，不一定準(zhǔn)確。

3.2 所思所想

官網(wǎng)這話很精辟，但其內(nèi)部的一些關(guān)鍵設(shè)計(jì)卻并未提及。

4.  關(guān)于Rows的一種解釋A

4.1 資料顯示

• 在查詢優(yōu)化器決定使用全表掃描的方式對(duì)某個(gè)表執(zhí)行查詢時(shí)，執(zhí)行計(jì)劃的rows列就代表該表的估計(jì)行數(shù)。
• 如果使用索引來(lái)執(zhí)行查詢，執(zhí)行計(jì)劃的rows列就代表預(yù)計(jì)掃描的索引記錄行數(shù)。

4.2 所思所想

• 全表掃描時(shí)：rows對(duì)應(yīng)的是僅數(shù)據(jù)頁(yè)中預(yù)計(jì)要掃描的行記錄數(shù)量嘛？
• 索引掃描時(shí)：rows對(duì)應(yīng)的是預(yù)計(jì)掃描的索引記錄行數(shù)

如果是聚簇索引，那這個(gè)行數(shù)是 索引頁(yè)+數(shù)據(jù)頁(yè)中的記錄行數(shù)嘛？

如果是非聚簇索引，那這個(gè)行數(shù)是僅非聚簇索引頁(yè)中的索引記錄行數(shù)嘛？

• 既然是掃描，那為什么又會(huì)說(shuō)數(shù)據(jù)不準(zhǔn)呢？這里為什么沒(méi)提及統(tǒng)計(jì)信息呢？SQL Server中執(zhí)行計(jì)劃評(píng)估的掃描行數(shù)是跟統(tǒng)計(jì)信息有關(guān)的，莫非MySQL不是？

5. 關(guān)于Rows的一種解釋B

5.1 資料顯示

如果查詢優(yōu)化器決定使用全表掃描的方式對(duì)某個(gè)表執(zhí)行查詢時(shí)，執(zhí)行計(jì)劃的 rows 列就代表預(yù)計(jì)需要掃描的行數(shù)。

如果使用索引來(lái)執(zhí)行查詢時(shí)，執(zhí)行計(jì)劃的 rows 列就代表預(yù)計(jì)掃描的索引記錄行數(shù)。

這有可能是個(gè)精確值，也可能是個(gè)估算值，計(jì)算方法有 index dive 和基于索引統(tǒng)計(jì)的估算。

5.2 所思所想

1、2兩條跟A說(shuō)法相似，且未提到更多的細(xì)節(jié)，但第3條信息就很重要了，給前邊的疑問(wèn)提供了一些線索，MySQL也是會(huì)基于統(tǒng)計(jì)信息來(lái)選擇執(zhí)行計(jì)劃的，統(tǒng)計(jì)信息是會(huì)有誤差的；只是 index dive 是什么呢？統(tǒng)計(jì)信息又是怎樣的實(shí)現(xiàn)機(jī)制呢？

6. 關(guān)于index div的解釋

6.1 Index dive是什么

獲取索引對(duì)應(yīng)的B+樹(shù)的 區(qū)間最左記錄和區(qū)間最右記錄，然后再計(jì)算這兩條記錄之間有多少記錄（記錄條數(shù)少的時(shí)候可以做到精確計(jì)算，多的時(shí)候只能估算）。MySQL把這種通過(guò)直接訪問(wèn)索引對(duì)應(yīng)的B+樹(shù)來(lái)計(jì)算某個(gè)范圍區(qū)間對(duì)應(yīng)的索引記錄條數(shù)的方式稱之為Index dive。

跟Index dive相關(guān)的有一個(gè)配置參數(shù) eq_range_index_dive_limit，作用大概是這樣：

• 當(dāng)where語(yǔ)句in條件中參數(shù)個(gè)數(shù)小于這個(gè)值的時(shí)候，MySQL就采用Index dive的方式預(yù)估掃描行數(shù)，非常準(zhǔn)確。
• 當(dāng)where語(yǔ)句in條件中參數(shù)個(gè)數(shù)大于等于這個(gè)值的時(shí)候，MySQL就采用另一種方式索引統(tǒng)計(jì)預(yù)估掃描行數(shù)，誤差較大。
• MySQL 不同的版本中這個(gè)默認(rèn)值不同，可以根據(jù)需求場(chǎng)景進(jìn)行調(diào)整。

6.2 所思所想

從這個(gè)信息再次看出，采用Index div會(huì)較精準(zhǔn)的預(yù)估掃描行數(shù)，但估算成本較高，適合小數(shù)據(jù)量。

索引統(tǒng)計(jì)估算成本較低，適合數(shù)據(jù)量大的情況。但使用索引統(tǒng)計(jì)的話，評(píng)估不準(zhǔn)，甚至誤差很大，為什么誤差大以及誤差到底有多大，接下來(lái)再搜集相關(guān)資料來(lái)了解。

7. 關(guān)于統(tǒng)計(jì)信息的解釋

7.1 統(tǒng)計(jì)信息介紹

查詢優(yōu)化是在代價(jià)統(tǒng)計(jì)分析的基礎(chǔ)上進(jìn)行的，合理的代價(jià)模型和準(zhǔn)確的代價(jià)統(tǒng)計(jì)信息決定了查詢優(yōu)化的優(yōu)劣。My SQL的代價(jià)模型依賴的主要因素是IO和CPU，IO主要跟數(shù)據(jù)量和緩存相關(guān)，CPU主要跟參與排序比較的記錄數(shù)相關(guān)。因此統(tǒng)計(jì)信息的指標(biāo)主要是數(shù)據(jù)量和記錄數(shù)，如：

• table scan：全表掃描統(tǒng)計(jì)信息包括數(shù)據(jù)量和記錄數(shù)。
• index scan：索引統(tǒng)計(jì)信息，索引鍵值分布情況，即cardinality。
• range scan：索引范圍掃描統(tǒng)計(jì)信息，一定范圍內(nèi)的記錄數(shù)和數(shù)據(jù)量。

7.2 查看索引統(tǒng)計(jì)

innodb的統(tǒng)計(jì)信息

• mysql.innodb_table_stats ：存儲(chǔ)了關(guān)于表的統(tǒng)計(jì)數(shù)據(jù)，每一條記錄對(duì)應(yīng)著一個(gè)表的統(tǒng)計(jì)數(shù)據(jù)
• mysql.innodb_index_stats ：存儲(chǔ)了關(guān)于索引的統(tǒng)計(jì)數(shù)據(jù)，每一條記錄對(duì)應(yīng)著一個(gè)索引的一個(gè)統(tǒng)計(jì)項(xiàng)的統(tǒng)計(jì)數(shù)據(jù)。

以innodb_table_stats表為例，各個(gè)列的說(shuō)明：

列名	說(shuō)明
database_name	數(shù)據(jù)庫(kù)名
table_name	表名
last_update	本條記錄最后更新時(shí)間
n_rows	表中記錄的條數(shù)
clustered_index_size	表的聚簇索引占用的頁(yè)面數(shù)量
sum_of_other_index_sizes	表的其他索引占用的頁(yè)面數(shù)量

顯而易見(jiàn)，這其中的n_rows很關(guān)鍵，那他的值是怎么算的呢？

7.3 統(tǒng)計(jì)信息的采樣

執(zhí)行計(jì)劃中的預(yù)估的行數(shù)依賴n_rows，InnoDB中n_rows的統(tǒng)計(jì)是這樣的：

• 按照一定算法（并不是純粹隨機(jī)的）選取幾個(gè)葉子節(jié)點(diǎn)頁(yè)面
• 計(jì)算每個(gè)頁(yè)面中主鍵值記錄數(shù)量
• 計(jì)算平均一個(gè)頁(yè)面中主鍵值的記錄數(shù)量乘以全部葉子節(jié)點(diǎn)的數(shù)量就算是該表的n_rows值

由此可知n_rows值是否精確取決于統(tǒng)計(jì)時(shí)采樣的頁(yè)面數(shù)量，通過(guò)innodb_stats_persistent_sample_pages設(shè)置，設(shè)置的越大，統(tǒng)計(jì)出的相對(duì)越精確，但是耗時(shí)也會(huì)增加；設(shè)置得越小，統(tǒng)計(jì)出的值越不精確，但是統(tǒng)計(jì)耗時(shí)就少，要視實(shí)際情況而定。

7.4 統(tǒng)計(jì)信息的更新

MySQL中以下情況會(huì)觸發(fā)統(tǒng)計(jì)信息更新：

• 距離上一次更新統(tǒng)計(jì)信息，發(fā)生變化的行數(shù)超過(guò)一定數(shù)值時(shí)自動(dòng)更新(transient：1/16， persistent ：1/10）
• analyze table
• create table/truncate table 會(huì)初始化統(tǒng)計(jì)信息
• 查詢information_schema.tables information_schema.statistic(innodb_stats_on_metadata=ON)

其第一種是發(fā)生變動(dòng)的記錄數(shù)量超過(guò)了表大小的10%，那么服務(wù)器會(huì)自動(dòng)觸發(fā)一次異步的統(tǒng)計(jì)數(shù)據(jù)的計(jì)算；其他方式是手動(dòng)觸發(fā)。

8. 總結(jié)

本篇主要是基于一次日常工作中的技術(shù)溝通，以執(zhí)行計(jì)劃中rows為主線，搜集資料梳理認(rèn)知；可知識(shí)是無(wú)限的，到現(xiàn)在也還未能探索出跟預(yù)期不一致的實(shí)際的計(jì)算過(guò)程，也只是達(dá)到對(duì)此知識(shí)點(diǎn)有個(gè)淺層的系統(tǒng)的認(rèn)知，幫助后續(xù)繼續(xù)分析探索其內(nèi)幕；同時(shí)也希望本次學(xué)習(xí)中的記錄能夠?qū)δ阌幸妗?/p>

本文轉(zhuǎn)載自微信公眾號(hào)「架構(gòu)染色」，可以通過(guò)以下二維碼關(guān)注。轉(zhuǎn)載本文請(qǐng)聯(lián)系聯(lián)系【架構(gòu)染色】公眾號(hào)作者。