基础

数据库的三大范式是什么

• 第一范式：数据库的列是原子的不可再分
• 第二范式：数据库的非主属性必须完全依赖于主属性
• 第三范式：数据库的所有属性不能依赖于非主属性

MySQL常用的存储引擎

• InnoDB（默认）
• MyISAM
• Memory

MyISAM和InnoDB的区别

• MyISAM不支持外键和事务，InnoDB支持
• MyISAM和InnoDB尽管都使用的是B+树作为索引的数据结构，但是MyISAM是非聚簇索引，数据项存储的是数据的指针，InnoDB是聚簇索引，数据项存放的是记录
• MyiSAM锁的粒度比InnoDB的更大，最低只支持到表锁；而InnoDB支持到行锁，所以其并发性能更好
• MyISAM支持全文检索，检索效率比InnoDB更高
• MyISAM在Count(*)语句中，由于内部存储了总行数，所以直接返回，而InnoDB却需要全表查询

MySQL的逻辑架构

• 网络层，主要负责客户端和连接器连接的细节，例如TCP三次握手，鉴权等。
• 服务层：里面包含了连接器，分析器、优化器、执行器和一些内置的函数。一些跨存储引擎的功能，例如视图、存储过程、触发器等都在这里实现
• 存储引擎层：主要负责数据的的存储和管理。5.5之后，默认的存储引擎为InnoDB
• 文件层：负责将和存储引擎层交互，将数据存储到磁盘中。

SQL的约束有几种

• unique 非重复
• primary key，主键约束，非重复不为空
• foreign key，外键约束，值必须指向具体表的一个属性
• not null：非空
• check：数据的范围

自增主键的理解

主键持久化

在8.0之前，是没有主键持久化的功能的，每次重启，自增主键的值取决于当前表中记录的主键最大值。8.0之后，具有了主键持久化。是借助redo log来实现的。每次重启，都会从redo log日志里面读取自增主键的值。

主键更新原则

