MySQL 配置InnoDB配置非持久優化器統計資訊引數

配置非持久優化器統計資訊引數
本節介紹如何配置非持久優化器統計資訊。當innodb_stats_persistent=OFF或使用STATS_PERSISTENT=0建立或修改單個表時，優化器統計資訊不會被持久化到磁碟。相反，統計資訊儲存在記憶體中，並且在伺服器關閉時丟失。統計資料還由某些操作在某些條件下定期更新。

從MySQL 5.6.6開始，預設情況下，優化器統計資料被持久化到磁碟上，由innodb_stats_persistent配置選項啟用。

優化器資料更新
在以下情況出現時非持久化的優化器統計資訊會被更新:
.執行analyze table

.執行show table status,show index，或者在innodb_stats_on_metadata選項被啟用時查詢information_schema.tables或information_schema.statistics表。

MySQL 5.6.6中，當持久化優化器統計資訊被啟用時，innodb_stats_on_metadata預設設定為OFF。啟用innodb_stats_on_metadata可能會降低具有大量表或索引的模式的訪問速度，並降低涉及InnoDB表的查詢的執行計劃的穩定性。innodb_stats_on_metadata使用SET語句全域性配置。set global innodb_stats_on_metadata=ON

innodb_stats_on_metadata只適用於優化器統計資訊配置為非持久化(當innodb_stats_persistent被禁用時)。

.啟動mysql客戶端時啟用--auto-rehash選項，這是預設設定。auto-rehash選項會開啟所有InnoDB表，開啟表的操作會導致統計資料重新計算。為了提高mysql客戶端的啟動和更新統計資訊時間，你可以使用--disable-auto-rehash選項關閉auto-rehash。自動auto-rehash特性允許互動使用者自動完成資料庫、表和列名的命名。

.表第一次開啟。

.InnoDB檢測到有1 / 16的表在上次統計資料更新後被修改。

配置取樣頁面數
MySQL查詢優化器使用關於鍵分佈的估計統計資訊，根據索引的相對選擇性為執行計劃選擇索引。當InnoDB更新優化器統計資料時，它會從表上的每個索引中隨機取樣，以估計索引的基數。(這種技術被稱為隨機潛水。)

為了控制統計資訊評估的質量(從而為查詢優化器提供更好的資訊)，可以使用引數innodb_stats_transient_sample_pages更改抽樣頁面的數量。預設的抽樣頁面數是8，這可能不足以產生準確的評估，導致查詢優化器的索引選擇很差。這種技術對於大型表和連線中使用的表尤其重要。對這樣的表進行不必要的全表掃描可能會造成嚴重的效能問題。

當innodb_stats_transient_sample_pages =0時，innodb_stats_persistent的值會影響所有InnoDB表和索引的索引取樣。當您更改索引樣本大小時，請注意以下潛在的重大影響。
.小值像1或2可以導致不精確的基數評估

.增加innodb_stats_transient_sample_pages的值可能需要更多的磁碟讀取。大於8(比如100)的值會導致開啟表或執行SHOW table STATUS所需的時間顯著放緩。

.優化器可能會根據索引選擇性的不同估計選擇非常不同的查詢計劃

無論innodb_stats_transient_sample_pages的值是什麼，設定該選項並保持該值。選擇一個值，它可以為資料庫中的所有表提供合理準確的估計，而不需要過多的I/O。因為除了在執行ANALYZE TABLE時，統計資料會在其他時間自動重新計算，所以增加索引樣本大小，執行ANALYZE TABLE，然後再次減少樣本大小是沒有意義的。

較小的表通常比較大的表需要更少的索引樣本。如果你的資料庫有很多大的表，考慮使用一個更大的innodb_stats_transient_sample_pages值。

評估InnoDB表analyze table的複雜度
InnoDB表的ANALYZE TABLE複雜度依賴於:
.取樣的頁面數，由innodb_stats_persistent_sample_pages定義

.表中索引列的數目

.分割槽數。如果表沒有分割槽，則認為分割槽數為1。

使用這些引數，估計ANALYZE TABLE複雜度的近似公式是
innodb_stats_persistent_sample_pages的值*表中索引的列數*分割槽數

通常，結果值越大，ANALYZE TABLE的執行時間就越長

innodb_stats_persistent_sample_pages定義了在全域性級別上取樣的頁面數量。要設定單個表的取樣頁數，請使用有STATS_SAMPLE_PAGES選項的CREATE TABLE或ALTER TABLE的語句。

如果innodb_stats_persistent=OFF，則由innodb_stats_transient_sample_pages定義取樣的頁面數

要了解估算ANALYZE TABLE複雜度的更深入的方法，請考慮以下示例:
在大O符號中，ANALYZE TABLE的複雜度被描述為:

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是取樣的頁面數(由innodb_stats_persistent_sample_pages定義)

.n_cols_in_uniq_i是所有唯一索引中所有列的總數(不計算主鍵列)

.n_cols_in_non_uniq_i是所有非唯一索引中所有列的總數

.n_cols_in_pk是主鍵中的列數(如果沒有定義主鍵，InnoDB會在內部建立一個單列主鍵)

.n_non_uniq_i是表中非唯一索引的個數

.n_part是分割槽的數量。如果沒有定義分割槽，則將表視為單個分割槽。

現在，考慮下面的表(表t)，它有一個主鍵(2列)、一個唯一索引(2列)和兩個非唯一索引(各有兩列):

mysql> 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)
    -> );
Query OK, 0 rows affected (0.13 sec)

對於上述演算法所需的列和索引資料，查詢mysql.innodb_index_stats來檢視錶t的持久索引統計資訊。n_diff_pfx%顯示了每個索引列的統計資訊。
例如，列a和列b用於計算主鍵索引。對於非唯一索引，除了使用者定義的列外，還要統計主鍵列(a,b)。

mysql> select index_name, stat_name, stat_description
    -> from mysql.innodb_index_stats
    -> where
    -> database_name='mysql' 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          |
+------------+--------------+------------------+
12 rows in set (0.01 sec)

根據上面顯示的索引統計資料和表定義，可以確定以下值:
.n_cols_in_uniq_i，不計算主鍵列的所有唯一索引中所有列的總數為2 (c和d)

.n_cols_in_non_uniq_i，所有非唯一索引中所有列的總數，為4 (e、f、g和h)

.n_cols_in_pk，主鍵中的列數是2(a和b)

.n_non_uniq_i，表中非唯一索引的數量為2 (i2nonuniq和i3nonuniq))

.n_part, 分割槽數，為1

現在可以計算innodb_stats_persistent_sample_pages *(2 + 4 + 2 *(1 + 2)) * 1來確定掃描的葉頁數。如果將innodb_stats_persistent_sample_pages設定為預設值20，並將預設的頁面大小設定為16 KiB (innodb_page_size=16384)，那麼你可以估計為表t讀取20 * 12 * 16384位元組，或者大約4 MiB。

所有4MiB可能不是從磁碟讀取的，因為一些葉頁可能已經快取在緩衝池中。