数据库面试知识点

来自公众号：Java面试那点事

1. 什么是超键？ 什么是主键？ 二者有什么关系？

• 超键： 在关系中能唯一标识元组的属性集称为关系模式的超键。 一个属性可以为作为一
  个超键， 多个属性组合在一起也可以作为一个超键。 超键包含候选键和主键。
• 候选键： 最小超键， 即没有冗余元素的超键。
• 主键： 数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。 一个数
  据列只能有一个主键， 且主键的取值不能缺失， 即不能为空值（Null） 。
• 只能能唯一标识元组的属性集就是超键， 所以， 超键不唯一， 并且， 超键可以是一个属
  性也可以是多个属性， 同时候选键是最少的超键， 那么候选键也是不唯一的， 但是一个
  元组， 主键是唯一的， 是从候选键中选择一个作为主键。

2. 数据库的三范式是什么？

• 第一范式： (确保每列保持原子性) 所有字段值都是不可分解的原子值。
• 第二范式： (确保表中的每列都和主键相关) 在一个数据库表中， 一个表中只能保存一
  种数据， 不可以把多种数据保存在同一张数据库表中。
• 第三范式： (确保每列都和主键列直接相关， 而不是间接相关) 数据表中的每一列数据
  都和主键直接相关， 而不能间接相关。

3. char 和 varchar 的区别是什么？

• char： 存储定长数据很方便， CHAR 字段上的索引效率级高， 必须在括号里定义长度，
  可以有默认值， 比如定义 char (10)， 那么不论你存储的数据是否达到了 10 个字节，
  都要占去 10 个字节的空间（自动用空格填充） ， 且在检索的时候后面的空格会隐藏掉，
  所以检索出来的数据需要记得用 trim 函数去过滤空格。
• varchar： 存储变长数据， 但存储效率没有 CHAR 高， 必须在括号里定义长度， 可以有
  默认值。 保存数据的时候， 不进行空格自动填充， 而且如果数据存在空格时， 当值保存
  和检索时尾部的空格仍会保留。 另外， varchar 类型的实际长度是它的值的实际长度 +
  1， 这一个字节用于保存实际使用了多大的长度。

4. delete 和 truncate 有什么区别？ 谁效率更好？

• DELETE 语句执行删除的过程是每次从表中删除一行， 并且同时将该行的删除操作作为
  事务记录在日志中保存以便进行进行回滚操作。
• TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日
  志保存， 删除行是不能恢复的。 并且在删除的过程中不会激活与表有关的删除触发器。
  执行速度快。
• 当表被 TRUNCATE 后， 这个表和索引所占用的空间会恢复到初始大小， 而 DELETE 操
  作不会减少表或索引所占用的空间。 drop 语句将表所占用的空间全释放掉。 TRUNCATE
  效率更高一点。

5. 存储过程和函数的区别？

• 存储过程是一个预编译的 SQL 语句， 优点是允许模块化的设计， 就是说只需创建一次，
  以后在该程序中就可以调用多次。 函数每次执行都需要编译一次。
• 存储过程中可以使用 try-catch 块和事务， 而函数中不可以
• 函数有且只有一个输入参数和一个返回值， 而存储过程没有这个限制
• 函数可以被存储过程调用而存储过程不可以被函数调用

6. 视图的操作会对基本表产生影响吗？

• 视图是一种虚拟的表， 具有和物理表相同的功能。 可以对视图进行增， 改， 查， 操作，
  视图通常是有一个表或者多个表的行或列的子集。 对视图的修改会影响基本表。 它使得
  我们获取数据更容易， 相比多表查询。
• 当用户试图修改视图的某些信息时， 数据库必须把它转化为对基本表的某些信息的修
  改， 对于简单的视图来说， 这是很方便的， 但是， 对于比较复杂的试图， 可能是不可修
  改的。

7. count（ * ） 和 count（列名） 谁的效率更高？

• count (*) 对行的数目进行计算， 包含 NULL
• count (column) 对特定的列的值具有的行数进行计算， 不包含 NULL 值。
• count (1) 这个用法和 count (*) 的结果是一样的。
• count (1) 和 count (*) 效率差不多， 二者都快于 count (column)

