mysql大表加索引的方案
mysql大表加索引的方案
在 MySQL 中对大表（百万级或更大数据量）添加索引时，需要谨慎规划以避免影响数据库性能或导致服务中断。以下是针对不同场景的优化方案和最佳实践：
一、方案选择与优先级
根据业务需求和数据库架构，优先考虑以下方案：
1. 优先评估是否真的需要索引
- 通过查询分析工具（如EXPLAIN）确认索引是否能显著提升性能。
- 避免过度索引（每个索引会增加写操作开销和存储空间）。
2. 优先选择在线添加索引（Online DDL）
- MySQL 5.6+ 支持ALGORITHM=INPLACE，无需复制全量数据。
- 优先使用CONCURRENTLY选项（MariaDB/PostgreSQL）或ALGORITHM=INPLACE, LOCK=NONE（MySQL）。
3. 考虑业务低峰期执行
- 对于无法完全避免锁表的操作，选择业务低峰期执行。
二、MySQL 在线添加索引（推荐）
1. 使用ALGORITHM=INPLACE（MySQL 5.6+）
ALTER TABLE table_name
ADD INDEX index_name (column_name),
ALGORITHM=INPLACE, LOCK=NONE;
原理：
- 通过 InnoDB 的 Online DDL 机制，直接在原表上构建索引，无需复制全量数据。
优点：
- 锁表时间极短（仅在切换索引时持有 MDL 写锁，通常小于 1 秒）。
限制：
- 需要足够的临时空间（约为索引大小的 1.2 倍）。
- 不支持所有索引类型（如全文索引可能需要ALGORITHM=COPY）。
2. 使用CONCURRENTLY（MariaDB/PostgreSQL 风格）
CREATE INDEX CONCURRENTLY index_name ON table_name (column_name);
优点：
- 完全无锁，不影响读写操作。
限制： - MySQL 原生不支持，需通过第三方工具（如 pt-online-schema-change）模拟。
三、第三方工具方案(PT)
1. pt-online-schema-change（Percona Toolkit）
pt-online-schema-change \
--alter "ADD INDEX index_name (column_name)" \
--user=root --password=xxx \
--host=localhost --port=3306 \
--execute D=db_name,t=table_name
原理：
- 创建与原表结构相同的临时表并添加索引。
- 通过触发器同步原表数据到临时表。
- 数据同步完成后，重命名临时表替换原表。
优点：
- 无锁操作，对业务影响极小。
- 支持所有版本的 MySQL。
缺点：
- 需要额外的磁盘空间（至少为原表大小）。
- 数据同步期间会增加主从延迟。
2. gh-ost（GitHub Online Schema Migration Tool）
gh-ost \
--max-load=Threads_running=25 \
--critical-load=Threads_running=1000 \
--chunk-size=1000 \
--throttle-control-replicas="replica1,replica2" \
--alter="ADD INDEX index_name (column_name)" \
--switch-to-rbr \
--execute
原理：
- 与 pt-osc 类似，但通过直接读取 binlog 而非触发器同步数据，性能更高。
优点：
- 对主库性能影响更小。
- 支持大表（亿级数据）。
缺点：
- 仅支持行级复制（ROW-based binlog）。
- 需要配置binlog_format=ROW。
四、特殊场景处理
1. 超大表（亿级数据）
分批次添加索引：
-- 示例：对用户表按ID范围分批添加索引
ALTER TABLE users ADD INDEX idx_age (age) WHERE id BETWEEN 1 AND 1000000;
ALTER TABLE users ADD INDEX idx_age (age) WHERE id BETWEEN 1000001 AND 2000000;
-- 需结合应用逻辑实现
使用物理备份工具：
- 通过xtrabackup备份数据，在备份上添加索引后恢复。
2. 主从环境
先从库后主库：
- 在从库添加索引（可使用pt-online-schema-change）。
- 主从切换（通过 VIP 或中间件）。
- 在新从库（原主库）添加索引。
3. 分区表
按分区添加索引：
ALTER TABLE table_name PARTITION (p0) ADD INDEX index_name (column_name);
-- 逐个分区处理
五、执行前的准备工作
1.备份数据
- 通过xtrabackup或mysqldump备份全量数据。
2.评估空间需求
- 确保磁盘有足够空间（至少为表大小的 50%）。
3.调整参数
[mysqld]
innodb_online_alter_log_max_size = 4G # 增大在线DDL日志上限
sort_buffer_size = 256M # 增大排序缓冲区
binlog_format = ROW # 启用行级复制（若使用gh-ost）
4.设置执行阈值
-- 设置超时时间（避免长时间锁定），如果执行时间确实要超过1小时，可以设置更长
SET max_execution_time = 3600000; -- 1小时
六、执行过程监控
1.监控磁盘空间
2.监控复制延迟
SHOW SLAVE STATUS\G -- 检查Seconds_Behind_Master
3.监控锁等待
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
SELECT * FROM performance_schema.metadata_locks;
4.监控进程状态
SHOW PROCESSLIST; -- 查看ALTER TABLE进度
七、回滚策略
1.创建失败
- MySQL 会自动回滚，但需确认临时文件是否已清理。
2.性能下降
- 若添加索引后查询性能反而下降，可通过DROP INDEX删除索引。
- mysql8.0或者以上版本可以通过设置为影藏索引
2.1 创建隐藏索引
CREATE INDEX index_name ON table_name (column_name) INVISIBLE;
-- 示例：创建隐藏的用户年龄索引
CREATE INDEX idx_age ON users (age) INVISIBLE;
ALTER TABLE table_name ALTER INDEX index_name INVISIBLE;
2.2 修改现有索引为隐藏状态
-- 示例：将用户表的email索引设为隐藏
ALTER TABLE users ALTER INDEX idx_email INVISIBLE;
2.3 将隐藏索引恢复为可见
ALTER TABLE table_name ALTER INDEX index_name VISIBLE;
3.空间占用过大
- 执行OPTIMIZE TABLE回收空间（可能导致短暂锁表）。
八、总结与最佳实践
| 方案 | 适用场景 | 优点 | 缺点 |
|---|---|---|---|
| ALGORITHM=INPLACE | 中小表，MySQL 5.6+ | 速度快，锁表时间短 | 需要临时空间 |
| pt-online-schema-change | 大表，所有 MySQL 版本 | 无锁操作，兼容性强 | 占用双倍空间 |
| gh-ost | 超大表，ROW 复制 | 性能高，对主库影响小 | 依赖 ROW 复制 |
最佳实践：
- 优先使用ALGORITHM=INPLACE（MySQL 5.6+）。
- 对亿级表使用gh-ost或分批次处理。
- 执行前在测试环境模拟并评估耗时。
- 始终准备回滚方案，避免不可逆操作。
浙公网安备 33010602011771号