Mysql 面试题积累（希望对我对大家都有用！）

团子大人
229 阅读15分钟

Mysql

参考以及推荐文章：

说明：还没有写完！慢慢准备！！！如果存在一些问题，希望能得到指点。感谢！！！🎉🎉🎉

Mysql

参考以及推荐文章：

基础

1.什么是数据库, 数据库管理系统, 数据库系统, 数据库管理员?简写分别是什么？

数据库 : 数据库(DataBase 简称 DB)就是信息的集合或者说数据库是由数据库管理系统管理的数据的集合。

数据库管理系统 : 数据库管理系统(Database Management System 简称 DBMS)是一种操纵和管理数据库的大型软件，通常用于建立、使用和维护数据库。

数据库系统 : 数据库系统(Data Base System，简称 DBS)通常由软件、数据库和数据管理员(DBA)组成。

数据库管理员 : 数据库管理员(Database Administrator, 简称 DBA)负责全面管理和控制数据库系统。

2.主键，外键有什么区别?为什么不是很推荐使用外键？

主键外键
定义用于唯一标识一个元组，不能有重复，不允许为空。表的外键是另一表的主键, 外键可以有重复的, 可以是空值
作用保证数据完整性，提供元组的位置信息用来和其他表建立联系用的
个数一个表只能有一个一个表可以有多个外键

外键的优点：

  • 由数据库自身最大限度保证数据一致性，完整性
  • 在一定程度上，说明了业务逻辑，设计更加全面。增加ER图可读性
  • 联级操作方便。

缺点：

  • 增加了复杂性，会平添开发难度，导致表更多等问题
  • 维护工作复杂
  • 降低耦合度，影响性能，对分库分表会造成影响。

既然有外键，那么肯定有他存在的开发环境，当回答这种问题，不能一味的否定，现在项目越来越复杂，外键弊大于利，但是如果你的项目轻量，不分库分表，对性能没有要求，其实可以使用外键去简化一些过程的。

3.数据库三范式分别是什么？

  • 1NF：属性不可再分。
  • 2NF：1NF 的基础之上，消除了非主属性对于码的部分函数依赖，换句话说非主属性对码完全函数依赖。
  • 3NF：3NF 在 2NF 的基础之上，消除了非主属性对于码的传递函数依赖 。

4.Mysql引擎支持哪些引擎？讲讲常用 MyISAM 和 InnoDB 的区别

image-20220304212100214

支持的引擎如上。

MyISAM 和 InnoDB 的区别从以下几个方面说明：

  1. 是否支持行级锁

MyISAM：不提供行级锁，当处理并发时候，利用表锁。

InnoDB：支持行级锁，权限细粒度更低，并发上比 MyISAM 更好。

  1. 是否支持事务

MyISAM：不支持事务。

InnoDB：支持事务，具有提交(commit)和回滚(rollback)事务的能力。

  1. 是否支持数据库异常崩溃后的安全恢复。

MyISAM：不支持

InnoDB：支持，利用 redo log(重做日志) 将数据库状态恢复到你想要任意时刻。

索引

1.什么是索引？常用结构是什么？

索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。

2.索引的优缺点

优点：

  • 大大加快数据的检索速度，这是主要原因
  • 使用分组和排序语句，性能会有显著的提高。
  • 唯一性索引，保证数据库中每一行数据的唯一性

缺点：

  • 创建索引和维护索引需要时间，随着数据量变大，时间需要的更多。当表中数据发生更新时候，索引也需要动态维护。
  • 创建索引占据空间，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

3.使用索引一定会提高查询速度？索引有哪些使用场景？

不一定！

场景很多，举常用的例子。

推荐使用索引场景：

  • 主键长度小，建立唯一索引
  • 频繁用作查询条件的字段建立索引
  • 需要排序或分组的字段建立索引
  • 多个字段需要被查询建立复合索引

