mysql数据库之面试常问题型

前言：临近秋招，许多大三的同学们都面临着面试，这里总结一下我从网上以及视频中看到的可能会问到的面试题，共分5个模块，这里写出来也方便记忆。

如何设计一个关系型数据库（RDBMS）

这道题是一道开放性的题，主要考察对数据库的理解，数据库结构的理解以及个模块的划分。
1、存储系统（如Linux的文件系统），既然是数据库，肯定会有自己的存储（文件系统）。
2、程序实例，我们在使用数据库时候，都会通过一个程序实例来操作存储系统，程序实例又分为以下几个部分。
2-1、存储管理，将数据存储的逻辑结构转化为在数据库中物理结构。
2-2、SQL解析，要使用SQL语句，我们必须有对应的SQL解析模块。
2-3、缓存，要设计一个高效的数据库，必须有缓存机制，如操作某些数据时，根据临近原则，会将其附近的块（页）加载到缓存中，提高操作效率。
2-4、日志管理。作为一个完整的数据库系统，必须要有日志系统，记录平时的操作，为异常之后的数据库恢复做准备。
2-5、权限管理。
2-6、容灾机制（异常处理），一个完整的数据库系统，必须配备容灾机制，在数据库异常时可以尽可能完整的保留数据。
2-7、锁。锁作为并发处理的重要方式，不管是在OS中，还是在Java中，都是必不可少的。
2-8、索引。如果有人说，怎么提高数据的查询效率，那么，索引可以提高数据的查询效率，通过其特殊的B-+Tree可以优化查询效率，妈妈再也不用担心全表扫描的问题了。

索引

1、为什么要建立索引

建立索引的目的是为了提高查询效率。

2、什么关键字才能成为索引

主键、唯一键

3、优化你的索引之——平衡二叉树

平衡二叉树：左右子树高度相差不超过一的二叉树

最优时间复杂度O(log(n)) 最差时间复杂度O(n)
优点：在条件完好的平衡二叉树之上查询时，由于对半查找，所以时间复杂度为最优O(log(n))。
缺点：在数据进行增删改之后，二叉树结构可能会改变，最坏的情况是成为单行树，此时查找时间复杂度为O(n).
为什么不进行旋转？旋转需要遍历整个二叉树，如果索引在磁盘中，每遍历一个节点就会进行一次I/O，这样所花费的时间远大于全表扫描。

4、优化你的索引之——B-Tree

B-Tree结构特征