8. 索引是什么？

• 在数据之外， 数据库系统还维护着满足特定查找算法的数据结构， 这些数据结构以某种
  方式引用（指向） 数据， 这样就可以在这些数据结构上实现高级查找算法。 这种数据结
  构， 就是索引。
• 索引的底层结构是 B 树、 B + 树和 hash 结构。

9. 索引的分类？ 索引失效条件？

分类：

• 主键索引 （PRIMARY KEY）
• 唯一索引 （UNIQUE）
• 普通索引 （INDEX）
• 组合索引 （INDEX）
• 全文索引 （FULLTEXT）

索引失效条件：

• 在 where 子句中使用！ = 或 <> 操作符
• 在 where 子句中使用 or 来连接条件， 当连接的字段有字段没有索引时， 将导致所有
  字段的索引失效
• 在 where 子句字段进行 null 值判断，
• 在 where 子句中 like 的模糊匹配以 % 开头
• 在 where 子句中对有索引的字段进行表达式或函数操作
• 如果执行引擎估计使用全表扫描要比使用索引快， 则不使用索引

10. 索引优化方式？

创建：

• 在经常需要搜索的列上， 可以加快搜索的速度；
• 在作为主键的列上， 强制该列的唯一性和组织表中数据的排列结构；
• 在经常用在连接的列上， 这些列主要是一些外键， 可以加快连接的速度；
• 在经常需要根据范围进行搜索的列上创建索引， 因为索引已经排序， 其指定的范围是连
  续的；
• 在经常需要排序的列上创建索引， 因为索引已经排序， 这样查询可以利用索引的排序，
  加快排序查询时间；
• 在经常使用在 WHERE 子句中的列上面创建索引， 加快条件的判断速度。

避免创建：

• 对于那些在查询中很少使用或者参考的列不应该创建索引。
• 对于那些只有很少数据值的列也不应该增加索引。
• 对于那些定义为 text, image 和 bit 这种数据量很大的数据类型的列不应该增加索引。
• 当修改性能远远大于检索性能时， 不应该创建索引

11. 怎么验证 mysql 的索引是否满足需求？

用数据库再带的命令 explain 查看语句中索引是否启动，

• type: 主要衡量该检索的性能（all 代表全表扫描， index 代表全索引）
• key: 显示 Mysql 实际决定使用的键（索引） ， null 代表未走索引。

12. 索引的底层结构是什么？ 说说各种的特点和缺点？

B 树的定义：

• 根节点至少有 2 个孩子， 至多有 m 个孩子。
• 除了根节点以外， 所有内部节点至少有 m/ 2（向上取整） 个孩子， 至多有 m 个孩子。
• 节点内部关键字 = 孩子数 - 1， 并且内部关键字是有序的。
• 所有外部节点位于同一层上。

B 树和 B+树区别：

• B 树， 每个节点都存储 key 和 data， 所有节点组成这棵树， 并且叶子节点指针为 null，
  叶子结点不包含任何关键字信息。
• B + 树， 所有的叶子结点中包含了全部关键字的信息， 及指向含有这些关键字记录的指
  针， 且叶子结点本身依关键字的大小自小而大的顺序链接， 所有的非终端结点可以看成
  是索引部分， 结点中仅含有其子树根结点中最大（或最小） 关键字。 (而 B 树的非终节
  点也包含需要查找的有效信息)
• Hash 索引无法范围查询， 无法模糊查询， 无法排序操作， 不支持联合索引最左匹配，
  无法避免表扫描。 但是等值查询具有极高效率。
• B + 的磁盘读写代价更低， B+tree 的查询效率更加稳定

13. 什么是事务？

• 事务是对数据库中一系列操作进行统一的回滚或者提交的操作， 主要用来保证数据的完
  整性和一致性。

14. 事务的 ACID 特性？

