mysql总结

索引！！！！！！！！！！！！！！！！

索引树中无论是叶子节点还是非叶子节点都是一个个磁盘块，所以树越高你就要磁盘IO越多次。以及索引树中节点内部会存放子节点的地址，这个地址是文件逻辑块号。

索引类型

从物理实现划分

聚簇索引

又名聚集索引、一级索引，叶子节点存储着key和数据行，非叶子节点仅存储key。每个表都只有唯一的一个聚簇索引并由innodb自动创建，默认使用表结构中的主键建立(此时主键索引就是聚簇索引)；若无声明主键则使用unique索引作为聚簇索引；如果unique索引也没有，innodb引擎会在表中隐藏地构造一个6字节的DB_ROW_ID自增字段，用该列来构造名为GEN_CLUST_INDEX聚簇索引。总的来说，给定key走聚簇索引可以直接得出对应数据(可以给非聚簇索引回表)。数据行也仅存在于聚簇索引中

非聚簇索引

又名辅助索引、二级索引，叶子节点存储着key(被选定作为索引的字段的值)和对应数据行在聚簇索引的key(常规情况下即表有主键，那么聚簇索引的key就是主键)，非叶子节点仅存储key。手动创建的索引都是非聚簇索引

索引失效

联合索引失效

联合索引在b+树中，是先按照最左边的字段来排序，只有左边字段都完全相同时，右边的字段才是有序的，比如下图，Lilei字典序小于Lucy而18却大于17，Zhangwei的时候才有18，25

范围查询

假设利用软件工程定位到一批记录的索引，这批记录都是软件工程的，那就可以利用这批索引使用二分定位到age>30的那部分，然后在这一部分进行完全遍历来找到status=’0‘的

如上图，加了等号就使用到联合索引的全部索引字段，不加等号就只用到前两个，其实总用时还是差不多的，只是刚好数据库里面存在age=30的数据，所以对于这部分age=30的数据是可以利用到整个联合索引，但对于age>30的那部分数据根据最左索引原则还是只能用到联合索引中的前两个字段，第三个已经失效，

自增主键和随机主键插入区别

B+tree 页面分裂

要在下图 B+tree （fan-out=4）上继续插入记录 9，InnoDB 首先以 <= 9 的条件会定位到 leaf 层的记录 8 上，但是发现该 page 已经没有更多空间，此时就需要申请一个 new page。

这里产生一个问题：如何选择分裂点（split point），把哪些 record 移动到 new page？

InnoDB 采用两种分裂策略：

中间点（mid point）分裂

将原始页面中 50% 数据移动到新页面，这是最普通的分裂方法。以上图为例，分裂后 5、6 保留在原页面，7、8 移动到新页面，并将 9 插入到 8 之后，调整树结构后如下图：

这种分裂方法使两个 page 的空闲率相同，如果之后的插入在这两个 page 上是随机的，那可以很好地利用空闲空间。但是，如果后续插入不是随机的，比如递增插入 10、11、12 等等，填充和分裂的永远是右侧 page，左侧 page 的利用率只有 50%，如下图：

插入点（insert point）分裂

为了优化上述中间点分裂在顺序插入场景的问题，InnoDB 实现了在插入点分裂的方法，在每个 page 上记录上次插入位置 （PAGE_LAST_INSERT），以此判断本次插入是否递增 or 递减，如果判定为顺序插入，就在当前插入点进行分裂。还是以插入记录 9 为例，假设上次插入的是记录 8，本次插入时会判定为递增，在当前位置分裂后如下图：
此后，继续插入记录 10、11、12 都无需分裂，直到插入 13 时才会再次按插入点分裂一次：
（注意，按插入点分裂并不一定发生在 page 的最后一个 rec，如果 PAGE_LAST_INSERT 在 page 中间，并且判定当前插入为顺序插入，也会在插入点进行分裂。）

随机主键的弱势

插入时自增主键必然是插入到最右下的page，只有满了才会页分裂而且经过上述优化即使页分裂也不会数据迁移只会把新数据单独放在新页，而乱序插入导致原本就满了的page发生分裂而且还是常规的即数据要迁移，而别人自增主键不会让本来就满的重新满一次，而且大量随机页分裂会导致更多的页面碎片, 浪费存储空间的同时影响查询效率因为原本能装满的被强行五五开分裂后面又不一定能插回满，导致使用了更多的页.

唯一索引和唯一约束

建立唯一约束就会建立一颗索引树，这很好理解，不然他怎么快速地知道该字段当前是否重复呢！

索引下推

本来查询时联合索引失效的部分不管，统一回表给server层过滤，而实际上联合索引失效部分还是可以利用的，现在有了覆盖索引，就尽量在存储引擎层的索引中过滤，减少回表的数量，比如

CREATE INDEX idx_name_age ON student(name,age);
#索引成功；MySQL5.6引入索引下推，where后面的name和age都在联合索引里，可以又过滤又索引，不用回表，索引生效
SELECT * FROM student WHERE `name` like 'bc%' AND age=30;
#索引成功；name走索引，age用到索引下推过滤，classid不在联合索引里，需要回表。
SELECT * FROM student WHERE `name` like 'bc%' AND age=30 AND classid=2;

