1. 范式

• 第一范式：每列都是原子的
• 第二范式：需要确保每一列都完全依赖主键，而不是部分主键
• 第三范式：不能出现传递函数依赖，不能有某一列实际依赖的是非主键

---

2. 事务

• 原子性A
• 一致性C
• 隔离性I
• 持久性D

2.1 事务并发可能遇见的问题

• 脏读：事务A读了事务B修改中的数据，但是B之后回滚了，导致A读取了脏数据
• 不可重复读：事务A前后多次读取同一个数据，中间某时刻事物B修改了该数据并提交了，导致A读取的数据前后不一致
• 幻读：幻读侧重的是select操作的得到的结果不能支撑后续的业务操作，更具体一点的例子是——select某记录，发现不存在，准备insert该记录，但是在此之前恰好有另一个事务执行了该记录的insert操作并提交了，结果本事务发现insert失败，仿佛自己出现了幻觉！甚至，在RR级别下，无论select多少次都显示记录确实不存在。（RU、RC级别下不会这么离谱，但依旧可能遇到前面提到的情况）

2.2 事务的隔离级别

事务分四个隔离级别，由低到高，逐渐避免了上述提到的问题

• 读未提交
  • 事务A可以读取事务B中正在操作的数据
  • 上述三种问题都会遇到
• 读已提交
  • 多次读取某一数据，期间该数据可以被其他事务修改
  • 避免了脏读
• 可重复读
  • 多次读取某一数据，该数据期间不受其他事物影响
  • 只可能遇到幻读
• 串行化
  • 最高级别隔离，保证事务在并发时几乎像一个个先后运行的一样
  • 可以避免上述所有问题

2.3 MySQL如何使用事务

关键字：

• begin/start transaction：开启事务
• commit/commit work：提交事务
• rollback/rollbakc work：回滚事务

在mysql命令行的默认设置下，执行sql语句后会马上执行commit操作，所以想要执行事务就需要使用begin/start transaction开启事务来关闭自动commit的操作。
还可以利用set session transaction isolation level read committed等指令设置事务隔离级别。

2.4 事务隔离性的实现

MySQL是靠各种锁+MVCC（多版本并发控制）机制实现

2.4.1 锁的类型

• 按照锁的类型分：读锁（S锁、共享锁）、写锁（X锁，互斥锁）
• 按照锁的范围分：行锁、表锁、全局锁（对整个数据库实例加锁，往往用于数据库逻辑备份，该锁启启动时，数据库只能执行读操作）

2.4.2 读锁、写锁

• 读锁：又称共享锁，可以容纳其它读锁，串行化隔离级别下所有select默认加上读锁。
  mysql加读锁——select ... from ... where ... lock in share mode
• 写锁：又称互斥锁，不能容忍其他任何锁，所有**增删改**都自动加写锁
  mysql加写锁——select ... from ... where ... for update

2.4.3 行锁、表锁、间隙锁

• • 行锁
    MyISAM不使用行级锁，InnoDB会用到行级锁与表级锁
    开销大、加锁慢；
    可能出现死锁；
    发生锁冲突的概率小，并发度高
  • 表锁
    MyISAM存储引擎只会加表级锁，在执行select之前自动给涉及到的所有表加上读锁，在执行增删改之前会给涉及到的所有表加上写锁。
    开销小，加锁快；
    不会死锁；
    发生锁冲突的概率最高，并发度低。
  • 间隙锁
    第一种情况，当我们使用范围条件，并要求加锁时，InnoDB会给最终符合条件的数据行加“间隙锁”；第二种情况，等值查询结果为空时，也会在筛选条件附近加间隙锁。
    间隙锁可以解决幻读
    对于键值在条件范围内但实际并不存在的记录叫做间隙。
    例如select * from user where id > 5 for update;语句，哪怕实际数据不存在，也会阻塞其他想要对这些数据进行加锁的操作。
    此外，如果使用等值查询不存在的数据并加锁，也会触发间隙锁，如下图：
    
    

 

