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默认存储引擎方法
- 关闭mysql服务
- 找到mysql安装目录下的my.ini文件：
- 找到default-storage-engine=INNODB 改为目标引擎。如：default-storage-engine=MYISAM
- 启动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);
