一、兩種不同的統(tǒng)計(jì)數(shù)據(jù)存儲方式

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

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

• 非永久性的統(tǒng)計(jì)數(shù)據(jù)：這種統(tǒng)計(jì)數(shù)據(jù)存儲在內(nèi)存中，當(dāng)服務(wù)器關(guān)閉時(shí)這些這些統(tǒng)計(jì)數(shù)據(jù)就都被清除掉了，等到服務(wù)器重啟之后，在某些適當(dāng)?shù)膱鼍跋虏艜?huì)重新收集這些統(tǒng)計(jì)數(shù)據(jù)

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

不過InnoDB默認(rèn)是以表為單位來收集和存儲統(tǒng)計(jì)數(shù)據(jù)的，也就是說我們可以把某些表的統(tǒng)計(jì)數(shù)據(jù)（以及該表的索引統(tǒng)計(jì)數(shù)據(jù)）存儲在磁盤上，把另一些表的統(tǒng)計(jì)數(shù)據(jù)存儲在內(nèi)存中。怎么做到的呢？我們可以在創(chuàng)建和修改表的時(shí)候通過指定stats_persistent屬性來指明該表的統(tǒng)計(jì)數(shù)據(jù)存儲方式：

create table 表名 (...) engine=innodb, stats_persistent = (1|0);
alter table 表名 engine=innodb, stats_persistent = (1|0);

• 當(dāng)stats_persistent=1時(shí)，表明我們想把該表的統(tǒng)計(jì)數(shù)據(jù)永久的存儲到磁盤上
• 當(dāng)stats_persistent=0時(shí)，表明我們想把該表的統(tǒng)計(jì)數(shù)據(jù)臨時(shí)的存儲到內(nèi)存中

需要注意的是如果我們在創(chuàng)建表時(shí)未指定stats_persistent屬性，那默認(rèn)采用系統(tǒng)變量innodb_stats_persistent的值作為該屬性的值

mysql> show variables like 'innodb_stats_persistent';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_stats_persistent | ON    |
+-------------------------+-------+
1 row in set (0.01 sec)
mysql> set persist innodb_stats_persistent =1;
Query OK, 0 rows affected (0.01 sec)

二、基于磁盤的永久性統(tǒng)計(jì)數(shù)據(jù)

當(dāng)我們選擇把某個(gè)表以及該表索引的統(tǒng)計(jì)數(shù)據(jù)存放到磁盤上時(shí)，實(shí)際上是把這些統(tǒng)計(jì)數(shù)據(jù)存儲到了兩個(gè)表里：

mysql> show tables from mysql like 'innodb%';
+---------------------------+
| Tables_in_mysql (innodb%) |
+---------------------------+
| innodb_index_stats        |
| innodb_table_stats        |
+---------------------------+
2 rows in set (0.03 sec)

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

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

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

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

2.1 innodb_table_stats

這里直接看一下innodb_table_stats表中的各個(gè)列都是干嘛的：

注意這個(gè)表的主鍵是(database_name,table_name)，也就是innodb_table_stats表的每條記錄代表著一個(gè)表的統(tǒng)計(jì)信息。我們直接看一下這個(gè)表里的內(nèi)容：

mysql> select * from mysql.innodb_table_stats where database_name= 'testdb' limit 2;
+---------------+---------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name    | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+---------------+---------------------+--------+----------------------+--------------------------+
| testdb        | demo1         | 2023-05-06 18:20:56 |      1 |                    1 |                        0 |
| testdb        | demo8         | 2023-05-16 16:38:16 |  18758 |                   97 |                      170 |
+---------------+---------------+---------------------+--------+----------------------+--------------------------+
2 rows in set (0.01 sec)

可以看到我們熟悉的demo8表的統(tǒng)計(jì)信息就對應(yīng)著mysql.innodb_table_stats的第二條記錄。幾個(gè)重要統(tǒng)計(jì)信息項(xiàng)的值如下：

• n_rows的值是18758，表明demo8表中大約有18758條記錄，注意這個(gè)數(shù)據(jù)是估計(jì)值
• clustered_index_size的值是97，表明demo8表的聚簇索引占用97個(gè)頁面，這個(gè)值是也是一個(gè)估計(jì)值
• sum_of_other_index_sizes的值是170，表明demo8表的其他索引一共占用170個(gè)頁面，這個(gè)值是也是一個(gè)估計(jì)值

