MySQL 8.0 `mysql.innodb_table_stats` 与 `mysql.innodb_index_stats` 的维护

MySQL 8.0 统计信息：表与索引的“体检报告”与“保养指南” 🩺

大家好！我是你们的老朋友，数据库界的段子手——DB小能。今天呢，咱们不聊复杂的事务隔离级别，也不谈深奥的查询优化器，而是要来聊聊MySQL 8.0中两个非常重要，但常常被忽略的家伙：mysql.innodb_table_stats 和 mysql.innodb_index_stats。

别看它们名字长，还带着神秘的innodb前缀，其实它们就像数据库的“体检报告”和“保养指南”，告诉我们表和索引的健康状况，以便我们及时调整，让数据库跑得更快、更稳！🚀

想象一下，你有一辆心爱的跑车，但你从来不检查机油、轮胎气压，也不做任何保养，你觉得它能一直保持巅峰状态吗？肯定不行！数据库也是一样，表和索引的统计信息如果过时、不准确，查询优化器就可能做出错误的判断，导致查询速度慢如蜗牛。🐌

所以，今天咱们就来好好了解一下这两个“体检报告”和“保养指南”，看看它们到底有什么用，以及如何正确地使用它们。

第一部分：认识“体检报告”——mysql.innodb_table_stats 和 mysql.innodb_index_stats

这两个表都位于mysql系统数据库中，它们存储了InnoDB存储引擎中表和索引的统计信息。你可以把它们想象成医生给你的体检报告，里面详细记录了你的各项指标，比如身高、体重、血压等等。

1. mysql.innodb_table_stats：表的“体检报告”

这个表存储了关于整个表的统计信息，主要包括：

列名	数据类型	描述
database_name	VARCHAR(64)	数据库名
table_name	VARCHAR(64)	表名
last_update	TIMESTAMP	上次更新统计信息的时间。这个时间很重要，它告诉我们这个“体检报告”是不是最新的。
n_rows	BIGINT	表中的行数。注意，这只是一个估计值，并不是精确的数字。InnoDB会定期更新这个值，但不会在每次插入、更新或删除操作后都立即更新。
clustered_index_size	BIGINT	聚集索引的大小，单位是页。聚集索引通常是主键索引，它决定了数据在磁盘上的物理存储顺序。
sum_of_other_index_sizes	BIGINT	其他非聚集索引的大小之和，单位是页。
row_size	BIGINT	平均行大小，单位是字节。
space	BIGINT	表所占用的空间ID。

举个栗子🌰：

SELECT * FROM mysql.innodb_table_stats WHERE table_name = 'your_table_name';

这条SQL语句会显示 your_table_name 这张表的“体检报告”，你可以看到它的行数、大小、上次更新时间等等。

2. mysql.innodb_index_stats：索引的“体检报告”

这个表存储了关于每个索引的统计信息，主要包括：

列名	数据类型	描述
database_name	VARCHAR(64)	数据库名
table_name	VARCHAR(64)	表名
index_name	VARCHAR(64)	索引名
last_update	TIMESTAMP	上次更新统计信息的时间。
stat_name	VARCHAR(64)	统计信息的名称，例如：size、n_leaf_pages、n_diff_pfx01 等。
stat_value	BIGINT	统计信息的值。
sample_size	BIGINT	用于生成统计信息的采样页数。这个值越大，统计信息就越准确，但更新统计信息的时间也越长。
stat_description	VARCHAR(1024)	统计信息的描述。

一些重要的 stat_name：

• size: 索引的大小，单位是页。
• n_leaf_pages: 索引的叶子节点页数。叶子节点存储了实际的索引数据。
• n_diff_pfx01: 索引前缀的唯一值数量。例如，如果索引包含 (col1, col2) 两列，n_diff_pfx01 表示 col1 列的唯一值数量。
• n_diff_pfx02: 索引前缀的唯一值数量。例如，如果索引包含 (col1, col2) 两列，n_diff_pfx02 表示 (col1, col2) 的组合的唯一值数量。

举个栗子🌰：

SELECT * FROM mysql.innodb_index_stats WHERE table_name = 'your_table_name' AND index_name = 'your_index_name';

这条SQL语句会显示 your_table_name 表的 your_index_name 索引的“体检报告”，你可以看到它的大小、叶子节点数、唯一值数量等等。

总结一下：

• mysql.innodb_table_stats 关注的是整个表的整体情况。
• mysql.innodb_index_stats 关注的是每个索引的详细信息。

它们就像是不同级别的“体检报告”，帮助我们从不同角度了解表和索引的健康状况。

第二部分：统计信息的重要性——“体检报告”的解读与应用 🧐

有了“体检报告”，我们就要学会解读它，并根据报告的结果来采取相应的措施。统计信息对于MySQL查询优化器来说至关重要，它会影响查询计划的选择，从而影响查询的性能。

1. 优化器如何使用统计信息？

