mysql相关

总结

行锁，表锁

1.首先比较DB_TRX_ID <  up_limit_id ，如果小于，则当前事务能看到DB_TRX_ID所在的记录，如果是大于等于则进入下一个判断。2.判断DB_TRX_ID >= low_limit_id，如果大于等于则代表 DB_TRX_ID所在的记录是readview生成后才出现的，那么对于当前事务肯定不可见，即读取不到最新修改的数据，如果小于则进入下一步判断。3.判断DB_TRX_ID是否在活跃事务列表trx_list中，如果在，则代表在readview生成时刻，这个事务还是处于活跃状态的，还没有commit，修改的数据，当前事务也是看不到的。如果不在，则说明这个事务在readview生成之前就已经开始commit了，那么修改的结果是能够看见的。

第三部分

状态锁

失效

最左匹配

意向锁

保证的是数据的持久性

select

聚簇索引和非聚簇索引：数据和索引存储在一起的叫做聚簇索引，数据和索引不存储在一起的叫做非聚簇索引。innoDB存储引擎在进行数据插入的时候，数据必须和某一个索引列存储在一起，这个索引列可以是主键，如果没有主键，选择唯一键，如果没有唯一键，选择6字节的rowid来进行存储。 此时数据必定是和某一个索引绑定在一起的，这个绑定数据的索引叫做聚簇索引。其他索引的叶子节点中，存储的数据不再是整行的记录，而是聚簇索引的id值。比如有张user表，id，name，age，gender字段，其中id是主键，name是普通索引。id是聚簇索引，name对应的索引的B+树上的叶子节点存储的就是id值。此时这个name的索引就是非聚簇索引，也叫二级索引innoDB中既有聚簇索引，也有非聚簇索引。MyISAM中只有非聚簇索引，因为MyISAM中索引和数据文件是分开存放的。

分块读取

redolog

select * from user where name = '张三' and age = '12'; 在没有索引下推之前：处理方式是，先根据name从存储引擎中拉取数据到mysql的server层，然后在server层中，对age进行筛选。有了索引下推之后，对name和age两个数据进行筛选，筛选完之后，将数据返回到server层。下推是指，原来在server层进行的数据筛选过滤，下推到了存储引擎层，索引下推是默认开启的。下推是好的，可以减少数据量。比如：本来如果返回name10条数据到server层的，下推到引擎后，匹配age只返回了5条。

因为mysql中也有个日志文件，binlog二进制文件，这是属于mysql本身自带的，而redolog是属于innoDB的，innoDB是一个插件引擎，两个日志属于不同的组件，因此为了保证数据的一致性，两个日志文件一定要能保持一致，因此有了二次提交的概念。

RC

设计原则

MVCC

持久性

up_limit_id

hash表

4

共享锁，排它锁

有张user表，id，name，age，gender字段，其中id是主键，name是普通索引。select * from user where name = '张三';这条语句的检索过程是？1.这个表有两颗B+树，主键的B+树和name的B+树。2.先根据name的值去name的B+树匹配到对应的叶子节点，查询到对应行记录的id值3.根据id去id的B+树中检索整行记录这个过程就称为回表。效率低，因此要尽量避免

当前读

慢

开启事务

因此考虑选择多叉树，每个分支不止2个节点，即B树，又叫多路搜索树，多叉平衡查找树，每个结点都存放了数据以及指向下一节点的指针，天然具有范围性

随机读写

一致性：事务追求的最终目标，一致性的实现即需要数据库层面的保障，也需要应用层面的保障。两个层面：1.数据库    通过原子性， 持久性，隔离性，来保证一致性。 AID 三大特性，是前提。2.应用层面    通过代码判断数据库数据是否有效，然后再决定是回滚还是提交数据。

为什么mysql选择了B+树

乐观锁

作为索引，不太合适，因此选择B+树

意向共享锁

。。。

server(服务端，运行中的mysql)

意向排他锁

回表

优化

表锁

6

ACID

若是select * from user where age > '12' and name = '张三';此时name会生效，age以及age以后的联合索引会失效。比如id是主键，name，age，gender是联合索引select * from user where name= '张三' and age > 12 and gender = 1;

