6.8.9 表维护与统计信息管理

表维护与统计信息管理#

表维护与统计信息管理是保障查询优化器稳定、减少碎片、维持写入性能的关键环节。本节结合维护流程、碎片治理、统计信息刷新与排错给出可执行规范与演练。

原理草图：维护与统计信息影响链路#

1. 维护目标与周期（含示例）#

目标：降低碎片、稳定计划、减少锁冲突与I/O放大。
周期建议：按“行变更比例 + 碎片率 + 慢查询趋势”触发。
维护窗口示例（cron）：

# /etc/cron.d/mysql_maint
# 每周日 02:00 对业务库执行统计信息刷新
0 2 * * 0 mysql -uroot -p'PASS' -e "ANALYZE TABLE app.orders, app.order_items;"
# 每月 1 日 03:00 对冷门表做重建
0 3 1 * * mysql -uroot -p'PASS' -e "OPTIMIZE TABLE app.archive_2023;"

说明：ANALYZE TABLE刷新统计信息；OPTIMIZE TABLE重建以回收空间。

2. 碎片识别与治理（命令+解释）#

识别碎片

-- 查看碎片指标（DATA_FREE 越大表示可回收空间越多）
SELECT table_schema, table_name, engine, data_length, index_length, data_free
FROM information_schema.tables
WHERE table_schema='app'
ORDER BY data_free DESC;

解释：data_free反映可回收空间；InnoDB 中可作为重建参考。

治理手段

-- 轻量：刷新统计信息，不重建物理文件
ANALYZE TABLE app.orders;

-- 重建表（可能锁表，需评估窗口）
OPTIMIZE TABLE app.orders;

-- 等价重建（InnoDB）
ALTER TABLE app.orders ENGINE=InnoDB;

-- 优先在线重建（依赖版本与表结构）
ALTER TABLE app.orders ALGORITHM=INPLACE, LOCK=NONE, ENGINE=InnoDB;

解释：ALGORITHM=INPLACE尽量减少锁；LOCK=NONE尝试无锁。

分区表维护

-- 仅重建热点分区，减少全表成本
ALTER TABLE app.orders
REBUILD PARTITION p2024m06;

3. 统计信息管理（示例+配置）#

参数建议（my.cnf）

[mysqld]
innodb_stats_auto_recalc=ON
innodb_stats_persistent=ON
innodb_stats_persistent_sample_pages=64

解释：开启持久化统计与自动更新，采样页数影响精度与耗时。

手动刷新场景

-- 大批量导入后
ANALYZE TABLE app.orders;

-- 分区合并/拆分后
ANALYZE TABLE app.orders PARTITION (p2024m06);

执行计划对比

EXPLAIN SELECT * FROM app.orders WHERE user_id=1001 AND status=1;

说明：在刷新统计信息前后对比 type、rows、key 是否变化。

4. 维护操作流程（含脚本）#

操作前：确认备份/回滚、主从延迟。
操作中：监控锁等待与IO。
操作后：验证慢查询与空间回收。

一键维护脚本示例（先分析后重建）

#!/bin/bash
# /opt/ops/mysql_maint.sh
MYSQL="mysql -uroot -p'PASS' -N -e"
DB=app
TABLE=orders

echo "[1] ANALYZE TABLE..."
$MYSQL "ANALYZE TABLE ${DB}.${TABLE};"

echo "[2] 检查碎片..."
$MYSQL "SELECT data_free FROM information_schema.tables WHERE table_schema='${DB}' AND table_name='${TABLE}';"

echo "[3] 需要重建时执行 OPTIMIZE"
# 这里可加入阈值判断（示例直接执行）
$MYSQL "OPTIMIZE TABLE ${DB}.${TABLE};"

echo "[4] 记录维护结果"
$MYSQL "INSERT INTO ops.maint_log(obj, action, ts) VALUES('${DB}.${TABLE}','OPTIMIZE',NOW());"

5. 排错与验证（命令+说明）#

问题1：ANALYZE 后计划仍异常

-- 检查统计信息是否持久化
SHOW VARIABLES LIKE 'innodb_stats_persistent';
-- 强制刷新
ANALYZE TABLE app.orders;

处理：确保 innodb_stats_persistent=ON，否则重启后统计信息丢失。

问题2：OPTIMIZE 锁表

SHOW PROCESSLIST;
SHOW ENGINE INNODB STATUS\G

处理：评估是否使用在线重建或工具（pt-online-schema-change）。

问题3：空间未回收

SELECT table_schema, table_name, data_length, index_length, data_free
FROM information_schema.tables
WHERE table_schema='app' AND table_name='orders';

处理：InnoDB 共享表空间可能不回收 OS 空间；使用 innodb_file_per_table=ON 并重建表。

6. 练习与验证#

创建测试表并插入/删除模拟碎片：

CREATE TABLE app.t_frag (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  c1 VARCHAR(100),
  c2 INT,
  INDEX idx_c2(c2)
) ENGINE=InnoDB;

INSERT INTO app.t_frag(c1,c2)
SELECT REPEAT('x',50), FLOOR(RAND()*1000)
FROM information_schema.columns LIMIT 50000;

DELETE FROM app.t_frag WHERE id % 3 = 0;

观察碎片指标并执行维护：

SELECT data_free FROM information_schema.tables
WHERE table_schema='app' AND table_name='t_frag';

ANALYZE TABLE app.t_frag;
OPTIMIZE TABLE app.t_frag;

对比维护前后表空间与执行计划：

EXPLAIN SELECT * FROM app.t_frag WHERE c2=10;

7. 维护最佳实践清单#

批量写入/删除后执行 ANALYZE TABLE。
大表重建采用在线方式或分区维护。
维护窗口与备份窗口错开，减少资源争用。
维护结果入库，形成可追溯运维历史。