MySQL資料庫表索引取樣統計

cdrcsy發表於2024-09-02

指資料庫透過取樣、統計出來的表、索引的相關資訊，例如，表的記錄數、索引page個數、欄位的Cardinality選擇率等等。MySQL在生成執行計劃時,需要根據索引的統計資訊進行估算,計算出最小代價的執行計劃.MySQL支援有限的索引統計資訊，MySQL 8.0版本，支援直方圖。

一、取樣統計資訊引數

MySQL的InnoDB儲存引擎的統計資訊引數有7：

mysql> show variables like 'innodb_stats%';

+------------------------------------------+-------------+

| Variable_name | Value |

+------------------------------------------+-------------+

| innodb_stats_auto_recalc | ON | 當被修改的資料超過10%時就會觸發統計資訊重新統計計算

| innodb_stats_include_delete_marked | OFF | 重新計算統計資訊時是否包含刪除標記的記錄。OFF不包含

| innodb_stats_method | nulls_equal | 統計方法，關於NULL值的統計

| innodb_stats_on_metadata | OFF | 操作後設資料時是否觸發更新統計資訊，OFF否

| innodb_stats_persistent | ON | 統計資訊是否持久化,預設ON

| innodb_stats_persistent_sample_pages | 20 | 持久化抽樣page數

| innodb_stats_transient_sample_pages | 8 | 瞬時抽樣page數

+------------------------------------------+-------------+

引數innodb_stats_auto_recalc

該引數innodb_stats_auto_recalc控制是否自動重新計算統計資訊，當表中資料有大於10%被修改時就會重新計算統計資訊（後臺非同步處理，這個可能存在延時，不會立即觸發）。如果關閉了innodb_stats_auto_recalc,需要透過analyze table來保證統計資訊的準確性。

即使innodb_stats_auto_recalc=OFF時，當新索引被增加到表中，所有索引的統計資訊會被重新計算並且更新到innodb_index_stats表上。

下面驗證一下系統變數innodb_stats_auto_recalc=OFF時，表的屬性STATS_AUTO_RECALC=0。建立索引時，會觸發該表所有索引重新統計計算。

mysql> set global innodb_stats_auto_recalc=off;

mysql> select * from mysql.innodb_index_stats

mysql> ALTER TABLE test STATS_AUTO_RECALC=0

-> where database_name='MyDB' and table_name = 'test';

+---------------+------------+-----------------+---------------------+--------------+------------+-------------+--

+---------------+------------+-----------------+---------------------+--------------+------------+-------------+--

| MyDB | test | GEN_CLUST_INDEX | 2019-10-28 14:54:48 | n_diff_pfx01 | 2 | 1 |

+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-

3 rows in set (0.00 sec)

mysql> create index ix_test_name on test(name);

mysql> select * from mysql.innodb_index_stats

-> where database_name='MyDB' and table_name = 'test';

+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-

+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-

| MyDB | test | GEN_CLUST_INDEX | 2019-10-28 22:02:07 | n_diff_pfx01 | 2 | 1 |

| MyDB | test | ix_test_name | 2019-10-28 22:02:07 | n_diff_pfx01 | 999 | 1 |

| MyDB | test | ix_test_name | 2019-10-28 22:02:07 | n_diff_pfx02 | 252 | 1 |

+---------------+------------+-----------------+---------------------+--------------+------------+-------------+--

7 rows in set (0.00 sec)

引數innodb_stats_include_delete_marked 重新計算統計資訊時是否會考慮刪除標記的記錄.OFF不考慮不統計

引數innodb_stats_method

· 當變數設定為nulls_equal時，所有NULL值都被視為相同(即，它們都形成一個 value group)。

· 當變數設定為nulls_unequal時，NULL值不被視為相同。相反，每個NULL value 形成一個單獨的 value group，大小為 1。

· 當變數設定為nulls_ignored時，將忽略NULL值。

還有一個系統變數myisam_stats_method控制MyISAM表對Null值的統計方法。

引數innodb_stats_on_metadata