视图

虚拟的表，本质上是一个select语句，将它查询到的结果称为视图，但视图本身没有存任何数据所以说是虚拟的表，每次查看视图的数据都要执行一遍其代表的select语句

应用场景，比如有些表同样的字段经常需要被查询，那就没必要每次都写一遍同样的sql语句，直接用视图减少重复

隔离级别

脏读幻读不可重复读的定义和区别

脏读强调当前事务读取了另一个事务未提交的数据，
后面两种问题都是基于 事务只能读取到另一个事务提后的数据 为前提：
幻读强调增删insert delete，即记录数量会发生改变，不可重复读强调改update，即记录内容改变了

幻读：一个事务前后两次查询同一个范围的数据时，后一次查询出现新的一行数据

根据MySQL官网的描述，幻读是“相同的查询在不同时间返回了不同的结果”

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times.

同时官网还举例说明了，如：两次查询中，后一次多出来的行就是所谓的“幻影行”

For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.

事务隔离！！！！！！！！！！！

打勾代表会出现该问题

read uncommitted读未提交

就和字面意义一样，事务A会读到别的事务更新（插入呢）但未提交commit的新数据即脏读

事务未commit的数据仍能被其他事务读到

read committed读已提交

只有事务commit后的数据才会被其他事务读到

repeatable read可重复读

事务A开启期间，即使其他事务commit了新数据，事务A也不会读到，即对于每一个事务内部，数据只可由自身造成改变，如果自身不作修改，那么事物内部同一个sql语句反复查的结果都是一样的。事务A内的数据就是它事务开启的那瞬间及之前commit的数据

但仍然会有幻读问题，比如第一次查询得到了结果，这时候其他事务插入了数据并commit了，此时如果select语句带update或者share mode当前读就会走锁那一套，等待最新的数据

serializable可串行化

串行执行事务，比如打开两个sql客户端，分别打开两个事务，那么后打开的事务的增改删操作会被阻塞即光标卡住，直到先打开的事务commit

change buffer

buffer pool里面有一片区域叫change buffer，存储那些有辅助索引的表的写操作，因为正常一个表会有主键索引顺便存数据本身，如果你再对其他字段搞个辅助索引，那更新一条数据(如果涉及到该数据的辅助索引字段的修改)的时候还需要更新对应的辅助索引，那又要一次磁盘io，所以change buffer记录下这种情况下的修改操作，等到以后某次读取辅助索引到buffer pool的时候，直接把change buffer中的修改应用到buffer pool中的辅助索引

bin log（有buffer顺序写一直用新文件）

sync_binlog的值可以是0、1或N（N是一个正整数）：

1. sync_binlog=0：事务提交时写页缓存
2. sync_binlog=1：事务提交时落盘，这是最安全但性能开销最大的设置。
3. sync_binlog=N：每次提交事务的时候，执行 write，累积 N 个事务后再批量执行 fsync。这是一种折中的方案，可以在安全性和性能之间找到平衡

redo log（有buffer循环写两个文件来回倒腾）

用于实现acid中的d即持久性，它让MySQL拥有了崩溃恢复能力。
比如 MySQL 实例挂了或宕机了，重启时，InnoDB存储引擎会使用redo log恢复数据，保证数据的持久性与完整性。redo log是InnoDB引擎层的日志。记录的是物理级别上的页修改操作，比如XX页号XX偏移量写入的XX数据(具体来说是：每条 redo 记录由“表空间号+数据页号+偏移量+修改数据长度+具体修改的数据”组成)。

每当事务执行增删改语句就会发生上述的记录行为，即将页的修改记录写入到redo log文件中

为什么不直接把每次修改后的数据页刷盘

这样根本不需要redo log了，但是

实际上，数据页大小是16KB，刷盘比较耗时，可能就修改了数据页里的几 Byte 数据，没必要那么快就把完整的数据页刷盘

而且数据页刷盘是随机写，因为一个数据页对应的位置可能在硬盘文件的随机位置，所以性能是很差。

如果是写 redo log，一行记录可能就占几十 Byte，只包含表空间号、数据页号、磁盘文件偏移 量、更新值，再加上是顺序写(每条redo log以追加形式写入)，所以刷盘速度很快。

innodb_flush_log_at_trx_commit参数

用来控制redo log写入page cache以及随后把page cache落入磁盘这两个动作的时机

我总结为：0定期落盘，1提交就落盘，2提交就写到页缓存（落盘时机由os定）

innodb后台线程导致每秒会直接落盘

innodb_flush_log_at_trx_commit=0

事务commit时无事发生，依靠innodb后台线程定期将redo log buffer的数据刷盘：即每秒钟将redo log buffer中的数据写入os的文件系统缓存即page cache然后立马再将page cache内容写到磁盘即fsync；或者当redo log buffer占用空间达到innodb_log_buffer_size一半时，后台线程会主动刷盘