• 根结点至少包括两个孩子
• 非根非叶节点最多有M个孩子
• 非根非叶节点最少有ceil(M/2)个孩子（向上取整）
• 所有叶子都在同一层
• ceil(M/2)-1 <= 关键字个数 <= M-1
• 非叶节点的指针p[1] p[2] … p[m] 其中p[1]指向关键字小于k[1]的孩子，p[i]指向关键字大于k[i-1]关键字小于k[i[的子树,p[m]指向关键字大于k[m-1]的子树。
• 关键字按升序排列
  优点：B-Tred的结构特点决定了其不会变成单行树的结构，当数据进行增、删、改之后，B-Tree会通过上移、下移、合并、拆分等保持B-Tree的结构。
  缺点：B-Tree的每行数据保存在其节点中，导致节点中保存的关键字信息不是很多，高度没有被进一步压缩。
  最优时间复杂度O(log(n))

5、优化你的索引之——B±Tree

B±Tree作为当前Mysql主流索引，一只都是非常重要的，它在B-Tree上做了改进，改进了B-Tree的缺点。
B±Tree的结构特征

• 根结点至少有两个孩子
• 非根节点叶节点至少有ceil(m/2)个孩子，最多有m个孩子
• 所有的叶子节点都在同一层
• 关键字个数=孩子个数
• 指针p[1],p[2]…p[m] 关键字k[1] k[2]… k[m] 其中p[1]指向关键字小于p[2]的子树，p[i]指向关键字小于k[i+1]的子树，但是其子树中的关键字不一定大于k[i]，说白了指向的子树所有关键字都小于后一个关键字，可以理解为左开右闭区间。
• 关键字个数范围=孩子节点个数范围 [ceil(m/2),m]
• 所有的关键字按顺序排列，且每一个叶节点都指向下一个叶节点
  优点：
• B±Tree由于其结构特征，所以在数据进行变动的同时，也会上移，下移，拆分，合并等保持其结构，不会出现单行树的情况。
• B±Tree的读写I/O次数更加少，B±Tree的所有行数据都存放在叶子结点中，这样就可以增加非叶子节点内索引的个数，索引可以使得树结构更矮，I/O次数更少。
• B±Tree更稳定，由于行数据都存放在叶节点中，每次查询都必须查询到叶子节点中，所以时间复杂度稳定在O(log(n))
• B±Tree更有利于范围查找以及全盘扫描，由于所有叶子结点以链表的形式连接在一起，所以只要找到开头，就能找到之后的所有数据。

6、优化你的索引之——Hash索引

哈希索引将关键字通过哈希函数进行计算，得出一个哈希值，将相同Hash值的行数据保存在一起。
优点：查询速度快（只针对于特定查询 =、IN）
缺点：

• 只能进行=和IN的查询，不能进行范围查找。
• 无法被用来避免任何的排序操作，就算之前表中是按照某个顺序排列的，但是经过Hash函数计算之后不一定还是按照该顺序进行排列。
• 不能利用部分索引进行查询
• 不能避免表的扫描，当不同的关键字经过Hash函数运算之后，可能有相同的Hash值，最极端的情况下，所有的Hash值都是相同的，这就会进行全表扫描，极大的浪费时间。

7、密集索引以及稀疏索引

7.1、稀疏索引

• MyISAM下全部都是稀疏索引
• 稀疏索引中，叶子节点不存放行数据，而是存放行数据的地址
• 在InnoDB中，有的稀疏索引只保存了主键信息。
• InnoDB中可以有稀疏索引，但必须有一个密集索引

7.2、密集索引

• 密集索引叶子节点保存着该行数据
• InnoDB中必须有一个密集索引
• 密集索引会根据主键创立，如果没有主键会根据唯一键创立，两者都没有的话，Mysql会自动创建一个隐藏列，并将其当作主键来创建密集索引
• InnoDB中的稀疏索引中存放着主键值，根据稀疏索引查询时，会先查稀疏索引获取主键值，再用主键值查找密集索引，最终得到行数据，包含了两次查找。

8、优化你的查询之——定位并查询慢Sql

8.1、根据慢日志查询慢Sql

show variables like '&quer%';

可以查询到关于慢日志配置的信息
其中
slow-query-log 慢日志是否开启
slow-query-log-file 慢日志保存的位置
slow-query-time 慢SQL最长执行时间，超过后可认定为慢SQL，可自己设置

show status like '%slow-queries%'

查询本次慢SQL的条数，只用于当前的session会话窗口

set global show_query_log = on; //开启慢日志，仅本次有效
set global long_query_time = 1; //设置慢sql执行最长时间，超过可认定为慢sql

8.2、使用explain进行慢SQL原因查询

explain select * from user where id = xx

前面加上explain之后不会真正的执行该语句，而是会将该语句是否进行了全表扫描，是否利用索引查询等信息返回
其中各字段可以参考explain各字段参考
其中最重要的就是type以及Extra
type：如果该值为index或者all，说明进行了全表扫描
Extra：using filesort 说明结果使用了一个外部排序，没有使用本身的索引排序，一般称为文件排序。using temporary说明使用了临时表，常见于使用order by和group by。
8.3、如何优化Sql查询效率
修改Sql或者是尽可能使其走索引查询。

9、联合索引的最左匹配原则

最左匹配原则：mysql会一直向右匹配，直到遇到了范围查询（> < between like)会终止匹配，如a=3 and b=4 and c>5 and d=6 在索引为（a,b,c,d)的情况下就不会去走索引查询，因为到c>5的时候已经匹配终止了。
如果索引为(a,b) 条件为a=3 and b=4 或者 a=3 或者 b=4 and a=3就会走索引，但是条件为b=4时，就不会走索引，因为没有匹配到第一个索引a，其中的原因是因为Mysql有一个优化查询器.

优化查询器：

• mysql会根据查询条件来匹配索引，如果没有出现范围查询，并且所有查询的关键字命中了全部索引，这时候，不管其顺序如何，优化查询器会自动帮其调整顺序。
• 如果没有全部命中索引时，必须按照索引的顺序出现其中部分，且不能有范围查询，这样优化查询器会帮助我们匹配到该索引，这就是为什么之前条件 a=3 可以走索引 而 b=4 不可以走索引的原因了。
• 成因：mysql进行索引查询时，会先按照联合索引最左边的值进行排序，之后依此类推，所以只查后面的值是不会走索引的。

10、索引是越多越好吗？

• 数据量小的表没必要建立索引（InnoDB下强制按照主键建立的索引不算）
• 变更数据频繁的表需要频繁的维护索引，带来的系统开销比较大
• 建立索引需要额外的空间。

锁

1、MyISAM与InnoDB锁的区别

• MyISAM是表级锁，不支持行级锁
• InnoDB默认是行级锁，也支持表级锁
• MyISAM对于频繁执行全表的Count（因为其中有一个变量保存着count），频繁查询、增删改不频繁、没有事务的情况下适用。
• InnoDB在增删改查都频繁的情况下、需要事务支持的情况下适用。
• 行级锁加上之后，除了被操作的这些行，其他行都可以继续操作。
• 表级锁加上之后，任何对这张表进行操作的行为都将会被阻塞。
• InnoDB在操作数据没有用到索引时，会加一个表级锁/Gap锁（间隙锁）。

