MySQL的存储引擎，MySQL的事务，MySQL的锁机制，MySQL的日志

1，MySQL的存储引擎

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据

不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎

用户可以根据不同的需求为数据表选择不同的存储引擎

可以使用SHOWENGINES命令可以查看Mysql的所有执行引擎我们可以到默认的执行引擎是innoDB支持事务,行级锁定和外键

（1）分类

MylSAM:Mysql 5.5之前的默认数据库引擎，最为常用。拥有较高的插入，查询速度，但不支持事务

InnoDB:事务型速记的首选引擎，支持ACID事务，支持行级锁定，MySQL5.5成为默认数据库引擎

Memory:所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在MYSQL重新启动是会丢失

Archive:非常适合存储大量的独立的，作为历史记录的数据。因为它们不经常被读取。Archive拥有高效的插入速度，但其对查询的支持相对较差

Federated:将不同的MySQL服务器联合起来，逻辑上组成一个完整的数据库。非常适合分布式应用

功能	MyISAM	MEMORY	InnoDB
存储限制	256TB	RAM	64TB
支持事务	No	No	Yes
支持全文索引	Yes	No	No
支持B树索引	Yes	Yes	Yes
支持哈希索引	No	Yes	No
支持集群索引	No	No	Yes
支持数据索引	No	Yes	Yes
支持数据压缩	Yes	No	No
空间使用率	低	N/A	高
支持外键	No	No	Yes

（2）操作

-- 查看当前数据库的存储引擎
show engines;

-- 查看当前默认的存储引擎
show variables like '%storage engine%';

-- 查看某个表用了什么数据引擎
show create table student;

-- 创建新表时指定的存储引擎
create table(...) engine=MyISAM;

-- 修改数据库引擎
alter table student engine =INNODB;
alter table student engine =MyISAM;

--修改MySQL默认存储引擎方法

1.关闭mysql服务

2.找到mysql安装目录下的my.ini文件

3.找到default-storage-engine=lNNODB改为目标引擎，如:default-storage-engine=MYISAM

 4.启动mysql服务

2，MySQL的事务

在MySQL中的事务(Transaction）是由存储引擎实现的，在MySQL中，只有InnoDB存储引擎才支持事务

事务处理可以用来维护数据库的完整性，保证成批的sQL语句要么全部执行，要么全部不执行

事务用来管理DDL、DML、DCL操作，比如 insert,update,delete语句，默认是自动提交的

（1）事务操作

1，开启事务:Start Transaction

任何一条DML语句(insert、update、delete)执行，标志事务的开启

命令:BEGIN或START TRANSACTION

2，提交事务:Commit Transaction

成功的结束，将所有的DML语句操作历史记录和底层硬盘数据来一次同步

命令:COMMIT

3，回滚事务:Rollback Transaction

失败的结束,将所有的DML语句操作历史记录全部清空命令:ROLLBACK

之前的所有sQL操作其实也有事务，只是MysQL自动帮我们完成的，每执行一条sQL时MysQL就帮我们自动提交事务，因此如果想要手动控制事务，则必须关闭MySQL的事务自动提交

在MySQL中直接用SET 来改变MySQL的自动提交模式:

set autocommit=0禁止自动提交

set autocommit=1 开启自动提交

-- 开启事务
begin;
update account set money =money-200 where id=1;
update account set money =money+200 where id=2;
-- 提交事务
commit;
-- 回滚事务
rollback;

（2）特性

1）原子性

事务是一个不可分割的整体,事务开始后的所有操作,要么全部完成,要么全部不做

2）一致性

系统从一个正确的状态,迁移到另一个正确的状态

3）隔离性

每个事务的对象对其他事务的操作对象互相分离,事务提交前对其他事务不可见

4）持久性

事务一旦提交,则其结果是永久性的

（3）隔离级别

lsolate，顾名思义就是将事务与另一个事务隔离开，为什么要隔离呢?如果一个事务正在操作的数据被另一个事务修改或删除了，最后的执行结果可能无法达到预期。如果没有隔离性还会导致其他问题

隔离级别	注释	解释	解释
READ UNCOMMITTED	读未提交	一个事务可以读取另一个未提交事务的数据，最低级别，任何情况都无法保证,会造成脏读	这种隔离级别会引起脏读，A事务读取到B事务没有提交的数据
READ COMMITTED	读提交	一个事务要等另一个事务提交后才能读取数据，可避免脏读的发生，会造成不可重复读	这种隔离级别会引起不可重复读，A事务在没有提交事务期间，可以看到的数据是不同的
REREATABLE READ（MySQL默认）	可重复读	就是在开始读取数据（事务开启）时，不再允许修改操作，可避免脏读、不可重复读的发生，但是会造成幻读	这种隔离级别会引起幻读，A事务在提交之前和提交之后看到的数据不一致
SERIALIZABLE	序列化	是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用	这种隔离级别比较安全，但是效率低，A事务操作表时，表会被锁起，B事务不能操作。

事务隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	是	是	是
READ COMMITTED	否	是	是
REREATABLE READ	否	否	是
SERIALIZABLE	否	否	否

3，MySQL的锁机制

锁是计算机协调多个进程或线程并发访问某一资源的机制（避免争抢）

在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂

分类：

从对数据操作的粒度分:

1）表锁:操作时,会锁定整个表

2）行锁:操作时，会锁定当前操作行

从对数据操作的类型分:

1）读锁（共享锁):针对同一份数据，多个读操作可以同时进行而不会互相影响