undolog(回滚日志)

数据结构

持久性：1. 利用innodb 的 redo log。mysql把磁盘上的数据加载到内存中，在内存中对数据进行修改，在刷回磁盘上。此时宕机，内存中的数据就会丢失。        2.如何避免宕机？         在事物提交之前将数据写入磁盘中。--------【引发的问题。】》只修改一个页面里的一个字节，就要将整个页面刷入磁盘，太浪费资源了        毕竟一个事务里的SQL可能牵涉到多个数据页的修改，而这些数据页可能不是相邻的，也就是属于随机IO。显然操作随机IO，速度会比较慢。        3.采用redo log?做数据修改的时候，不仅在内存中操作，还会在redo  log 中记录这次操作，提交事物的时候，会将redo log 日志进行刷盘 redo log 一部分在内存中，一部分在磁盘上。数据库宕机重启，就将redo log中的内容恢复到数据库中，再根据undo log 和binlog 内容决定回滚还是提交数据。4.好处？     redo log 体积小，只记录了那一页修改了什么，体积小，刷盘快。一直往末尾进行追加，属于顺序io.效率比随机io来快。 

存储引擎

为什么会有二次提交

读取的数据的最新版本，总是读取最新的数据

A事务能否读取到刚刚最新修改的数据？RC:可以RR：不可以原因是因为，事务版本的可见性

BST（Binary Search Tree）二叉查找树

上面说到磁盘块是分块的，因此一个块能存储的数据是有限的，所以B树一个结点存储的指针数依然不是最大值，由此衍生了B+树，B+树的结点存的都是下一个结点的指针，且因为没有存data，因此一个块可以存储比B树更多的指针，只是在叶子节点上才会有存储具体的数据，一般情况下，3-4层的B+树足以支撑千万级别的数据。

因为随机读写的效率比顺序读写的效率低，为了保证数据的一致性，可以先将数据通过顺序读写的方式写入到日志文件中，再将数据写入到对应的磁盘文件中，如果实际数据没有保存到磁盘中，只要日志文件保存成功了，数据就不会丢失，可以通过日志文件来进行数据恢复。

调整

答案是：能！因为readview是事务2第一次进行快照读的时候才生成的，所以第一次读出来的是没有快照读的，即最新数据，但是继续按照该流程读取，读取的是快照读，则读取不到最新修改的值。有一个读取规则：如右图

行锁：顾名思义，行锁就是一锁锁一行或者多行记录，mysql的行锁是基于索引加载的，所以行锁是要加在索引响应的行上，即命中索引行锁的特征：锁冲突概率低，并发性高，但是会有死锁的情况出现。1.行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。2.两个事务不能锁同一个索引。3.insert，delete，update在事务中都会自动默认加上排它锁。表锁：表锁就是一锁锁一整张表，在表被锁定期间，其他事务不能对该表进行操作，必须等当前表的锁被释放后才能进行操作。表锁响应的是非索引字段，即全表扫描，全表扫描时锁定整张表，sql语句可以通过执行计划看出扫描了多少条记录表锁的特征：由于表锁每次都是锁一整张表，所以表锁的锁冲突几率特别高，表锁不会出现死锁的情况

多版本并发控制

1

平衡

readview

乐观锁，悲观锁

最左前缀

mysql中的三种日志

事务2

索引和实际数据都是存储在磁盘的，只是在进行数据读取的时候会优先把索引加载到内存中

临键锁

隐藏字段

WAL(预写日志)write ahead log

红黑树

索引失效的情况：（在索引列上使用 IS NULL 或 IS NOT NULL操作，索引不一定失效！https://www.jianshu.com/p/3cae3e364946、https://mp.weixin.qq.com/s/CEJFsDBizdl0SvugGX7UmQ）1、like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。2、or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效3、组合索引，不是使用第一列索引，索引失效。（即没有满足最左前缀原则）4、数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。5、在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。6、对索引字段进行计算操作、字段上使用函数。7、当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。

select ...

索引覆盖

对业务逻辑进行synchronized加锁以后，效率会变慢，于是有了CAS的概念，数据库也有相应的概念，即MVCC

范围锁

upatecommit

1，2，3

