mysql之InnoDB 统计信息收集

InnoDB 统计信息收集

MySQL 查询优化器依赖于准确的统计信息来做出最佳的执行计划决策。对于 InnoDB 存储引擎，统计信息的收集至关重要，它是优化器的 “粮草”。

核心概念：统计信息的价值

统计信息是关于表和索引数据分布的元数据，帮助优化器估算不同执行计划的成本，并选择最优方案。准确的统计信息是 CBO (Cost-Based Optimizer) 做出明智决策的基础。

表级别统计信息

• 总行数 (Table Rows): 表中记录的总数量。

• 数据页数量 (Data Pages): 表数据占用的数据页数量。

• 平均行长度 (Avg Row Length): 每行数据的平均字节数。

• 表的版本号 (Table Version): 记录表结构或数据修改的次数。

索引级别统计信息

• Cardinality (基数): 索引列中唯一值的数量。

• 索引页数量 (Index Pages): 索引结构占用的索引页数量。

• 索引版本号 (Index Version): 记录索引结构修改的次数。

• 索引深度（B+树层级）

• 索引页分布（通过INFORMATION_SCHEMA.INNODB_INDEX_STATS查看）

• 直方图 (Histogram, 可选): 更详细地描述索引列的数据分布。

列统计(MySQL 8.0+)

• 直方图（HISTOGRAM）：等宽/等高分布统计

统计信息的存储位置

InnoDB 的统计信息主要存储在两个地方：

1. 数据字典 (Data Dictionary): 系统表，例如 information_schema.TABLES, information_schema.STATISTICS, information_schema.COLUMN_STATISTICS, mysql.innodb_table_stats, mysql.innodb_index_stats。

-- 持久化统计信息存储表
SELECT * FROM mysql.innodb_table_stats;  -- 表级统计
SELECT * FROM mysql.innodb_index_stats;  -- 索引级统计

1. 内存 (Memory): InnoDB 也会在内存中缓存统计信息。

统计信息的收集方式

InnoDB 统计信息收集主要有两种方式：

1. 自动收集 (Automatic Collection): InnoDB 后台线程自动触发。

2. 手动收集 (Manual Collection): 通过 ANALYZE TABLE 命令显式触发。

自动统计信息收集的触发条件

• 表被首次打开 (First Open): 首次访问时，如果统计信息缺失或过期。

• 表数据发生显著变化 (Significant Data Change): 数据修改达到一定比例 (由 innodb_stats_auto_recalc 控制，默认 ON)。

  • innodb_stats_auto_recalc = ON (默认): 根据数据变化比例自动判断。

  • innodb_stats_auto_recalc = OFF: 禁用自动重新计算。

• 执行SHOW TABLE STATUS/INDEX

• 查询INFORMATION_SCHEMA.TABLES/STATISTICS

• 后台线程异步更新 (Asynchronous Update): 统计信息更新通常是异步的，不阻塞查询。

自动统计信息收集的具体机制

• 采样 (Sampling): InnoDB 通常采用采样方式，随机读取一定数量的数据页 (由 innodb_stats_sample_pages 控制，默认 8) 进行分析。

  • innodb_stats_sample_pages: 控制采样页数。

• 版本号 (Version) 机制: 使用版本号跟踪统计信息是否过期。

手动统计信息收集： ANALYZE TABLE 命令

• 语法: ANALYZE TABLE table_name;

• 作用: 强制 InnoDB 重新计算统计信息。

• 执行过程: 默认也会进行采样收集，采样页数由 innodb_stats_sample_pages 控制。

• 全量扫描选项 (可选): 可以通过 FOR COLUMNS 子句结合 HISTOGRAM 选项，进行全量扫描并生成直方图。

ANALYZE TABLE table_name FOR COLUMNS index_column1, index_column2 HISTOGRAM ON index_column1;

• 数据归档场景优化, 可以指定分区

