首页 > 数据库 >MySQL加索引会导致数据库锁表吗

MySQL加索引会导致数据库锁表吗

来源：互联网 2026-03-26 11:22:13

目录1. MySQL不同版本的区别MySQL 5.6及之前版本MySQL 5.6及之后版本（Online DDL）2. 不同索引创建方式的锁表情况创建普通二级索引（最常见场景）创建主键索引或改变主键创建全文索引或空间索引3. Online DDL的具体行为支持的Online DDL操作（通常不锁表）可能需要锁表的操作4. 查看DDL操作的锁机制查看DDL操作是否支持Online使用INPLACE和COPY算法对比5. 实际案例和最佳实践案例1：安全添加索引（推荐）案例2：大表添加索引的优化案例3：监控DD

1. MySQL不同版本的区别

MySQL 5.6及之前版本

实话实说，那个年代的MySQL在创建索引时确实会锁表。具体来说：

执行CREATE INDEX时，整个表会被加上排他锁（X锁）
在此期间，表完全不可读写，业务只能干等着
对于生产环境，这种影响可不是闹着玩的

MySQL 5.6及之后版本（Online DDL）

好消息是，从MySQL 5.6开始，情况有了根本性改善：

长期稳定更新的攒劲资源： >>>点此立即查看<<<

引入了Online DDL（在线数据定义语言）机制
创建二级索引时，DML操作（INSERT、UPDATE、DELETE）基本不受影响
不过话说回来，开始和结束时仍有短暂的元数据锁，但这个时间通常可以忽略不计

MySQL加索引会导致数据库锁表吗

2. 不同索引创建方式的锁表情况

创建普通二级索引（最常见场景）

-- MySQL 5.6+ 通常不锁表
CREATE INDEX idx_name ON users(name);

具体锁表情况分解：

开始阶段：获取元数据锁（MDL），持续时间极短，基本是毫秒级
创建阶段：DML操作畅通无阻，读写完全不受阻塞
结束阶段：再次获取元数据锁，快速更新表定义

创建主键索引或改变主键

-- 这个要小心，很可能锁表，特别是表里已经有数据的情况
ALTER TABLE users ADD PRIMARY KEY (id);

创建全文索引或空间索引

-- 这类特殊索引通常还是需要锁表
CREATE FULLTEXT INDEX idx_content ON articles(content);

3. Online DDL的具体行为

支持的Online DDL操作（通常不锁表）

-- 1. 添加二级索引
ALTER TABLE users ADD INDEX idx_email(email);

-- 2. 删除索引  
ALTER TABLE users DROP INDEX idx_email;

-- 3. 重命名索引
ALTER TABLE users RENAME INDEX old_name TO new_name;

-- 4. 修改索引类型（如改为HASH）
ALTER TABLE users DROP INDEX idx_email, ADD INDEX idx_email USING HASH(email);

可能需要锁表的操作

-- 1. 修改主键 - 这个要特别注意
ALTER TABLE users DROP PRIMARY KEY, ADD PRIMARY KEY(new_id);

-- 2. 修改列数据类型 - 数据类型变动通常锁表
ALTER TABLE users MODIFY COLUMN name VARCHAR(100);

-- 3. 添加自增列 - 特别是作为主键时
ALTER TABLE users ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY;

-- 4. 添加/删除外键约束 - 涉及关系变更
ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id);

4. 查看DDL操作的锁机制

查看DDL操作是否支持Online

-- 查看表结构，了解支持的算法和锁类型
SHOW CREATE TABLE users\G

-- 查看当前是否有锁等待
SELECT * FROM information_schema.INNODB_TRX WHERE trx_state = 'LOCK WAIT';

-- 或者通过performance_schema监控元数据锁
SELECT * FROM performance_schema.metadata_locks;

使用INPLACE和COPY算法对比

-- 使用INPLACE算法（尽量减少锁表）
ALTER TABLE users ADD INDEX idx_phone(phone) ALGORITHM=INPLACE;

-- 使用COPY算法（这个会锁表，慎用）
ALTER TABLE users ADD INDEX idx_phone(phone) ALGORITHM=COPY;

