MySQL学习笔记

1.基本概念

数据库：是文件的集合，是依照一定数据模型组织起来并持久化保存在存储介质中的数据集合。

在MySQL中包含：数据文件，索引文件，日志文件（binglog，redo log，undo log，慢查询日志）。

数据库实例：指运行的数据库管理软件程序，用户或者应用程序通过数据库实例来操作数据库的数据（文件），而不是直接操作数据库的数据（文件）。

2.数据库设计的三大范式是什么？

第一范式（1NF）：列不可再分。数据库中的字段具有原子性，不可再分，是单一职责。如果两个列的属性相近或一样，需要合并成一列。

第二范式（2NF）：在第一范式的基础上，属性完全依赖于主键。每一行的数据只与主键相关，当列中出现数据重复时就要从该表中拆分出来，这个表中只记录新表中的主键。

第三范式（3NF）：在第一、第二范式的基础上，属性不依赖于其它非主属性，属性直接依赖于主键。确保每列都和主键直接相关，需要记录其他表的属性时，只记录其他的主键。

三大范式的主要意义是尽量减少数据冗余，减少磁盘使用量。不用处理冗余数据，很容易实现增删改逻辑。但是查询实现起来非常复杂，需要组合多个表的结果。

现今的系统查询的量远大于增删改的量，对查询的性能要求很高，存储变得很便宜，如今数据库很少严格按照三大范式进行设计，通常增加冗余字段、减少联表查询来提高查询性能。

3.MySQL体系结构

MySQL是一个单进程多线程架构的数据库。

MySQL的组成部分是：

（1）连接池组件

（2）管理服务和工具组件

（3）SQL接口组件

（4）查询分析器组件

（5）优化器组件

（6）缓冲组件

（7）插件式存储引擎

存储引擎是基于表的，不是基于数据库的。

（8）物理文件

4.MySQL不同的存储引擎的比较

5.InnoDB存储引擎的整体架构 

 

 

后台线程主要负责刷新内存池中的数据，保证缓存池中内存缓存的数据是最新的，将已修改的数据文件刷新到磁盘中，保证数据库发生异常时InnoDB能恢复到正常运行状态。

InnoDB使用磁盘持久化存储数据，CPU与磁盘速度相差非常大，使用内存缓存池技术提高整体性能。在生产环境使用64位操作系统，尽可能分配多的内存资源。

内存缓冲池中不仅仅只有数据页和索引页。缓冲池的最小单位为页，数据页和索引页会分成三个列表：空闲页列表，脏页列表，LRU列表。InnoDB通过LRU算法（最近最少使用算法）管理缓冲池中非空闲的数据页和索引页。

InnoDB在传统LRU算法的基础上做了改进，增加了增加midpoint insertion strage策略。

使用最频繁的页放在LRU列表的前端，使用最少的页放在尾端，当缓冲池满的时候会将尾端的页的数据释放，用于存放新的数据，插入到LRU的midpoint位置，如果在一定时间内InnoDB检测到这个页频繁使用，会把这个页调整到LRU列表热端。这样处理是为了避免一两次查询的数据缓冲页把真正的热点页从LRU热端挤出来，影响缓冲池的效率。

6.表的相关文件

表的结构定义文件放在frm格式的文件中。

表的数据文件，在InnoDB下存储在ibd格式的文件中，称为表空间文件。数据、主键索引、辅助索引（二级索引）都放在ibd格式的表空间文件中。不同的表数据可以放在共享表空间文件下，也可以放在单独的表空间文件下，通过innodb_file_per_table参数控制。

当启用innodb_file_per_table参数时，各个表自己的表空间文件存放自己的数据页、索引页和插入缓存位图页，undo log页、插入缓冲索引页、系统事务信息、二次写缓冲（Double Write Bufer）还是存在共享表空间文件中。

7.InnoDB对CHAR类型列的处理方式是怎样的？

与表采用的编码格式和行采用的记录格式都有关系。

在表使用LATIN编码格式的情况下，

