MySQL实战45讲 笔记（6)

写在最前，本篇文章来源于对MySQL实战45讲的笔记，主要是为了帮助自己理解。如果同时还能对其他人有所裨益，那就更好不过了。如果有谬误的地方，还请不吝指出。

本文并非对文章的直接复制，并且肯定有理解不到位的情况，如果希望系统地学习，还是要去官网支持原作者。

注意：最好拥有一定的MySQL基础再来看本系列文章，可以去b站搜索动力节点的mysql基础教程，或者翻看我做的走进MySQL系列（笔记做的并不是特别详尽，仅作为参考）

全局锁

对整个数据库实例加锁。
MySQL提供了全局读锁：Flush tables with read lock，整个库处于只读状态。
数据更新、数据定义（建表、修改表）和更新类事务的提交都会被阻塞。

使用场景：作全库逻辑备份。把整库每张表都select出来存成文本。

缺陷是：

1. 如果是主库备份，备份期间不能执行更新，业务停摆
2. 如果是从库备份，备份期间不能执行主库同步过来的binlog，导致主从延迟。

不加锁会怎样？
备份系统备份得到的库不是一个逻辑时间点，视图是逻辑不一致的。

在RR隔离级别下可以不使用锁
在可重复读隔离级别下开启事务。
官方自带的逻辑备份工具是mysqldump
使用-single-transaction时，导数据之前就会启动一个事务来确保拿到一致性视图。
由于MVCC支持，数据可以正常更新。

有了一致性视图，为什么还需要加锁？
有的引擎不支持这个隔离级别。比如MyISAM不支持事务，如果备份有更新，总是只能取到最新的数据，造成逻辑上的不一致。

为什么不直接设置全局只读，即set global readonly = true呢？

1. 有些系统中，readonly会被用来做其它逻辑，比如判断一个库是主库还是备库。修改global变量的方式影响面更大。
2. 异常处理机制有差异。如果FTWRL执行后，由于客户端发生异常断开，mysql会自动释放全局锁，整个库回到可以正常更新的状态。如果设为readonly，客户端发生异常，数据库一直保持readonly状态，长时间处于不可写状态，风险较大

表级锁

表级锁有两种：表锁；元数据锁（meta data lock, MDL）

表锁的语法是lock tables ... read/write 可以用unlock tables 主动释放，也可以在客户端断开的时候自动释放。

表锁一般在引擎不支持行锁才会用到。

注意：lock tables语法除了限制别的线程的读写外，也限定了本线程接下来的操作对象。
如果在某个线程A中执行lock tables t1 read, t2 write;则其它线程写t1,读写t2都会被阻塞。
在unlock之前，线程A也只能执行读t1，读写t2的操作，连写t1都不允许，自然也不能访问其他表。

在没有更细粒度的锁时，表锁是最常用的处理并发的方式。当然，对于InnoDB，还是使用行锁效率更高。

另一种表级锁是MDL
MDL不需要显式使用，在访问一个表时会被自动加上，保证读写的正确性。
MDL是server层锁

加读锁则所有线程可正常读元数据，不影响增删改查操作，只是不能修改表结构；加写锁则只有拥有锁的线程可以读写元数据，也就是修改表结构，其它线程不能执行任何操作，包括修改表结构与增删改查。

MDL可以防止遍历数据时，另一线程对表结构做变更。
增删改查操作，加MDL读锁；DDL操作，即表结构改变，MDL写锁

1. 读锁之间不互斥，因此可以有多个线程同时对一张表增删改查
2. 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程同时给一个表加字段，其中一个要等待另一个执行完。

MDL虽然与引擎无关，但却解决了某个可能造成可重复隔离级别下数据不一致问题：
在一个事务的两次查询中，如果有另一个事务改变了表结构，就会造成读取错误。
为什么呢？
InnoDB通过MVCC实现了RR级别，使用了一种快照读的方法。避免了加锁，也没有真正地去复制一份事务创建最初的表，而仅仅是通过取得当前记录的值，并回滚到它能够读取的位置。

所以，它不能解决幻读的问题，也就更不能解决关于表结构的问题了。

通过MDL，在两次查询之间，如果有另一个事务想要改变表结构，则会先去尝试获取MDL锁，发现已经有另一个事务获取了读锁，那么它就会被阻塞直到这个事务释放锁。

---

MDL机制会对我们造成什么影响呢？

session A先启动， 加读锁，session B也是读锁，因此可以正常执行。
session C被blocked，因为读写冲突，MDL读锁还未释放，C需要写锁，只能被阻塞。
不仅如此，后面所有的申请读锁的请求也会被阻塞。
如果某个表上的查询语句频繁，且客户端有重试机制（超时后再起session），线程很快就会爆满
为什么？
申请MDL锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻塞后续该表的所有操作。事务中的MDL锁，在语句执行开始时申请，但会等待整个事务提交后再释放。

注意：同一个事务中，如果发生了DDL操作，则会隐式提交前面的事务，将DDL操作作为单独的一个事务来操作，同时这也意味着这个事务不用commit了。

怎么安全地给小表加字段？
首先解决长事务，因为如果不解决，就会一直占用锁。要么暂停DDL，或者kill这个长事务。

如果变更的表是一个热点表，请求频繁，但又不得不加字段怎么办？
kill可能不太管用，因为请求很快。
可以在alter里设置等待时间，如果超时就放弃，不阻塞后面的语句。

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ... 

原本需要用到表锁的场景都可以直接用事务替代，因为事务自动添加 MDL 锁，MDL 锁能够区别对待表结构的修改和普通增删改查，粒度更细

上期问题

CREATE TABLE `geek` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;

如果频繁需要以上两个请求，建表时创建的索引是否多余？
对于二级索引来说，相同的数据会按照主键的顺序来排序。
主键索引a,b先按照a的顺序排序，再按照b排序，相当于order by a,b
所以，key©和 key(ca)的效果是一样的
所以索引ca是多余的。
而索引cb不是多余的。