Home 面试题 MySQL

# MySQL 面试题

# 关系型数据库和非关系型数据库

# MyISAM 和 InnoDB 的区别

MyISAM ： B+树 的叶子节点的 data 域存放的是数据的地址（非聚簇索引），即其索引文件和数据文件是分离的.
InnoDB ：主键索引的 B+树 的叶子节点的 data 域存放的是数据本身（聚簇索引），而对于非自建索引（辅助索引 / 二级索引）的 B+树 的叶子节点的 data 域存放的是主键的 id 值，需要对齐进行回表操作才能查到到全部的数据.

  1. MyISAM 索引采用的是非聚簇索引，索引的数据域存储数据的指针，而 InnoDB 主键采用的是聚簇索引，非主键采用非聚簇索引，索引的数据域存储主键的 id.
  2. MyISAM 不支持事务，但是每次查询都是原子操作； Innodb 支持 ACID 的事务，支持事务的四种隔离级别
  3. MyISAM 存储表的总行数； InnoDB 不存储表的总行数
  4. MyISAM 不支持外键约束，而 InnoDB 支持
  5. MyISAM 支持最小粒度所为表级锁； InnoDB 支持行级锁

# 覆盖索引

索引的值包含所需要查询的字段

# 为什么使用索引

索引是一种能过帮助数据库，高效的从磁盘中检索数据的一种数据结构.

  • 在关系型数据库中，通常使用 B-Tree 或哈希表等索引结构

  • 索引可以提高查询效率，建立索引的原则如下.

    1. 区分度高的列作为索引可以加快查询速度
    2. 经常出现在 where 中的列
    3. 表记录较少索引效果不好，没有必要建立索引
    4. 定义有外键的数据列一定要建立索引
    5. 更新频繁的数据列不适合建立索引

  • 索引会降低插入、删除、更新表的速度，因为在执行这些操作时，还需要操作索引文件，增大了系统开销.

  • 索引需要占用物理空间，除了索引表需要占用空间之外，每个索引也需要占用额外的存储空间。同时如果非聚餐索引很多，一旦聚簇索引改变，那么非聚簇索引也需要跟着改变，代价比较大

MySQL 中使用较多的是 Hash索引 和 B+树索引

对于哈希索引来说，底层的数据结构就是哈希表，因此对于绝大多数需求为单挑记录的查询的时候，可以选择哈希索引，查询性能高，其余的绝大多数场景使用 B + 树所以

# 聚簇索引和非聚簇索引的区别

  • 聚簇索引：主键索引

    1. 将数据存储在索引树的叶子节点上，可以减少一次查询（查询索引树的同时就能获得数据）
    2. 当需要对数据进行修改或删除时，需要更新索引树，增加系统开销（聚簇索引保证顺序，当中间有数据需要插入时，可能引起分页）

  • 非聚簇索引：二级索引 / 辅助索引

    1. 不将数据存储在索引树中，而是存储在数据页中；查询数据时需要两次查询，一次用于查询索引树，获取数据页的地址，在通过数据页的地址来查询数据（如果索引覆盖的话实际上不用回表）
    2. 当需要对数据进行修改或删除时，不需要更新索引树，减少了系统开销

# 唯一索引与普通索引

  • 区别：唯一索引的索引值必须唯一，但允许有空值（即可以是单列唯一索引也可以是联合唯一索引）；普通索引索引值不一定唯一，且允许有空值.

    唯一索引可以保证数据的唯一性

# 联合索引、组合索引、复合索引

组合索引、联合索引、复合索引指代都是一个意思，一个索引包含多个列

  • 最左前缀匹配 ：组合索引的第一个字段必须出现在查询语句中，还不能跳跃，只有这样才能让组合索引生效

    // 假设给 username，age 创建了组合索引
    // 组合索引生效
    select * from user where username = 'xujiahao' and age = 24;
    select * from user where username = 'xujiahao';
    select * from user where age = 24 and username = 'xujiahao';   // 特殊情况
    // 组合索引失效
    select * from user where age = 24;
    select * from user where city = 'shenzhen' and age = 18

