show index from 及analyze table 詳解
- 2019 年 10 月 4 日
- 筆記
內容參考：官方文檔、老葉茶館、姜承堯的InnoDB存儲引擎2
https://mp.weixin.qq.com/s/1MsyxhtG6Zk3Q9gIV2QVbA
https://dev.mysql.com/doc/refman/5.7/en/show-index.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-analyze-table-complexity.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-statistics-estimation.html
show index from tb1 G 或者使用select * from information_schema.STATISTICS G 都可以列出表上面存在哪些索引及其它相關資訊。
創建一個測試表：
CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(20) CHARACTER SET latin1 NOT NULL DEFAULT '', `age` int(11) DEFAULT NULL, `sex` enum('F','M') CHARACTER SET latin1 DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_name` (`name`), KEY `idx_name_age` (`name`,`age`), KEY `idx_name_sub` (`name`(5)) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 ; INSERT INTO t1 (name,age,sex) VALUES('zhang san',24,'F'); INSERT INTO t1 (name,age,sex) VALUES('li si',26,'F'); INSERT INTO t1 (name,age,sex) VALUES('wang wu',20,'M'); INSERT INTO t1 (name,age,sex) VALUES('zhao liu',34,'M'); INSERT INTO t1 (name,age,sex) VALUES('John',64,'F'); show index from t1 ;
+---------+--------------+--------------+----------------+---------------+-------------+---------------+------------+----------+--------+--------------+-----------+-----------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | |---------+--------------+--------------+----------------+---------------+-------------+---------------+------------+----------+--------+--------------+-----------+-----------------| | t1 | 0 | PRIMARY | 1 | id | A | 5 | <null> | <null> | | BTREE | | | | t1 | 1 | idx_name | 1 | name | A | 5 | <null> | <null> | | BTREE | | | | t1 | 1 | idx_name_age | 1 | name | A | 5 | <null> | <null> | | BTREE | | | | t1 | 1 | idx_name_age | 2 | age | A | 5 | <null> | <null> | YES | BTREE | | | | t1 | 1 | idx_name_sub | 1 | name | A | 5 | 5 | <null> | | BTREE | | | +---------+--------------+--------------+----------------+---------------+-------------+---------------+------------+----------+--------+--------------+-----------+-----------------
詳解：
1.Table
表的名稱。
2.Non_unique
如果索引不能包括重複詞，則為0。如果可以，則為1。
3.Key_name
索引的名稱。
4.Seq_in_index
索引中的列序列號，從1開始。
5.Column_name
列名稱。
6.Collation
列以什麼方式存儲在索引中。在MySQL中，有值『A』（升序）或NULL（無分類）。[InnoDB都是A，Heap表都是NULL]
7.Cardinality
非常關鍵的一個參數。表示的是索引中唯一值的數目的估計值。
Cardinality/n_rows_in_table的值應儘可能接近1，如果非常小，那麼用戶需要考慮是否可以刪除該索引。
8.Sub_part
如果列只是被部分地編入索引，則為被編入索引的字元的數目。如果整列被編入索引，則為NULL。
9.Packed
指示關鍵字如何被壓縮。如果沒有被壓縮，則為NULL。
10.Null
索引的列中含有NULL。含有NULL則為YES。如果沒有，則這裡顯示為空。
11.Index_type
索引的類型（BTREE, FULLTEXT, HASH, RTREE）。這裡是InnoDB存儲引擎，所以顯示的都是BTREE
12.Comment
多種評註。
上面這一堆的狀態值中，最重要的就是Cardinality。
Cardinality 值：
表的cardinality（可以翻譯為「散列程度」），優化器會根據這個值來判斷是否使用這個索引。但是這個值不是實時更新的，因為實時的話代價太大了，因此這個值不是太準確，只是個估值。
如果cardinality大大少於數據的實際散列程度，那麼索引就基本失效了。
一般經驗是，在訪問表中很少一部分時使用B+樹索引才會有意義。對應性別、地區、這些欄位，它們可取值的範圍很小，稱為低選擇性。如：
SELECT* FROM students WHERE sex='M';
這種查詢的話，在大量樣本的情況下一般能返回50% 的數據，這時添加B+樹索引是完全沒有必要的。
相反，如果某個欄位的取值範圍很廣，幾乎沒有重複，即屬於高選擇性，則此時建議使用B+樹索引。
例如，對應姓名欄位，基本上在一個應用中不允許重名的出現。
在實際應用中，cardinality/n_rows_in_table應儘可能地接近1。如果非常小，那麼用戶需要考慮是否還有必要創建這個索引了。
故在訪問高選擇性屬性的欄位並從表中去除很少一部分數據時，對這個欄位添加B+樹索引是非常有必要的。
Cardinality 統計值的更新：
MySQL對Cardinality的統計是放在存儲引擎層進行的。
在生產環境，索引的更新操作可能會非常的頻繁。如果每次索引在發生操作時就對其進行Cardinality統計的話，會給資料庫造成很大的負擔。
假如一張表數據非常大，對其進行一次Cardinality統計可能要花費很長的時間，這對於生產環境而言，是不可接受的。
因此，資料庫對於Cardinality的統計都是通過取樣(Sample)的方法來完成的。
在InnoDB存儲引擎中，Cardinality統計資訊的更新發生在兩個操作中：INSERT、UPDATE。但是我們不可能每次變動就去更新Cardinality，這樣消耗太大了。
因此，InnoDB存儲引擎內部對更新Cardinality資訊的策略為：
1、 表中1/16的數據已發生過變化。
2、 stat_modified_counter > 20 0000 0000 【20億行】
第一種策略是自從上次統計Cardinality資訊後，表中1/16的數據已經發生過變化，這時需要更新Cardinality資訊。
第二種情況考慮的是，如果對表中某一行數據頻繁地更新操作，這時表中的數據實際上並沒有增加，實際發生變化的還是這一樣數據，則第一種更新策略就無法適用這種情況。故在InnoDB存儲引擎內部有一個計數器stat_modified_counter，用來表示發生變化的次數，當stat_modified_counter大於20 0000 0000時，則同樣需要更新Cardinality資訊。
InnoDB存儲引擎內部是如何進行Cardinality資訊的統計和更新操作的？
方法如下：
1、取得B+樹所以中葉子節點的數量，記為A。
2、InnoDB存儲引擎隨機對8個葉子節點(leaf page)進行取樣，統計每個頁不同記錄的個數，記為P1，P2 … P8。
3、估算出的Cardinality值為：(P1+P2…+P8) * A/8
因為是隨機取8個葉子節點做的統計，因此每次算出的Cardinality可能都不一樣。如下我從脫敏的資料庫摘錄的例子：
> use Mobile; > show index from wx_tableG *************************** 1. row *************************** Table: wx_table Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: Id Collation: A Cardinality: 39129 #第一次計算的Cardinality結果。表示的是存儲引擎估算表中有多少個不同的值 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 1 row in set (0.00 sec) > analyze table wx_table; #手工執行下，以便更新 Cardinality 值，不然要等到InnoDB的刷新要等好久。 +--------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------------+---------+----------+----------+ | Mobile.wx_table | analyze | status | OK | +--------------------+---------+----------+----------+ 1 row in set (0.01 sec) > show index from wx_tableG *************************** 1. row *************************** Table: wx_table Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: Id Collation: A Cardinality: 41417 第二次計算的Cardinality結果 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 1 row in set (0.00 sec)
如果我們再次執行 analyze table wx_table; 然後執行show index from wx_tableG 可以發現Cardinality的值又變了。
另外，如果用戶不管怎麼刷新，看到的Cardinality值都是不變的。一般這種情況發生在表足夠小，表的葉子節點數小於或等於8。這樣無論怎樣隨機取樣，都會全部取樣，算出的Cardinality總是一樣的。
與之相關的參數有：
>show VARIABLES like 'innodb_stats%'; +--------------------------------------+-------------+ | Variable_name | Value | |--------------------------------------+-------------| | innodb_stats_auto_recalc | ON | | innodb_stats_method | nulls_equal | | innodb_stats_on_metadata | OFF | | innodb_stats_persistent | ON | | innodb_stats_persistent_sample_pages | 20 | | innodb_stats_sample_pages | 8 | | innodb_stats_transient_sample_pages | 8 | +--------------------------------------+-------------+
innodb_stats_sample_pages
設置取樣的頁的數量，默認是8
MySQL5.6.3開始已經廢棄，改為使用innodb_stats_transient_sample_pages
innodb_stats_method
判斷如何對待索引中出現的NULL值記錄。
默認是nulls_equal，表示將NULL值記錄視為相等的記錄。
其有效值還有nulls_unequal和nulls_ignored。 nulls_unequal表示將NULL值記錄視為不同的記錄，nulls_ignored表示忽略NULL值記錄。
舉例子：
假如取樣的8個頁中索引記錄值為 NULL、NULL、1、2、2、3、3、3。
如果設置innodb_stats_method=nulls_equal，則Cardinality為4; 【取樣值：NULL、1、2、3】
如果設置innodb_stats_method=nulls_unequal，則Cardinality為5; 【取樣值：NULL、NULL、1、2、3】
如果設置innodb_stats_method=nulls_ignored，則Cardinality為3; 【取樣值：1、2、3】
注意：
當我們執行analyze table xxx; show table status; show index from tb_name; 以及訪問information_schema架構下的表tables和statistics時會導致InnoDB存儲引擎去重新計算索引的Cardinality值。
若表中存在多個輔助索引時，執行上述這些操作可能會非常慢，雖然用戶可能並不希望去更新Cardinality值。
在innodb1.2後，對這些參數又做了下擴展及修改：
innodb_stats_persistent 默認ON
控制是否使用永久化的統計數據（也就是是否將analyze table計算出的Cardinality值存放到磁碟上)。
設置為ON的好處是可以減少重新計算每個索引的Cardinality值，例如當MySQL資料庫重啟時。
此外，用戶也可通過命令create table和alter table的選項STATS_PERSISTENT來對每張表進行控制。
例如：
關閉某張表的取樣統計：
CREATE TABLE `t2` (a int(11) NOT NULL AUTO_INCREMENT,b int(11) DEFAULT NULL, PRIMARY KEY (a), KEY idx_b (b)) ENGINE=InnoDB STATS_PERSISTENT=0;
手動設置取樣的page數量：
CREATE TABLE `t3` (a int(11) NOT NULL AUTO_INCREMENT,b int(11) DEFAULT NULL, PRIMARY KEY (a), KEY idx_b (b)) ENGINE=InnoDB STATS_SAMPLE_PAGES=30;
永久化的統計數據存儲在 mysql.innodb_index_stats 和 mysql.innodb_table_stats 中，官方文檔說統計數據並非實時的，也就是收集統計數據會滯後幾分鐘，如果想要及時的更新統計可以執行analyze table。
innodb_stats_persistent_sample_pages 默認是20
只有在 innodb_stats_persistent=ON時候，innodb_stats_persistent_sample_pages 參數的設置才會生效。 【為了獲得更準確的執行計劃，我們可以設置my.cnf裡面這個值稍微大些，如設置為32或者64】
官方文檔：
innodb_stats_persistent_sample_pages only applies when innodb_stats_persistent is enabled for a table; when innodb_stats_persistent is disabled, innodb_stats_transient_sample_pages applies instead.
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_stats_on_metadata
該參數表示analyze table更新Cardinality值時每次需要取樣的頁的數量。默認是20個頁。
增加這個值，可以提高統計資訊的精確度，同樣也能提高執行計劃的準確性，不過也相應增加了analyze table的時間，也會增加在InnoDB表上分析的I/O開銷。
innodb_stats_on_metadata 默認為OFF 【不建議設置為ON】
注意：這個參數只有在 innodb_stats_persistent=OFF 時候才會起作用。
【官方文檔：https://dev.mysql.com/doc/refman/5.7/en/innodb-statistics-estimation.html
innodb_stats_on_metadata only applies when optimizer statistics are configured to be non-persistent (when innodb_stats_persistent is disabled).】
如果這個參數是ON的話， 使用show table status、show index from tb_name、及訪問information_schema架構下的表tables和statistics時，會立即導致存儲引擎重新計算索引的Cardinality值，這個造成的影響在日常業務時間是不能接受。
innodb_stats_transient_sample_pages
表示每次取樣頁的數量。默認是8。該參數用來取代老版本裡面之前版本的參數 innodb_stats_sample_pages
只有在 某張表 STATS_PERSISTENT=0 時候， innodb_stats_transient_sample_pages 參數的設置才會生效。
官方文檔：
innodb_stats_transient_sample_pages only applies when innodb_stats_persistent is disabled for a table; when innodb_stats_persistent is enabled, innodb_stats_persistent_sample_pages applies instead.
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_stats_on_metadata
innodb_stats_auto_recalc
用於決定是否在表上存在大量更新時（超過10%的記錄更新）重新計算統計資訊。
默認打開.
如果關閉該選項，就需要在每次創建索引或者更改列之後，運行一次ANALYZE TABLE命令來更新統計資訊，否則可能選擇錯誤的執行計劃。
同樣的，也可以在CREATE TABLE/ALTER TABLE命令中指定STATS_AUTO_RECALC值。
analyze table 說明：
老葉部落格：https://mp.weixin.qq.com/s/1MsyxhtG6Zk3Q9gIV2QVbA
官網：https://dev.mysql.com/doc/refman/5.7/en/innodb-analyze-table-complexity.html
ANALYZE TABLE 作用：
ANALYZE TABLE 會統計索引分布資訊，並將結果持久化存儲；
對於 MyISAM 表，相當於執行了一次 myisamchk –analyze；
支援 InnoDB、NDB、MyISAM 等存儲引擎，但不支援 視圖（view）；
ANALYZE TABLE也可以用在表分區上；
對InnoDB、MyISAM表執行 ANALYZE TABLE 時，會加上讀鎖（read lock）；
執行 ANALYZE TABLE 會記錄binlog。（這是合理的，因為索引分析這個操作，在MASTER端執行完後，SLAVE端也是需要的）
ANALYZE TABLE 代價估算：
影響代價因素：
innodb_stats_persistent_sample_pages定義值大小；
表中索引數多少；
表中分區數多少。
analye table的代價 = innodb_stats_persistent_sample_pages * 索引數 * 分區數
而更嚴謹的計算公式見下：
O(n_sample * (n_cols_in_uniq_i + n_cols_in_non_uniq_i + n_cols_in_pk * (1 + n_non_uniq_i)) * n_part)
各項指標解釋：
n_sample
採集的data page數量
is the number of pages sampled (defined by innodb_stats_persistent_sample_pages)
n_cols_in_uniq_i
所有唯一索引（不含主鍵索引）中的列總數
is total number of all columns in all unique indexes (not counting the primary key columns)
n_cols_in_non_uniq_i
所有普通索引中的列總數
is the total number of all columns in all non-unique indexes
n_cols_in_pk
主鍵索引中的列總數（若未顯式定義主鍵，則相當於只有一列的ROWID）
is the number of columns in the primary key (if a primary key is not defined, InnoDB creates a single column primary key internally)
n_non_uniq_i
非唯一索引數量
is the number of non-unique indexes in the table
n_part
表分區數量
is the number of partitions. If no partitions are defined, the table is considered to be a single partition.
mysql官網的實例：
USE test; CREATE TABLE t ( a INT, b INT, c INT, d INT, e INT, f INT, g INT, h INT, PRIMARY KEY (a, b), UNIQUE KEY i1uniq (c, d), KEY i2nonuniq (e, f), KEY i3nonuniq (g, h) ); SELECT index_name, stat_name, stat_description FROM mysql.innodb_index_stats WHERE database_name='test' AND table_name='t' AND stat_name like 'n_diff_pfx%'; +------------+--------------+------------------+ | index_name | stat_name | stat_description | +------------+--------------+------------------+ | PRIMARY | n_diff_pfx01 | a | | PRIMARY | n_diff_pfx02 | a,b | | i1uniq | n_diff_pfx01 | c | | i1uniq | n_diff_pfx02 | c,d | | i2nonuniq | n_diff_pfx01 | e | | i2nonuniq | n_diff_pfx02 | e,f | | i2nonuniq | n_diff_pfx03 | e,f,a | | i2nonuniq | n_diff_pfx04 | e,f,a,b | | i3nonuniq | n_diff_pfx01 | g | | i3nonuniq | n_diff_pfx02 | g,h | | i3nonuniq | n_diff_pfx03 | g,h,a | | i3nonuniq | n_diff_pfx04 | g,h,a,b | +------------+--------------+------------------+
上面這個結果看起來有點奇怪是不是，其實沒錯，先科普幾點知識：
所有的普通索引，實際物理存儲時，都要包含主鍵列的，也就是所謂的 index extensions 特性；
統計索引資訊時，是根據最左原則，要統計各種組合的。比如(a,b) 索引，要統計(a), (a,b), (a,b,pk) 三種資訊，而不是只統計(a,b)這個資訊；
不過，在 mysql.innodb_index_stats 中存儲統計資訊時，是不統計唯一索引後面存儲主鍵列資訊的，非唯一普通索引後存儲主鍵列資訊則會被統計進去；
因此，上面 mysql.innodb_index_stats 中存儲的統計結果是正確的。
我們再回來看下索引統計的代價公式，像下面這樣計算：
– n_sample，採集的data page數量，值為 20（默認值）；
– n_cols_in_uniq_i，所有唯一索引（不含主鍵索引）中的列總數，值為 2（c和d）
– n_cols_in_non_uniq_i，所有普通索引中的列總數，值為 4 （e、f、g、h）
– n_cols_in_pk，主鍵索引中的列總數（若未顯式定義主鍵，則相當於只有一列的ROWID），值為 2 (a、b)
– n_non_uniq_i，非唯一索引數量，值為 2 （注意，這裡指的是索引的數量，而不是不是列的數量。因此這裡是i2nonuniq、i3nonuniq）
– n_part，表分區數量，值為 1（沒有表分區，值為1，而不是0）。
那麼根據公式最終需要掃描的data page數結果就是：
20 * (2 + 4 + 2 * (1 + 2)) * 1 = 240 （單位是pages）
實際需要讀取的位元組數則是：240 * 16*1024 = 3932160 （即 3.84M）
當然了，要讀取的data page，有可能已經在buffer pool中了，因此並不全是物理讀。
從中，我們也可以看到，這個代價和表的數據量並無直接關係。
不過，當表數量越大時，聚集索引的 B+ 樹也越大，搜索代價肯定也越大。