在MySQL 5.6.6之前的版本預設開啟（預設值為O），每當查詢information_schema後設資料庫裡的表時（例如，information_schema.TABLES、information_schema.TABLE_CONSTRAINTS .... ）或show table status、SHOW INDEX..這類操作時，Innodb還會隨機提取資料庫每個表索引頁的部分資料，從而更新information_schema.STATISTICS表，並返回剛才查詢的結果。當你的表很大，且數量很多時，耗費的時間就很長，以致很多經常不訪問的資料也會進入Innodb_buffer_pool緩衝池中，造成池汙染，關閉這個引數，可以加快對於schema庫表訪問，同時也可以改善查詢執行計劃的穩定性（對於Innodb表的訪問）。所以從MySQL 5.6.6這個版本開始，此引數預設為OFF。

注意：僅當最佳化器統計資訊配置為非永續性時，此選項才生效。

引數innodb_stats_persistent 此引數控制統計資訊是否持久化

如果此引數啟用，統計資訊將會儲存到mysql資料庫的innodb_table_stats和innodb_index_stats表中。從MySQL 5.6.6開始，MySQL預設使用持久化的統計資訊，能保證執行計劃的穩定性。設定為OFF。很多操作會觸發該操作。在資料表比較多、高併發下可能會造成一定的效能上影響，並且會導致執行計劃變動，不穩定。

另外，我們可以使用表的建表引數（STATS_PERSISTENT，STATS_AUTO_RECALC和STATS_SAMPLE_PAGES子句）來覆蓋系統變數設定的值，建表選項可以在CREATE TABLE或ALTER TABLE語句中指定。表上面指定的引數會覆蓋全域性變數

例如：

mysql> ALTER TABLE test STATS_PERSISTENT=1;

mysql> ALTER TABLE test STATS_AUTO_RECALC=0;

持久化統計表mysql.innodb_index_stats和mysql.innodb_table_stats中，這兩個表的定義如下：

innodb_table_stats：

Column name Description

database_name 資料庫名

table_name 表名，分割槽名或者子分割槽名

last_update 統計資訊最後一次更新時間戳

n_rows 表中資料行數

clustered_index_size 聚集索引page個數

sum_of_other_index_sizes 非聚集索引page個數

innodb_index_stats：

Column name Description

database_name 資料庫名

table_name 表名，分割槽名或者子分割槽名

index_name 索引名

last_update 最後一次更新時間戳

stat_name 統計資訊名

stat_value 統計資訊不同值個數

sample_size 取樣page個數

stat_description 描述

非持久化（Non-persistent optimizer statistics）儲存在記憶體裡，並在伺服器關閉時丟失。某些業務和某些條件下也會定期更新統計資料。

其實這裡指儲存在內層表（MEMROY TABLE）

引數innodb_stats_persistent_sample_pages

如果引數innodb_stats_persistent設定為ON，該參數列示ANALYZE TABLE更新Cardinality值時每次取樣頁的數量。預設值為20個頁面。innodb_stats_persistent_sample_pages太少會導致統計資訊不夠準確，太多會導致分析執行太慢。

我們可以在建立表的時候對不同的表指定不同的page數量、是否將統計資訊持久化到磁碟上、是否自動收集統計資訊，如下所示：

CREATE TABLE `test` (
`id` int(8) NOT NULL auto_increment,
`data` varchar(255),
`date` datetime,
PRIMARY KEY (`id`),
INDEX `DATE_IX` (`date`)
) ENGINE=InnoDB,
STATS_PERSISTENT=1,
STATS_AUTO_RECALC=1,
STATS_SAMPLE_PAGES=25;

引數innodb_stats_transient_sample_pages

控制取樣pages個數，預設為8。Innodb_stats_transient_sample_pages可以runtime設定

innodb_stats_transient_sample_pages在innodb_stats_persistent=0的時候影響取樣。

注意點：

1.若值太小，會導致評估不準

2.若果值太大，會導致disk read增加。

3.會生產很不同的執行計劃，因為統計資訊不同。

