关于mysql:第13期表统计信息的计算

1次阅读

共计 4804 个字符，预计需要花费 13 分钟才能阅读完成。

本篇介绍 MySQL 表如何计算统计信息。表统计信息是数据库基于老本的优化器最重要的参考信息；统计信息不精确，优化器可能给出不够优化的执行打算或者是谬误的执行打算。

对统计信息的计算分为 非长久化统计信息 （实时计算）与 长久化统计信息。

非长久化统计信息

  • 统计信息没有保留在磁盘上，而是频繁的实时计算统计信息；
  • 每次对表的拜访都会从新计算其统计信息；

假如针对一张大表的频繁查问，那么每次都要从新计算统计信息，很消耗资源。

长久化统计信息

  • 把一张表在某一时刻的统计信息值保留在磁盘上；
  • 防止每次查问时从新计算；
  • 如果表更新不是很频繁，或者没有达到 MySQL 必须从新计算统计信息的临界值，可间接从磁盘上获取；
  • 即便 MySQL 服务重启，也能够疾速的获取统计信息值；
  • 统计信息的长久化能够针对全局设置也能够针对单表设置。

接下来，具体说 MySQL 统计信息如何计算，何时计算，成果评估等问题。

在 MySQL Server 层来管制是否主动计算统计信息的散布，并且来决策是长久化还是非长久化。

一、长久化统计相干参数：

  • innodb_stats_persistent：是否开启统计信息长久化，默认开启。
  • innodb_stats_auto_recalc：是否主动从新计算长久化统计信息，默认开启。

二、具体的更新策略为：

当一张表数据变动超过 10% 后，MySQL 会针对这张表统计信息的更新工夫戳做一个判断，查看最初一次更新的工夫是否超过 10 秒；如果不到 10 秒，把这张表加到一个统计信息更新队列中，到工夫了再从新计算；如果超过了 10 秒，间接从新计算，并且更新工夫戳。

目前这个超时工夫写死在 MySQL 代码里，临时不能更改。不过在某些 MySQL 分支版还能够管制这个工夫，比方 Percona。

  • innodb_stats_include_delete_marked：更新长久化统计信息时，是否会计算曾经标记为删除的行。

    默认是敞开的，会获取未提交的脏数据。开启这个选项，MySQL 计算统计信息时只会思考曾经提交的数据。

  • innodb_stats_persistent_sample_pages：用于更新长久化索引散布或者其余统计信息的随机基数页，默认 20 个。

    页数越多，统计信息也就越精确，也就有助于查问优化器抉择最优的查问打算。

什么时候思考更改这个值呢？

  1. 当查问打算不是很精确时。比方比照指定表在零碎表 mysql.innodb_index_stats 的数据跟 distinct 查问的后果，如果相差太大，能够思考减少这个值。
  2. 当 analyze table 变的十分慢时，可能是这个值设置的太大了，此时要思考减小这个值。

三、非长久化统计信息参数

  • innodb_stats_transient_sample_pages：设置非长久化统计信息的采样页数目，默认 8 个。
  • innodb_stats_on_metadata：当统计信息配置为非长久化时失效，默认敞开。

    参数 innodb_stats_persistent 为 0 或者建表时属性 STATS_PERSISTENT=0 才起作用。

当开启后，对以下元数据的拜访会自动更新统计信息：

  • show table status
  • show index
  • information_schema.tables
  • information_schema.statistics

所以开启这个选项会额定减少拜访表的开销，特地是大表。

还有一些其余的场景会自动更新非长久化统计信息，比方：

  1. 表第一次被拜访；
  2. InnoDB 检测到有十六分之一的表自从上次统计信息计算后被更新了，这时触发自动更新；
  3. MySQL 客户端默认选项 –auto-rehash 关上所有 InnoDB 表，导致所有 InnoDB 表被自动更新统计信息；

四、表属性管制

STATS_AUTO_RECALC

用来指定是否要主动计算指定 InnoDB 表的统计信息。

三个值：default, 0, 1

  • default：也就是默认值，依赖 server 端参数 innodb_stats_auto_recalc 的设置成果
  • 0：示意禁用统计信息的主动从新计算，也就是永远不从新计算，须要手动执行 analyze table
  • 1：示意当表数据有 10% 的数据变动后，则从新计算长久化统计信息。

STATS_PERSISTENT

用来指定是否要开启指定 InnoDB 表的统计信息长久化。

三个值：default, 0, 1

  • default：依赖 server 端参数 innodb_stats_persistent 的设置
  • 0：示意不须要长久化统计信息
  • 1：示意开启长久化统计信息

STATS_SAMPLE_PAGES

用来指定计算统计信息时的采样页数量。

五、手动更新统计信息