2.1.1 n_rows 統(tǒng)計(jì)項(xiàng)的收集

為啥一直強(qiáng)調(diào)n_rows這個(gè)統(tǒng)計(jì)項(xiàng)的值是估計(jì)值呢？現(xiàn)在就來揭曉答案。InnoDB統(tǒng)計(jì)一個(gè)表中有多少條記錄是這樣的：

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

小提示：真實(shí)的計(jì)算過程比這個(gè)稍微復(fù)雜一些，不過大致上就是這樣的意思

可以看出來這個(gè)n_rows值精確與否取決于統(tǒng)計(jì)時(shí)采樣的頁面數(shù)量，MySQL為我們準(zhǔn)備了一個(gè)名為innodb_stats_persistent_sample_pages的系統(tǒng)變量來控制使用永久性的統(tǒng)計(jì)數(shù)據(jù)時(shí)，計(jì)算統(tǒng)計(jì)數(shù)據(jù)時(shí)采樣的頁面數(shù)量。

• 該值設(shè)置的越大，統(tǒng)計(jì)出的n_rows值越精確，但是統(tǒng)計(jì)耗時(shí)也就最久
• 該值設(shè)置的越小，統(tǒng)計(jì)出的n_rows值越不精確，但是統(tǒng)計(jì)耗時(shí)特別少。

所以在實(shí)際使用是需要我們?nèi)?quán)衡利弊，該系統(tǒng)變量的默認(rèn)值是20。

mysql> show variables like 'innodb_stats_persistent_sample_pages';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| innodb_stats_persistent_sample_pages | 20    |
+--------------------------------------+-------+
1 row in set (0.01 sec)
mysql> set persist innodb_stats_persistent_sample_pages =20;
Query OK, 0 rows affected (0.02 sec)

