MySQL逻辑架构架构
- Parser: 解析器。SQL命令传递到解析器的时候会被解析器验证和解析。
- Optimizer: 查询优化器。SQL语句在查询之前会使用查询优化器对查询进行优化。
- MySQL会先解析查询，并创建内部数据结构（解析树），然后对其进行优化。对于select语句，服务器会先检查缓存。
并发控制
读写锁，或共享锁和排他锁
- 读锁是共享的，即不会阻塞其他线程
- 写锁是排他的，一个写锁会阻塞其他的写锁和读锁
锁粒度：表锁和行锁 注意：alter table是会锁定整张表，忽略存储引擎的锁策略
事务
为什么需要事务，举一个例子，银行转账的例子
事务的ACID特性
- 原子性(automicity)：事务为一个最小的不可分割的工作单元
- 一致性(consistency)：数据库总是从一个一致性状态到另一个一致性状态
- 隔离性(isolation)：通常来说，一个事务所做的修改在最终提交前，对其他事务是不可见的
- 持久性(durability)：一旦事务提交，其所做得修改就会永久保存到数据库中
事务的隔离级别
- 未提交读：事务中的修改，即使没有提交，对其他事务也是可见的
- 读已提交：一个事务开始后，只能看见已经提交的事务所做得修改
- 可重复读：保证同一个事务中多次读取相同的记录是一致的
- 可串行化：强制事务串行执行
两个概念
- 脏读：事务中的修改，即使没有提交，对其他事务也是可见的
- 不可重复读：同一个事务中，执行两次相同的查询，读取的记录不同
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交 | 会 | 会 | 会 |
| 读已提交 | 不会 | 会 | 会 |
| 可重复读 | 不会 | 不会 | 会 |
| 可串行化 | 不会 | 不会 | 不会 |
MVCC
InnoDB默认的隔离级别是可重复读，但可重复读没有解决幻读的可能性，而InnoDB通过MVCC（多版本并发控制）解决幻读的问题。 通过在每行记录后面保存两个隐藏的列来实现，一个保存行的创建时间，一个保存行的删除时间，实际存储的不是时间值，而是系统版本号。每开始一个新的事务，系统版本号会递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号比较。
select
innodb会根据以下两个条件检查每行的记录
- innodb只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始之前已经存在，要么是事务自身插入或修改的数据。
- 行的删除版本要么未定义，要么大于当前事务版本号，这可以确保事务读取到的行，在事务开始之前未被删除（即，这样做的目的是为了事务不会读取到被真正删除的行,删除版本号小于当前事务版本号的表示操作删除记录的事务已经提交–数据已经被删除，删除版本号大于当前事务版本号的表示这个事务是在当前事务之后开始的–当前事务开始时这些记录是还存在的，根据事务的隔离性，一致性要求，之后开始的事务操作的记录并提交，对当前事务不可见，所以还需要当前事务能够查询这些记录–只能够查询，不能够修改和删除）。
- 只有满足以上两个条件的才可以返回作为查询结果。
insert
innodb为新插入的每一行保存当前系统版本号作为行版本号
delete
innodb为删除的每一行保存当前系统版本号作为行删除标识
update
innodb为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识
索引
B-Tree索引
B+Tree索引
很多引擎使用的是B-Tree结构，但InnoDB使用的是B+Tree结构
B+Tree相对于B-Tree有几点不同：
- 非叶子节点只存储键值信息。每个逻辑页的存储空间有限，当数据区data较大时会导致B+树的高度过高，增加I/O
- 所有叶子节点之间都有一个链指针。方便对主键的范围查找
- 数据记录都存放在叶子节点中。
- 索引的最小前缀匹配，由于B+Tree的结构决定的
哈希索引
基于哈希表实现，因为是对所有索引列计算哈希码，所以只有精确匹配索引所有列才有效。
- 哈哈希索引只包含哈希值和行指针，所以不能避免读取行
- 哈希索引无法用于排序
- 哈希索引不支持部分列索引查找
- 只支持等值比较查询，不支持范围查询
- 适合选择性高的列建索引
有趣的是InnoDB会对基于B+Tree索引上使用频繁的索引值建立哈希索引，加快访问速度。
索引的优点
- 大大减少了服务器需要扫描的数据量
- 帮助服务器避免排序和临时表
- 讲随机I/O变为顺序I/O
高性能的索引 多列索引 选择合适的索引顺序 将选择性最高的列放到最前面
聚簇索引
- 提高了I/O密集型应用的性能
- 插入速度依赖插入顺序
- 更新聚簇索引列代价高
- 插入可能导致页分裂
- 全表扫描慢
- 二级索引更大
覆盖索引
- 索引覆盖了查询的所有字段
- 覆盖索引必须要存储索引列的值，所以InnoDB只能使用B-Tree索引
- Explain的Extra列的Using index的含义就是查询被索引覆盖的意思
- 使用索引扫描来做排序，type列为index即使用了索引扫描排序