不推荐建立索引场景：

  • 表的记录少，这时候全表扫描和索引查询时间差不多的。
  • 增删改操作多，查询少的表不建议建立索引，当你进行增删改时候，需要时间去维护索引。
  • 数据重复且分布平均的表字段，此时建立索引没有太大的实际效果。

4.索引的底层数据结构聊一聊

索引常见的数据结构：B 树， B+树和 Hash。

Hash

hash 索引基于哈希表实现，哈希表是 (k,v) 键对的集合，可以通过键值 (k) 快速取到对应的值 (v)。MySQL中，只有Memory存储引擎显示支持hash索引，是Memory表的默认索引类型。

实现原理：对于每一行数据，存储引擎会对索引列根据哈希函数计算一个哈希值，哈希索引将所有的哈希值存储在索引中，同时保存指向每个数据行的指针。哈希函数会存在哈希冲突，解决方法：链地址法（如果有多个列的哈希值相同，索引会以链表方式存放多个记录指针）

img

使用限制：

  • 哈希索引数据不是按照索引值顺序存储，不能用于排序。
  • 哈希索引不支持部分索引查找，必须使用所有的索引列来查找。
  • 哈希索引只支持等值查询（=,IN(),<,=,<），不支持范围查询。
  • 哈希冲突会影响查询速度。

**总结：**哈希索引限制多，只适用于一定的场合（等值查询）。而一旦适合哈希索引，它带来的性能提升将非常显著。

B树&B+树

推荐阅读：

在一堆数据中查询一个数据时候，常用的数据结构二叉查找树（binary search tree，简称BST）、哈希桶等。以BST 为例，常见的实现有 AVT、红黑树等，由于这类型的树是平衡的，每次比较操作都会去掉当前数据量一半的数据，因此查找的时间复杂度为O(logn)。

bst-example

其实B树性能很不错，但是由于每个节点只能存储一个数据，可能导致树的高度会变高，每次查询的次数最坏情况会等于索引树的高度，逻辑上挨着的节点数据会离得很远（例如上面的34，35），这些问题好像不是很严重，毕竟B树是多路平衡树，可是设置合适的阶，其实这是分情况的。

在内存中操作数据，问题不大。

在磁盘中操作数据，会有一些不同点：

  • 读写磁盘数据很慢。
  • 读写磁盘的最小单位是数据页。

为了减少读写磁盘IO的次数，能够充分利用局部性原理，磁盘预读，需要把瘦高的B树结构变成矮胖的B+树。这种数据结构特点：每个节点能容纳更多的数据，这样就能降低树的高度，同时让逻辑上相邻的数据都能尽量的存储在物理上也相邻的硬盘空间上，减少磁盘读写。

img

当你使用主键索引时候，B+树非叶子节点存主键的值，叶子节点存整行数据。非主键索引，B+树非叶子节点存储非主键字段，叶子节点存储对应的主键值。

在这里，一个节点越“胖”，意味着扇出（fanout）越大，同时高度越低，这两个性质决定了：

  • 高扇出：邻近键值的数据局部性更好。
  • 低高度：遍历期间的寻道次数更少。

5.为什么选择 B+ 作为Mysql的索引，红黑树等平均树不行吗？

  • 更少的查找次数

平衡树查找时间复杂度最坏的情况是等于树高 h。 B+ 树的出度大，高度低，矮胖矮胖的，红黑树出度为2，树高会比较大，时间复杂度就会高。

  • 利用磁盘的预读特性与局部性原理

局部性原理是指无论程序指令还是数据都趋于聚集在一个较小的连续区域中。

为了尽量减少I/O操作，计算机系统一般采取预读的方式，预读的长度一般为页（page）的整倍数。

数据库系统将索引的一个节点的大小设置为页的大小，使得每一次 I/O 都能完全载入节点并且利用预读属性，相邻节点也能被预先载入，根据局部性原理，减少了 I/O 操作比其他平衡树更加合适。

