mysql innodb 配置_MySQL InnoDB配置统计信息

最新推荐文章于 2022-03-28 18:41:22 发布

bymbrofeng

最新推荐文章于 2022-03-28 18:41:22 发布

阅读量262

点赞数

文章标签： mysql innodb 配置

本文链接：https://blog.csdn.net/weixin_35537635/article/details/113160871

版权

MySQL InnoDB配置统计信息

本文档介绍如何为InnoDB表配置持久性和非持久性统计信息。

持久化统计信息在服务器重新启动时保持不变，从而使执行计划和查询性能更加稳定。持久化统计信息还提供了控制和灵活性以及这些额外的好处：

可以使用innodb_stats_auto_recalc配置选项来控制在对表进行实质性更改后是否自动更新统计信息。

可以将STATS_PERSISTENT，STATS_AUTO_RECALC和STATS_SAMPLE_PAGES子句与CREATE TABLE和ALTER TABLE语句一起使用，以配置各个表的优化程序统计信息。

可以在mysql.innodb_table_stats和mysql.innodb_index_stats表中查询优化程序统计信息数据。

可以查看mysql.innodb_table_stats和mysql.innodb_index_stats表的last_update列，以查看最后一次更新统计信息的时间。

在不需要修改数据库的情况下，可以手动修改mysql.innodb_table_stats和mysql.innodb_index_stats表，以强制执行特定的执行计划或测试备用执行计划。

默认情况下启用持久优化程序统计信息功能innodb_stats_persistent = ON。

非持久化统计信息在每次重新启动服务器和其他一些操作后清除，并在下一个表访问时重新计算。因此，在重新计算统计数据时可能会产生不同的估计值，从而导致执行计划中的不同选择和查询性能的变化。

本文档还提供了有关估计ANALYZE TABLE复杂性的信息，这在尝试实现精确统计信息和ANALYZE TABLE执行时间之间的平衡时可能很有用。

1. 配置持久化(Persistent)统计信息参数

innodb_stats_persistent

参数含义：是否启用持久化统计信息功能

默认值：ON

持久化统计信息功能通过将统计信息存储到磁盘并使其在服务器重新启动期间保持不变来提高执行计划的稳定性，以便优化器更有可能每次为给定查询做出一致的选择。

当innodb_stats_persistent = ON或使用STATS_PERSISTENT = 1创建或更改单个表时，统计信息将持久保存到磁盘。innodb_stats_persistent默认启用。

要恢复使用非持久化统计信息，可以使用ALTER TABLE tbl_name STATS_PERSISTENT = 0语句修改表。

1.1 配置自动触发更新统计信息参数

innodb_stats_auto_recalc

参数含义：是否自动触发更新统计信息

触发阈值：

表修改时，确认变化的数据是否超过10%，超过自动收集统计信息

表，索引统计信息是持久化存储

默认值：ON

由于自动统计信息重新计算(发生在后台)是异步，在运行影响超过10％的表的DML操作时(即使innodb_stats_auto_recalc启用后)，可能不会立即重新计算统计信息。在某些情况下，统计重新计算可能会延迟几秒钟(10s)。如果在更改表的重要部分之后立即需要最新统计信息，请运行ANALYZE TABLE以启动统计信息的同步(前台)重新计算。

如果禁用了innodb_stats_auto_recalc，请在对索引列进行实质性更改后，通过为每个适用的表发出ANALYZE TABLE语句来确保统计信息的准确性。

在表上添加索引或者添加删除索引中的列时，将自动计算索引统计信息并将其添加到innodb_index_stats表，不受innodb_stats_auto_recalc的值影响。

1.2 配置每张表的统计参数

innodb_stats_persistent，innodb_stats_auto_recalc和innodb_stats_persistent_sample_pages是全局配置选项。

若要覆盖这些系统范围的设置并为各个表配置统计信息参数，可以在CREATE TABLE或ALTER TABLE语句中定义STATS_PERSISTENT，STATS_AUTO_RECALC和STATS_SAMPLE_PAGES子句。

STATS_PERSISTENT

指定是否为InnoDB表启用持久统计信息。

DEFAULT：表示表的持久统计信息设置由innodb_stats_persistent配置选项确定

1：表示启用表的持久统计信息

0：关闭此功能

STATS_AUTO_RECALC

指定是否自动重新计算InnoDB表的持久统计信息。