• 行锁、表锁的使用时机
  InnoDB涉及到“加锁”的sql语句中，如果查询条件中用到了索引（哪怕只是一部分的查询字段有索引），InnoDB会对符合筛选条件的行加上行锁；如果所有的查询条件都没有用到索引，那么本次加锁会加在整个表上。原因如下解释。
  InnoDB的行锁其实是加在索引项上的，而不是直接在数据库的对应数据行加锁。这还就带来了另一个细节，其他事务sql哪怕最终锁定的行不同，只要是该索引值作为筛选条件，就会被阻塞，因为行上对应的索引项是被锁住的，如下图：（id有索引，name无索引）

2.4.4 数据库对于死锁的处理策略

• 死锁超时：事务持续等待，直到超时，超时时间通过参数`innodb_lock_wait_timeout=50s`设置，这样处理起来虽然简单，但是死锁造成的性能影响较大
• 死锁检测（主要手段）：innodb自带该功能，可以通过参数`innodb_deadlock_detect=on`来开启。innodb存储引擎内部维护了一张“有向图”，该图以事务为顶点、以锁为边，当图中存在环时说明发生了死锁。发现死锁后，数据库会主动回滚一条代价最小（锁持有量最少）的事务。

2.4.5 MVCC机制

多版本并发控制(MVCC)帮助解决不可重复读，或者说实现了可重复读效果。
核心内容是：数据库中的记录底层不只有当前数据，还有一个记录了历史数据的版本链，每一次对记录的修改（哪怕还没有提交也会记录）都会保留相关的事务id和旧版本指向。

• 读未提交：直接读取最新的版本数据
• 读已提交：每次查询都执行快照读（生成readview），读取的是快照生成时刻可见的最新版本记录，保证读到的都是当前时刻已经提交的数据记录中最新的那个。
  • 什么是“可见的最新版本”？
    首先肯定不能是当前活跃的事务（它们都还没提交呢）生成的记录，此外，指历史记录中，最近的、且版本对应的事务id不大于快照时刻已存在事务拥有的最大版本号的记录。（后文会具体解释可见性判断流程）
• 可重复读：只有第一次查询才保留一次ReadView快照（快照生成方式和读已提交一样，是“可见的最新版本”），之后读取都使用第一次的快照，不再另外更新。
• 串行化：select操作默认加上读锁（其他事务级别是不会加的），保证读写串行。

理解：
除了“串行化”是严格同步，其他各种隔离级别都有并发性（读数据都没有加锁，读写可以并发），通过MVCC机制可以实现类似于CopyOnWrite（修改的是数据副本，读取的是旧数据）那样的读写分离效果！
不同隔离级别（除了串行化），底层区别是读取历史版本数据的策略不同。
MVCC可以说就是为了实现不加锁的读写并发（真的很像COW机制）
补充：Read View
Read view——“读视图”
是快照读“可见性”判断的依据，保存了快照读那一刻的事物信息：活跃事物id表、数据库下一个事务的ID（还没有被分配）、当前活跃的最小的事务id
可见性判断流程：

从最新的版本记录开始检查！

1. 版本事务id<活跃的最小事务id？
   如果成立，直接ok，结束检查
2. 版本事务id>=下一个事务的id？
   如果成立，那么gg，检查下一个
3. 版本事务id在活跃事务id表中？
   如果成立，那么gg，检查下一个
4. 直接ok，结束检查

2.5 事务id的生成时机

• 数据库在内部维护了一个全局变量——下一个带分配的事务id
• 需要注意的是，当一个事务开启时，不会立刻获得属于它的事务id，只有在事务执行数据修改操作（update、insert、delete）时才会被分配事务id。
• 上一点却暗含两个问题：第一，在RR隔离级别下，当事务开始后第一步执行select语句时，生成的read view里的当前事务id是多少呢？第二，如果事务在后面执行增删改获得事务id时，再去查询自己修改的字段信息是能够获得自己修改的数据的，可按理说，事务的id已经超出read view里记录的最大事务id了呀，为什么还能看到自己修改后的数据呢？
• 针对第一个问题：经实验发现，第一次快照读需要的read view里的当前事务id是一个很大的数字（貌似还不随机），如下图:

• 针对第二个问题：我目前还没办法从MVCC机制的角度去解释，只能暂且记住这种结果。

---

3. 索引与散列

