谈谈mysql

MySql重难点剖析

事务(ACID)

事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为事务的ACID属性。

原子性(Atomicity)

• 事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
  【面向过程】

一致性(Consistent)

• 在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规
  则都必须应用于事务的修改,以保持数据的完整性。（比如A向B转账，不可能A扣了钱，B却没收到。）【面向数据】

隔离性(Isolation)

• 数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独
  立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。

持久性(Durable) ：

• 事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。
  （redo日志实现）

并发带来的问题(脏写、脏读、不可重复读、幻读)

首先来说一下事务中会发生的问题：脏写、脏读、不可重复读、幻读

假设id=5的记录最原始的username=“小顾”

脏写

sessionA	sessionB
begin //开启事务
	begin //开启事务
update user set username=“张三” where id=5;
commit//提交事务
	update user set username=“李四” where id=5;
	ROllBACK;
1.sessionA开启了事务之后，紧接着sessionB也开启了事务，那么sessionB如果事务回滚就会回滚到最原始的记录，也就是sessionB开启事务之前的记录,也就是username为"小顾"。

2.sessionA明明对id为5的记录进行了修改，而且sessionB也没有进行事务提交，只是进行了事务的回滚，这种情况就称为脏写。更改了别人修改的记录。最后的更新覆盖了由其他事务所做的更新。

脏读

sessionA	sessionB
begin //开启事务
	begin //开启事务
update user set username=“张三” where id=5;
	select * from user where id =5;
COMMIT;//提交事务
	COMMIT;//提交事务
sessionB会读取到username为张三的记录，这种情况就是脏读。事务B读取到了事务A已经修改但尚未提交的数据

不可重复读

sessionA	sessionB
begin //开启事务
select * from user where id =5; //读取到的是小顾
	update user set username=“张三” where id=5;//这里有个隐式事务。自动提交
select * from user where id =5; //读取到的是张三
COMMIT;//提交事务
上面这种情况就是不可重复情况，在一个开启的事务中，同一个select语句能读取到的其他事务修改的值。事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔离性

幻读

sessionA	sessionB
begin //开启事务
select * from user where id >=5;
	insert into user(id，username) values(6,‘老李’);//隐式事务，自动提交
select * from user where id >=5; 此时读取到的就是除了之前有的 还多了一条老李的记录
COMMIT;//提交事务

• 幻读就是相比第一条查询，第二条查询多了其他数据。事务A读取到了事务B提交的新增数据，不符合隔离性

锁

间隙锁

• 在某些情况下可以解决幻读问题。
  假设account表里数据如下：
  
  那么间隙就有 id 为 (3,10)，(10,20)，(20,正无穷) 这三个区间，在Session_1下面执行 update account set name = ‘gugugu’ where id > 8 and id <18;则其他Session没 法在这个范围所包含的所有行记录(包括间隙行记录)以及行记录所在的间隙里插入或修改任何数据，即id在 (3,20]区间都无法修改数据，注意最后那个20也是包含在内的。
  间隙锁是在可重复读隔离级别下才会生效。

临键锁(Next-key Locks)

Next-Key Locks是行锁与间隙锁的组合。像上面那个例子里的这个(3,20]的整个区间可以叫做临键锁。

无索引行锁会升级为表锁

表结构

mysql> desc country;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| countryname | varchar(255) | YES  |     | NULL    |                |
| countrycode | varchar(255) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
3 rows in set

索引信息

mysql> show index from country;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| country |          0 | PRIMARY  |            1 | id          | A         |           5 | NULL     | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set

这个表，只有主键索引，其他字段上未建立二级索引 。

现在使用没有建立索引的字段进行操作，观察其结果

操作演示

sessionA	sessionB
begin 模拟开启事务
	begin 模拟开启事务
	update country set countryname = ‘DDD’ where countrycode = ‘anotherline’ ; ---- 一直被阻塞 ，直到超时 1205 - Lock wait timeout exceeded; try restarting transaction
我们知道锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁

结论

• InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁 。
• 所以建表的时候 ，结合你的业务，如果有更新的操作，切记要对操作的字段建立索引，不然并发下这个问题就非常明显了

事务隔离级别

MVCC多版本并发控制机制

MVCC

1. Mysql在可重复读隔离级别下如何保证事务较高的隔离性
2. 对一行数据的读和写两个操作默认 是不会通过加锁互斥来保证隔离性，避免了频繁加锁互斥，而在串行化隔离级别为了保证较高的隔离性是通过将所有操
   作加锁互斥来实现的。
3. Mysql在读已提交和可重复读隔离级别下都实现了MVCC机制。

undo日志版本链

undo日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚 日志，并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链(见下图)

read view机制详解

readview:[100,200]，300

• 在可重复读隔离级别，当事务开启，执行任何查询sql时会生成当前事务的一致性视图read-view，该视图在事务结束 之前都不会变化
• 这个视图由执行查询时所有未提交事务id数组（数组里最小的id为min_id，此处指的是100）和已创建的最大事务id（max_id，此处指的是300）组成
• 事务里的任何sql查询结果需要从对应 版本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果。
• 执行第一个更新 新增 删除操作 才会生成当前的第一个事务正在的ID，查询不会生成一个新的sessionId

版本链比对规则：

• 1、如果 row 的 trx_id 落在绿色部分( trx_id<min_id )，表示这个版本是已提交的事务生成的，这个数据是可见的；（如何理解：就是线程号<100的部分线程）
• 2、如果 row 的 trx_id 落在红色部分( trx_id>max_id )，表示这个版本是由将来启动的事务生成的，是不可见的(若 row 的 trx_id 就是当前自己的事务是可见的）；（如何理解：就是线程号>300的部分线程）
• 3、如果 row 的 trx_id 落在黄色部分(min_id <=trx_id<= max_id)，那就包括两种情况
  a. 若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的，不可见(若 row 的 trx_id 就是当前自己的事务是可见的)；（如何理解：就是线程号100,200两个未提交的线程）
  b. 若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见。

Innodb引擎SQL执行的BufferPool缓存机制

1. 加载缓存数据：加载ID为1的记录所在【整页】的数据
2. 写入更新数据的旧值，便于回滚（undo日志文件，如果事务提交失败要回滚数据，可以用undo日志里面的数据恢复buffer pool里的缓存数据）
3. 更新内存数据（缓存）
4. 写redo日志（缓存）
5. 准备提交事务，redo日志写入磁盘（如果事务提交成功，buffer pool里的数据还没来得及写入磁盘，此时系统宕机了，可以用redo日志里面的数据恢复buffer pool里的缓存数据）
6. 准备提交事务，binlog日志写入磁盘（主要用来恢复数据库磁盘里面的数据）
7. 写入commit标记到redo日志里面

其他Mysql调优知识脑图，后续待增加