数据库MYSQL学习系列三

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:
       数据库MYSQL学习系列三

三.MYSQL事务与存储引擎
3.1-数据库事务
什么是事务
一系列有序的数据库操作:
o要么全部成功
o要么全部回退到操作前的状态
o中间状态对其他连接不可见
事务的基本操作: | 基本操作 | 说明 | | :------------- | :------------- | | start transaction | 开始事务 | | commit | 提交(全部完成) | | rollback | 回滚(回到初始状态) |
-- 开启一个事务start transaction;-- 或者使用(非标准sql)begin;insert into t values (1, 1, 1);-- 事务结束,插入成功commit;
begin;insert into t values (2, 1, 1);insert into t values (3, 1, 1);insert into t values (4, 1, 1);-- 事务结束,没有插入数据rollback;
begin;
insert into t values (1, 1, 1);
savepoint a1;insert into t values (2, 1, 1);-- 回滚到指定的保存点rollback to a1;commit;
自动提交
autocommit可以在session级别设置
每个DML操作都自动提交
DDL永远都是自动提交,无法通过rollback回滚
事务的四个基本属性(ACID)
原子性(Atomicity)
一致性(Consistency)
隔离性(Isolation)
持久性(Durability)
事务的原子性
包含在事务中的操作要么全部被执行,要么都不执行
中途数据库或应用发生异常,未提交的事务都应该被回滚
事务的一致性
数据的正确性,合理性,完整性
数据一致性应该符合应用需要规则:
o余额不能是负数
o交易对象必须先有账号
o用户账号不能重复
事务的结果需要满足数据的一致性约束
事物的持久性
提交完成的事务对数据库的影响必须是永久性的
o数据库异常不会丢失事务更新
o通常认为成功写入磁盘的数据即为持久化成功
事务的持久化的实现
数据文件持久化
o随机同步刷新(慢)
事务日志持久化与实例恢复
o顺序同步刷新(快) -> 事务日志
o随机异步刷新 -> 磁盘
o事务日志 -> 磁盘(实例恢复)
事务的隔离性
数据库事务在提交完成前,中间的任何数据变化对其他的事务都是不可见的。
数据库隔离现象
隔离现象 描述
脏读(Dirty Read) 事务B读到事务A尚未提交的数据变更
不可重复读(NonRepeatable Read) 事务B读取前后两次读取一条记录之间该记录被事务A修改并提交,于是事务B读到了不一样的结果
幻读(Phantom Read) 事务B按条件匹配到了若干行记录并修改。但是由于修改过程中事务A新插入了符合条件记录,导致B更新完成后发现仍有符合条件却未被更新的记录。
数据库隔离等级
隔离等级 脏读 不可重复读 幻读
未提交读 可能 可能 可能
已提交读 不可能 可能 可能
可重复读 不可能 不可能 可能
可串行化读 不可能 不可能 不可能
MySQL的事务隔离级别
InnoDB默认标记为可重复读
InnoDB并不是标准定义上的课重复读
InnoDB默认在可重复读的基础上避免幻读
MySQL事务隔离级别设置
可在global/session/下个事务,级别分别进行设置
建议使用Read committed(同Oracle)
或者建议使用默认的Repeatable read
set tx_isolation = ''-- 设置隔离级别
事务与并发写
某个正在更新的记录再提交或回滚前不能被其他事务同时更新
事务回滚的实现
回滚段(rollback segment)与数据前像
3.2-存储引擎概述
MySQL程序层次架构

MySQL存储引擎
有多种可选方案,可插拔,可修改存储引擎
基于表选择使用何种存储引擎
主要存储引擎
存储引擎 常用度 支持事务
InnoDB 主要,推荐 是
MyISAM 古老,偶尔有用,系统表 否
MEMORY 偶尔临时表有用,纯内存 否
BLACKHOLE 不用来存放数据,个别特殊用处 否
TokuDB 新颖,个别特殊场景有奇效 是
Cluster 新颖,分布式,内存,线上不要用 是
InnoDB存储引擎
索引组织表
支持事务
支持行级锁
数据块缓存
日志持久化
稳定可靠,性能好,线上尽量使用InnoDB
MyISAM存储引擎
堆表
不支持事务
只维护索引缓存池,表数据缓存交给操作系统
锁粒度较大
数据文件可以直接拷贝,偶尔可能会用上
不建议线上业务数据使用
MWMORY存储引擎
数据全内存存放,无法持久化
性能较高
不支持事务
适合偶尔作为临时表使用
create temporary table tmp (id int) engine = memory ;
BLACKHOLE存储引擎
数据不作任何存储
利用MySQL Replicate,充当日志服务器
在MySQL Replicate环境中充当代理主
TokuDB
分形树存储结构
支持事务
行锁
压缩效率较高
适合大批量insert的场景
MySQL Cluster
多主分布式集群
数据节点间冗余,高可用
支持事务
设计上易于扩展
面向未来,线上慎用
改变表的存储引擎
alter table m ENGINE=innodb;
3.3-InnoDB存储引擎
InnoDB存储引擎体系架构