• 原子性（Atomicity） : 原子性是指事务包含的所有操作要么全部成功， 要么全部失败回
  滚， 因此事务的操作如果成功就必须要完全应用到数据库， 如果操作失败则不能对数据
  库有任何影响。
• 一致性（Consistency） : 事务开始前和结束后， 数据库的完整性约束没有被破坏。 比如
  A 向 B 转账， 不可能 A 扣了钱， B 却没收到。
• 隔离性（Isolation） : 隔离性是当多个用户并发访问数据库时， 比如操作同一张表时，
  数据库为每一个用户开启的事务， 不能被其他事务的操作所干扰， 多个并发事务之间要
  相互隔离。 同一时间， 只允许一个事务请求同一数据， 不同的事务之间彼此没有任何干
  扰。 比如 A 正在从一张银行卡中取钱， 在 A 取钱的过程结束前， B 不能向这张卡转
  账。
• 持久性（Durability） : 持久性是指一个事务一旦被提交了， 那么对数据库中的数据的改
  变就是永久性的， 即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

15. 事务并发会造成的问题？

• 脏读： 事务 A 读取了事务 B 更新的数据， 然后 B 回滚操作， 那么 A 读取到的数据
  是脏数据
• 不可重复读： 事务 A 多次读取同一数据， 事务 B 在事务 A 多次读取的过程中， 对数
  据作了更新并提交， 导致事务 A 多次读取同一数据时， 结果因此本事务先后两次读到
  的数据因更新结果会不一致。 不可重复读的重点是修改。 同样的条件， 你读取过的数
  据， 再次读取出来发现值不一样了
• 幻读： 幻读发生在当两个完全相同的查询执行时， 第二次查询所返回的结果集跟第一个
  查询不相同。 幻读的重点在于新增或者删除。 同样的条件， 第 1 次和第 2 次读出来
  的记录数不一样

16. 事务的隔离级别？

• Read uncommitted： 读未提交， 顾名思义， 就是一个事务可以读取另一个未提交事务的
  数据。 会造成脏读。
• Read committed： 读提交， 顾名思义， 就是一个事务要等另一个事务提交后才能读取数
  据。 若有事务对数据进行更新（ UPDATE） 操作时， 读操作事务要等待这个更新操作事
  务提交后才能读取数据， 可以解决脏读问题。 但会造成不可重复读。
• Repeatable read： 重复读， 就是在开始读取数据（ 事务开启） 时， 不再允许修改操作在
  同一个事务里， SELECT 的结果是事务开始时时间点的状态， 因此， 同样的 SELECT 操
  作读到的结果会是一致的。 但是， 会有幻读现象。
• Serializable 序列化， Serializable 是最高的事务隔离级别， 在该级别下， 事务串行化顺
  序执行， 可以避免脏读、 不可重复读与幻读。 但是这种事务隔离级别效率低下， 比较耗
  数据库性能， 一般不使用。

17. 说一下乐观锁和悲观锁？ 说一下 mysql 的行锁和表锁？

• 悲观锁: 先获取锁， 再进行业务操作。 即 “ 悲观” 的认为获取锁是非常有可能失败的，
  因此要先确保获取锁成功再进行业务操作。 通常来讲在数据库上的悲观锁需要数据库本
  身提供支持， 即通过常用的 select … for update 操作来实现悲观锁。
• 乐观锁的特点先进行业务操作， 不到万不得已不去拿锁。 在提交数据更新之前， 每个事
  务会先检查在该事务读取数据后， 有没有其他事务又修改了该数据。 如果其他事务有更
  新的话， 那么当前正在提交的事务会进行回滚。
• 表级锁： 开销小， 加锁快； 不会出现死锁； 锁定粒度大， 发生锁冲突的概率最高， 并发
  度最低。
• 行级锁： 开销大， 加锁慢； 会出现死锁； 锁定粒度最小， 发生锁冲突的概率最低， 并发
  度也最高。

行锁又分为读锁和写锁：

• 共享锁： 也称读锁或 S 锁。 如果事务 T 对数据 A 加上共享锁后， 则其他事务只能对 A
  再加共享锁， 不能加排它锁。 获准共享锁的事务只能读数据， 不能修改数据。