（1）当行使用Compact格式时，如果值为NULL，在NULL标志位上设置为1，不占用存储空间。如果值不为NULL，NULL标志位上设置为0，占用存储空间，未填满的字节用0x20填充。

（2）当行使用Redundant格式时，值是不是NULL都会占用存储空间，存储空间大小都为表定义时列的长度，值是NULL用0x00填充，值不是NULL未填满的字节用0x20填充。

在采用多字节编码格式（GBK，UTF8等）时，Compact格式与VARCHAR的处理方式一样。Redundant格式与LATIN编码的处理方式一样，不为NULL时，固定使用的长度为最大字节数。

8.InnoDB对VARCHAR类型列的处理方式是怎样的？

值为NULL时，NULL标志位上设置为1，不占用存储空间。值不为NULL时，NULL标志位上设置为0，占用存储空间，只占用实际存储内容的字节数。

9.InnoDB的索引

InnoDB中表的数据按照聚集索引的形式存放，组成一颗B+树，非叶子节点都是索引页，按照高度可能会分成多层。孩子不是叶子的索引页中记录的是主键与索引页的对应关系，孩子是叶子的索引页记录的是主键与数据页的对应关系。

InnoDB必须有主键，如果表没有显示指定主键，InnoDB自动创建隐式的主键列，存入行记录的rowId列中。

InnoDB通过主键找到是该行记录所在的页，要获取该行的数据，需要到页里边通过页目录（Page Directory)才能找到该行，首先通过二分查找的方式找到槽，即链表的首节点，然后再遍历链表找到行。

InnoDB的辅助索引和辅助索引，以及数据都是存储在同一个idb文件中，辅助索引也是一颗B+树，没有存放数据行，存储的是索引key和主键的对应关系。通过辅助索引查找行，先找到主键，再通过主键找行，这个过程也称为回表查找。

10.InnoDB的事务和锁

事务的隔离性通过锁实现。

事务的原子性、一致性、持久性通过redo log和undo log实现。redo log保证事务的持久性，redo log和undo log保证事务的原子性、一致性。

undo并不是redo的逆过程。redo和undo都是用来做恢复操作。redo log是物理日志，记录页的物理修改操作，用于恢复提交事务修改的页操作。undo log是逻辑日志，回滚行记录到特定的版本。undo log也用于实现MVCC的功能。

InnoDB锁的种类：

行级锁

InnoDB的行锁加在索引上，主键索引、普通索引。

间隙锁

在可重复读隔离级别使用。

表级锁

在隔离级别不同，索引类型不同，加锁的方式也会不同。把行级锁和间隙锁合在一起称为next-key lock，先开后闭的区间，在加锁时分成两个锁执行，加间隙锁，加行锁。

在隔离级别为RR可重复读，加锁规则如下：

1）原则1：加锁的基本单位是next-key lock。

2）原则2：查找过程中访问到的对象才加锁。

3）优化1：索引上的等值查询，给唯一索引（包含主键索引）加锁的时候，next-key lock退化成行锁。

next-key lock的区间范围最后一个值与查询条件相等，退化成行锁。

4）优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化成间隙锁。

5）一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

注意以上规则适用于特定的MySQL版本，新版本可能会做调整。

RR隔离级别下的加锁案例分析

表结构和数据如下。

create table t (
id int not null,
c int default null,
d int default null,
primary key(id),
key(c)
)engine=innodb;
insert into t values (0,0,0),(5,5,5),(10,10,10),(15,15,15);

 

（1）在事务中，select * from t where d=5 for update;

分析：

（a）d列上没有创建索引，InnoDB通过主键索引采用全表扫描方式查询所有记录。

（b）根据原则2，主键上0,5,10,15都加上行锁。

（c）在d列上，根据原则1，加锁单位是next-key lock，加锁范围是(负无穷, +supremum]。

（d）此时索引c上并没有加锁，因为没有使用c索引访问。

（2）在事务中，select * from t where id=5 for update;

分析：