# 索引失效的情况

  • 使用 like 查询并且左边带 %

    索引往往使用的是 B + 树结构，是一个有序排列的。百分号放在前面进行一个模糊查找时，前面都能匹配，很有可能就无法更具索引的有序性来进行查找了.

  • 隐式类型转换。索引字段是 int ，而使用字符串类型去查询

  • where 条件里面对索引使用运算或者函数

  • 使用 or 且存在非索引列

  • where 条件中对两列作比较

  • 使用 in （和数据量有关系）

  • 使用 order by

  • is null 、 is not null 、 ≠

# InnoDB 为什么使用自增 id 作为主键

  • 使用自增 id 作为主键，那么在建立索引时，每次插入新的记录，记录会顺序添加到当前索引的后续位置，直到一页满为至才会开辟一个新的页
  • 如果使用非自增主键，由于每次插入的主键值比较随机，每次新纪录可能需要插入到现在索引的中间位置，会操作频繁的移动（内部单链表）和分页

# B 树与 B + 树

查询一个数据时，在数据页内往往使用二分查找

  • B 树：一种平衡的多叉搜索树

    1. 每个节点包含多个关键字和对应的指针，即 B 树的每个节点都会存储数据
    2. B 树的叶子节点之间是指针相连的，不支持顺序访问

  • B + 树：一种平衡的多叉搜索树

    1. 所有的关键字都存储在叶子节点上，非叶子节点不存储数据，只存储索引和指向孩子节点的指针
    2. 叶子节点通过指针连接起来，形成一个有序的链表，支持顺序访问和范围查找（MySQL 底层实际是一个双向链表）

  • MySQL 选择 B + 树作为索引结构的原因

    1. 范围查询和顺序访问的性能更高：B + 树的叶子节点通过指针连接成一个双向链表，可以很好的支持数据库的范围查询，同时在连续的数据查询中，B + 树可以利用磁盘的预读特性，提高数据顺序访问性能，嫌少磁盘 I/O
    2. 更少的磁盘 I/O：B + 树的非叶子节点只存储索引和指向孩子的节点指针，不存储数据，相比于 B 树，每个物理页中可以存储更多的关键字，减少了磁盘 I/O 次数（节点可以存储更多的关键字，可以使得 B + 树高更矮)
    3. 数据检索方面，由于所以的数据均存储在叶子节点上，每次查询的 IO 次数稳定.

# 数据库三大范式

数据库设计表结构时所遵循的规范，目的是为了减少冗余，提高数据存储和使用的效率

  • 第一范式：数据库表中的每个列都必须是原子性的，不可再分；同时每个表有一个主键.
  • 第二范式：表中非主键列必须完全依赖于主键，而不是部分依赖于主键. (如果有非主键列部分依赖于主键，需要将改列拆分并且放置于另外一张表中)
  • 第三范式：表中的非主键列之间不能存在传递依赖关系

在实际的设计，经常也有反范式的现象，通过冗余的数据，来避免跨表的连接查询，以此来提高数据库的查询效率

# 数据库表的连接方式

  • 内连接
  • 左连接
  • 右连接
  • 全连接
  • 自连接

# char 与 varchar

  • char ：定长字符类型。如果存储的字符串长度小于指定长度，会使用空格进行填充

  • varchar ：变长字符类型。它实际占用的存储空间为字符串的实际空间和用于存储字符串长度信息的空间

    1. 存储效率： char 定长，无论字符串的实际长度多少，它占用的存储空间是固定，会浪费一定的空间. varchar 是变长的，其占用的实际空间会随着字符串的大小而变动，存储效率较高
    2. 查询效率： char 在搜索和排序方面的操作效率要高于 varchar 类型

