为什么MySQL大表加索引会导致锁表该如何安全添加

小伟君_7091

小伟君_7091

2026-11-20

981人浏览

原创

MySQL大表加索引不必然锁表,但默认ALGORITHM=DEFAULT和LOCK=DEFAULT易因MDL等待或退化为COPY算法导致写阻塞甚至全表不可用;真正无锁需显式指定ALGORITHM=INPLACE、LOCK=NONE,并确保无长事务、外键、全文索引且innodb_file_per_table=ON。

为什么mysql大表加索引会导致锁表该如何安全添加

MySQL大表加索引不必然锁表,但默认行为极大概率导致写阻塞甚至全表不可用——关键在是否触发元数据锁(MDL)等待,以及是否退化为COPY算法。

ALGORITHM=INPLACE, LOCK=NONE 为什么不是默认值

MySQL 自动选择 ALGORITHM=DEFAULT 和 LOCK=DEFAULT,看似省事,实则危险:

  • 只要表上有任何未提交事务(哪怕只是个 SELECT ... FOR UPDATE 或隐式事务中的长查询),MDL 就会卡住 DDL,状态显示为 Waiting for table metadata lock
  • LOCK=DEFAULT 在多数二级索引场景下会降级为 LOCK=SHARED(读可用、写阻塞),而非你期待的 LOCK=NONE
  • 外键、触发器、全文索引、主键变更等操作,根本**不支持** INPLACE,MySQL 会静默 fallback 到 ALGORITHM=COPY,全程锁表
  • innodb_file_per_table=OFF 时,INPLACE 直接不可用,报错 ALGORITHM=INPLACE is not supported

如何确认加索引是否真能并发读写

执行前必须验证计划,不能只看语句写了 LOCK=NONE:

  • 先跑 EXPLAIN FORMAT=JSON ALTER TABLE users ADD INDEX idx_name (name);,检查输出中是否有 "alter_algorithm": "inplace" 和 "lock": "none"
  • 若出现 "lock": "shared",说明写操作会被暂停,应避开高峰;若为 "lock": "exclusive",等于直接锁表,立刻中止
  • 查 SHOW VARIABLES LIKE 'innodb_file_per_table';,确保值为 ON
  • 查 SELECT * FROM information_schema.INNODB_TRX WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;,清掉运行超 1 分钟的事务

大表加索引的实际执行策略

即使满足 INPLACE+NONE,I/O 和 CPU 压力仍会让线上查询变慢——这不是锁,是资源挤占:

win可用的mysql指令skill
win可用的mysql指令skill

安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。

下载
  • 优先用 pt-online-schema-change:自动分片、限流、校验,适合千万级以上表,命令如 pt-osc --alter "ADD INDEX idx_email(email)" D=test,t=users --max-load="Threads_running=25" --critical-load="Threads_running=50"
  • 若坚持原生命令,加索引前临时调小 innodb_buffer_pool_size(避免刷脏页风暴),加完立即恢复
  • 监控 SHOW ENGINE INNODB STATUS\G 中的 FILE I/O 部分,关注 pending normal aio reads 是否持续升高
  • 绝对不要在从库上直接执行 DDL,GTID 模式下可能引发复制延迟突增甚至中断

被锁住时怎么快速恢复业务

一旦发现 ALTER TABLE 卡住,且其他查询全挂起,别等它自己完成:

  • 用 SHOW PROCESSLIST; 找出状态为 Waiting for table metadata lock 的线程,记下其 Id
  • 再查 SELECT THREAD_ID, EVENT_NAME, SOURCE FROM performance_schema.events_waits_current WHERE EVENT_NAME = 'wait/lock/metadata/sql/mdl';(MySQL 8.0+),定位持有 MDL 的源头事务
  • 对源头事务执行 KILL [thread_id],不是杀 DDL 线程本身——杀错线程只会让锁更久
  • 如果源头是长事务且无法 kill(比如应用层没暴露连接),只能临时重启 MySQL,但这是最后手段

真正容易被忽略的点:MDL 锁和事务生命周期绑定,而不是和 SQL 执行时间绑定。一个没提交的 SELECT ... FOR UPDATE 可能在后台睡了 10 分钟,却足以让后续所有 DDL 排队等待。所以加索引前,必须确认“此刻没有活跃事务”,而不是“刚才没看到长查询”。

相关文章

PHP速学视频免费教程(入门到精通)
PHP速学视频免费教程(入门到精通)

PHP怎么学习?PHP怎么入门?PHP在哪学?PHP怎么学才快?不用担心,这里为大家提供了PHP速学教程(入门到精通),有需要的小伙伴保存下载就能学习啦!

下载

相关标签:

mysql

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

相关专题

更多
数据分析工具有哪些
数据分析工具有哪些

数据分析工具有Excel、SQL、Python、R、Tableau、Power BI、SAS、SPSS和MATLAB等。详细介绍:1、Excel,具有强大的计算和数据处理功能;2、SQL,可以进行数据查询、过滤、排序、聚合等操作;3、Python,拥有丰富的数据分析库;4、R,拥有丰富的统计分析库和图形库;5、Tableau,提供了直观易用的用户界面等等。

2023.10.12

5123

8

SQL中distinct的用法
SQL中distinct的用法

SQL中distinct的语法是“SELECT DISTINCT column1, column2,...,FROM table_name;”。本专题为大家提供相关的文章、下载、课程内容,供大家免费下载体验。

2023.10.27

1051

4

SQL中months_between使用方法
SQL中months_between使用方法

在SQL中,MONTHS_BETWEEN 是一个常见的函数,用于计算两个日期之间的月份差。想了解更多SQL的相关内容,可以阅读本专题下面的文章。

2024.02.23

1349

5

SQL出现5120错误解决方法
SQL出现5120错误解决方法

SQL Server错误5120是由于没有足够的权限来访问或操作指定的数据库或文件引起的。想了解更多sql错误的相关内容,可以阅读本专题下面的文章。

2024.03.06

7241

10

sql procedure语法错误解决方法
sql procedure语法错误解决方法

sql procedure语法错误解决办法:1、仔细检查错误消息;2、检查语法规则;3、检查括号和引号;4、检查变量和参数;5、检查关键字和函数;6、逐步调试;7、参考文档和示例。想了解更多语法错误的相关内容,可以阅读本专题下面的文章。

2024.03.06

3843

4

oracle数据库运行sql方法
oracle数据库运行sql方法

运行sql步骤包括:打开sql plus工具并连接到数据库。在提示符下输入sql语句。按enter键运行该语句。查看结果,错误消息或退出sql plus。想了解更多oracle数据库的相关内容,可以阅读本专题下面的文章。

2024.04.07

7220

11

sql中where的含义
sql中where的含义

sql中where子句用于从表中过滤数据,它基于指定条件选择特定的行。想了解更多where的相关内容,可以阅读本专题下面的文章。

2024.04.29

10141

6

sql中删除表的语句是什么
sql中删除表的语句是什么

sql中用于删除表的语句是drop table。语法为drop table table_name;该语句将永久删除指定表的表和数据。想了解更多sql的相关内容,可以阅读本专题下面的文章。

2024.04.29

1370

5

sql中删除一列的命令是什么
sql中删除一列的命令是什么

在sql中,使用alter table语句可以删除一列,语法为:alter table table_name drop column column_name。想了解更多sql的相关内容,可以阅读本专题下面的文章。

2024.04.29

1192

5

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程