知识备忘录——关系型数据库

本文对《高性能MySQL（第三版）》进行了粗略节选与总结，并参考了聚簇索引与非聚簇索引（也叫二级索引）--最清楚的一篇讲解 (opens new window)和MySQL索引底层：B+树详解 (opens new window)。

关系型数据库

关系型数据库为数据管理提供了不同于文件系统的方法。通过数据表建模的方法，数据库实现了数据的元信息及其之间联系的表达，屏蔽了数据管理的部分繁琐操作，并提供了一层对数据操作的抽象，使得数据能够被有组织地存储、处理和使用。

MySQL架构

首先通过一张结构示意图了解MySQL的架构：

        客户端      （连接处理、授权认证、安全等）
        ⬇ ⬇ ⬇              
---------------------
  连接/线程处理   
  ⬇         ⬇
查询缓存⬅解析器     （内置函数、存储过程、触发器、视图等）
            ⬇
         优化器      
-------(API↕)---------
        存储引擎    （数据存储与提取）

基于这个架构，又有如下的设计约束：

• 每个客户端在服务器中都有一个线程，其查询均在这个线程上执行；
• 连接的线程通过缓存方式避免创建和销毁的开销；
• 服务器对客户端认证后根据身份赋予特定权限；
• 查询会被解析、优化（重写、决定表顺序、选择索引）；
• hint可用于修改优化过程、explain可用于获取优化器决策；
• 优化器不关心存储引擎，但存储引擎影响优化，因而需要根据存储引擎进行优化；
• SELECT会检查缓存并优先使用缓存结果；

并发控制

MySQL使用服务器层与存储引擎层控制并发。MySQL通过锁机制实现并发控制。对于同一个资源，使用锁可以使得某些特定的线程访问它。

MySQL通常使用两类锁——读锁（共享锁，允许多个线程访问，不阻塞）与写锁（排他锁，阻塞其他线程的读写，保证并发安全），这些锁有粒度策略的区分，以平衡资源消耗和安全性，例如行锁、表锁等。

• 表锁：锁定整张表，开销最小。对其他读写进行阻塞，有更高的优先级（插入到读前）。
• 行锁：锁定行，提高并发性能，锁开销最大。InnoDB实现在存储引擎层。行锁包含共享锁和排他锁两类。
• 页锁：页锁介于表锁和行锁之间，可以被认为是对表中一部分行的锁，通常一个页与聚簇索引的一个文件页有关。

死锁

死锁是指线程之间相互等待的资源被对方锁住并不被释放的情况，死锁需要被尽可能地避免，当无法被避免且发生时，数据库只能通过回滚其中一个事务（InnoDB中使用有最少行锁的事务）来打破死锁。关于死锁，将在操作系统知识点总结中讨论。

事务

事务是MySQL的独立工作单元，是一组被视为单一操作的数据库操作，例如从操作一个用户的账户转移金额到另一个账户。事务以START TRANSACTION开始，以COMMIT提交或通过ROLLBACK撤销。

ACID特性

事务具备被称为ACID的特性：

• 原子性（Atomic）：事务内所有操作只能全部成功或全部失败，内部的操作不能被分割，如同原子一般被视为一个整体；
• 一致性（Consistency）：数据库的状态总是从一个一致的转换到另一个一致的，如账户金额的总和不会因为转账成功与失败而发生变化。
• 隔离性（Isolation）：事务之间不会相互影响，一个事务的执行不应该影响另一个，如A事务不应该在执行过程中受到B的影响。
• 持久性（Durability）：事务被执行后产生的修改会被保留在数据库中，如修改的金额在数据库再次启动后与上一次的进程中一致。

这四个特性保证了事务的可靠性，使得数据可以被安全地处理和存储。除此之外，关于事务MySQL还有如下设计：

• MySQL使用了事务日志，将数据的修改首先存储在内存中再逐渐存储到硬盘，而把修改的首先操作存储在硬盘上，避免了在硬盘上频繁修改数据内容带来的性能问题。事务日志采用顺序写方式，加速了读写。
• MySQL默认自动提交。自动提交将每个操作都作为一个事务执行。

隔离级别

为了保证数据的隔离性，即使得事务之间不相互影响，MySQL使用了四种隔离级别：

