MySQL如何安全的给小表加字段
唯爱编程 人气:0MySQL学习笔记-如何安全的给小表加字段
如果要给一个大表加字段,你一般都会非常谨慎小心,以免对线上业务造成影响,但实际上给一个小表加字段不慎操作也会导致线上业务出问题,这篇文章主要学习一下MySQL
中MDL
锁对性能的影响,防止采坑。
1.笔记图
2.全局锁
- 全局锁就是对整个数据库实例加锁
- 命令:
Flush tables with read lock (FTWRL)
- 全局锁现象:数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句会被阻塞
3.数据逻辑备份的方式
- 加全局锁(MyISAM):
- 通过
FTWRL
确保不会有其他线程对数据库做更新,然后对整个库做备份 - 如果在主库上备份,备份期间都不能执行更新
- 如果在从库上备份,备份期间从库不能执行主库同步过来的
binlog
,会导致主从延迟 - 备份为什么要加锁?:
- 假设现在要维护购买系统,用户账户余额表和用户课程表
- 发起一个逻辑备份,不加锁的话,备份系统备份的得到的库不是一个逻辑时间点
- 通过
- 事务(InnoDB):
- 方自带的逻辑备份工具是
mysqldump
mysqldump
使用single-transaction
参数,导数据之前就会启动一个事务,拿到一致性视图- 由于
MVCC
的支持,这个过程中数据是可以正常更新的
- 方自带的逻辑备份工具是
4.表级锁
- 表锁:
- 加表锁的语法是
lock tables … read/write
- 如果在某个线程
A
中执行lock tables t1 read, t2 write
这个语句,则其他线程写t1
、读写t2
的语句都会被阻塞 - 同时,线程
A
在执行unlock tables
之前,也只能执行读t1
、读写t2
的操作,不能访问其他表 - 对于
InnoDB
这种支持行锁的引擎,一般不使用lock tables
命令来控制并发 - 可以用
unlock tables
主动释放锁,也可以在客户端断开的时候自动释放
- 如果在某个线程
- 加表锁的语法是
- 元数据锁(meta data lock,MDL)
- 如果一个查询正在遍历一个表中的数据,执行期间另一个线程对这个表结构做变更,删了一列,查询线程拿到的结果跟表结构对不上,肯定是不行的
- 在访问一个表的时候会被自动加上
MDL
锁,其作用是保证读写的正确性 - 当对一个表做增删改查操作的时候,加
MDL
读锁,读锁之间不互斥,可以有多个线程同时对一张表增删改查 - 当要对表做结构变更操作的时候,加
MDL
写锁,读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性
5.给一个小表加个字段,导致整个库挂了
-
现象:给一个小表加个字段,导致整个库挂了
-
给一个表加字段,或者修改字段,或者加索引,需要扫描全表的数据,一般在对大表操作的时候,会特别小心,以免对线上服务造成影响,实际上,即使是小表,操作不慎也会出问题
-
session A
先启动,这时候会对表t
加一个MDL
读锁 -
session B
需要的也是MDL
读锁,因此可以正常执行 -
session A
的MDL
读锁还没有释放,session C
需要MDL
写锁,因此只能被阻塞 -
之后所有要在表
t
上新申请MDL
读锁的请求也会被session C
阻塞,所有对表的增删改查操作都需要先申请MDL
读锁,这个表此时完全不可读写
6.如何安全地给小表加字段?
- 事务中的
MDL
锁,在语句执行开始时申请,但是语句结束后并不会马上释放,而会等到整个事务提交后再释放 - 首先要解决长事务,事务不提交,会一直占着
MDL
锁,如果要做DDL
变更的表刚好有长事务在执行,要考虑先暂停DDL
,或者kill
掉这个长事务 - 如果要变更的表是一个热点表,请求很频繁,不得不加个字段,该怎么做呢?
- 在 alter table 语句里面设定等待时间,在这个指定的等待时间里面能够拿到
MDL
写锁最好,拿不到也不要阻塞后面的业务语句,先放弃 - 再通过重试命令重复这个过程
MariaDB
已经合并了AliSQL
的这个功能,所以这两个开源分支目前都支持DDL NOWAIT/WAIT n
这个语法
- 在 alter table 语句里面设定等待时间,在这个指定的等待时间里面能够拿到
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...
7.课后问题
当备库用single-transaction
做逻辑备份的时候,如果从主库的binlog
传来一个DDL
语句会怎么样?
- 从库备份过程中几个关键的语句:
Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Q2:START TRANSACTION WITH CONSISTENT SNAPSHOT;
/* other tables */
Q3:SAVEPOINT sp;
/* 时刻 1 */
Q4:show create table `t1`;
/* 时刻 2 */
Q5:SELECT * FROM `t1`;
/* 时刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp;
/* 时刻 4 */
/* other tables */
Tips:
Q1
表示为了确保RR
(可重复读)隔离级别,设置RR
隔离级别(Q1)
,Q2
表示WITH CONSISTENT SNAPSHOT
这个语句执行完就可以得到一个一致性视图(Q2)
,Q3
表示设置一个保存点,Q4
表示show create
是为了拿到表结构,Q5
表示正式导数据,Q6
表示回滚到SAVEPOINT sp
,作用是释放t1
的MDL
锁。
- 如果主库
DDL
命令在Q4
之前到达从库,没有影响 - 如果主库
DDL
在时刻2
到达从库,此时会有MDL写锁
,会造成Q5
执行失败,备份过程被终止 - 如果主库
DDL
在时刻3
到达从库,由于备份过程占用读锁,会导致binlog
复制被阻塞,会造成主从延迟,直到备份完成Q6
执行之后从库才能继续复制主库binlog
- 如果主库
DDL
在时刻4
到达从库,没有影响
加载全部内容