深入浅出MySQL

深入浅出MySQL

以下内容参考自 《MySQL是怎样运行的：从根儿上理解MySQL》一书，强烈推荐

存储引擎

对于不同的表可以设置不同的存储引擎

CREATE TABLE tableName (xxxx
) ENGINE = 引擎名称;
# 修改
ALTER TABLE tableName ENGINE = xxx;

编码格式

mysql中的utf8默认的是使用的自定义的1~3字节表示的uft8mb3，对于一些特殊的字符，比如emoji，需要我们指定为utf8mb4才能够存储。

CREATE / ALTER DATABASE 数据库名CHARACTER SET 字符集名称COLLATE 比较规则名称
# 或者对于表来修改CREATE TABLE tableName()CHARACTER SET 字符集COLLATE 比较规则
ALTER tableName CHARACTER SET 字符集名称
## 或者对于某一列
CREATE TABLE 表名(
列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
其他列...
);
ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

InnoDB

物理存储结构

1. 页
   将数据划分为页，以页为单位作为磁盘和内存交互的单位，默认页大小为16KB
2. 行结构
   记录的单位是行。

行格式

行/记录格式有很多，可以在建表的时候指定行格式

CREATE TABLE tableName(
xxxx;
) ROW_FORMAT=COMPACT;

1. COMPACT格式 （重点，最常用）

   

• 变长字段长度列表，只会存放变长字段的长度
  支持VARCHAR等变成字段类型的，结构

1. 真正的数据内容
2. 占用的字节数
   所有变长字段的真实占用长度，按照列顺序的逆序来进行存放。
   这个长度占用的字节数：
   如果可变字段允许的最大字节数超过255字节，并且真实存储的字节数超过127字节，就使用两个字节来表示这个长度，否则使用一个字节来表示。

• NULL值列表
  Compact将列中的NULL值统一进行管理。而不是放在真实数据里面，从而减少存储占用
  进行的流程：

1. 统计允许存储NULL值的列有哪些，如果不存在，NULL值列表就不存在了
2. 表示形式：使用1表示为NULL值，0表示不为NULL，按照逆序排序。
3. 要求NULL值列表必须使用整个字节的位来表示，如果不足位数，就在最前面补0

   

• 记录头信息

  

• 记录的真实数据

  记录的真实数据除了会有我们定义的数据，还会有MySQL为每一条记录添加的一些隐藏列

  

  1. row_id 行id，唯一标识一条记录
     2.(trx_id) transaction_id 事务ID，MVCC中会使用
  2. roll_pointer 回滚指针，事务回滚会用到，undo_log相关

• 主键选取：
  优先使用用户定义的主键，如果没设置就选择一个Unique的键作为主键，如果不存在这种，就生成一个隐藏的row_id作为主键，所以 row_id不是 必须的

对于CHAR(M)，MySQL会为分配大于这个值的空间，并且要求至少占用M个字节，即使存的是一个空字符串也会占用M个字节，而VARCHAR(M)没有这个要求。

目的是：如果后续更新CHAR(M)的大小，就无需分配一个额外的记录空间，直接在原记录上进行更新即可。就不会造成碎片空间。

• Redundant行格式(老东西，不常用了)

溢出数据存储

可变数据类型需要占用3部分的存储空间：

1. 真实数据
2. 真实数据占用字节的长度
3. NULL值标识，如果该列有NOT NULL属性则可以没有这部分存储空间
   如果要存储的列非常大 ，只会保存实际真实数据的一部分，把剩余的数据分散在几个其他页中。

数据页(重点，重中之重)

大小16KB

• File Header 文件头部，记录页的一些通用信息
• Page Header 页面头部，数据页专有
• Infimum + Supremum 最小记录和最大记录， 虚拟行记录
• User Records 用户记录 实际存储的行记录内容，一开始没有，每次从Free Space中分配空间
• Free Space 空闲空间 页中未使用的空间
• PageDirectory 页面目录 页中某些记录的相对位置
• File Trailer 文件尾部 检验页是否完整

记录头信息

1. delete_mask 标记记录是否被删除，先做一个标记实际还在磁盘里还没删除。所有删除掉的记录会组成一个垃圾链表，如果后续有新的记录插入表中，可能会直接覆盖这些被删除的记录空间
2. min_rec_mask B+树的每层非叶子节点中的最小记录都会添加该标记
3. n_owned 表示当前记录拥有的记录数
4. heap_no 表示当前记录在记录堆的位置信息。
5. record_type 记录类型 0普通记录， 1 B+树非叶子节点记录 2最小记录 3最大记录
6. next_record 表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量。 指向这个位置，向左就是记录头信息，向右读就是真实数据，同时因为变长字段列表和NULL值列表都是逆序存放的 ，所以可以使得真实数据和他们对应的长度在内存中的地址更近，能够提高缓存的命中率