6.聊一聊索引类型，你是怎么理解的？

主键索引

主键索引又被称作聚簇索引。

对于 InnoDB ，rowid 是每个引擎用来唯一标识数据行的信息。

  • 对于有主键的 InnoDB 来说 rowid就是主键 ID。
  • 如果没有创建主键，系统会生成长度为 6 字节 rowid 来作为主键。
  • 对于 MEMORY 引擎的 rowid 是数组下标。

每张表都会有主键索引。

主键索引的叶子节点存储的是整行的数据。

二级索引(辅助索引)

唯一索引，普通索引，前缀索引等都是属于二级索引，二级索引的叶子节点存储的数据是主键。

  • 唯一索引：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
  • 普通索引：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
  • 前缀索引：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符，会影响覆盖索引的使用。
  • 全文索引 ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

7.你知道哪些索引的原则？

覆盖索引

🌰：

#k是字段也是普通索引 T是表
select * from T where k between 3 and 5

在这个查询语句工程中，k 索引树下查询 k=3 对应的 ID 值然后回到主键索引树中搜索所有的数据，这个过程我们称之为回表。

select ID from T where k between 3 and 5

我们需要查询的 ID 值在 k 索引树中的结果，可以直接返回查询结果，不用回表，这个过程我们称之为覆盖索引。在这个查询里面，对于索引树 k 已经覆盖了我们的查询需求。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

最左前缀原则

B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

🌰：

#现在有一个存储了id，姓名和年龄的表,姓名是索引。
select * from t where name like ‘张 %’
# 姓名，年龄是联合索引
select * from t where name like ‘张 %’

上面两个查询语句都可以使用索引。

最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

索引下推

索引下推：可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

mysql 会一直向右匹配索引，直到遇到范围查询（>、<、between、like）就停止匹配。

范围列可以用到索引，但是范围列后面的列无法用到索引。即：索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。

🌰：

#现在有一个存储了id，姓名和年龄和性别的表,# 姓名，年龄是联合索引
select * from tuser where name like '张%' and age=10 and ismale=1;

索引树会先查询姓张的数据，由于是 like 后面就不能用索引，通过索引下推然后再内部进行对age的判断，最后回表查询进行比较。

8.对一个字段，你会唯一索引还是普通索引？

  • 业务要求优先：如果数据库的数据需要保证数据的唯一性，做约束，那么优先选择唯一索引。
  • 如果业务没有要求，分场景去选择：
    • 写多读少的场景（账单，日志）推荐选择普通索引
    • 写多读多的场景不推荐选择普通索引。

解释（主要解释第二条）：

唯一索引和普通索引在查询上面性能上是差不多的。

在更新方面，两者有一定的差别：

  • 唯一索引：需要每次更新时候将磁盘中的数据读入内存，在进行更新操作。
  • 普通索引：当需要更新的数据在内存中就直接更新，如果不在，更新记录写入 change buffer，后面在进行 merge，减少了将数据从磁盘读入内存的操作。

change buffer 发起merge的条件：

  • 再次访问记录的数据页
  • 定期merge
  • 数据正常关闭时候。

事务

1. 事务有哪些特性？

  1. 原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
  2. 一致性： 执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
  3. 隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
  4. 持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

2.并发事务会带来哪些问题？

  • 脏读

当一个事务 A 正在访问数据 D，并对其进行了修改，在事务提交之前有另一个事务 B 访问了并使用了修改后的数据 D，读到的数是脏数据，后续的操作都是无效的。

  • 不可重复读

事务 A 访问了数据 B，这时候，事务 C 也访问了数据 B 并对其进行更新操作之后，事务 A 再次访问数据 B，发现前后读取的数据不一致。

  • 幻读

事务 A 访问表里面一些数据，这时候，事务 B 往这个表里面插入了几条数据之后，事务 A 再次访问表里数据，发现多了一些原本不存在或者少了一些数据。

注：

