MySQL 执行引擎 事务 锁 日志

图片无法加载请跳转CSDN：MySQL 执行引擎 事务 锁 日志-CSDN博客

一、执行引擎

1、查询设置引擎

-- 查询当前数据库支持的存储引擎：默认的执行引擎是innoDB 支持事务，行级锁定和外键
show engines;
 
-- 查看当前的默认存储引擎：
show variables like ‘%storage_engine%’;

-- 查看某个表用了什么引擎(在显示结果里参数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=INNODB 改为目标引擎。如：default-storage-engine=MYISAM
4. 启动mysql服务

2、功能

功能	MylSAM	MylSAM	lnnoDB
存储限制	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

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

二、事务

1、理解事务

• 在MySQL中的事务（Transaction）是由存储引擎实现的，在MySQL中，只有InnoDB存储引擎才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
• 事务用来管理 DDL、DML、DCL 操作，比如 insert,update,delete 语句，默认是自动提交的。

在银行转账时，必须保证转账绝对安全，这时需要事务参与，示例如下:

update account set money = money - 200 where id = 1; 
update account set money = money + 200 where id = 2;
-- 假如在第一次update之后，出现了意外、异常，没有执行第二次update，这时转账是否会出现异常？
-- 所以这两行命令要么全部执行，要么全部不执行

2、事务操作

操作	介绍	命令
开启事务 (Start Transaction)	任何一条DML语句(insert、update、delete)执行，标志事务的开启	BEGIN 或 START TRANSACTION
提交事务 (Commit Transaction)	成功的结束，将所有的DML语句操作历史记录和底层硬盘数据来一次同步	COMMIT
回滚事务 (Rollback Transaction)	失败的结束，将所有的DML语句操作历史记录全部清空	ROLLBACK

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

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

set autocommit=0 -- 禁止自动提交 
set autocommit=1 -- 开启自动提交 

样例:

-- 创建账户表
create table account(
	id int primary key, -- 账户id
	name varchar(20), -- 账户名
	money double -- 金额
);

--  插入数据
insert into account values(1,'zhangsan',1000);
insert into account values(2,'lisi',1000);

-- 设置MySQL的事务为手动提交(关闭自动提交)
select @@autocommit;	-- 查看
set autocommit = 0;

-- 模拟账户转账
-- 开启事务 
begin;
update account set money = money - 200 where name = 'zhangsan';
update account set money = money + 200 where name = 'lisi';
-- 提交事务
commit;
-- 回滚事务
rollback;	-- 没提交事务之前将内存中的数据还原

3、事务的特性

4、事务的隔离级别

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

-- 设置read uncommitted
set session transaction isolation level read uncommitted;
-- 这种隔离级别会引起脏读，A事务读取到B事务没有提交的数据

-- 设置read committed
set session transaction isolation level read committed;
-- 这种隔离级别会引起不可重复读，A事务在没有提交事务之前，可看到数据不一致

-- 设置repeatable read （MySQ默认的）
set session transaction isolation level repeatable read;
-- 这种隔离级别会引起幻读，A事务在提交之前和提交之后看到的数据不一致

-- 设置serializable
set session transaction isolation level serializable;
-- 这种隔离级别比较安全，但是效率低，A事务操作表时，表会被锁起，B事务不能操作。

三、锁

1、概述

锁类型	特点
表级锁	偏向Mylsam存储引擎，开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低.
行级锁	偏向InnoDB存储引擎，开销大，加锁慢;会出现死锁;锁定粒度最小，发生锁冲突的概率最低, 并发度也最高

表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web 应用；

行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并查询的应用，如一些在线事务处理（OLTP）系统。

2、MyISAM 表锁

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

lock table table_name read;  -- 加读锁 
lock table table_name write; -- 加写锁

特点：

• 对MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
• 对MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作；

3、InnoDB行锁

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

行锁模式：
InnoDB 实现了以下两种类型的行锁。

• 共享锁（S）：又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
• 排他锁（X）：又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；
对于普通SELECT语句，InnoDB不会加任何锁；

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE -- 共享锁（S）
SELECT * FROM table_name WHERE ... FOR UPDATE 		  -- 排他锁（X) 

四、日志

1、binlog日志

-- 查看错误日志位置指令 
show variables like 'log_error%';

2、二进制日志(binlog)

-- 查看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';

-- 从指定的位置开始,查看指定的Binlog日志
show binlog events in 'binlog.000010' from 156;

-- 从指定的位置开始,查看指定的Binlog日志,限制查询的条数
show binlog events in 'binlog.000010' from 156 limit 2;
--从指定的位置开始，带有偏移，查看指定的Binlog日志,限制查询的条数
show binlog events in 'binlog.000010' from 666 limit 1, 2;

-- 清空所有的 binlog 日志文件
reset master

3、查询日志

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

4、慢日志查询

-- 查看慢查询日志是否开启
show variables like 'slow_query_log%';

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

-- 查看慢查询的超时时间
show variables like 'long_query_time%';

select sleep(12);