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

欧立尼龙树脂固井工具 發表於 2026-1-4 10:27:31

<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">1. MySQL不同版本的区别</a></li><ul class="second_class_ul"><li><a href="#_lab2_0_0">MySQL 5.6及之前版本</a></li><li><a href="#_lab2_0_1">MySQL 5.6及之后版本（Online DDL）</a></li></ul><li><a href="#_label1">2. 不同索引创建方式的锁表情况</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_2">创建普通二级索引（最常见场景）</a></li><li><a href="#_lab2_1_3">创建主键索引或改变主键</a></li><li><a href="#_lab2_1_4">创建全文索引或空间索引</a></li></ul><li><a href="#_label2">3. Online DDL的具体行为</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_5">支持的Online DDL操作（通常不锁表）</a></li><li><a href="#_lab2_2_6">可能需要锁表的操作</a></li></ul><li><a href="#_label3">4. 查看DDL操作的锁机制</a></li><ul class="second_class_ul"><li><a href="#_lab2_3_7">查看DDL操作是否支持Online</a></li><li><a href="#_lab2_3_8">使用INPLACE和COPY算法对比</a></li></ul><li><a href="#_label4">5. 实际案例和最佳实践</a></li><ul class="second_class_ul"><li><a href="#_lab2_4_9">案例1：安全添加索引（推荐）</a></li><li><a href="#_lab2_4_10">案例2：大表添加索引的优化</a></li><li><a href="#_lab2_4_11">案例3：监控DDL执行进度</a></li></ul><li><a href="#_label5">6. 不同锁级别的影响</a></li><ul class="second_class_ul"></ul><li><a href="#_label6">7. 生产环境最佳实践</a></li><ul class="second_class_ul"><li><a href="#_lab2_6_12">1.评估影响</a></li><li><a href="#_lab2_6_13">2.使用合适的工具</a></li><li><a href="#_lab2_6_14">3.执行步骤</a></li><li><a href="#_lab2_6_15">4.避免的陷阱</a></li></ul><li><a href="#_label7">8. 常见问题解答</a></li><ul class="second_class_ul"><li><a href="#_lab2_7_16">Q: Online DDL真的完全不锁表吗？</a></li><li><a href="#_lab2_7_17">Q: 如何知道DDL操作是否在执行中？</a></li><li><a href="#_lab2_7_18">Q: 添加索引失败会怎样？</a></li></ul><li><a href="#_label8">9. 总结</a></li><ul class="second_class_ul"></ul></ul></div><p>答案是：<strong>可能会锁表，但取决于MySQL版本、索引类型和操作方式。</strong></p>
<p class="maodian"><a name="_label0"></a></p><h2>1. MySQL不同版本的区别</h2>
<p class="maodian"><a name="_lab2_0_0"></a></p><h3>MySQL 5.6及之前版本</h3>
<p><strong>会锁表</strong>（多数情况下）</p>
<ul><li>创建索引时会对表加上排他锁（X锁）</li><li>期间表不可读写，直到索引创建完成</li><li>对生产环境影响较大</li></ul>
<p class="maodian"><a name="_lab2_0_1"></a></p><h3>MySQL 5.6及之后版本（Online DDL）</h3>
<p><strong>通常不锁表</strong>，但仍有短暂锁定</p>
<ul><li>支持Online DDL（在线数据定义语言）</li><li>创建二级索引时，允许DML操作（INSERT、UPDATE、DELETE）</li><li>但开始和结束时有短暂元数据锁</li></ul>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410252071.png" /></p>
<p class="maodian"><a name="_label1"></a></p><h2>2. 不同索引创建方式的锁表情况</h2>
<p class="maodian"><a name="_lab2_1_2"></a></p><h3>创建普通二级索引（最常见场景）</h3>
<div class="jb51code"><pre class="brush:sql;">-- MySQL 5.6+ 通常不锁表
CREATE INDEX idx_name ON users(name);</pre></div>
<p><strong>锁表情况：</strong></p>
<ul><li><strong>开始阶段</strong>：获取元数据锁（MDL），非常短暂（毫秒级）</li><li><strong>创建阶段</strong>：允许DML操作，不阻塞读写</li><li><strong>结束阶段</strong>：再次获取元数据锁，更新表定义</li></ul>
<p class="maodian"><a name="_lab2_1_3"></a></p><h3>创建主键索引或改变主键</h3>
<div class="jb51code"><pre class="brush:sql;">-- 可能锁表，尤其是表已经有数据时
ALTER TABLE users ADD PRIMARY KEY (id);</pre></div>
<p class="maodian"><a name="_lab2_1_4"></a></p><h3>创建全文索引或空间索引</h3>
<div class="jb51code"><pre class="brush:sql;">-- 通常需要锁表
CREATE FULLTEXT INDEX idx_content ON articles(content);</pre></div>
<p class="maodian"><a name="_label2"></a></p><h2>3. Online DDL的具体行为</h2>
<p class="maodian"><a name="_lab2_2_5"></a></p><h3>支持的Online DDL操作（通常不锁表）</h3>
<div class="jb51code"><pre class="brush:sql;">-- 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);</pre></div>
<p class="maodian"><a name="_lab2_2_6"></a></p><h3>可能需要锁表的操作</h3>
<div class="jb51code"><pre class="brush:sql;">-- 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);</pre></div>
<p class="maodian"><a name="_label3"></a></p><h2>4. 查看DDL操作的锁机制</h2>
<p class="maodian"><a name="_lab2_3_7"></a></p><h3>查看DDL操作是否支持Online</h3>
<div class="jb51code"><pre class="brush:sql;">-- 查看支持的算法和锁类型
SHOW CREATE TABLE users\G
-- 查看具体的DDL操作信息
SELECT * FROM information_schema.INNODB_TRX
WHERE trx_state = 'LOCK WAIT';
-- 或者使用performance_schema监控
SELECT * FROM performance_schema.metadata_locks;</pre></div>
<p class="maodian"><a name="_lab2_3_8"></a></p><h3>使用INPLACE和COPY算法对比</h3>
<div class="jb51code"><pre class="brush:sql;">-- 使用INPLACE算法（尽量减少锁表）
ALTER TABLE users ADD INDEX idx_phone(phone) ALGORITHM=INPLACE;
-- 使用COPY算法（会锁表）
ALTER TABLE users ADD INDEX idx_phone(phone) ALGORITHM=COPY;</pre></div>
<p class="maodian"><a name="_label4"></a></p><h2>5. 实际案例和最佳实践</h2>
<p class="maodian"><a name="_lab2_4_9"></a></p><h3>案例1：安全添加索引（推荐）</h3>
<div class="jb51code"><pre class="brush:sql;">-- 1. 首先在测试环境验证
-- 2. 选择业务低峰期执行
-- 3. 监控进程状态
-- 使用INPLACE算法，指定不锁表
ALTER TABLE large_table
ADD INDEX idx_create_time(create_time),
ALGORITHM=INPLACE,
LOCK=NONE;</pre></div>
<p class="maodian"><a name="_lab2_4_10"></a></p><h3>案例2：大表添加索引的优化</h3>
<div class="jb51code"><pre class="brush:sql;">-- 对于超大表，可以采用pt-online-schema-change工具
-- 而不是直接执行ALTER TABLE
-- 使用pt-online-schema-change（Percona Toolkit）
pt-online-schema-change \
--alter "ADD INDEX idx_email(email)" \
D=database,t=users \
--execute</pre></div>
<p class="maodian"><a name="_lab2_4_11"></a></p><h3>案例3：监控DDL执行进度</h3>
<div class="jb51code"><pre class="brush:sql;">-- 在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%';</pre></div>
<p class="maodian"><a name="_label5"></a></p><h2>6. 不同锁级别的影响</h2>
<div class="jb51code"><pre class="brush:sql;">-- 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;</pre></div>
<p class="maodian"><a name="_label6"></a></p><h2>7. 生产环境最佳实践</h2>
<p class="maodian"><a name="_lab2_6_12"></a></p><h3>1.评估影响</h3>
<div class="jb51code"><pre class="brush:sql;">-- 先检查表大小和当前负载
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';</pre></div>
<p class="maodian"><a name="_lab2_6_13"></a></p><h3>2.使用合适的工具</h3>
<ul><li><strong>小表</strong>：直接使用<code>ALTER TABLE ... ALGORITHM=INPLACE</code></li><li><strong>大表</strong>：使用<code>pt-online-schema-change</code>或<code>gh-ost</code></li><li><strong>云数据库</strong>：使用云服务商提供的在线DDL功能</li></ul>
<p class="maodian"><a name="_lab2_6_14"></a></p><h3>3.执行步骤</h3>
<div class="jb51code"><pre class="brush:sql;"># 1. 备份表结构
mysqldump -d your_db your_table > table_structure.sql
# 2. 测试环境验证
# 3. 业务低峰期执行
# 4. 监控性能影响
# 5. 验证索引效果</pre></div>
<p class="maodian"><a name="_lab2_6_15"></a></p><h3>4.避免的陷阱</h3>
<div class="jb51code"><pre class="brush:sql;">-- 错误做法：在事务中执行DDL
START TRANSACTION;
-- 其他DML操作...
ALTER TABLE users ADD INDEX idx_name(name); -- 可能导致长时间锁表
COMMIT;
-- 正确做法：单独执行DDL
ALTER TABLE users ADD INDEX idx_name(name);</pre></div>
<p class="maodian"><a name="_label7"></a></p><h2>8. 常见问题解答</h2>
<p class="maodian"><a name="_lab2_7_16"></a></p><h3>Q: Online DDL真的完全不锁表吗？</h3>
<p><strong>A:</strong> 不完全。Online DDL在开始和结束时需要获取元数据锁，虽然非常短暂（毫秒到秒级），但如果有长时间未提交的事务，可能会导致等待。</p>
<p class="maodian"><a name="_lab2_7_17"></a></p><h3>Q: 如何知道DDL操作是否在执行中？</h3>
<div class="jb51code"><pre class="brush:sql;">-- 查看当前运行进程
SHOW PROCESSLIST;
-- 或者使用sys库（MySQL 5.7+）
SELECT * FROM sys.session WHERE command = 'Query';</pre></div>
<p class="maodian"><a name="_lab2_7_18"></a></p><h3>Q: 添加索引失败会怎样？</h3>
<p><strong>A:</strong> MySQL会回滚操作，表会恢复到之前状态，但期间可能消耗了大量系统资源。</p>
<p class="maodian"><a name="_label8"></a></p><h2>9. 总结</h2>
<table><thead><tr><th>场景</th><th>是否锁表</th><th>建议</th></tr></thead><tbody><tr><td>MySQL 5.6+，添加二级索引</td><td>基本不锁表</td><td>使用<code>ALGORITHM=INPLACE, LOCK=NONE</code></td></tr><tr><td>修改主键或列类型</td><td>通常锁表</td><td>使用pt-online-schema-change</td></tr><tr><td>大表添加索引</td><td>可能长时间锁表</td><td>使用gh-ost或分批操作</td></tr><tr><td>生产环境高峰期</td><td>尽量不操作</td><td>选择业务低峰期</td></tr></tbody></table>
<p><strong>最终建议：</strong></p>
<ul><li><strong>MySQL 5.6+版本</strong>：添加普通二级索引通常不锁表，可放心使用</li><li><strong>主键操作或列修改</strong>：需要谨慎，可能锁表</li><li><strong>超大表操作</strong>：使用专业工具（pt-online-schema-change、gh-ost）</li><li><strong>生产环境</strong>：先在测试环境验证，选择合适时间执行</li><li><strong>监控</strong>：执行时监控数据库性能和锁状态</li></ul>
<p>        在大多数现代MySQL部署中（5.6+），正确使用Online DDL可以实现在不锁表的情况下添加索引，对业务影响极小。</p>

頁: [1]

琼殿技术论坛's Archiver

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