2）写锁（排它锁):当前操作没有完成之前，它会阻断其他写锁和读锁

存储引擎	表级锁	行级锁
MyISAM	支持	不支持
InnoDB	支持	支持
MEMORY	支持	不支持
BDB	支持	不支持

锁类型	特点
表级锁（适合查询，少量索引如web）	偏向MylSAM存储引擎，开销小,加锁快，不会出现死锁;锁定粒度大。发生锁冲突的概率最高,并发度最低
行级锁（大量索引，如OLTP系统）	偏向InnoDB存储引擎，开销大，加锁慢;会出现死锁;锁定粒度最小，发生锁冲突的概率最低,并发度也最高

（1）MyISAM表锁

MylSAM在执行查询语句(SELECT)前，会自动给涉及的所有表加读锁，在执行更新操作(UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MylSAM表显式加锁

加读锁:lock table table_name read;

加写锁:lock table table_name write;

（2）InnoDB行锁

特点：

偏向InnoDB存储引擎，开销大，加锁慢;会出现死锁;锁定粒度最小，发生锁冲突的概率最低,并发度也最高

InnoDB 与MyISAM的最大不同有两点:一是支持事务;二是采用了行级锁

模式：

共享锁(S):又称为读锁，简称s锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改

排他锁（X):又称为写锁，简称x锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改

对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁（X);

对于普通SELECT语句,InnoDB不会加任何锁;

共享锁(S）:SELECT *FROM table name WHERE ... LocK IN SHARE MODE

排他锁(X) :SELECT * PROM table name WHERE ... FOR UPDATE

4，MySQL的日志

分类：

错误日志

二进制日志

查询日志

慢查询日志

（1）错误日志

错误日志是 MysQL中最重要的日志之一，它记录了当mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，可以首先查看此日志

该日志是默认开启的，默认存放目录为mysql的数据目录,默认的日志文件名为hostname.err (hostname是主机名)

查询日志位置指令：

show variables like 'log_error%';

（2）二进制日志-binlog

二进制日志（BINLOG）记录了所有的DDL(数据定义语言）语句和DML（数据操纵语言）语句，但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用，MySQL的主从复制，就是通过该binlog实现的

二进制日志，MySQl8.0默认已经开启，低版本的MySQL的需要通过配置文件开启，并配置MySQL日志的格式。Windows系统:my.ini Linux系统:my.cnf

#配置开启binlog日志，日志的文件前缀为mysqlbin ----生成的文件名如:mysqlbin.o00001,mysqbin.o00002log_bin=mysqlbin

#配置二进制日志的格式

binlog_format=STATEMENT

日志格式：

STATEMENT

该日志格式在日志文件中记录的都是sQL语句(statement)，每一条对数据进行修改的sQL都会记录在日志文件中，通过Mysql提供的mysqlbinlog工具，可以清晰的查看到每条语句的文本。主从复制的时候，从库(slave)会将目志解析为原文本，并在从库重新执行一次

Row

该日志格式在日志文件中记录的是每一行的数据变更，而不是记录sQL语句。比如，执行sQL语句: updatetb_book set status=1',如果是STATEMENT日志格式，在日志中会记录一行saL文件;如果是Row，由于是对全表进行更新，也就是每一行记录都会发生变更，ROW格式的日志中会记录每一行的数据变更

MIXED

混合了STATEMENT 和 ROW两种格式

-- 查看MySQL是否开启了binlog日志
show variables like 'log_bin';

-- 查看binlog日志的格式
show variables like 'binlog_format';

-- 查看所有日志
show binlog events;

-- 查看最新的日志
show master status;

-- 查询指定的binlog日志
show binlog events in 'binlog.000010';

-- 清空日志文件
reset master

（3）查询日志

查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的sQL语句

默认情况下，查询日志是未开启的。如果需要开启查询日志，可以设置以下配置:

#该选项用来开启查询日志,可选值:0或者1;0代表关闭，1代表开启

general_log=1

#设置日志的文件名,如果没有指定，默认的文件名为

host_name.loggeneral_log_file=file_name

-- 查看MySQL是否开启了查询日志
show variables like 'general_log';

-- 开启查询日志
set global general_log=1;

（4）慢查询日志

慢查询日志记录了所有执行时间超过参数long_query_time设置值并且扫描记录数不小于min_examined_row_limit的所有的sQL语句的日志。long_query_time默认为10秒，最小为0，精度可以到微秒

#该参数用来控制慢查询日志是否开启，可取值:1和0，1代表开启，0代表关闭

slow_query_log=1

#该参数用来指定慢查询日志的文件名

slow_query_log_file=slow_query.log

#该选项用来配置查询的时间限制，超过这个时间将认为值慢查询，将需要进行日志记录，默认10s

long_query_time=10