InnoDB相关的磁盘文件
文件 名称 数量 位置
系统表空间 ibdata1 一个实例一个 innodb_data_home_dir
日志文件 ib_logfile0/1 一个实例两个(可配置) innodb_log_group_home_dir
表定义文件 表名.frm 每张表一个 Schema目录下
表数据文件 表名.ibd 如果innodb_file_per_table = 1, 则每张表一个 Schema目录下
InnoDB系统表空间文件
ibdata1里存放了什么:
o回滚段
o所有InnoDB表元数据信息
oDouble Write, Insert buffer dump等等....
自动扩展机制
InnoDB与磁盘文件有关的参数
参数 样例值 备注
innodb_data_home_dir /data/mysql/node_1 数据主目录
innodb_log_group_home_dir /data/mysql/node_1 一般同上
innodb_data_file_path ibdata1:512M:autoextned 请开启autoextned
innodb_autoextend_increment 128 MB,勿太大或太小
innodb_file_per_table 1 强烈建议开启
innodb_log_file_size 100MB 性能相关
innodb_log_files_in_group 2 性能相关
InnoDB数据文件存储结构
索引组织表(聚簇表)
根据表逻辑主键排序
数据节点每页16K
根据主键寻址速度很快
主键值递增的insert插入效率较好
主键值随机insert插入效率差
因此,InnoDB表必须指定主键,建议使用自增数字
InnoDB数据块缓存池
数据的读写需要经过缓存
数据以整页(16K)为单位读取到缓存中
缓存中的数据以LRU策略换出
IO效率高,性能好
InnoDB Buffer Pool相关参数
参数 样例值 备注
innodb_buffer_pool_size 10G 根据总物理内存设置
InnoDB数据持久化与事务日志
事务日志实时持久化
内存变化数据(脏数据)增量异步刷出到磁盘
实例故障靠重放日志恢复
性能好,可靠,恢复快
InnoDB日志持久化相关参数
参数 样例值 备注
innodb_flush_log_at_trx_commit 1 可选:0:每隔1s写入并持久化一次日志。1:每次commit都写入并持久化日志。2:每次提交日志写到内存,每1s持久化一次
InnoDB行级锁
写不阻塞读
不同行间的写互相不阻塞
并发性能好
InnoDB与事务ACID
事务ACID特性完整支持
o回滚段失败回滚
o支持主外键约束
o事务版本+回滚段=MVCC
o事务日志持久化
默认可重复读隔离级别,可以调整
3.4-InnoDB事务锁
什么是计算机程序锁
计算机程序锁
o控制对共享资源进行并发访问
o保护数据的完整性和一致性
数据库中的锁
分为两个大类
lock latch/mutex
对象 事务 线程
保护 数据库逻辑内容 内存数据结构
持续时间 事务过程中 临界资源争抢
我们主要关心的是事务锁
数据库事务并发
对同一行记录的修改必须串行化
事务锁粒度
行锁
oInnoDB, Oracle
页锁
oSQL Server
表锁
oMyISAM, Memory
锁升级
InnoDB存储引擎中的锁模式与粒度
四种基本锁模式
o共享锁(S) - 读锁 - 行锁
o排他锁(X) - 写锁 - 行锁
o意向共享锁(IS) - 表级
o意向排他锁(IX) - 表级
意向锁
o意向锁总是自动先加,并且意向锁自动加自动释放
o意向锁提示数据库这个session将要在接下来施加何种锁
o意向锁和X/S锁级别不同,除了阻塞全表级别的X/S锁外其他任何锁
InnoDB锁模式互斥