# MySQL 锁的种类

  • 读写锁（锁的属性）

    1. 共享锁 Share Lock ：读锁（ S锁 ），当一个事务为数据加上读锁之后，其他事务只能对该数据加读锁。为了支持高并发读取数据.
    2. 排他锁 Exclusive Lock ：写锁（ X锁 ），当一个事务为数据加上了写锁之后，其他请求不能再为这条数据加任何锁.

  • 锁的粒度

    1. 行锁：锁住表中的某一行或多行，粒度小，冲突小
    2. 表锁：锁住整张表，粒度大，容易冲突
    3. 记录数 Record Lock ：是行锁的一种，锁住表中的一条记录（命中的条件字段是一定是唯一索引）
    4. 页锁：一种粒度介于行所和表锁之间的一种锁。每次锁定相邻的一组记录.
    5. 间隙锁 Gap Lock ：属于行锁的一种
    6. 临建锁 Next-Key Lock

  • 意向锁

    如果当事务 A 加锁成功后设置一个状态告诉后面的人：已经有人对表里的行加了一个排他锁，不能再对整个表加共享锁或排他锁，那么后面需要对整个表加锁的人获取这个状态就知道是不是可以对表加锁，避免对这个索引表的每个节点扫描查看他们是否加锁，这个锁就是意向锁.

    • 意向共享锁：当一个事务试图对整个表加共享锁时，首先需要获取这个表的意向共享锁
    • 意向排他锁：当一个事务试图对整个表加排他锁时，首先需要获取这个表的意向排他锁

# MySQL 执行计划

执行计划： sql 的执行查询的顺序，以及如何使用索引

在语句前面添加 explain 即可打印执行计划

  • 执行计划参数

    1. id ：

# MySQL 中的死锁

死锁是指两个或两个以上的进程在执行过程中，因争夺资源而造成的的一种互相等待的现象.

产看死锁的命令： show engine innodb status ；可以通过设置 innodblockwait_timeout 来设置曹超时时间或主动回滚死锁中的某个事务.

# 数据库的四大特性 ACID

  • 原子性Atomicity ：保证多个 DML 操作的原子性，要么都执行成功，要么执行失败

    执行失败意味着原本执行成功的数据要进行回滚，其是由 undo_log 表来保证（在事务执行的过程中，把修改之前的数据快照保存到表中，一旦出现错误，就直接从 undo_log 表中读取数据，进行反向操作）

  • 一致性Consistency

    由其他三大特性共同来保证

  • 隔离性Isolation ：多个并行事务对同一个数据进行操作的时候，避免多个事务之间的互相干扰导致数据混乱的问题.

    四种隔离级别： RU/RC/RR/Serializable . InnoDB 默认采用的 RR 隔离级别，通过 MVCC 来解决脏读、不可重复度的问题，使用了行锁 / 表锁来解决幻读的问题

  • 持久性Durability ：只要事务提交成功，那么这个数据的结果的影响一定是永久的。不能因为数据库的宕机或其他原因导致数据变更失效的问题.

    从理论上来说，事务提交之后，直接把数据放到磁盘即可，但是磁盘 IO 的读写效率很低，所以往往会先将数据放到缓冲区（ InnoDB 的 Buffer Pool ），也就是说当数据发生变更之后，先更新缓冲区中，然后在合适的时间再持久化到磁盘中，这样的优化设计可能由于宕机导致数据变更失效的问题。在 InnoDB 中，通过 redo_log 来保证数据库的持久性（存储了数据变更后的值），当我们通过事务进行数据变更的时候，除了修改内存缓冲区的数据以外，还会把本次修改的值追加到 redo_log 日志中

# 事务隔离级别

InnoDB 中 隔离性是由 MySQL 的各种锁机制以及 MVCC 来实现的

  • 读未提交 read_uncommitted(RU) ：有脏读问题
  • 读已提交 read_committed(RC) ：有不可重复读的问题
  • 可重复读 repeatable-read(RR) ：会有幻读的问题
  • 串行化 serializable

# 数据库并发事务会带来哪些问题

  • 脏读
  • 不可重复读
  • 幻读
  • 丢失修改

# MVCC