由于innodb后台线程，所以即使redolog没提交事务也是很大可能被落盘的

innodb_flush_log_at_trx_commit=1(默认)

事务commit会导致redo log buffer写入到page cache紧接着fsync到磁盘发生，确保了事务的ACID

innodb_flush_log_at_trx_commit=2

事务commit会导致redo log buffer写入到page cache，但依靠innodb后台线程定期fsync写磁盘操作时机

崩溃后

• 参数为 0 的策略：MySQL进程的崩溃(特别是其后台刷盘线程)会导致崩溃发生那一刻的上一秒钟记录事务操作的redo log（即使commit了）丢失导致无法恢复，因为崩溃发生那一刻后台刷盘线程本应该将上一秒内redo log事务操作记录刷到page cache然后刷到磁盘
• 参数为 1 的策略 ：无论是mysql还是os崩溃，只要崩溃前1秒内事务commit了那就能保证可恢复因为redo log已经在磁盘中了，当然如果innodb_flush_log_at_trx_commit=1但没commit那是没办法了的
• 参数为 2 的策略 ：MySQL 进程的崩溃不会导致崩溃前1秒内commit的事务丢失数据即可以恢复，因为这些事务的redo log都仍然存在于page cache中，os的后台线程也会特定时机(脏页多或脏页久)刷盘，只有在os崩溃或者断电的情况下数据才可能丢失这一秒的数据即连page cache也没了

需要注意，如果redo log的page cache写到磁盘时磁盘坏了那也是没办法的了(极低概率)

各参数的数据安全性和效率比较

数据安全性：参数 1 > 参数 2 > 参数 0
即对应于 硬件问题 - 系统崩溃 - mysql进程崩溃 才会导致不可恢复

效率：参数 0 > 参数 2> 参数 1

0：只依赖innodb后台线程特定时机写入page cache及刷盘
2：commit会写入page cache但依赖innodb后台线程特定时机刷盘
1：commit会写入page cache且刷盘 再加上innodb后台线程特定时机写入page cache及刷盘

redolog和binlog两阶段提交

数据库提交事务的时候，innodb引擎是下边几个步骤：

1. 先写入redo log，状态定为prepare状态
2. 写入binlog
3. 写入binlog完成以后，再修改redo log状态为commit状态
4. 最后再是事务的提交的commit命令

而宕机的时候，需要区分宕机是在哪一步宕机的：

1. 如果第1步结束就宕机，那就是回滚，因为binlog没有数据，防止主从不一致；
2. 如果第2步结束就宕机，那会去验证binlog是不是写完了。如果写完，就继续往下走，写入commit状态到redo log；如果没写完，那就回滚，防止主从不一致。
3. 如果第3步结束以后宕机，那就直接往下走，提交事务。

为什么需要两阶段提交来保证两个日志落盘的原子性？

因为不这样会有mysql主从数据不一致问题。

来看两种情况

1. redolog落盘后，mysql宕机，binlog还没落盘。mysql恢复后根据redolog恢复了，此时mysql根据binlog主从同步时，从少了一部分数据

2. binlog落盘后，mysql宕机，redolog还没落盘。mysql恢复后，此时mysql根据binlog主从同步时，从能够根据binlog拿到完整的数据，而主少了一部分数据

流程

当客户端执行 commit 语句或者在自动提交的情况下，MySQL 内部开启一个 XA 事务，分两阶段来完成 XA 事务的提交，如下图：
从图中可看出，事务的提交过程有两个阶段，就是将 redo log 的写入拆成了两个步骤：prepare 和 commit，中间再穿插写入binlog，具体如下：

1. prepare 阶段：将 XID（内部 XA 事务的 ID） 写入到 redo log，同时将 redo log 对应的事务状态设置为 prepare，然后将 redo log buffer落盘到redo log（innodb_flush_log_at_trx_commit = 1 的作用）；
2. commit 阶段：把 XID 写入到 binlog，然后将 binlog buffer落盘到binlog（sync_binlog = 1 的作用），接着调用引擎的提交事务接口，将 redo log 状态设置为 commit即把commit写入redolog，此时该状态并不强求一定直接落盘，只需要 写page cache 就够了，因为只要 binlog 写磁盘成功，就算 redo log 的状态还是 prepare 也没有关系，一样会被认为事务已经执行成功（因为恢复事务时redolog能根据xid在binlog找到对应xid，此时只需要继续完成最后两步）

什么时候undo什么时候redo

提交了但崩溃了redo，主动rollback或未提交崩溃了undo。

但实际上这里判断是否提交也很细节，不是执行了commit就是，执行commit之后会开一个事务实现原子性的redolog和binlog一起都落盘，

undo log！！！！！！！！！！！！！！

用于实现acid的a即原子性。即通过undo log实现回滚操作来实现原子性。undo log同样是InnoDB引擎层的日志。

