MySQL及事务
2020-05-22 15:05:25 0 举报AI智能生成
MySQL及事务
作者其他创作
大纲/内容
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
默认的隔离等级
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 条评论
回复 删除
下一页
