MySQL事务及常见存储引擎

一、事务的四特性

事务：transaction

一个数据库事务由一条或者多条可发生事务的SQL语句构成，它们形成一个逻辑的工作单元。这些SQL语句要么全部执行成功，要么全部执行失败

原子性(Atomicity) A

    事务的原子性是指事务中包含的所有操作要么完成（提交），要么不做（回滚）,也就是说所有的活动在数据库中要么全部反映,要么全部不反映,以保证数据库的一致性。

一致性(Consistency) C

   事务的一致性是指数据库在事务操作前和事务处理后,数据库中的数据必须保持状态的一致，且满足业务的规则约束。

隔离性(Isolation) I

  隔离性是指数据库允许多个并发的事务同时对其中的数据进行读写或修改的能力,隔离性可以防止多个事务的并发执行时,由于它们的操作命令交叉执行而导致数据的不一致性。简单点说就是两个事务之间是不能互相干扰的。

持久性(Durability) D    事务的持久性是指在事务处理结束后,它对数据的修改应该是永久的。即便是系统在遇到故障的情况下也不会丢失,这是数据的重要性决定的。简单点说就是提交后不能回滚

二、事务的四种隔离级别（TransactionIsolation Level）

Readuncommitted 未提交读：顾名思义，就是一个事务可以读取另一个未提交事务的数据（修改删除 插入等）。

Readcommitted 提交读：顾名思义，就是一个事务要等另一个事务提交后才能读取到已提交事务发生操作的数据。（修改 删除 插入等）。

注：有些翻译版本将提交读翻译成为一致性读。

Repeatableread 重复读：即在数据读出来之后加锁，类似"select * from XXX forupdate"，明确数据读取出来就是为了更新用的，所以要加一把锁，防止别人修改它。

SERLALIZABLE 串行化，最高的事务隔离级别，不管多少事务，都是挨个运行，做完一个事务或其所有子事务之后才可以执行另外一个事务或其所有子事务，这样就解决了脏读、不可重复读和幻读的问题了

查看隔离级别：

select @@GLOBAL.transaction_isolation;

select @@SESSION.transaction_isolation;

三、MySQL常见的存储引擎

存储引擎查询

show engines;

InnoDB 存储引擎

MySQL 5.5 及以后版本的默认存储引擎

特点： 

1.支持事务（ACID），支持4个事务隔离级别，支持多版本读。

2.行级锁定（更新时一般是锁定当前行）。

3.读写阻塞与事务隔离级别相关。

4.具有非常高效的缓存特性：能缓存索引，也能缓存数据。

5.整个表和主键以Cluster(聚集)方式存储，组成一个平衡树。

6.所有SecondaryIndex都会保存主键信息。

7.支持分区，表空间，类似oracle数据库。8.支持外键约束，5.6之后支持全文索引

MyISAM 存储引擎 

MySQL5.5 版本之前的默认存储引擎，较多的系统表也还是使用这个存储引擎，系统临时表也会用到MyISAM存储引擎

特点：

1.做count（*）查询的时候比INNODB快

2.数据（MYD）和索引（MYI）分开存储,只对索引进行缓存,虽然key_buffer可以大幅提高性能,减少磁盘IO,但对数据不缓存

3.表级锁

4.不支持事务

5.适合读业务比较多的生产环境,比如BLOG等,读的速度比较快.

6.占用资源比较少.服务器硬件不好时.可以考虑使用

7.数据恢复没有innodb引擎恢复的完美

8.支持全文索引,不支持外键约束

Memory 存储引擎 

数据都是存储在内存中，IO 效率要比其他引擎高很多，服务重启数据会丢失，内存数据表默认只有16M，一般我们不会使用到Memory存储引擎

特点：

1.支持hash索引，Btree 索引，默认hash（查找复杂度0(1)）

2.字段长度都是固定长度varchar(32)=char(32)

3.不支持大数据存储类型字段如blog，text

4.表级锁

Archive存储引擎

压缩协议进行数据的存储，数据存储为ARZ文件格式。

特点：

1.只支持insert和select两种操作

2.只允许自增ID列建立索引

3.行级锁

4.不支持事务

5.数据占用磁盘少

应用场景：

1.日志系统

2.大量的设备数据采集

CSV存储引擎

特点：

1.不能定义索引、列定义必须为NOTNULL、不能设置自增列，不适用大表或者数据的在线处理

2.CSV数据的存储用逗号隔开，可直接编辑CSV文件进行数据的编排，数据安全性低。编辑之后，要生效使用flushtable XXX 命令

应用场景：

1.数据的快速导出导入

2.表格直接转换成CSV

四、MYSQL5.7/8.0支持的几种日志文件

官方关于这几种日志的描述：

https://dev.mysql.com/doc/refman/5.7/en/server-logs.html

https://dev.mysql.com/doc/refman/8.0/en/server-logs.html

错误日志（Error log）

MySQL错误日志记录MySQL运行过程中较为严重的警告和错误信息，以及MySQL每次启动和关闭的详细信息。

MySQL错误日志默认是开启的。可以通过MySQL配置文件中的log-error=/var/log/mysqld.log配置，修改错误日志的配置信息。

可以通过如下SQL查看错误日志的详细信息：

showvariables like '%log_err%';

一般或通用查询日志（General query log） 

记录已连接MYSQL数据库的客户端所执行的语句。

可以通过如下SQL查看当前的通用日志是否开启：

SHOWVARIABLES LIKE '%general%';

开启通用查询日志：setglobal general_log= on;

关闭通用查询日志：setglobal general_log= off;

二进制日志（Binary log）

MySQL的二进制日志（binarylog）是一个二进制文件，主要用于记录修改数据或有可能引起数据变更的SQL语句。二进制日志（binarylog）中记录了对MySQL数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息，但是它不记录SELECT、SHOW等那些不修改数据的SQL语句。二进制日志（binarylog）主要用于数据库恢复和主从复制，以及审计（audit）操作

二进制日志（Binary log） 的操作语句

/*删除所有二进制日志文件：*/

resetmaster

resetslave

/*删除部分二进制日志文件：*/

purgemaster logs to/befor ‘args’;

例如：

PURGEMASTER LOGS TO 'mysql-bin.010';

PURGEMASTER LOGS BEFORE '2021-06-02 22:46:26';

/*查看是否启用二进制日志：*/

showvariables like '%log_bin%';

/*查看所有的二进制日志参数*/

showvariables like '%binlog%';

/*查看文件的位置*/

showvariables like '%datadir%';

/*查看当前服务器所有的二进制日志文件*/

showbinary logs;

showmaster logs;

慢查询日志（Slow query log）

记录所有执行时间超过long_query_time秒的查询SQL或者没有使用索引的查询SQL，默认情况下，MySQL不开启慢查询日志，

long_query_time值查询语句：show variables like 'long_query_time';

long_query_time值修改语句：set long_query_time= 秒数;

/*查看当前慢查询日志的开启情况：*/

showvariables like '%query%';

slow_query_log：ON表示开启慢查询日志，OFF表示关闭慢查询日志

slow_query_log_file：记录慢查询日志的文件地址（默认为主机名.log）

long_query_time：指定了慢查询的阈值，单位是秒，即执行语句的时间若超过这个值则为慢查询语句log_queries_not_using_indexes：ON 表示会记录所有没有利用索引来进行查询的语句，前提是 slow_query_log 的值也是 ON，否则，不会奏效，OFF 表示不会记录所有没有利用索引来进行查询的语句