MySQL如何安全的给小表加字段

软件发布|下载排行|最新软件

当前位置:首页IT学院IT技术

MySQL如何安全的给小表加字段

唯爱编程   2021-01-30 我要评论

MySQL学习笔记-如何安全的给小表加字段

如果要给一个大表加字段,你一般都会非常谨慎小心,以免对线上业务造成影响,但实际上给一个小表加字段不慎操作也会导致线上业务出问题,这篇文章主要学习一下MySQLMDL锁对性能的影响,防止采坑。

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 AMDL读锁还没有释放,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 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,作用是释放t1MDL锁。

  • 如果主库DDL命令在Q4之前到达从库,没有影响
  • 如果主库DDL时刻2到达从库,此时会有MDL写锁,会造成Q5执行失败,备份过程被终止
  • 如果主库DDL时刻3到达从库,由于备份过程占用读锁,会导致binlog复制被阻塞,会造成主从延迟,直到备份完成Q6执行之后从库才能继续复制主库binlog
  • 如果主库DDL时刻4到达从库,没有影响

Copyright 2022 版权所有 软件发布 访问手机版

声明:所有软件和文章来自软件开发商或者作者 如有异议 请与本站联系 联系我们