1. 事件背景

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

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

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

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

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

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

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

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

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

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

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

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

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

下表是對執(zhí)行計(jì)劃信息各字段的簡單介紹，本文的重點(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ù)。對于InnoDB，這個數(shù)字是一個估計(jì)，不一定準(zhǔn)確。

3.2 所思所想

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

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

4.1 資料顯示

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

4.2 所思所想

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

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

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

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

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

5.1 資料顯示

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

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

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

5.2 所思所想

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

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

6.1 Index dive是什么

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

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

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

6.2 所思所想

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

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

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

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

查詢優(yōu)化是在代價統(tǒng)計(jì)分析的基礎(chǔ)上進(jìn)行的，合理的代價模型和準(zhǔn)確的代價統(tǒng)計(jì)信息決定了查詢優(yōu)化的優(yōu)劣。My SQL的代價模型依賴的主要因素是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 ：存儲了關(guān)于表的統(tǒng)計(jì)數(shù)據(jù)，每一條記錄對應(yīng)著一個表的統(tǒng)計(jì)數(shù)據(jù)
• mysql.innodb_index_stats ：存儲了關(guān)于索引的統(tǒng)計(jì)數(shù)據(jù)，每一條記錄對應(yīng)著一個索引的一個統(tǒng)計(jì)項(xiàng)的統(tǒng)計(jì)數(shù)據(jù)。

以innodb_table_stats表為例，各個列的說明：

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

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

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

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

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

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

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

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

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

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

8. 總結(jié)

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

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