大家好。我們知道，mysql中存在許多的統(tǒng)計數(shù)據(jù)，比如通過SHOW TABLE STATUS 可以看到關(guān)于表的統(tǒng)計數(shù)據(jù)，通過SHOW INDEX可以看到關(guān)于索引的統(tǒng)計數(shù)據(jù)，那么這些統(tǒng)計數(shù)據(jù)是怎么來的呢？它們是以什么方式收集的呢？今天我們來說說InnoDB 存儲引擎的統(tǒng)計數(shù)據(jù)收集策略。

一、統(tǒng)計數(shù)據(jù)的存儲方式

InnoDB 提供了兩種存儲統(tǒng)計數(shù)據(jù)的方式：

永久性統(tǒng)計數(shù)據(jù)： 這種統(tǒng)計數(shù)據(jù)存儲在磁盤上，服務(wù)器重啟之后這些統(tǒng)計數(shù)據(jù)還在。

非永久性統(tǒng)計數(shù)據(jù)： 這種統(tǒng)計數(shù)據(jù)存儲在內(nèi)存中，當(dāng)服務(wù)器關(guān)閉時這些統(tǒng)計數(shù)據(jù)就會被清除掉，等到服務(wù)器重啟之后，在某些場景下會重新收集這些統(tǒng)計數(shù)據(jù)。

MySQL 通過系統(tǒng)變量innodb_stats_persistent來控制到底采用哪種方式去存儲統(tǒng)計數(shù)據(jù)。在MySQL 5.6.6之前，innodb_stats_persistent 的值默認(rèn)是OFF ，也就是說 nnoDB 的統(tǒng)計數(shù)據(jù)默認(rèn)是存儲到內(nèi)存的，之后的版本中innodb_stats_persistent的值默認(rèn)是ON ，也就是統(tǒng)計數(shù)據(jù)默認(rèn)被存儲到磁盤中。

InnoDB 默認(rèn)是以表為單位來收集和存儲統(tǒng)計數(shù)據(jù)的，所以我們可以把某些表的統(tǒng)計數(shù)據(jù)存儲在磁盤上，把另一些表的統(tǒng)計數(shù)據(jù)存儲在內(nèi)存中。我們可以在創(chuàng)建和修改表的時候通過指定STATS_PERSISTENT屬性來指明該表的統(tǒng)計數(shù)據(jù)存儲方式：

CREATE TABLE 表名 (...) Engine=InnoDB, STATS_PERSISTENT = (1|0);
ALTER TABLE 表名 Engine=InnoDB, STATS_PERSISTENT = (1|0);

當(dāng)STATS_PERSISTENT=1時，該表的統(tǒng)計數(shù)據(jù)存儲到磁盤上，當(dāng) STATS_PERSISTENT=0時，該表的統(tǒng)計數(shù)據(jù)臨時的存儲到內(nèi)存中。如果我們在創(chuàng)建表時未指定STATS_PERSISTENT屬性，那默認(rèn)采用系統(tǒng)變量innodb_stats_persistent 的值作為該屬性的值。

二、永久性統(tǒng)計數(shù)據(jù)

永久性統(tǒng)計數(shù)據(jù)存放到磁盤上時，實際上是存儲到下面這兩個表里：

可以看到，這兩個表都位于mysql 系統(tǒng)數(shù)據(jù)庫下邊，其中：

innodb_table_stats 存儲了關(guān)于表的統(tǒng)計數(shù)據(jù)，每一條記錄對應(yīng)著一個表的統(tǒng)計數(shù)據(jù)。

innodb_index_stats 存儲了關(guān)于索引的統(tǒng)計數(shù)據(jù)，每一條記錄對應(yīng)著一個索引的一個統(tǒng)計項的統(tǒng)計數(shù)據(jù)。

下面我們看一下這兩個表里邊都有什么以及表里的數(shù)據(jù)是如何生成的。

1、innodb_table_stats

上圖就是innodb_table_stats表里存放的數(shù)據(jù)，下面我們以single_table 表的統(tǒng)計信息為例介紹一下幾個重要統(tǒng)計信息項：

