Movatterモバイル変換

Theinnodb_stats_auto_recalc variable, which is enabled by default, controls whether statistics are calculated automatically when a table undergoes changes to more than 10% of its rows. You can also configure automatic statistics recalculation for individual tables by specifying theSTATS_AUTO_RECALC clause when creating or altering a table.

Because of the asynchronous nature of automatic statistics recalculation, which occurs in the background, statistics may not be recalculated instantly after running a DML operation that affects more than 10% of a table, even wheninnodb_stats_auto_recalc is enabled. Statistics recalculation can be delayed by few seconds in some cases. If up-to-date statistics are required immediately, runANALYZE TABLE to initiate a synchronous (foreground) recalculation of statistics.

Ifinnodb_stats_auto_recalc is disabled, you can ensure the accuracy of optimizer statistics by executing theANALYZE TABLE statement after making substantial changes to indexed columns. You might also consider addingANALYZE TABLE to setup scripts that you run after loading data, and runningANALYZE TABLE on a schedule at times of low activity.

When an index is added to an existing table, or when a column is added or dropped, index statistics are calculated and added to theinnodb_index_stats table regardless of the value ofinnodb_stats_auto_recalc.

For a histogram withAUTO UPDATE enabled (seeHistogram Statistics Analysis), automatic recalculation of persistent statistics also causes the histogram to be updated.

innodb_stats_persistent,innodb_stats_auto_recalc, andinnodb_stats_persistent_sample_pages are global variables. To override these system-wide settings and configure optimizer statistics parameters for individual tables, you can defineSTATS_PERSISTENT,STATS_AUTO_RECALC, andSTATS_SAMPLE_PAGES clauses inCREATE TABLE orALTER TABLE statements.

All three clauses are specified in the followingCREATE TABLE example:

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;

The optimizer uses estimatedstatistics about key distributions to choose the indexes for an execution plan, based on the relativeselectivity of the index. Operations such asANALYZE TABLE causeInnoDB to sample random pages from each index on a table to estimate thecardinality of the index. This sampling technique is known as arandom dive.

Theinnodb_stats_persistent_sample_pages controls the number of sampled pages. You can adjust the setting at runtime to manage the quality of statistics estimates used by the optimizer. The default value is 20. Consider modifying the setting when encountering the following issues:

By default,InnoDB reads uncommitted data when calculating statistics. In the case of an uncommitted transaction that deletes rows from a table, delete-marked records are excluded when calculating row estimates and index statistics, which can lead to non-optimal execution plans for other transactions that are operating on the table concurrently using a transaction isolation level other thanREAD UNCOMMITTED. To avoid this scenario,innodb_stats_include_delete_marked can be enabled to ensure that delete-marked records are included when calculating persistent optimizer statistics.

Wheninnodb_stats_include_delete_marked is enabled,ANALYZE TABLE considers delete-marked records when recalculating statistics.

innodb_stats_include_delete_marked is a global setting that affects allInnoDB tables, and it is only applicable to persistent optimizer statistics.

The persistent statistics feature relies on the internally managed tables in themysql database, namedinnodb_table_stats andinnodb_index_stats. These tables are set up automatically in all install, upgrade, and build-from-source procedures.

Theinnodb_table_stats andinnodb_index_stats tables include alast_update column that shows when index statistics were last updated:

mysql> SELECT * FROM innodb_table_stats \G*************************** 1. row ***************************           database_name: sakila              table_name: actor             last_update: 2014-05-28 16:16:44                  n_rows: 200    clustered_index_size: 1sum_of_other_index_sizes: 1...

mysql> SELECT * FROM innodb_index_stats \G*************************** 1. row ***************************   database_name: sakila      table_name: actor      index_name: PRIMARY     last_update: 2014-05-28 16:16:44       stat_name: n_diff_pfx01      stat_value: 200     sample_size: 1     ...

Theinnodb_table_stats andinnodb_index_stats tables can be updated manually, which makes it possible to force a specific query optimization plan or test alternative plans without modifying the database. If you manually update statistics, use theFLUSH TABLEtbl_name statement to load the updated statistics.

Persistent statistics are considered local information, because they relate to the server instance. Theinnodb_table_stats andinnodb_index_stats tables are therefore not replicated when automatic statistics recalculation takes place. If you runANALYZE TABLE to initiate a synchronous recalculation of statistics, the statement is replicated (unless you suppressed logging for it), and recalculation takes place on replicas.

