关于MySQL建表对DML的影响

朱雀 2022-06-09 13:10 401阅读 0赞

今天一位同学问到线上曾经碰到过连续建表，导致阻塞普通的insert、update等。不过也没有保留现场。因此有疑问为什么建表会影响DML？

**分析**

**         **首先这个现象不是在所有场景都会碰到（否则MySQL的用户们早就跳起来了）。

一来建表这个操作本身很快，只涉及到写表定义文件和初始化表空间。中间涉及到redo和undo的操作也很少（这里只讨论InnoDB表）。因此除非碰到磁盘IO响应不了，否则多数情况下建表操作很快结束，不会“稳定复现”

二来即使由于io原因，建表过程执行时间较长，建表操作也不会阻塞一些DML操作。

因此只能从代码出发看冲突的case。

假设session 1正在执行一个create table操作，且由于io原因阻塞在写表空间文件这个步骤上。讨论session2作如下操作的场景。

**无主键表insert**

**         **此时insert操作由于需要申请系统自增主键，需要对dict\_sys->mutex加锁。而这个锁需要等session1建表操作完成后才释放，因此出现等待。

**有外键表的操作**

**         **此时session2需要判断外键一致性，需要对dict\_sys->mutex加锁。

这里包含几个方面：外键约束的child表插入数据时和parent表删除数据时，已经这两个表的关联外键字段被修改时，均会触发等待。

有同学会说我们线上这两种情况都禁止了，是不是就不会因为这个锁的原因导致阻塞dml？

**新打开表时**

**         **若这个insert操作需要新打开一个表时，需要根据表名从字典中取出信息，也会触发等待。

即使原来已经打开过的表，也会因为执行了flush table或者表空间淘汰而要求下次访问需要重新打开。

**影响的其他操作**

**         **顺着dict\_sys->mutex我们还可以发现有以下几个操作，若发生在session2，都会被阻塞

1)                 1) Flush tables

2)                    select \* from information\_schema.tables;

2) 以上两个因为都要访问到表对象列表，还比较好理解

3)                 select \* from information\_schema.innodb\_sys\_tables;

3)实际上可以用另外一个锁来单独处理sys\_tables

4)                  show create table another\_table

这个是因为必须判断是否有外键关联

简单留个问题：为什么show tables并不会被阻塞？

发表评论取消回复

表情：

评论列表（有 0 条评论，401人围观）

还没有评论，来说两句吧...

相关阅读

相关 mysql的大小写对性能的影响问题

一. mysql对大小写是否敏感是根据平台区分的 1. 默认情况下: linux环境严格区分大小写，windows环境下不区分大小写\\ 所以正常开发的时候在本地测

待我称王封你为后i/ 2023年06月18日 14:56/ 0 赞/ 3 阅读

相关 MySQL表数据的增删改（DML）

之前我们详细的了解了数据库的数据类型 [MySQL数据类型详解][MySQL] 本篇博客我们来看一下怎么对表中的数据进行增（insert）删(delete)改(update)

梦里梦外;/ 2022年11月15日 11:45/ 0 赞/ 156 阅读

相关 mysqldump对mysql数据库的影响

对于想入门或者初级，中级mysql数据库运维人员，了解mysqldump对mysql数据库的影响，是非常必要的，当执行mysqldump命令之后，mysql后台执行了什么，下面

小咪咪/ 2022年11月07日 04:26/ 0 赞/ 216 阅读

相关 mysql建表文档_mysql建表建库

create database if not exists \`dbname\`; DROP TABLE IF EXISTS \`tbname\`; CREATE TABL

「爱情、让人受尽委屈。」/ 2022年11月01日 00:48/ 0 赞/ 203 阅读

相关 mysql--DML

DML (data manipulation language, 数据操作语言):insert, delete, update, 对标当中的数据进行增删改. 1.

蔚落/ 2022年10月23日 08:28/ 0 赞/ 132 阅读

相关 Oracle 对表设置并行度对执行计划的影响

Oracle数据库版本11.2.0.4，最近发现有一条SQL很慢，执行一次要20多s,GG\_FUNCTION\_LOCATION这张表很大，发现这张表有并行度，修改并

╰+攻爆jí腚メ/ 2022年08月22日 11:25/ 0 赞/ 139 阅读

相关关于MySQL建表对DML的影响

今天一位同学问到线上曾经碰到过连续建表，导致阻塞普通的insert、update等。不过也没有保留现场。因此有疑问为什么建表会影响DML？分析首

朱雀/ 2022年06月09日 13:10/ 0 赞/ 402 阅读

相关 MySQL字符编码，对表关联查询的速度影响很大

最近遇到一个left join，表关联查询的SQL，很简单，如下： select count(0) from tableA left join tableB on t

男娘i/ 2022年02月24日 08:10/ 0 赞/ 208 阅读

相关 InnoDB 隔离模式对 MySQL 性能的影响

InnoDB 隔离模式对 MySQL 性能的影响过去的几个月我写了两篇文章，一篇是[InnoDB 事务历史][InnoDB]相关的危险债务，另一篇是关于[MVCC 可能

た入场券/ 2022年01月27日 08:37/ 0 赞/ 257 阅读

相关 MySQL二进制日志格式对复制的影响

基于SQL语句的复制 - SBR 主库二进制日志格式使用STATEMENT 在 MySQL 5.1之前仅存在SBR模式, 又称之为逻辑复制. 主库记录CUD操作的SQ

Love The Way You Lie/ 2021年09月21日 06:16/ 0 赞/ 260 阅读