最小记录和最大记录

最小记录heap_no = 0
最大记录heap_no = 1
 

这两个记录是自动生成的，所以不放在User_Record中
链表中的节点是按照主键值从小到大的顺序连接起来的

MySQL是如何进行查找的

对于主键查找记录：

select * from user where id = 3;

我们知道MySQL中的记录是根据链表从小到大连接起来的，那么如何快速从不支持随机访问的链表中找到我们需要的数据呢？

Page Directory

MySQL中的设计：

1. 将所有的正常记录(不包括最小和最大记录)划分为多个组
2. 每个组的最后一条信息记录的n_owned记录这个组中有多少条记录
3. 将每个组的最后一条记录的地址偏移量单独提取出出来按照顺序存储到靠近 页 尾部的地方，也就是Page Directory页目录，这些偏移量被称为Slot 槽
4. 最小记录为单独一个组，最大记录所在的分组条数只能在18条，其余分组只能有48条记录

查找过程

1. 根据槽列表通过二分法来计算中间值，默认low 是最小记录的值，也就是0， high是最大记录的偏移量
2. 通过对比中间槽的偏移量的值快速定位到所在的记录的位置，比对这条记录的主键值
3. 对比之后接着通过二分法反复定位，直到 heigh - low = 1时，也就确认所需要的记录的数据所在的组
4. 通过遍历链表找到该槽中的主键值值最小的那条记录，也就是上一个槽所对应的那条记录的下一条
5. 通过next_record即可遍历该槽所在的组的各个记录

Page Header 页面头部

All problems in computer science can be solved by another level of indirection计算机科学中的所有问题都可以通过增加一个间接层来解决

在上文中，我们已经可以在一个数据页内部快速定位到我们所需要的记录，但是一张表中不仅仅存在一个数据页，如何快速定位到我们所需要的数据页呢?答案是再加一层抽象：对于每一个下层，我们都对其进行抽象，屏蔽掉其内部细节，方便上层使用。

Page Header 位于页结构的第二部分，用于存储页总的各种信息，比如第一条记录的地址是什么，本页存储了多少地址，页目录存储了多少个槽

主要的信息：

• FIL_PAGE_SPAE_OR_CHECKSUM：校验和，通过算法来计算一个值，方便我们去比较
• FIL_PAGE_OFFSET：页号，用于定位页
• FIL_PAGE_TYPE：页类型，存放记录的页就是索引页，也是数据页
• FIL_PAGE_PREV和FIL_PAGE_NEXT：用于组成双向链表

File Trailer

1. 存储页的校验和：与Header部分中的校验和对应，如果同步磁盘中同步到一半就失败了，那么Header中的校验和就会变成已经修改后的校验和，而Trailer的校验和还代表着原来的校验和，从而确定同步出现错误
2. 页面最后被修改时对应的日志序列位置LSN

数据页结构总结

• File Header
• Page Header
• Infimum + Supremum
• User Records
• Free Space
• Page Directory
• File Trailer

索引！

以下部分是重中之重，可以说这篇文章就是为了这里才有写的必要的
指面试的时候因为忘了而被疯狂拷打

回顾

1. InnoDB中数据页通过双向链表连接起来
2. 数据页内部的记录通过双向链表连接起来，并且按照主键从小到大进行排序
3. Page Directory 保存了每组记录最后一条记录的偏移量，方便我们快速定位到每个组

   

索引为什么会出现

1. 对于主键查找，我们可以使用设计快速定位，而对于其他列，我们没有这种方便的方式可以快速定位
2. 对于不同的页，我们也没办法快速定位到满足查询条件的记录所在的页在哪里，从而只能一个一个进行查找

索引的结构与实现原理

1. 数据页链表中，后一个数据页中的主键值必须大于上一个页中用户记录的主键值。
2. 仿照Page Directory，将数据页中的链表项建立目录
3. key为数据页中最小的主键，page_no为页号

   

MySQL中的实现

1. 使用数据页来存储目录项，通过record_type(0用户记录，1目录项记录，2最小记录，3最大记录) 来与用户记录进行区分

   

2. 一个数据页中存储的记录是有限的，所以需要使用链表的形式将目录串起来，保证后一个页中的页号要大于前一个
3. 为了方便我们使用二分查找从目录链表中快速定位，我们可以再次将其使用目录记录

   

4. 反复之后就形成了B+树