n_rows 的值是9446，表明single_table表中大約有9446條記錄，注意這個數(shù)據(jù)是估計值（實際是10000條）。

clustered_index_size 的值是97，表明single_table表的聚簇索引占用97個頁面，這個值是也是一個估計值。

sum_of_other_index_sizes 的值是68，表明single_table表的其他索引一共占用68個頁面，這個值是也是一個估計值。

1. n_rows統(tǒng)計項的收集

InnoDB 統(tǒng)計一個表中有多少行記錄的方式是這樣的：按照一定算法選取幾個葉子節(jié)點頁面，計算每個頁面中主鍵值記錄數(shù)量，然后計算平均一個頁面中主鍵值的記錄數(shù)量乘以全部葉子節(jié)點的數(shù)量就算是該表的n_rows值。所以n_rows值是一個估計值，并不是真正的記錄數(shù)。

可以看出來這個n_rows 值精確與否取決于統(tǒng)計時采樣的頁面數(shù)量，MySQL通過一個名為innodb_stats_persistent_sample_pages的系統(tǒng)變量來控制使用永久性統(tǒng)計數(shù)據(jù)時計算統(tǒng)計數(shù)據(jù)時采樣的頁面數(shù)量。該值設(shè)置的越大，統(tǒng)計出的n_rows值越精確，但是統(tǒng)計耗時也就最久；該值設(shè)置的越小，統(tǒng)計出的n_rows值越不精確，但是統(tǒng)計耗時特別少。該系統(tǒng)變量的默認(rèn)值是20。

我們也可以單獨設(shè)置某個表的采樣頁面的數(shù)量，設(shè)置方式就是在創(chuàng)建或修改表的時候通過指定STATS_SAMPLE_PAGES屬性來指明該表的統(tǒng)計 數(shù)據(jù)存儲方式：

CREATE TABLE 表名 (...) Engine=InnoDB, STATS_SAMPLE_PAGES = 具體的采樣頁面數(shù)量;
ALTER TABLE 表名 Engine=InnoDB, STATS_SAMPLE_PAGES = 具體的采樣頁面數(shù)量;

如果我們在創(chuàng)建表的語句中并沒有指定STATS_SAMPLE_PAGES 屬性的話，將默認(rèn)使用系統(tǒng)變量 innodb_stats_persistent_sample_pages的值作為該屬性的值。

2. clustered_index_size和sum_of_other_index_sizes統(tǒng)計項的收集

這兩個統(tǒng)計項的收集過程如下：

1. 從數(shù)據(jù)字典里找到表的各個索引對應(yīng)的根頁面位置。系統(tǒng)表SYS_INDEXES里存儲了各個索引對應(yīng)的根頁面信息。

2. 從根頁面的Page Header里找到葉子節(jié)點段和非葉子節(jié)點段對應(yīng)的 Segment Header。在每個索引的根頁面的Page Header部分都有兩個字段：

PAGE_BTR_SEG_LEAF： 表示B+樹葉子段的Segment Header信息。

PAGE_BTR_SEG_TOP： 表示B+樹非葉子段的Segment Header信息。

下面是Segment Header結(jié)構(gòu)示意圖：

3. 從葉子節(jié)點段和非葉子節(jié)點段的Segment Header中找到這兩個段對應(yīng)的INODE Entry結(jié)構(gòu)。 下面是INODE Entry結(jié)構(gòu)示意圖：

4. 從對應(yīng)的INODE Entry結(jié)構(gòu)中可以找到該段對應(yīng)所有零散的頁面地址以及FREE 、NOT_FULL 、FULL鏈表的基節(jié)點。下面是鏈表的基節(jié)點結(jié)構(gòu)示意圖：

5. 直接統(tǒng)計零散的頁面有多少個，然后從FREE 、NOT_FULL 、FULL三個鏈表的List Length字段中讀出該段占用的區(qū)的大小，每個區(qū)占用64 個頁，所以就可以統(tǒng)計出整個段占用的頁面。