和redo log不同的是，undo log记录的是逻辑日志，比如事务中delete一条记录，undo log中会记录一条对应的insert语句；update一条记录，undo log中会记录一条对应的update旧值的语句。这样rollback的时候只需要按着undo log文件中的sql语句执行就可以恢复到事务开始前的状态即回放

除此以外，undo log还可被用于MVCC

undo log在事务执行时产生，事务commit后并不会立即被删除，因为这些日志可能还要被MVCC使用

MVCC多版本并发控制！！！！！！！！

MVCC这种机制（和锁机制概念同一级，即锁之外的另一种并发机制）为查询提供了一个基于时间点的快照。

MVCC本质就是查询时只能看到自己或者是查询那一刻之前的事务修改完成的版本，而在查询开始之后提交的数据是不可以看到的

给读写事务分配 id 充当时间戳

MySQL 内部会维护一个计数器，用于给读写事务分配事务 id。该计数器是递增的，每分配一次 id 就增 1，所以能保证给每个事务分配的 id 都是递增的。而且在大多数情况下，计数器分配出的 id 都是连续递增的。

因为事务 id 是随着时间递增的，所以事务 id 的大小可以反应事务的先后顺序。

事务 id 的分配时机

读写事务在创建时并不会立马分配 id，而是等到事务执行第一条更改语句（INSERT、UPDATE、DELETE）或锁定读语句时才分配 id。

为什么不给只读事务分配事务 id？

因为只读事务不会修改数据，完全不会影响其他事务，其他事务不需要意识到只读事务的存在，不需要给个 id 来标识给其他事务看。至于没有 id，怎么标识和其他事务的时间先后关系，参考本文后面和 ReadView 相关的内容。

事务 id 一直递增，会耗尽吗？

当前读和快照读

总的来说，当前读就是读数据的最新版本，快照读就是读数据的历史版本(版本就是数据行的每一次修改时的一条undo log记录)
只有普通select属于快照读，其他包括update、select in share mode，select for update全是当前读

版本链

对于使用 InnoDB 存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列：

• trx_id ： 每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的 事务id 赋值给 trx_id
  隐藏列。
• roll_pointer ：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到 undo日志 中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息

每条记录和涉及到自身的全部undo log通过链表形式形成了一条关于该记录的版本链

下面是例子，是事务执行时序图(自上而下顺序执行)，假设事务1插入了一条id=30的记录，

事务2修改了这条记录，于是产生了一条undo log以记录数据原本的版本

事务3继续修改，undo log再次产生，链表发生变化

显然在undo log中越上面版本越新

ReadView

是快照读时选择读取记录哪个版本的依据
max_trx_id表示生成ReadView那一刻系统中应该分配给下一个事务的id值（这个值是由InnoDB在系统表空间维护的一个全局变量）

下面是例子

Read Committed的ReadView

RC隔离级别下，在事务中每一次执行快照读时都会生成ReadView

Repeatable Read的ReadView

RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

ReadView如何指导所读取版本的选择？

每当执行快照读的sql时，都会拿着生成的ReadView去访问要读的记录及其undo log(即版本链)，从版本链尾部开始遍历看是否符合可读的规则，不符合就向着旧版本进发，符合则直接返回该版本的记录

下面就是可读的规则，其中trx_id指的是记录或其undo log中的DB_TRX_ID字段即导致得到该版本的事务
就是拿着ReadView去跟某记录的每个版本的DB_TRX_ID字段比较

1. 如果被访问版本的DB_TRX_ID == creator_trx_id意味着要执行快照读的事务就是修改当前版本记录的事务，因此该版本记录当然对该快照读sql可见
2. 如果被访问版本的DB_TRX_ID<min_trx_id意味着生成当前版本记录的事务早已提交(反正是在ReadView形成之前提交)，当然也是对该快照读sql可见的
3. 如果被访问版本的DB_TRX_ID>=max_trx_id意味着生成该版本的事务在要执行快照读的事务生成ReadView后才开启，不可见
4. 如果被访问版本min_trx_id<=DB_TRX_ID< max_trx_id且DB_TRX_ID不在m_ids中，说明该版本记录对应事务已提交，对该快照读sql可见，反之不可见

ReadView如何实现Read Committed

Repeatable Read的ReadView同理

MVCC和隔离级别

通过在事务中每一次执行快照读时都会生成ReadView，实现了RC级别

通过在事务中第一次执行快照读时生成ReadView，后续的快照读均复用该ReadView，实现了RR级别(顺带避免了 部分 快照读导致的幻读)，事实上RR级别还用上了临键锁来避免 部分 当前读导致的幻读

为什么Repeatable Read解决不了幻读？

如上所述
准确说RR避免了部分幻读问题，但没办法完全解决

首先一个很重要的点：rr隔离级别由两部分机制来实现，MVCC负责快照读语句rr隔离级别，锁机制负责当前读语句rr隔离级别，这里并不是完全靠锁机制就是因为快照读部分没必要，这一部分可以靠MVCC提速

依靠MVCC的快照读可解决的幻读场景

