事务
ACID
- A 原子性: 事务是最小的执行单位，不允许分割。事务的原子性确保动作 要么全部完成，要么完全不起作用;
- C 一致性: 执行事务前后，数据保持一致，多个事务对同一个数据读取的 结果是相同的;
- I 隔离性: 并发访问数据库时，一个用户的事务不被其他事务所干扰，各 并发事务之间数据库是独立的;
- D 持久性: 一个事务被提交之后。它对数据库中数据的改变是持久的，即 使数据库发生故障也不应该对其有任何影响。
脏读/幻读/不可重复读
- 脏读(Drity Read):某个事务已更新一份数据，另一个事务在此时读取了同一份 数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不 正确的。
- 不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这 可能是两次查询过程中间插入了一个事务更新的原有的数据。
- 幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事 务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务 在接下来的查询中，就会发现有几列数据是它先前所没有的。
隔离级别
- READ-UNCOMMITTED(读取未提交)
最低的隔离级别，允许读取尚 未提交的数据变更 - READ-COMMITTED(读取已提交) [Oracle默认]
允许读取并发事务已经提交的数 据 - REPEATABLE-READ(可重复读) [MySQL默认]
对同一字段的多次读取结果都是一致 的，除非数据是被本身事务自己所修改 - SERIALIZABLE(可串行化)
最高的隔离级别，完全服从ACID的隔离级 别。所有的事务依次逐个执行，事务之间完全不可能产生干扰。
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| READ-UNCOMMITTED | 发生 | 发生 | 发生 |
| READ-COMMITTED | 防 | 发生 | 发生 |
| REPEATABLE-READ | 防 | 防 | 发生 |
| SERIALIZABLE | 防 | 防 | 防 |
锁
并发事务时，锁机制保证访问次序
| 隔离级别 | 一致性非锁定读 | 共享锁 | 排他锁 |
|---|---|---|---|
| READ-UNCOMMITTED | 无 | 无 | 写 |
| READ-COMMITTED | 总是读取最新一份快照 | 无 | 写 |
| REPEATABLE-READ | 总是读取事务开始时的行数据版本 | 无 | 写 |
| SERIALIZABLE | 无 | 读,事务执行完释放 | 写 |
InnoDB的锁
支持
行级锁(row-level locking)和表级锁
基于
索引来完成行锁
算法
- Record lock
单个行记录上的锁
当查询的索引含有唯一属性时，将next-key lock降级为record key - Gap lock
间隙锁，锁定一个范围，不包括记录本身
可能导致幻读 - Next-key lock
record+gap 锁定一个范围，包含记录本身
行的查询使用
为了解决幻读问题
MVCC
多版本并发控制 Multiversion Concurrency Control
通过保存数据在某个时间点的快照来实现并发控制的。也就是说，不管事务执行多长时间，事务内部看到的数据是不受其它事务影响的，根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。
原理
行记录的隐藏列
- DB_ROW_ID：6-byte，隐藏的行 ID，用来生成默认聚簇索引。如果我们创建数据表的时候没有指定聚簇索引，这时 InnoDB 就会用这个隐藏 ID 来创建聚集索引。采用聚簇索引的方式可以提升数据的查找效率。
- DB_TRX_ID：6-byte，操作这个数据的事务 ID，也就是最后一个对该数据进行插入或更新的事务 ID。每开启一个事务，session会从数据库中获得一个事务 ID，自增长，ID越大事务越新。
- DB_ROLL_PTR：7-byte，回滚指针，也就是指向这个记录的 Undo Log 信息。
REPEATABLE READ下的流程
查询（SELECT）
InnoDB 会根据以下两个条件检查每行记录：
InnoDB只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。
行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。
只有符合上述两个条件的记录，才能返回作为查询结果。
插入（INSERT）
InnoDB为新插入的每一行保存当前系统版本号作为行版本号。
删除（DELETE）
InnoDB为删除的每一行保存当前系统版本号作为行删除标识。
删除在内部被视为更新，行中的一个特殊位会被设置为已删除。
更新（UPDATE）
InnoDB为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。
SQL优化
explain
- select_type
| id | select_type | description |
|---|---|---|
| 1 | SIMPLE | 不包含任 何子查询 或union等 查询 |
| 2 | PRIMARY | 包含子查 询最外层 查询就显 示为 PRIMARY |
| 3 | SUBQUERY | 在select或 where字 句中包含 的查询 |
| 4 | DERIVED | from字句 中包含的 查询 |
| 5 | UNION | 出现在 union后的 查询语句 中 |
| 6 | UNION RESULT | 从UNION 中获取结 果集 |
- id相同执行顺序由上至下。
- id不同，id值越大优先级越高，越先被执行。
- id为null时表示一个结果集，不需要使用它查询，常出现在包含union等查询语
句中。
table
查询的数据表type
| 值 | 含义 |
|---|---|
| ALL | 扫描全表数据 |
| index | 遍历索引 |
| range | 索引范围查找 |
| index_subquery | 在子查询中使用 ref unique_subquery 在子查询中使用 eq_ref |
| ref_or_null | 对Null进行索引的优化的 ref fulltext 使用全文索引 |
| ref | 使用非唯一索引查找数据 |
| eq_ref | 在join查询中使用PRIMARY KEYorUNIQUE NOT NULL索引关联。 |
key
在查询中实际使用的索引(覆盖索引时只在此字段显示)ref
连接匹配条件，即哪些列或常量被用于查找索引列上的值rows
估算的结果集数目，非精确值extra
- Using index 使用覆盖索引
- Using where 使用了用where子句来过滤结果集
- Using filesort 使用文件排序，使用非索引列进行排序时出现，非常消
耗性能，尽量优化。 - Using temporary 使用了临时表
优化目标
range < ref < consts
【推荐】SQL性能优化的目标:至少要达到 range 级别，要求是ref级别，如果可以是 consts最好。 说明: 1) consts 单表中最多只有一个匹配行(主键或者唯一索引)，在优 化阶段即可读取到数据。 2) ref 指的是使用普通的索引(normal index)。 3) range 对索引进行范围检索。 反例:explain表的结果，type=index，索引物理文件全扫描，速 度非常慢，这个index级别比较range还低，与全表扫描是小巫见大巫。