3.1 基本概念

• 基本索引类型
  • 顺序索引：基于索引码的值的顺序排序，适合范围查询
    按照聚集or非聚集划分：

• • • 聚集索引（主索引）：索引顺序和搜索码的值顺序相同，网上都说mysql一张表只能有一个聚集索引（如果没有的话Innodb会选取一个unique列作为主键，还没有就会使用隐藏的唯一标识字段），而且还说聚集索引的索引项包含了数据行，参考的文章：MySQL聚集索引
    • 非聚集索引（辅助索引）：定义与上面的相反

按照稠密or稀疏划分：

• • • 稠密索引：每一个搜索码值都对应一个索引项
    • 稀疏索引：只为某些搜索码值建立索引项，索引只有当索引是聚集索引的时候才可以用稀疏索引（稀疏索引必然是聚集索引）
  • 散列索引：将搜索项（搜索码+指针）尽量平均地分到若干散列桶，具体分布到哪一个桶是由散列函数确定的。适合于等值查询。
• 顺序索引结构之一：B+树索引结构
  普通的索引顺序文件会收到文件增大带来的性能下降问题，所以提出了更加优秀的索引结构——B+树
  • B+树是一种多级索引结构
  • 相比于哈希索引的优势
    • 查询时间比较平均，不会遇到哈希索引中的哈希碰撞问题
    • 范围查询很快，因为保留了索引值的原有顺序（底层叶节点是有序链表），哈希索引无法保证哈希映射后会破坏索引码的顺序。

3.2 InnoDB和MyISAM的索引

InnoDB的索引方式叫做聚集索引，InnoDB的数据文件本身就是索引文件。
在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。
因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
InnoDB的辅助索引data域存储不是数据记录的地址，而是相应的主键的值，也就是说，InnoDB的所有辅助索引都引用主键作为data域。这也能解释select id from 表 where 索引字段=？会“using index”！
聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

推荐阅读:
mysql索引的数据结构及其原理——关于mysql索引的物理结构讲得循序渐进!!!看完什么都清楚了

---

4. 级联优化

推荐阅读：
【explain】MySQL级联查询中的驱动表
驱动表和被驱动表、小表和大表——详述了级联查询的细节

MySQL 表关联的算法是 Nest Loop Join（嵌套循环连接，简称NLJ），是通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。
de.cel 在2012年总结说，不管是你，还是 MySQL，优化的目标是尽可能减少JOIN中Nested Loop的循环次数。

4.1 小表驱动大表

小表：总体积（行数*一行的体积）相对小的结果集
大表：总体积相对大的结果集

驱动表和被驱动表：

• left join：左边的表为驱动表，右边的表是被驱动表
• right join：右边的表为驱动表，左边的表是被驱动表
• inner join：小表默认为驱动表，mysql是根据真正参与关联查询的数据行和列所占用的空间大小来确认谁作为驱动表且谁作为被驱动表的，这就意味着where语句的过滤效果也会影响到mysql到最终决定
• explain可以查看驱动表——分析的第一行的表就是驱动表
• 被驱动表可以使用索引！！！！当大表被驱动时，大表就可以用索引加快查询时间（索引B+树结构查询时间复杂度为logN），而驱动表因为要一条一条的作为比对条件，很难发挥出索引的优势。

对比：

假设A是小表，数据量为100；B表是大表，数据量为1000000

• 当A驱动B时，用时大约为100*log1000000，该时间设为x
• 反之，用时大约为1000000*log100，该时间设为y
  
  

所以，x<y，所以让数据量大的表使用索引的效益更高！

---

5. 分库、分表

分库分表是常用的解决性能瓶颈的手段，具体分为“垂直分库”、“水平分库”、“垂直分表”、“水平分表”

5.1 分库

• 垂直分库：按照业务耦合程度，将一个数据库内的表分散到多个数据库中，每一个数据库包含的都是和某一个业务相关的程度高的表。简而言之，“专库专用”。
• 水平分库：将一个数据库的数据量分散到多个数据库中，每一个数据库的结构都一样，各自存储一部分数据，减少单个数据库的体量。比如一个数据库拆成两个，一个存所有表中id为奇数数据，另一个存所有表中id为偶数的数据。