DEFAULT：表示表的持久统计信息设置由innodb_stats_auto_recalc配置选项确定

1：表示表中10％的数据发生更改时将重新计算统计信息

0：禁用自动重新计算此表

STATS_SAMPLE_PAGES

指定在估计索引列的基数和其他统计信息时要采样的索引页数

示例

CREATE TABLE`t1` (

`id`int(8) NOT NULLauto_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;

1.3 配置InnoDB优化器统计信息的采样页数参数

innodb_stats_persistent_sample_pages

参数含义：配置持久化统计信息采样的页数

默认值：20

在什么情况下需要修改此参数呢：

统计信息不够准确，优化器选择次优计划

如果确定统计信息不够准确，则应增加innodb_stats_persistent_sample_pages的值，直到统计估计值足够准确。但是，过多地增加innodb_stats_persistent_sample_pages可能会导致ANALYZE TABLE运行缓慢。

ANALYZE TABLE太慢

在这种情况下，应减少innodb_stats_persistent_sample_pages，直到ANALYZE TABLE执行时间可以接受。但是，过多地降低该值可能会导致生成不准确的统计信息和次优查询执行计划的问题。

1.4 包含Delete-marked的记录参数

innodb_stats_include_delete_marked

参数含义：在MySQL 5.7.16中引入的此参数，默认为不启用，表示在未提交的事务有从表中删除行，则InnoDB在收集统计信息时，将会排除这些delete_marked行。这可能会导致除READ UNCOMMITTED之外的事务隔离级别的事务，运行的不是最佳的执行计划。

为了避免这种情况，可以启用innodb_stats_include_delete_marked以确保在计算持久化统计信息时InnoDB包含Delete-marked记录。

默认值：OFF

1.5 InnoDB持久化统计表

持久化统计信息功能依赖于mysql数据库中的内部表，名为innodb_table_stats和innodb_index_stats。

这些表在所有安装，升级和源代码构建过程中自动设置。

mysql.innodb_table_stats

+--------------------------+---------------------+------+-----+-------------------+-----------------------------+--------------------------

mysql.innodb_index_stats

+------------------+---------------------+------+-----+-------------------+-----------------------------+----------------------------

start_name:

为size时：此时stat_value显示索引的page数量。

为n_leaf_pages时：此时stat_value显示叶子节点的数量。

为n_diff_pfxNN时：显示索引字段上唯一值的数量

【注意】：

innodb_table_stats和innodb_index_stats表是普通表，可以手动更新。

如果手动更新统计信息，请发出FLUSH TABLE tbl_name命令以使MySQL重新加载更新的统计信息。

1.6 InnoDB持久化统计表示例

创建表t1包含主索引(列a，b)二级索引(列c，d)和唯一索引(列e，f)：

CREATE TABLEt1 (

aINT, b INT, c INT, d INT, e INT, f INT,PRIMARY KEY (a, b), KEY i1 (c, d), UNIQUE KEYi2uniq (e, f)

) ENGINE=INNODB;

插入5行数据后

insert into t1 select 1,1,10,11,100,101;insert into t1 select 1,2,10,11,200,102;insert into t1 select 1,3,10,11,100,103;insert into t1 select 1,4,10,12,200,104;insert into t1 select 1,5,10,12,100,105;SELECT * FROMt1;+---+---+------+------+------+------+

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

+---+---+------+------+------+------+

要立即更新统计信息，请运行ANALYZE TABLE。(如果启用了innodb_stats_auto_recalc，则假定已达到更改的表行的10％阈值，则会在几秒钟内自动更新统计信息)

mysql> ANALYZE TABLEt1;+---------+---------+----------+----------+

+---------+---------+----------+----------+

+---------+---------+----------+----------+

1 row in set (0.02 sec)

表最后一次收集统计信息时间为2018-08-29 11:10:03,表的行数为 5，主键索引大小为 1page，其他索引的总大小为 2 page。

mysql> select * from mysql.innodb_table_stats t where t.database_name='test' and t.table_name='t1'\G*************************** 1. row ***************************database_name: test

table_name: t1

last_update:2018-08-29 11:10:03n_rows:5clustered_index_size:1sum_of_other_index_sizes:2

1 row in set (0.00 sec)

innodb_index_stats表包含每个索引的多行。 innodb_index_stats表中的每一行都提供与特定索引统计信息相关的数据，该统计信息在stat_name列中命名并在stat_description列中进行了描述。例如：

mysql> select * from mysql.innodb_index_stats t where t.database_name='test' and t.table_name='t1';+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+

+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+

| test | t1 | PRIMARY | 2018-08-29 11:10:03 | n_diff_pfx01 | 1 | 1 | a |

| test | t1 | PRIMARY | 2018-08-29 11:10:03 | n_diff_pfx02 | 5 | 1 | a,b |

| test | t1 | i1 | 2018-08-29 11:10:03 | n_diff_pfx01 | 1 | 1 | c |

| test | t1 | i1 | 2018-08-29 11:10:03 | n_diff_pfx02 | 2 | 1 | c,d |

| test | t1 | i1 | 2018-08-29 11:10:03 | n_diff_pfx03 | 2 | 1 | c,d,a |

| test | t1 | i1 | 2018-08-29 11:10:03 | n_diff_pfx04 | 5 | 1 | c,d,a,b |

| test | t1 | i1 | 2018-08-29 11:10:03 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |

| test | t1 | i1 | 2018-08-29 11:10:03 | size | 1 | NULL | Number of pages in the index |

| test | t1 | i2uniq | 2018-08-29 11:10:03 | n_diff_pfx01 | 2 | 1 | e |

| test | t1 | i2uniq | 2018-08-29 11:10:03 | n_diff_pfx02 | 5 | 1 | e,f |

+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+

14 rows in set (0.00 sec)

stat_name列显示以下类型的统计信息：

如果stat_name = size，则stat_value列显示索引中的总大小(单位page)。

如果stat_name = n_leaf_pages，则stat_value列显示索引中的叶子页数。

如果stat_name = n_diff_pfx01，stat_value列显示索引第一列中的不同值的数量。当 stat_name = n_diff_pfx02，stat_value列显示索引前两列中的不同值的数量，依此类推。此外，在stat_name = n_diff_pfxNN的情况下，stat_description列显示了计算的索引列。

进一步说明基数数据的n_diff_pfxNN统计量：

表t1使用主索引(列a，b)，辅助索引(列c，d)和唯一索引(列e，f)。

对于PRIMARY索引，有两个n_diff％行。行数等于索引中的列数。

【注意】

对于非唯一索引，索引会附加主键列。

index_name = PRIMARY和stat_name = n_diff_pfx01

stat_value为1，表示索引的第一列中存在单个不同的值(列a)。通过查看表t1中的列a中的数据来确认列a中的不同值的数量，其中存在单个不同的值(1)。计数列(a)显示在结果集的stat_description列中。

index_name = PRIMARY和stat_name = n_diff_pfx02

stat_value为5，表示索引(a，b)的两列中有五个不同的值。通过查看表t1中列a和b中的数据来确认列a和b中的不同值的数量，其中有五个不同的值：(1,1)，(1,2)，(1,3) ，(1,4)和(1,5)。计数列(a，b)显示在结果集的stat_description列中。

对于二级索引(i1)，有四个n_diff％行

对于二级索引(i1)，有四个n_diff％行。仅为辅助索引(c，d)定义了两列，但是辅助索引有四个n_diff％行，因为InnoDB使用主键为所有非唯一索引添加后缀。

index_name = i1和stat_name = n_diff_pfx01

stat_value为1，表示索引的第一列(列c)中存在单个不同的值。通过查看表t1中列c中的数据来确认列c中的不同值的数量，其中存在单个不同的值：(10)。计数列(c)显示在结果集的stat_description列中。

index_name = i1和stat_name = n_diff_pfx02

stat_value为2，表示索引(c，d)的前两列中有两个不同的值。通过查看表t1中列c和d中的数据来确认列c和d中的不同值的数量，其中存在两个不同的值：(10,11)和(10,12)。计数列(c，d)显示在结果集的stat_description列中。

index_name = i1和stat_name = n_diff_pfx03

stat_value为2，表示索引的前三列中有两个不同的值(c，d，a)。通过查看表c1中的列c，d和a中的数据来确认列c，d和a中的不同值的数量，其中有两个不同的值：(10,11,1)和(10， 12,1)。计数列(c，d，a)显示在结果集的stat_description列中。

index_name = i1和stat_name = n_diff_pfx04

stat_value为5，表示索引的四列中有五个不同的值(c，d，a，b)。通过查看表t1中列c，d，a和b中的数据来确认列c，d，a和b中的不同值的数量，其中有五个不同的值：(10,11,1,1)，(10,11,1,2)，(10,11,1,3)，(10,12,1,4)和(10,12,1,5)。计数列(c，d，a，b)显示在结果集的stat_description列中。

对于唯一索引(i2uniq)，有两个n_diff％行

index_name = i2uniq和stat_name = n_diff_pfx01

stat_value为2，表示索引的第一列中有两个不同的值(列e)。通过查看表t1中的列e中的数据来确认列e中的不同值的数量，其中存在两个不同的值：(100)和(200)。计数列(e)显示在结果集的stat_description列中。

index_name = i2uniq和stat_name = n_diff_pfx02

stat_value为5，表示索引的两列中有五个不同的值(e，f)。通过查看表t1中的列e和f中的数据来确认列e和f中的不同值的数量，其中存在五个不同的值：(100,101)，(200,102)，(100,103)，(200,104)和(100105)。计数列(e，f)显示在结果集的stat_description列中。

1.7 使用innodb_index_stats表查询索引大小

可以使用innodb_index_stats表查询表，分区或子分区的索引大小，例如：

SELECT SUM(stat_value) pages,

index_name,SUM(stat_value) * @@innodb_page_sizesizeFROMmysql.innodb_index_statsWHERE table_name = 't1' AND database_name = 'test' AND stat_name = 'size'

GROUP BYindex_name;+-------+------------+-------+

| pages | index_name | size |

+-------+------------+-------+

| 1 | PRIMARY | 16384 |

| 1 | i1 | 16384 |

| 1 | i2uniq | 16384 |

+-------+------------+-------+

3 rows in set (0.00 sec)

对于分区或子分区，可以使用修改的WHERE子句的相同查询来查询索引大小。

例如，以下查询检索表t1的分区的索引大小：

SELECT SUM(stat_value) pages,

index_name,SUM(stat_value) * @@innodb_page_sizesizeFROMmysql.innodb_index_statsWHERE table_name LIKE 't1#P%' AND stat_name = 'size'

GROUP BY index_name;

2. 配置非持久化(Non-Persistent)统计信息参数

当innodb_stats_persistent = OFF或使用STATS_PERSISTENT = 0创建或更改单张表时，统计信息不会保留到磁盘。相反，统计信息存储在内存中，并在服务器关闭时丢失。某些业务和某些条件下也会定期更新统计数据。

从MySQL 5.6.6开始，默认情况下，统计信息会持久保存到磁盘，由innodb_stats_persistent配置选项启用。

2.1 优化器统计信息更新

innodb_stats_on_metadata

参数含义：表示是否InnoDB在(如SHOW TABLE STATUS)或访问INFORMATION_SCHEMA.TABLES或INFORMATION_SCHEMA.STATISTICS)操作期间更新统计信息。

