数据库八股

MySQL

数据库三大范式

第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项
第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

连表查询

内连接返回两个表中有匹配关系的行
左外连接返回左表中的所有行，即使在右表中没有匹配的行。未匹配的右表列会包含NULL。
右外同上
全外连接返回两个表中所有行，包括非匹配行

MySQL如何避免重复插入数据

使用UNIQUE约束
使用INSERT ... ON DUPLICATE KEY UPDATE

如果没有定义外键约束，那么就有可能出现学生选了不存在的课程或者删除了一个课程而忘记从学生表中删除选修该课程的学生的情况

in和exist

在很多情况下，EXISTS 的性能优于 IN
IN 能够正确处理子查询中包含NULL值的情况，而EXISTS 不受子查询结果中NULL值的影响，因为它关注的是行的存在性，而不是具体值

MySQL的基本函数

concat，length，substring，replace，abs，power，now，curdate，count，sum，max，min，avg

SQL查询语句的执行顺序

查询语句都是从FROM开始执行，在执行过程中，每个步骤都会生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入，最后一个步骤产生的虚拟表即为输出结果

SQL请求的过程

建立连接，查询缓存，解析 SQL，执行 SQL（预处理，优化，执行）

mysql的引擎

InnoDB
MyISAM

MySQL为什么InnoDB是默认引擎

支持事务，并发性能，崩溃恢复

innodb有行锁，myisam只有表锁，redolog崩溃恢复

mysql的innodb与MyISAM的区别

事务，索引结构（是否聚簇），锁粒度

数据文件大体分成哪几种数据文件

如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；
在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）

主键索引的 B+Tree 的叶子节点存放的是实际数据，二级索引的 B+Tree 的叶子节点存放的是主键值

字段特性分类

主键索引，唯一索引，普通索引，前缀索引

什么字段适合当做主键

唯一性，递增的趋势，不建议用业务数据作为主键

区别不大的字段不适合当索引，查出大量数据回表更慢

主键用自增ID还是UUID

自增 id，uuid 相对顺序的自增 id 来说是毫无规律可言的，新行的值不一定要比之前的主键的值要大，所以 innodb 无法做到总是把新行插入到索引的最后，而是需要为新行寻找新的合适的位置从而来分配新的空间

什么自增ID更快一些，UUID不快吗，它在B+树里面存储是有序的吗

主键页就会近乎于顺序的记录填满，提升了页面的最大填充率

不会为计算新行的位置而做出额外的消耗

减少了页分裂和碎片的产生

Mysql中的索引是怎么实现的

B+树作为了索引的数据结构，非叶子节点只存放索引

B+树的特性

所有叶子节点都在同一层，非叶子节点存储键值，叶子节点存储数据记录，自平衡

B+树和B树的区别

在B+树中，数据都存储在叶子节点上，而非叶子节点只存储索引信息；而B树的非叶子节点既存储索引信息也存储部分数据。数据量相同的情况下，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少
B+树的叶子节点使用链表相连，便于范围查询和顺序访问；B树的叶子节点没有链表连接。
B+树的查找性能更稳定，每次查找都需要查找到叶子节点；而B树的查找可能会在非叶子节点找到数据，性能相对不稳定

B+树的叶子节点链表是单向还是双向

双向的

MySQL为什么用B+树结构？和其他结构比的优点

b+树矮胖，查询速度更快，且适合范围查询

为什么 MysSQL 不用 跳表

B+树的高度在3层时存储的数据可能已达千万级别，但对于跳表而言同样去维护千万的数据量那么所造成的跳表层数过高而导致的磁盘io次数增多，也就是使用B+树在存储同样的数据下磁盘io次数更少

创建联合索引时需要注意什么

区分度大的字段排在前面

联合索引ABC，现在有个执行语句是A = XXX and C < XXX，索引怎么走

根据最左匹配原则，A可以走联合索引，C不会走联合索引，但是C可以走索引下推

联合索引(a,b,c) ，查询条件 where b > xxx and a = x 会生效吗

索引会生效，a 和 b 字段都能利用联合索引，符合联合索引最左匹配原则。

联合索引 (a, b，c)，where条件是 a=2 and c = 1，能用到联合索引吗？

会用到联合索引，但是只有 a 才能走索引，c 无法走索引，因为不符合最左匹配原则。虽然 c 无法走索引， 但是 c 字段在 5.6 版本之后，会有索引下推的优化，能减少回表查询的次数

索引失效

左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%

查询条件中对索引列使用函数

查询条件中索引列进行表达式计算

不遵循最左匹配原则

OR 前的条件列是索引列，而在 OR 后的条件列不是索引列

如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了，这个过程就是回表。

覆盖索引

一个索引包含了查询所需的所有列

如果一个列即使单列索引，又是联合索引，单独查它的话先走哪个

优化器会选择联合索引，因为查询成本更低，查询也不需要回表，直接索引覆盖了

索引的优缺点

占用空间，维护时间

索引优化

前缀索引优化，覆盖索引优化，主键索引最好是自增，防止索引失效

事务的特性是什么

ACID：原子性，一致性，隔离性，持久性

