数据库核心术语知识库

定义

统计信息 (Statistics) 是数据库系统维护的关于表和索引的数据分布特征的元数据。优化器利用这些统计信息来估算不同执行计划的成本,从而选择最优的查询执行策略。

统计信息通常包括:

表的行数
列的基数(不同值的数量)
数据分布(直方图)
索引的深度和页数
聚簇因子

详细笔记

核心原理

优化器如何使用统计信息

SQL 查询
  ↓
优化器读取统计信息
  ↓
基于统计信息估算:
  - 每个访问方法需要读取的行数
  - I/O 成本
  - CPU 成本
  - 内存需求
  ↓
选择成本最低的执行计划

示例:

sql

SELECT * FROM users WHERE age > 25;

-- 优化器查看统计信息:
-- 1. 表总行数:100 万
-- 2. age 列的分布:
--  - 最小值:18
--  - 最大值:80
--  - 平均值:35
--  - 直方图显示:25 岁以下占 30%
-- 
-- 3. 估算:age > 25 的记录约 70 万行
-- 
-- 4. 决策:
--  - 如果使用索引:需要回表 70 万次
--  - 如果全表扫描:读取 100 万行
--  - 选择:全表扫描(成本更低)

MySQL 统计信息

InnoDB 持久化统计信息

MySQL 5.6+ 支持持久化统计信息,重启后不会丢失:

sql

-- 统计信息存储在 mysql.innodb_table_stats 和 mysql.innodb_index_stats

-- 查看表统计信息
SELECT * FROM mysql.innodb_table_stats WHERE table_name = 'users';

-- 输出:
-- +---------------+------------+---------------------+--------+----------------------+--------------------------+
-- | database_name | table_name | last_update   | n_rows | clustered_index_size | sum_of_other_index_sizes |
-- +---------------+------------+---------------------+--------+----------------------+--------------------------+
-- | test    | users  | 2024-01-15 10:30:00 | 100000 |       1234 |       5678 |
-- +---------------+------------+---------------------+--------+----------------------+--------------------------+

-- 查看索引统计信息
SELECT * FROM mysql.innodb_index_stats WHERE table_name = 'users';

-- 输出:
-- +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
-- | database_name | table_name | index_name | last_update   | stat_name  | stat_value | sample_size | stat_description      |
-- +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
-- | test    | users  | PRIMARY  | 2024-01-15 10:30:00 | n_diff_pfx01 |   100000 |    20 | id            |
-- | test    | users  | idx_name | 2024-01-15 10:30:00 | n_diff_pfx01 |   500000 |    20 | name          |
-- +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+

关键字段:

n_rows:估算的行数
clustered_index_size:聚集索引占用的页数
stat_value:基数(不同值的数量)
sample_size:采样页数(用于估算)

统计信息更新模式

sql

-- 查看当前模式
SHOW VARIABLES LIKE 'innodb_stats_persistent';
SHOW VARIABLES LIKE 'innodb_stats_auto_recalc';

-- innodb_stats_persistent:
-- ON:持久化统计信息(默认)
-- OFF:非持久化,重启后重新计算

-- innodb_stats_auto_recalc:
-- ON:自动重新计算(默认)
-- OFF:手动更新

-- 配置自动更新的阈值
SHOW VARIABLES LIKE 'innodb_stats_on_metadata';
-- ON:访问元数据时更新
-- OFF:不自动更新

手动更新统计信息

sql

-- 方法一:ANALYZE TABLE
ANALYZE TABLE users;

-- 方法二:修改表结构(会触发统计信息更新)
ALTER TABLE users ENGINE=InnoDB;

-- 方法三:禁用自动更新,手动控制
SET GLOBAL innodb_stats_auto_recalc = OFF;
ANALYZE TABLE users;  -- 手动更新

配置采样精度

sql

-- 查看默认采样页数
SHOW VARIABLES LIKE 'innodb_stats_persistent_sample_pages';
-- 默认:20

-- 提高采样精度(更准确,但更慢)
SET GLOBAL innodb_stats_persistent_sample_pages = 50;

-- 针对特定表设置
ALTER TABLE users STATS_SAMPLE_PAGES = 50;
ALTER TABLE users STATS_AUTO_RECALC = 1;  -- 启用自动更新
ALTER TABLE users STATS_PERSISTENT = 1; -- 启用持久化

PostgreSQL 统计信息

pg_statistic 系统表

sql

-- 查看统计信息
SELECT 
  attname AS column_name,
  attnum AS column_number,
  stanullfrac AS null_fraction,
  stawidth AS average_width,
  stadistinct AS distinct_values,
  stakind1,
  staop1,
  stanumbers1,
  stavalues1
