MySQL数据库学习笔记（Ⅲ）

MySQL存储引擎

MySQL默认的存储引擎是InnoDB；InnoDB支持事务和行级锁（也支持表级锁）。
MySQL默认的事务隔离级别是可重复读。

# 查看MySQL事务隔离级别
show variables like '%isolation';

锁的类型和特点

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大；发生锁冲突的概率高，并发度低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度小；发生锁冲突的概率低，并发度高。

对于锁开销的理解：在对相同数据量加锁的情况下，表锁直接对全表加锁，而行锁需要对每一条记录加锁，消耗内存更多，开销更大。

数据库事务

数据库事务是访问并可能更新数据库中各种数据记录的一个程序执行单元，通常包含一个对数据库进行读或写的操作序列。简单地说，事务是一组SQL语句的集合。

事务的目的和作用

• 成功情况下，将数据从一种状态变为另一种状态，并持久化
• 异常情况下，将数据库恢复到正常状态，保证一致性
• 并发情况下，各操作相互独立，不产生影响

事务的特性（ACID）

• 原子性（Actomicity），事务中的操作序列为一个不可分割整体，要么全部执行成功，要么全部不执行。
• 一致性（Consistency）事务要确保数据库的状态从一个一致的状态转变成另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
• 隔离性（Isolation）多个事务并发执行时，彼此之间相互独立
• 持久性（Durability）事务成功提交，其对数据库的修改将被永久保存。

一致性理解：预定约束为账号余额不能小于零，A账号余额为10元，执行事务A向B转账20元，此时10-20=-10<0,由于该操作破坏了约束，事务将会回滚，即事务执行失败，也许说事务能够保证一致性更好理解。

事务的并发异常

• 回滚丢失/第一类更新丢失

事务A	事务B
读i=0	读i=0
写i=1	写i=2
提交	由于某种原因，回滚
	此时i=0；事务A由于事务B的回滚导致 更新丢失

• 覆盖丢失/第二类更新丢失

事务A	事务B
读i=0	读i=0
写i=i+1	写i=i+2
提交，i=1
	提交，i=2
	此时i=2；事务A由于事务B的更新，结果被覆盖，造成覆盖丢失

• 脏读

事务A	事务B
读i=0	读i=0
写i=1
	再次读i=1
由于某种原因，回滚	写i=i+1
	提交，i=2
	此时i=2；事务A回滚，事务B读取到事务A修改后未提交的脏数据

• 不可重复读

事务A	事务B
读i=0	读i=0
写i=1
	读i=1
	事务B对i的两次读结果不同，称为不可重复读

• 幻读

事务A	事务B
读表记录数count=10 ，其中表字段flag=0	读表记录数count=10
将表字段flag全部修改为flag=1	插入一条新数据，flag=0
读表发现记录数count=11，某记录flag=0
彷佛全表更新操作有问题，读取数据记录数异常，出现了幻觉

其中，不可重复读和幻读有点相似，不可重复读针对的是某记录的字段值，而幻读是针对表记录的数据量。

事务隔离级别

• 读未提交（Read Uncommitted）
  一个事务的更新语句未提交，其他事务可以读取到结果
• 读已提交（Read Committed）
  一个事务只能读到其他事务已经提交的结果
• 可重复读（Repeatable Read）
  一个事务对数据多次读取，结果一致
• 串行化（Serializable）
  事务串行执行

innodb采用的隔离级别是可重复读
可重复读的一般实现是，读前加读锁，写前加写锁，直至事务最后才释放。

innodb策略
当前读：间隙锁，next-key locks
普通读：MVCC，多版本控制协议

MySQL索引

inoodb存储引擎：支持B+树，自适应哈希索引（主键是聚集索引，其他记录是非聚集索引，叶子节点只存储主键）
memory：hash索引

选择索引

短小精悍，重复度低（离散性高），查询频度高
联合索引，最左匹配原则
like’****%'是否走索引：看数据的离散性

为什么是B+树？

1. 哈希；单点检索效率高，但是区间检索、排序（order by）很差，也许还会有大数据量的哈希碰撞问题。
2. 二叉搜索树，会产生单边树问题（连最基本的自增主键效果都很差），树高过高，浪费空间。
3. AVL，自平衡二叉查找树，相对于二叉查找树效果有所改善，但还是有树高问题，IO操作高。
4. B树，page–16kb。
5. B+树，平均查询次数低，查询时间稳定（稳定大于一切）。非叶子节点可以存储更多的索引（每次IO获取更多的索引），全表扫描直接到叶子节点就行，叶子节点有链表，支持范围查询。

SQL优化

1. 建立高效且合适的索引。明确业务场景，建立合适的索引。
2. 排查连接资源未显式关闭的情形。
3. 合并短的请求。利用CPU的空间局部性原理。
4. 合理拆分多个表join的SQL，若是超过三个表则禁止join。join带来笛卡尔积。
5. 使用临时表。把中间结果存到临时表，然后重建索引，再通过临时表进行后续的数据操作。
6. 应用层优化。
7. 针对业务场景选用合适的数据库。

慢SQL

MySQL数据库结构

MySQL数据库结构
执行器调用存储引擎API获取结果