【SQL】MySQL中的存储引擎、事务、锁、日志
存储引擎：
数据库管理系统（DBMS）使用数据存储引擎进行创建、查询、更新和删除数据。
MySQL5.5之前默认的存储引擎是MyISAM，5.5及之后版本默认的存储引擎是InnoDB。(my.ini中指定的)
- MyISAM：不支持事务，不支持外键
- InnoDB：支持事务，支持外键
- 存储引擎是针对表来说的，可以通过alter改变表的存储引擎
- InnoDB与MyISAM的最大区别是两点：InnoDB支持事务、支持行级锁
事务：
事务可以保证成批的SQL要么全部执行，要么全部不执行。
MySQL中只有InnoDB存储引擎才支持事务。
MySQL中默认是自动提交事务的，因此使用事务时要先禁止自动提交。
--禁止自动提交
set autocommit=0
--开启自动提交
set autocommit=1
--开启事务(可以使用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
事务的特性ACID：
- 原子性（Atomicity）： 事务是一个整体，要么全部成功，要么全部失败。
- 一致性（Consistency）： 事务之前和事务之后的状态都是“正确”的（满足业务合理性）
- 隔离性（Isolation）： 并发执行的两个事务之间相互隔离，互不影响（即使操作同一条数据也不应该互相影响）
- 持久性（Durability）： 事务一旦被成功完成，数据被永久存储，不能回滚
事务的隔离级别：（越向下隔离级别越高）
- 脏读： A事务读取到B事务没有提交的数据
- 不可重复读： A事务提交之前，读取的同一个数据值发生变化（B事务提交导致）
- 幻读： A事务提交之前和提交之后，同一个数据值发生变化（A事务提交之前读到的数据值不会变化，提交之后才能读到B事务提交修改的数据）
| 事务隔离级别 | 解释 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|---|
| 读未提交（read-uncommitted） | 一个事务可以读取另一个事务未提交的数据 | 是 | 是 | 是 |
| 读已提交（read-committed）（Oracle默认） | 一个事务要等另一个事务提交后才能读取数据 | 否 | 是 | 是 |
| 可重复读（repeatable-read）（MySQL默认） | 事务开启后，不再允许修改操作，但可以读取数据 | 否 | 否 | 是 |
| 串行化（serializable） | 各事务串行化顺序执行，互不影响，效率很低，一般不用 | 否 | 否 | 否 |
隔离级别的设置：
-- 查看隔离级别
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;
锁：
- 按数据操作粒度划分：
表锁：锁定整个表
行锁：锁定当前操作行 - 按数据操作类型划分：
读锁（共享锁）：多个读操作可以同时进行互不影响，同一个表读锁可以加多个，加了读锁后只能读，自己和其他人均不能修改
写锁（排他锁）：当前操作没完成前，其他人既不能写也不能读，同一个表写锁只能加一个
| 存储引擎 | 表级锁 | 行级锁 |
|---|---|---|
| MyISAM | 支持 | 不支持 |
| InnoDB | 支持 | 支持 |
加表锁
MyISAM存储引擎使用表锁。
在查询语句（SELECT）前，自动给涉及的所有表加读锁。
在更新语句（UPDATE、DELETE、INSERT）前，自动给涉及的表加写锁。
一般不需要使用lock table命令给MyISAM表显式加锁。
-- 显式加表级读锁（共享锁）
lock table table_name read;
-- 显式加表级写锁（排他锁）
lock table table_name write;
-- 解锁
unlock tables;
加行锁
InnoDB默认使用行锁。
对于查询语句（SELECT）不会加任何锁。
对于更新语句（UPDATE、DELETE、INSERT），自动加排他锁，其他事务不能再修改该行数据，只有等当前事务commit后（释放排他锁），其他事务才能修改该行数据。
-- 显式加行级读锁（共享锁）
select * from table_name where ... lock in share mode;
-- 显式加行级写锁（排他锁）
select * from table_name where ... for update日志：
- 错误日志： 记录MySQL发生错误的信息，默认开启
-- 查看错误日志存放目录
show variables like 'log_error%';
- 二进制日志： 记录了所有的DDL（Data Definition Language）语句和DML（Data Manipulation Language）语句，可以用于灾难时的数据恢复。MySQL8.0默认开启，低版本使用配置文件my.ini开启。
# 配置开启binlog日志，日志的文件前缀为mysqlbin
log_bin = mysqlbin
# 配置二进制日志的格式
binlog_format = STATEMENT
-- 查看是否开启binlog日志
show variables like 'log_bin';
-- 查看binlog日志格式
show variables like 'binlog_format';
| 二进制日志的格式 | 特点 |
|---|---|
| STATEMENT | 记录的都是SQL语句 |
| ROW（MySQL8.0默认） | 记录每一行数据的变更 |
| MIXED | 混合了STATEMENT和ROW |
- 查询日志： 记录了所有操作语句（相比二进制日志多了查询语句），默认不开启
# 配置开启查询日志，0关闭，1开启
general_log = 1
# 配置日志的文件名
general_log_file = file_name
-- 查看是否开启查询日志
show variables like 'general_log';
- 慢查询日志： 记录执行时间超过参数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