如何手动收集数据库的统计信息？

wen IT资讯 2026-06-03 240

DBA必备的性能优化指南

目录导读

为什么需要手动收集统计信息？
主流数据库统计信息收集方法对比
Oracle数据库手动收集实战
SQL Server统计信息更新技巧
MySQL/PostgreSQL手动收集方案
常见问题与最佳实践
问答环节：DBA高频疑问解答

为什么需要手动收集统计信息？

数据库查询优化器（Optimizer）依赖统计信息生成高效执行计划，当数据发生大量插入、更新或删除后，自动统计信息更新可能滞后，导致：

如何手动收集数据库的统计信息？

执行计划错误：使用过时的索引统计，导致全表扫描
响应时间飙升：SQL查询从毫秒级退化到秒级
资源浪费：CPU和I/O被低效查询消耗

手动收集的核心价值在于：在关键业务窗口前（如大促、月结）主动刷新统计信息，确保优化器有最新数据决策。

主流数据库统计信息收集方法对比

数据库	自动收集机制	手动收集命令	适用场景
Oracle	默认夜间作业GATHER_STATS_JOB	DBMS_STATS包	分区表、大表增量收集
SQL Server	按数据变更阈值触发	UPDATE STATISTICS	索引级精细控制
MySQL	默认开启但依赖参数	ANALYZE TABLE	InnoDB采样率调整
PostgreSQL	autovacuum守护进程	ANALYZE	复制拓扑中的一致性保障

Oracle数据库手动收集实战

1 核心命令示例

-- 收集全库统计信息（生产环境慎用）
EXEC DBMS_STATS.GATHER_DATABASE_STATS;
-- 收集指定用户所有表（推荐）
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR', options => 'GATHER AUTO');
-- 收集单个大表，指定采样率（100%精确）
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', estimate_percent => 100);

2 关键参数优化

method_opt：控制直方图精度，如FOR ALL COLUMNS SIZE 254
degree：并行度，单核服务器建议DEFAULT
granularity：分区表的收集粒度，增量模式用APPROX_GLOBAL AND PARTITION

3 监控收集进度

SELECT * FROM DBA_OPTSTAT_OPERATION_TASKS 
WHERE STATUS='IN_PROGRESS';

SQL Server统计信息更新技巧

SQL Server的统计信息自动更新阈值：当表行数变化超过20%（或500行）时触发，但大表可能需要手动干预。

1 完整更新与抽样更新

-- 全量扫描更新（最精确，但耗时）
UPDATE STATISTICS Sales.Orders WITH FULLSCAN;
-- 按页样本更新（平衡性能与精度）
UPDATE STATISTICS Sales.Orders WITH SAMPLE 50 PERCENT;

2 检查过期统计信息

SELECT OBJECT_NAME(s.object_id) AS TableName,
       s.name AS StatName,
       STATS_DATE(s.object_id, s.stats_id) AS LastUpdated
FROM sys.stats AS s
WHERE DATEDIFF(DAY, STATS_DATE(s.object_id, s.stats_id), GETDATE()) > 7;

3 针对索引的增量更新

UPDATE STATISTICS Sales.Orders IX_OrderDate;

MySQL/PostgreSQL手动收集方案

1 MySQL的InnoDB引擎

-- 单表分析（存储引擎计算卡迪尔数）
ANALYZE TABLE employees;
-- 查看表统计信息更新状态
SHOW TABLE STATUS WHERE Name='employees';

注意：MySQL的ANALYZE在InnoDB中大表可能导致锁，建议业务低峰期执行，从MySQL 8.0开始支持ANALYZE TABLE ... UPDATE HISTOGRAM ON指定列。

2 PostgreSQL的ANALYZE机制

-- 单表分析（建议）
ANALYZE orders;
-- 全库分析（谨慎使用）
ANALYZE VERBOSE;
-- 检查统计信息年龄
SELECT relname, last_analyze, last_autoanalyze 
FROM pg_stat_user_tables;

关键参数：default_statistics_target控制采样大小，默认100，可根据列基数调整。

常见问题与最佳实践

Q：如何避免统计信息收集干扰业务？
A：

使用资源管理组限制CPU使用率（如Oracle的DEGREE参数）
SQL Server中用MAXDOP控制并行度
选择业务低峰窗口（凌晨2-4点）

Q：统计信息频繁过期怎么办？
A：

调整自动统计信息阈值（如SQL Server的STATS_RETENTION）
对频繁更新的表设置MODIFIED_PERCENTAGE参数
使用增量统计（Oracle的分区表支持实时增量）

Q：统计信息收集后查询反而变慢？
A：

检查直方图是否过于细化（可回退到SIZE 1）
收集后清除共享池（ORACLE: ALTER SYSTEM FLUSH SHARED_POOL）
检查执行计划绑定,必要时手动指定HINT

问答环节：DBA高频疑问解答

Q：手动收集统计信息会锁表吗？
A：视数据库类型而定，Oracle和SQL Server默认不锁表，但MySQL的InnoDB在非索引列分析时会有元数据锁，PostgreSQL的ANALYZE基于快照，不影响读写。

Q：大表统计信息收集超时怎么处理？
A：采用三步策略：

先用5%采样率快速收敛
再对高频查询列单独收集
最后在窗口期用100%采样补全

Q：RDS云数据库如何手动收集？
A：云厂商通常提供存储过程封装，

AWS Aurora MySQL：CALL mysql.rds_set_configuration('table_open_cache', 2000);
阿里云RDS SQL Server：通过SSMS连接后直接执行标准命令

Q：统计信息收集频率如何定？
A：建议

每日变更行数超过10%的表：每天收集1次
大分区表：按分区粒度每周收集
数据仓库表：每月批量加载后立即收集

手动收集统计信息是数据库性能调优的最后防线，但要避免过度收集带来的资源开销，建议监控LAST_ANALYZED时间，结合业务变更节奏制定策略，当遇到慢查询时，优先检查统计信息是否新鲜，再考虑索引优化或SQL重写。完）