我們前邊說過，不過InnoDB默認(rèn)是以表為單位來收集和存儲統(tǒng)計(jì)數(shù)據(jù)的，我們也可以單獨(dú)設(shè)置某個(gè)表的采樣頁面的數(shù)量，設(shè)置方式就是在創(chuàng)建或修改表的時(shí)候通過指定stats_sample_pages 屬性來指明該表的統(tǒng)計(jì)數(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.1.2 clustered_index_size 和 sum_of_other_index_sizes統(tǒng)計(jì)項(xiàng)的收集

統(tǒng)計(jì)這兩個(gè)數(shù)據(jù)需要大量用到我們之前學(xué)習(xí)的InnoDB表空間的知識，如果大家壓根就沒有看那一章，那下邊的計(jì)算過程大家還是不要看了（看也看不懂）；如果看過了，那大家就會(huì)發(fā)現(xiàn)InnoDB表空間的知識真是有用。

這兩個(gè)統(tǒng)計(jì)項(xiàng)的收集過程如下：

步驟一： 從數(shù)據(jù)字典中找到表的各個(gè)索引對應(yīng)的根頁面位置(系統(tǒng)表sys_indexes里存儲了各個(gè)索引對應(yīng)的根頁面信息)

步驟二： 從根頁面的Page Header里找到葉子節(jié)點(diǎn)段和非葉子節(jié)點(diǎn)段對應(yīng)的Segment Header。在每個(gè)索引的根??的Page Header部分都有兩個(gè)字段：

• Page_btr_seg_leaf：表示B+樹葉?段的Segment Header信息
• Page_btr_seg_top：表示B+樹?葉?段的Segment Header信息

步驟三： 從葉子節(jié)點(diǎn)段和非葉子節(jié)點(diǎn)段的Segment Header中找到這兩個(gè)段對應(yīng)的INODE Entry結(jié)構(gòu)。

Segment Header結(jié)構(gòu)如下：

從對應(yīng)的INODE Entry結(jié)構(gòu)中可以找到該段對應(yīng)所有零散的頁面地址以及free、not_full、full鏈表的基節(jié)點(diǎn)。

INODE Entry結(jié)構(gòu)如下：

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

這個(gè)是鏈表基節(jié)點(diǎn)的示意圖：

步驟四： 分別計(jì)算聚簇索引的葉子結(jié)點(diǎn)段和非葉子節(jié)點(diǎn)段占用的頁面數(shù)，它們的和就是clustered_index_size的值，按照同樣的套路把其余索引占用的頁面數(shù)都算出來，加起來之后就是sum_of_other_index_sizes的值

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

2.2 innodb_index_stats

直接看一下這個(gè)innodb_index_stats表中的各個(gè)列都是干嘛的：

注意這個(gè)表的主鍵是(database_name,table_name,index_name,stat_name)，其中的stat_name是指統(tǒng)計(jì)項(xiàng)的名稱，也就是說innodb_index_stats表的每條記錄代表著一個(gè)索引的一個(gè)統(tǒng)計(jì)項(xiàng)?？赡苓@會(huì)大家有些懵逼這個(gè)統(tǒng)計(jì)項(xiàng)到底指什么，別著急，我們直接看一下關(guān)于demo8表的索引統(tǒng)計(jì)數(shù)據(jù)都有些什么：

mysql> select * from mysql.innodb_index_stats where table_name='demo8';
+---------------+------------+--------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name   | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+--------------+---------------------+--------------+------------+-------------+-----------------------------------+
| testdb        | demo8      | PRIMARY      | 2023-05-16 16:38:16 | n_diff_pfx01 |      18750 |          20 | id                                |
| testdb        | demo8      | PRIMARY      | 2023-05-16 16:38:16 | n_leaf_pages |         75 |        NULL | Number of leaf pages in the index |
| testdb        | demo8      | PRIMARY      | 2023-05-16 16:38:16 | size         |         97 |        NULL | Number of pages in the index      |
| testdb        | demo8      | idx_key1     | 2023-05-16 16:38:16 | n_diff_pfx01 |        256 |          22 | key1                              |
| testdb        | demo8      | idx_key1     | 2023-05-16 16:38:16 | n_diff_pfx02 |      18567 |          22 | key1,id                           |
| testdb        | demo8      | idx_key1     | 2023-05-16 16:38:16 | n_leaf_pages |         22 |        NULL | Number of leaf pages in the index |
| testdb        | demo8      | idx_key1     | 2023-05-16 16:38:16 | size         |         23 |        NULL | Number of pages in the index      |
| testdb        | demo8      | idx_key2     | 2023-05-16 16:38:16 | n_diff_pfx01 |      18565 |          18 | key2                              |
| testdb        | demo8      | idx_key2     | 2023-05-16 16:38:16 | n_leaf_pages |         18 |        NULL | Number of leaf pages in the index |
| testdb        | demo8      | idx_key2     | 2023-05-16 16:38:16 | size         |         19 |        NULL | Number of pages in the index      |
| testdb        | demo8      | idx_key3     | 2023-05-16 16:38:16 | n_diff_pfx01 |       4053 |          30 | key3                              |
| testdb        | demo8      | idx_key3     | 2023-05-16 16:38:16 | n_diff_pfx02 |      18568 |          30 | key3,id                           |
| testdb        | demo8      | idx_key3     | 2023-05-16 16:38:16 | n_leaf_pages |         30 |        NULL | Number of leaf pages in the index |
| testdb        | demo8      | idx_key3     | 2023-05-16 16:38:16 | size         |         31 |        NULL | Number of pages in the index      |
| testdb        | demo8      | idx_key_part | 2023-05-16 16:38:16 | n_diff_pfx01 |      16122 |          50 | key_part1                         |
| testdb        | demo8      | idx_key_part | 2023-05-16 16:38:16 | n_diff_pfx02 |      18570 |          50 | key_part1,key_part2               |
| testdb        | demo8      | idx_key_part | 2023-05-16 16:38:16 | n_diff_pfx03 |      18570 |          50 | key_part1,key_part2,key_part3     |
| testdb        | demo8      | idx_key_part | 2023-05-16 16:38:16 | n_diff_pfx04 |      18570 |          50 | key_part1,key_part2,key_part3,id  |
| testdb        | demo8      | idx_key_part | 2023-05-16 16:38:16 | n_leaf_pages |         50 |        NULL | Number of leaf pages in the index |
| testdb        | demo8      | idx_key_part | 2023-05-16 16:38:16 | size         |         97 |        NULL | Number of pages in the index      |
+---------------+------------+--------------+---------------------+--------------+------------+-------------+-----------------------------------+
20 rows in set (0.03 sec)

這個(gè)結(jié)果有點(diǎn)多，正確查看這個(gè)結(jié)果的方式是這樣的：

• 先查看index_name列，這個(gè)列說明該記錄是哪個(gè)索引的統(tǒng)計(jì)信息，從結(jié)果中我們可以看出來，PRIMARY索引（也就是主鍵）占了3條記錄，idx_key_part索引占了6條記錄。
• 針對index_name列相同的記錄，stat_name表示針對該索引的統(tǒng)計(jì)項(xiàng)名稱，stat_value展示的是該索引在該統(tǒng)計(jì)項(xiàng)上的值，stat_description指的是來描述該統(tǒng)計(jì)項(xiàng)的含義的。我們來具體看一下一個(gè)索引都有哪些統(tǒng)計(jì)項(xiàng)：

  • n_leaf_pages：表示該索引的葉子節(jié)點(diǎn)占用多少頁面

  • size：表示該索引共占用多少頁面

  •  n_diff_pfxNN：表示對應(yīng)的索引列不重復(fù)的值有多少。其中的NN長得有點(diǎn)?怪呀，啥意思呢？ 其實(shí)NN可以被替換為01、02、03… 這樣的數(shù)字。比如對于idx_key_part來說：
    • n_diff_pfx01表示的是統(tǒng)計(jì)key_part1這單單一個(gè)列不重復(fù)的值有多少
    • n_diff_pfx02表示的是統(tǒng)計(jì)key_part1、key_part2這兩個(gè)列組合起來不重復(fù)的值有多少
    • n_diff_pfx03表示的是統(tǒng)計(jì)key_part1、key_part2、key_part3這三個(gè)列組合起來不重復(fù)的值有多少
    • n_diff_pfx04表示的是統(tǒng)計(jì)key_part1、key_part2、key_part3、id這四個(gè)列組合起來不重復(fù)的值有多少

小提示：
這里需要注意的是，對于普通的二級索引，并不能保證它的索引列值是唯一的，比如對于idx_key1來說，key1列就可能有很多值重復(fù)的記錄。此時(shí)只有在索引列上加上主鍵值才可以區(qū)分兩條索引列值都一樣的二級索引記錄。對于主鍵和唯一二級索引則沒有這個(gè)問題，它們本身就可以保證索引列值的不重復(fù)，所以也不需要再統(tǒng)計(jì)一遍在索引列后加上主鍵值的不重復(fù)值有多少。比如上邊的idx_key1有n_diff_pfx01、n_diff_pfx02兩個(gè)統(tǒng)計(jì)項(xiàng)，而idx_key2卻只有n_diff_pfx01一個(gè)統(tǒng)計(jì)項(xiàng)。

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

小提示：
對于有多個(gè)列的聯(lián)合索引來說，采樣的頁面數(shù)量是：innodb_stats_persistent_sample_pages × 索引列的個(gè)數(shù)。當(dāng)需要采樣的頁面數(shù)量大于該索引的葉子節(jié)點(diǎn)數(shù)量的話，就直接采用全表掃描來統(tǒng)計(jì)索引列的不重復(fù)值數(shù)量了。所以大家可以在查詢結(jié)果中看到不同索引對應(yīng)的size列的值可能是不同的。

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

隨著我們不斷的對表進(jìn)行增刪改操作，表中的數(shù)據(jù)也一直在變化，innodb_table_stats和innodb_index_stats表立的統(tǒng)計(jì)數(shù)據(jù)是不是也應(yīng)該跟著變一變了？當(dāng)然要變了，不變的話MySQL查詢優(yōu)化器計(jì)算的成本可就差老遠(yuǎn)了。MySQL的提供了如下兩種更新統(tǒng)計(jì)數(shù)據(jù)的方式：

• 開啟innodb_stats_auto_recalc

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

mysql> show variables like 'innodb_stats_auto_recalc';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_stats_auto_recalc | ON    |
+--------------------------+-------+
1 row in set (0.00 sec)
mysql> set persist innodb_stats_auto_recalc =1;
Query OK, 0 rows affected (0.00 sec)

再一次強(qiáng)調(diào)，InnoDB默認(rèn)是以表為單位來收集和存儲統(tǒng)計(jì)數(shù)據(jù)的，我們也可以單獨(dú)為某個(gè)表設(shè)置是否自動(dòng)重新計(jì)算統(tǒng)計(jì)數(shù)的屬性，設(shè)置方式就是在創(chuàng)建或修改表的時(shí)候通過指定stats_auto_recalc屬性來指明該表的統(tǒng)計(jì)數(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時(shí)，表明我們想讓該表自動(dòng)重新計(jì)算統(tǒng)計(jì)數(shù)據(jù)當(dāng)stats_persistent=0時(shí)，表明不想讓該表自動(dòng)重新計(jì)算統(tǒng)計(jì)數(shù)據(jù)

如果我們在創(chuàng)建表時(shí)未指定stats_auto_recalc屬性，那默認(rèn)采用系統(tǒng)變量innodb_stats_auto_recalc的值作為該屬性的值。

• 手動(dòng)調(diào)用analyze table語句來更新統(tǒng)計(jì)信息

  如果innodb_stats_auto_recalc系統(tǒng)變量的值為OFF的話，我們也可以手動(dòng)調(diào)用analyze table語句來重新計(jì)算統(tǒng)計(jì)數(shù)據(jù)，比如我們可以這樣更新關(guān)于demo8表的統(tǒng)計(jì)數(shù)據(jù)：

mysql> analyze table demo8;

需要注意的是，analyze table語句會(huì)立即重新計(jì)算統(tǒng)計(jì)數(shù)據(jù)，也就是這個(gè)過程是同步的，在表中索引多或者采樣頁面特別多時(shí)這個(gè)過程可能會(huì)特別慢，請不要沒事就運(yùn)行一下analyze table語句，最好在業(yè)務(wù)不是很繁忙的時(shí)候再運(yùn)行。

2.4 手動(dòng)動(dòng)更新innodb_table_stats 和 innodb_index_stats表

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

步驟一： 更新demo8表

update innodb_table_stats set n_rows = 1 where table_name = 'demo8';

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

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

flush table demo8;

之后我們使用show table status語句查看表的統(tǒng)計(jì)數(shù)據(jù)時(shí)就看到Rows行變?yōu)榱?。

三、基于內(nèi)存的非永久性統(tǒng)計(jì)數(shù)據(jù)

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

與永久性的統(tǒng)計(jì)數(shù)據(jù)不同，非永久性的統(tǒng)計(jì)數(shù)據(jù)采樣的頁面數(shù)量是由innodb_stats_transient_sample_pages控制的，這個(gè)系統(tǒng)變量的默認(rèn)值是8。另外，由于非永久性的統(tǒng)計(jì)數(shù)據(jù)經(jīng)常更新，所以導(dǎo)致MySQL查詢優(yōu)化器計(jì)算查詢成本的時(shí)候依賴的是經(jīng)常變化的統(tǒng)計(jì)數(shù)據(jù)，也就會(huì)生成經(jīng)常變化的執(zhí)行計(jì)劃，這個(gè)可能讓大家有些懵逼。不過最近的MySQL版本都不咋用這種基于內(nèi)存的非永久性統(tǒng)計(jì)數(shù)據(jù)了，所以我們也就不深入了解了。

mysql> show variables like 'innodb_stats_transient_sample_pages';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| innodb_stats_transient_sample_pages | 8     |
+-------------------------------------+-------+
1 row in set (0.01 sec)
mysql> set persist innodb_stats_transient_sample_pages =1;
Query OK, 0 rows affected (0.00 sec)

四、innodb_stats_method 的使用

我們知道索引列不重復(fù)的值的數(shù)量這個(gè)統(tǒng)計(jì)數(shù)據(jù)對于MySQL查詢優(yōu)化器十分重要，因?yàn)橥ㄟ^它可以計(jì)算出在索引列中平均一個(gè)值重復(fù)多少行，它的應(yīng)用場景主要有兩個(gè)：