持久性是通过 redo log （重做日志）来保证的；
原子性是通过 undo log（回滚日志） 来保证的；
隔离性是通过 MVCC（多版本并发控制） 或锁机制来保证的；
一致性则是通过持久性+原子性+隔离性来保证；

mysql可能出现什么和并发相关问题

脏读：读到未提交

不可重复读：读不出相同数据

幻读：读数量不一致

怎么解决并发问题

锁+MVCC

隔离级别

读未提交（read uncommitted）

读提交（read committed）

可重复读（repeatable read）

串行化（serializable）

mysql默认级别是什么？

可重复读隔离级别

串行化隔离级别是通过什么实现的？

是通过行级锁来实现的，序列化隔离级别下，普通的 select 查询是会对记录加 S 型的 next-key 锁，其他事务就没没办法对这些已经加锁的记录进行增删改操作了，从而避免了脏读、不可重复读和幻读现象。

MVCC实现原理

「读提交」隔离级别是在「每个select语句执行前」都会重新生成一个 Read View；
「可重复读」隔离级别是执行第一条select时，生成一个 Read View，然后整个事务期间都在用这个 Read View

Read View 有四个重要的字段：m_ids（「活跃事务」的事务 id 列表） ，min_trx_id（活跃事务」中事务 id 最小的事务），max_trx_id（下一个事务的 id 值），creator_trx_id（该 Read View 的事务的事务 id）

InnoDB 存储引擎的数据库表，它的聚簇索引记录中都包含下面两个隐藏列

trx_id（该事务的事务 id），roll_pointer(指向每一个旧版本记录指针)

MySQL锁

全局锁，通过flush tables with read lock 语句会将整个数据库就处于只读，全局锁主要应用于做全库逻辑备份
表级锁
表锁，lock tables限制读写
元数据锁，，对一张表进行 CRUD 操作时，加的是 MDL 读锁；对一张表做结构变更操作的时候，加的是 MDL 写锁
意向锁，对表加上「意向独占锁」，然后对该记录加独占锁。意向锁的目的是为了快速判断表里是否有记录被加锁
行级锁，
记录锁，锁记录，分s,x实现读写互斥，写写互斥
间隙锁，只存在于可重复读，为了解决换读
临键锁，锁住一个范围

表锁作用

整体控制，粒度大，适用于大批量操作

行锁

细粒度控制，减少锁冲突，适用于频繁单行操作

MySQL两个线程的update语句同时处理一条数据，会阻塞
两条update语句处理一张表的不同的主键范围的记录，一个<10，一个>15，不会阻塞
如果2个范围不是主键或索引，走全表扫描，会阻塞

日志文件

redo log 重做日志，持久性
undo log 回滚日志，原子性
bin log 二进制日志，用于数据备份和主从复制；
relay log 中继日志，用于主从复制场景下，slave通过io线程拷贝master的bin log后本地生成的日志
慢查询日志

binlog

更新操作时会生成binlog，等事务提交时全部追加进日志，写满了新开一个写，binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作

binlog 有 3 种格式类型，分别是 STATEMENT（默认格式）、ROW、 MIXED，区别如下
statement记录sql，逻辑日志
row记录最后数据
mixd是两者混合

UndoLog日志的作用

撤销回退，保证原子性，记录相反语句

为什么有redolog

保证持久性，bufferpool总是不可靠的， MySQL 的写操作并不是立刻写到磁盘上，而是先写日志，然后在合适的时间再写到磁盘上（WAL），MySQL 重启后，可以根据 redo log 的内容，将所有数据恢复到最新的状态

redolog顺序写更快

能不能只用binlog不用relo log

不行，binlog是 server 层的日志，没办法记录哪些脏页还没有刷盘，redolog 是存储引擎层的日志，可以记录哪些脏页还没有刷盘，

binlog 两阶段提交过程

update语句的具体执行过程

执行器负责具体执行
如果记录不在 buffer pool，将数据页从磁盘读入到 buffer pool
如果一样的话就不进行后续更新流程，否则交给innodb层完成后续
开启事务，记录undolog,同时记录该redolog
更新内存，写入redolog,WAL
事务提交

mysql的explain

explain 是查看 sql 的执行计划，主要用来分析 sql 语句的执行过程

possible_keys 字段表示可能用到的索引；
key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
key_len 表示索引的长度；
rows 表示扫描的数据行数。
type 表示数据扫描类型，我们需要重点看这个。

常见扫描类型

All（全表扫描）
index（全索引扫描）
range（索引范围扫描）
ref（非唯一索引扫描）
eq_ref（唯一索引扫描）
const（结果只有一条的主键或唯一索引扫描）

extra 显示的结果

Using filesort
Using temporary
Using index

发现查询速度很慢，你有那些解决方案

分析查询语句，创建或优化索引，避免索引失效，查询优化，分页优化，优化数据库表，使用缓存技术

Explain用到的索引不正确的话，有什么办法干预吗？

可以使用 force index，强制走索引

MySQL 集群的主从复制过程

写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据。
同步 Binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。
回放 Binlog：回放 binlog，并更新存储引擎中的数据

主从延迟都有什么处理方法

强制走主库方案

分表和分库是什么

垂直：拆功能
水平：拆数据量