Skip to main content

DataBase 面试简答

1. 简述乐观锁和悲观锁​

乐观锁：对于数据冲突保持一种乐观态度，操作数据时不会对操作的数据进行加锁，只有到数据提交的时候才通过一种机制来验证数据是否存在冲突。

悲观锁：对于数据冲突保持一种悲观态度，在修改数据之前把数据锁住，然后再对数据进行读写，在它释放锁之前任何人都不能对其数据进行操作，直到前面一个人把锁释放后下一个人数据加锁才可对数据进行加锁，然后才可以对数据进行操作，一般数据库本身锁的机制都是基于悲观锁的机制实现的。

2. 非主键索引的查询一定会回表吗？​

不一定，当查询语句的要求字段全部命中索引，不用回表查询。如select 主键 from 非主键=XX，此时非主键索引叶子节点即可拿到主键信息，不用回表。

3. 简述覆盖索引​

覆盖索引指一个索引包含或覆盖了所有需要查询的字段的值，不需要回表查询，即索引本身存了对应的值。

4. redo log与binlog的区别？​

  1. redo log是InnoDB引擎特有的，只记录该引擎中表的修改记录。binlog是MySQL的Server层实现的，会记录所有引擎对数据库的修改。
  2. redo log是物理日志，记录的是在具体某个数据页上做了什么修改；binlog是逻辑日志，记录的是这个语句的原始逻辑。
  3. redo log是循环写的，空间固定会用完；binlog是可以追加写入的，binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

5. WAL技术是什么？​

WAL的全称是Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。事务在提交写入磁盘前，会先写到redo log里面去。如果直接写入磁盘涉及磁盘的随机I/O访问，涉及磁盘随机I/O访问是非常消耗时间的一个过程，相比之下先写入redo log，后面再找合适的时机批量刷盘能提升性能。

6. crash-safe能力是什么？​

InnoDB通过redo log保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

7. 简述事务​

事务内的语句要么全部执行成功，要么全部执行失败。

事务满足如下几个特性：

  • 原子性（Atomicity）一个事务中的所有操作要么全部完成，要么全部不完成。
  • 一致性（Consistency） 事务执行前后数据库的状态保存一致。
  • 隔离性（Isolation） 多个并发事务对数据库进行操作，事务间互不干扰。
  • 持久性（Durability） 事务执行完毕，对数据的修改是永久的，即使系统故障也不会丢失

8. 索引是什么？​

索引是存储引擎中用于快速找到记录的一种数据结构。在关系型数据库中，索引具体是一种对数据库中一列或多列的值进行排序的存储结构。

9. SQL的事务隔离级别有哪些？​

  • 读未提交: 一个事务还没提交，它做的变更就能被别的事务看到。
  • 读提交: 一个事务提交后，它做的变更才能被别的事务看到。
  • 可重复读: 一个事务执行过程中看到的数据总是和事务启动时看到的数据是一致的。在这个级别下事务未提交，做出的变更其它事务也看不到。
  • 串行化: 对于同一行记录进行读写会分别加读写锁，当发生读写锁冲突，后面执行的事务需等前面执行的事务完成才能继续执行。

10. 简述B-Tree与B+树​

B-Tree 是一种自平衡的多叉树。每个节点都存储关键字值。其左子节点的关键字值小于该节点关键字值，且右子节点的关键字值大于或等于该节点关键字值。

B+树也是是一种自平衡的多叉树。其基本定义与B树相同，不同点在于数据只出现在叶子节点，所有叶子节点增加了一个链指针，方便进行范围查询。

B+树中间节点不存放数据，所以同样大小的磁盘页上可以容纳更多节点元素，访问叶子节点上关联的数据也具有更好的缓存命中率。并且数据顺序排列并且相连，所以便于区间查找和搜索。

B树每一个节点都包含key和value，查询效率比B+树高。

11. 简述执行SQL语言的过程​

  1. 客户端首先通过连接器进行身份认证和权限相关
  2. 如果是执行查询语句的时候，会先查询缓存，但MySQL 8.0 版本后该步骤移除。
  3. 没有命中缓存的话，SQL 语句就会经过解析器，分析语句，包括语法检查等等。
  4. 通过优化器，将用户的SQL语句按照 MySQL 认为最优的方案去执行。
  5. 执行语句，并从存储引擎返回数据。

12. 数据库中多个事务同时进行可能会出现什么问题？​

  • 丢失修改
  • 脏读：当前事务可以查看到别的事务未提交的数据。
  • 不可重读：在同一事务中，使用相同的查询语句，同一数据资源莫名改变了。
  • 幻读：在同一事务中，使用相同的查询语句，莫名多出了一些之前不存在的数据，或莫名少了一些原先存在的数据。

13. 简述InnoDB存储引擎​

InnoDB 是 MySQL 的默认事务型引擎，支持事务，表是基于聚簇索引建立的。支持表级锁和行级锁，支持外键，适合数据增删改查都频繁的情况。

InnoDB 采用 MVCC 来支持高并发，并且实现了四个标准的隔离级别。其默认级别是 REPEATABLE READ，并通过间隙锁策略防止幻读，间隙锁使 InnoDB 不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定防止幻行的插入。

14. 基于主键索引的查询和非主键索引的查询有什么区别？​

对于select * from 主键=XX，基于主键的普通查询仅查找主键这棵树，对于select * from 非主键=XX，基于非主键的查询有可能存在回表过程（回到主键索引树搜索的过程称为回表），因为非主键索引叶子节点仅存主键值，无整行全部信息。

15. 简述联合索引和最左匹配原则​

联合索引是指对表上的多个列的关键词进行索引。

对于联合索引的查询，如果精确匹配联合索引的左边连续一列或者多列，则mysql会一直向右匹配直到遇到范围查询 (>,<,between,like) 就停止匹配。Mysql会对第一个索引字段数据进行排序，在第一个字段基础上，再对第二个字段排序。

例如

-- 若是a,b,c,d顺序，则d用不到index
-- 若是a,b,d,c顺序，则都可以用到index
-- SQL优化器无法优化 最左匹配原则
a = 3 and b = 4 and c > 5 and d = 6

16. 如何解决数据库死锁​

  • 预先检测到死锁的循环依赖，并立即返回一个错误。
  • 当查询的时间达到锁等待超时的设定后放弃锁请求。

17. B+ tree 作为数据库主流index数据结构的原因​

  1. B+ 树的磁盘读写代价低，指针不指向关键字，只存放索引，因此节点比B tree更小。
  2. 查询效率稳定，数据存放在叶子节点中，每次查询经过根节点到叶子节点的路径查询，时间复杂度为O(logN)，比较稳定。
  3. B+ 树有利于数据库的扫描，因为数据值存在在叶子节点，且有序，更好的查询范围。

18. 如何定位并优化慢查询SQL​

  1. 根据慢日志定位慢查询SQL
-- 设置打开慢日志
set global slow_query_log = on;
-- 设置慢查询的时间，即超过1s就计入慢日志
set global long_query_time = 1;
  1. explain工具分析慢日志中的字段
type: all -- 指全表扫描
type: index -- using index
key: xx -- 使用xx index

-- 以下2项字段表明SQL无法使用索引，需要优化
extra: using filesort -- 外部索引
extra: using temporary -- 对查询结果排序时使用临时表
  1. 修改SQL语句