MySQL从information_schema获取每日表大小数据不更新问题咨询

2、为什么仅有少量表的统计值可以正常更新，其余表的统计值从事件运行开始就一直没有变化 #

• MySQL的information_schema表统计缓存默认不会随数据写入自动刷新，仅两种场景会触发缓存更新：一是缓存留存时间超过information_schema_stats_expiry设置的阈值，二是执行ANALYZE TABLE、ALTER TABLE这类会触发表元数据更新的操作。
• 能正常更新的小表通常是刚好有DDL操作触发了元数据刷新，或是查询频率低，缓存到期后触发了重新统计。而你关注的大表，MySQL为了避免每次查询information_schema都扫描磁盘文件重算尺寸，会优先复用未过期的缓存，哪怕表内数据有大量写入，只要没有触发刷新条件，缓存值就不会更新。你的每日执行时间间隔刚好和默认24小时的缓存过期时长接近，很容易出现事件执行时缓存还未过期的情况，导致拿到旧值。

1、该问题如何解决才能获取到准确的每日表尺寸数据 #

推荐两个无业务侵入的方案，按需选择：

方案1：会话级临时关闭统计缓存（优先选，性能影响最小） #

只需要在事件逻辑中新增会话级参数配置，强制当前事件执行时实时统计表尺寸，不会影响全局其他业务的缓存策略，修改后的事件代码如下：

DELIMITER //
CREATE DEFINER=`myUser` EVENT `daily_fill_table_space` 
ON SCHEDULE EVERY 1 DAY STARTS '2021-10-22 03:00:00' 
ON COMPLETION NOT PRESERVE DISABLE 
ON SLAVE COMMENT 'Creates for each table a new row on a daily basis with that table size in MB'
DO
BEGIN
    -- 仅当前会话生效，强制实时计算表尺寸，不走缓存
    SET SESSION information_schema_stats_expiry = 0;
    INSERT INTO `analytics_daily_table_sizes` 
    (`date`, table_schema, `table_name`, size_mb)
    SELECT CURDATE() - INTERVAL 1 DAY, 
    TABLE_SCHEMA, TABLE_NAME, 
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2)
    FROM information_schema.TABLES
    WHERE TABLE_TYPE != 'VIEW' 
    AND TABLE_SCHEMA IN('schema1', 'schema2');
END //
DELIMITER ;

方案2：主动刷新元数据统计（适用于不支持修改information_schema_stats_expiry的旧版本MySQL） #

在执行统计插入前，先对所有要统计的表执行ANALYZE TABLE主动刷新元数据，代码示例如下：

DELIMITER //
CREATE DEFINER=`myUser` EVENT `daily_fill_table_space` 
ON SCHEDULE EVERY 1 DAY STARTS '2021-10-22 03:00:00' 
ON COMPLETION NOT PRESERVE DISABLE 
ON SLAVE COMMENT 'Creates for each table a new row on a daily basis with that table size in MB'
DO
BEGIN
    -- 动态生成所有目标表的ANALYZE语句并执行
    SET @sql = (
        SELECT GROUP_CONCAT('ANALYZE TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '`;' SEPARATOR ' ')
        FROM information_schema.TABLES
        WHERE TABLE_TYPE != 'VIEW' AND TABLE_SCHEMA IN('schema1', 'schema2')
    );
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- 执行数据插入
    INSERT INTO `analytics_daily_table_sizes` 
    (`date`, table_schema, `table_name`, size_mb)
    SELECT CURDATE() - INTERVAL 1 DAY, 
    TABLE_SCHEMA, TABLE_NAME, 
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2)
    FROM information_schema.TABLES
    WHERE TABLE_TYPE != 'VIEW' 
    AND TABLE_SCHEMA IN('schema1', 'schema2');
END //
DELIMITER ;

ANALYZE TABLE只会加短暂的元数据锁，在凌晨业务低峰期执行不会影响正常业务。

内容的提问来源于stack exchange，提问作者Nicolas Gorga