MySQL及事务
2020-05-22 15:05:25 0 举报
AI智能生成
MySQL及事务
作者其他创作
大纲/内容
事务
事物的基本性质
ACID
原子性Atomic
一致性Consistency
隔离性Isolation
当前事务会不会看到其他事务的结果,如果会,说明不满足隔离性
持久性Durability
并发事务带来的问题
脏读
读到了已经被修改、但是还没被提交的数据
不可重复读
两次读同一条数据中间时间,发生了一次提交,导致两次读到的数据不一样
幻读
两次读同一批数据的中间时间,发生了一次提交,导致读到的数据变少或者变多
修改丢失
对同一条数据发生的两次修改,后面的提交覆盖了前面的提交
事务解决方案
XA协议
实现方案
2PC
执行流程
准备->提交
协调者确认所有节点是否准备好->如果都准备好了,提交事务
3PC
优点
结构简单
缺点
1、性能不高:参与的节点都处于同步阻塞的状态
2、可靠性问题:如果协调者出现异常,节点会被长时间锁定
3、数据一致性问题:如果在提交阶段,有部分节点如果未收到协调者的提交信息,会导致一致性的问题
TCC(Try-Confirm-Cancel)
为了解决什么问题:
XA事务会锁住所有相关的资源,形成长事务,对性能造成影响
执行流程:
1、Try:用业务的try方法,尝试锁定/冻结资源
例如订单状态改为updating、库存增加冻结库存数字段,经过try之后
try的目的是为confirm保留需要的资源,满足事务的Isolation隔离性,可以理解为隔离资源
2、Confirm:用业务的confirm方法,借助TCC框架,如果各个服务都try成功了,正式执行最终逻辑
例如订单状态由updating改为pay
如果try成功,会一直重试confirm方法直到confirm成功
3、Cancel:TCC框架感知到某些服务try失败了,用业务的cancel方法,对已经执行的try进行回滚
如果try失败,会一直重试cancel方法直到cancel方法成功
优点
1、性能提升:粒度较小
2、保证最终一致性:confirm和cancel保证幂等
3、可靠性:集群保证不会出现XA协调者宕机的问题
缺点
1、对代码的侵入性很大,需要业务实现try、confirm、cancel方法
2、需要引入TCC框架
3、执行过程需要日志,对性能有一定的影响
TCC框架
tcc transaction
可靠消息最终一致性
执行流程
1、服务A投递下游参数消息a至mq,a消息状态为未确认
2、服务A提交本地,如果失败回滚本地,并且通知mq删除a消息;如果成功通知mq
3、mq发送a消息到下一服务B,B开启新的本地事务:[提交+返回成功mq],保证mq发失败,B的提交也能够进行回滚
事务的隔离等级
READ-UNCOMMITED
最低等级
脏读、不可重复读、幻读都可能发生
允许读取未提交的数据
READ-COMMITED
允许读取已提交的数据
避免了脏读,其他问题还存在
REPEATABLE-READ
保证对同一字段的重复读取是一样的
避免了不可重复读、脏读;仍然存在幻读的问题
SERIALIZABLE
最高等级
串行化操作,事务依次进行,完全符合ACID
Cardinality
含义:
MySQL预估的某一列中,不重复的值的数量
来源:
MySQL通过采样预估
更新的时机:
改动的数据超过了1/16
改动的次数超过了2*10^9次
优化方向
1、减少数据访问
由于硬盘响应最慢,最先优化
2、减少返回数据
网络速度其次
3、减少交互次数
网络速度其次
4、减少CPU的使用量
计算逻辑放在客户端处理,不要放到数据库服务器
5、增加硬件
最快速、最临时、成本最高的方式,加强配置
引擎
MyISAM
不支持事务
只支持表锁
InnoDB
支持事务
XA
默认的隔离等级
REPEATABLE-READ
MySQL定义的不可重复读,是包含了幻读的概念的
因此,在MySQL默认的隔离等级REPEATABLE-READ下,是满足SQL标准定义的SERIALIZABLE等级的
锁机制
Record Lock
锁住单条记录,为了解决不可重复读问题
Gap Lock
锁住一段范围,为了解决幻读
Next Kty Lock
结合以上2种锁,同时解决了不可重复读和幻读
MVCC(Multi Version Concurrent Control)
多版本并发控制
索引
B+Tree
B指的是平衡(balance)
哈希索引
适用于比较多的单条数据查找
联合索引
覆盖索引
辅助索引查出来的结果已经含有了需要的所有字段,不需要再通过聚集索引回查
是完美的联合索引
唯一索引
全文索引
倒排索引
优化手段
1、字段
字段不要超过20个
如果超过,可以按访问频率垂直拆分成2个表,让缓存命中率提高
评估预留字段的必要性
因为预留字段的类型无法确定,未来用上的可能性不大,即使能够用上,在修改预留字段的时候也会造成锁表,带来风险
避免存图片等需要大IO的数据
优先使用无符号
比如无符号int可以有多一倍的空间
2、索引
通常在查询条件中出现的列加上索引
评估索引效果的指标
Cardinality/n_row_in_table
正常指标约为1,如果非常小,需要考虑是否去掉该索引
索引对插入、删除、修改的性能影响大,单表索引不要超过5个
3、SQL
(1)使用Explain来分析sql语句,主要关注3个字段
type
全表扫描
all、index
需要进行优化
用上了索引
range
ref
key
查询中实际使用到的索引,如果没用到、为null,需要优化
rows
可以理解为为了达成这次查询,扫描的总行数
(2)select具体字段,不要select *
(3)按需求取数:带上范围查询、或者分页查询、或者limit需要的数量,不要返回全表信息
(4)
4、拆分
前提条件:
整形为主的表千万级才考虑
字符为主的表五百万级才考虑
方式:
垂直拆分
优点
简单
缺点
增加了额外的join、拆分后主键的冗余
水平拆分(不太推荐)
优点
缺点
需要中间件(如sharding-jdbc)协助,增加了复杂性
分布式id生成方案
数据库自增
优点:简单
缺点:1、受到数据库id发放限制;2、安全性,可以遍历id来发起请求
Snow-flake算法
时间戳+机器id+序号
读写分离
5、引擎
查询、分析多的数据库(OLAP)
使用MyISAM
在线事务处理(OLTP)
使用InnoDB
6、系统调优参数
innodb_buffer_pool_size:缓存池大小
影响性能的最重要因素,设置为内存的70~80%
实际优化案例
1、excel批量导入的优化
原方案是在循环中提交,每次读取到一行就自动提交到MySQL
优化:先把数据10个一组,开启一个事务收集10个插入后执行一次提交
0 条评论
下一页
为你推荐
查看更多