表达方式

锁模式

读取的历史版本的记录

快

二叉树

up_limit_id(列表中最小的事务id)

一致性

乐观锁、悲观锁

DB_TRX_ID

聚簇索引

属性锁

索引格式

用途

有序

原子性：利用innodb的undo log。 叫做回滚的日志，在操作数据库之前，首先将数据备份到一个地方（存储数据备份的地方称为Undo log）然后进行数据的修改，如果出现了错误或用户执行了回滚语句，系统可以利用Undo log中的备份将数据恢复到事务开始之前的状态。1、当你delete一条数据的时候，就需要记录这条数据的信息，Undo log会记录一条insert2、当你update一条数据的时候，就需要记录之前的旧值，Undo log会记录一条相反update操作3、当年insert一条数据的时候，就需要这条记录的主键，Undo log会记录一条delete操作

树

隐藏主键

快照读

DB_ROW_ID

间隙锁

hash

分为意向共享锁，意向排它锁。S锁：读锁，共享锁  加了S锁的记录，允许其他事务再加S锁，不允许其他事务再加X锁  加锁语法：select…lock in share modeX锁：写锁，排他锁 加了X锁的记录，不允许其他事务再加S锁或者X锁   加锁语法：select…for update意向共享锁（IS锁）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁意向排他锁（IX锁）：表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。意向锁是InnoDB所用的表级锁，其设计目的主要是为了在一个事务中揭示下一步将要被请求的锁的类型。为什么是表级锁：因为当我们需要加一个排他锁时，需要根据意向锁去判断表中有没有数据行被锁定（行锁）；（1）如果意向锁是行锁，则需要遍历每一行数据去确认；（2）如果意向锁是表锁，则只需要判断一次即可知道有没数据行被锁定，提升性能。意向锁是InnoDB自动加的，不需要用户干预。

low_limit_id(系统尚未分配的下一个事务id)

索引优化

3

undolog

索引下推

事务，锁，MVCC

事务4

查看锁情况：show engine innodb status\\G;（展示信息不是特别完整，如果想看更详细的信息，可以设置：set global innodb_status_output_locks = 1）

explain

需要比较好的hash算法，如果算法不好的话，会导致hash碰撞，又叫hash冲突，数据散列不均匀，又是无序的，当需要进行范围查询的时候，则需要挨个遍历，效率低下。mysql中有hash索引，memory引擎支持的就是hash索引。innoDB支持自适应hash。其四大特性：1：插入缓冲。2：二次写。3：自适应哈希。4：预读https://www.cnblogs.com/zhs0/p/10528520.html

事务2能否读取到事务4刚刚修改的值？

算法锁

当不同的事务对同一行记录进行修改的时候，会使该记录的undolog形成一个链表，链表的头部是最新的记录，链表的尾部指向上一次的记录，即DB_ROLL_PTR（回滚指针）的位置

2

分而治之

使用 select…for update 锁数据，需要注意锁的级别，MySQL InnoDB 默认行级锁。行级锁都是基于索引的，如果一条 SQL 语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住，这点需要注意。https://www.jianshu.com/p/d2ac26ca6525

可见性算法

创建索引的时候，可以选择多个列来组合，此时叫做组合索引或者联合索引，要遵循最左匹配原则（最左前缀原则）有张user表，id，name，age，gender字段，其中id是主键，name，age是联合索引。select * from user where name = '张三' and age = '12'; //会走联合索引select * from user where name = '张三'; //会走联合索引select * from user where age = '12'; //不会走联合索引select * from user where age = '12' and name = '张三'; //会走联合索引（因为这个查询的结果并不会变，mysql会有个优化机制，将这个sql改成name=? and age =?以符合索引的创建规则）

K-V

原因：readview生成的时机不同RC：每次在读的时候，都会生成心的readviewRR：只有在第一次进行快照读的时候，才会生成readview，之后的读操作都会使用第一次生成的readview

RR

提高查询效率

事务在进行快照读的时候，产生的读视图

第二部分

成长

表示最后一次创建或修改该记录的事务id

减少IO量

原子性（Atomicity）原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。一致性（Consistency）事务前后数据的完整性必须保持一致。隔离性（Isolation）事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。持久性（Durability）持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