通過上述5個步驟可以統(tǒng)計出索引的某個段占用的頁面數(shù)量。分別計算聚簇索引的葉子節(jié)點段和非葉子節(jié)點段占用的頁面數(shù)，它們的和就是clustered_index_size的值，按照同樣的套路把其余索引占用的頁面數(shù)都算出來，加起來之后就是sum_of_other_index_sizes的值。

注意：一個段的數(shù)據(jù)在非常多時（超過32個頁面），會以區(qū)為單位來申請空間，以區(qū)為單位申請空間中有一些頁可能并沒有使用，但是在統(tǒng)計clustered_index_size和sum_of_other_index_sizes時都把它們算進(jìn)去了，所以說聚簇索引和其他的索引占用的頁面數(shù)可能比這兩個值要小。

2、innodb_index_stats

我們依舊以single_table表為例，上圖是single_table表在innodb_index_stats中存儲的記錄信息。下面我們來看一下如何查看這些信息：

1. 先查看index_name列，這個列說明該記錄是哪個索引的統(tǒng)計信息。我們可以看出來，PRIMARY索引（也就是主鍵）占了3條記錄，idx_key_part索引占了6條記錄。
2. 針對index_name列相同的記錄， stat_name表示針對該索引的統(tǒng)計項名稱， stat_value 展示的是該索引在該統(tǒng)計項上的值，stat_description指的是來描述該統(tǒng)計項的含義的。
   我們來具體看一下一個索引都有哪些統(tǒng)計項：
   n_leaf_pages：表示該索引的葉子節(jié)點占用多少頁面。
   size：表示該索引共占用多少頁面。
   n_diff_pfx NN：表示對應(yīng)的索引列不重復(fù)的值有多少。

這里的NN 可以被替換為01、02、03… 這樣的數(shù)字。比如對于 idx_key_part 來說：
n_diff_pfx01表示的是統(tǒng)計key_part1這單單一個列不重復(fù)的值有多少。
n_diff_pfx02表示的是統(tǒng)計key_part1、key_part2這兩個列組合起來不重復(fù)的值有多少。
n_diff_pfx03 表示的是統(tǒng)計key_part1、key_part2、key_part3 這三個列組合起來不重復(fù)的值有多少。
n_diff_pfx04 表示的是統(tǒng)計key_part1、key_part2、key_part3、id 這四個列組合起來不重復(fù)的值有多少。

注意：對于普通的二級索引，并不能保證它的索引列值是唯一的，此時只有在索引列上加上主鍵值才可以區(qū)分兩條索引列值都一樣的二級索引記錄。

3. 在計算某些索引列中包含多少不重復(fù)值時，需要對一些葉子節(jié)點頁面進(jìn)行采樣，sample_size列就表明了采樣的頁面數(shù)量是多少。

對于有多個列的聯(lián)合索引來說，采樣的頁面數(shù)量是：innodb_stats_persistent_sample_pages × 索引列的個數(shù)。當(dāng)需要采樣的頁面數(shù)量大于該索引的葉子節(jié)點數(shù)量的話，就直接采用全表掃描來統(tǒng)計索引列的不重復(fù)值數(shù)量了。所以不同索引對應(yīng)的sample_size列的值可能是不同的。

3、定期更新統(tǒng)計數(shù)據(jù)

隨著我們不斷的對表進(jìn)行增刪改操作，表中的數(shù)據(jù)也一直在變化，這時innodb_table_stats和innodb_index_stats表里的統(tǒng)計數(shù)據(jù)也會跟著變。MySQL提供了兩種更新統(tǒng)計數(shù)據(jù)的方式：

