MySQl 配置InnoDB持久化的優化器統計信息

配置innodb的優化器統計信息

介紹如何為innodb表配置持久化和非持久化的優化器統計信息。

持久性優化器統計數據將被持久保存可以跨躍服務器的重啟，從而實現更大的計劃穩定性和更一致的查詢性能。持久性優化器統計數據還提供了控制和靈活性以及這些額外的好處:

.您可以使用innodb_stats_auto_recalc配置選項來控制是否在對表進行重大更改后自動更新統計信息

.您可以使用STATS_PERSISTENT、STATS_AUTO_RECALC和STATS_SAMPLE_PAGES子句以及CREATE TABLE和ALTER TABLE語句為單個表配置優化器統計信息

.您可以在Mysql.innodb_table_stats和Mysql.innodb_index_stats表中查詢優化器的統計數據

mysql> select * from mysql.innodb_table_stats where database_name=‘d1’;

±--------------±------------------±--------------------±-------±---------------------±-------------------------+

| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |

±--------------±------------------±--------------------±-------±---------------------±-------------------------+

| d1 | child | 2021-08-17 16:27:43 | 2 | 1 | 0 |

| d1 | t | 2021-08-17 17:30:55 | 0 | 1 | 0 |

| d1 | t1 | 2021-08-09 16:18:51 | 0 | 1 | 0 |

±--------------±------------------±--------------------±-------±---------------------±-------------------------+

55 rows in set (0.04 sec)

mysql> select * from mysql.innodb_index_stats where database_name=‘d1’;

±--------------±------------------±-----------------±--------------------±-------------±-----------±------------±----------------------------------+

| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |

±--------------±------------------±-----------------±--------------------±-------------±-----------±------------±----------------------------------+

| d1 | child | PRIMARY | 2021-08-17 16:27:43 | n_diff_pfx01 | 2 | 1 | id |

| d1 | child | PRIMARY | 2021-08-17 16:27:43 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |

| d1 | child | PRIMARY | 2021-08-17 16:27:43 | size | 1 | NULL | Number of pages in the index |

| d1 | t | GEN_CLUST_INDEX | 2021-08-17 17:30:55 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |

| d1 | t | GEN_CLUST_INDEX | 2021-08-17 17:30:55 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |

| d1 | t | GEN_CLUST_INDEX | 2021-08-17 17:30:55 | size | 1 | NULL | Number of pages in the index |

| d1 | t1 | GEN_CLUST_INDEX | 2021-08-09 16:18:51 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |

| d1 | t1 | GEN_CLUST_INDEX | 2021-08-09 16:18:51 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |

| d1 | t1 | GEN_CLUST_INDEX | 2021-08-09 16:18:51 | size | 1 | NULL | Number of pages in the index |

±--------------±------------------±-----------------±--------------------±-------------±-----------±------------±----------------------------------+

191 rows in set (0.00 sec)

.可以查看mysql.innodb_table_stats和mysql.innodb_index_stats表的last_update列查看統計信息最后一次更新的時間。

.您可以手動修改mysql.innodb_table_stats和mysql.innodb_index_stats表強制執行特定的查詢優化計劃或在不修改數據庫的情況下測試可選計劃。

默認情況下，持久化優化器統計特性是啟用的(innodb_stats_persistent=ON)。

非持久優化器統計信息在每次服務器重啟時和一些其他操作之后被清除，并在下一個表訪問時重新計算。因此，在重新計算統計信息時可能會產生不同的估計，導致執行計劃中的不同選擇和查詢性能的變化

本節還提供了有關估計ANALYZE TABLE復雜度的信息，這在試圖在準確的統計數據和ANALYZE TABLE執行時間之間取得平衡時可能很有用。

配置持久優化器統計信息參數

持久性優化器統計信息特性將統計信息存儲到磁盤，并在服務器重啟時保持這些統計信息的持久性，從而提高了計劃的穩定性，這樣優化器就更有可能在每次給定查詢時做出一致的選擇。