trx_list

0

7

AVL二叉平衡树

二次提交

binlog

意向共享锁，意向排他锁

事务A

行锁

排它锁

DB_ROLL_PTR

临键锁：是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。 注：临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。mysql的行锁默认就是使用的临键锁，临键锁是由记录锁和间隙锁共同实现的，间隙锁的触发条件是命中索引，范围查询没有匹配到相关记录。而临键锁恰好相反，临键锁的触发条件也是查询条件命中索引，不过，临键锁有匹配到数据库记录；

事务B

事务3

执行计划，一条sql语句的执行过程

先写日志，再写数据

锁机制的基本原理可以概括为：事务在修改数据之前，需要先获得相应的锁；获得锁之后，事务便可以修改数据；该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。行锁与表锁按照粒度，分为表锁与行锁：MyISAM 支持表锁，InnoDB 支持表锁和行级锁，默认是行级锁。表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发送锁冲突的概率比较高，并发处理效果较低。行级锁： 开销大，加锁慢，会出现死锁，锁定粒度较小，发生锁冲突的概率会小一点，并发处理的效果高。MVCCRR（可重复读）解决了 脏读，不可重复读，幻读等问题使用到的就是MVCC（Multi-Version Concurrency Control） 既多版本的并发控制：在同一个时刻，不同的事物读取到的数据可能是不同的（多版本）。对于MVCC来说最大的优点就是读不加锁，因此读写不冲突，并发性能好InnoDB实现MVCC，多个版本的数据可以共存，主要是依靠数据的隐藏列(也可以称之为标记位)和undo log。其中数据的隐藏列包括了该行数据的版本号、删除时间、指向undo log的指针等等；当读取数据时，MySQL可以通过隐藏列判断是否需要回滚并找到回滚需要的undo log，从而实现MVCC。如何解决幻读nnoDB实现的RR（可重复读）通过next-key lock机制避免了幻读现象。next-key lock是行锁的一种，实现相当于record lock(记录锁) + gap lock(间隙锁)；其特点是不仅会锁住记录本身(record lock的功能)，还会锁定一个范围(gap lock的功能)。当然，这里我们讨论的是不加锁读：此时的next-key lock并不是真的加锁，只是为读取的数据增加了标记（标记内容包括数据的版本号等）； 

索引失效

原子性

每一行记录上都会包含几个用户不可见的字段

IO问题

(一个事务)写操作对(另一个事务)写操作的影响：锁机制保证隔离性(一个事务)写操作对(另一个事务)读操作的影响：MVCC保证隔离性

复盘

5（因为事务4已提交了）

表锁，行锁

看不到

都是二叉树，每个分支最多只有两个节点

四大特性实现原理：

不同隔离级别的结果：

当下标相同时，延伸出链表

事务隔离级别：MySQL 事务都是指在 InnoDB 引擎下，MyISAM 引擎是不支持事务的脏读：脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读。不可重复读：指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据更新（UPDATE）操作。幻读：幻读是针对数据插入（INSERT）操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，但其实是事务B刚插入进来的，感觉出现了幻觉，这就叫幻读。SQL 标准定义了四种隔离级别，MySQL都支持。这四种隔离级别分别是：读未提交（READ UNCOMMITTED） 可能脏读          可能不可重复读           可能幻读读已提交 （READ COMMITTED）     不可能脏读       可能不可重复读           可能幻读可重复读 （REPEATABLE READ）     不可能脏读       不可能不可重复读        可能幻读（通过(间隙?)锁的方式能解决部分幻读，但不能解决所有的幻读）串行化 （SERIALIZABLE）                 不可能 脏读      不可能不可重复读        不可能幻读从上往下，隔离强度逐渐增强，性能逐渐变差。采用哪种隔离级别要根据系统需求权衡决定，其中，可重复读是 MySQL 的默认级别。读未提交和串行化基本上是不需要考虑的隔离级别，前者不加锁限制，后者相当于单线程执行，效率太差。读提交解决了脏读问题，行锁解决了并发更新的问题。并且 MySQL 在可重复读级别解决了幻读问题，是通过行锁和间隙锁的组合 Next-Key 锁实现的。 查看当前数据库的隔离级别：select @@transaction_isolation; SELECT @@global.tx_isolation;SELECT @@session.tx_isolation;SELECT @@tx_isolation;修改隔离级别的语句：set [作用域] transaction isolation level [事务隔离级别];作用域可以是 SESSION 或者 GLOBAL，GLOBAL 是全局的，而 SESSION 只针对当前回话窗口。隔离级别是 {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE} 这四种，不区分大小写。例如：set global transaction isolation level read committed;查看当前有多少事务在运行：select * from information_schema.innodb_trx;

