MySQL 执行引擎 事务 锁 日志

MySQL 执行引擎 事务 锁 日志

• 一、执行引擎
• 二、事务
• 三、锁
• 四、日志

一、执行引擎

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、功能

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、事务操作

正常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、概述

表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如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);