不可重复度和幻读，都是一个事务中对数据进行了多次的读取，前后数据不一致，但是着重点不一样。

不可重复读的重点是数据的更新，幻读的重点是数据的新增和删除。

3.并发带来的问题怎么解决？

可以通过 SQL 标准定义的隔离级别来解决：

  • READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更。无法解决问题。
  • READ-COMMITTED(读取已提交)：允许读取已经事务提交的数据。可以解决脏读。
  • REPEATABLE-READ(可重复读)：在事务开始到结束这一段过程，读取数据保持一致。可以阻止脏读和不可重复读。
  • SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰。都可以解决。
隔离级别脏读不可重复读幻影读
READ-UNCOMMITTED√√√
READ-COMMITTED×√√
REPEATABLE-READ××√
SERIALIZABLE×××

4.在可重复读级别是怎么解决幻读？

间隙锁和行锁合称 next-key lock，next-key lock帮助解决幻读问题。但是注意：间隙锁的引入，可能会导致同样的语句锁住的范围更大，会影响到并发度的。

注：间隙锁只有在可重复读隔离级别才会生效。

锁

日志

我们今天聊一聊关于日志方面的。

1.redo log是什么？在Mysql起着什么作用，记录了啥？

redo log （重做日志）是 InnoDB 引擎独有的，可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，保证了数据持久性与完整性，这个能力成为 crash-safe。

当有数据需要更新时候，会将磁盘需要更新数据的数据页加载进入内存中，在内存中进行更新数据，接下来就会把 ”在某个数据页上做了什么修改“ 这一条记录到 redo log，根据策略选择合适的时机，将记录写入磁盘中。

小贴士：每条 redo 记录由“表空间号+数据页号+偏移量+修改数据长度+具体修改的数据”组成

2.redo log日志是怎样工作的？

InnoDB 的 redo log 是固定大小的，你可以选择进行配置。比如比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么 redo log 可以记录 4G 的内容。

redo log 从头开始写，写到结尾，又回到开头循环写，如同一个圆，如下图：

img

redo log 有两个比较重要的属性：

  • write pos 是当前记录的位置，一边写一边后移。
  • checkpoint 是当前要擦除的位置，也是向后推移。

write pos 和 checkpoint 之间的区域是可以用来记录新的操作。

如果 write pos 追上 checkpoint，表示日志满了，那么不能写入新的更新记录了，需要停下来写入一些数据进入磁盘中，checkpoint 需要向后面移动。

4.redo log每次都是什么时候将记录写入磁盘，总不能每次都写满了再去写入把？

与 redo log 刷盘相关的参数是 innodb_flush_log_at_trx_commit，具有三种值：

  • 0：日志缓存区将每隔一秒写到日志文件中，并且将日志文件的数据刷新到磁盘上。该模式下在事务提交时不会主动触发写入磁盘的操作。
  • 1：每次事务提交时MySQL都会把日志缓存区的数据写入日志文件中，并且刷新到磁盘中，该模式为系统默认
  • 2：每次事务提交时MySQL都会把日志缓存区的数据写入日志文件中，但是并不会同时刷新到磁盘上。该模式下，MySQL会每秒执行一次刷新磁盘操作。

分别说明每个模式的优缺点：

  • 当设置为0，该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失；
  • 当设置为1，该模式是最安全的，但也是最慢的一种方式。在mysqld服务崩溃或者服务器主机宕机的情况下，日志缓存区只有可能丢失最多一个语句或者一个事务；
  • 当设置为2，该模式速度较快，较取值为0情况下更安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失；

5.binlog 又是什么，他在Mysql起着什么作用，记录了啥？

binlog （归档日志）是在 Server 层的，主要做的是 Mysql 功能层面的事情，记录内容是语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。

不管你用什么存储引擎，只要发生了表数据更新，都会产生 binlog 日志。binlog会记录所有涉及更新数据的逻辑操作，并且是顺序写。