• 單表查詢中單點(diǎn)區(qū)間太多，比如說這樣： 當(dāng)IN里的參數(shù)數(shù)量過多時(shí)，采用index dive的方式直接訪問B+樹索引去統(tǒng)計(jì)每個(gè)單點(diǎn)區(qū)間對應(yīng)的記錄的數(shù)量就太耗費(fèi)性能了，所以直接依賴統(tǒng)計(jì)數(shù)據(jù)中的平均一個(gè)值重復(fù)多少行來計(jì)算單點(diǎn)區(qū)間對應(yīng)的記錄數(shù)量。

select * from tbl_name where key in ('xx1', 'xx2', ..., 'xxn');

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

select * from t1 join t2 on t1.column = t2.key where ...;

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

在統(tǒng)計(jì)索引列不重復(fù)的值的數(shù)量時(shí)，有一個(gè)比較煩的問題就是索引列中出現(xiàn)NULL值怎么辦，比如說某個(gè)索引列的內(nèi)容是這樣：

+------+
| col  |
+------+
|    1 |
|    2 |
| NULL |
| NULL |
+------+

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

• 有的人認(rèn)為NULL值代表一個(gè)未確定的值，所以MySQL認(rèn)為任何和NULL值做比較的表達(dá)式的值都為NULL，就是這樣：