通过MVCC复用第一次快照读ReadView避免了”事务两个快照读执行之间待查记录被其他事务增删改“，这种类型的幻读

依靠锁机制的当前读可解决的幻读场景

通过临键锁机制避免了 事务两个当前读执行之间待查记录被其他事务增删改 这种类型的幻读

MySQL 里除了普通查询是快照读，其他都是当前读，比如 update、insert、delete，这些语句执行前都会查询最新版本的数据，然后再做进一步的操作。

这很好理解，假设你要 update 一个记录，另一个事务已经 delete 这条记录并且提交事务了，这样不是会产生冲突吗，所以 update 的时候肯定要知道最新的数据。

另外，select … for update 这种查询语句是当前读，每次执行的时候都是读取最新的数据。

接下来，我们假设select … for update实现当前读是不加锁的（实际上是会加锁的）

显然，因为select … for update读最新版本，导致出现幻读，mysql官方在RR下会默认帮我们避免这种幻读，就是在底层实现select … for update操作时还加上了临键锁实现的

现在事务 A 执行了这条select … for update语句后，就会为该表加上 id 范围为 (2, +∞] 的 next-key lock即临键锁（next-key lock 是间隙锁+记录锁的组合）。

然后，事务 B 在执行插入语句的时候，判断到插入的位置被事务 A 加了 next-key lock，于是事物 B 会生成一个插入意向锁，同时进入阻塞状态，直到事务 A 提交了事务。这就避免了由于事务 B 插入新记录而导致事务 A 发生幻读的现象。

然后有两种幻读无法解决，下面细说

不可解决的幻读场景一

整个发生幻读的时序图如下：
在可重复读隔离级别下，事务 A 第一次执行普通的 select 语句时生成了一个
ReadView，假设事务A的事务id=1，B的id=2

m_ids	min_trx_id	max_trx_id	creator_trx_id
1，2	1	2	1

由于此时还没有id=5的数据所以返回空

紧接着事务B插入id=5的数据即

id	name	age	DB_TRX_ID	DB_ROLL_PTR
5	“小美”	18	2	null

此时由于update是当前读而非快照读于是会读取最新版本(MVCC只对普通查询操作即快照读管用，update、delete和select for update这几种并不在此列，所以Update行为不受前面生成的ReadView限制，所以update可以看见id = 5的这条数据)

于是数据的DB_TRX_ID字段改为1(第二行是undo log)

id	name	age	DB_TRX_ID	DB_ROLL_PTR
5	“小林coding”	18	1	⬇
5	“小美”	18	2	null

此时，事务A的第二次快照读复用第一次的ReadView即

m_ids	min_trx_id	max_trx_id	creator_trx_id
1，2	1	2	1

此时访问id=5记录时因为该记录最新版本的DB_TRX_ID=creator_trx_id=1，所以事务A访问到了id=5的数据即发生幻读

不可解决的幻读场景二

1. T1 时刻：事务 A 先执行「快照读语句」：select * from t_test where id > 100 得到了 3 条记录。
2. T2 时刻：事务 B 往插入一个 id= 200 的记录并提交；
3. T3 时刻：事务 A 再执行「当前读语句」 select * from t_test where id > 100 for update 就会得到 4 条记录，此时也发生了幻读现象。

