MariaDB 10.6.7中innodb_table_stats与innodb_index_stats无法自动更新

一、调试自动统计更新失效问题 #

1. 验证参数配置有效性 #

首先确认全局和表级的自动统计更新开关是否正常：

• 查看全局参数：SHOW GLOBAL VARIABLES LIKE 'innodb_stats_auto_recalc';，确保值为ON
• 检查表级覆盖设置：通过SHOW CREATE TABLE your_table_name;查看是否存在STATS_AUTO_RECALC=OFF的定义；或查询系统表确认：

  SELECT NAME, STATS_AUTO_RECALC 
  FROM INFORMATION_SCHEMA.INNODB_TABLES 
  WHERE NAME LIKE '%your_database/your_table_name%';
  

  若表级为OFF，会覆盖全局设置，需执行ALTER TABLE your_table_name STATS_AUTO_RECALC=ON;开启。

2. 确认数据变更量达标 #

自动更新触发的前提是自上次统计后的数据变更量≥10%，可通过系统表验证：

SELECT N_ROWS, MODIFIED_COUNT 
FROM INFORMATION_SCHEMA.INNODB_TABLE_STATS 
WHERE TABLE_NAME='your_table_name' AND TABLE_SCHEMA='your_database';

计算MODIFIED_COUNT/N_ROWS的比例，若未达到10%，自动更新不会触发；若已达标但未更新，说明触发逻辑存在异常。

3. 检查后台统计线程状态 #

自动统计更新由专属后台线程处理，通过以下方式确认线程运行状态：

• 查看进程列表：SHOW PROCESSLIST;，查找是否存在innodb_stats_recalc相关线程
• 查询InnoDB metrics指标，统计触发与完成次数：

  SELECT NAME, VALUE 
  FROM INFORMATION_SCHEMA.INNODB_METRICS 
  WHERE NAME LIKE 'stats_recalc_%';
  

  若stats_recalc_triggered有值但stats_recalc_done无对应增长，说明更新任务被中断或超时。

4. 排查资源限制与超时 #

超大规模分区表的统计计算可能因资源不足超时：

• 查看统计超时参数：SHOW GLOBAL VARIABLES LIKE 'innodb_stats_wait_timeout';，默认60秒，若服务器负载高，可适当调大（如设为120），避免因计算超时导致更新失败
• 查看MariaDB错误日志，搜索关键词stats_recalc或auto-recalculate statistics，是否存在权限不足、磁盘IO超时等报错信息

5. 验证分区表特殊性 #

分区表的统计更新逻辑依赖分区状态：

• 检查分区是否处于只读状态：SELECT PARTITION_NAME, PARTITION_METHOD, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='your_database' AND TABLE_NAME='your_table_name';，只读分区不会触发自动统计更新
• 确认分区未被锁定，若存在分区锁（如ALTER TABLE操作残留锁），会阻塞统计更新

二、减小统计样本量的方法 #

针对超大规模表，可通过减小统计样本页数来降低计算开销，同时保证统计信息基本可用：

1. 表级单独配置（推荐） #

仅针对目标分区表调整样本量，不影响其他表：

ALTER TABLE your_table_name STATS_SAMPLE_PAGES=5; -- 可根据需求设置为5-20之间的数值

该设置会持久化到表定义，后续自动统计或手动ANALYZE TABLE都会使用此样本量。

2. 全局参数调整 #

若需批量调整所有表的统计样本量，可修改全局参数：

SET GLOBAL innodb_stats_persistent_sample_pages=10; -- 临时生效

永久生效需在my.cnf/my.ini中添加：

innodb_stats_persistent_sample_pages=10

注意：样本量过小可能导致统计偏差，影响查询执行计划的合理性，需根据业务场景权衡。

3. 手动ANALYZE时指定样本量 #

执行手动统计更新时，可临时指定样本页数：

ANALYZE TABLE your_table_name PERSISTENT FOR ALL COLUMNS SIZE 5;

此命令会同时更新持久化统计信息，并使用指定的样本页数。

内容的提问来源于stack exchange，提问作者ragul rangarajan