案例

劣势：当需要向树中插入更多数据的时候，会使当前树高度变高（因为最多就2个节点，被限制了，只能增加树的高度），存储数据会增加IO次数，影响效率

在MySQL中还存在binlog(二进制日志)也可以记录写操作并用于数据的恢复.和redolog的不同：（1）作用不同：redo log是用于crash recovery的，保证MySQL宕机也不会影响持久性；binlog是用于point-in-time recovery的，保证服务器可以基于时间点恢复数据，此外binlog还用于主从复制。（2）层次不同：redo log是InnoDB存储引擎实现的，而binlog是MySQL的服务器层(可以参考文章前面对MySQL逻辑架构的介绍)实现的，同时支持InnoDB和其他存储引擎。（3）内容不同：redo log是物理日志，内容基于磁盘的Page；binlog的内容是二进制的，根据binlog_format参数的不同，可能基于sql语句、基于数据本身或者二者的混合。（4）写入时机不同：binlog在事务提交时写入；redo log的写入时机相对多元：前面曾提到：当事务提交时会调用fsync对redo log进行刷盘；这是默认情况下的策略，修innodb_flush_log_at_trx_commit参数可以改变该策略，但事务的持久性将无法保证。除了事务提交时，还有其他刷盘时机：如master thread每秒刷盘一次redo log等，这样的好处是不一定要等到commit时刷盘，commit速度大大加快。

记录锁

记录锁：锁的是表中的某一条记录，不像行锁是根据索引来锁多行记录，记录锁的出现条件必须是精准命中索引并且索引是唯一索引，如主键id

事务1

修改字段值commit;

隔离性

trx_list(当前系统活跃的事务id列表)

能看到

减少IO次数

索引相关

若是只有8G存储空间，要读取16G的数据怎么办？

第一部分

回滚指针

无论是悲观锁还是乐观锁，都是人们定义出来的概念，可以认为是一种思想，是一种概念。不应用来和其他锁比较。乐观锁比较适用于读多写少的情况(多读场景)，悲观锁比较适用于写多读少的情况(多写场景)。乐观锁：乐观锁是乐观的认为一般情况下，数据不会有冲突的情况发生，因此采取了更加宽松的加锁机制，不会刻意使用数据库本身的锁机制，而是依据数据本身来保证数据的正确性。乐观锁的实现：1.CAS(Compare and Swap) 实现2.版本号控制：一般是在数据表中加上一个数据版本号 version 字段，表示数据被修改的次数，或者时间戳，时间戳天然具有顺序性悲观锁：悲观锁总是悲观的认为每次对数据的操作都会引起冲突，因此每次操作前都会进行加锁操作。悲观锁主要分为共享锁和排他锁：共享锁又称为读锁，简称 S 锁。顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。排它锁又称为写锁，简称 X 锁。顾名思义，排他锁就是不能与其他锁并存，如果一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁。获取排他锁的事务可以对数据行读取和修改。悲观锁的实现，往往依靠数据库提供的锁机制(也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据)1.传统的关系型数据库使用这种锁机制，比如行锁、表锁、读锁、写锁等，都是在操作之前先上锁。2.Java 里面的同步 synchronized 关键字的实现。

乐观锁悲观锁间隙锁行锁表锁临键锁记录锁自增锁意向锁意向共享锁意向排他锁

共享锁

5

间隙锁，临键锁，记录锁

client(客户端，如Navicat)

low_limit_id

锁

配合undolog完成

悲观锁

RR一定每次都是读的第一次的快照吗？

保证的是数据的隔离性以及原子性