2、共享锁和排他锁

• 读锁又叫共享锁，共享锁之上还可以再加共享锁
• 写锁又叫排他锁，排他锁之上不能再加任何锁

3、锁的划分

• 按照粒度划分：行级锁、表级锁、页级锁
• 按级别划分：排他锁、共享锁
• 按照加锁方式划分：自动锁（UPDATE、DELETE、INSERT）显式锁 SELECT （在RC模式下，增删改会自动加锁）SELECT可以通过

select ... for update //加写锁
select ... lockin share mode //加读锁

• 按照操作划分：DML锁、DDL锁
• 按照使用方式划分：悲观锁（任何操作前先加锁，数据库的默认形式） 乐观锁（通过添加版本号字段或者时间戳字段，每当操作成功时更新版本号，每当要更新时都需要通过版本号作为条件控制，是人为控制的一种方式）

4、数据库事务的四大特性

• 原子性：一个操作，要么都做，要么都不做，不可再分
• 一致性：数据库的状态要从一个一致性状态转移到另一个一致性状态
• 隔离性：一个事务的操作不能影响到另一个事务的操作
• 持久性：对数据库做的改变应该是永久性的

5、由事务并发引起的问题

• 更新丢失：事务A修改了一个字段，在其没有提交的过程中，事务B修改了这个字段，B之后提交，A提交之后，发现自己的提交被B破坏，自己的更新丢失（在InnoDB下，最低的隔离级别也已经到达了阻止丢失修改的情况）。
• 脏读：事务B正在修改一个字段，修改完成没有提交时，事务A查看该字段，之后B却回滚了事务，A再次查看，发现读到的数据已经不同，这就是脏读（在InnoDB下，RC（READ-COMMITTED）级别可防止脏读）。
• 不可重复读：事务A查看一个字段，此时，突然一个事务B修改了此字段并且提交，A再次查看，发现两次读到的数据不同。（在在InnoDB下，RR（REPEATABLE-READ）级别下可防止不可重复读）。
• 幻影读：事务A在查看某个数据时，事务B对该数据进行了插入或删除，当事务A再次操作时，发现多或者少了数据，被称为幻读（在InnoDB下，最高隔离级别SERIALIZABLE下可防止幻读）。

6、快照读以及当前读

• 快照读：没有加锁的SELECT
• 当前读：加了锁的增删改查

7、RR、RC下 InnoDB的非阻塞读（快照读）如何实现

我们都知道，在RR模式下，事务A查看某一字段，事务B在查看过程中修改该字段，然后提交，事务A再次查看时，字段没有发生变化，（但实际字段已经发生了改变，只是为了体现可重复读），那么，我们当前看到的没有发生变化的字段，其实是之前该字段的快照，也就是在Undo log中该字段的旧版本。
DB_TRX_ID：最近一次修改本行的事务ID
DB_ROLL_PTR：回滚指针，指向Undo log日志中最新的该行数据版本。
DB_ROW_ID：行号
Undo log：每当我们修改一行数据时，Undo log中会生成一条记录，这条记录就是被修改前的该行记录，并且当前修改过数据的回滚指针会指向该记录。
在RR级别下的快照读具体流程是：每当我们执行SELECT，执行的同时会创建一个Read View（可见行判断），这个可见行判断会执行一个算法，决定我们看到的是那个版本的数据。每当我们新创建一个修改时，Undo log中就会保存修改前的记录，并且将该记录的DB_TRX_ID➕1，每次我们进行查询操作时（每个事务开启之后都会有一个事务ID）生成的ReadView会将当前的活跃事务同Undo log中该数据的事务ID相对比，如果小于该事务，那么就显示undo log中的记录，反之，则显示当前最新的数据记录（修改过的）。
这就能解释，

• 开启事务A查询当前记录，事务B修改了该记录，虽然真实的记录已经改变，但是我们再次查询发现记录还是之前未修改的，这是因为当前查询的事务ID小于当前修改的事务ID（事务生成的越晚ID越大），就会取出Undo
  log中旧版本的记录。

• 反之，我们先开启事务修改该记录，然后再去查询，此时查询的ID要大于修改的ID，所以我们可以看到最新的数据

然而，在RC模式下，我们每查看一次数据，就会生成一次快照，这样才会出现不可重复读的现象

我们称RR模式下这种Undo log➕Read View 的模式为伪MVCC（多版本并发控制）模式，为什么是伪呢，是因为没有做到真正的多版本并发，只是通过Undo日志的串型化来控制读写并发。
如果想了解的更加详细，可以参考这篇文章InnoDB MVCC 机制