这是因为T1时刻使用的是快照读语句而不是当前读导致没能上临键锁(100,+∞），所以T2的记录插入畅通无阻(没被阻塞)，导致T3当前读为时已晚，读到了T2生成的最新版本

要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select … for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。

总结

• rr通过 MVCC 方式解决了快照读（普通 select 语句）类型的幻读。
• rr通过 next-key lock（记录锁+间隙锁）方式解决了当前读（select … for update 等语句）类型的幻读。

MVCC解决不了当前读是很直观的，因为MVCC靠版本，但是当前读是不管版本的，直接就去读最新的，所以在当前读mvcc废了。

注意：rr下当前读的临键锁除非是手动释放否则事务完成才释放

锁！！！！！！！！！！！

全局锁

mysql命令行下执行下面命令就会为所有数据库实例上锁，

flush tables with read lock

执行后，整个数据库里面的每个数据库实例中的所有表都处于只读状态了，这时其他线程执行以下操作，都会被阻塞：

• 对数据的增删改操作，比如 insert、delete、update等语句
• 对表结构的更改操作，比如 alter table、drop table 等语句

如果要释放全局锁，则要执行这条命令：

unlock tables

如果会话断开了，全局锁也是会被自动释放的。

全局锁应用场景

全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。

举个例子大家就知道了。

在全库逻辑备份期间，假设不加全局锁的场景，看看会出现什么意外的情况。

如果在全库逻辑备份期间，有用户购买了一件商品，一般购买商品的业务逻辑是会涉及到多张数据库表的更新，比如在用户表更新该用户的余额，然后在商品表更新被购买的商品的库存。

那么，有可能出现这样的顺序：

1. 先备份了用户表的数据；
2. 然后有用户发起了购买商品的操作；
3. 接着再备份商品表的数据。

也就是在备份用户表和商品表之间，有用户购买了商品。

这种情况下，备份的结果是用户表中该用户的余额并没有扣除，反而商品表中该商品的库存被减少了，如果后面用这个备份文件恢复数据库数据的话，用户钱没少，而库存少了，等于用户白嫖了一件商品。

所以，在全库逻辑备份期间，加上全局锁，就不会出现上面这种情况了。

全局锁缺点

加上全局锁，意味着整个数据库都是只读状态。

那么如果数据库里有很多数据，备份就会花费很多的时间，关键是备份期间，业务只能读数据，而不能更新数据，这样会造成业务停滞。

既然备份数据库数据的时候，使用全局锁会影响业务，那有什么其他方式可以避免？？？？？

有的，如果数据库的引擎支持的事务支持可重复读的隔离级别，那么在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。

因为在可重复读的隔离级别下，即使其他事务更新了表的数据，也不会影响备份数据库时的 Read View，这就是事务四大特性中的隔离性，这样备份期间备份的数据一直是在开启事务时的数据。

备份数据库的工具是 mysqldump，在使用 mysqldump 时加上 –single-transaction 参数的时候，就会在备份数据库之前先开启事务。这种方法只适用于支持「可重复读隔离级别的事务」的存储引擎。（mysqldump备份数据库本质是通过 SELECT 查询来获取表的数据所以还得是快照读也因此开启事务有意义因为有的数据库不支持事务或者当前没开事务，开了事务后的快照读会产生readview从而保证只能读生成readview之前最新的，而不是通过 INSERT INTO 向备份文件写入数据。导出的 INSERT INTO SQL 语句只是备份结果的一种表现形式，是给你用的）
即
查询阶段：mysqldump 运行的是一系列 SELECT 查询，用来读取数据表的内容。
输出阶段：查询结果被转化为备份文件中的 SQL 语句，例如 INSERT INTO table_name VALUES (…);。

InnoDB 存储引擎默认的事务隔离级别正是可重复读，因此可以采用这种方式来备份数据库。

但是，对于 MyISAM 这种不支持事务的引擎，在备份数据库时就要使用全局锁的方法。

表级锁

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

1. 表锁；
2. 元数据锁（MDL）;
3. 意向锁；
4. AUTO-INC 锁；

表锁(应用场景？？？)

如果我们想对学生表（t_student）加表锁，可以使用下面的命令：

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

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

需要注意的是，表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作。

也就是说如果本线程对学生表加了「共享表锁」，那么本线程接下来如果要对学生表执行写操作的语句，是会被阻塞的，当然其他线程对学生表进行写操作时也会被阻塞，直到锁被释放。

要释放表锁，可以使用下面这条命令，会释放当前会话的所有表锁：

unlock tables

另外，当会话退出后，也会释放所有表锁。

不过尽量避免在使用 InnoDB 引擎的表使用表锁，因为表锁的颗粒度太大，会影响并发性能，InnoDB 牛逼的地方在于实现了颗粒度更细的行级锁。

元数据锁

我们不需要显示的使用 MDL，因为当我们对数据库表进行操作时，会自动给这个表加上 MDL：

• 对一张表进行 CRUD 操作时，加的是 MDL 读锁（即使是普通select即快照读）表明有人正在使用这张表；
• 对一张表做结构变更操作的时候，加的是 MDL 写锁；

MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。

当有线程在执行 select 语句（ 加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（ 申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（ 释放 MDL 读锁）。

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

MDL 不需要显示调用，那它是在什么时候释放的?

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

那如果数据库有一个长事务（所谓的长事务，就是开启了事务，但是一直还没提交），那在对表结构做变更操作的时候，可能会发生意想不到的事情，比如下面这个顺序的场景：

1. 首先，线程 A 先启用了事务（但是一直不提交），然后执行一条 select 语句，此时就先对该表加上 MDL 读锁；
2. 然后，线程 B 也执行了同样的 select 语句，此时并不会阻塞，因为「读读」并不冲突；
3. 接着，线程 C 修改了表字段，此时由于线程 A 的事务并没有提交，也就是 MDL 读锁还在占用着，这时线程 C 就无法申请到 MDL 写锁，就会被阻塞，

那么在线程 C 阻塞后，后续有对该表的 select 语句，就都会被阻塞，如果此时有大量该表的 select 语句的请求到来，就会有大量的线程被阻塞住，这时数据库的线程很快就会爆满了。

为什么线程 C 因为申请不到 MDL 写锁，而导致后续的申请读锁的查询操作也会被阻塞？

这是因为申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。

所以为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务，是否有事务已经对表加上了 MDL 读锁，如果可以考虑 kill 掉这个长事务，然后再做表结构的变更。

意向锁

意向锁也是表级别的，而且比较特殊，意向锁之间(ss、sx、xx)完全兼容，不互斥的

意向锁是为了提高发生表锁和行锁冲突的检测效率，因为要上独占表锁之前肯定需要确认表中每行都没有行锁（独占行锁或共享行锁），但又不想遍历每一行去检测是否有行锁，那就通过 “每次上行锁前都先上对应意向锁” 这种方式，使得上独占表锁之前只需看一下是否存在意向锁即可，不存在就可以上了，反之不能

• 在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」；
• 在使用 InnoDB 引擎的表里对某些记录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」；

也就是，当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占行锁。

而执行普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。

不过，select 也是可以对记录加共享锁和独占锁的，具体方式如下：

//先在表上加上意向共享锁，然后对读取的记录加共享锁
select ... lock in share mode;

//先表上加上意向独占锁，然后对读取的记录加独占锁
select ... for update;

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables … read）和独占表锁（lock tables … write）发生冲突。

表锁和行锁是满足读读共享、读写互斥、写写互斥的。

如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。

那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录。

所以，意向锁的目的是为了快速判断表里是否有记录被加锁。

AUTO-INC 锁

表里的主键通常都会设置成自增的，这是通过对主键字段声明 AUTO_INCREMENT 属性实现的。

之后可以在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过 AUTO-INC 锁实现的。

AUTO-INC 锁是特殊的表锁机制，锁不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放。

在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等整条插入语句完全执行完成后，才会把 AUTO-INC 锁释放掉。

那么，一个事务在持有 AUTO-INC 锁的过程中，其他事务的如果要向该表插入语句都会被阻塞，从而保证插入数据时，被 AUTO_INCREMENT 修饰的字段的值是连续递增的。

但是， AUTO-INC 锁再对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。即这种表级锁导致了多个插入语句是完全串行的。

因此， 在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级（粒度小）的锁即互斥锁来实现自增。

即只有在为要插入的记录申请自增值时用一个互斥锁来自增当前自增值的系统变量，自增完成立刻分配给要插入的记录然后放锁。这种比表级锁并发程度更高因为没有了表级锁，那么第一个insert执行的同时其他的insert也可以先执行一部分即申请自增值之前的工作（计组流水线），即使是第一个insert到了申请自增值阶段。

InnoDB 存储引擎提供了个 innodb_autoinc_lock_mode 的系统变量，是用来控制选择用 AUTO-INC 锁，还是轻量级的锁。按照数字并发性能递增：

• 当 innodb_autoinc_lock_mode = 0，传统模式。就采用 AUTO-INC 锁，语句执行结束后才释放锁；
• 当 innodb_autoinc_lock_mode = 2，无锁模式（交叉模式）。对于所有类型的insert语句都用1的轻量级互斥锁来直接并发竞争，并发执行所带来的副作用就是单个批量INSERT语句内部的自增值并不连续，因为 AUTO_INCREMENT 的值分配会在多个 INSERT 语句中来回交叉的执行，比如事务A插入3条数据，B插入1条，那可能会得到A：1，3，4而B：2而不是A直接123或者234
• 当 innodb_autoinc_lock_mode = 1，连续模式。
  - 普通 insert 语句，使用轻量级的互斥锁即乐观锁即采用CAS+自旋替代原有的表级自增锁实现，在处理自增值字段时申请这个锁，申请到自增值后就马上释放。如果是insert多条具体数据即可确定插入数量，那也是基于这种锁，一次性自增具体数量，然后一次性分配给事务再立刻放锁；
  - 类似 insert … select 这样的不确定插入数量的批量插入数据的语句，仍旧使用表级自增锁，要等insert语句完全执行后才被释放；

在 MySQL 8.0 之前，InnoDB 锁模式默认为连续模式，值为1，而在 MySQL 8.0 之后，默认模式变成了交叉模式

行级锁

InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。

前面也提到，普通的 select 语句是不会对记录加锁的，因为它属于快照读。如果要在查询时对记录加行锁，可以使用下面这两个方式，这种查询会加锁的语句称为锁定读。上记录锁即行锁是当前读语句的默认行为，如果在不同隔离级别比如rr那会利用记录锁+间隙锁即临键锁来尽量（非完全）避免幻读，而且不同细分情况还会进一步优化利用不同锁来处理

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

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

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

共享锁（S锁）满足读读共享，读写互斥。独占锁（X锁）满足写写互斥、读写互斥。
行级锁的类型主要有三类：

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

记录锁 通常也直接说是行锁

Record Lock 称为记录锁通常也直接说是行锁，锁住的是一条记录。而且记录锁是有 S 锁(select lock in share mode)和 X 锁(插更删或查for update)之分的：

• 当一个事务对一条记录加了 S 型记录锁后，其他事务也可以继续对该记录加 S 型记录锁（S 型与 S 锁兼容），但是不可以对该记录加 X 型记录锁（S 型与 X 锁不兼容）;
• 当一个事务对一条记录加了 X 型记录锁后，其他事务既不可以对该记录加 S 型记录锁（S 型与 X 锁不兼容），也不可以对该记录加 X 型记录锁（X 型与 X 锁不兼容）。

举个例子，当一个事务执行了下面这条语句：

mysql > begin;
mysql > select * from t_test where id = 1 for update;

就是对 t_test 表中主键 id 为 1 的这条记录加上 X 型的记录锁，这样其他事务就无法对这条记录进行读取和修改了。

当事务执行 commit 后，事务过程中生成的锁都会被释放。

间隙锁（仅rr下生效，为了尽量解决幻读）

Gap Lock 称为间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离即rr级别下的部分幻读现象。通常是出现于范围查询的当前读情况，比如update XX set XX=“XX” where id>3 and id<5

假设，表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的减少幻读现象的发生。

间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的X或S型间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的。

临键锁（仅rr下生效，为了尽量解决幻读）

Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

当前读本来正常就已经上了记录锁，rr下会进一步上一个间隙锁，于是看起来当前读在rr下上的就是临键锁

假设，表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改 id = 5 这条记录。

所以，next-key lock 即能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。

next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。

比如，一个事务持有了范围为 (1, 10] 的 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，就会被阻塞。

虽然相同范围的间隙锁是多个事务相互兼容的，但对于记录锁，我们是要考虑 X 型与 S 型关系，X 型的记录锁与 X 型的记录锁是冲突的。

Tips：间隙锁只会阻塞insert，记录锁会阻塞任意的锁（要注意排他锁和共享锁的关系）；

特殊情况

rr级别下：

1. 唯一约束字段的等值查询，

插入意向锁（区别于表级的意向锁）针对insert的

一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁（next-key lock 也包含间隙锁）。

如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。

举个例子，假设事务 A 已经对表加了一个范围 id 为（3，5）间隙锁。

当事务 A 还没提交的时候，事务 B 向该表插入一条 id = 4 的新记录，这时会判断到插入的位置已经被事务 A 加了间隙锁，于是事物 B 会生成一个插入意向锁，然后将锁的状态设置为等待状态（PS：MySQL 加锁时，是先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁），此时事务 B 就会发生阻塞，直到事务 A 提交了事务。

插入意向锁名字虽然有意向锁，但是它并不是意向锁，它是一种特殊的间隙锁，属于行级别锁。

如果说间隙锁锁住的是一个区间，那么「插入意向锁」锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。

插入意向锁与间隙锁的另一个非常重要的差别是：尽管「插入意向锁」也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）。

limit效率

select * from test where val=4 limit 300000,5;
300000是offset，5是数量
假设val有索引，
理论上：那就先从这个辅助索引开始找到300005个val=4的主键，然后回表。又或者会全表扫描，反正就是很耗时，最后返回给server层300005条记录，由它拿出最后5条

分库分表

key%n 其中n为数据库服务器数量

这种方式简单但是不方便集群伸缩，无论是扩容还是删除都很麻烦，几乎每次加入一台服务器都会导致所有服务器之间进行数据迁移

0 1 2 三台数据库服务器

n=3

key            0 1 2 3 4 5 6 7 8 9
所属数据库id    0 1 2 0 1 2 0 1 2 0
-----------------------------------
0 1 2 3 新增一台数据库服务器

n=4

key         0 1 2 3 4 5 6 7 8 9
所属数据库id	0 1 2 3 0 1 2 3 0 1


最终对比
key 0 1 2 3 4 5 6 7 8 9
从  0 1 2 0 1 2 0 1 2 0
到  0 1 2 3 0 1 2 3 0 1

一致性hash

新增服务器时只需要最近的一台服务器与之迁移数据

图中的蓝色长方体是数据库服务器，橙色圆形是数据即要存储的记录

首先数据库服务器根据ip哈希到环上（随机分布），接着每当数据来哈希得到一个值然后顺时针找到离他最近的服务器即最小的大于该哈希值的服务器，

这样移除了B节点也只需要把B节点数据迁移给顺时针离他最近的C

除此之外，因为哈希随机映射如果很聚集那么数据也会聚集存到一个数据库上

所以每个真实数据库都会有很多的虚拟节点，尽量使得圆环上的不同服务器分布均匀

主从同步

双方开一个tcp长连接，主的dump线程发现自己的binlog变化则将新的日志内容发送到从的io线程，从的io线程将其写入relay log，并由sql线程定期执行relay log中内容

mybatis

sql注入：攻击者通过在用户输入中插入恶意的 SQL 代码，欺骗数据库执行非预期的操作

${}的内容会被直接拼接到sql语句中容易导致sql注入即’or 1==1，

SELECT * FROM users WHERE username = '${username}' AND password = '${password}';
如果用户输入 username 为 admin' --，生成的 SQL 语句就会变成：
SELECT * FROM users WHERE username = 'admin' --' AND password = '${password}';

#{}在把sql语句传给preparedstatement时用作占位符，后面可以通过preparedstatement.set指定第几个{}填充什么值，preparedstatement会预编译，把sql语句的结构固定下来，而且还会为{}的内容外面加上单引号，以及转义字符避免用户的’打乱sql语句结构即改为’，预编译后可以重复利用，只需要改变参数，不用每次提交原生的sql语句