【SQL】MySQL中的存储引擎、事务、锁、日志

存储引擎：

数据库管理系统（DBMS）使用数据存储引擎进行创建、查询、更新和删除数据。
MySQL5.5之前默认的存储引擎是MyISAM，5.5及之后版本默认的存储引擎是InnoDB。(my.ini中指定的)

• MyISAM：不支持事务，不支持外键
• InnoDB：支持事务，支持外键
• 存储引擎是针对表来说的，可以通过alter改变表的存储引擎
• InnoDB与MyISAM的最大区别是两点：InnoDB支持事务、支持行级锁

---

事务：

事务主要针对的是一组DML语句（insert、delete、update），该组DML语句要么全部成功，要么全部失败。典型案例：转账

MySQL中只有InnoDB存储引擎才支持事务。
MySQL中默认是自动提交事务的，因此使用事务时要先禁止自动提交。

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

--开启事务(可以使用begin或者start transaction)
begin
--执行多个SQL语句
update account set money = money-200 where id = 1
update account set money = money+200 where id = 2
--提交事务
commit

--回滚事务
rollback
1
2
3
4
5
6
7
8
9
10

事务的特性ACID：

• 原子性（Atomicity）： 事务是一个整体，要么全部成功，要么全部失败。
• 一致性（Consistency）： 事务之前和事务之后的状态都是“正确”的（满足业务合理性）
• 隔离性（Isolation）： 并发执行的两个事务之间相互隔离，互不影响（即使操作同一条数据也不应该互相影响）
• 持久性（Durability）： 事务一旦被成功完成，数据被永久存储，不能回滚

事务的隔离级别：（越向下隔离级别越高）

• 脏读： A事务读取到B事务没有提交的数据
• 不可重复读： A事务提交之前，读取的同一个数据值发生变化（B事务提交导致）
• 幻读： A事务提交之前和提交之后，同一个数据值发生变化（A事务提交之前读到的数据值不会变化，提交之后才能读到B事务提交修改的数据）

隔离级别的设置：

-- 查看隔离级别
show variables like '%isolation%';
-- 设置隔离级别
set session transaction isolation level read uncommitted;
set session transaction isolation level read committed;
set session transaction isolation level repeatable read;
set session transaction isolation level serializable;
1
2
3
4
5
6
7

---

锁：

• 按数据操作粒度划分：
  表锁：锁定整个表
  行锁：锁定当前操作行
• 按数据操作类型划分：
  读锁（共享锁）：多个读操作可以同时进行互不影响，同一个表读锁可以加多个，加了读锁后只能读，自己和其他人均不能修改
  写锁（排他锁）：当前操作没完成前，其他人既不能写也不能读，同一个表写锁只能加一个

加表锁
MyISAM存储引擎使用表锁。
在查询语句（SELECT）前，自动给涉及的所有表加读锁。
在更新语句（UPDATE、DELETE、INSERT）前，自动给涉及的表加写锁。
一般不需要使用lock table命令给MyISAM表显式加锁。

-- 显式加表级读锁（共享锁）
lock table table_name read;
-- 显式加表级写锁（排他锁）
lock table table_name write;
-- 解锁
unlock tables;
1
2
3
4
5
6

加行锁
InnoDB默认使用行锁。
对于查询语句（SELECT）不会加任何锁。
对于更新语句（UPDATE、DELETE、INSERT），自动加排他锁，其他事务不能再修改该行数据，只有等当前事务commit后（释放排他锁），其他事务才能修改该行数据。

-- 显式加行级读锁（共享锁）
select * from table_name where ... lock in share mode;
-- 显式加行级写锁（排他锁）
select * from table_name where ... for update

1
2
3
4
5

---

日志：

• 错误日志： 记录MySQL发生错误的信息，默认开启

-- 查看错误日志存放目录
show variables like 'log_error%';
1
2

• 二进制日志： 记录了所有的DDL（Data Definition Language）语句和DML（Data Manipulation Language）语句，可以用于灾难时的数据恢复。MySQL8.0默认开启，低版本使用配置文件my.ini开启。

# 配置开启binlog日志，日志的文件前缀为mysqlbin
log_bin = mysqlbin
# 配置二进制日志的格式
binlog_format = STATEMENT
1
2
3
4

-- 查看是否开启binlog日志
show variables like 'log_bin';
-- 查看binlog日志格式
show variables like 'binlog_format';
1
2
3
4

• 查询日志： 记录了所有操作语句（相比二进制日志多了查询语句），默认不开启

# 配置开启查询日志，0关闭，1开启
general_log = 1
# 配置日志的文件名
general_log_file = file_name
1
2
3
4

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

• 慢查询日志： 记录执行时间超过参数long_query_time（默认10s）且扫描记录数不小于min_examined_row_limit（默认0）的所有SQL语句

# 配置开启慢查询日志，0关闭，1开启
slow_query_log = 1
# 配置慢查询日志的文件名
slow_query_log_file = slow_query.log
# 设置long_query_time时间
long_query_time = 10
1
2
3
4
5
6