MySQL 8.0 Reference Manual（讀書筆記75節--Optimizer Statistics for InnoDB (1)）

概述

This section describes how to configure persistent and non-persistent optimizer statistics for InnoDB tables.

Persistent optimizer statistics are persisted across server restarts【意思是重啟操作，對這些數據沒有影響】, allowing for greater plan stability and more consistent query performance. Persistent optimizer statistics also provide control and flexibility with these additional benefits:

• You can use the innodb_stats_auto_recalc configuration option to control whether statistics are updated automatically after substantial changes to a table.

• You can use the STATS_PERSISTENT, STATS_AUTO_RECALC, and STATS_SAMPLE_PAGES clauses with CREATE TABLE and ALTER TABLE statements to configure optimizer statistics for individual tables.

• You can query optimizer statistics data in the mysql.innodb_table_stats and mysql.innodb_index_stats tables.---核心表

• You can view the last_update column of the mysql.innodb_table_stats and mysql.innodb_index_stats tables to see when statistics were last updated.

• You can manually modify the mysql.innodb_table_stats and mysql.innodb_index_stats tables to force a specific query optimization plan or to test alternative plans without modifying the database.

The persistent optimizer statistics feature is enabled by default (innodb_stats_persistent=ON).--預設開始

Non-persistent optimizer statistics are cleared on each server restart and after some other operations, and recomputed on the next table access. As a result, different estimates could be produced when recomputing statistics, leading to different choices in execution plans and variations in query performance.

This section also provides information about estimating ANALYZE TABLE complexity【kəmˈpleksəti 複雜性;難題;難懂;難以理解的局勢】, which may be useful when attempting to achieve a balance between accurate statistics and ANALYZE TABLE execution time.

Configuring Persistent Optimizer Statistics Parameters

The persistent optimizer statistics feature improves plan stability【stəˈbɪləti 穩定(性);穩定性;穩固(性)】 by storing statistics to disk and making them persistent across server restarts so that the optimizer is more likely to make consistent choices each time for a given query.

Optimizer statistics are persisted to disk when innodb_stats_persistent=ON or when individual tables are defined with STATS_PERSISTENT=1. innodb_stats_persistent is enabled by default.

Formerly【ˈfɔːrmərli 以前;原名;往時】, optimizer statistics were cleared when restarting the server and after some other types of operations, and recomputed on the next table access. Consequently【ˈkɑːnsɪkwentli 因此;所以】, different estimates could be produced when recalculating statistics leading to different choices in query execution plans and variation in query performance.

Configuring Automatic Statistics Calculation for Persistent Optimizer Statistics

The innodb_stats_auto_recalc variable, which is enabled by default, controls whether statistics are calculated automatically when a table undergoes【ʌndərˈɡoʊz 經歷，經受(變化、不快的事等)】 changes to more than 10% of its rows. You can also configure automatic statistics recalculation for individual tables by specifying the STATS_AUTO_RECALC clause when creating or altering a table.

Because of the asynchronous【eɪˈsɪŋkrənəs 不同時存在(或發生)的;非共時的】 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 when innodb_stats_auto_recalc is enabled. Statistics recalculation can be delayed by few seconds in some cases. If up-to-date statistics are required immediately, run ANALYZE TABLE to initiate a synchronous (foreground) recalculation of statistics.

If innodb_stats_auto_recalc is disabled, you can ensure the accuracy of optimizer statistics by executing the ANALYZE TABLE statement after making substantial changes to indexed columns. You might also consider adding ANALYZE TABLE to setup scripts that you run after loading data, and running ANALYZE 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 the innodb_index_stats table regardless of the value of innodb_stats_auto_recalc.

Configuring Optimizer Statistics Parameters for Individual Tables

innodb_stats_persistent, innodb_stats_auto_recalc, and innodb_stats_persistent_sample_pages are global variables. To override these systemwide settings and configure optimizer statistics parameters for individual tables, you can define STATS_PERSISTENT, STATS_AUTO_RECALC, and STATS_SAMPLE_PAGES clauses in CREATE TABLE or ALTER TABLE statements.

• STATS_PERSISTENT specifies whether to enable persistent statistics for an InnoDB table. The value DEFAULT causes the persistent statistics setting for the table to be determined by the innodb_stats_persistent setting. A value of 1 enables persistent statistics for the table, while a value of 0 disables the feature. After enabling persistent statistics for an individual table, use ANALYZE TABLE to calculate statistics after table data is loaded.

