大神地址: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等。