Mysql 长镜头

山涛
141 阅读10分钟

1. Mysql中的锁

image.png

1.1 全局锁 FTWRL

flush tables with read lock

如果要释放全局锁，则要执行下面命令；或者会话结束自动释放。

unlock tables

全局锁主要应用于做全库逻辑备份。 全局锁的缺点: 开启全局锁后所有库表只读,造成业务停滞

官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数–single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。

因为有的引擎不支持事务比如 MyISAM，所以只能通过全局锁这种方式来进行数据备份。

set global readonly=true 也可以实现全库只读，但是不会自动重置 （全局锁在链接异常断开时会自动释放）。这样如果客户端出了问题将整个业务带来很大的风险（业务不可用）。

1.2 表级锁

MySQL 里面表级别的锁有这几种：

  • 表锁；
  • 元数据锁（MDL）;
  • 意向锁；
  • AUTO-INC 锁；

1.2.1 表锁

支持会话结束自动释放

//表级别的共享锁，也就是读锁；
lock tables t_student read;

//表级别的独占锁，也就是写锁；
lock tables t_stuent wirte;

表锁除了会限制别的线程的读写外，也会限制本线程接下来在unlock tables之前的读写操作。 在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。（没有更细粒度的也只能用这个了）。 而对于InnoDB这种支持行锁的引擎，一般不使用locktables命令来控制并发，毕竟锁住整个表的影响面还是太大。

1.2.2 MDL（metadatalock)

MDL不需要显式使用，在访问一个表的时候会被自动加上。

  • 当有线程在执行 select 语句（ 加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（ 申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（ 释放 MDL 读锁）。
  • 反之，当有线程对表结构进行变更（ 加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 操作（ 申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（ 释放 MDL 写锁）。

MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。

1.2.3 意向锁

Innodb的意向锁主要用于解决多粒度的锁并存的情况。比如事务A要在一个表上加S锁，如果表中的一行已被事务B加了X锁,那么该锁的申请也应被阻塞。如果表中的数据很多，逐行检查锁标志的开销将很大，系统的性能将会受到影响。为了解决这个问题，可以在表级上引入新的锁类型来表示其所属行的加锁情况，这就引出了“意向锁”的概念。
●意向共享锁（IS）： 事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
●意向排他锁（IX）： 事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
注意： 这两种意向锁都是表锁。
意向锁的目的是为了快速判断表里是否有记录被加锁。

image.png

1.2.3 AUTO-INC 锁

在为某个字段声明 AUTO_INCREMENT 属性时，之后在插入数据时可不指定字段的值,数据库会自动递增赋值,这主要是通过 AUTO-INC 锁实现的。
锁的释放时机: 执行完插入语句后就会立即释放

一个事务在持有 AUTO-INC 锁的过程中，其他事务的如果要向该表插入语句都会被阻塞，从而保证插入数据时，被 AUTO_INCREMENT 修饰的字段的值是连续递增的。但是， AUTO-INC 锁再对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。
因此， 在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增。一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。InnoDB 存储引擎提供了个 innodb_autoinc_lock_mode 的系统变量，是用来控制选择用 AUTO-INC 锁，还是轻量级的锁。

  • 当 innodb_autoinc_lock_mode = 0，就采用 AUTO-INC 锁；
  • 当 innodb_autoinc_lock_mode = 2，就采用轻量级锁；
  • 当 innodb_autoinc_lock_mode = 1，这个是默认值，两种锁混着用，如果能够确定插入记录的数量就采用轻量级锁，不确定时就采用 AUTO-INC 锁。

不过，当 innodb_autoinc_lock_mode = 2 是性能最高的方式，但是会带来一定的问题。因为并发插入的存在，在每次插入时，自增长的值可能不是连续的.

1.3 行级锁

InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。 行级锁的类型主要有三类：

  • Record Lock，记录锁，也就是仅仅把一条记录锁上；
  • Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
  • Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

普通的 select 语句是不会对记录加锁的，如果要在查询时对记录加行锁，可以使用下面这两个方式：

//对读取的记录加共享锁
select ... lock in share mode;

//对读取的记录加独占锁  / 排他锁
select ... for update;

上面这两条语句必须再一个事务中，当事务提交了，锁就会被释放，因此在使用这两条语句的时候，要加上 begin、start transaction 或者 set autocommit = 0。

如果查询条件用了索引/主键，那么select ..... for update就会进行行锁。 如果是普通字段(没有索引/主键)，那么select ..... for update就会进行锁表。

image.png

2. 索引

Mysql中的索引有哈希索引(Memory引擎)、B+树、全文索引(MyISAM)。

InnoDb中主要的索引为B+树,B+树由B树演化而来。我们通过图片看下两者的区别。

image.png

image.png

image.png