数据库加锁操作
一般的select语句不加任何锁,也不会被任何事物锁阻塞
o读的隔离性由MVCC确保
S锁
o手动:select from tb_test lock in share mode;
o自动:insert前
X锁
o手动:select 
from tb_test lock for update;
o自动:update,delete前
InnoDB行锁的实现
通过索引项加锁实现
o只有条件走索引才能实现行级锁
o索引上有重复值,可能锁住多个记录
o查询有多个索引可以走,可以对不同索引加锁
o是否对索引加锁实际上取决于MySQL执行计划
自增主键做条件更新,性能最好
没有索引的话会对整张表加锁。
InnoDB的gap lock
什么是幻读
gap lock消灭幻读
oInnoDB消灭幻读仅仅为了确保statement模式replicate的主从一致性
小心gap lock
自增主键做条件更新,性能最好
死锁

什么是死锁

oA、B两个事务,A先更新t1,同时B更新t2,A再更新t2,B再更新t1就发生了死锁。

死锁数据库自动解决

o数据库挑选冲突事务中回滚代价较小的事务回滚

死锁预防

o单表死锁可以根据批量更新里的更新条件排序
o可能冲突的跨表事务尽量避免并发
o尽量缩短事务长度
业务逻辑加锁

业务流程中的悲观锁

o任何的并发修改都有可能造成我们的业务逻辑最终的错误,在事务流程中一开始就加锁,最后释放

如何缩短锁的时间











本文转自去轻狂书生51CTO博客,原文链接:http://blog.51cto.com/8999a/2044630 ,如需转载请自行联系原作者


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2天前
|
缓存 算法 关系型数据库
Mysql(3)—数据库相关概念及工作原理
数据库是一个以某种有组织的方式存储的数据集合。它通常包括一个或多个不同的主题领域或用途的数据表。
17 3
Mysql(3)—数据库相关概念及工作原理
|
2天前
|
SQL JSON 关系型数据库
MySQL是一个广泛使用的开源关系型数据库管理系统,它有许多不同的版本
【10月更文挑战第3天】MySQL是一个广泛使用的开源关系型数据库管理系统,它有许多不同的版本
17 5
|
1天前
|
分布式计算 关系型数据库 MySQL
大数据-88 Spark 集群 案例学习 Spark Scala 案例 SuperWordCount 计算结果数据写入MySQL
大数据-88 Spark 集群 案例学习 Spark Scala 案例 SuperWordCount 计算结果数据写入MySQL
16 3
|
4天前
|
存储 SQL 关系型数据库
MySQL查询数据库锁表的SQL语句
MySQL查询数据库锁表的SQL语句
23 1
|
1天前
|
关系型数据库 MySQL 数据库
MySQL数据库基础(数据库操作,常用数据类型,表的操作)
MySQL数据库基础(数据库操作,常用数据类型,表的操作)
16 5
|
3天前
|
数据可视化 关系型数据库 MySQL
【IDEA】配置mysql环境并创建mysql数据库
【IDEA】配置mysql环境并创建mysql数据库
23 0
|
28天前
|
NoSQL 关系型数据库 MySQL
微服务架构下的数据库选择:MySQL、PostgreSQL 还是 NoSQL?
在微服务架构中,数据库的选择至关重要。不同类型的数据库适用于不同的需求和场景。在本文章中,我们将深入探讨传统的关系型数据库(如 MySQL 和 PostgreSQL)与现代 NoSQL 数据库的优劣势,并分析在微服务架构下的最佳实践。
|
6天前
|
存储 关系型数据库 MySQL
一个项目用5款数据库?MySQL、PostgreSQL、ClickHouse、MongoDB区别,适用场景
一个项目用5款数据库?MySQL、PostgreSQL、ClickHouse、MongoDB——特点、性能、扩展性、安全性、适用场景比较
|
5天前
|
SQL 关系型数据库 MySQL
|
25天前
|
存储 SQL 关系型数据库
MySQL的安装&数据库的简单操作
本文介绍了数据库的基本概念及MySQL的安装配置。首先解释了数据库、数据库管理系统和SQL的概念,接着详细描述了MySQL的安装步骤及其全局配置文件my.ini的调整方法。文章还介绍了如何启动MySQL服务,包括配置环境变量和使用命令行的方法。最后,详细说明了数据库的各种操作,如创建、选择和删除数据库的SQL语句,并提供了实际操作示例。
64 13
MySQL的安装&数据库的简单操作