還有一個引數information_schema_stats_expiry。這個引數的作用如下：

對於INFORMATION_SCHEMA下的STATISTICS表和TABLES表中的資訊，8.0中透過快取的方式，以提高查詢的效能。可以透過設定information_schema_stats_expiry引數設定快取資料的過期時間，預設是86400秒。查詢這兩張表的資料的時候，首先是到快取中進行查詢，快取中沒有快取資料，或者快取資料過期了，查詢會從儲存引擎中獲取最新的資料。如果需要獲取最新的資料，可以透過設定information_schema_stats_expiry引數為0或者ANALYZE TABLE操作。

二、檢視統計資訊

持久化統計資料儲存在mysql.innodb_index_stats和mysql.innodb_table_stats中

非持久化統計資料

MySQL 8.0之前，儲存在information_schema.INDEXES和information_schema.TABLES中， MySQL8.0之後存放在INFORMATION_SCHEMA.TABLES、INFORMATION_SCHEMA.STATISTICS、INNODB_INDEXES。非持久化統計資訊放在記憶體中，記憶體表（MEMORY Table）中。

mysql.innodb_index_stats的資料如何看懂，要搞懂stat_name和stat_value的具體含義：

mysql> select * from mysql.innodb_index_stats

-> where database_name='MyDB' and table_name = 'test';

+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-

+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-

| MyDB | test | GEN_CLUST_INDEX | 2019-10-28 22:02:07 | n_diff_pfx01 | 2 | 1 |

| MyDB | test | ix_test_name | 2019-10-28 22:02:07 | n_diff_pfx01 | 999 | 1 |

| MyDB | test | ix_test_name | 2019-10-28 22:02:07 | n_diff_pfx02 | 252 | 1 |

+---------------+------------+-----------------+---------------------+--------------+------------+-------------+--

7 rows in set (0.00 sec)

stat_name=size時：stat_value表示索引的頁的數量。

stat_name=n_leaf_pages時：stat_value表示葉子節點的數量。

stat_name=n_diff_pfxNN時：stat_value表示索引欄位上唯一值的數量，此處做一下具體說明：

n_diff_pfxNN： NN代表數字（例如： 01，02等），當stat_name為n_diff_pfxNN時，stat_value列值顯示索引的first column(即索引的最前索引列)列的唯一值數量，例如：當NN為01時，stat_value列值就表示索引的第一個列的唯一值數量，當NN為02時，stat_value列值就表示索引的第一和第二個列的組合唯一值數量，以此類推。此外，在stat_name = n_diff_pfxNN的情況下，stat_description列顯示一個以逗號分隔的計算索引統計資訊列的列表。

三、MySQL的直方圖

MySQL 8.0推出了直方圖(histogram), 直方圖資料存放在information_schema.column_statistics這個系統表下，每行記錄對應一個欄位的直方圖，以json格式儲存。同時，新增了一個引數histogram_generation_max_mem_size來配置建立直方圖記憶體大小。

對於RDBMS，直方圖是特定列內資料分佈的近似值。

mysql> show variables like 'histogram_generation_max_mem_size';

+-----------------------------------+----------+

| Variable_name | Value |

+-----------------------------------+----------+

| histogram_generation_max_mem_size | 20000000 |

+-----------------------------------+----------+

1 row in set (0.01 sec)

mysql> desc information_schema.column_statistics;

+-------------+-------------+------+-----+---------+-------+

+-------------+-------------+------+-----+---------+-------+

+-------------+-------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

MySQL的直方圖有兩種：

等寬直方圖每個桶（bucket）儲存一個值以及這個值累積頻率；

等高直方圖每個桶需要儲存不同值的個數，上下限以及累計頻率等。MySQL會自動分配用哪種型別的直方圖。

建立刪除直方圖

MySQL的直方圖比較特殊,不會在建立索引的時候自動生成直方圖資料，需要手工執行 ANALYZE TABLE [table] UPDATE HISTOGRAM .... 這樣的命令產生表上各列的直方圖，預設情況下這些資訊會被複制到備庫。