B+树和B树的区别：B+树只在最底层的节点上存储真实数据，其余都是用来存储目录项的，B树的任何一个节点都能保存数据

聚簇索引(重点)

定义：

1. 使用主键值的大小进行记录和页的排序：
   • 页内记录按照主键大小的单向链表
   • 存放用户记录的页根据主键大小排成双向链表
   • 存放目录项的页也是同一层次排成双向链表
2. 叶子节点存储的是完整记录(包括隐藏列)
   索引即数据，数据即索引。

二级索引

1. 使用非主键值作为排序标准
   • 同聚簇索引，不过标准是我们使用的非主键
2. B+树的叶子节点存储的是 这个列 + 主键两个列的值
3. 目录项记录存的是 这个列 + 页号 + 主键

   

   查询过程：
   例如查询c2的值为4的记录：
4. 根据页44， 2 < 4 < 9，可以定位到页42
5. c2没有唯一约束，所以4可能在多个记录中，由此对比可以确定应该在页34和页35，因为 2 <4 < 5
6. 定位到具体的记录
7. 通过具体记录中的主键值进行回表操作—> 也就是根据主键值去聚簇索引中再查找一遍完整的用户记录

回表的好坏

优点：
可以不用重新存储完整的数据，减少空间占用
缺点：
回表会浪费额外的时间

联合索引(重点，加个书签)

比如对c2、c3建立联合索引

1. 记录和页按照c2进行排序
2. 在这个基础上对c3进行排序
3. 叶子节点存储的是c2、c3和主键的值
4. 仍然是一个二级索引，也是需要回表的

MyISAM索引方案的差别

1. InnoDB使用索引即数据，而MyISAM将索引和数据分开存储。
2. 将数据存在一个文件中，使用行号来快速访问，而不是使用主键值进行排序，无法进行二分查找
3. 索引文件中存储的是主键值 + 行号，也就是所有查询都需要回表，全是二级索引
4. 联合索引存储的也是行号 + 相应的列

创建和删除索引的sql

CREATE TABLE 表名(列信息,KEY / INDEX 索引名 (需要被索引的单个列或者多个列，联合索引使用 , 隔开列)
)
ALTER TABLE 表名 ADD INDEX / KEY 索引名 (列)
ALTER TABLE 表名 DROP INDEX / KEY 索引名

索引的一些常见八股和如何合理使用B+树索引

如何避免回表？

需要回表记录越多，使用二级索引的性能就越低。
覆盖索引：
在查询列表中只包含索引列，即可避免回表。所以一般不建议使用 * 作为查询列表，最好把需要查询的列都依次标明

访问方法

全表扫描

索引扫描

p139，接着看

最适合建立索引的场景

1. 全值匹配：搜索条件的列和索引列一致，可以快速使用索引
2. 联合索引最左匹配原则
3. 前缀匹配 abc%
4. 索引列的范围查找
5. 对查找出的索引列数据进行排序：索引列本来就是基于排序的，所以可以不需要再内存或文件中进行排序

索引失效的场景

1. LiIKE操作符以通配符开头例如 “%xx”或 “%xx%” ，但是 “xx%” 可以使用索引。
2. 对索引使用函数或者表达式操作 select * from t_user where length(name)=6 ， 因为索引存的是原始值
3. 对索引隐式转换，如果查询条件中的类型和列的类型不匹配，MySQL可能会进行类型转换，索引就会失效。因为索引存的是原始值
4. 联合索引非最左匹配，多个普通字段组合在一起创建的索引叫做联合索引，不遵循最左优先的方式就会失效。
5. where子句中使用了OR，如果OR后的条件不是索引列就会失效
6. 出现NULL值：不一定不走索引 ，需要看查询的cost和优化器的选择。
7. 联合索引没有对最左列进行范围查找
   为何失效
8. like %xx：我们索引是根据列的值大小进行排列的，也就是说，例如我们对 小写字母建立索引，那么我们的索引全是基于 a -> z的大小顺序排列的，同时我们是按照字典序的方式进行排序，也就是说如果前一个相同才会去根据后面的字母进行排序，也就是说当我们匹配abc%xxx的时候，我们可以顺着这个索引去查询，而我们匹配 %abc时，我们无法确认%之前的的字母是什么，于是我们必须走一次全表匹配所以无法走索引去去匹配
9. 使用函数修改的列不满足最原始的排序了
10. 联合索引，通过上文，我们可以很清楚的明白联合索引实际上就是先按照列的顺序去建立索引，只有前一个列相同，我们才会根据后一个列进行排序，也就是说，我们只有这种顺序能够保证是按照大小排序。如果我们没有遵循最左匹配，也就是将顺序倒置，那么后面的列是不能满足大小排序的，也就无法走索引
11. 联合索引只有最左列是完全按照大小排序的，同时数据是基于链表，也就是我们可以很轻易将最左列的范围查找取出。

