大神地址：https://mp./s/pWHCieOwAdCrz8cauduWlQ

学而思资料：https

mysql数组库

第一章 数据库索引1.1 数据库索引1.2 MyISAM和InnoDb1.2.1 MyISAM1.2.2 InnoDb第二章 InnoDB并发如此高，原因竟然在这？2.1 并发控制2.2 锁2.3 数据多版本2.4 redo, undo,回滚段2.5 两种引擎对比第三章 七种锁3.1 自增锁3.2 共享/排他锁3.3 意向锁3.4 插入意向锁3.5 记录锁锁定索引记录3.6 间隙锁锁定间隔，防止间隔中被其他事务插入3.7 临键锁锁定索引记录+间隔，防止幻读；第四章 四种隔离级别4.1 读未提交(Read Uncommitted)4.2 串行化(Serializable)4.3 可重复读(Repeated Read, RR)4.4 读提交(Read Committed, RC)4.5 总结第五章 主键与唯一索引第六章 缓冲池6.1 读缓冲6.2 写缓冲

第一章 数据库索引

1.1 数据库索引

数据库索引用于加速查询

虽然哈希索引是O(1)，树索引是O(log(n))，但SQL有很多“有序”需求，故数据库使用树型索引

InnoDB不支持哈希索引

数据预读的思路是：磁盘读写并不是按需读取，而是按页预读，一次会读一页的数据，每次加载更多的数据，以便未来减少磁盘IO

局部性原理：软件设计要尽量遵循“数据读取集中”与“使用到一个数据，大概率会使用其附近的数据”，这样磁盘预读能充分提高磁盘IO

数据库的索引最常用B+树：

(1)很适合磁盘存储，能够充分利用局部性原理，磁盘预读；

(2)很低的树高度，能够存储大量数据；

(3)索引本身占用的内存很小；

(4)能够很好的支持单点查询，范围查询，有序性查询；

1.2 MyISAM和InnoDb

1.2 MyISAM和InnoDb

1.2.1 MyISAM

MyISAM的索引与行记录是分开存储的，叫做非聚集索引（UnClustered Index）。

其主键索引与普通索引没有本质差异：

有连续聚集的区域单独存储行记录

主键索引的叶子节点，存储主键，与对应行记录的指针

普通索引的叶子结点，存储索引列，与对应行记录的指针

画外音：MyISAM的表可以没有主键。

主键索引与普通索引是两棵独立的索引B+树，通过索引列查找时，先定位到B+树的叶子节点，再通过指针定位到行记录。

举个例子，MyISAM：

t(id PK, name KEY, sex, flag);

表中有四条记录：

1, shenjian, m, A

3, zhangsan, m, A

5, lisi, m, A

9, wangwu, f, B

其B+树索引构造如上图：

行记录单独存储

id为PK，有一棵id的索引树，叶子指向行记录

name为KEY，有一棵name的索引树，叶子也指向行记录

1.2.2 InnoDb

InnoDB的索引*InnoDB的主键索引与行记录是存储在一起的，故叫做聚集索引（Clustered Index）：

没有单独区域存储行记录

主键索引的叶子节点，存储主键，与对应行记录（而不是指针）

画外音：因此，InnoDB的PK查询是非常快的。

因为这个特性，InnoDB的表必须要有聚集索引：

(1)如果表定义了PK，则PK就是聚集索引；(2)如果表没有定义PK，则第一个非空unique列是聚集索引；(3)否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

** 聚集索引，也只能够有一个，因为数据行在物理磁盘上只能有一份聚集存储。

InnoDB的普通索引可以有多个，它与聚集索引是不同的：

普通索引的叶子节点，存储主键（也不是指针）**

对于InnoDB表，这里的启示是：

(1)不建议使用较长的列做主键，例如char(64)，因为所有的普通索引都会存储主键，会导致普通索引过于庞大；

(2)建议使用趋势递增的key做主键，由于数据行与索引一体，这样不至于插入记录时，有大量索引分裂，行记录移动；

仍是上面的例子，只是存储引擎换成InnoDB：

t(id PK, name KEY, sex, flag);

表中还是四条记录：

1, shenjian, m, A

3, zhangsan, m, A

5, lisi, m, A

9, wangwu, f, B

其B+树索引构造如上图：

id为PK，行记录和id索引树存储在一起

name为KEY，有一棵name的索引树，叶子存储id

当：