-- 分区表统计优化
ALTER TABLE logs 
  ANALYZE PARTITION p2023;  -- 仅更新特定分区统计

统计信息的持久化

InnoDB 统计信息有两种持久化模式，由参数 innodb_stats_persistent 控制：

• innodb_stats_persistent = ON (默认，MySQL 5.6.6+): 持久化统计信息。

  • 优点: 数据库重启后，统计信息仍然有效。

  • 缺点: 每次更新都需要写入磁盘，略微增加 I/O 开销。

• innodb_stats_persistent = OFF: 非持久化统计信息。

  • 优点: 更新速度更快，减少 I/O 开销。

  • 缺点: 数据库重启后，统计信息丢失。

innodb_stats_persistent_sample_pages 参数:

• 与 innodb_stats_sample_pages 类似，但仅在 innodb_stats_persistent = ON 时生效，控制持久化统计信息的采样页数。

• 如果 innodb_stats_persistent = ON，建议 innodb_stats_persistent_sample_pages 的值大于等于 innodb_stats_sample_pages。

采样算法原理

def collect_stats(table):
    total_pages = get_total_pages(table)
    sample_pages = min(innodb_stats_persistent_sample_pages, total_pages)
    sampled_pages = random.sample(range(total_pages), sample_pages)
    
    cardinality = 0
    for page in sampled_pages:
        index_entries = read_index_entries(page)
        cardinality += count_distinct(index_entries)
    
    # 使用Hasse公式估算基数
    estimated_cardinality = (cardinality / sample_pages) * total_pages #估算基数 = (采样页总唯一键数 / 采样页数) * 总页数
    return estimated_cardinality

动态调整采样 (8.0+改进)

自动增加采样页数的情况：

• 检测到统计信息误差超过15%

• 索引深度增加超过1层

统计信息应用

优化器决策依据

• 索引选择： 通过比较全表扫描成本 vs 索引扫描成本

• 连接顺序优化： 基于各表的筛选后行数估算

• 范围查询优化： 估算范围扫描需要访问的数据页

分层校准机制：

成本计算公式

索引扫描成本 = 
    (索引树高度 * 单页IO成本) + 
    (估算行数 * 单行CPU成本) + 
    (估算行数 * 回表IO成本)

生产环境调优

采样参数优化建议

-- 大表优化（亿级数据）
SET GLOBAL innodb_stats_persistent_sample_pages = 200;
-- 高基数列优化
ALTER TABLE users 
  STATS_SAMPLE_PAGES = 100 
  STATS_PERSISTENT = 1;

监控统计信息健康度

-- 检查统计信息更新时间
SELECT table_name, last_update 
FROM mysql.innodb_table_stats
WHERE database_name = 'mydb';

-- 对比估算值与实际值
SELECT 
  table_name,
  table_rows AS estimated_rows,
  (SELECT COUNT(*) FROM table_name) AS actual_rows 
FROM information_schema.tables
WHERE table_schema = 'mydb';

统计信息管理实践

直方图使用案例

-- 创建直方图
ANALYZE TABLE users 
  UPDATE HISTOGRAM ON age WITH 50 BUCKETS;

-- 查看直方图信息
SELECT * FROM information_schema.column_statistics
WHERE table_name = 'users';

innodb_stats_method 的使用

统计索引列不重复值数量时对 NULL 值的处理, 有三个候选值：

• nulls_equal：认为所有 NULL 值都是相等的，默认值。

• nulls_unequal：认为所有 NULL 值都是不相等的。

• nulls_ignored：直接忽略 NULL 值。

附：统计信息相关系统变量

SHOW VARIABLES LIKE 'innodb_stats%';
-- 关键输出：
-- innodb_stats_method：nulls_equal/null_unequal/nulls_ignored
-- innodb_stats_on_metadata：是否在元数据查询时更新统计

参考：https://relph1119.github.io/mysql-learning-notes/#/mysql ,推荐理解本文之后去看原书，原书有一定深度需前后贯穿仔细理解