建立索引时应该考虑什么？重点

1. 只为用于搜索、排序、分组的列建立索引，出现在查询列表中的列就没必要建立索引了，因为走不走索引还是看查询条件、排序条件、分组条件，而不是查询的列
2. 考虑列的基数(不重复的数据的个数)，为基数大的列建立索引效果更好
3. 索引的类型尽量小，因为数据类型越小，查询时占用的存储空间越少，查询速度越快，数据页中可以放下更多的记录。
4. 对于一些较长的字符串，可以只对其前缀建立索引

   CREATE TABLE person_info(name VARCHAR(100) NOT NULL,birthday DATE NOT NULL,phone_number CHAR(11) NOT NULL,country varchar(100) NOT NULL,KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
   );

5. 让索引列在比较表达式中单独出现：这个和使用函数去修饰索引列是一个问题，会改变索引列的原来的形式，从而不能够走索引。例如:

   WHERE c1 * 2 < 4
   替换为
   WHERE c1 < 4 / 2

6. 插入数据时应该注意主键的顺序，因为当数据页满了，再插入记录时，会导致页分裂->将本页中的一些记录移动到新创建的野种，从而需要将记录转移，带来性能消耗，所以推荐让主键具有AUTO_INCREMENT

连接

循环嵌套连接

两表连接，驱动表只访问一次，而被驱动表要访问多次。
左外连接左边的是驱动表。右外连接右边的表是驱动表
然后每一个被驱动表其实都是一次循环，多个表循环嵌套，驱动表的每一行都要去遍历被驱动表。这种连接被称为嵌套循环连接。

事务

redo log

redo log 会把事务在执行过程中对数据库所做的所有的修改都记录下来，之后系统崩溃重启后可以把事务所做的任何操作都回复出来。

格式

• type：表示该redo日志的类型
• space ID：表空间ID
• page number：页号
• data：该条redo 日志的具体内容
  刷盘时机：
• 当log buffer空间不足时，当redo log占满了log buffer的一半左右
• 事务提交时
• 后台线程自动刷新，约是每秒一次
• 正常关闭服务器时

redo log 如何保证事务的完整性的

undo log

undo log 是单独存储的

• trx_id事务id
  被删除的记录也会通过记录头信息中的next_record组成一个单项链表，然后指向PAGE_FREE空间

  

  事务提交之前，被删除的记录的delete_mask会被设置为1，但是不会被加入垃圾链表中，也就是会处于中间态

MVCC

只靠MVCC不能解决幻读，需要额外使用锁

实现原理

1. 每一行的数据都有多个版本，更新时不会覆盖原来的数据，而是生成新的版本。
2. 读操作根据ReadView去读
3. 写操作，旧的版本不会被删除，而是放在垃圾链表，将原来的数据写入undo log之后，通过roll_id指向这一行的undo log

事务隔离等级和几个并发会出现的问题

并发常见到的问题

1. 脏读：一个事务读到了另一个未提交事务修改过的数据
2. 不可重复读：一个事务两次读取读取到的数据不同，也就是两次读取之间被其他事务修改了数据
3. 幻读：相同的查询，查出来的结果不同
   几个等级：
   脏写 < 脏读 < 不可重复读 < 幻读

事务隔离等级

1. 读未提交：允许一个事务读取另一个事务未提交的数据 阻止不了上面几种
2. 读已提交：只允许一个事务读取另一个事务已经提交的修改，可以阻止脏读
3. 可重复读：保证同一个事务多次读取获得的数据是相同的，避免了脏读、不可重复读
4. 可串行化：完全隔离事务，确保事务按照顺序执行，彷佛他们是串行执行的

版本链

rolle_pointer：每次对某条聚簇索引进行改动时，会将原来的旧版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改之前的信息。

每次对记录进行更新后，都会将旧值放到一条undo日志中，就算是该记录中的一个旧版本，随着更新次数的增多，所有的版本都会被role_pointer连接成一个链表，版本链的头节点就是当前记录的最新值，另外，每个版本还包含该版本时对应的事务id

ReadView

如何判断一条记录是否可以被某个事务可见。ReadView的内容

• m_ids：表示生成ReadView时当前系统中活跃读写事务id列表
• min_trx_id：表示生成ReadView时，当前系统中活跃的最小事务中的最小事务id，也就是m_ids的最小值
• max_trx_id：生成ReadView时系统应该分配给下一个事务的id值。
• creator_trx_id：表示生成该ReadView的事务的事务id
  当事务访问某条记录时，进行一下判断访问是否可见：