開啟innodb_stats_auto_recalc： 系統(tǒng)變量innodb_stats_auto_recalc決定著服務(wù)器是否自動重新計算統(tǒng)計數(shù)據(jù)，它的默認(rèn)值是ON（開啟）。每個表都維護(hù)了一個變量，該變量記錄著對該表進(jìn)行增刪改的記錄條數(shù)，如果發(fā)生變動的記錄數(shù)量超過了表大小的10%，并且自動重新計算統(tǒng)計數(shù)據(jù)的功能是打開的，那么服務(wù)器會重新進(jìn)行一次統(tǒng)計數(shù)據(jù)的計算，并且更新innodb_table_stats 和 innodb_index_stats 表。不過自動重新計算統(tǒng)計數(shù)據(jù) 的過程是異步發(fā)生的，也就是即使表中變動的記錄數(shù)超過了10%，自動重新計算統(tǒng)計數(shù)據(jù)也不會立即發(fā)生， 可能會延遲幾秒才會進(jìn)行計算。

InnoDB 默認(rèn)是以表為單位來收集和存儲統(tǒng)計數(shù)據(jù)的，我們可以單獨為某個表設(shè)置是否自動重新計算統(tǒng)計數(shù)的屬性，設(shè)置方式就是在創(chuàng)建或修改表的時候通過指定STATS_AUTO_RECALC屬性來指明該表的統(tǒng)計數(shù)據(jù)存儲方式：

CREATE TABLE 表名 (...) Engine=InnoDB, STATS_AUTO_RECALC = (1|0);
ALTER TABLE 表名 Engine=InnoDB, STATS_AUTO_RECALC = (1|0);

當(dāng)STATS_AUTO_RECALC=1時，表明我們想讓該表自動重新計算統(tǒng)計數(shù)據(jù)，當(dāng)STATS_PERSISTENT=0時，表明不想讓該表自動重新計算統(tǒng)計數(shù)據(jù)。如果我們在創(chuàng)建表時未指定STATS_AUTO_RECALC屬性，那默認(rèn)采用系統(tǒng) 變量innodb_stats_auto_recalc 的值作為該屬性的值。

手動調(diào)用ANALYZE TABLE語句來更新統(tǒng)計信息： 如果innodb_stats_auto_recalc系統(tǒng)變量的值為OFF，我們也可以手動調(diào)用 ANALYZE TABLE語句來重新計算統(tǒng)計數(shù)據(jù)，ANALYZE TABLE語句會立即重新計算統(tǒng)計數(shù)據(jù)，也就是這個過程是同步的。

4、 手動更新innodb_table_stats和innodb_index_stats表

其實innodb_table_stats和innodb_index_stats表就相當(dāng)于一個普通的表一樣，我們能對它們做增刪改查操作。這也就意味著我們可以手動更新某個表或者索引的統(tǒng)計數(shù)據(jù)。比如說我們想把single_table 表關(guān)于行數(shù)的 統(tǒng)計數(shù)據(jù)更改一下可以這么做：

步驟一：更新innodb_table_stats 表。

UPDATE innodb_table_stats SET n_rows = 1 WHERE table_name = 'single_table

步驟二：讓MySQL 查詢優(yōu)化器重新加載我們更改過的數(shù)據(jù)。

更新完innodb_table_stats只是單純的修改了一個表的數(shù)據(jù)，需要運行下邊的命令讓MySQL查詢優(yōu)化器重新加載我們更改過的數(shù)據(jù)：

FLUSH TABLE single_table;

三、非永久性統(tǒng)計數(shù)據(jù)

當(dāng)我們把系統(tǒng)變量innodb_stats_persistent的值設(shè)置為OFF時，創(chuàng)建的表的統(tǒng)計數(shù)據(jù)默認(rèn)就都是非永久性的了，或者我們直接在創(chuàng)建表或修改表時設(shè)置STATS_PERSISTENT屬性的值為0，那么該表的統(tǒng)計數(shù)據(jù)就是非永久性的了。

與永久性的統(tǒng)計數(shù)據(jù)不同，非永久性的統(tǒng)計數(shù)據(jù)采樣的頁面數(shù)量是由innodb_stats_transient_sample_pages控制的，這個系統(tǒng)變量的默認(rèn)值是8。

最近的MySQL版本都不怎么使用這種基于內(nèi)存的非永久性統(tǒng)計數(shù)據(jù)這里我們了解即可。