select * from t where name=‘lisi’;

会先通过name辅助索引定位到B+树的叶子节点得到id=5，再通过聚集索引定位到行记录。

画外音：所以，其实扫了2遍索引树。

三，总结

MyISAM和InnoDB都使用B+树来实现索引：

MyISAM的索引与数据分开存储

MyISAM的索引叶子存储指针，主键索引与普通索引无太大区别

InnoDB的聚集索引和数据行统一存储

InnoDB的聚集索引存储数据行本身，普通索引存储主键

InnoDB一定有且只有一个聚集索引

InnoDB建议使用趋势递增整数作为PK，而不宜使用较长的列作为PK

第二章 InnoDB并发如此高，原因竟然在这？

InnoDB并发如此高，原因竟然在这？

2.1 并发控制

为啥要进行并发控制？

并发的任务对同一个临界资源进行操作，如果不采取措施，可能导致不一致，故必须进行并发控制（Concurrency Control）。

技术上，通常如何进行并发控制？

通过并发控制保证数据一致性的常见手段有：

锁（Locking）

数据多版本（Multi Versioning）

2.2 锁

如何使用普通锁保证一致性？

普通锁，被使用最多：

(1)操作数据前，锁住，实施互斥，不允许其他的并发任务操作；

(2)操作完成后，释放锁，让其他任务执行；

如此这般，来保证一致性。

普通锁存在什么问题？

简单的锁住太过粗暴，连“读任务”也无法并行，任务执行过程本质上是串行的。

于是出现了共享锁与排他锁：

共享锁（Share Locks，记为S锁），读取数据时加S锁

排他锁（eXclusive Locks，记为X锁），修改数据时加X锁

共享锁与排他锁的玩法是：

共享锁之间不互斥，简记为：读读可以并行

排他锁与任何锁互斥，简记为：写读，写写不可以并行

可以看到，一旦写数据的任务没有完成，数据是不能被其他任务读取的，这对并发度有较大的影响。

画外音：对应到数据库，可以理解为，写事务没有提交，读相关数据的select也会被阻塞。

有没有可能，进一步提高并发呢？

即使写任务没有完成，其他读任务也可能并发，这就引出了数据多版本。

2.3 数据多版本

数据多版本是一种能够进一步提高并发的方法，它的核心原理是：

（1）写任务发生时，将数据克隆一份，以版本号区分；

（2）写任务操作新克隆的数据，直至提交；

（3）并发读任务可以继续读取旧版本的数据，不至于阻塞；

如上图：

最开始数据的版本是V0；T1时刻发起了一个写任务，这是把数据clone了一份，进行修改，版本变为V1，但任务还未完成；T2时刻并发了一个读任务，依然可以读V0版本的数据；T3时刻又并发了一个读任务，依然不会阻塞；

可以看到，数据多版本，通过“读取旧版本数据”能够极大提高任务的并发度。

提高并发的演进思路，就在如此：

普通锁，本质是串行执行

读写锁，可以实现读读并发

数据多版本，可以实现读写并发

画外音：这个思路，比整篇文章的其他技术细节更重要，希望大家牢记。

好，对应到InnoDB上，具体是怎么玩的呢？

2.4 redo, undo,回滚段

在进一步介绍InnoDB如何使用“读取旧版本数据”极大提高任务的并发度之前，有必要先介绍下redo日志，undo日志，回滚段（rollback segment）。

为什么要有redo日志？

数据库事务提交后，必须将更新后的数据刷到磁盘上，以保证ACID特性。磁盘随机写性能较低，如果每次都刷盘，会极大影响数据库的吞吐量。

优化方式是，将修改行为先写到redo日志里（此时变成了顺序写），再定期将数据刷到磁盘上，这样能极大提高性能。

画外音：这里的架构设计方法是，随机写优化为顺序写，思路更重要。

假如某一时刻，数据库崩溃，还没来得及刷盘的数据，在数据库重启后，会重做redo日志里的内容，以保证已提交事务对数据产生的影响都刷到磁盘上。

一句话，redo日志用于保障，已提交事务的ACID特性。

为什么要有undo日志？

数据库事务未提交时，会将事务修改数据的镜像（即修改前的旧版本）存放到undo日志里，当事务回滚时，或者数据库奔溃时，可以利用undo日志，即旧版本数据，撤销未提交事务对数据库产生的影响。

画外音：更细节的，

