Mysql
2021-09-12 15:57:04 1 举报AI智能生成
Mysql资料整理
作者其他创作
大纲/内容
在底层跟数据库建立网络连接
mysql驱动
会维持多个数据库连接，执行完sql语句后，不销毁这个数据库连接，而是放回池子里，后续可以再使用
解决了多个线程并发使用多个数据库连接去执行sql语句的问题，也避免了数据库连接使用完之后就销毁的问题
常见的有DBCP，C3P0，Druid
数据库连接池
基础知识
2，sql接口：负责处理接收到的sql语句
3，查询解析器：让mysql看懂sql语句
4，查询优化器：选择最优的查询路径，生成执行计划
5，执行器：根据优化器生成的执行计划去调用存储引擎的接口
6，调用存储引擎接口，执行sql语句，访问内存以及磁盘上的数据
Sql语句执行流程
update users set name ='XXX' where id =10
1，buffer pool缓冲池，执行语句时，判断数据是否在缓冲池里，若没有，从磁盘读取放入缓冲池
2，更新时，会把更新前的值写入undo日志，便于回滚
提交事务前宕机，内存中buffer pool 数据丢失，redo日志也丢失，此时报数据库异常
提交事务时，redo日志写入磁盘。假如宕机，redo日志去恢复修改数据。
三个日志刷盘策略：\tinnodb_flush_log_at_trx_commit:0 不写入磁盘。\t\tinnodb_flush_log_at_trx_commit:1 一定会写入磁盘。通常建议设置为1\t\tinnodb_flush_log_at_trx_commit:2 先写入os cache，1秒后再写入磁盘。
4，把对内存的修改写入一个redo Log Buffer缓冲区，存放redo日志，用来宕机恢复数据
binlog不是innodb存储引擎特有的日志文件，是属于mysql server自己的日志文件
刷盘策略：sync_binlog:0，默认，先写os cache sync_binlog:1，强制写磁盘
binlog文件名称、位置写入redo log日志文件，同时redo日志写一个commit标记，这才表示事务最终完成
redo log的commit标记事务才算提交成功，redo log和binlog完全是一致的
5，提交事务时，binlog归档日志写入磁盘
在把脏数据写入磁盘之前，mysql宕机也没关系，因为重启后，根据redolog会恢复之前提交事务做过的修改到内存里去
6，io线程随机把buffer pool修改后的脏数据刷回磁盘
存储引擎架构
经验而言，java应用系统常选用机器大致是2核4G，4核8G，数据库最低也要在8核16G，正常16核32G，因为数据库需要执行大量的IO操作，这是比较慢的
16核32G每秒抗个两三千问题不大，但是如果达到每秒上万请求，那么数据库的cpu，磁盘，io，内存的负载瞬间飙升，可能扛不住
最好采用SSD固态硬盘，因为需要大量的读写磁盘文件，所以固态硬盘性能更高一些
QPS：代表数据库每秒能处理多少请求。TPS代表数据库每秒能处理多少事务
吞吐量：代表机器的磁盘存储每秒能读写多少字节
io相关的压测指标
数据的延迟写入，磁盘的读写延迟
cpu负载
网络负载
内存负载
对数据库做一个压测，再考虑java系统能承载的并发数
压测工具sysbench
生产环境配置
假如对磁盘文件读写，每次请求可能需要几百毫秒进行处理，相当的慢，所以实际上增删改查都是基于buffer pool、进行的
font color=\"#2196f3\
初始化：数据库启动时，分配一块内存区域，申请内存之后，buffer pool就会按照16K数据页加上每个数据页800字节的描述信息划分一个个的缓存页和描述数据，此时缓存页都是空的
用链表存储空闲的缓存页的描述数据
哪些缓存页是空闲的？每个空闲的缓存页的描述数据组成一个双向链表，作用是用来保存空闲缓存的描述数据
由一个free双向链表，缓存页的描述数据分别有两个指针，free-pre，free-next链接成一个链表，free链表并不会申请独立的空间。
free链表
数据库有一个哈希表数据结构，他会用表空间号+数据页号作为key，缓存页地址作为value，当用表空间号+数据页号去buffer pool查询，如果有，说明已经缓存了
怎么知道数据页有没有被缓存？
用链表存储脏数据描述数据
如果内存中的数据和数据库的数据不一样，就称为脏数据，还没有被刷到磁盘上的更新过的数据
哪些缓存页是已经更新了的“脏数据”？由一个flush双向链表记录，然后刷到磁盘中
flush双向链表
当BufferPool内存空间不够使用时，会淘汰最近最少使用的数据刷入到磁盘中去
淘汰算法：缓存命中率，引入一个LRU链表，当free链表没有空闲缓存页时，把最近最少使用的缓存页数据淘汰及刷入到磁盘中，释放空闲缓存页
被加载进来的缓存页顺序插入LRU链表头部（链表头插法），如果BufferPool不够用了，mysql就会将LRU链表的末尾节点刷入到磁盘
LRU链表怎么工作？
LRU链表
某条数据可能在数据页的头部，也可能在数据页的尾部，所以mysql为了提高效率会将某个数据也的相邻数据页也加载到bufferpool
预读机制优点：为了提高性能，减少IO操作
缺点：在全表扫描时，会把频繁访问的数据刷到磁盘里去，但仅使用一次。却将经常命中的缓存页直接挤到了LRU链表的尾部
冷热数据分离，第一次加载会放到冷数据链表头
innodb_old_blocks_time=1000毫秒，即在1S后（不是1S内）再次访问，才会把数据放入热数据链的表头，解决了全表扫描的问题之所以要在一定时间之后，是防止某条数据刚被加载到缓存池，紧接着又被访问，但是之后就不使用了，又出现上面的缺点
什么时候淘汰冷数据，假如缓存不足，在1S后然后没再次访问的数据，就在冷数据连尾部删除。
热数据区域优化：热数据区域的后3/4部分被再次访问，才移动到表头位置，为了减少链表的移动
为了解决预加载带来的缺点，推出了冷热分离的LRU链表
预读机制：会把相邻的数据页缓存到buffer pool
数据库后台有一个线程定时的把冷数据连尾部的几个缓存页刷到磁盘
刷入磁盘后，缓存页加入到free链表，从flush链表中移除，从LRU链表中移除
这个后台线程，同时会在繁忙的时候把flush链表中的缓存页刷到磁盘中。
详情加算法画图文件（LRU算法）
定义一个int CACHE_SIZE
最近访问的放在头，最老访问的就在尾
插入头部，判断是否大于CACHE_SIZE，如果大于，删除尾部
假如再次访问节点，刷新节点在链表中的位置，移到头部。
也可以直接继承LinkedHashMap来实现
LRU算法简单算法
LRU刷入磁盘
一边不停的加载数据到缓存页里去，不停的查询和修改缓存数据，然后free链表中的缓存页不停的在减少，flush链表中的缓存页不停的在增加，lru链表中的缓存页不停的增加和移动
另一边，后台线程不停的把lru链表的冷数据区域的缓存页以及flush链表的缓存页，输入磁盘来清空缓存页，然后flush链表和lru链表中的缓存页在减少，free链表中的缓存页在增加。
运转整体机制
因为每个bufferPool虽然在内存里运行，但每个bufferPool内部是串行执行的
设置多个buffer pool优化并发能力
buffer pool不支持运行期间动态调整大小
数据库可以由多个buffer pool组成，一个buffer pool由多个chunk组成，一个chunk默认是128M
buffer pool设置为服务器的内存50%-60%左右，32G内存就设置为16Gbuffer pool总值=（chunk*buffer pool数量）*倍数，必须是倍数，多少倍都行。
Buffer Pool
对应磁盘文件，是一个个的 \"表名.ibd\"
表空间：包含多组数据区一组数据区：256个数据区一个数据区：64个数据页，1MB数据页：16KB。包含多个数据行
执行crud时，说白了就是从磁盘上的表空间的数据文件里，加载一些数据页到buffer pool的缓存里去使用
表空间
redo log 日志是 偏物理层面的日志，也叫 重做日志。而 binlog 是归档日志redo log：本质就是 表空间+数据页号+偏移量+修改几个字节的值+具体的值
redo log block：512字节，存放多个单行日志redo log group：一个事务是一组日志，一个redo log group可能存放在多个block里面，一个block也可能存放多个groupredo log buffer：16MB，里面是block。
2，事务提交的时候。
3，后台线程定时刷新，每隔1秒
4，mysql关闭的时候。redo log block都会刷入磁盘
何时刷入磁盘
redo log
如果我们执行一个更新语句，在没有提交事务之前，我们都是可以对数据进行回滚的undo 日志文件就是保证我们可以回滚数据的一个组件举例：如果我们要把 id = 10 的数据的 name 从 张三 改为 李四第一步是把数据加载到 Buffer Pool 里第二步 就要把 id = 10 ，name = 张三 的这条原始数据，写到undo日志文件如果数据回滚，就会从 undo 日志文件中读取原始数据恢复
undo 日志文件
脏写：一个事务（回滚）修改了 另外一个事务未提交的修改过的数据
举例：原来有一行数据是 NULL
事务 B 做的也是 update 操作，把值 A 改成了值 B
sql标准中四种事务隔离级别read uncommitted \t读未提交，会避免脏写read committed\t读已提交，会避免脏写，脏读repeatable read \t可重复读，会避免脏写，脏读，不可重复读，保证事务内，读到的值都是同一个serializable\t串行化，不允许多个事务并发执行。这种级别一般除非脑子换了，否则不可能设置
mysql隔离级别默认的是repeatable read，但是mysql的RR级别，依托MVCC机制，可以避免幻读。事务时并发处理的，并且避免了幻读，所以就用默认的就特别好。一般情况下不用改。除非你一定要在你的事务执行期间多次查询时必须要查到其他事务已经提交的最新值，那么就设置成Isolation.READ_COMMITTED
undo log多版本链条+ReadView机制
ReadView：基于undo log版本链条实现的一套试图机制，ReadView一旦生成就不会改变
RC隔离级别：的关键点在于，事务里每次查询都生成新的ReadView
RR隔离级别：的关键点在于，事务里每次查询都是同一个ReadView
MVCC机制：多版本并发控制机制
原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做
一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到
隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账
持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚
基本要素ACID
详见分布式事务process脑图
spring的事务
事务，多线程
共享锁也叫S锁：查询的时候加，与共享锁是不互斥的。与独占锁互斥。select * from table lock in share，加了之后其他事务想更新时更新不了的，因为另外的事务拿不到独占锁。
独占锁也叫X锁：更新的时候加，都互斥查询时，加独占锁：select * from table for update
1、 行锁：锁定一行记录2、 间隙锁：锁定一个范围，但是不包含记录本身3、Next-Key Lock：Record Lock和Gap Lock的组合，锁定一个区间以及对应的记录4、 表锁：锁定整张表
锁粒度
锁机制
SHOW VARIABLES LIKE '%datadir%'
默认是这里 （数据库安装目录\\data\\某个数据库 ）
数据库文件都有后缀例如：数据文件：. myd索引文件：. MYI表定义文件：. frm
数据库索引的位置
存储结构：数据页之间：双向链表数据页内部数据行：单向链表
数据页分裂：假如主键不是自增的，要把主键较大和较小的在不同数据页进行切换位置，为了保证下一个数据页的主键值比上一个数据页的主键值都小
主键索引：把每个数据页的页号和最小主键组成一个索引目录
索引的存储物理结构，跟数据页一样的结构，组成一颗B+树
font color=\"#ff00ff\
更新语句：三大核心问题1，索引不能太多，更新的时候维护很多的索引树2，可能会涉及到锁等待，死锁问题3，可能涉及到mysql连接池，写redo log文件
索引涉及考虑的因素：第一条:font color=\"#ff00ff\
为什么说B+树比B树更适合数据库索引？1、 B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。2、B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。3、由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以font color=\"#ff00ff\
假如一行数据是1KB，也就是说一页可以存放16行数据。然后因为非叶子节点的结构是：“页指针+键值”，我们假设主键ID为bigint类型，长度为8字节（byte），而指针大小在InnoDB源码中设置为6字节（byte），这样一共14字节（byte），因为一个页可以存放16k个byte，所以一个页可以存放的指针个数为16384/14=1170个。因此一个两层的B+树可以存放的数据行的个数为：1170*16=18720（行）
在InnoDB中一个3层B+树最多大概可以存放多少行数数据？？
索引
1，从磁盘把数据读出来，从磁盘数据读到内存就是IO成本，而且是一页一页读，读一页成本约定为1
2，数据运算，验证是否符合搜索条件、排序，都是消耗cpu资源的，属于cpu成本，约定读取和检测一条数据成本是0.2
拿到rows和data_length（聚簇索引的字节数）
data_length/1024就是kb为单位的大小，在除以16KB==页数
show table status like \"表名\"
总成本=IO成本（页数*1）+cpu成本（rows*0.2）
全表扫描成本计算
运行sql的成本
select * from t1 join t2 on t1.x1=t2.x1 and t1.id=1
select t1表中id=1的那行数据的各个字段的常量值，t2.* from t1 join t2 on t1表里x1字段的常量值=t2.x1
mysql自动优化为：
select * from t1 where x1 in (select x2 from t2 where x3=xxx)
运行时，并不是，t1全表扫描，判断每条数据是否在子查询的结果集里
而是，全表扫描物化表去t1通过索引查询
对于t1表而言，只要在t2表里有符合t1.x1=t2.x2和t2.x3=xxx条件的数据就可以
semi join（半连接）
子查询优化
执行计划优化
执行计划个数
id:每个select对应一个id。复杂的查询会拆分出多个select（比如子查询），也就包含多条执行计划，与表没关系，id相同代表同一个select语句possible_keys：结合type使用，确定具体的索引可供选择key：在possible_keys：里实际选择的那个索引key_len：索引用到的字段的最大长度ref：与索引列等职匹配rows：大概会读取多少条数据filtered：经过搜索条件过滤后筛选出来的数据占表里数据的百分比
simple普通查询
primary代表主查询
subquery子查询
union合并查询
derived 子查询衍生物化临时表
select_type：查询类型
通过聚簇索引或者二级索引（唯一索引）+聚簇索引回表查询，常量级
const
普通二级索引，不是唯一索引，也很快
ref（二级索引等值匹配）
范围查询
rang
index（扫描二级索引叶子节点每条数据）
all
const后面加类似name is null
ref_or_null
type：索引访问方式
信息说明:比如查询过程中使用了临时表
Using index仅仅用到了一个二级索引
Using index condition用到了一个二级索引同时，加了条件过滤
Using where没用到索引或者用了索引，where里好几个条件
Using join buffer 多表关联，关联条件并不是索引，内部使用一种内存技术提升性能
Using filesort在order by时没用直接利用索引排序，需把所有数据放入临时磁盘文件。
Using temproxy在group by，union，distinct时如果没法直接利用索引，就会基于临时表来完成，涉及磁盘操作，性能极低。
extra说明
explain 参数说明
1.索引字段，没有设置NOT NULL，则需要加一个字节。2.定长字段：tinyiny占1个字节、int占4个字节、bitint占8个字节、date占3个字节、datetime占5个字节，char(n)占n个字符。3.变长字段：varchar(n)占n个字符+2个字节。4.不同的字符集，一个字符占用的字节数不同：latin1编码，每个字符占用一个字节gbk编码，每个字符占用两个字节utf8编码，每个字符占用三个字节utf8mb4编码，每个字符占用四个字节
name的字段类型是varchar(20)，字符编码是utf8，一个字符占用3个字节，那么key_len应该是 20*3=60，字符编码是utf8mb4，一个字符占用4个字节，那么key_len应该是 20*4=80若该列类型定义时允许NULL，其key_len还需要再加 1 bytes
key_len 用于表示本次查询中，所选择的索引长度有多少字节，通常我们可借此判断联合索引有多少列被选择了
字段占用长度
执行计划
查询最近登录的用户，发送推销短信
在测试环境单表5万条数据跑执行计划，即使是5万条数据，跑了10多秒
原因1，子查询的4561条数据物化成了一个临时表，临时表会落磁盘
原因2，针对users表做了个全表扫描5万条数据，扫描的过程中，每一条数据跟一个没有索引的临时表进行了join操作（相当于全表扫描）
mysql生成计划时，自动把普通的in子句，优化成了基于semi join进行in+子查询的操作
表示每条数据，不需要跟临时表所有记录join上，只要找到匹配的就返回了。mysql自己做的优化
半连接导致了大量的无索引的全表扫描
semi join(半连接)：
为什么会做join操作？
问题：
关掉半连接set optimizer_switch='semijoin=off'
提升几十倍，编程100ms，反而他自己的半连接优化导致问题，正常的方式基于主键索引去执行，性能提高
这样并没有在进行semi jion优化，而是正常的用了子查询，主查询也是基于索引去执行的，这样性能就一下提升到几百毫秒了
子主题
想办法避免他全表扫描，一定要让他用索引，用索引才是王道，是最重要的
生产不能关闭半连接：
解决：
1，千万级用户表
大量的慢查询导致每个数据库连接执行一个慢查询要耗费很久，同时连接变多，报警数据的连接突然暴增
接着数据库的连接全部打满，导致数据库没法处理新的查询，查询发送到数据库直接阻塞后超时了，商品系统濒临崩溃
大量慢查询耗尽数据库的连接资源，用户也就没法查询和筛选电商网站的商品了
一个查询要执行几十秒的sql
问题：
由于连接资源被占用，大量的查询变成了慢查询
每分钟慢查询超过了10W+
监控情况：
商品表大致有几千万数据
用户在电商网站上根据商品的品类以及子类进行筛选
索引也有:
功能
按理说，用上索引的话，会很快，绝对不会超过1S
结果没用索引，select_type是primary，走的是主键索引，而且extra显示：Using where
本质上，主键聚簇索引上进行扫描，一遍扫描，一遍还用where条件的两个字段去筛选，必要耗时长
可见这个sql语句是没有用到索引，explain 查看执行计划
1，首先接近亿级数据量的大表，二级索引也是比较大的
2，mysql觉得从二级索引查到符合where条件的数据，接着还得回表，回表之前，还得做完order 和limit的操作，查出来数据太多，mysql判定这一方式不好
3，mysql认为按照顺序扫描聚簇索引，拿到10条符合where条件的数据的速度应该很快，可能比使用二级索引
本来走聚簇索引，是很快会拿到数据的，但是，导致查不到任何商品数据，等于接近几千万的商品表进行了全表扫描，正因如此，出现十几秒的慢查询
为什么mysql默认会选择对主键聚簇索引进行扫描
分析：
select * from products font color=\"#f44336\
明显提升，控制在100ms以内
解决办法：
2，几千万商品系统大量慢查询
评论数据，做了分库分表，单表数据量控制在百万级别
每个商品的评论都放在一个库的一张表里，确保用户在分页查询评论时，一般直接从一个库的一张表里获取数据
有些热门商品评论可达几十万条，某些用户就喜欢一页一页翻，有时候还会分页跳转，这就涉及深分页问题
前提
select * from comments where product_id='xx' and is_good_comment='1' (表示只看好评)font color=\"#ff00ff\
sql语句
也就是说每一条评论都会回表，取出来is_good_comment字段，做对比
导致几十万的评论回表操作，虽然根据id在聚簇索引里快速查询，但是架不住每条都回表
并且还得基于临时磁盘文件进行倒序排序，有的耗时很久，还得进行limit
分析
几十万次回表查询，还有几十万条数据的磁盘文件排序，sql每次基本要跑1-2s
导致问题
这个思路，反而要跟第二个案例反过来，这个案例就不应该走索引，再回表了
这个案例，因为product_id和is_good_comment不是联合索引，所以会出现大量的回表操作，耗时极高
子查询反而会使用primary聚簇索引，根据id排序进行扫描，扫描时，只要有100000+20条符合条件的数据，此时就可以根据limit 提取5001页的20条数据
接着对着20条数据回表，按照id去聚簇索引里查找一下完成数据
优化后，减少几百毫秒
结论，根据product_id的二级索引查找，反而出现10几万次回表查询，所以二级索引查找方式不适合
改造sql：
解决问题
有些商品比较热门，评论高达几十万，分页查询涉及到深分页问题，如果只查看好评，且好评字段没有索引的情况下，会对几十万条评论都会回表查询，耗时极高，所以干脆想办法，不走2级索引，而是直接基于主键聚簇索引去扫描。反而更快
总结：
查询sql本身不会发生慢查询问题
比如磁盘io负载特别高，也就是每秒执行大量的高负载随机io，还有就是cpu、负载过高也会导致慢查询
应该排查一下服务器的负载，尤其看磁盘，网络和cpu的负载
是生产服务器负载太高导致
比如灌入大量数据，最好在凌晨低峰期灌入，别影响线上系统运行
结果发现负载都没问题，用sql调优利器，profiling
通过profiling工具，发现他的sending Data耗时严重，几乎1S时间，占据了sql执行耗时的99%
sendingData：表示select语句把数据读出来，同时发送给客户端
show engine innodb status
大量的事务，mvcc在构建undo多版本快照链条，此时history list length值会很高，是一些长事务运行时间长导致
发现长事务是一下清理了上千万的数据，居然开了一个事务，导致事务一直在运行
查询的时候，可能会把上千万标记为已删除的数据都扫描一遍，因为readview机制数据没有真正删除，只是标记为删除
查询的时候会根据readview判断哪些数据是你可见的，以及可见的数据版本是哪个版本。
所以会读到所有标记为删除的数据就会继续扫描下去，出现千万级数据的扫描，造成慢查询
用一个命令：
sending Data：
问题分析：
直接kill掉正在删除的千万级数据的长事务，大量的数据清理应该全部放在凌晨执行，所以查询很少
4，千万级数据删除，导致慢查询
span style=\"mso-spacerun:'yes';font-family:'Times New Roman';mso-fareast-font-family:宋体;font-size:10.5000pt;mso-font-kerning:1.0000pt;\"SQL调优案例
当对存在的行进行锁的时候(主键)，mysql就只有行锁。当对未存在的行进行锁的时候(即使条件为主键)，mysql是会锁住一段范围（有gap锁） 锁住的范围为：(无穷小或小于表中锁住id的最大值，无穷大或大于表中锁住id的最小值)
1，不同表相同记录行锁冲突，事务A和事务B操作两张表，但是顺序颠倒
2，font color=\"#f15a23\
update masg set message =' 订单' where token>'aaa'
delete from msg where id>1;
3，font color=\"#f15a23\
死锁成因
1）以固定的顺序访问表和行。比如对第2节两个job批量更新的情形，简单方法是对id列表先排序，后执行，这样就避免了交叉等待锁的情形；又比如对于3.1节的情形，将两个事务的sql顺序调整为一致，也能避免死锁。2）大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。3）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。4）降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。5）为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。
如何尽可能避免死锁
死锁
（1）普通锁，本质是串行执行；（2）读写锁，可以实现读读并发；（3）数据多版本，可以实现读写并发；
提高并发的演进思路
将修改行为先写到redo日志里（此时变成了顺序写），再定期将数据刷到磁盘上
redo日志用于保障，已提交事务的ACID特性
redo日志
数据库事务未提交时，会将事务修改数据的镜像（即修改前的旧版本）存放到undo日志里，当事务回滚时，或者数据库奔溃时，可以利用undo日志，即旧版本数据，撤销未提交事务对数据库产生的影响
undo日志用于保障，未提交事务不会对数据库的ACID特性产生影响
undo日志
事务读取到的数据，要么是事务开始前就已经存在的数据，要么是事务自身插入或者修改的数据
span style=\
快照读（Snapshot Read），这种一致性不加锁的读
为何InnoDB能够支撑如此之高的并发
一种特殊的表级别锁（table-level lock），专门针对事务插入AUTO_INCREMENT类型的列
如果一个事务正在往表中插入记录，所有其他事务的插入必须等待
（4）事务A再select：select * from t where id>3;
不可能查询到5的记录，在RR的隔离级别下，不可能读取到还未提交事务生成的数据
使用的是插入意向锁
并不会阻塞事务B
这里，便不再使用自增锁
假设不是自增列
案例分析
如果插入位置冲突，多个事务会阻塞，以保证数据一致性
（1）自增锁（Auto-inc Locks）
（1）事务拿到某一行记录的共享S锁，才可以读取这一行
（2）事务拿到某一行记录的排它X锁，才可以修改或者删除这一行
（1）多个事务可以拿到一把S锁，读读可以并行
（2）而只有一个事务可以拿到X锁，写写/读写必须互斥
标准的行级锁
共享/排它锁的潜在问题是，不能充分的并行，解决思路是数据多版本
（2）共享/排它锁(Shared and Exclusive Locks)
意向锁，是一个表级别的锁，弱锁，仅仅表明意向
意向锁之间并不相互互斥，而是可以并行
会与共享锁/排它锁互斥
事务要获得某些行的S锁，必须先获得表的IS锁事务要获得某些行的X锁，必须先获得表的IX锁
（3）意向锁(Intention Locks)
插入意向锁，是间隙锁(Gap Locks)的一种
那对于数据的插入，是否还需要加这么强的锁，来实施互斥呢？
对已有数据行的修改与删除，必须加强互斥锁X锁
多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此，以提高插入并发
（4）插入意向锁(Insert Intention Locks)
封锁索引记录，对索引记录实施互斥，以保证数据一致性
select * from t where id=1 for update;
阻止其他事务插入，更新，删除id=1的这一行
（5）记录锁(Record Locks)
索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围
select * from t where id between 8 and 15 for update;
会封锁区间，以阻止其他事务比如id=10的记录插入
主要目的，就是为了防止其他事务在间隔中插入数据，以导致“不可重复读”
（6）间隙锁(Gap Locks)
记录锁与间隙锁的组合
临键锁会封锁索引记录本身，以及索引记录之前的区间。
在RR下有效，防止幻读；
封锁范围，既包含索引记录，又包含索引区间
如果一个会话占有了索引记录R的共享/排他锁，其他会话不能立刻在R之前的区间插入新的索引记录
（7）临键锁(Next-key Locks)
（1）InnoDB使用共享锁，可以提高读读并发；
（2）为了保证数据强一致，InnoDB使用强互斥锁，保证同一行记录修改与删除的串行性；
（3）InnoDB使用插入意向锁，可以提高插入并发；
InnoDB的细粒度锁，是实现在索引记录上的，如果查询没有命中索引，也将退化为表锁
（1）如果表定义了PK，则PK就是聚集索引；（2）如果表没有定义PK，则第一个非空unique列是聚集索引；（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；
小结
七种类型的锁
mysql单机配置是8核16G，每秒抗4000读写请求
此时就要搭建主从复制的mysql架构
假设记得真是业务已经达到了，2500读+2500写，及每秒5000读写请求
此时要搭建2个从节点
随着业务增多，请求越来越多，读请求可能达到6000，此时一个从节点抗不下来
当主库宕机时，通过中间件（比如mycat）把从库切换为主库，实现高可用
1，主库生成binlog日志
2，从库有一个io线程，从库主动请求出库
3，主库有一个io dump线程，通过tcp连接传输binlog日志到从库的io线程
4，从库把binlog日志写入自己本地的relay日志，进行日志重做，达到数据同步
同步机制
1，确保主从的server-id是不同的
2，主库必须打开binlog功能
首先主库上创建一个主从复制的账号
做mysqldump时，不允许操作主库，否则数据复制到从库会不一致
一定要凌晨执行
备份出一个backup.sql文件
用mysqldump工具在主库做一个全量备份
从库执行backup.log文件
新加入从库操作
可以用mycat或者sharding-sphere之类的中间件实现
实现读写分离
异步同步机制的问题：主库宕机，可能造成从库数据丢失，主从不一致
意思是确binlog日志复制到从库
然后才告诉客户端本次写入事务成功
可以保证数据不丢失
采取半同步复制方式
主库写入日志到binlog，等待binlog复制到从库，
主库先提交自己的本地事务，再等待从库给自己一个成功的响应
然后主库才响应给客户端提交事务成功
After-commit
主库把日志写入binlog，并且复制给从库
主库先等待从库响应，再提交本地事务
返回给客户端响应
mysql5.7默认方式
两种方式
在之前搭建好的异步复制的基础上，安装半同步机制插件就可以了
先在主库安装半同步复制插件，同时开启半同步复制功能
接着从库也安装这个插件，以及开启半同步复制功能
搭建半同步
从库也用多线程并行执行复制数据
减少主从同步延迟
解决丢失问题：
如何搭建主从同步
主从复制
1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；3. InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；5. Innodb不支持全文索引（全文检索一般使用倒排索引来实现。倒排索引和B+树一样也是一种索引结构），而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：font color=\"#ff00ff\
myisam和innodb的区别
mysql
0 条评论
回复 删除
下一页