四、innodb_stats_method的使用

我們知道，索引列不重復(fù)的值的數(shù)量對于MySQL查詢優(yōu)化器十分重要，因為通過它可以計算出在索引列中平均一個值重復(fù)多少行，它的應(yīng)用場景主要有兩個：

1. 單表查詢中單點區(qū)間太多

SELECT * FROM tbl_name WHERE key IN ('xx1', 'xx2', ..., 'xxn');

當(dāng)上述sql中IN里的參數(shù)數(shù)量過多時，采用index dive的方式直接訪問 B+樹索引去統(tǒng)計每個單點區(qū)間對應(yīng)的記錄的數(shù)量就太耗費性能了，所以直接依賴統(tǒng)計數(shù)據(jù)中的平均一個值重復(fù)多少行來計算單點區(qū)間對應(yīng)的記錄數(shù)量。

2. 連接查詢時，如果有涉及兩個表的等值匹配連接條件，該連接條件對應(yīng)的被驅(qū)動表中的列又擁有索引時，則可以使用ref 訪問方法來對被驅(qū)動表進(jìn)行查詢

SELECT * FROM t1 JOIN t2 ON t1.column = t2.key WHERE ...;

在真正執(zhí)行對t2表的查詢前，t1.comumn 的值是不確定的，所以也不能通過index dive的方式直接訪問B+樹索引去統(tǒng)計每個單點區(qū)間對應(yīng)的記錄的數(shù)量，只能依賴統(tǒng)計數(shù)據(jù)中的平均一個值重復(fù)多少行來計算單點區(qū)間對應(yīng)的記錄數(shù)量。

下面我們思考一下：在統(tǒng)計索引列不重復(fù)的值的數(shù)量時，如果索引列中出現(xiàn)NULL值怎么辦？例如下面這列：

此時計算這個col 列中不重復(fù)的值的數(shù)量就有下邊的分歧：

1. 有的人認(rèn)為NULL值代表一個未確定的值，在統(tǒng)計索引列不重復(fù)的值的數(shù)量時，應(yīng)該把NULL值當(dāng)作 一個獨立的值，所以col列的不重復(fù)的值的數(shù)量就是：4（分別是1、2、NULL、NULL這四個值）。
2. 有的人認(rèn)為其實NULL值在業(yè)務(wù)上就是代表沒有，所有的NULL值代表的意義是一樣的，所以col列不重復(fù)的值的數(shù)量就是：3（分別是1、2、NULL這三個值）。
3. 有的人認(rèn)為這NULL 完全沒有意義，所以在統(tǒng)計索引列不重復(fù)的值的數(shù)量時壓根兒不能把它們算進(jìn)來，所以col 列不重復(fù)的值的數(shù)量就是：2（分別是1、2這兩個值）。

MySQL提供了一個名為innodb_stats_method 的系統(tǒng)變量，這個系統(tǒng)變量有三個候選值：

nulls_equal： 認(rèn)為所有NULL值都是相等的。這個值也是 innodb_stats_method的默認(rèn)值。如果某個索引列中NULL 值特別多的話，這種統(tǒng)計方式會讓優(yōu)化器認(rèn)為某個列中平均一個值重復(fù)次數(shù)特別多，所以傾向于不使用索引進(jìn)行訪問。

nulls_unequal： 認(rèn)為所有 NULL 值都是不相等的。如果某個索引列中NULL 值特別多的話，這種統(tǒng)計方式會讓優(yōu)化器認(rèn)為某個列中平均一個值重復(fù)次數(shù)特別少，所以傾向于使用索引進(jìn)行訪問。

nulls_ignored： 直接把 NULL 值忽略掉。

好了，到這里我們就講完了，今天主要講了InnoDB的統(tǒng)計數(shù)據(jù)是如何產(chǎn)生的，大家有什么想法歡迎留言討論。也希望大家能給作者點個關(guān)注，謝謝大家！最后依舊是請各位老板有錢的捧個人場，沒錢的也捧個人場，謝謝各位老板！