Linux
Nginx,MySQL,PHP/Python

MySQL存储引擎及事务ACID

存储引擎

简介:相当于linux文件系统,只不过此文件系统强大
功能:数据读写,提高性能,数据安全,热备份,自动恢复故障,高可用方面支持等
存储引擎是作用在表上的,也就意味着,不同的表可以使用不同的存储引擎类型
MySQL存储引擎类型
  innodb,myisam,memory,csv,默认是innodb
  PerconaDB:默认是XtraDB
  MAriaDB:默认是innodb
其他的存储引擎支持
  TokuDB
  RocksDB
  MyRocks
以上三种存储引擎的共同点:压缩比较高,数据插入性能极高

innodb和myisam区别

innodb:支持事务,行级锁,支持csr自动故障恢复,支持热备(备份和恢复),MVCC多版本并发控制
myisam:不支持事务,温备,不支持csr
innodb属于mysql5.5版本开始默认的,提供可靠性和高性能
优点:
事务(Transaction)
mvcc(Multi-Version Concurrency Control多版本并发控制)
行级锁(Row-level Lock)
ACSR(Auto Crash Safey Recovery)自动故障安全恢复
支持热备份(Hot Backup)
Replication:Group commit,GTID(Global Thransaction ID),多线程(Multi-Threads-SQL)

查看

查看默认存储引擎
3306 [(none)]>select @@default_storage_engine;
 +--------------------------+
 | @@default_storage_engine |
 +--------------------------+
 | InnoDB                   |
 +--------------------------+
 1 row in set (0.00 sec)
永久修改
vim /etc/my.cnf
default_storage_engine=myisam 
查看库中所有表的引擎
3306 [(none)]>show table status from world;
修改一个表的引擎
3306 [(none)]>alter table world.a engine=myisam;
 Query OK, 9 rows affected (0.32 sec)
 Records: 9  Duplicates: 0  Warnings: 0
生成修改所有表的引擎的语句
3306 [information_schema]>select concat('alter table ',table_schema,'.',table_name,' ','engine=innodb') from
information_schema.tables into outfile '/tmp/engine.sh';

Innodb存储引擎的物理存储结构

ibdata1:系统数据字典信息,undo表空间数据
ib_logfile0~1:redo日志文件,事务日志文件
ibtmp1:临时表空间磁盘位置,存储临时表
frm:存储表的列信息
ibd:表的数据行和索引

共享表空间

共享表空间:5.5版本推出,也是默认管理模式
需要将所有数据存储到同一个表空间中,管理表复杂
5.6版本以后,共享表空间保留,只用来存储数据字典信息,undo,临时表
5.7版本,临时表被独立出来
8.0版本,undo也被独立出来

设置:共享表空间设置(在搭建mysql时,初始化之前设置带参数文件中)
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
查看当前设置
3306 [information_schema]>select @@innodb_data_file_path;
+-------------------------+
| @@innodb_data_file_path |
+-------------------------+
| ibdata1:12M:autoextend  |
+-------------------------+
1 row in set (0.00 sec)
3306 [information_schema]>show variables like '%extend%';
 +-----------------------------+-------+
 | Variable_name               | Value |
 +-----------------------------+-------+
 | innodb_autoextend_increment | 64    |
 +-----------------------------+-------+
 1 row in set (0.00 sec)

独立表空间

从5.6开始默认表空间不在使用共享表空间,替换为独立表空间
主要存储的是用户数据
存储特点为:一个表一个ibd文件,存储数据行和索引信息
Redo Log:ib_logfile0  ib_logfile1      redo日志
Undo Log:ibdata1  ibdata2  回滚日志
ibtmp1临时表,在做join  union操作时产生临时数据存储在这里

设置
3306 [information_schema]>select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)
alter table 库名 dicard tablespace; 删除表空间  alter table 库名 import tablespace; 重新加载表空间

事务的ACID特性

Atomic(原子性)
所有语句作为一个单元全部成功执行或者全部取消,不能出现中间状态
Consistent(一致性)
如果数据库在事务开始时处于一致状态,则在执行该事务期间将保留一致状态
Lsolated(隔离性)
事务之间不相互影响
Durable(持久性)
事务成功完成后,所做的所有更改都会准确的记录在数据库中,所做的更改不会丢失

事务的生命周期

事务的开始
begin
在5.5以上版本,不需要手工begin,只要你执行的是个DML语句,会自动在前面加一个begin命令
事务的结束
commit
完成一个事务,一旦事务提交成功,就说明具备ACID特性了
rollback:回滚事务
将内存中,已执行过的操作,回滚回去

自动提交策略(autocommit)

查看状态
3306 [(none)]>select @@autocommit;
 +--------------+
 | @@autocommit |
 +--------------+
 |            1 |
 +--------------+
 1 row in set (0.00 sec)
3306 [(none)]>set autocommit=0;         设置自动提交关闭
Query OK, 0 rows affected (0.00 sec)

3306 [(none)]>set global autocommit=0;  全局设定
Query OK, 0 rows affected (0.00 sec)

3306 [(none)]>select @@autocommit;      查看
 +--------------+
 | @@autocommit |
 +--------------+
 |            0 |
 +--------------+
 1 row in set (0.00 sec)

注:自动提交是否打开,一般在有事务需求的MySQL中,将其关闭
不管有没有事务需求,我们一般也都建议设置为0,可以很大程度上提高数据库性能
也可以在配置文件中设置
vim /etc/my.cnf
autocommit=0

隐式提交

导致提交的非事务语句:
DDL语句:ALTER,CREATE,DROP
DCL语句:GRANT,REVOKE,SET PASSWORD
锁定语句:LOCK TABLES , UNLOCK TABLES
我个人理解非事务语句是,DDL,DCL语句,要么成功,要么失败,没有中间型,所以说不具备事务