查询优化器会根据统计信息来评估不同查询计划的成本，并选择成本最低的那个。例如：

• 选择索引： 优化器会根据索引的基数（cardinality）来判断是否使用索引。基数指的是索引列的唯一值数量。如果基数很高，说明索引的选择性很好，使用索引可以大大减少扫描的行数。反之，如果基数很低，说明索引的选择性很差，使用索引可能还不如全表扫描。
• 连接顺序： 在多表连接查询中，优化器会根据表的行数来决定连接的顺序。通常情况下，会先连接行数较少的表，以减少中间结果集的大小。
• 选择连接算法： 优化器会根据表的大小和索引情况来选择不同的连接算法，例如：Nested Loop Join、Hash Join、Sort Merge Join 等。

2. 统计信息过时会导致什么问题？

如果统计信息过时，优化器就可能做出错误的判断，导致查询性能下降。例如：

• 错误地选择索引： 如果索引的基数发生了变化，优化器可能仍然会选择旧的索引，导致查询效率降低。
• 错误的连接顺序： 如果表的行数发生了变化，优化器可能仍然会按照旧的连接顺序进行连接，导致中间结果集过大，影响查询性能。
• 选择错误的连接算法： 如果表的大小和索引情况发生了变化，优化器可能仍然会选择旧的连接算法，导致查询效率降低。

举个栗子🌰：

假设你有一个 users 表，其中有一个 city 列，并且你创建了一个 city 索引。

• 初始状态： users 表有 100 万行数据，city 列的唯一值数量（基数）是 1000。优化器会认为 city 索引的选择性很好，所以在 WHERE city = 'Beijing' 的查询中，会使用 city 索引。
• 大量数据插入： 你向 users 表插入了 100 万行数据，其中 90 万行数据的 city 列的值都是 Beijing。现在 city 列的唯一值数量仍然是 1000，但 Beijing 这个值的占比已经非常高了。
• 统计信息未更新： 如果你没有更新统计信息，优化器仍然会认为 city 索引的选择性很好，所以在 WHERE city = 'Beijing' 的查询中，仍然会使用 city 索引。但实际上，由于 Beijing 这个值的占比非常高，使用 city 索引可能还不如全表扫描更快。

3. 如何判断统计信息是否过时？

• 查看 mysql.innodb_table_stats 和 mysql.innodb_index_stats 表中的 last_update 列。 如果 last_update 时间距离现在很久，说明统计信息可能已经过时。
• 观察查询性能的变化。 如果查询性能突然下降，并且没有其他明显的原因，可以考虑统计信息是否过时。
• 使用 ANALYZE TABLE 命令进行分析。 ANALYZE TABLE 命令会重新计算表的统计信息，并更新 mysql.innodb_table_stats 和 mysql.innodb_index_stats 表。

第三部分：统计信息的维护——“保养指南”的实践 🛠️

既然统计信息这么重要，那么我们应该如何维护它们呢？就像汽车需要定期保养一样，数据库也需要定期维护统计信息，以确保查询优化器能够做出正确的判断。

1. 如何更新统计信息？

MySQL提供了多种方式来更新统计信息：

• ANALYZE TABLE 命令： 这是最常用的更新统计信息的方式。它可以重新计算表的统计信息，并更新 mysql.innodb_table_stats 和 mysql.innodb_index_stats 表。

  ANALYZE TABLE your_table_name;

  你可以指定多个表，一次性更新它们的统计信息：

  ANALYZE TABLE table1, table2, table3;

• OPTIMIZE TABLE 命令： OPTIMIZE TABLE 命令不仅会更新统计信息，还会对表进行碎片整理，并回收未使用的空间。但需要注意的是，OPTIMIZE TABLE 命令会锁定表，所以在生产环境中要谨慎使用。

  OPTIMIZE TABLE your_table_name;

• innodb_stats_auto_recalc 参数： 这个参数控制InnoDB是否自动重新计算统计信息。默认情况下，它是开启的。当表中的数据发生变化超过一定比例时，InnoDB会自动重新计算统计信息。

  -- 查看当前值
  SHOW GLOBAL VARIABLES LIKE 'innodb_stats_auto_recalc';
  
  -- 开启自动重新计算
  SET GLOBAL innodb_stats_auto_recalc = ON;
  
  -- 关闭自动重新计算 (不推荐)
  SET GLOBAL innodb_stats_auto_recalc = OFF;

• innodb_stats_persistent 参数： 这个参数控制InnoDB是否将统计信息持久化到磁盘上。默认情况下，它是开启的。这意味着即使MySQL服务器重启，统计信息也不会丢失。

  -- 查看当前值
  SHOW GLOBAL VARIABLES LIKE 'innodb_stats_persistent';
  
  -- 开启统计信息持久化
  SET GLOBAL innodb_stats_persistent = ON;
  
  -- 关闭统计信息持久化 (不推荐)
  SET GLOBAL innodb_stats_persistent = OFF;