对于insert操作，undo日志记录新数据的PK(ROW_ID)，回滚时直接删除；

对于delete/update操作，undo日志记录旧数据row，回滚时直接恢复；

他们分别存放在不同的buffer里。

一句话，undo日志用于保障，未提交事务不会对数据库的ACID特性产生影响。

什么是回滚段？

存储undo日志的地方，是回滚段。

undo日志和回滚段和InnoDB的MVCC密切相关，这里举个例子展开说明一下。

栗子：

t(id PK, name);

数据为：

1, shenjian

2, zhangsan

3, lisi

此时没有事务未提交，故回滚段是空的。

接着启动了一个事务：

start trx;

delete (1, shenjian);

update set(3, lisi) to (3, xxx);

insert (4, wangwu);

并且事务处于未提交的状态。

可以看到：

(1)被删除前的(1, shenjian)作为旧版本数据，进入了回滚段；

(2)被修改前的(3, lisi)作为旧版本数据，进入了回滚段；

(3)被插入的数据，PK(4)进入了回滚段；

接下来，假如事务rollback，此时可以通过回滚段里的undo日志回滚。

画外音：假设事务提交，回滚段里的undo日志可以删除。

可以看到：

(1)被删除的旧数据恢复了；

(2)被修改的旧数据也恢复了；

(3)被插入的数据，删除了；

事务回滚成功，一切如故。

四、InnoDB是基于多版本并发控制的存储引擎

《大数据量，高并发量的互联网业务，一定要使用InnoDB》提到，InnoDB是高并发互联网场景最为推荐的存储引擎，根本原因，就是其多版本并发控制（Multi Version Concurrency Control, MVCC）。行锁，并发，事务回滚等多种特性都和MVCC相关。

MVCC就是通过“读取旧版本数据”来降低并发事务的锁冲突，提高任务的并发度。

核心问题：

旧版本数据存储在哪里？

存储旧版本数据，对MySQL和InnoDB原有架构是否有巨大冲击？

通过上文undo日志和回滚段的铺垫，这两个问题就非常好回答了：

(1)旧版本数据存储在回滚段里；

(2)对MySQL和InnoDB原有架构体系冲击不大；

InnoDB的内核，会对所有row数据增加三个内部属性：

(1)DB_TRX_ID，6字节，记录每一行最近一次修改它的事务ID；

(2)DB_ROLL_PTR，7字节，记录指向回滚段undo日志的指针；

(3)DB_ROW_ID，6字节，单调递增的行ID；

InnoDB为何能够做到这么高的并发？

回滚段里的数据，其实是历史数据的快照（snapshot），这些数据是不会被修改，select可以肆无忌惮的并发读取他们。

快照读（Snapshot Read），这种一致性不加锁的读（Consistent Nonlocking Read），就是InnoDB并发如此之高的核心原因之一。

这里的一致性是指，事务读取到的数据，要么是事务开始前就已经存在的数据（当然，是其他已提交事务产生的），要么是事务自身插入或者修改的数据。

什么样的select是快照读？

除非显示加锁，普通的select语句都是快照读，例如：

select * from t where id>2;

这里的显示加锁，非快照读是指：

select * from t where id>2 lock in share mode;

select * from t where id>2 for update;

redolog参数配置

show variables like ‘%innodb_flush_log_at_trx_commit%’;

沈剑大神mysql 事务提交

亲眼所见系列：[大神说应该选2 ，原因如上连接]主库 innodb_flush_log_at_trx_commit=1 强一致性从库 innodb_flush_log_at_trx_commit=0 性能最好，每隔一秒刷新写入，数据库崩溃可能有1秒数据丢失innodb_flush_log_at_trx_commit=2 折中

总结

(1)常见并发控制保证数据一致性的方法有锁，数据多版本；

(2)普通锁串行，读写锁读读并行，数据多版本读写并行；

(3)redo日志保证已提交事务的ACID特性，设计思路是，通过顺序写替代随机写，提高并发；

(4)undo日志用来回滚未提交的事务，它存储在回滚段里；

(5)InnoDB是基于MVCC的存储引擎，它利用了存储在回滚段里的undo日志，即数据的旧版本，提高并发；

(6)InnoDB之所以并发高，快照读不加锁；

(7)InnoDB所有普通select都是快照读；

画外音：本文的知识点均基于MySQL5.6。

2.5 两种引擎对比

知识点：