當innodb_stats_persistent=ON或使用STATS_PERSISTENT=1創建或修改單個表時，優化器統計信息被持久化到磁盤。innodb_stats_persistent默認啟用。

以前，在每次服務器重啟和一些其他操作之后，都會清除優化器統計信息，并在下一個表訪問時重新計算。因此，在重新計算統計信息時可能會產生不同的估計，導致查詢執行計劃中的不同選擇，從而導致查詢性能的變化。

持久性統計信息存儲在mysql.innodb_table_stats和mysql.innodb_index_stats表中。

要恢復使用非持久優化器統計信息，可以使用ALTER TABLE tbl_name STATS_PERSISTENT=0語句修改表。

為持久優化器統計信息配置自動統計信息計算

innodb_stats_auto_recalc配置選項在默認情況下是啟用的，它決定是否在表發生重大更改(超過10%的行)時自動計算統計數據。您還可以在CREATE TABLE或ALTER TABLE語句中使用STATS_AUTO_RECALC子句為單個表配置自動統計信息重新計算。innodb_stats_auto_recalc默認啟用。

mysql> show variables like ‘innodb_stats_auto_recalc’;

±-------------------------±------+

| Variable_name | Value |

±-------------------------±------+

| innodb_stats_auto_recalc | ON |

±-------------------------±------+

1 row in set (0.01 sec)

由于自動統計信息重新計算的異步特性(發生在后臺)，即使啟用了innodb_stats_auto_recalc，當DML操作影響一個表的10%以上時，統計數據可能不會立即重新計算。在某些情況下，統計信息的重新計算可能會延遲幾秒鐘。如果在更改表的重要部分后需要立即更新統計信息，則運行analyze table來啟動同步(前臺)統計信息的重新計算。

如果innodb_stats_auto_recalc被禁用，那么在對索引的列進行大量更改之后，為每個適用的表發出ANALYZE TABLE語句，以確保優化器統計數據的準確性。您可以在將代表性數據加載到表中之后，在設置腳本中運行此語句，并在DML操作顯著改變了索引列的內容之后定期運行它，或者在活動較少的時候調度運行它。當一個新的索引被添加到一個現有的表時，索引統計信息被計算并添加到innodb_index_stats表中，而不管innodb_stats_auto_recalc的值是多少。

要確保在創建新索引時收集統計信息，可以啟用innodb_stats_auto_recalc選項，也可以在啟用持久統計模式時，在創建每個新索引后運行ANALYZE TABLE。

為個別表配置優化器統計信息參數

innodb_stats_persistent,innodb_stats_auto_recalc和innodb_stats_persistent_sample_pages是全局配置選項。要覆蓋這些系統范圍的設置，并為各個表配置優化器統計信息參數，可以在CREATE TABLE或ALTER TABLE語句中定義STATS_PERSISTENT、STATS_AUTO_RECALC和STATS_SAMPLE_PAGES子句來實現。

.STATS_PERSISTENT指定InnoDB表是否啟用持久化統計信息。默認值導致表的持久統計信息設置由innodb_stats_persistent配置選項決定。值1啟用表的持久統計，而值0關閉此特性。在通過CREATE TABLE或ALTER TABLE語句啟用持久統計信息后，在將代表性數據加載到表中之后，發出ANALYZE TABLE語句來計算統計信息

.STATS_AUTO_RECALC指定是否自動重新計算InnoDB表的持久統計信息。默認值導致表的持久統計信息設置由innodb_stats_auto_recalc配置選項決定。當表中10%的數據發生變化時，值1將導致重新計算統計數據。0可以防止對該表進行自動重新計算;使用此設置，在對表進行實質性更改后，發出一條ANALYZE TABLE語句來重新計算統計數據。

