

首页  思维导图  详情



MySQL及事务

2020-05-22 15:05:25   0  举报





AI智能生成

MySQL及事务

MySQL

事务

软件开发

数据分析

学习笔记

模版推荐

作者其他创作

大纲/内容

事务

事物的基本性质

ACID

原子性Atomic

一致性Consistency

隔离性Isolation

当前事务会不会看到其他事务的结果，如果会，说明不满足隔离性

持久性Durability

并发事务带来的问题

脏读

读到了已经被修改、但是还没被提交的数据

不可重复读

两次读同一条数据中间时间，发生了一次提交，导致两次读到的数据不一样

幻读

两次读同一批数据的中间时间，发生了一次提交，导致读到的数据变少或者变多

修改丢失

对同一条数据发生的两次修改，后面的提交覆盖了前面的提交

事务解决方案

XA协议

实现方案

2PC

执行流程

准备->提交

协调者确认所有节点是否准备好->如果都准备好了，提交事务

3PC

优点

结构简单

缺点

1、性能不高：参与的节点都处于同步阻塞的状态

2、可靠性问题：如果协调者出现异常，节点会被长时间锁定

3、数据一致性问题：如果在提交阶段，有部分节点如果未收到协调者的提交信息，会导致一致性的问题<br>

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

支持事务

默认的隔离等级

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可以有多一倍的空间<br>

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个插入后执行一次提交

 Collect

Get Started

MYSQL(初级)

 Collect

Get Started

整体及运营流程

 Collect

Get Started

MySQL事务大纲

 Collect

Get Started

MySQL事务隔离级别





0 条评论

下一页