5.2 分表

• 垂直分表：将一个表中的列拆分成多份，每一份自成一个表（别忘了保持关联性），往往是为了解决原有表数据行臃肿的问题。垂直分表后可以让内存一次加载多行，避免每次都加载用不到且冗长的字段。
• 水平分表：在同一个数据库内，将表的数据分散到多个表，比如将user的数据分散到user1和user2两张表中，user1存储id为奇数的表，user2存储id为偶数的表。可以解决单表数据记录量大的问题。

---

6. redo log、undo log、bin log

推荐阅读：
详细分析MySQL事务日志
MySQL三大日志——强烈推荐！对于日志的剖析很精炼，对redo log和binlog的刷盘策略讲解得很好很好！！！

innodb的日志文件包括redo log和undo log。前者是重做日志，帮助恢复数据库，后者是回滚日志，帮助回滚事务。
undo log不是redo log的逆向过程。

6.1 redo log

• 通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成什么样子，它用来恢复提交后的物理数据页，且只能恢复到最后一次提交的位置。
• 通常意义上“每次提交前都会在redo log中记录日志”中的redo log其实都是指缓存中redo log，而不是磁盘中的redo log。如何以及何时将缓存中的redo log刷到磁盘，mysql有它的机制。
• 缓存中的redo log如何刷到磁盘redo log
  
  
  如上图所示，mysql的缓存工作在用户空间，而写入磁盘必须经过内核空间并使用操作系统的方法fsync()。所以需要先将redo log buffer刷到os buffer中，然后再使用fsync()刷到磁盘。
• redo log buffer何时刷到os buffer又何时调用fsync()方法
  mysql支撑用户自定义这个时机，配置参数是innodb_flush_log_at_trx_commit，该参数有0、1、2三个参数，具体效果如下图所示：
  
  
  参数1是默认设置，安全性最高，但效率最低，频繁调用fsync方法，IO性能差。
  参数0约每秒将log buffer刷到os buffer并fsync，可能会在系统故障时丢失一秒的操作。
  参数2效率和0几乎一样，但是更安全，因为它每次都将数据保存到了os buffer。

6.2 bin log和redo log的不同

详细内容可以参看：
查询程序崩溃日志_MySQL的日志-redolog——很细致
Bin log文件的两大作用与使用——很实践

• bin log时是redo log的上层日志，会先于redo log记录
• 和redo log不同，redo log是innodb引擎独有的，只记录该存储引擎的修改；bin log是在存储引擎上层产生的，无论是什么存储引擎的数据库发生了修改，都会产生二进制日志记录
• redo log是物理日志，而bin log是逻辑日志（针对行修改进行记录）。
• bin log只在事务提交前进行一次性写入缓存中的日志文件（对于非事务的sql，则在sql执行成功后立即记录）；redo log在每次数据准备修改前都会先写入缓存redo log中，然后才对缓存中的数据进行修改，并保证在事务提交前一定先在缓存中的redo log写入日志，再执行提交动作。
• 由于bin log是一次性记录整个事务操作，所以记录内容和事务的提交顺序有关；redo log是每次数据修改前都会执行记录，所以会有事务操作记录互相穿插的情况。
• redo log具有::幂等性::，多次操作得到同一结果的行为只会在日志中记录一次，而bin log由于是逻辑日志，会记录每一次修改，哪怕这些修改带来的操作是一样的。

6.3 undo log

• 是逻辑日志，保存每一行的版本记录，根据每行进行记录回滚。
• undo log还有个重要作用——提供MVCC（多版本并发控制）机制。
• 在数据修改时，不仅记录redo log，还记录undo log，可以简单想象——执行一个insert 操作时，undo log会记录一条对应的delete操作。
• 此外，undo log本身也会产生redo log记录，因为undo log也需要持久性保护。

---

7. mysql开启主从复制的具体操作

7.1 主从复制的主要工作线程

• 主库启动bin log二进制日志，这样会启动一个**dump**线程，这个线程是把主库的写入操作都记录到bin log中
• 从库会启动一个**IO线程**，这个线程主要是把主库的bin log二进制文件读取到本地并写入relay log文件中
• 最后从库其他sql线程会把relay log中的事件执行一遍，完成对从库的数据更新。
  
  