6.既然谈到了 binlog 你能说一说redo log和binlog 之间的一些异同点？

redo log 和 binlog 主要有三点不同：

  • 层面上来说：
    • redo log 是 InnoDB 引擎特有的；
    • binlog 是 MySQL 的 Server 层实现的，所有引擎都会产生。
  • 日志记录的数据来说：
    • redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；
    • binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
  • 从写日志的方式来说：
    • redo log 是循环写的，空间固定会用完；
    • binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
  • 从能力来讲
    • redo log（重做日志）让InnoDB存储引擎拥有了崩溃恢复能力。
    • binlog（归档日志）保证了MySQL集群架构的数据一致性。

7.binlog 又是什么时候将记录写入磁盘呢？

与 binlog 刷盘的时机与参数 sync_binlog 相关。允许值：0～4,294,967,295。

  • 0：当事务提交时候，会把日志写入文件系统的 page cache，但是不会立即将数据写入磁盘中，而是系统自行判断什么时候写。
  • n：累计到 n 个事务时候，将文件系统的 page cache 的数据写入磁盘中。

8.说到这了，这两个配置参数怎么样配置会更好

参考文章：innodb_flush_log_at_trx_commit和sync_binlog参数详解

推荐：参数都设置为1。

9.请完整详细一条更新语句，执行器和 InnoDB 引擎的运行过程？

update T set c=c+1 where ID=2;
  • 执行器操作引擎 ID=2 行数据，由于 ID 是主键，通过索引树可以直接找到。如果在内存中，直接返回执行器，如果内存没有，数据页从磁盘读入内存中。
  • 执行器拿到数据后，进行更新数据，再次调用引擎接口写入这行数据。
  • 引擎在将数据更新入内存中，同时将更新操作记录写入 redo log。
  • 此时 redo log 处于 prepare 状态，然后告知执行器执行完成，可以提交事务。
  • 执行器生成操作的 binlog 并写入磁盘。
  • 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

（注重：关于内存的流程）

img

10.为什么redo log在更新语句过程中会有两种状态呢？

第一种解答：

先用反证法去进行证明（例子依旧是上面的更新语句）：

  • 先写 redo log 后写 binlog。

现在举例一个场景：redo log 写完了，binlog 还没有开始，这个时候如果 Mysql 进程异常重启。系统崩溃了，也能够把数据恢复起来，此时 c=1（c 的初值为 0）。但是 binlog 由于没有写入这一条记录，两个日志的状态逻辑上不一致。如果你后面用 binlog 去恢复数据，由于语句的丢失，恢复的数据为 c=0，与原库值不一致。

  • 先写 binlog 后写 redo log。

举例：binlog 写完了，redo log 还没有写完，这个时候系统崩溃了，后面恢复的数据由于 redo log 没有写，导致 c=0，但是此时 binlog 已经记录了这一条操作了，两个日志的状态逻辑上不一致。后面用 binlog 恢复数据时候，恢复数据 c=1 与原库值不一致。

将 redo log 的写入拆成了两个步骤：prepare 和 commit，称之为"两阶段提交"。

如果没有两阶段的提交，就会导致两个日志状态逻辑上不一致。

第二种解答：

redo log 进行两阶段提交：当数据发生了崩溃的时候，恢复数据会对 redo log 进行一次判断：

  • 如果 redo log 已经是 commit 状态，代表这个过程没有发生意外，日志记录都已经写入了。
  • 如果 redo log 处于 prepare 状态，需要通过 binlog 来判断此记录是否有效
    • 如果 binlog 没有记录，那么此记录无效，无效就会回滚事务。
    • 如果 binlog 记录成功了，那么此记录有效。

两阶段的提交，保证了两个日志状态逻辑上是一致的。

说点闲话：朋友暑期实习面试过了二面，三面估计都稳了，给我羡慕的啊，自闭了！！加油吧！慢慢准备的来！

avatar
文章
阅读
粉丝