默认值：OFF

保留禁用的设置可以提高具有大量表或索引的模式的访问速度。它还可以提高涉及InnoDB表的查询的执行计划的稳定性。

如果启用此参数，在以下情况下更新非持久化统计信息：

运行ANALYZE TABLE

运行SHOW TABLE STATUS，SHOW INDEX或查询INFORMATION_SCHEMA.TABLES或INFORMATION_SCHEMA.STATISTICS表并启用innodb_stats_on_metadata选项。

默认情况下，在MySQL 5.6.6中启用持久化统计信息时，innodb_stats_on_metadata的默认设置已更改为OFF。启用innodb_stats_on_metadata可能会降低大表或索引的访问速度，并降低涉及InnoDB表的查询的执行计划的稳定性。使用SET语句全局配置innodb_stats_on_metadata。

SET GLOBAL innodb_stats_on_metadata=ON

【注意】

innodb_stats_on_metadata仅在统计信息配置为非持久化时(禁用innodb_stats_persistent时)适用。

默认设置，在mysql客户端启用--auto-rehash选项的。 auto-rehash选项会导致打开所有InnoDB表，并且open table操作会导致重新计算统计信息。

要改善mysql客户端的启动时间并更新统计信息，可以使用--disable-auto-rehash选项关闭自动重新连接。 auto-rehash功能可以为交互式用户自动完成数据库，表和列名称的名称。