7.2 配置主从复制

主库方面：

• 开启bin log二进制日志
• 配置唯一server-id
• 设置一个允许远程访问的账号（远程连接用的账号就行）
• （重启主库来重载配置）获取主库bin log日志文件的文件名和位置
  
  
  除此之外，主库还可以对一些信息进行细节配置，比如设置不允许同步的数据库或者只有哪些数据库允许同步

从库方面：

• 配置唯一server-id
• 使用主库提供的账号进行远程连接，方便获取到bin log
  
  
• 启动slave服务：start slave

---

8. 一行数据库记录在磁盘内如何存储？

详见参考视频：
《大厂面试题——一行数据在磁盘……》

四个部分：

• 变长字段长度列表：逆序记录varchar字段长度
• NULL值列表：记录所有可以为NULL值的字段实际的情况，1为null，0为非null
• 数据头：记录数据是否被逻辑删除等信息
• 真实数据：记录具体的字段信息，还有三个隐藏字段——数据库生成的记录唯一标识、产生这条记录的事务id、回滚指针

---

9. eplain关键字和索引使用检查

推荐阅读：
MySQL explain使用详解
mysql中explain的type的解释——对应每种type的解释很形象易懂

explain对于优化sql非常有帮助，比如检查该sql执行时使用索引的详情

关键字段的含义：

• select_type：最常见的取值是“simple”，意味着该查询是一个简单的select，没有union和子查询
• type：非常重要!描述当前查询的查询范围与索引使用情况
  • system：表仅由一行，是const类型的特例，几乎很少出现
  • const：表最多只有一个匹配行，例如where语句里用到主键（也是唯一索引）、或者唯一索引作为筛选条件。能达到这种type级别的sql语句的性能可以理解为“最优”。
    select * from user where id = 1;
  • eq_ref：和const几乎一样，也要求索引是唯一索引或者主键，这种模式下根据索引回表后拿到一个符合条件的数据后可以立刻停止查询，在级联查询中容易见到，效率很高！
    select * from a join b on a.id = b.id where a.name = 'hjk';
    
    
  • ref：索引不是唯一索引，一个索引项可能对应多条记录，找到索引项后还需要回表进行小范围查询，找到该索引值对应的所有记录，效率低于eq_ref，能达到这个type级别的sql也是很不错的！
    常见于等值查询某个具有非唯一索引的字段
    select * from user where name='hjk';(假设name是非唯一索引)
    
    
  • ref_or_null：和ref类似，只是添加了MySQL可以专门搜索包含NULL值的行，能达到这个type级别的sql也不错！
  • index_merge、unique_subquery、index_subquery不常见
  • range：有范围的索引扫描，肯定比index的全体索引扫描要好很多，比ref差的地方在于，该范围内的索引必须彻底扫描完，不像ref只要找到目标索引值就可以不再扫描剩下的。
    常见于范围查询某个具有索引的字段
    select * from user where age>1 and age<3;(假设age是索引)
  • index：对索引进行全体扫描，比ALL的体积小，找到符合符合的索引项后还是要“回表”取数据，然后继续扫描，直到扫描完所有索引项。
    常见于需要全表扫描，但是查询结果可以通过索引直接得到，还有像排序用的是索引字段
    `select id from user`orselect * from user order by id
  • ALL：最差，全表扫描，哪怕中途找到了一个符合条件的数据，依旧要找下去（因为ALL模式下意味着可能还有其他符合条件的数据），直到所有数据项都扫描完毕。
• extra的关键字段：
  • filesort：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。简而言之，慢！
  • using index：select需要的所有信息都可以在索引中直接获取，不需要“回表”，比如select id中的主键id就可以直接从索引值中获取
  • using temporary：MySQL需要额外创建一个临时表来存储结果，出现该描述就说明有待优化了。常见的场景：mysql只支持直接在驱动表上排序，这就意味着如果想按照被驱动表的字段进行排序，那么mysql就需要先将被驱动表的查询结果组合到一个临时表中，然后按照被驱动表的字段排序。如下图：