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可能不是從磁盤讀取的，因為一些葉頁可能已經緩存在緩沖池中。

MySQL

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

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