打开一张表

自上次更新统计信息以来，InnoDB检测到表中1/16数据已被修改。

2.2 配置采样页数参数

innodb_stats_transient_sample_pages

参数含义：表示每次随机采样页的数量

默认值：8

当innodb_stats_persistent = 0时，innodb_stats_transient_sample_pages的值会影响所有InnoDB表和索引的索引采样。更改索引样本大小时，请注意以下潜在的重大影响：

像1或2这样的小值可能导致基数估计不准确。

增加innodb_stats_transient_sample_pages值可能需要更多磁盘读取。远大于8(例如，100)的值可能导致打开表或执行SHOW TABLE STATUS所花费的时间显着减慢。

优化器可能会根据索引选择性的不同估计选择非常不同的查询计划。

3. Analyze Table复杂性度

InnoDB表的ANALYZE TABLE复杂性度决于：

采样的页数，由innodb_stats_persistent_sample_pages定义。

表中索引列的数量

分区数量。如果表没有分区，则分区数被视为1。

使用这些参数，估计ANALYZE TABLE复杂性度近似公式为：

innodb_stats_persistent_sample_pages * 表中索引列的数量 * 分区数, 通常，结果值越大，ANALYZE InnoDB TABLE的执行时间越长。

【注意】

innodb_stats_persistent_sample_pages定义在全局级别采样的页数。要设置单个表的采样页数，请使用带有CREATE TABLE或ALTER TABLE的STATS_SAMPLE_PAGES选项。