5. 实际案例和最佳实践

案例1：安全添加索引（推荐）

-- 1. 首先在测试环境验证 - 这是铁律
-- 2. 选择业务低峰期执行 - 凌晨2点比下午2点安全得多
-- 3. 监控进程状态 - 随时掌握执行情况

-- 使用INPLACE算法，明确指定不锁表
ALTER TABLE large_table ADD INDEX idx_create_time(create_time),
ALGORITHM=INPLACE,
LOCK=NONE;

案例2：大表添加索引的优化

-- 面对超大表，直接ALTER TABLE可能不是最佳选择
-- 这时候pt-online-schema-change工具就派上用场了

-- 使用Percona Toolkit的pt-online-schema-change
pt-online-schema-change \
  --alter "ADD INDEX idx_email(email)" \
  D=database,t=users \
  --execute

案例3：监控DDL执行进度

-- MySQL 5.7+提供了进度监控功能，这个很实用
SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED, 
       (WORK_COMPLETED/WORK_ESTIMATED)*100 as progress_pct
FROM performance_schema.events_stages_current
WHERE EVENT_NAME LIKE '%stage/innodb/alter%';

6. 不同锁级别的影响

-- LOCK=NONE: 最友好，允许读写，不阻塞任何操作
ALTER TABLE users ADD INDEX idx_name(name) LOCK=NONE;

-- LOCK=SHARED: 折中方案，允许读但阻塞写
ALTER TABLE users ADD INDEX idx_name(name) LOCK=SHARED;

-- LOCK=EXCLUSIVE: 最严格，阻塞读写（全表锁）
ALTER TABLE users ADD INDEX idx_name(name) LOCK=EXCLUSIVE;

7. 生产环境最佳实践

1.评估影响

-- 执行前先摸摸底，了解表的大小和当前负载
SELECT 
  TABLE_NAME,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 'Size(MB)',
  TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_db'
AND TABLE_NAME = 'your_table';

2.使用合适的工具

小表：直接使用ALTER TABLE ... ALGORITHM=INPLACE就够了
大表：请出pt-online-schema-change或gh-ost这些专业工具
云数据库：充分利用云服务商提供的在线DDL功能

3.执行步骤

# 1. 备份表结构 - 这是基本操作
mysqldump -d your_db your_table > table_structure.sql

# 2. 测试环境验证 - 不要跳过这一步
# 3. 业务低峰期执行 - 时机很重要  
# 4. 监控性能影响 - 全程盯紧
# 5. 验证索引效果 - 确认投资回报

4.避免的陷阱

-- 错误做法：在事务中执行DDL
START TRANSACTION;
-- 其他DML操作...
ALTER TABLE users ADD INDEX idx_name(name); -- 可能导致长时间锁表
COMMIT;

-- 正确做法：单独执行DDL
ALTER TABLE users ADD INDEX idx_name(name);

8. 常见问题解答

Q: Online DDL真的完全不锁表吗？

这个问题问得好。准确来说，Online DDL并非完全零锁表。它在开始和结束时需要获取元数据锁，虽然通常非常短暂（毫秒到秒级），但如果遇到长时间未提交的事务，就可能导致等待。所以说"基本不锁表"更准确。

Q: 如何知道DDL操作是否在执行中？

-- 查看当前运行进程
SHOW PROCESSLIST;

-- 或者使用sys库（MySQL 5.7+）
SELECT * FROM sys.session WHERE command = 'Query';

Q: 添加索引失败会怎样？

MySQL会回滚整个操作，表结构会恢复到之前状态。不过要注意的是，虽然数据不会丢，但期间可能已经消耗了大量系统资源。

9. 总结

场景	是否锁表	建议
MySQL 5.6+，添加二级索引	基本不锁表	使用`ALGORITHM=INPLACE, LOCK=NONE`
修改主键或列类型	通常锁表	使用pt-online-schema-change
大表添加索引	可能长时间锁表	使用gh-ost或分批操作
生产环境高峰期	尽量不操作	选择业务低峰期