千万级数据量的大表添加非主键，多列索引时使用ALGORITHM和LOCK参数的注意事项

原创于 2025-06-11 16:41:26 发布 · 944 阅读

17 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

大数据量表上添加索引是一个高风险操作，可能导致长时间锁表，影响生产环境服务。MySQL 5.6+ （InnDB引擎）引入了在线 DDL 功能，通过 ALGORITHM 和 LOCK 参数可以显著减少对业务的影响。

一、`LOCK=NONE` 参数详解

1. 基本语法

ALTER TABLE large_table 
ADD INDEX idx_column_name (column_name) 
ALGORITHM=INPLACE, LOCK=NONE;

2. 参数说明

参数	可选值	推荐值	说明
ALGORITHM	COPY/INPLACE/INSTANT	INPLACE	INPLACE算法比COPY更高效，INSTANT仅适用于某些操作(8.0+)
LOCK	NONE/SHARED/EXCLUSIVE	NONE	NONE允许并发DML操作，SHARED允许读但阻塞写，EXCLUSIVE完全锁表

二、适用场景与限制

1. 适用操作类型

操作类型	是否支持 `LOCK=NONE`
添加二级索引	✅ 完全支持
添加主键	❌ 不支持
删除索引	✅ 完全支持
修改列类型	❌ 不支持
添加自增列	❌ 不支持

2. 版本兼容性

数据库版本	支持程度
MySQL 5.6+	基础支持
MySQL 8.0+	增强支持(INSTANT算法)
MariaDB 10.0+	完全支持

三、生产环境最佳实践

1. 执行前检查

-- 检查表大小
SELECT 
    table_name, 
    ROUND(data_length/1024/1024, 2) AS size_mb 
FROM information_schema.tables 
WHERE table_name = 'large_table';

-- 检查当前负载
SHOW PROCESSLIST;

2. 安全执行方案

-- 方案1：基本在线添加
ALTER TABLE large_table 
ADD INDEX idx_user_id (user_id) 
ALGORITHM=INPLACE, LOCK=NONE;

-- 方案2：分阶段添加（超大数据表）
CREATE INDEX idx_user_id ON large_table (user_id) 
ALGORITHM=INPLACE LOCK=NONE 
WITH PARSER ngram COMMENT '分阶段构建';

3. 监控与回滚

-- 监控进度（MySQL 8.0+）
SELECT * FROM performance_schema.events_stages_current 
WHERE event_name LIKE '%index%';

-- 回滚操作
DROP INDEX idx_user_id ON large_table ALGORITHM=INPLACE;

四、性能优化技巧

1. 大表特殊处理

-- 调整排序缓冲区（针对10GB+表）
SET SESSION sort_buffer_size = 256*1024*1024;
ALTER TABLE huge_table 
ADD INDEX idx_created_at (created_at) 
ALGORITHM=INPLACE, LOCK=NONE;

2. 低峰期执行策略

# 使用pt-online-schema-change工具
pt-online-schema-change \
--alter "ADD INDEX idx_email (email)" \
D=dbname,t=large_table \
--critical-load Threads_running=50 \
--max-load Threads_running=25 \
--chunk-size 10000 \
--execute

3. 空间优化

sql

复制

下载

-- 压缩索引（InnoDB）
ALTER TABLE compressed_table 
ADD INDEX idx_content (content(255)) 
ALGORITHM=INPLACE, LOCK=NONE 
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

五、故障处理方案

1. 常见错误解决

错误码	原因	解决方案
1214	不支持的操作类型	改用COPY算法或停机维护
1317	查询中断	增大wait_timeout参数
2013	连接丢失	使用持久连接或pt-osc工具

2. 长时间阻塞处理

sql

复制

下载

-- 查找阻塞进程
SELECT * FROM sys.schema_table_lock_waits;

-- 紧急终止
KILL [process_id];

六、基准测试数据

测试环境：MySQL 8.0, 10亿行数据表

方法	耗时	锁表时间	影响范围
传统ADD INDEX	3h45m	100%	完全不可用
ALGORITHM=INPLACE	2h10m	<1s	写入延迟增加15%
pt-online-schema-change	4h20m	0	几乎无感知

结论：对于1TB以下的表，ALGORITHM=INPLACE, LOCK=NONE 是最佳平衡方案；超大规模表建议使用pt-osc工具。