（a）InnoDB通过聚簇索引查询数据。

（b）在主键索引上，根据原则1，加锁单位是next-key lock，加锁范围是(0, 5]。

（c）根据优化1，id是唯一索引，这个是等值查询，且区间最后一个值与查询条件相等，退化成行锁，最终在主键5这一行加锁。

（3）在事务中，select * from t where id=6 for update;

分析：

（a）InnoDB通过聚簇索引查询数据。

（b）在主键索引上，根据原则1，加锁单位是next-key lock，加锁范围是(5, 10]。

（c）根据优化2，这个是等值查询，但是10不等于6，退化成间隙锁，加锁范围为(5, 10)。

（4）在事务中，select id,c from t where c=5 lock in share mode;

分析：

（a）读锁，InnoDB通过覆盖索引c查询数据，不会回表。

（b）在索引c上，根据原则1，加锁单位是next-key lock，加锁范围是(0, 10]。

（c）根据优化2，这个是等值查询，但是10不等于5，退化成间隙锁，加锁范围是(0, 10)。

（d）只使用覆盖索引，根据原则2，不会访问主键索引，主键索引上不会加锁。

（5）在事务中，select id,c from t where c=5 for update;

分析：

（a）写锁，InnoDB先通过覆盖索引c查询，然后使用回表查询。

（b）在索引c上，根据原则1，加锁单位是next-key lock，加锁范围是(0, 10]。

（c）根据优化2，这个是等值查询，但是10不等于5，退化成间隙锁，加锁范围是(0, 10)。

（d）通过c=5条件查到的id为5，然后使用聚簇索引查询，在主键索引上对5加行锁。

11.InnoDB update的执行过程

在不同的隔离级别，不同的索引下处理过程会有一些差异，主要是加的锁不一样。以隔离级别为RR，使用主键索引为例。

1)server层对update sql进行合法性校验，如果不通过直接返回失败。

2)server层通过InnoDB引擎开启一个新事务，分配事务id，（在InnoDB引擎内部事务id单调增加）。

3)server层通过InnoDB引擎获取行记录。InnoDB引擎检查主键对应的记录是否在Buffer Pool中，如果不在，从磁盘中读取对应的数据页，放入缓存池的LRU队列中。

4)在主键索引上，对该行加上行锁。

5)server层先计算出新记录的内容，再调用InnoDB引擎执行修改操作。InnoDB引擎生成undo log日志信息，写入磁盘，undo log存储在共享的表空间文件中；然后修改缓存池中数据页的内容，此时变为脏页状态，并不会立即写入磁盘。

6)InnoDB引擎生成redo log日志信息，即该页的物理日志，整个页修改后的数据，此时redo log状态为prepare，然后写入redo log缓存区，再写入redo log文件，通过参数控制写入文件和文件刷盘的行为。

7)server层生成binlog日志，写入binlog日志文件，通过参数控制刷盘行为。

8)如果连接断开、客户端长时间不发出commit/rollback指令、写入redo log日志失败，利用undo log回滚数据。

9)接受到commit后，redo log状态改为commit，写入磁盘，此时事务持久性已完成。这个时候缓存池中的数据页是脏页，还没刷入到磁盘的表空间文件，此时的redo log页不能被回收，当主线程将脏页写入表空间之后，这个redo log页就可以回收再次使用了，释放对应的undo log记录的占用权，Purge线程会回收undo log页，给其他事务再次使用。binlog和redo log的一致性通过两阶段方式实现。

12.读分类

快照读：读取的是某个快照版本的数据

当前读：读取的是最新的版本数据，包括以下方式

select xxx lock in share mode;

select xxx for update;

update xxx;

delete xxx;

参考资料

[1]《面试八股文》之 MySql 35卷：《面试八股文》之 MySql 35卷

[2]《MySQL技术内幕InnoDB存储引擎第2版》姜承尧著

[3]数据库设计三大范式，还记得吗？数据库设计三大范式，还记得吗？

[4]MySQL实战45讲 林晓斌