InnoDB事务的ACID如何保证

 

ib_logfile:redo日志文件
redo buffer:还没刷写到ib_logfile中在内存中的redo日志
ibd:数据文件
page buffer cache:内存中的数据
ibdata:undo日志文件
undo buffer:没刷写到ibdata文件中的在内存中的undo日志

redo log

redo是什么
重做日志,是事务日志的一种,是innodb引擎CSR前滚的依据
作用是什么
在事务ACID过程中,实现的是"D"持久化的作用,对于"A和C"也有相应作用
redo日志位置
iblogfile0,iblogfile1

redo buffer

redo的buffer:数据页的变化信息+数据页当时的LSN号,在内存中
LSN号:日志序列号,磁盘数据页,内存数据页,redo buffer,redolog

redo的刷新策略

commit:
刷新当前事务的redo buffer中的日志到磁盘
还会顺便将一部分redo buffer中没有提交的事务日志也刷新到磁盘

MySQL CSR—–前滚

MySQL:在启动时,必须保证redo日志文件和数据文件LSN必须一致,如果不一致就会触发CSR,最终保证一致
举例:
我们做了一个事务,begin;update;commit
1.在begin时,会立即分配一个TXID(事务ID)=tx_01
2.update时,会将需要修改的数据页(dp_01,LSN=101),加载到data buffer中
3.DBWR线程,会进行dp_01数据页修改更新,并更新LSN=102
4.LOGWR日志写线程,会将dp_01数据页的变化+LSN+TXID存储到redo buffer中
5.执行commit时,LOGWR日志写线程会将redo buffer信息写入redolog日志文件中,基于WAL原则,在日志完全写入磁盘后,commit命令才执行成功(会将此日志打上commit标记)
6.如果此时宕机,内存脏页没有来得及写入磁盘,内存数据全丢失
7.MySQL再次重启时,必须要redolog和磁盘数据页的LSN是一致的,但是此时dp_01,TXID=tx_01磁盘是LSN=101,redolog中LSN是102,MySQL此时无法正常启动,MySQL触发CSR,在内存中追平LSN号,触发ckpt,将内存数据页更新到磁盘,从而保证磁盘数据页和redolog LSN一致,这时MySQL正常启动
以上的工作过程,我们把它称之为基于redo的"'前滚操作"

undo 回滚日志

undo是什么
回滚日志|
作用是什么
在事务ACID过程中,实现的是"A"原子性的作用
另外"CI"也依赖于undo
在rolback时,将数据恢复到修改之前的状态
在CSR实现的是,将redo当中记录的未提交的事务进行回滚
undo提供快照技术,保存事务修改之前的数据状态,保证了MVCC,隔离性,mysqldump的热备

“锁”顾名思义就是锁定的意思
“锁”的作用是什么?
在事务的ACID过程中,“锁”和“隔离级别”一起来实现“I”的隔离性和“C”一致性(redo也有参与)
悲观锁:行级锁定
谁先操作某个数据行,谁就会持有这行的锁
乐观锁:没有锁

隔离级别

影响到数据的读取,默认的级别是RR模式
transaction_isolation   隔离级别参数
负责的是,MVCC,读一致性问题

RU(读未提交):会出现脏读,不可重复度,幻读
客户端A设置为RU模式,在A的事务提交之前,打开另一个客户端B,更新表,这时,客户端B的事务还没提交,但是客户端A就可以查询到B的数据,一旦B的事务因为原因进行回滚,所有操作将被撤销,那么A看见的数据就是脏数据

RC(读已提交):可能出现幻读,可以防止脏读
客户端A查询表,在A的事务未提交之前,打开客户端B,更新表,这时客户端B还没提交,客户端A不能查询到B已经更新的数据,解决了脏读问题,这时B提交,客户端A执行上一步查询,结果不一样,即产生了不可重复度的问题
RC模式写数据只会锁住响应的行

RR(可重复读):防止幻读,利用的是undo的快照技术+GAP(间隙锁)+Nextlock(下键锁)
客户端A查询表,在A的事务未提交之前,打开客户端B,更新表并提交,在客户端A操作,结果终是一样,没有出现不可重复读的问题

SR(可串行化):可以防止死锁,但是并发事务性能较差
客户端A查询表,打开一个客户端B,插入一条记录报错,表被锁了
SR级别会锁表,因此不会产生幻读的情况,这种隔离级别并发性极低

innodb_flush_log_at_trx_commit(双一标准之一)

作用:
主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点,取值分别为0,1,2三个
3306 [(none)]>select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
| 1 |
+----------------------------------+
1 row in set (0.00 sec)

0:当事务提交时,不做日志写入,而是每秒钟将log buffer中的数据写入文件系统缓存并且秒fsync磁盘一次
1:每次事务的提交都会引起日志文件写入,flush磁盘的操作,确保了事务的ACID,flush到操作系统的文件系统缓存,fsync到物理磁盘
2:每次事务提交引起写入文件系统缓存,但每秒钟完成一次fsync磁盘操作

innodb_flush_method=

作用:
控制的是log buffer和data buffer,刷写磁盘的时候是否经过文件系统缓存
参数:
O_DIRECT:数据缓冲区写磁盘,不走OS buffer,日志缓冲区走OS buffer
fsync:日志和数据缓冲区写磁盘,都走OS buffer
O_DSYNC:日志缓冲区写磁盘,不走OS buffer,数据缓冲区走OS buffer
使用建议:
最高安全模式
innodb_flush_log_at_trx_commit=1
innodb)flush_method=O_DIRECT
最高性能
innodb_flush_log_at_trx_commit=0
innodb_flush_method=fsync
赞(1)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

LNMP社群 不仅仅是技术

关于我们联系我们