MySQL的物理存储结构
隐##1.物理存储结构
1.1 表空间
MySQL支持两类表空间: 共享、独立
不同版本的差异:
5.5版本:
默认共享表空间管理模式.包含:数据字典\undo\tmp\用户表数据和索引
5.6版本:
默认独立表空间.
包含:数据字典\undo\tmp,将用户数据和索引独立,每个表单独存储。
一个表一个ibd文件,存储数据行和索引信息。
5.7版本:
默认独立表空间.
包含:数据字典\undo,tmp独立,将用户数据和索引独立,每个表单独存储
8.0版本:
默认独立表空间.
数据字典取消掉,undo, tmp独立将 用户数据和索引独立,每个表单独存储
1.2 功能名词介绍
磁盘结构相关:
transaction 事务
undo : ibdata1 #回滚日志(保留事务之前的状态)
tmp : ibtmp1 #临时表空间磁盘位置,存储临时表
redo : ib_logfile0-N #重做日志
ibd : t1.ibd #表空间数据文件。表的数据行和索引
frm #存储表的列信息
内存结构:
Innodb Buffer Poll #数据缓冲区池(环境中给70-80%物理内存)《缓冲数据和索引页》《顺序IO存储空间》
(三个不同磁盘上,有三个不同无顺序表文件
经常被访问(热数据)内存数据页,放到缓冲区,以后访问不用加载到磁盘
通过Innodb Buffer Poll 功能把数据作为顺序IO
缺点 :关机慢(要将数据放到缓冲区),启动慢
log buffer #重做日志缓冲区
LSN #日志序列号 #相当于日志版本号
Rrx_id #事务ID号
checkpoint #检查点
1.3事务
1.3.1 什么是事务?
将多条DMl(标准的事务语句),放在一个组中运行,要么全成功要么全失败。
1.3.2 事务ACID特性atomicity, consistency, isolation, and durability.
Atomic:原子性 :每一个事务都是一个完整整体,不可再分性.要么全执行成功要么全失败.
Consistent:一致性 :在事务前,中,后,保证事务操作的数据前后一致.
Isolated:隔离性:多个事务之间,所做事务互不干扰,不能同时更新同一行数据.
Durable:持久性 :事务完成之后,所涉及到的数据,必须永久有效(落地)
1.3.3 事务的生命周期管理
(1) 开启一个事务
begin / start transaction;
(2) 标准的事务语句
insert
update
delete
(3) 结束事务
commit;#提交事务
rollbackup; #回滚事务 #只能提交之前做。
非标准的事务生命周期
1) 自动提交机制(autocommit)
只要是DML语句,自动添加
1.begin子句会自动添加
2.因为 MySQL5.6版本后,每一条执行完成之后都会自动提交
mysql> select @@autocommit;#查看是否开启这项功能(自动提交)
永久关闭参数:@@autocommit功能
vim /etc/my.cnf
图片.png
说明:默认情况下,开启事务时不加begin,逐条自动提交.手工开启begin命令,按照正常事务工作过程.
2)隐式提交(当打开一个事务,操作后没有进行提交,进行DDL语句操作后,开始执行的操作会自动提交,无法回滚)
用于隐式提交的 SQL 语句:
SET AUTOCOMMIT = 1
导致提交的非事务语句:
DDL语句: (ALTER、CREATE 和 DROP)
DCL语句: (GRANT、REVOKE 和 SET PASSWORD)
锁定语句:(LOCK TABLES 和 UNLOCK TABLES)
导致隐式提交的语句示例:
TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE
3)隐式回滚
原因:
会话断开
数据库重启
死锁
1.3.4 事务底层的工作过程
画图说明.
(1) redo
分布:
内存: log buffer
磁盘: ib_ logfile0~N
功能:
>1.保存内存数据页的变化
>2. commit时, 实现事务的快速持久化的特性:量少, 顺序I0
>3.宕机时,通过redo实现重做事务,将数据库恢复到宕机之前的状态.
我们由把这步称之为ACSR中的"前滚"操作
2)undo 回滚日志
分布: 默认 ibdata1, 5.7 开始使用可以独立undo,8.0后自动独立
功能:
1.保存当前事务操作的反操作
2.在执行rollback命令时, undo提供回滚操作,实现原子的特性。
3.宕机时,ACSR过程中提供回滚操作(将没有commit标记的)
1.3.5 锁 及隔离级别主要保证隔离性
1)锁:
S: 共享锁,读锁
X: 排它锁,写锁
IS: 意向S(想要有哪个锁)
IX: 意向X
兼容
S-S:
S-IS:
不兼容
X和任何锁都不兼容
2)锁的细分
TX: 表锁 DDL
RX: 行锁 DML
GAP LOCK X: 间隙锁 特殊DML
Next LOCK X: 下一键锁定
3) 隔离级别(transaction_isolation)
RU: 读未提交----READ-UNCOMMITTED
模拟:
session A
mysql> begin;
mysql> use world
mysql> delete from city where id=1000;
session B
mysql> begin;
mysql> use world
mysql> select *from city where id=1000;
**会产生的问题: **
- 脏读
- 不可重复读现象
- 幻读
RC: 读已提交----READ-COMMITTED
- 不可重复读现象
- 幻读
RR: 可重复读----REPEATABLE-READ
- 幻读
说明:- RR级别+ GAP+ Next lock(GAP+RX)有效防止幻读现象
- 通过MVCC,多版本并发控制中,一致性快照读技术,解决了不可重复读问题.
SR: 串行
2. InnoDB 存储引擎核心参数 *****
mysql> select @@innodb_data_file_path;
mysql> select @@innodb_file_per_table;
mysql> select @@innodb_buffer_pool_size; #不要超过80%物理内存 ----> Out of memory
mysql> select @@innodb_log_buffer_size;
mysql> select @@innodb_log_file_size;
mysql> select @@innodb_log_file_in_group;
mysql> select @@innodb_flush_log_at_trx_commit; # 双1标准之一.控制redo刷写的策略.
0 每秒钟刷写redo到磁盘.
1 每次事务提交,理解刷写redo到磁盘
2 每次事务提交,立即写日志到OS cache中,然后每秒钟刷写到磁盘.
mysql> select @@innodb_flush_method; 控制(buffer脏页,redo buffer日志)刷写方式建议设置:
O_DIRECT : 数据页刷写磁盘直接穿过文件系统缓存,redo 刷写时,先写os cache,再写到磁盘。