analyze table 用来手动更新表统计信息。倡议在业务低峰时执行。

六、长久化表统计元数据信息

优化器通过两张元数据表里的数据来决定查问最优执行打算。

表统计信息保留在表 mysql.innodb_table_stats 里

比方表 ytt_sample_persist 的统计信息

重要列阐明：

  • n_rows：表的行数
  • clustered_index_size：主键的数据页个数
  • sum_of_other_index_sizes：二级索引的数据页个数

以下例子能够看到表 ytt_sample_persist 表行数大略为 36W 行，聚簇索引页数为 15162，二级索引页数为 4113。

这些值都是基于采样页来计算的，所以是一个预估值。

mysql> select n_rows,clustered_index_size,sum_of_other_index_sizes  from innodb_table_stats where database_name ='ytt' and table_name = 'ytt_sample_persist';
+--------+----------------------+--------------------------+
| n_rows | clustered_index_size | sum_of_other_index_sizes |
+--------+----------------------+--------------------------+
| 356960 |                15162 |                     4113 |
+--------+----------------------+--------------------------+
1 row in set (0.00 sec)

其实表 ytt_sample_persist 实在数据为 40W 行。

mysql> select count(*) from ytt_sample_persist;
+----------+
| count(*) |
+----------+
|   406644 |
+----------+
1 row in set (0.90 sec)

强制更新统计信息

mysql> analyze table ytt_sample_persist;
+------------------------+---------+----------+----------+
| Table                  | Op      | Msg_type | Msg_text |
+------------------------+---------+----------+----------+
| ytt.ytt_sample_persist | analyze | status   | OK       |
+------------------------+---------+----------+----------+
1 row in set (0.28 sec)

统计信息曾经更新为最新，数据又离实在数据靠近一点。

mysql> select n_rows,clustered_index_size,sum_of_other_index_sizes from innodb_table_stats where database_name ='ytt' and table_name ='ytt_sample_persist';
+--------+----------------------+--------------------------+
| n_rows | clustered_index_size | sum_of_other_index_sizes |
+--------+----------------------+--------------------------+
| 387202 |                16380 |                     4562 |
+--------+----------------------+--------------------------+
1 row in set (0.01 sec)

索引统计信息保留在表 mysql.innodb_index_stats 里

比方表 ytt_sample_persist 索引统计信息

  • Index_name : 索引名字
  • stat_name / stat_value：统计名字和对应的值
  • sample_size: 采样页个数
  • stat_description：统计名字详细信息形容

比方表 ytt_sample_persist 的联结主键统计信息如下：

stat_name 显示的值 n_diff_pfx01 代表联结主键中第一个列状态名字，对应的 stat_value 为第一个列的惟一值个数；n_diff_pfx02 代表第二列状态名字，对应的 stat_value 为前两列的惟一值个数，以此类推。

n_leaf_pages /stat_value 代表叶子节点的页数目；size 代表索引的总页数。

mysql> select index_name,stat_name,stat_value,sample_size,stat_description from innodb_index_stats where database_name ='ytt' and table_name ='ytt_sample_persist'  and index_name = 'PRIMARY';
+------------+--------------+------------+-------------+-----------------------------------+
| index_name | stat_name    | stat_value | sample_size | stat_description                  |
+------------+--------------+------------+-------------+-----------------------------------+
| PRIMARY    | n_diff_pfx01 |      14137 |          20 | i1                                |
| PRIMARY    | n_diff_pfx02 |      75398 |          20 | i1,i2                             |
| PRIMARY    | n_diff_pfx03 |     387202 |          20 | i1,i2,i3                          |
| PRIMARY    | n_leaf_pages |      15708 |        NULL | Number of leaf pages in the index |
| PRIMARY    | size         |      16380 |        NULL | Number of pages in the index      |
+------------+--------------+------------+-------------+-----------------------------------+
5 rows in set (0.00 sec)

那对于表的统计信息相干知识点就介绍到此，理解这块对咱们优化 SQL 来说，会更加得心应手。

总结

简略总结下，本篇次要介绍了 MySQL 表和索引的统计信息计算，包含长久化统计信息与非长久化统计信息。如果前期有 SQL 走的执行打算不对，或者不是最优的，那就能够判定相干统计信息太旧了，须要及时更新。比方有时候多表 JOIN 的程序不对，导致查问效率变差，须要人工染指等等。

对于 MySQL 的技术内容，你们还有什么想晓得的吗？连忙留言通知小编吧！

正文完
mysql
发表至： mysql
2020-09-23
 0
关于mysql:InnoDB-Data-Dictionary施洪宝
关于mysql:每日一篇731MySQL的锁机制
关于mysql:MySQL80错误日志Error-log
关于mysql:mysql以及python-中关于事务提交的事项

站内搜索

-「」