B树与B+树的区别有两个:

  1. B树的所有阶段都有卫星数据,而B+树的非叶子节点只存储索引信息
  2. B+树的叶子节点有一个向右指针依次指向右边的叶子节点,形成一个有序的链表
  3. B+所有索引数据全部在叶子结点,每次查询都必须到叶子结点.因此B+树查询性能比较稳定。

第一个区别会导致相同的数据情况下B+树比B树更矮胖,查询需要磁盘IO的次数就更少。 第二个区别使得B+树更有利于范围查询。

2.1 最左前缀匹配原则

建立了一个联合索引 id_name_age_index，实际上相当于建立了三个索引（id）（id_name）（id_name_age)。满足最左前缀的where条件查询都可以用上这个联合索引,不论字段在查询语句中排列的顺序。 下列查询语句能用上联合索引id_name_age_index

  1. where id = ？
  2. where id = ？and name = ? 、where name = ？and id = ?
  3. where id = ？and name = ? and age =? 三字段组合排列 以下查询语句不能用上联合索引
  4. where name = ? and age = ?

where后面的查询条件，不论是使用（id，age，name）（name，id，age）还是（age，name，id）顺序，在查询时都使用到了联合索引,这是因为mysql有查询优化器会选择最优的执行计划。

image.png

3. 事务

image.png

  1. 原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
  2. 一致性： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
  3. 隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
  4. 持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

3.1 READ-UNCOMMITTED 读未提交

读未提交级别下,事务2可以读到事务1没有提交的修改。 如下图,事务1修改了一行数据,这个时候他自动持有该行独占锁,因为该级别下锁释放的时机在于修改完立即释放,因此事务2在事务过程中可能读到事务1未提交的变更,导致脏读。

graph TD
表题>读脏数据图示]
事务A --> A查询1[read a=10] --> A查询2[write a=20] --->|回滚| 事务A提交((提交))
事务B ----> 查询1[read a=20] -->  事务B提交((提交))

3.2 READ COMMITTED 读已提交

既然读未提交可能导致脏读,那么锁释放的时机就等到提交之后？在读已提交隔离级别下,mysql innodb就是这么干的, 但是这种隔离级别仍可带来不可重复读的问题。

事务A在做数据变更时,持有行独占锁,锁在事务提交时释放。独占锁与共享锁兼容,因此事务B可以在事务A修改期间进行第一次读,那有小伙伴就会说如果如果这个时候读写锁互斥,是不是就没有问题了？emm,确实木有问题,但是这个并发性能就太差了。

既然为了并发性能不想阻塞等待最新的数据，那就无视当前持有锁的操作，读取最新的历史版本数据先用着,这就是MVCC干的事情。

正因为对并发性能的极致追求或者说贪婪，该级别下还是遗留了不可重复读和幻读问题:

  1. MVCC版本的生成时机: 是每次select时，这就意味着，如果我们在事务A中执行多次的select，在每次select之间有其他事务更新了我们读取的数据并提交了，那就出现了不可重复读
  2. 锁的范围: 因为没有间隙锁，这就意味着，如果我们在事务A中多次执行select * from user where age>18 and age<30 for update时，其他事务是可以往age为(18,30)这个区间插入/删除数据的，那就出现了幻读
graph TD
表题> 不可重复读图示]
事务A --> a=20 --> 提交
事务B --> 查询1[select a = 10] ---> 查询2[select a =20]-->  事务B提交((提交))

3.3 REPEATABLE READ 可重复读

既然读已提交依然有较大的数据可靠性能问题，那就再往前迈一小步，可重复读，该级别在读已提交的基础上做了两点修改，从而避免了不可重复读和幻读：

  1. MVCC版本的生成时间: 一次事务中只在第一次select时生成版本，后续的查询都是在这个版本上进行，从而实现了可重复读
  2. 锁的范围: 在行锁的基础上，加上Gap Lock，从而形成Next-Key Lock，在所有遍历过的(不管是否匹配条件)索引行上以及之间的区域上，都加上锁，阻塞其他事务在遍历范围内进行写操作，从而避免了幻读
graph TD
表题> 可重复读图示]
事务A --> a=20 --> 提交
事务B --> 查询1[read a = 10 from MVCC v1024] ---> 查询2[read a =10 from MVCC v1024]-->  事务B提交((提交))

可重复读目前是innoDb的默认隔离级别。

InnoDB在可重复读级别下已经将数据可靠性和并发性能两方面做得尽善尽美了，但前提是用户查询时能够主动善用Locking Reads，即前文提到的select ... lock in share mode和select ... for update。如果只是使用普通的select，依然防不住幻读。 这是因为MVCC的快照只对读操作有效，对写操作无效，举例说明会更清晰一点： 事务A依次执行如下3条sql，事务B在语句1和2之间，插入10条age=20的记录，事务A就幻读了。

隔离级别脏读不可重复读幻影读
READ-UNCOMMITTED√√√
READ-COMMITTED×√√
REPEATABLE-READ××√
SERIALIZABLE×××
avatar
文章
阅读
粉丝