• 排它锁： 也称独占锁、 写锁或 X 锁。 如果事务 T 对数据 A 加上排它锁后， 则其他事
  务不能再对 A 加任何类型的锁。 获得排它锁的事务即能读数据又能修改数据。

18. 事务的隔离级别和加锁的关系？

• 读未提交
• 事务读不阻塞其他事务读和写， 事务写阻塞其他事务写但不阻塞读。
• 可以通过写操作加 “ 持续 - X 锁” 实现。
• 读已提交
• 事务读不会阻塞其他事务读和写， 事务写会阻塞其他事务读和写。
• 可以通过写操作加 “持续 - X” 锁， 读操作加 “临时 - S 锁” 实现。
• 可重复读
• 事务读会阻塞其他事务事务写但不阻塞读， 事务写会阻塞其他事务读和写。
• 可以通过写操作加 “持续 - X” 锁， 读操作加 “持续 - S 锁” 实现。
• 串行化
• “行级锁” 做不到， 需使用 “表级锁” 。

19. 两种常见的数据库引擎？ 分别具有什么特点？

InnoDB 和 MyISAM 是数据库最常见的两种存储引擎。

• InnoDB 支持事务， 支持行锁（读写锁） ， 适合频繁性修改操作和需要安全性的应用；
  MyISAM 不支持行锁， 不支持事务， 适合查询和插入的应用。 因为行锁的读写锁只能适
  用于修改， 插入和删除都是表锁。
• 如果你的应用程序对查询性能要求较高， 就要使用 MyISAM 了。 MyISAM 的性能更优，
  占用的存储空间少
• 如果你的应用程序一定要使用事务， 毫无疑问你要选择 INNODB 引擎。 但要注意，
  INNODB 的行级锁是有条件的。 在 where 条件没有使用主键时， 照样会锁全表。 比如
  DELETE FROM mytable 这样的删除语句。
• 现在一般都是选用 innodb 了， 主要是 MyISAM 的全表锁， 读写串行问题， 并发效率
  锁表， 效率低， MyISAM 对于读写密集型应用一般是不会去选用的。

20. 一张自增表里面总共有 7 条数据， 删除了最后 2 条数据， 重启 MySQL 数据库， 又插入了一条数据， 此时 id 是几？

• 一般情况下， 我们创建的数据库表引擎是 InnoDB， 如果新增一条记录（不重启 mysql 的
  情况下） ， 这条记录的 id 是 8； 但是如果重启（ 文中提到的） MySQL 的话， 这条记
  录的 ID 是 6。 因为 InnoDB 表只把自增主键的最大 ID 记录到内存中， 所以重启数据
  库或者对表 OPTIMIZE 操作， 都会使最大 ID 丢失。
• 但是， 如果我们使用表的类型是 MylSAM， 那么这条记录的 ID 就是 8。 因为 MylSAM
  表会把自增主键的最大 ID 记录到数据文件里面， 重启 MYSQL 后， 自增主键的最大 ID
  也不会丢失。
• 注： 如果在这 7 条记录里面删除的是中间的几个记录（比如删除的是 3,4 两条记录） ，
  重启 MySQL 数据库后， insert 一条记录后， ID 都是 8。 因为内存或者数据库文件存
  储都是自增主键最大 ID

21. 什么是主从复制？ 什么是读写分离？

在实际的生产环境中， 对数据库的读和写都在同一个数据库服务器中， 是不能满足实际需求
的。 无论是在安全性、 高可用性还是高并发等各个方面都是完全不能满足实际需求的。 因此，
通过主从复制的方式来同步数据， 再通过读写分离来提升数据库的并发负载能力。

主从复制的过程：

• 在每个事务更新数据完成之前， master 在二进制日志记录这些改变。 写入二进制日志
  完成后， master 通知存储引擎提交事务。