.STATS_SAMPLE_PAGES指定在估計索引列的基數和其他統計信息(例如由ANALYZE TABLE計算的統計信息)時要抽樣的索引頁數。

三個子句都在下面的CREATE TABLE示例中指定:

mysql> CREATE TABLE t1 (

-> 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;

Query OK, 0 rows affected (0.09 sec)

配置InnoDB優化器統計信息的采樣頁面數

MySQL查詢優化器使用關于鍵分布的估計統計信息，根據索引的相對選擇性為執行計劃選擇索引。像ANALYZE TABLE這樣的操作會導致InnoDB從表上的每個索引中隨機取樣頁，以估計索引的基數。(這種技術被稱為隨機潛水。)

為了控制統計信息估計的質量(從而為查詢優化器提供更好的信息)，可以使用參數innodb_stats_persistent_sample_pages更改采樣頁面的數量，這個參數可以在運行時設置

Innodb_stats_persistent_sample_pages的默認值是20。作為一般指導原則，當遇到以下問題時，請考慮修改此參數：

1.統計數據不夠準確，優化器會選擇次優計劃，如EXPLAIN輸出所示。通過比較索引的實際基數(在索引列上運行SELECT DISTINCT返回)和mysql.innodb_index_stats持久化統計表提供的估計，可以檢查統計信息的準確性。

如果確定統計信息不夠準確，則應該增加innodb_stats_persistent_sample_pages的值，直到統計信息估計足夠準確。然而，過多地增加innodb_stats_persistent_sample_pages可能會導致ANALYZE TABLE運行緩慢

2.ANALYZE TABLE太慢。在這種情況下，innodb_stats_persistent_sample_pages應該減少，直到ANALYZE TABLE的執行時間是可接受的。然而，過多地降低該值可能會導致第一個問題:不準確的統計數據和不夠理想的查詢執行計劃

如果無法在精確的統計數據和ANALYZE TABLE執行時間之間取得平衡，那么可以考慮減少表中索引列的數量，或者限制分區的數量，以降低ANALYZE TABLE的復雜性。表主鍵中的列數也需要考慮，因為主鍵列被附加到每個非唯一索引。

在持久性統計信息計算中包括刪除標記的記錄

默認情況下，InnoDB在計算統計信息讀取未提交的數據。在一個未提交事務從表中刪除行的情況下，InnoDB在計算行估計和索引統計時，會排除被標記刪除的記錄，這可能會導致使用READ UNCOMMITTED以外的事務隔離級別并發操作表的其他事務的執行計劃不是最優的。為了避免這種情況，可以啟用innodb_stats_include_delete_marked，以確保在計算持久優化器統計數據時，InnoDB包含有標記刪除的記錄。

當innodb_stats_include_delete_marked被啟用后，analyze table在計算統計信息時會考慮被標記為刪除的記錄。

innodb_stats_include_delete_marked是一個全局設置會影響所有的innodb表，并且它只應用于持久性優化器統計。

innodb_stats_include_delete_marked是在MySQL 5.7.16中引入的。

InnoDB持久化統計信息表

持久統計特性依賴于mysql數據庫的內部管理表innodb_table_stats和innodb_index_stats。這些表在所有安裝、升級和從源構建過程中自動設置。

mysql> desc innodb_table_stats;

±-------------------------±--------------------±-----±----±------------------±----------------------------+

| Field | Type | Null | Key | Default | Extra |

±-------------------------±--------------------±-----±----±------------------±----------------------------+

| database_name | varchar(64) | NO | PRI | NULL | |

| table_name | varchar(199) | NO | PRI | NULL | |

| last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

| n_rows | bigint(20) unsigned | NO | | NULL | |

| clustered_index_size | bigint(20) unsigned | NO | | NULL | |

| sum_of_other_index_sizes | bigint(20) unsigned | NO | | NULL | |

±-------------------------±--------------------±-----±----±------------------±----------------------------+

6 rows in set (0.00 sec)

mysql> desc innodb_index_stats;

±-----------------±--------------------±-----±----±------------------±----------------------------+

| Field | Type | Null | Key | Default | Extra |

±-----------------±--------------------±-----±----±------------------±----------------------------+

| database_name | varchar(64) | NO | PRI | NULL | |

| table_name | varchar(199) | NO | PRI | NULL | |

| index_name | varchar(64) | NO | PRI | NULL | |

| last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

| stat_name | varchar(64) | NO | PRI | NULL | |

| stat_value | bigint(20) unsigned | NO | | NULL | |

| sample_size | bigint(20) unsigned | YES | | NULL | |

| stat_description | varchar(1024) | NO | | NULL | |

±-----------------±--------------------±-----±----±------------------±----------------------------+

8 rows in set (0.00 sec)

innodb_table_stats和innodb_index_stats表都包含一個last_update列，顯示InnoDB上次更新索引統計信息的時間，如下例所示:

mysql> select * from innodb_table_stats \G

*************************** 1. row ***************************

database_name: cs

table_name: address

last_update: 2021-06-03 16:17:22

n_rows: 3

clustered_index_size: 1

sum_of_other_index_sizes: 0

mysql> select * from innodb_index_stats where table_name=‘address’ \G

*************************** 1. row ***************************

database_name: cs

table_name: address

index_name: PRIMARY

last_update: 2021-06-03 16:17:22

stat_name: n_diff_pfx01

stat_value: 3

sample_size: 1

stat_description: address_id

*************************** 2. row ***************************

database_name: cs

table_name: address

index_name: PRIMARY

last_update: 2021-06-03 16:17:22

stat_name: n_leaf_pages

stat_value: 1

sample_size: NULL

stat_description: Number of leaf pages in the index

*************************** 3. row ***************************

database_name: cs

table_name: address

index_name: PRIMARY

last_update: 2021-06-03 16:17:22

stat_name: size

stat_value: 1

sample_size: NULL

stat_description: Number of pages in the index

3 rows in set (0.00 sec)

innodb_table_stats和innodb_index_stats是普通表，可以手動更新。手動更新統計信息的能力使得在不修改數據庫的情況下強制執行特定的查詢優化計劃或測試備選計劃成為可能。如果您手動更新統計信息，請執行FLUSH TABLE tbl_name命令讓MySQL重新加載更新后的統計信息。

持久性統計信息被認為是本地信息，因為它們與服務器實例相關。因此，當自動統計信息重新計算發生時，innodb_table_stats和innodb_index_stats表不會被復制。如果您運行ANALYZE TABLE來啟動統計信息的同步重新計算，那么這個語句將被復制(

除非您抑制了對它的日志記錄)，并在復制從服務器上進行重新計算。

InnoDB持久化統計信息表示例

innodb_table_stats表每個表包含一行。下面的例子演示了收集到的數據。

表t1包含一個主索引(列a、b)二級索引(列c、d)和唯一索引(列e、f):

mysql> CREATE TABLE t1 (

-> a INT, b INT, c INT, d INT, e INT, f INT,

-> PRIMARY KEY (a, b), KEY i1 (c, d), UNIQUE KEY i2uniq (e, f)

-> ) ENGINE=INNODB;

Query OK, 0 rows affected (0.14 sec)

插入五行樣本數據后，表如下所示:

mysql> insert into t1 values(1,1,10,11,100,101),(1,2,10,11,200,102),(1,3,10,11,100,103),(1,4,10,12,200,104),(1,5,10,12,100,105);

Query OK, 5 rows affected (0.12 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;

±–±--±-----±-----±-----±-----+

| a | b | c | d | e | f |

±–±--±-----±-----±-----±-----+

| 1 | 1 | 10 | 11 | 100 | 101 |

| 1 | 2 | 10 | 11 | 200 | 102 |

| 1 | 3 | 10 | 11 | 100 | 103 |

| 1 | 4 | 10 | 12 | 200 | 104 |

| 1 | 5 | 10 | 12 | 100 | 105 |

±–±--±-----±-----±-----±-----+

5 rows in set (0.00 sec)

要立即更新統計信息，運行ANALYZE TABLE(如果啟用了innodb_stats_auto_recalc，假設改變的表行達到10%的閾值，統計信息會在幾秒鐘內自動更新)

mysql> analyze table t1;

±---------±--------±---------±---------+

| Table | Op | Msg_type | Msg_text |

±---------±--------±---------±---------+

| mysql.t1 | analyze | status | OK |

±---------±--------±---------±---------+

1 row in set (0.03 sec)

mysql> select * from innodb_table_stats where table_name=‘t1’ and database_name=‘mysql’;

±--------------±-----------±--------------------±-------±---------------------±-------------------------+

| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |

±--------------±-----------±--------------------±-------±---------------------±-------------------------+

| mysql | t1 | 2022-02-17 14:52:13 | 5 | 1 | 2 |

±--------------±-----------±--------------------±-------±---------------------±-------------------------+

1 row in set (0.01 sec)

表t1的表統計信息顯示InnoDB最后更新表統計信息的時間為(2022-02-17 14:52:13)，表中的行記錄數為5，集簇索引大小為1個索引頁，其它索引大小為2個索引頁。

mysql> select * from innodb_table_stats where table_name=‘t1’ and database_name=‘mysql’ \G

*************************** 1. row ***************************

database_name: mysql

table_name: t1

last_update: 2022-02-17 14:52:13

n_rows: 5

clustered_index_size: 1

sum_of_other_index_sizes: 2

1 row in set (0.01 sec)

innodb_index_stats表包含每個索引的多行。innodb_index_stats表中的每一行都提供了與特定索引統計相關的數據，在stat_name列中顯示命名，在stat_description列中顯示描述。例如:

mysql> select * from innodb_index_stats where table_name=‘t1’ and database_name=‘mysql’;

±--------------±-----------±-----------±--------------------±-------------±-----------±------------±----------------------------------+

| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |

±--------------±-----------±-----------±--------------------±-------------±-----------±------------±----------------------------------+

| mysql | t1 | PRIMARY | 2022-02-17 14:52:13 | n_diff_pfx01 | 1 | 1 | a |

| mysql | t1 | PRIMARY | 2022-02-17 14:52:13 | n_diff_pfx02 | 5 | 1 | a,b |

| mysql | t1 | PRIMARY | 2022-02-17 14:52:13 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |

| mysql | t1 | PRIMARY | 2022-02-17 14:52:13 | size | 1 | NULL | Number of pages in the index |

| mysql | t1 | i1 | 2022-02-17 14:52:13 | n_diff_pfx01 | 1 | 1 | c |

| mysql | t1 | i1 | 2022-02-17 14:52:13 | n_diff_pfx02 | 2 | 1 | c,d |

| mysql | t1 | i1 | 2022-02-17 14:52:13 | n_diff_pfx03 | 2 | 1 | c,d,a |

| mysql | t1 | i1 | 2022-02-17 14:52:13 | n_diff_pfx04 | 5 | 1 | c,d,a,b |

| mysql | t1 | i1 | 2022-02-17 14:52:13 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |

| mysql | t1 | i1 | 2022-02-17 14:52:13 | size | 1 | NULL | Number of pages in the index |

| mysql | t1 | i2uniq | 2022-02-17 14:52:13 | n_diff_pfx01 | 2 | 1 | e |

| mysql | t1 | i2uniq | 2022-02-17 14:52:13 | n_diff_pfx02 | 5 | 1 | e,f |

| mysql | t1 | i2uniq | 2022-02-17 14:52:13 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |

| mysql | t1 | i2uniq | 2022-02-17 14:52:13 | size | 1 | NULL | Number of pages in the index |

±--------------±-----------±-----------±--------------------±-------------±-----------±------------±----------------------------------+

14 rows in set (0.01 sec)

stat_name列顯示了以下類型的統計信息:

.size: 當tat_name=size時, stat_value列顯示索引中的總頁數。

.n_leaf_pages: 當stat_name=n_leaf_pages時, stat_value列顯示索引中葉頁的數量。

.n_diff_pfxNN: 當stat_name=n_diff_pfx01時，stat_value列顯示索引中第一列的distinct值的數量。當stat_name=n_diff_pfx02時，stat_value列顯示索引中前兩列的distinct值的數量。另外，stat_name=n_diff_pfxNN，stat_description列顯示了被計數的索引列的逗號分隔列表。

為了進一步說明n_diff_pfxNN統計數據所提供的基數數據，考慮t1表示例。如下所示，用一個主索引(列a、b)、一個輔助索引(列c、d)和一個唯一索引(列e、f)創建了t1表。

mysql> CREATE TABLE t1 (

-> a INT, b INT, c INT, d INT, e INT, f INT,

-> PRIMARY KEY (a, b), KEY i1 (c, d), UNIQUE KEY i2uniq (e, f)

-> ) ENGINE=INNODB;

Query OK, 0 rows affected (0.14 sec)

插入五行樣本數據后，表如下所示:

mysql> insert into t1 values(1,1,10,11,100,101),(1,2,10,11,200,102),(1,3,10,11,100,103),(1,4,10,12,200,104),(1,5,10,12,100,105);

Query OK, 5 rows affected (0.12 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;

±–±--±-----±-----±-----±-----+

| a | b | c | d | e | f |

±–±--±-----±-----±-----±-----+

| 1 | 1 | 10 | 11 | 100 | 101 |

| 1 | 2 | 10 | 11 | 200 | 102 |

| 1 | 3 | 10 | 11 | 100 | 103 |

| 1 | 4 | 10 | 12 | 200 | 104 |

| 1 | 5 | 10 | 12 | 100 | 105 |

±–±--±-----±-----±-----±-----+

5 rows in set (0.00 sec)

當查詢index_name,stat_name,stat_value和stat_description且where條件為stat_name like ‘n_diff%’，返回結果如下:

mysql> select index_name,stat_name,stat_value,stat_description from innodb_index_stats where table_name=‘t1’ and

-> database_name=‘mysql’ and stat_name like ‘n_diff%’;

±-----------±-------------±-----------±-----------------+

| index_name | stat_name | stat_value | stat_description |

±-----------±-------------±-----------±-----------------+

| PRIMARY | n_diff_pfx01 | 1 | a |

| PRIMARY | n_diff_pfx02 | 5 | a,b |

| i1 | n_diff_pfx01 | 1 | c |

| i1 | n_diff_pfx02 | 2 | c,d |

| i1 | n_diff_pfx03 | 2 | c,d,a |

| i1 | n_diff_pfx04 | 5 | c,d,a,b |

| i2uniq | n_diff_pfx01 | 2 | e |

| i2uniq | n_diff_pfx02 | 5 | e,f |

±-----------±-------------±-----------±-----------------+

8 rows in set (0.00 sec)

對于primary索引，這里有兩個n_diff%行。行數等于索引中的列數。

注意：對于非唯一索引 ，InnoDB會附加主鍵索引的列到非唯一索引中。

.當index_name=PRIMARY和stat_name=n_diff_pfx01時,stat_value值為1，這說明索引中第一個列(a)包含一個distinct值。列a的distinct值可以通過查看表t1中的列a的值來進行確認,只有單個distinct值1。計數列(a)顯示在結果集的stat_description列中。

.當index_name=PRIMAY和stat_name=n_diff_pfx02時，stat_value值為5，這說明索引中前兩列包含五個distinct值。列a和b的distinct值可以通過查看表t1中的列a和b的值來進行確認，有五個distinct值(1,1),(1,2),(1,3),(1,4),(1,5)。計數列(a，)顯示在結果集的stat_description列中。

對于二級索引(i1)，有4個n_diff%行。二級索引只定義了兩個列(c,d)，但是二級索引有四個n_diff%行，因為InnoDB將所有非唯一的索引都以主鍵作為后綴。因此，二級索引列(c,d)和主鍵列(a,b)有4個n_diff%行，而不是2個。

.當index_name=i1和stat_name=n_diff_pfx01時，stat_value的值為1,這說明索引中第一列(c）包含一個distinct值。列c的distinct值可以通過查看表t1中的列c的數據來進行確認。計數列c在stat_description列中顯示。

.當index_name=i1和stat_name=n_diff_pfx02時，stat_value的值為2，這說明索引中前兩列(c,d)包含兩個distinct值。列c和d的distinct值可以通過查看表t1中的列c和d的數據來進行確認。計數列(c,d)在stat_description列中顯示

.當index_name=i1和stat_name=n_diff_pfx03，stat_value的值為2，這說明索引中前三列(c,d,a)包含兩個distinct值。列c,d和a的distinct值可以通過查看表t1中列c,d和a的數據來進行確認，有兩個distinct值(10,11,1)和(10,12,1)。計數列(c,d,a)在stat_desciption列中顯示

.當index_name=i1和stat_name=n_diff_pfx04，stat_value的值為5，這說明索引中四列(c,d,a,b)包含五個distinct值。列c,d,a和b的distinct值可以通過查看表t1中列c,d,a和b的數據來進行確認，有五個distinct值(10,11,1,1)和(10,11,1,2)，（10,11,1,3),(10,12,1,4)和(10,12,1,5)。計數列(c,d,a,b)在stat_description列中顯示

對于唯一索引(i2uniq)，有兩個n_diff%行。

.當index_name=i2uniq和stat_name=n_diff_pfx01時，stat_value值為2，這說明索引中第一列(e)包含兩個distinct值。列e的distinct值可以通過查看表t1的列e的數據來進行確認，有兩個distinct值(100)和(200)。計數列e在stat_description列中顯示。

.當index_name=i2uniq和stat_name=n_diff_pfx02時，stat_value值為5，這說明索引中兩列(e,f)包含五個distinct值。列e和f的distinct值可以通過查看表t1的列e和f的數據來進行確認，有五個distinct值(100,101),(200,102),(100,103),(200,104)和(100,105)。計數列(e,f)在stat_description列中顯示。

使用innodb_index_stats表獲取索引大小

表、分區或子分區的索引大小可以使用innodb_index_stats表來檢索。在下面的例子中，檢索表t1的索引大小。

mysql> select sum(stat_value) pages,index_name,sum(stat_value)*@@innodb_page_size size

-> from mysql.innodb_index_stats where table_name=‘t1’ and database_name=‘mysql’ and stat_name=‘size’ group by index_name;

±------±-----------±------+

| pages | index_name | size |

±------±-----------±------+

| 1 | PRIMARY | 16384 |

| 1 | i1 | 16384 |

| 1 | i2uniq | 16384 |

±------±-----------±------+

3 rows in set (0.04 sec)

mysql> show variables like ‘innodb_page_size’;

±-----------------±------+

| Variable_name | Value |

±-----------------±------+

| innodb_page_size | 16384 |

±-----------------±------+

1 row in set (0.01 sec)

對于分區或子分區，可以使用帶有修改后的WHERE子句的相同查詢來檢索索引大小。例如，下面的查詢檢索表t1的分區的索引大小

mysql> select sum(stat_value) pages,index_name,sum(stat_value)*@@innodb_page_size size

-> from mysql.innodb_index_stats where table_name like’t1#P%’ and database_name=‘mysql’ and stat_name=‘size’ group by index_name;

Empty set (0.01 sec)

MySQL 數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。