語法：

ANALYZE TABLE tbl_name UPDATE HISTOGRAM ON col_name [, col_name] WITH N BUCKETS;

ANALYZE TABLE tbl_name DROP HISTOGRAM ON col_name [, col_name];

例如：

ANALYZE TABLE test UPDATE HISTOGRAM ON create_date,name WITH 16 BUCKETS;

注意：可指定BUCKETS的值，也可以不指定，它的取值範圍為1到1024，如果BUCKETS超過1024，就會報“ERROR 1690 (22003): Number of buckets value is out of range in 'ANALYZE TABLE'”

如果不指定BUCKETS值的話，預設值是100。低於100資料行的則會與資料行相關。

生成直方圖資料:

ANALYZE TABLE test UPDATE HISTOGRAM ON name;

刪除直方圖

ANALYZE TABLE test DROP HISTOGRAM ON create_date

檢視直方圖資料：

直接將json格式展示出來，看起來非常不直觀。常用格式化SQL

SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_PRETTY(HISTOGRAM)

FROM information_schema.column_statistics

WHERE TABLE_NAME='test'\G

SELECT SCHEMA_NAME,TABLE_NAME,COLUMN_NAME,

HISTOGRAM->>'$."data-type"' AS 'DATA_TYPE',

HISTOGRAM->>'$."sampling-rate"' AS SAMPLING_RATE,

HISTOGRAM->>'$."last-updated"' AS LAST_UPDATED,

HISTOGRAM->>'$."number-of-buckets-specified"' AS NUM_BUCKETS_SPECIFIED

JSON_LENGTH(HISTOGRAM->>'$."buckets"') AS 'BUCKET_COUNT'

FROM INFORMATION_SCHEMA.COLUMN_STATISTICS

WHERE TABLE_NAME = 'test';

SELECT FROM_BASE64(SUBSTRING_INDEX(v, ':', -1)) value, concat(round(c*100,1),'%') cumulfreq,

CONCAT(round((c - LAG(c, 1, 0) over()) * 100,1), '%') freq

FROM information_schema.column_statistics, JSON_TABLE(histogram->'$.buckets',

'$[*]' COLUMNS(v VARCHAR(60) PATH '$[0]', c double PATH '$[1]')) hist

WHERE schema_name = 'MyDB' and table_name = 'test' and column_name = 'name';

SELECT v value, concat(round(c*100,1),'%') cumulfreq,

CONCAT(round((c - LAG(c, 1, 0) over()) * 100,1), '%') freq

FROM information_schema.column_statistics, JSON_TABLE(histogram->'$.buckets',

'$[*]' COLUMNS(v VARCHAR(60) PATH '$[0]', c double PATH '$[1]')) hist

WHERE schema_name = 'MyDB' and table_name = 'test' and column_name = 'name';

更新統計資訊

非持久統計統計資訊也會觸發自動更新，非持久化統計資訊在以下情況會被自動更新，官方文件介紹如下：

1 執行ANALYZE TABLE

2 innodb_stats_on_metadata=ON情況下，執SHOW TABLE STATUS, SHOW INDEX, 查詢 INFORMATION_SCHEMA下的TABLES, STATISTICS

3 啟用--auto-rehash功能情況下，使用mysql client登入會觸發。

4 表第一次被開啟

5 距上一次更新統計資訊，表1/16的資料被修改

持久統計資訊的統計資訊更新上面已經有介紹，還有一種方法就是手動更新統計資訊，

1、手動更新統計資訊，注意執行過程中會加讀鎖：

ANALYZE TABLE TABLE_NAME;

2、如果更新後統計資訊仍不準確，可考慮增加表取樣的資料頁，兩種方式可以修改：

1) 全域性變數INNODB_STATS_PERSISTENT_SAMPLE_PAGES，預設為20；

2) 單個表可以指定該表的取樣：

ALTER TABLE TABLE_NAME STATS_SAMPLE_PAGES=100; #最大值是65535，超出會報錯。