FROM pg_statistic
WHERE starelid = 'users'::regclass;

-- 更友好的视图:pg_stats
SELECT 
  attname AS column_name,
  n_distinct,
  most_common_vals,
  most_common_freqs,
  histogram_bounds,
  correlation
FROM pg_stats
WHERE tablename = 'users';

关键字段:

n_distinct:不同值的数量估计
- 0:实际数量
- < 0:比例(绝对值 × 总行数)
most_common_vals:最常见的值
most_common_freqs:最常见值的频率
histogram_bounds:直方图边界(数据分布)
correlation:物理存储顺序与列值顺序的相关性(-1 到 1)

更新统计信息

sql

-- 手动更新
ANALYZE users;

-- 更新特定列
ANALYZE users (age, city);

-- 更新整个数据库
ANALYZE;

-- 配置统计目标(影响采样率)
ALTER TABLE users ALTER COLUMN age SET STATISTICS 1000;
-- 默认:100
-- 范围:0-10000
-- 值越大,统计信息越准确,但 ANALYZE 越慢

-- 查看当前统计目标
SELECT attname, attstattarget
FROM pg_attribute
WHERE attrelid = 'users'::regclass AND attname = 'age';

Auto-analyze

PostgreSQL 会自动更新统计信息:

触发条件:
  变化的行数 > threshold + scale_factor * 总行数
  
默认值:
  threshold = 50
  scale_factor = 0.1 (10%)
  
示例:
  100 万行的表,变化超过 100050 行时触发 auto-analyze

sql

-- 查看 auto-analyze 配置
SHOW autovacuum_analyze_threshold;
SHOW autovacuum_analyze_scale_factor;

-- 针对特定表配置
ALTER TABLE users SET (autovacuum_analyze_threshold = 100);
ALTER TABLE users SET (autovacuum_analyze_scale_factor = 0.05);

SQL Server 统计信息

查看统计信息

sql

-- 查看表的统计信息
DBCC SHOW_STATISTICS ('users', 'idx_name');

-- 或使用系统视图
SELECT 
  name AS stats_name,
  stats_id,
  auto_created,
  user_created,
  no_recompute,
  last_updated,
  rows,
  rows_sampled,
  steps,
  unfiltered_rows,
  modification_counter
FROM sys.dm_db_stats_properties(OBJECT_ID('users'), 1);

-- 查看所有统计信息
SELECT 
  t.name AS table_name,
  s.name AS stats_name,
  sp.last_updated,
  sp.rows,
  sp.rows_sampled,
  sp.modification_counter
FROM sys.stats s
JOIN sys.tables t ON s.object_id = t.object_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE t.name = 'users';

更新统计信息

sql

-- 手动更新
UPDATE STATISTICS users;

-- 更新特定索引
UPDATE STATISTICS users idx_name;

-- 使用完整扫描(最准确,但最慢)
UPDATE STATISTICS users WITH FULLSCAN;

-- 使用采样
UPDATE STATISTICS users WITH SAMPLE 50 PERCENT;

-- 启用自动更新
ALTER DATABASE your_db SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE your_db SET AUTO_UPDATE_STATISTICS_ASYNC ON;  -- 异步更新

统计信息与查询性能

案例一:过时的统计信息导致错误决策

sql

-- 场景:用户表,初始 1000 行
CREATE TABLE users (
  id INT PRIMARY KEY,
  status TINYINT,
  name VARCHAR(50)
);

INSERT INTO users SELECT ...;  -- 插入 1000 行
ANALYZE TABLE users;

-- 统计信息:
-- n_rows: 1000
-- status 基数: 3

-- 业务增长,数据达到 100 万行
INSERT INTO users SELECT ...;  -- 再插入 999000 行

-- 但统计信息未更新!
-- n_rows 仍然是: 1000 (过时)

-- 查询
EXPLAIN SELECT * FROM users WHERE status = 1;

-- 问题:
-- 优化器以为只有 1000 行,status=1 约 333 行
-- 实际有 100 万行,status=1 约 33 万行
-- 优化器可能错误地选择索引扫描,而应该全表扫描

-- 解决:更新统计信息
ANALYZE TABLE users;

-- 重新 EXPLAIN,优化器现在知道有 100 万行,选择全表扫描

案例二:数据倾斜导致统计信息不准确

sql

-- 场景:订单表,大部分订单状态为"已完成"

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  status TINYINT,  -- 0:待支付, 1:已支付, 2:已完成
  create_time DATETIME
);