• 被访问版本的trx_id == ReadView中的creator_trx_id时，意味着当前事务访问的是自己修改过的记录，可以被当前事务访问
• trx_id < ReadView中的min_trx_id，表示该版本是在当前事务之前旧已经提交了，可以访问
• trx_id > max_trx_id，表示当前记录的事务是在当前事务之后才开始的，不能访问
• min_trx_id < trx_id < max_trx_id，说明创建ReadView时，该版本的事务还是活跃的，需要从m_ids中判断是否存在，如果存在就不能访问，否则就可以访问
  如果某个版本的数据对于当前的事务不可见，就沿着版本链去找下一个版本的数据，如果版本链中所有的数据都不可见，才意味着这条记录对当前事务不可见，查询条件就不会包括这条记录。

读已提交和可重复读的区别

区别就是二者的ReadView生成时机不同：

• 读已提交在每次执行查询语句时生成一个ReadView，此后不会再重复生成了
• 可重复读只在第一次进行查询语句时生成一个ReadView，此后查询操作都重复使用这个ReadView，从而做到保证多次读取读取到相同的数据。

锁

锁的结构

对于每一条记录，都会有一个锁对应。当一个事务相对这条记录做改动时，首先会看看内存中是否有与这条记录想关联的锁结构，如果没有就需要生成一个锁结构与之关联。
所结构中的最重要的两个属性：

• trx信息：这个锁结构是由哪个事务生成的
• is_waiting：代表当前事务是否在等待

1. 获取锁成功/加锁：当事务修改这条记录时，如果不存在锁，就会在修改记录之后生成一个锁机构1，trx是这个事务的id，is_waiting是false。
2. 获取锁失败：当其他事务尝试修改这条记录时发现存在了如果发现已经存在了这个锁1，那么就会给自己生成一个锁2，但是is_waiting是true，表示需要等待。
3. 当事务1正常提交之后会把该事务生成的锁结构1删除，然后查看是否还有其他别的事务在等待获取锁，发现事务2在等待，于是就将锁2的is_waiting设置为false，之后将这个事务的线程唤醒，继续执行。

一致性读

事务使用MVCC进行读取被称为一致性读，或者一致性无锁读，也叫快照读。

锁定读

行级锁对于读写冲突问题，MySQL使用了MVCC+锁来解决

1. 共享锁：Shared Locks S锁，读取记录时需要先获取该记录的S锁
2. 独占锁：也叫排他锁， X锁，修改记录时需要鲜活的该记录的X锁。
   主动加锁的sql

   SELECT ... LOCK IN SHARE MODE; # 加S锁
   SELECT ... FOR UPDATE; # 加X锁

多粒度锁

上文提到的都是行级锁

• 意向共享锁 IS锁：表级锁
• 意向独占锁 IX锁：表级锁
  这两个锁时为了之后对表添加表级别的S锁和X锁时可以快判断表中的记录是否被上锁，以免需要使用遍历来确定是否有记录被上锁了。

InnoDB中的锁

表级锁

• 表级的S锁、X锁：
• 表级的IS锁、IX锁：
• 表级的AUTO-INC锁：自增AUTO_INCREMENT修饰的列递增赋值

行级锁(重点)

• Record Locks：正经记录锁，分为S锁和X锁
• Gap Locks：间隙锁，加锁方案解决幻读的关键
• Next-Key锁：简单来说就是 record locks + gap locks
• Insert Intention Locks：事务在等待的时候也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录。叫做插入意向锁，目的是避免插入操作的相互阻塞

Gap Locks

对于幻读中出现的幻影记录，我们没法加锁，因为他们还未存在。间隙锁就是所著一个范围内所有的间隙，从而阻止其事务在这些间隙中插入数据

八股

InnoDB为什么是默认引擎

1. 唯一支持事务的引擎
2. 行级锁
3. 支持外键约束
4. 崩溃恢复，redolog和undolog

为什么InnoDB使用B+树

1. 支持快速查找：平衡多路查找树，高度较低
2. 有序性：翻遍范围查找分组
3. 插入和删除更搞笑：使用双向链表
4. 适应磁盘存储：节点的大小控制在磁盘页面大小范围内，减少I/O操作
5. 支持有序查找和范围查找

MVCC

多版本并发控制

1. 每一行的记录都维护多个版本，每一行更新时，不会覆盖原来的数据，而是会生成新的版本
2. 读操作只读事务创建之前的