• 读未提交（Read Uncommitted）：对于其他事务中的修改，即使那个事务没有提交其中的修改也会被读取到，即脏读。这会导致数据一致性受到影响，在性能上也没有明显优势，因而很少被使用；
• 读提交（Read Committed）：事务只能看到已经提交的修改，即解决了脏读的问题。但是由于读取的过程中，其他事务可能被提交，两次读同一个数据的结果可能不同，即不可重复读，读出内容不必然相同。
• 可重复读（Repeatable Read，MySQL默认级别）：可重复读解决了同一事务中两次读到同一行的数据因为其他事务提交修改不一致的问题。但是如果读取的范围内的数据被其他事务新插入的记录修改，则会产生幻读（Phantom Row）。幻读问题的解决可以通过多版本并发控制（MVCC）解决。
• 串行化（Serializable）：事务的执行成为线性的、非并发的，解决了所有读不一致的问题，但是采用了加锁读的机制，影响了并发性能。

总结一下各类问题与隔离级别的关系：

问题	描述	解决方法
脏读	读到未提交的事务数据	读提交
不可重复读	两次读同一行数据不一致	可重复读
幻读	读出内容因新增行不一致	MVCC

多版本并发控制（MVCC）

MVCC在实现非阻塞读和仅锁定必要行的同时，避免了加锁带来的性能开销。InnoDB通过每条记录后的两个隐藏列实现MVCC，这两列为行的创建时间和过期时间，其值为系统版本号（版本号随新事务产生而自增）。对于读写操作，InnoDB的MVCC有以下约束：

• SELECT（同时满足以下两个条件）：
  • 所查找的行版本号等于或小于当前版本号（保证了读取的数据是已经存在或自己修改的）；
  • 删除版本号只能未定义或大于当前事务号（保证读取的行在事务开始之前未被删除）；
• INSERT：
  • 在新增记录时设置新增行的创建版本号为当前系统版本号；
• DELETE：
  • 在删除记录时设置删除行的版本号为当前系统版本号；
• UPDATE：
  • 插入新记录并设置创建版本号为当前版本号，为删除的行设置删除行为当前版本号；
• 只在读提交和可重复读两个两个级别可用；

存储引擎InnoDB

InnoDB是MySQL的默认引擎，具有自动回滚功能的事务型引擎。

• 使用MVCC，实现了四个隔离级别，默认为可重复读；
• 使用聚簇索引建立，二级索引必须包含主键列；
• 通过可预测性预读、自适应哈希索引、插入缓冲区等机制优化性能；
• 支持热备份，在不停机的情况下实现备份；

间隙锁

间隙锁在可重复读的隔离级别下引入，用于解决幻读问题。

索引机制

索引机制的目标是减少数据检索中的磁盘IO，并提升查找数据的速度。索引的实现被分为聚簇索引和非聚簇索引。

聚簇索引

将数据存储与索引放到了一块，找到索引也就找到了数据。由于聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引。聚簇索引将主键组织到一棵B+树中，而行数据就储存在叶子节点上。

• 聚簇索引内的行数据会在一次读取中加载到buffer中，从而加速多次读取（使其从内存而非硬盘读取），找到叶子节点即可立即返回行数据。
• 聚簇索引通过主键进行索引，因而适合以下数据功能：
  • 由于聚簇索引将主键放置在一起且一次读出到内存中，排序可被加速；
  • 相似内容查询，对于同一类内容，聚簇索引能加速查询未被查询过的相似数据；
• 聚簇索引维护代价昂贵，在插入或更新主键时会导致分页进而带来开销。
• 使用UUID会导致数据稀疏和插入代价升高，进而减慢速度。（使用自增int主键避免）

非聚簇索引

将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行。辅助索引通常是非聚簇索引，叶节点存储的是主键值。对于MyISAM，叶子节点是磁盘IO位置。

• 由于存储与主键在索引上分离，因此适合处理频繁更新的数据（聚簇索引会因为更新产生更多的分页）。

B+树

B+树和其他树一样，是一种层次型数据结构。在MySQL中，B+树被用作索引的底层结构。根据使用特性，我们可以知道B+树是一种适合查找的树，其定义如下：

• 每个节点至多有m个子节点；
• 非根节点的关键值个数为[ceil(m/2)-1, m-1]；
• 相邻叶子节点通过指针链接并以关键值大小排序；

由以上这些特性可以得知B+树适合作为索引的原因有：

• 叶子节点由指针链接，并且按关键值排序，适合范围查找；
• 始终有多个子节点，不会退化为链表，因而不会导致全表扫描；
• 有至多m个子节点，因而树的深度会比红黑树更低，并减少硬盘IO；
• 由于B-树会在叶子节点和非叶子节点保存数据，B-树需要更多空间作为索引；