8、InnoDB在可重复读隔离机制下如何避免幻读

有人会说，幻读不是在最高隔离级别下才能避免吗，但实际上，我们在可重复读的级别下也可以防止幻读，我们在通过一个事务读取数据时，其他事务插入或者删除一行数据时，该行数据会被阻塞（具体阻塞得看当前读数据是什么），而不仅仅是我们通过undo日志+ReadView那样看到的是旧的版本，毕竟通过快照读，我们不确定看到的版本是不是真正的修改过的版本（有一种掩耳盗铃的感觉）。在RR以及SERIALIZABLE（串型化）级别下真正防止幻读发生的措施是next-key锁（行锁+gap锁）。

1. 在走主键索引或者唯一索引的条件下，where条件部分命中（where中的有的数据，数据库记录中没有）和全部不命中的情况下，会加gap锁，假设条件为where id=7，但是表中id只有【1，3，6，9】这时出现了全部不命中的情况，此时，会对7周围加gap锁，具体区间为【6，9】，因为7处于中间。假设条件为where id in (3,5,6)，出现了部分命中的情况，此时，会对【3，6】之间的数据加gap锁。如果是全部命中，则不会加gap锁，只会对这些命中的行加行锁。
2. gap锁会用在非唯一索引的当前读中。

name	b	c	f	d
id	7	9	9	11

如当前表，其中name为主键，id为非唯一索引，对id=9进行当前读时，会将9附近的区间加上gap锁，所以gap加锁区间为[7,11)，至于为什么是左闭右开，因为mysql官方文档就是这么说的。此外，主键的值也在其中起到了作用，如 insert into user values(“bb”,7)，则会插入成功，因为bb相对于id为7的主键b来说，是在区间之外的，主键影响只局限于区间临界点。

3. gap锁会用在不走索引的情况下

name	b	c	f	d
id	7	9	9	11

同样是这个表，其中name是主键，id不是索引，如果当前读为where = 7，此时，会将所有的间隙加上gap锁，相对于表锁，这样的gap锁代价更大。

4. 小提示，在我上网查找关于InnoDB在可重复读隔离机制下如何避免幻读的面题中，找到了这么一篇博客，大致意思就是博主面试字节时，面试官问，既然在可重复读隔离机制下可以避免幻读，为什么这个机制不叫避幻机制呢，我看了博主现在的想法，觉得比较好，所以分享给大家，在最高级别SERIALIZABLE（串型化）下，所有操作，都会强制加自动锁，SELECT也不例外，但是在RR条件下，SELECT是不会主动加锁的，这样就是说，在RR隔离机制下，所有的SELECT都是快照度，除非加锁才能成为当前读，我们这里说的关于InnoDB在可重复读隔离机制下如何避免幻读所用的都是当前读（当然这个当前读指的不一定是SELECT），大家如果碰到这种问题，可以参照这样说。

关键语法

最近看数据库面试题的时候，发现一提到关键语法，各种语法都出来了，当然，最基本的建表、删表、修改表结构、增删改查，条件筛选、外键约束这些就不说了，必须要会的，这里我也不说什么触发器了😂（太难了，我也不会）。Group By绝对是一个难点，想到我刚开始学数据库时，被一道 查选修了XX门课的学生学号，姓名，真的是难到了，毕竟我也不是什么天才，总之，大家可以多做一些关于分组的数据库题，多了解以下聚集函数的用处，这里只提两点

1. 对于一张表进行查询时，select 查询的列必须包含在group by条件中，或者是对其他列的聚集函数（MAX MIN AVG SUM COUNT）。

SELECT ID,COUNT(score）
FROM tb_course
GROUP BY ID

2. 对于多表联查，select中可以有group by中没有的字段

SELECT s.id,COUNT(score),name
FROM tb_course c,tb_student s
WHERE s.id=c.id
GROUP BY s.id

范式

1NF

第一范式：第一范式规定每个元组中的每一列，或者说是每个字段都是不可再分的，第一范式是关系型数据库的基础，每个关系型数据库都满足第一范式。

2NF

第二范式：第二范式是在第一范式的基础上，消除了非主键列对主键的部分依赖，比如某个表是联合主键，其中有某个非主属性依赖于主键的一部分，这样就不是2NF，必须拆分表才能成为2NF。

3NF

第三范式：第三范式是在第二范式的基础上，消除了非主键列对主键列的传递依赖，即不能存在非主属性C依赖于非主属性B，而非主属性B依赖于主属性A，此时必须拆分，将中间的B写成另一个关系（B，C）

BCNF

BC范式：BC范式消除了主属性之间的传递依赖。
具体可以参考第一范式、第二范式、第三范式