Theinnodb_table_stats table contains one row for each table. The following example demonstrates the type of data collected.

Tablet1 contains a primary index (columnsa,b) secondary index (columnsc,d), and unique index (columnse, f):

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;

After inserting five rows of sample data, tablet1 appears as follows:

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

To immediately update statistics, runANALYZE TABLE (ifinnodb_stats_auto_recalc is enabled, statistics are updated automatically within a few seconds assuming that the 10% threshold for changed table rows is reached):

mysql> ANALYZE TABLE t1;+---------+---------+----------+----------+| Table   | Op      | Msg_type | Msg_text |+---------+---------+----------+----------+| test.t1 | analyze | status   | OK       |+---------+---------+----------+----------+

Table statistics for tablet1 show the last timeInnoDB updated the table statistics (2014-03-14 14:36:34), the number of rows in the table (5), the clustered index size (1 page), and the combined size of the other indexes (2 pages).

mysql> SELECT * FROM mysql.innodb_table_stats WHERE table_name like 't1'\G*************************** 1. row ***************************           database_name: test              table_name: t1             last_update: 2014-03-14 14:36:34                  n_rows: 5    clustered_index_size: 1sum_of_other_index_sizes: 2

Theinnodb_index_stats table contains multiple rows for each index. Each row in theinnodb_index_stats table provides data related to a particular index statistic which is named in thestat_name column and described in thestat_description column. For example:

mysql> SELECT index_name, stat_name, stat_value, stat_description       FROM mysql.innodb_index_stats WHERE table_name like 't1';+------------+--------------+------------+-----------------------------------+| index_name | stat_name    | stat_value | stat_description                  |+------------+--------------+------------+-----------------------------------+| PRIMARY    | n_diff_pfx01 |          1 | a                                 || PRIMARY    | n_diff_pfx02 |          5 | a,b                               || PRIMARY    | n_leaf_pages |          1 | Number of leaf pages in the index || PRIMARY    | size         |          1 | Number of pages in the index      || 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                           || i1         | n_leaf_pages |          1 | Number of leaf pages in the index || i1         | size         |          1 | Number of pages in the index      || i2uniq     | n_diff_pfx01 |          2 | e                                 || i2uniq     | n_diff_pfx02 |          5 | e,f                               || i2uniq     | n_leaf_pages |          1 | Number of leaf pages in the index || i2uniq     | size         |          1 | Number of pages in the index      |+------------+--------------+------------+-----------------------------------+

Thestat_name column shows the following types of statistics:

To further illustrate then_diff_pfxNN statistic, which provides cardinality data, consider once again thet1 table example that was introduced previously. As shown below, thet1 table is created with a primary index (columnsa,b), a secondary index (columnsc,d), and a unique index (columnse,f):

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;

After inserting five rows of sample data, tablet1 appears as follows:

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

When you query theindex_name,stat_name,stat_value, andstat_description, wherestat_name LIKE 'n_diff%', the following result set is returned:

mysql> SELECT index_name, stat_name, stat_value, stat_description       FROM mysql.innodb_index_stats       WHERE table_name like 't1' 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              |+------------+--------------+------------+------------------+

For thePRIMARY index, there are twon_diff% rows. The number of rows is equal to the number of columns in the index.

For the secondary index (i1), there are fourn_diff% rows. Only two columns are defined for the secondary index (c,d) but there are fourn_diff% rows for the secondary index becauseInnoDB suffixes all nonunique indexes with the primary key. As a result, there are fourn_diff% rows instead of two to account for the both the secondary index columns (c,d) and the primary key columns (a,b).

For the unique index (i2uniq), there are twon_diff% rows.

You can retrieve the index size for tables, partitions, or subpartitions can using theinnodb_index_stats table. In the following example, index sizes are retrieved for tablet1. For a definition of tablet1 and corresponding index statistics, seeSection 17.8.10.1.6, “InnoDB Persistent Statistics Tables Example”.

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 stat_name = 'size' GROUP BY index_name;+-------+------------+-------+| pages | index_name | size  |+-------+------------+-------+|     1 | PRIMARY    | 16384 ||     1 | i1         | 16384 ||     1 | i2uniq     | 16384 |+-------+------------+-------+

For partitions or subpartitions, you can use the same query with a modifiedWHERE clause to retrieve index sizes. For example, the following query retrieves index sizes for partitions of tablet1:

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 stat_name = 'size' GROUP BY index_name;