多版本并发控制：读取数据时通过一种类似于快照的方式将数据保存下来，这样读锁和写锁就不冲突了；不同的事务会看到特定版本的数据（版本链）

  • MVCC 只在读已提交和可重复读两个隔离级别下工作. ( read_uncommited 总是读取最新的数据； serializable 会对所以的读操作加锁)

    聚簇索引中有两个隐藏的列

    1. trx_id ：存储每次对这条索引记录进行修改的 事务ID (事务 ID 根据时间顺序依次递增)
    2. roll_pointer ：上一版本的记录地址.（每次对聚簇索引记录有修改操作时，都会把老版本写入 undo 日志中，而这个修改后的新记录的 roll_pointer 则会指向日志中旧版本）

    ReadView 是快照读（普通的 select 查询语句）执行时 MVCC 提取数据的依据

    1. m_ids ：当前活跃的事务编号的集合（还未提交的事务集合）
    2. min_trx_id ：最小活跃事务的编号
    3. max_trx_id ：预分配事务的编号，当前最大事务 ID+1
    4. creator_trx_id ： ReadView 创建者的事务编号

    当前读 ：指执行如下指令对数据进行读取

    • Insert、Update、Delete、Select ... for Update、Select ... lock in share mode

  • 读已提交隔离级别下 MVCC 的工作方式：在每一次执行快照读的时候生成 ReadView

    1. 判断版本链的 trx_id 是否等于 creator_trx_id ，若等于说明对该条记录的更新就是当前事务，则可以访问

    2. 判断版本链的 trx_id 是否小于 min_trx_id ，若小于说明对该记录的处理已经提交，可访问

    3. 判断版本链的 trx_id 是否大于等于 max_trx_id ，如果成立说明对该条记录的操作是在生成 ReadView 之后，不可访问

    4. 判断版本链的 trx_id 是否在 m_idx 中，如果不存在，说明对该条记录的操作已提交，可以访问，若存在，则不可访问

  • 可重复读级别下 MVCC 的工作方式：仅在第一次执行快照读的时候生成 ReadView ，从而保证事务内对于一条记录的查询是一致的

    多次连续的快照读， ReadView 会复用，没有幻读问题。但是两次连续的 快照读 之间存在 当前读 时， ReadView 会重新生成，导致产生幻读

# MySQL 主从同步原理

MySQL主从同步 主要涉及到三个线程，主节点 master 上的 binlog dump thread ，从节点 slave 上的 IO thread 和 sql thread . 主节点的 binlog 维护的是主库记录的所以变更记录.

  1. 主节点的 log dump 线程，当 binlog 有变动时， log dump 会读取 binlog 日志变动的内容并发送到从节点
  2. 从节点的 IO thread 接收到 binlog 内容 ，并将其写入 relay log 文件中.
  3. 从节点的 sql thread 读取 realy log 新增的内容并执行，最终保持主从数据库的一致性.

注：主节点使用 binlog 文件和 position 偏移量来定位主动同步的位置（增量同步）

由于 MySQL 的默认的主从同步方式是异步 的，主节点把日志发送给从节点后不关系从库是否已经处理，这样会产生一个问题：主节点挂了，从节点处理失败了，那么这时从节点升级为主节点后，就会有一部分日志丢失了，有一下解决方案：

  • 全同步复制：主节点写入 binlog 并将数据同步到从节点时，需要等待所以的从节点执行完成之后，在将结果返回给客户端. (影响效率)
  • 半同步复制：主节点写入 binlog 并将数据同步到从节点时，不需要等待所以的从节点执行完成，当收到至少一个从节点的同步完成就将结果返回给客户端.
## 慢查询如何优化
  • 检查是否建了索引，查询是否走了索引
  • 检查是否 load 了多余的数据列
  • 返回的数据量太多

# MySQL Ngram 全文检索

# 跳表与 B + 树

  • 跳表：将部分链表节点提取出来，再构建出一个新的链表，一直递归的做上述的过程，形成的新的数据结构就是跳表。每次在进行查询操作的时候，先查询上层的链表，看落在那个范围，在递归去下层链表搜索. （跳表一条数据一个节点）

跳表的写入性能比 B + 树好：B + 树在进行插入数据的时候，由于 B + 树的一个多叉的平衡树，可能会导致拆分合并索引数据页。跳表则是独立插入，并根据随机函数确定是否在上层中增加节点。没有旋转维持平衡的开销

Edited on Views times

Give me a cup of [coffee]~(￣▽￣)~*

Value WeChat Pay

WeChat Pay