MyISAM只支持表锁，InnoDB可以支持行锁。MyISAM不支持事务，InnoDB支持事务。MyISAM不支持外键，InnoDB支持外键。MyISAM会直接存储总行数，InnoDB则不会，需要按行扫描。

InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

启示：InnoDB务必建好索引，否则锁粒度较大，会影响并发。

在大数据量，高并发量的互联网业务场景下，对于MyISAM和InnoDB

有where条件，count(*)两个存储引擎性能差不多不要使用全文索引，应当使用《索引外置》的设计方案事务影响性能，强一致性要求才使用事务不用外键，由应用程序来保证完整性不命中索引，InnoDB也不能用行锁

结论

在大数据量，高并发量的互联网业务场景下，请使用InnoDB:行锁，对提高并发帮助很大事务，对数据一致性帮助很大这两个点，是InnoDB最吸引人的地方。

第三章 七种锁

3.1 自增锁

自增锁是一种特殊的表级别锁（table-level lock），专门针对事务插入AUTO_INCREMENT类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

与此同时，InnoDB提供了innodb_autoinc_lock_mode配置，可以调节与改变该锁的模式与行为。

3.2 共享/排他锁

InnoDB并发插入，居然使用意向锁？

在InnoDB里当然也实现了标准的行级锁(row-level locking)，共享/排它锁：

(1)事务拿到某一行记录的共享S锁，才可以读取这一行；

(2)事务拿到某一行记录的排它X锁，才可以修改或者删除这一行；

修改就会将该行锁住

3.3 意向锁

InnoDB并发插入，居然使用意向锁？

它会与共享锁/排它锁互斥，其兼容互斥表如下：

S X

IS 兼容 互斥

IX 互斥 互斥

意向锁分为：

意向共享锁(intention shared lock, IS)，它预示着，事务有意向对表中的某些行加共享S锁

意向排它锁(intention exclusive lock, IX)，它预示着，事务有意向对表中的某些行加排它X锁

举个例子：

select … lock in share mode，要设置IS锁；

select … for update，要设置IX锁；

3.4 插入意向锁

InnoDB并发插入，居然使用意向锁？

插入意向锁，是间隙锁(Gap Locks)的一种（所以，也是实施在索引上的），它是专门针对insert操作的。

它的玩法是：

多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此。

InnoDB使用共享锁，可以提高读读并发；为了保证数据强一致，InnoDB使用强互斥锁，保证同一行记录修改与删除的串行性；InnoDB使用插入意向锁，可以提高插入并发；

3.5 记录锁锁定索引记录

InnoDB的索引与行记录存储在一起，这一点和MyISAM不一样；InnoDB的聚集索引存储行记录，普通索引存储PK，所以普通索引要查询两次；select * from t where id=1 for update;select * from t where id=1; 快照读，不加锁

3.6 间隙锁锁定间隔，防止间隔中被其他事务插入

select * from t

where id between 8 and 15

for update;

3.7 临键锁锁定索引记录+间隔，防止幻读；

表中有四条记录：

1, shenjian, m, A

3, zhangsan, m, A

5, lisi, m, A

9, wangwu, f, B

PK上潜在的临键锁为：

(-infinity, 1]

(1, 3]

(3, 5]

(5, 9]

(9, +infinity]

第四章 四种隔离级别

大神地址：4种事务的隔离级别，InnoDB如何巧妙实现？

可以看到，并发的事务可能导致其他事务：

读脏不可重复读幻读

按照SQL92标准，InnoDB实现了四种不同事务的隔离级别：

读未提交(Read Uncommitted)读提交(Read Committed, RC)可重复读(Repeated Read, RR)串行化(Serializable)

不同事务的隔离级别，实际上是一致性与并发性的一个权衡与折衷。

4.1 读未提交(Read Uncommitted)

这种事务隔离级别下，select语句不加锁。

此时，可能读取到不一致的数据，即“读脏”。这是并发最高，一致性最差的隔离级别。

4.2 串行化(Serializable)

这种事务的隔离级别下，所有select语句都会被隐式的转化为select … in share mode（意向锁）这可能导致，如果有未提交的事务正在修改某些行，所有读取这些行的select都会被阻塞住。这是一致性最好的，但并发性最差的隔离级别。

在互联网大数据量，高并发量的场景下，几乎不会使用上述两种隔离级别。

4.3 可重复读(Repeated Read, RR)

这是InnoDB默认的隔离级别，在RR下：

