1. 事件背景

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

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

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

2. 相關技術簡述

2.1 B+樹組織結構

這種索引情況MySQL 是以B+樹結構來組織管理索引頁和數據頁

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

2.2 執行計劃

• 執行計劃是什么

執行計劃是數據庫的查詢優化器根據用戶輸入的SQL語句，以及其內部的執行策略和統計信息選擇出一個其認為執行效率最優的計劃，然后使用這個計劃獲取數據。我們通常借助執行計劃查看數據庫如何處理SQL語句，分析性能瓶頸。

• 查看執行計劃：

在select前面加explain關鍵字，執行后可看到下圖中的執行計劃信息

下表是對執行計劃信息各字段的簡單介紹，本文的重點是其中的rows字段。

3. rows官網怎么解釋

3.1 資料顯示

從官網可看到以下描述

• 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認為執行查詢必須檢查的行數。對于InnoDB，這個數字是一個估計，不一定準確。

3.2 所思所想

官網這話很精辟，但其內部的一些關鍵設計卻并未提及。

4.  關于Rows的一種解釋A

4.1 資料顯示

• 在查詢優化器決定使用全表掃描的方式對某個表執行查詢時，執行計劃的rows列就代表該表的估計行數。
• 如果使用索引來執行查詢，執行計劃的rows列就代表預計掃描的索引記錄行數。

4.2 所思所想

• 全表掃描時：rows對應的是僅數據頁中預計要掃描的行記錄數量嘛？
• 索引掃描時：rows對應的是預計掃描的索引記錄行數

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

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

• 既然是掃描，那為什么又會說數據不準呢？這里為什么沒提及統計信息呢？SQL Server中執行計劃評估的掃描行數是跟統計信息有關的，莫非MySQL不是？

5. 關于Rows的一種解釋B

5.1 資料顯示

如果查詢優化器決定使用全表掃描的方式對某個表執行查詢時，執行計劃的 rows 列就代表預計需要掃描的行數。

如果使用索引來執行查詢時，執行計劃的 rows 列就代表預計掃描的索引記錄行數。

這有可能是個精確值，也可能是個估算值，計算方法有 index dive 和基于索引統計的估算。

5.2 所思所想

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

6. 關于index div的解釋

6.1 Index dive是什么

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

跟Index dive相關的有一個配置參數 eq_range_index_dive_limit，作用大概是這樣：

• 當where語句in條件中參數個數小于這個值的時候，MySQL就采用Index dive的方式預估掃描行數，非常準確。
• 當where語句in條件中參數個數大于等于這個值的時候，MySQL就采用另一種方式索引統計預估掃描行數，誤差較大。
• MySQL 不同的版本中這個默認值不同，可以根據需求場景進行調整。

6.2 所思所想

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

索引統計估算成本較低，適合數據量大的情況。但使用索引統計的話，評估不準，甚至誤差很大，為什么誤差大以及誤差到底有多大，接下來再搜集相關資料來了解。

7. 關于統計信息的解釋

7.1 統計信息介紹

查詢優化是在代價統計分析的基礎上進行的，合理的代價模型和準確的代價統計信息決定了查詢優化的優劣。My SQL的代價模型依賴的主要因素是IO和CPU，IO主要跟數據量和緩存相關，CPU主要跟參與排序比較的記錄數相關。因此統計信息的指標主要是數據量和記錄數，如：

• table scan：全表掃描統計信息包括數據量和記錄數。
• index scan：索引統計信息，索引鍵值分布情況，即cardinality。
• range scan：索引范圍掃描統計信息，一定范圍內的記錄數和數據量。

7.2 查看索引統計

innodb的統計信息

• mysql.innodb_table_stats ：存儲了關于表的統計數據，每一條記錄對應著一個表的統計數據
• mysql.innodb_index_stats ：存儲了關于索引的統計數據，每一條記錄對應著一個索引的一個統計項的統計數據。

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

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

7.3 統計信息的采樣

執行計劃中的預估的行數依賴n_rows，InnoDB中n_rows的統計是這樣的：

• 按照一定算法（并不是純粹隨機的）選取幾個葉子節點頁面
• 計算每個頁面中主鍵值記錄數量
• 計算平均一個頁面中主鍵值的記錄數量乘以全部葉子節點的數量就算是該表的n_rows值

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

7.4 統計信息的更新

MySQL中以下情況會觸發統計信息更新：

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

其第一種是發生變動的記錄數量超過了表大小的10%，那么服務器會自動觸發一次異步的統計數據的計算；其他方式是手動觸發。

8. 總結

本篇主要是基于一次日常工作中的技術溝通，以執行計劃中rows為主線，搜集資料梳理認知；可知識是無限的，到現在也還未能探索出跟預期不一致的實際的計算過程，也只是達到對此知識點有個淺層的系統的認知，幫助后續繼續分析探索其內幕；同時也希望本次學習中的記錄能夠對你有益。