如果innodb_stats_persistent = OFF，则采样的页数由innodb_stats_transient_sample_pages定义。

有关估计ANALYZE TABLE复杂性的更深入方法，请考虑以下示例。

在Big 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列)和两个非唯一索引(每个2列)：

CREATE TABLEt (

aINT,

bINT,

cINT,

dINT,

eINT,

fINT,

gINT,

hINT,PRIMARY KEY(a, b),UNIQUE KEYi1uniq (c, d),KEYi2nonuniq (e, f),KEYi3nonuniq (g, h)

);SELECTt.index_name,

t.last_update,

t.stat_name,

t.stat_descriptionFROMmysql.innodb_index_stats tWHERE t.database_name = 'test'

AND t.table_name = 't'

AND t.stat_name LIKE 'n_diff_pfx%';+------------+---------------------+--------------+------------------+

+------------+---------------------+--------------+------------------+

| PRIMARY | 2018-08-29 13:38:37 | n_diff_pfx01 | a |

| PRIMARY | 2018-08-29 13:38:37 | n_diff_pfx02 | a,b |

| i1uniq | 2018-08-29 13:38:37 | n_diff_pfx01 | c |

| i1uniq | 2018-08-29 13:38:37 | n_diff_pfx02 | c,d |

| i2nonuniq | 2018-08-29 13:38:37 | n_diff_pfx01 | e |

| i2nonuniq | 2018-08-29 13:38:37 | n_diff_pfx02 | e,f |

| i2nonuniq | 2018-08-29 13:38:37 | n_diff_pfx03 | e,f,a |

| i3nonuniq | 2018-08-29 13:38:37 | n_diff_pfx01 | g |

| i3nonuniq | 2018-08-29 13:38:37 | n_diff_pfx02 | g,h |

| i3nonuniq | 2018-08-29 13:38:37 | n_diff_pfx03 | g,h,a |

+------------+---------------------+--------------+------------------+

根据上面显示的索引统计数据和表定义，可以确定以下值：

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。

根据公式计算扫描的叶页数：

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)

innodb_stats_persistent_sample_pages= 20n_cols_in_uniq_i= 2n_cols_in_non_uniq_i=4n_cols_in_pk= 2n_non_uniq_i= 2n_part= 1估计表t读取20 * (2 + 4 + 2 *(1 + 2) * 16384 /1024 = 3932160字节，大约4M。

bymbrofeng

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql innodb 配置_MySQL InnoDB配置统计信息

MySQL InnoDB配置统计信息本文档介绍如何为InnoDB表配置持久性和非持久性统计信息。持久化统计信息在服务器重新启动时保持不变，从而使执行计划和查询性能更加稳定。持久化统计信息还提供了控制和灵活性以及这些额外的好处：可以使用innodb_stats_auto_recalc配置选项来控制在对表进行实质性更改后是否自动更新统计信息。可以将STATS_PERSISTENT，STATS_AUTO...
复制链接

扫一扫