一、兩種不同的統(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ù)器重啟之后，在某些適當(dāng)?shù)膱鼍跋虏艜匦率占@些統(tǒng)計數(shù)據(jù)

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

不過InnoDB默認是以表為單位來收集和存儲統(tǒng)計數(shù)據(jù)的，也就是說我們可以把某些表的統(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屬性，那默認采用系統(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)計數(shù)據(jù)

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

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

可以看到，這兩個表都位于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ù)是如何生成的

2.1 innodb_table_stats

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

注意這個表的主鍵是(database_name,table_name)，也就是innodb_table_stats表的每條記錄代表著一個表的統(tǒng)計信息。我們直接看一下這個表里的內(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)計信息就對應(yīng)著mysql.innodb_table_stats的第二條記錄。幾個重要統(tǒng)計信息項的值如下：

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

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

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

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

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

可以看出來這個n_rows值精確與否取決于統(tǒng)計時采樣的頁面數(shù)量，MySQL為我們準(zhǔn)備了一個名為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)計耗時特別少。

所以在實際使用是需要我們?nèi)?quán)衡利弊，該系統(tǒng)變量的默認值是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默認是以表為單位來收集和存儲統(tǒng)計數(shù)據(jù)的，我們也可以單獨設(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屬性的話，將默認使用系統(tǒng)變量innodb_stats_persistent_sample_pages的值作為該屬性的值。

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

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

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

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

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

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

步驟三： 從葉子節(jié)點段和非葉子節(jié)點段的Segment Header中找到這兩個段對應(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é)點。

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

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

這個是鏈表基節(jié)點的示意圖：

步驟四： 分別計算聚簇索引的葉子結(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時都把它們算進去了，所以說聚簇索引和其他的索引占用的頁面數(shù)可能比這兩個值要大一些。

2.2 innodb_index_stats

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

注意這個表的主鍵是(database_name,table_name,index_name,stat_name)，其中的stat_name是指統(tǒng)計項的名稱，也就是說innodb_index_stats表的每條記錄代表著一個索引的一個統(tǒng)計項?？赡苓@會大家有些懵逼這個統(tǒng)計項到底指什么，別著急，我們直接看一下關(guān)于demo8表的索引統(tǒng)計數(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)

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

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

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

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

  •  n_diff_pfxNN：表示對應(yīng)的索引列不重復(fù)的值有多少。其中的NN長得有點?怪呀，啥意思呢？ 其實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ù)的值有多少

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

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

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

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

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

• 開啟innodb_stats_auto_recalc

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

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)

再一次強調(diào)，InnoDB默認是以表為單位來收集和存儲統(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屬性，那默認采用系統(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ù)，比如我們可以這樣更新關(guān)于demo8表的統(tǒng)計數(shù)據(jù)：

mysql> analyze table demo8;

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

2.4 手動動更新innodb_table_stats 和 innodb_index_stats表

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

步驟一： 更新demo8表

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

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

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

flush table demo8;

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

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

當(dāng)我們把系統(tǒng)變量innodb_stats_persistent的值設(shè)置為OFF時，之后創(chuàng)建的表的統(tǒng)計數(shù)據(jù)默認就都是非永久性的了，或者我們直接在創(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)變量的默認值是8。另外，由于非永久性的統(tǒng)計數(shù)據(jù)經(jīng)常更新，所以導(dǎo)致MySQL查詢優(yōu)化器計算查詢成本的時候依賴的是經(jīng)常變化的統(tǒng)計數(shù)據(jù)，也就會生成經(jīng)常變化的執(zhí)行計劃，這個可能讓大家有些懵逼。不過最近的MySQL版本都不咋用這種基于內(nèi)存的非永久性統(tǒng)計數(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ù)量這個統(tǒng)計數(shù)據(jù)對于MySQL查詢優(yōu)化器十分重要，因為通過它可以計算出在索引列中平均一個值重復(fù)多少行，它的應(yīng)用場景主要有兩個：

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

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

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

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值怎么辦，比如說某個索引列的內(nèi)容是這樣：

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

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

• 有的人認為NULL值代表一個未確定的值，所以MySQL認為任何和NULL值做比較的表達式的值都為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)

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

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

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

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

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

總結(jié)

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

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

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

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

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

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

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

最新評論