• 当新增和修改的时候，如果不指定，那么就按照自增主键的值走。如果指定了合法的主键，那么就按照指定的插入
• 插入后的自增主键的值，等于Max(当前自增主键值，插入的自增主键值）+1

自增主键为什么往往会不连续

唯一值冲突

事务回滚

当记录插入时，自增主键+1，但是此时由于唯一键冲突，或者是事务回滚导致插入记录失败。而为了保证并发的同步性，自增主键设置为不回退，否则会导致ID冲突的问题，此时就会发生自增主键不连续的情况。

为什么InnoDB表必须有主键，还设置为自增主键

• 必须有主键的原因是，InnoDB优先会选择用户定义的主键作为主键，如果没有，那么就会以unique属性作为主键，如果还没有，那么会自己添加一个row_id的隐藏属性作为主键
• 当新增数据的时候，对于磁盘来说，找到写入的位置是很快的，因为已经明确知道要插入到当前主键值最大的下一个。顺序IO比随机IO开销要小。并且还可以防止频繁的分页合并，造成内存碎片的产生，和额外的花销

Varchar和Char的区别

• varchar是变长的是字符串，而char是定长的
• varchar需要有1到2个字节记录字符串的长度，如果大于255则使用2个字节。char适合存储定长的字符串例如md5加密的密码
• varchar采用非unicode编码，英语和汉字均占2个字节，而char的话，采用unicode编码，英语占1个字节，汉字占用2个字节。

mysql执行查询的过程

三种删除数据的区别

InnoDB记录结构

InnoDB的行格式

数据在磁盘上存储的方式叫做行格式，InnoDB默认的行格式为Compact

Compact行格式

• 记录的额外信息
  • 边长字段长度列表
    • 将变长的数据类型例如varchar占用的字节数存储起来
      • 占用的字节数
      • 真正的数据内容
  • NULL值列表：Compact将值为NULL的统一管理起来
  • 记录头信息：描述记录的头信息
• 记录真实的数据
  • 真实数据
  • 隐藏列（transaction_id和roll_pointer默认都有，row_id是可选的）

InnoDB表对主键的生成策略

• 优先使用主键，如果没有使用Unique属性的列，如果还没有，就自己创建一个隐藏列，row_id

InnoDB数据页结构

InnoDB将数据划分成若干个页，将页作为磁盘和内存交互之间的基单位，页的大小为16KB

InnoDB的数据页大小为什么是16KB

• 操作系统的文件管理系统默认一次的IO读写时4KB
• 又因为局部性原理，操作系统把命中的页的周围的三快页一起加载到InnoDB的缓存池中。所以时16KB

数据页中查找指定主键值的过程

• 通过二分法找到对应的槽，获取主键最小的记录
• 然后利用记录的next_record便利槽所在组中的各个记录

数据页相关信息

每个数据页File Header部分都有一个上一页和下一个页的编号。所以所有数据页会组成一个双链表

索引

没有索引如何查找数据

一个页中查找

• 按照主键：二分查找找到对应的槽，然后遍历槽中对应的分组
• 按照其他列：只能从最小记录依次遍历

很多页中查找

没有索引的话，只能一页一页，一个一个槽进行查找。

索引是什么

一种排好序的数据结构，用来加快搜索速度的。

索引分类

• 数据结构：哈希索引，B树，B+树
• 功能层次：普通索引，唯一索引，主键索引，联合索引
• 物理存储：聚簇索引和非聚簇索引
  • 聚簇索引：叶子节点存放的是整张表的数据
  • 非聚簇索引：叶子节点存放的是主键值，需要回表查询主键索引。

索引的底层实现

• Hash索引：在控制哈希冲突的情况下，查询速度非常快，
  • 若哈希冲突过高，会影响查询效率
  • 不适合做范围查询，适合等值查询
• B树索引：数据分布在各个节点，需要有额外的索引去指向这些节点
  • 范围查找时，B+树只需要查找两个节点即可，而B树需要获取所有的节点，会有回旋查找的可能
  • B+树的非叶子节点不存储数据，只存放关键字等信息，用于数据索引。可以存放更多的关键字，一次读入内存时，需要的关键字也就越多，IO读写次数相对降低
• B+树索引：数据都在叶子节点上，并且都增加了顺序访问指针。

和其他数据结构比较

• 红黑树：通病
• 普通二叉树：普通二叉树除了通病，会退化成链表
• 平衡二叉树：通病
• 二叉树的通病：二叉树，高度随着数据量增加而增加IO代价高。B+树3层就可以满足2000w条左右的数据

索引的代价

• 空间上的代价
  • 索引需要占用额外的空间，和索引字段有关
• 时间上的代价
  • 当对数据进行增删改操作的时候，都需要去修改B+树的索引，有一些额外的消耗

回表的代价

• 当回表的记录越多，使用二级索引的性能就越低
  • 假设有一个查询，需要回表全部数据的90%，此时还不如去全表扫描。
  • 而优化器可以进行判断是走回表还是走全表扫描

非聚簇索引一定会回表查询吗

• 不一定，有一种覆盖索引，条件语句和查询字段都被覆盖，这种就不需要进行回表

联合索引是什么，为什么要注意联合索引的顺序

• 多个字段建立一个索引，叫做联合索引
• 联合索引按照最左匹配原则。
• 例如abcd，建立索引，先按照a进行排序，然后b然后c然后d。一般把需求频繁的放在前面。
• 如果使用bcd进行查询，那么不会使用到索引

索引的设计原则

• 只为搜索、排序或分组的列创建索引
  • 例如where，order by，group by子句后面的列
• 为区分度大的创建索引
• 索引的列尽可能的小，例如前缀建立索引
  • 一方面可以加快查询速度，一方面一个数据页可以放下更多的元素，减少IO消耗
• 让主键有自增属性
  • 可以尽量的减少页面分裂和记录移位
• 尽量使用覆盖索引进行查询，减少回表
• 插入、删除、更新频繁的表，不适合建立索引

索引失效的场景

• 模糊匹配左匹配失效
• OR前后没有同时使用索引失效
• 运算符导致了索引失效
• 索引字段上使用了not <>,!=（这种会全表扫描）
• 类型不一致，导致隐式类型转换导致索引失效
• 函数导致索引失效

索引下推优化

索引遍历过程中，对索引中包含的字段先做判断，过了不满足条件的记录，减少回表次数

建立索引的顺序

• 建立索引的目的，加快查询性能，减少随机IO，增加IO性能
• 区分度高度的在联合索引的最左侧
• 尽量把字段小的列放在联合索引的最左侧（字段越小，一页存放的数据量越大）
• 最频繁的列放在左侧

百万计以上如何进行数据删除

• 如果直接删除数据，那么每次删除都会去操作索引。
• 所以需要先删除索引，然后再删除数据

事务

什么是数据库事务

事务是逻辑上的一组操作，要不都执行，要不都不执行。

事务的四大特性

• 原子性
• 一致性
• 隔离性
• 持久性

MySQL的四种隔离级别

• 读未提交：一个事务可以读取另外一个事务未提交的数据，造成脏读、不可重复读、幻读
• 读提交：一个事务只能读取另外一个事务可以提交的数据，造成不可重复读，和幻读
• 可重复读：一个事务开启时读取的数据，在事务执行整个过程中，读取的内容不变。解决不了幻读
• 串行化：写加写锁，读加读锁，可以解决所有的事务并发问题。

脏读、幻读、不可重复读

• 脏读：一个事务读取了另一个事务未提交的数据
• 不可重读读：一个事务两次查询，会返回不同的两个数据
• 幻读：事务A对表中的数据进行了修改，涉及表中的全部数据行，事务B也插入了一个数据行，事务A发现还有没有修改的数据行。

事务的实现原理

• 基于重做日志（redo log）和回滚日志（undo log）实现
• 每提交一个日志，都必须把日志写入到redo log进行持久化，保障了事务的持久性
• undo log，如果需要回滚，可以根据undolog反向语句进行逻辑操作。保证了事务的原子性
• 数据库管理系统，通过锁机制，实现了事务的隔离性

MySQL的事务日志介绍一下

redo log是innodb特有的，叫做重做日志

• redo log是固定大小的，一共有4个文件，每个文件大小未1g
• 是循环写的空间，由check point 和 write pos

undo log

• 属于逻辑日志，用来回滚到记录的某个版本
• 当事务对数据库进行修改的时候，InnoDB会先记录undolog，如果失败，则调用rollback进行事务回滚，根据undolog内容做相反的操作。

MySQL的binlog

• bing log是记录数据表结构变更，和表数据修改的二进制日志。
• 主要用于时间点恢复和主从复制
• 在server层中实现
• 是逻辑日志，记录这个语句的原始逻辑，追加写并且不会覆盖之前的日志
• bin log有三种格式
  • row格式：把每一行的修改的记录下来
  • statement格式：记录操作的sql语句
  • mixed模式：混合Row格式和Statement格式

更新语句怎么执行

• 执行引擎将数据更新到内存中
• 执行器执行更新操作，并记录在redo log buffer里面，此时redo log出于prepare状态，代表执行完成随hi可以提交事务（时刻1）
• 执行器执行这个操作，binglog，把binlog写入磁盘（时刻2）
• 执行器调用引擎事务提交接口，把刚刚写入的redolog改为commit，更新完成。

故障恢复数据

• 如果时刻1发生了崩溃，此时binlog还没写，redolog还没提交，此时直接回滚即可
• 如果时刻2发生了崩溃
  • 如果redolog里面的事务已经commint，那么说明binlog的事务是完整的，那么直接从redolog提交数据
  • 如果redolog里面的事务只有prepare，那么根据XID去binlog判断事务是否存在完整，如果完整，那么就从binlog恢复redo log的信息，进而恢复数据，提交事务。

什么是MVCC

多版本并发控制，主要是来解决读写冲突的无锁并发控制。在读（快照读）写请求的时候，发生冲突不用加锁解决。

• 快照读：基于MVCC，是按照事务进行读取，读取的不一定是当前的最新值
• 当前读：读取数据库记录时当前最新的版本，可以对读取的数据进行加锁。

MVCC只在读已提交和可重复读的两个隔离下工作

• 读未提交，总是读取最新的数据行，而不是读取当前事务版本的数据行
• 串行化：对读的所有数据加锁。

MVCC的优点

• 提高并发的读写性能，因为读写都不用加对应的锁阻塞其他的操作
• 可以解决脏读、不可重复读，但是无法解决丢失更新/幻读等问题

并发的场景

• 读-读：不存在任何问题，不需要并发控制
• 读写：有线程安全问题，可能会遇到脏读、幻读、不可重复读，
• 写写：有线程安全问题：会有丢失更新的问题

MVCC的实现原理

通过隐藏字段、版本链、undo日志、Read View来实现

隐藏字段

聚簇索引里面包含3跟隐藏列，主要row id（替代主键）、事务ID（记录最后一次修改的事务ID），回滚指针（指向这条记录的上一个版本）

举例

例如有两个事务1，2，事务1，插入一个数据，事务2，更新这个数据

• 隐藏的ID依然是1
• 事务ID为2，最后一个记录的事务为2
• 回滚指针指向undo log里面的上一个版本

版本链

• 对数据进行改动时，都会产生新的undo log（回滚日志），随着更新次数的增多，所有版本都会被roll_pointer（回滚指针）属性连接成一个链表。这个就叫版本链。

Read View

Read View是事务进行快照读操作时候的读视图，该事务执行快照读的那一刻会生成数据库系统当前一个快照。记录并维护当前活跃事务ID，用来做可见性判断。根据视图来判断当前事务可以看到哪一个版本的数据

Read View里面包含了什么

• m_ids 列表，当前系统中活跃的事务id列表（未提交事务的集合，当前事务也在其中）
• 低水位：当前活跃事务的最小ID
• 高水位：下一个将要分配的事务ID，也就是目前出现过的最大事务ID+1

数据可见性规则是怎么实现的

当前操作事务的ID，和读视图进行对比，来实现事务是否可见

若事务B要访问一个数据A，那么先获取到数据A的事务ID（最近或者当前操作的事务ID），然后对比产生Read View

• 如果事务B的ID,在低水位左边，左边的事务已经提交，可以进行访问这个数据
• 如果事务B的ID在高水位的右边，那么就表示这个版本是由将来的启动的事务生成的，不可见
• 如果事务在低水位和高水位之间，那么就看当前数据的事务ID是否在该活跃的事务列表中
  • 如果在列表中，表示该版本的事务正在运行，不能读未提交的数据
  • 如果不在列表中，表示这个版本是已经提交的事务，可见

MVCC是否解决了幻读的问题呢

只解决了不可重复读的问题，没有解决幻读的问题。不可重复读解决是需要锁机制

• 可重复读隔离级别没有完全解决幻读，若只使用快照读和当前读，就不会产生幻读。如果先快照读，在当前读，中间在快照读插入数据，当前读就会产生幻读。
• 即使给每行数据加上行锁也没有用，因为行锁只能组织修改，无法阻止新插入的记录。为了解决这个问题，加上间隙锁（锁住的是两个值之间的间隙），例如插入6个记录，就产生了7个间隙，保证读取的时候，间隙不变。
• 使用写锁的时候，自动生效间隙锁
• MYSQL在可重复读的隔离场景下解决了幻读问题，是通过行锁和间隙锁组合的Next-key锁实现的

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

两者创建的一致性视图（Read View）的时机不一样

• 读已提交：每一次select都会产生ReadView
• 可重复读：第一次select产生ReadView

读已提交如何解决脏读

• 脏读：一个事务读取了另一个事务没有提交的数据
• 利用MVCC实现：利用MVCC实现，事务的每次快照读，都会新生成一个ReadView。

可重复度怎么解决不可重复度的

• 利用MVCC实现，只有事务第一次读取快照读的时候会创建Read View，此后都使用同一个Read View

MySQL的默认隔离级别

可重复读：无法避免幻读，需要加锁（行锁+间隙锁）才能保证避免幻读