mysql> select 1 = null;
+----------+
| 1 = null |
+----------+
|     null |
+----------+
1 row inset (0.00 sec)

mysql> select 1 != null;
+-----------+
| 1 != null |
+-----------+
|      null |
+-----------+
1 row inset (0.00 sec)

mysql> select null = null;
+-------------+
| null = null |
+-------------+
|        null |
+-------------+
1 row inset (0.00 sec)

mysql> select null != null;
+--------------+
| null != null |
+--------------+
|         null |
+--------------+
1 row inset (0.00 sec)

所以每一個(gè)NULL值都是獨(dú)一無二的，也就是說統(tǒng)計(jì)索引列不重復(fù)的值的數(shù)量時(shí)，應(yīng)該把NULL值當(dāng)作一個(gè)獨(dú)立的值，所以col列的不重復(fù)的值的數(shù)量就是：4（分別是1、2、NULL、NULL這四個(gè)值）。

• 有的人認(rèn)為其實(shí)NULL值在業(yè)務(wù)上就是代表沒有，所有的NULL值代表的意義是一樣的，所以col列不重復(fù)的值的數(shù)量就是：3（分別是1、2、NULL這三個(gè)值）。
• 有的人認(rèn)為這NULL完全沒有意義嘛，所以在統(tǒng)計(jì)索引列不重復(fù)的值的數(shù)量時(shí)壓根不能把它們算進(jìn)來，所以col列不重復(fù)的值的數(shù)量就是：2（分別是1、2這兩個(gè)值）。