• Slave 将 master 的 binary log 复制到其中继日志。 首先 slave 开始一个工作线程
  （I/O） ， I/O 线程在 master 上打开一个普通的连接， 然后开始 binlog dump process。
  binlog dump process 从 master 的二进制日志中读取事件， 如果已经跟上 master， 它
  会睡眠并等待 master 产生新的事件， I/O 线程将这些事件写入中继日志。
• Sql slave thread（sql 从线程） 处理该过程的最后一步， sql 线程从中继日志读取事件，
  并重放其中的事件而更新 slave 数据， 使其与 master 中的数据一致， 只要该线程与
  I/O 线程保持一致， 中继日志通常会位于 os 缓存中， 所以中继日志的开销很小。

22. 数据库从哪几方面进行调优？

• 数据库设计 — 三大范式、 字段、 表结构
• 数据库索引
• 存储过程 (模块化编程， 可以提高速度)
• 分表分库 (水平分割， 垂直分割)
• 主从复制、 读写分离
• SQL 调优

23. 索引优化方向？

一般来说， 应该在这些列上创建索引：

• 在经常需要搜索的列上， 可以加快搜索的速度；
• 在作为主键的列上， 强制该列的唯一性和组织表中数据的排列结构；
• 在经常用在连接的列上， 这些列主要是一些外键， 可以加快连接的速度；
• 在经常需要根据范围进行搜索的列上创建索引， 因为索引已经排序， 其指定的范围是连
  续的；
• 在经常需要排序的列（group by 或者 order by） 上创建索引， 因为索引已经排序， 这样
  查询可以利用索引的排序， 加快排序查询时间；
• 在经常使用在 WHERE 子句中的列上面创建索引， 加快条件的判断速度。
• 总结就是： 唯一、 不为空、 经常被查询的字段。

对于有些列不应该创建索引：

• 对于那些在查询中很少使用或者参考的列不应该创建索引。
• 对于那些只有很少数据值的列也不应该增加索引。
• 对于那些定义为 text, image 和 bit 这种数据量很大的数据类型的列不应该增加索引。
• 当修改性能远远大于检索性能时， 不应该创建索引。 修改性能和检索性能是互相矛盾的。
  当增加索引时， 会提高检索性能， 但是会降低修改性能。 当减少索引时， 会提高修改性
  能， 降低检索性能。 因此， 当修改性能远远大于检索性能时， 不应该创建索引。

索引失效：
在以下这些情况种， 执行引擎将放弃使用索引而进行全表扫描

• 在 where 子句中使用！ = 或 <> 操作符
• 在 where 子句中使用 or 来连接条件， 当连接的字段有字段没有索引时， 将导致所有
  字段的索引失效
• 在 where 子句字段进行 null 值判断，
• 在 where 子句中 like 的模糊匹配以 % 开头
• 在 where 子句中对索引进行表达式运算或函数操作
• 如果执行引擎估计使用全表扫描要比使用索引快， 则不使用索引

24. mysql 问题排查都有哪些手段？ 怎么验证 mysql 的索引是否满足需求？

SQL 调优最常见的方式是， 由自带的慢查询日志或者开源的慢查询系统定位到具体的出问
题的 SQL， 然后使用 explain、 profile 等工具来逐步调优， 最后经过测试达到效果后上线。

开启慢查询

• slow_query_log 慢查询开启状态。
• slow_query_log_file 慢查询日志存放的位置（这个目录需要 MySQL 的运行帐号的可写
  权限， 一般设置为 MySQL 的数据存放目录） 。
• long_query_time 查询超过多少秒才记录。

分析慢查询 explain

在该语句之前加上 explain 再次执行， explain 会在查询上设置一个标志， 当执行查询时，
这个标志会使其返回关于在执行计划中每一步的信息， 而不是执行该语句。
explain 通常用于查看索引是否生效， explain 执行语句返回的重要字段：

• type： 显示是搜索方式（全表扫描 all 或者索引扫描 index）
• key： 使用的索引字段， 未使用则是 null explain 通常用于查看索引是否生效， explain 执行语句返回的重要字段：
• type： 显示是搜索方式（全表扫描 all 或者索引扫描 index）
• key： 使用的索引字段， 未使用则是 null