【MySQL深入详解】第18篇：索引维护——保持索引高效的日常操作

xyghehehehe
0 阅读5分钟

开篇引入

索引建好了，不等于一劳永逸。

数据会变化、统计信息会过时、索引会碎片化。如果不维护，再好的索引也会慢慢变慢。

《高性能MySQL》第7章的维护索引和表，这篇文章帮你做好索引的日常维护。

索引维护的三个任务

-- 1. 找到并修复损坏的表
-- 2. 维护准确的索引统计信息
-- 3. 减少碎片

1. 检查表和索引损坏

CHECK TABLE检查

-- 检查表是否有错误
CHECK TABLE orders;

-- 输出示例：
-- Table   | Op    | Msg_type | Msg_text |
-- orders  | check | status   | OK       |

-- 如果有错误：
-- Table   | Op    | Msg_type | Msg_text |
-- orders  | check | error    | Corrupt  |

REPAIR TABLE修复

-- 修复损坏的表（MyISAM支持，InnoDB有限支持）
REPAIR TABLE orders;

-- InnoDB不支持REPAIR TABLE？
-- 可以用ALTER重建表
ALTER TABLE orders ENGINE = InnoDB;

重建表修复损坏

-- 如果表损坏且无法修复，可以尝试重建
-- 方法1：导出再导入
mysqldump db_name orders > orders.sql
mysql db_name < orders.sql

-- 方法2：在线重建（InnoDB）
ALTER TABLE orders ENGINE = InnoDB;

-- 方法3：OPTIMIZE TABLE
OPTIMIZE TABLE orders;

2. 更新索引统计信息

为什么统计信息重要

-- MySQL优化器根据统计信息估算成本
-- 统计信息不准 → 选错索引 → 查询变慢

-- 例如：
-- 统计说user_id=1有100条记录 → 用索引
-- 实际有100万条记录 → 索引扫描反而更慢

ANALYZE TABLE

-- 更新表的统计信息
ANALYZE TABLE orders;

-- 输出：
-- Table   | Op    | Msg_type | Msg_text |
-- orders  | analyze | status   | OK       |

查看索引基数

SHOW INDEX FROM orders;

-- 关键列：
-- Column: 列名
-- Non_unique: 是否唯一（1=不唯一，0=唯一）
-- Key_name: 索引名
-- Seq_in_index: 索引中的顺序
-- Cardinality: 基数（不同值数量估算）

-- Cardinality/表行数 ≈ 选择性
-- 选择性越高，索引越有效

手动设置统计信息（MySQL 8.0+）

-- 设置统计信息采样页面数
SHOW VARIABLES LIKE 'innodb_stats_sample_pages';
-- 默认8，增大可以提高统计准确性（但ANALYZE更慢）

-- 设置后执行ANALYZE
SET GLOBAL innodb_stats_sample_pages = 64;
ANALYZE TABLE orders;

避免元数据锁阻塞

-- 大量表执行ANALYZE可能很慢
-- 可以分批执行
SELECT TABLE_NAME 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'db_name' 
  AND TABLE_TYPE = 'BASE TABLE';

-- 然后逐个ANALYZE
ANALYZE TABLE orders_2024;
ANALYZE TABLE orders_2023;

3. 索引碎片整理

碎片的类型

-- 1. 行间碎片（Intra-row fragmentation）
--    逻辑上顺序的行，物理上不连续
--    影响全表扫描性能

-- 2. 页面碎片（Page-level fragmentation）  
--    数据页没有填满
--    浪费存储空间

-- 3. 空闲空间碎片（Free space fragmentation）
--    删除数据后，页内有空隙

查看碎片情况

-- 查看表大小和索引大小
SELECT 
    table_name,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb,
    table_rows
FROM information_schema.TABLES 
WHERE table_schema = 'db_name';

OPTIMIZE TABLE整理碎片

-- 整理表碎片，回收空间
OPTIMIZE TABLE orders;

-- 对InnoDB表，会重建表和索引
-- 相当于：ALTER TABLE orders ENGINE = InnoDB

ALTER TABLE重建

-- 如果OPTIMIZE TABLE太慢，可以直接ALTER
-- 改一个不影响逻辑的设置
ALTER TABLE orders ENGINE = InnoDB;

-- 例如：禁用某个特性再开启
ALTER TABLE orders ADD COLUMN temp INT DEFAULT NULL;
ALTER TABLE orders DROP COLUMN temp;

在线DDL操作

MySQL 5.6+的在线DDL

-- 添加索引不锁表（在线）
ALTER TABLE orders ADD INDEX idx_user_id (user_id), 
ALGORITHM=INPLACE, LOCK=NONE;

-- ALGORITHM选项：
-- INPLACE: 尽量在线，不复制表
-- COPY: 复制表，会锁表