• innodb_stats_sample_pages 参数： 这个参数控制InnoDB在计算统计信息时采样的页数。采样页数越多，统计信息就越准确，但更新统计信息的时间也越长。

  -- 查看当前值
  SHOW GLOBAL VARIABLES LIKE 'innodb_stats_sample_pages';
  
  -- 设置采样页数
  SET GLOBAL innodb_stats_sample_pages = 100;  -- 默认值是 20

2. 何时更新统计信息？

• 在大量数据插入、更新或删除后： 当表中的数据发生变化超过一定比例时，应该立即更新统计信息。
• 在创建或修改索引后： 创建或修改索引会改变表的结构，应该立即更新统计信息。
• 定期更新： 即使表中的数据没有发生明显的变化，也应该定期更新统计信息，以确保其准确性。可以根据实际情况设置更新频率，例如：每天、每周或每月。
• 当查询性能下降时： 如果查询性能突然下降，并且没有其他明显的原因，可以尝试更新统计信息。

3. 更新统计信息的注意事项：

• ANALYZE TABLE 命令会锁定表，所以在生产环境中要谨慎使用。 可以选择在业务低峰期执行 ANALYZE TABLE 命令，或者使用 ONLINE 选项，以尽量减少对业务的影响。
• innodb_stats_auto_recalc 参数虽然可以自动更新统计信息，但可能会对性能产生一定的影响。 可以根据实际情况调整 innodb_stats_auto_recalc_sample 参数，以控制自动更新的频率。
• innodb_stats_sample_pages 参数的值越大，统计信息就越准确，但更新统计信息的时间也越长。 可以根据实际情况调整 innodb_stats_sample_pages 参数，以在准确性和性能之间取得平衡。
• 对于非常大的表，更新统计信息可能需要很长时间。 可以考虑使用抽样的方式来更新统计信息，例如：只更新一部分索引的统计信息，或者只更新一部分数据的统计信息。

总结一下：

维护统计信息就像给汽车做保养一样，需要定期进行，并根据实际情况进行调整。只有保持统计信息的准确性，才能确保查询优化器能够做出正确的判断，从而提高查询性能。

第四部分：高级技巧与最佳实践 🧙‍♂️

除了上面介绍的基本方法，还有一些高级技巧和最佳实践可以帮助你更好地维护统计信息：

1. 使用 INFORMATION_SCHEMA.TABLES 和 INFORMATION_SCHEMA.STATISTICS 辅助判断:

这两个视图可以提供关于表和索引的更多信息，例如：表的行数、数据大小、索引的大小等等。这些信息可以帮助你判断统计信息是否需要更新。

SELECT
  TABLE_NAME,
  TABLE_ROWS,
  DATA_LENGTH,
  INDEX_LENGTH
FROM
  INFORMATION_SCHEMA.TABLES
WHERE
  TABLE_SCHEMA = 'your_database_name'
ORDER BY
  DATA_LENGTH DESC;

SELECT
  TABLE_NAME,
  INDEX_NAME,
  CARDINALITY
FROM
  INFORMATION_SCHEMA.STATISTICS
WHERE
  TABLE_SCHEMA = 'your_database_name';

2. 使用 pt-table-checksum 和 pt-table-sync 工具：

这些工具可以用来检测数据是否一致，并同步数据。在数据发生变化后，可以使用这些工具来确保统计信息的准确性。它们是Percona Toolkit的一部分，非常强大。

3. 监控统计信息的更新频率：

可以编写脚本或者使用监控工具来监控 mysql.innodb_table_stats 和 mysql.innodb_index_stats 表中的 last_update 列，如果发现更新频率过低，就需要手动更新统计信息。

4. 针对特定查询进行优化：

有时候，即使统计信息是最新的，查询优化器仍然可能做出错误的判断。这时候，可以使用 SQL_HINT 来强制优化器选择特定的索引或者连接算法。但这是一种最后的手段，应该尽量避免使用。

5. 注意分区表:

对于分区表，你需要分别对每个分区进行 ANALYZE TABLE 操作，或者使用 ANALYZE TABLE your_table_name PARTITION (partition_name) 命令。

6. 考虑使用历史统计信息:

MySQL 8.0.22 引入了历史统计信息功能，可以让你回溯到过去某个时间点的统计信息。这在分析查询性能问题时非常有用。

总结：

维护统计信息是一个持续的过程，需要根据实际情况进行调整。只有不断学习和实践，才能掌握维护统计信息的技巧，并将其应用到实际工作中。

最后的忠告 👂

记住，统计信息是数据库优化的基石。一个健康的数据库，离不开准确的统计信息。所以，请重视你的“体检报告”和“保养指南”，定期维护它们，让你的数据库跑得更快、更稳！🚀

希望今天的分享对大家有所帮助。如果你觉得这篇文章对你有用，请点赞、评论、转发，让更多的人受益！也欢迎大家在评论区留言，分享你的经验和技巧，一起进步！

我是DB小能，我们下期再见！👋