參考資料：

https://www.cnblogs.com/kerrycode/p/11821042.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-persistent-stats.html

https://dev.mysql.com/doc/refman/8.0/en/index-statistics.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-performance-optimizer-statistics.html

https://www.percona.com/blog/2019/10/29/column-histograms-on-percona-server-and-mysql-8-0/ 重點

http://chinaunix.net/uid-31396856-id-5787793.html

https://mysqlserverteam.com/histogram-statistics-in-mysql/

https://mp.weixin.qq.com/s/698g5lm9CWqbU0B_p0nLMw?

MySQL資料庫審計系統
2018-08-06
MySql資料庫
MySQL 規範 (資料庫表設計規範)
2019-03-08
MySql資料庫
MySQL如何按周統計表中資料
2020-12-15
MySql
MySQL資料庫之分庫分表方案
2019-07-24
MySql資料庫
MySQL-檢索資料
2022-06-08
MySql
sql統計各種奇葩的資料庫表資料
2021-09-09
SQL資料庫
查詢MySQL資料庫，MySQL表的大小
2020-11-03
MySql資料庫
統計資訊記錄表|全方位認識 mysql 系統庫
2019-08-28
MySql
資料庫物件資訊記錄表|全方位認識 mysql 系統庫
2019-08-12
資料庫物件MySql
MySQL單表檢索
2021-12-11
MySql
程式設計師面試之MySQL資料庫表的設計
2021-08-27
程式設計師面試MySql資料庫
[轉帖]達夢資料庫-統計資料表資料量及空間表大小
2024-04-28
資料庫
MySQL練習——教學系統資料庫設計
2020-10-25
MySql資料庫
MySQL—-MySQL資料庫入門—-第二章資料庫和表的基本操作
2019-02-20
MySql資料庫
MySQL資料庫delete資料時，不支援表別名
2019-01-07
MySql資料庫delete
MySQL資料庫設計規範
2018-12-20
MySql資料庫
MySQL 資料庫設計總結
2019-03-11
MySql資料庫
4，MySQL資料庫的設計
2024-11-10
MySql資料庫
MYSQL——mysql檢索不包含字母U的資料
2024-07-09
MySql
通用許可權系統之資料庫表設計
2024-12-07
資料庫
資料庫的檢索語句
2020-04-05
資料庫
MySQL資料庫 ---MySQL表的增刪改查(進階)
2022-03-08
MySql資料庫
MySQL資料庫之mysql5.7基礎檢視一個資料庫中的所有表
2020-11-06
MySql資料庫
【MySQL資料庫】認識資料庫+環境搭建--------Windows系統
2024-05-26
MySql資料庫Windows
【資料庫資料恢復】linux系統下MYSQL資料庫資料恢復案例
2022-11-15
資料庫資料恢復LinuxMySql
[資料庫]【MySQL】MySQL資料庫規範總結
2019-03-10
資料庫MySql
MySQL庫表設計小技巧
2020-05-08
MySql
zabbix上對mysql資料庫做分割槽表
2018-11-07
MySql資料庫
MySQL資料庫表誤刪除恢復(一)
2023-05-14
MySql資料庫
【資料庫】MySQL鎖機制、熱備、分表
2019-04-26
資料庫MySql
如何刪除資料庫下的所有表（mysql）
2024-08-20
資料庫MySql
使用AnalyticDB MySQL建立資料庫及表過程
2021-04-20
MySql資料庫
開啟mysql 資料庫審計功能。
2021-11-17
MySql資料庫
MySQL 資料庫規範--設計篇
2021-09-09
MySql資料庫
MySQL 資料庫設計的“奧祕”
2021-06-27
MySql資料庫
MySQL之規範資料庫設計
2020-12-05
MySql資料庫
【資料庫】解決Mysql資料庫提示innodb表不存在的問題！
2018-12-03
資料庫MySql
統計資料庫中表大小
2019-09-19
資料庫

MySQL資料庫表索引取樣統計

相關文章