• STATS_AUTO_RECALC specifies whether to automatically recalculate persistent statistics. The value DEFAULT causes the persistent statistics setting for the table to be determined by the innodb_stats_auto_recalc setting. A value of 1 causes statistics to be recalculated when 10% of table data has changed. A value 0 prevents automatic recalculation for the table. When using a value of 0, use ANALYZE TABLE to recalculate statistics after making substantial changes to the table.

• STATS_SAMPLE_PAGES specifies the number of index pages to sample when cardinality and other statistics are calculated for an indexed column, by an ANALYZE TABLE operation, for 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;

Configuring the Number of Sampled Pages for InnoDB Optimizer Statistics

The optimizer uses estimated statistics about key distributions to choose the indexes for an execution plan, based on the relative selectivity of the index. Operations such as ANALYZE TABLE cause InnoDB to sample random pages from each index on a table to estimate the cardinality【kɑːrdɪˈnæləti 基數；集的勢】 of the index. This sampling technique is known as a random dive.

 The innodb_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:

1. Statistics are not accurate【ˈækjərət 精確的;準確的;準確無誤的】 enough and the optimizer chooses suboptimal【次優的;次優;次優化;次佳】  plans, as shown in EXPLAIN output. You can check the accuracy of statistics by comparing the actual cardinality of an index (determined by running SELECT DISTINCT on the index columns) with the estimates in the mysql.innodb_index_stats table.

If it is determined that statistics are not accurate enough, the value of innodb_stats_persistent_sample_pages should be increased until the statistics estimates are sufficiently accurate. Increasing innodb_stats_persistent_sample_pages too much, however, could cause ANALYZE TABLE to run slowly. --該增就增

2. ANALYZE TABLE is too slow. In this case innodb_stats_persistent_sample_pages should be decreased until ANALYZE TABLE execution time is acceptable. Decreasing the value too much, however, could lead to the first problem of inaccurate statistics and suboptimal query execution plans. --該減就減

If a balance cannot be achieved between accurate statistics and ANALYZE TABLE execution time, consider decreasing the number of indexed columns in the table or limiting the number of partitions to reduce ANALYZE TABLE complexity. The number of columns in the table's primary key is also important to consider, as primary key columns are appended to each nonunique index.

Including Delete-marked Records in Persistent Statistics Calculations

 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 than READ 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.---優化器不提供的方案不是最優,這也是種因素.

When innodb_stats_include_delete_marked is enabled, ANALYZE TABLE considers deletemarked records when recalculating statistics.

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

InnoDB Persistent Statistics Tables

The persistent statistics feature relies on the internally managed tables in the mysql database, named innodb_table_stats and innodb_index_stats. These tables are set up automatically in all install, upgrade, and build-from-source procedures. ---主要依賴著兩個系統表

表innodb_table_stats的欄位意思

The innodb_table_stats table contains one row for each table.

 表innodb_index_stats的欄位說明

The innodb_index_stats table contains multiple rows for each index. Each row in the innodb_index_stats table provides data related to a particular index statistic which is named in the stat_name column and described in the stat_description column.

The stat_name column shows the following types of statistics:---關於欄位stat_name說明

• size: Where stat_name=size, the stat_value column displays the total number of pages in the index.

• n_leaf_pages: Where stat_name=n_leaf_pages, the stat_value column displays the number of leaf pages in the index.

For nonunique indexes, InnoDB appends the columns of the primary key.

註意:The innodb_table_stats and innodb_index_stats tables include a last_update column that shows when index statistics were last updated.---如果有主鍵的話,你應該發現,針對這張表,innodb_index_stats主鍵的行對應的last_update 和  innodb_table_stats 的last_update , 是一樣的.

The innodb_table_stats and innodb_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 the FLUSH TABLE tbl_name statement to load the updated statistics.---手動觸發更新

Persistent statistics are considered local information, because they relate to the server instance. The innodb_table_stats and innodb_index_stats tables are therefore not replicated when automatic statistics recalculation takes place. If you run ANALYZE TABLE to initiate a synchronous recalculation of statistics, the statement is replicated (unless you suppressed logging for it), and recalculation takes place on replicas.---命令是實例級別的,在主從環境下,要小心.

其它說明

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

Retrieving Index Size Using the innodb_index_stats Table

 You can retrieve the index size for tables, partitions, or subpartitions can using the innodb_index_stats table.In the following example, index sizes are retrieved for table t1.

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;

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

 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;

---https://dev.mysql.com/doc/refman/8.0/en/innodb-persistent-stats.html