-- 数据分布:
-- status=0: 1% (1 万行)
-- status=1: 9% (9 万行)
-- status=2: 90% (90 万行)

-- 普通统计信息无法反映这种倾斜

-- 查询一:稀有状态
SELECT * FROM orders WHERE status = 0;
-- 优化器估算:33 万行(假设均匀分布)
-- 实际:1 万行
-- 结果:可能选择全表扫描,而应该用索引

-- 解决方案:使用直方图
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 100 BUCKETS;

-- 现在优化器知道 status=0 只占 1%,选择使用索引

监控统计信息

MySQL

sql

-- 查看统计信息最后更新时间
SELECT 
  table_name,
  last_update,
  n_rows,
  clustered_index_size
FROM mysql.innodb_table_stats
WHERE database_name = 'your_database';

-- 检查是否需要更新统计信息
SELECT 
  table_name,
  n_rows AS estimated_rows,
  (SELECT COUNT(*) FROM your_database.users) AS actual_rows,
  ABS(n_rows - (SELECT COUNT(*) FROM your_database.users)) / 
  (SELECT COUNT(*) FROM your_database.users) AS error_rate
FROM mysql.innodb_table_stats
WHERE table_name = 'users';

-- 如果 error_rate > 0.1 (10%),建议更新统计信息

PostgreSQL

sql

-- 查看最后一次 ANALYZE 时间
SELECT 
  relname AS table_name,
  last_analyze,
  last_autoanalyze,
  analyze_count,
  autoanalyze_count
FROM pg_stat_user_tables
WHERE relname = 'users';

-- 查看需要更新的统计信息
SELECT 
  relname,
  n_live_tup,
  n_dead_tup,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 50 + 0.1 * n_live_tup;  -- 超过 auto-analyze 阈值

SQL Server

sql

-- 查看过时的统计信息
SELECT 
  t.name AS table_name,
  s.name AS stats_name,
  sp.last_updated,
  sp.modification_counter,
  sp.rows,
  CAST(sp.modification_counter AS FLOAT) / sp.rows AS modification_ratio
FROM sys.stats s
JOIN sys.tables t ON s.object_id = t.object_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE CAST(sp.modification_counter AS FLOAT) / sp.rows > 0.1  -- 变化超过 10%
ORDER BY modification_ratio DESC;

最佳实践

定期更新统计信息:特别是大量数据变更后(批量导入、删除)
监控统计信息准确性:定期检查估算行数与实际行数的差异
合理配置采样率:

小表:使用默认值
大表或数据倾斜严重:提高采样率

启用自动更新:大多数场景下,auto-analyze 足够
关键查询手动更新:对于性能敏感的查询,在大批量数据变更后手动更新统计信息
使用直方图处理数据倾斜:MySQL 8.0+、PostgreSQL、SQL Server 都支持
避免频繁更新:统计信息更新本身有开销,不要过度频繁

关联术语

[[基数]]
[[执行计划]]
[[索引选择性]]
[[参数嗅探]]

参考资料

MySQL 官方文档: InnoDB Persistent Statistics
PostgreSQL 文档: Statistics Collector
Microsoft Docs: Statistics
《高性能 MySQL》第 4 章:查询性能优化

定义 ​

详细笔记 ​

核心原理 ​

优化器如何使用统计信息 ​

MySQL 统计信息 ​

InnoDB 持久化统计信息 ​

统计信息更新模式 ​

手动更新统计信息 ​

配置采样精度 ​

PostgreSQL 统计信息 ​

pg_statistic 系统表 ​

更新统计信息 ​

Auto-analyze ​

SQL Server 统计信息 ​

查看统计信息 ​

更新统计信息 ​

统计信息与查询性能 ​

案例一:过时的统计信息导致错误决策 ​

案例二:数据倾斜导致统计信息不准确 ​

监控统计信息 ​

MySQL ​

PostgreSQL ​

SQL Server ​

最佳实践 ​

关联术语 ​

参考资料 ​

定义

详细笔记

核心原理

优化器如何使用统计信息

MySQL 统计信息

InnoDB 持久化统计信息

统计信息更新模式

手动更新统计信息

配置采样精度

PostgreSQL 统计信息

pg_statistic 系统表

更新统计信息

Auto-analyze

SQL Server 统计信息

查看统计信息

更新统计信息

统计信息与查询性能

案例一:过时的统计信息导致错误决策

案例二:数据倾斜导致统计信息不准确

监控统计信息

MySQL

PostgreSQL

SQL Server

最佳实践

关联术语

参考资料