MySQL提供了一個(gè)名為innodb_stats_method的系統(tǒng)變量，相當(dāng)于在計(jì)算某個(gè)索引列不重復(fù)值的數(shù)量時(shí)如何對待NULL值這個(gè)鍋甩給了用戶，這個(gè)系統(tǒng)變量有三個(gè)候選值：

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

反正這個(gè)鍋是甩給用戶了，當(dāng)你選定了innodb_stats_method值之后，優(yōu)化器即使選擇了不是最優(yōu)的執(zhí)行計(jì)劃，那也跟MySQL沒關(guān)系了哈～ 當(dāng)然對于用戶的我們來說，最好不在索引列中存放NULL值才是正解。

總結(jié)

InnoDB以表為單位來收集統(tǒng)計(jì)數(shù)據(jù)，這些統(tǒng)計(jì)數(shù)據(jù)可以是基于磁盤的永久性統(tǒng)計(jì)數(shù)據(jù)，也可以是基于內(nèi)存的非永久性統(tǒng)計(jì)數(shù)據(jù)。

• innodb_stats_persistent控制著使用永久性統(tǒng)計(jì)數(shù)據(jù)還是非永久性統(tǒng)計(jì)數(shù)據(jù)；

• innodb_stats_persistent_sample_pages控制著永久性統(tǒng)計(jì)數(shù)據(jù)的采樣
  頁面數(shù)量；

• innodb_stats_transient_sample_pages控制著非永久性統(tǒng)計(jì)數(shù)據(jù)的采樣頁面數(shù)量；

• innodb_stats_auto_recalc控制著是否自動(dòng)重新計(jì)算統(tǒng)計(jì)數(shù)據(jù)。

• 我們可以針對某個(gè)具體的表，在創(chuàng)建和修改表時(shí)通過指定stats_persistent、stats_auto_recalc、stats_sample_pages的值來控制相關(guān)統(tǒng)計(jì)數(shù)據(jù)屬性。
• innodb_stats_method決定著在統(tǒng)計(jì)某個(gè)索引列不重復(fù)值的數(shù)量時(shí)如何對待NULL值

以上就是一文帶你了解MySQL之InnoDB統(tǒng)計(jì)數(shù)據(jù)是如何收集的的詳細(xì)內(nèi)容，更多關(guān)于MySQL InnoDB統(tǒng)計(jì)數(shù)據(jù)收集的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論