普通的select使用快照读(snapshot read)，这是一种不加锁的一致性读(Consistent Nonlocking Read)，底层使用MVCC来实现，加锁的select(select … in share mode / select … for update), update, delete等语句，它们的锁，依赖于它们是否在唯一索引(unique index)上使用了唯一的查询条件(unique search condition)，或者范围查询条件(range-type search condition)：在唯一索引上使用唯一的查询条件，会使用记录锁(record lock)，而不会封锁记录之间的间隔，即不会使用间隙锁(gap lock)与临键锁(next-key lock)范围查询条件，会使用间隙锁与临键锁，锁住索引记录之间的范围，避免范围间插入记录，以避免产生幻影行记录，以及避免不可重复的读

4.4 读提交(Read Committed, RC)

这是互联网最常用的隔离级别，在RC下：

普通读是快照读；加锁的select, update, delete等语句，除了在外键约束检查(foreign-key constraint checking)以及重复键检查(duplicate-key checking)时会封锁区间，其他时刻都只使用记录锁；此时，其他事务的插入依然可以执行，就可能导致，读取到幻影记录。

4.5 总结

并发事务之间相互干扰，可能导致事务出现读脏，不可重复度，幻读等问题

InnoDB实现了SQL92标准中的四种隔离级别

读未提交：select不加锁，可能出现读脏；读提交(RC)：普通select快照读，锁select /update /delete 会使用记录锁，可能出现不可重复读；可重复读(RR)：普通select快照读，锁select /update /delete 根据查询条件情况，会选择记录锁，或者间隙锁/临键锁，以防止读取到幻影记录；串行化：select隐式转化为select … in share mode，会被update与delete互斥；

InnoDB默认的隔离级别是RR，用得最多的隔离级别是RC

第五章 主键与唯一索引

总结，对于主键与唯一索引约束：

执行insert和update时，会触发约束检查InnoDB违反约束时，会回滚对应SQLMyISAM违反约束时，会中断对应的SQL，可能造成不符合预期的结果集可以使用 insert … on duplicate key 来指定触发约束时的动作通常使用 show warnings; 来查看与调试违反约束的ERROR

第六章 缓冲池

【58沈剑大神】缓冲池(buffer pool)，这次彻底懂了！！！

6.1 读缓冲

总结：

（1）缓冲池(buffer pool)是一种常见的降低磁盘访问的机制；

（2）缓冲池通常以页(page)为单位缓存数据；

（3）缓冲池的常见管理算法是LRU，memcache，OS，InnoDB都使用了这种算法；

（4）InnoDB对普通LRU进行了优化：

将缓冲池分为老生代和新生代，入缓冲池的页，优先进入老生代，页被访问，才进入新生代，以解决预读失效的问题页被访问，且在老生代停留时间超过配置阈值的，才进入新生代，以解决批量数据访问，大量热数据淘汰的问题

参数：innodb_buffer_pool_size

介绍：配置缓冲池的大小，在内存允许的情况下，DBA往往会建议调大这个参数，越多数据和索引放到内存里，数据库的性能会越好。

innodb_buffer_pool_size数值的单位是字节（b），1kb=1024b，图中63GB左右。

参数：innodb_old_blocks_pct

介绍：老生代占整个LRU链长度的比例，默认是37，即整个LRU中新生代与老生代长度比例是63:37。

画外音：如果把这个参数设为100，就退化为普通LRU了。

参数：innodb_old_blocks_time

介绍：老生代停留时间窗口，单位是毫秒，默认是1000，即同时满足“被访问”与“在老生代停留时间超过1秒”两个条件，才会被插入到新生代头部。

6.2 写缓冲

写缓冲(change buffer)，这次彻底懂了！！！

什么时候适合使用写缓冲，如果：

（1）数据库大部分是非唯一索引；

（2）业务是写多读少，或者不是写后立刻读取；

可以使用写缓冲，将原本每次写入都需要进行磁盘IO的SQL，优化定期批量写磁盘。

画外音：例如，账单流水业务。

参数：innodb_change_buffer_max_size

介绍：配置写缓冲的大小，占整个缓冲池的比例，默认值是25%，最大值是50%。

画外音：写多读少的业务，才需要调大这个值，读多写少的业务，25%其实也多了。

参数：innodb_change_buffering

介绍：配置哪些写操作启用写缓冲，可以设置成all/none/inserts/deletes等。