-- LOCK选项：
-- NONE: 不锁表（如果可能）
-- SHARED: 允许共享锁
-- EXCLUSIVE: 需要排他锁

重建索引

-- 删除并重建索引
ALTER TABLE orders DROP INDEX idx_old_name, ADD INDEX idx_new_name (user_id);

添加列

-- 添加NOT NULL列需要小心
ALTER TABLE orders ADD COLUMN new_col VARCHAR(50) NULL;

-- 如果要NOT NULL，需要先建默认值
ALTER TABLE orders ADD COLUMN new_col VARCHAR(50) NOT NULL DEFAULT '';

pt-online-schema-change

Percona工具

# 安装
wget percona.com/get/percona-toolkit.tar.gz
tar xzf percona-toolkit.tar.gz
cd percona-toolkit-3.*
perl Makefile.PL
make && make install

# 使用
pt-online-schema-change \
    --alter "ADD COLUMN phone VARCHAR(20)" \
    D=database,t=orders \
    --execute

gh-ost（GitHub工具）

# 安装
wget github.com/github/gh-ost/releases/download/v1.1.0/gh-ost-binary-linux-amd64.tar.gz
tar xzf gh-ost-binary-linux-amd64.tar.gz

# 使用
./gh-ost \
    --database="database" \
    --table="orders" \
    --alter="ADD COLUMN phone VARCHAR(20)" \
    --execute

日常维护计划

定期任务建议

-- 1. 每周：检查表健康
CHECK TABLE orders;

-- 2. 每月：更新统计信息
ANALYZE TABLE orders;

-- 3. 每月/每季度：检查碎片
-- 如果数据变化频繁，碎片会累积
OPTIMIZE TABLE orders;

-- 4. 定期：检查索引使用情况
-- 删除没用的索引

查看未使用的索引

-- MySQL 8.0可以通过performance_schema查看
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_FETCH,
    COUNT_INSERT,
    COUNT_UPDATE,
    COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'db_name'
  AND INDEX_NAME != 'PRIMARY'
ORDER BY COUNT_FETCH + COUNT_INSERT + COUNT_UPDATE + COUNT_DELETE;

慢查询与索引

-- 慢查询日志中的查询
-- 结合EXPLAIN分析是否需要新索引

-- 典型问题：
-- 1. 全表扫描 → 添加索引
-- 2. Using filesort → 添加排序列到索引
-- 3. Using temporary → 考虑覆盖索引

维护脚本模板

MySQL健康检查脚本

#!/bin/bash
# mysql_health_check.sh

DATABASE="your_database"
LOG_FILE="/var/log/mysql_maintenance.log"

echo "=== MySQL Maintenance $(date) ===" >> $LOG_FILE

# 检查表
mysql -e "CHECK TABLE $(mysql -N -e "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='$DATABASE' AND TABLE_TYPE='BASE TABLE'")" >> $LOG_FILE 2>&1

# 更新统计
mysql -e "ANALYZE TABLE $(mysql -N -e "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='$DATABASE' AND TABLE_TYPE='BASE TABLE'")" >> $LOG_FILE 2>&1

echo "=== Done ===" >> $LOG_FILE

Crontab配置

# 每周日凌晨3点执行维护
0 3 * * 0 /path/to/mysql_health_check.sh >> /var/log/mysql_maintenance.log 2>&1

# 每月OPTIMIZE（建议低峰期）
0 4 1 * * mysql -e "OPTIMIZE TABLE $(mysql -N -e "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='$DATABASE'")"

索引维护最佳实践

-- 1. 定期检查，不要等问题发生
-- 2. 低峰期执行维护操作
-- 3. 大表维护前先在测试环境验证
-- 4. 保留维护日志，便于回溯
-- 5. 监控维护操作对性能的影响

小结

  1. CHECK TABLE定期检查：及时发现表损坏
  2. ANALYZE TABLE更新统计：让优化器做出正确决策
  3. OPTIMIZE TABLE整理碎片：回收空间，提升性能
  4. 使用在线DDL：减少维护窗口的锁表时间
  5. pt-osc和gh-ost：大表变更的专业工具
  6. 定期检查未使用索引：删除冗余索引
  7. 低峰期执行维护：减少对业务的影响
  8. 记录维护日志：便于问题排查和趋势分析

索引维护是DBA的日常工作，虽然枯燥，但能让系统保持最佳状态。

延伸阅读

  • 《高性能MySQL》第7章 维护索引和表
  • MySQL 8.0 Reference Manual: OPTIMIZE TABLE
  • Percona Toolkit: pt-online-schema-change
  • GitHub gh-ost: Online Schema Migration
avatar
文章
阅读
粉丝