🌱日拱一卒

搜索

SearchSearch

MySQL 面试题

2024年7月23日25分钟阅读

能说下 MyISAM 和 InnoDB 的区别吗？

InnoDB 与 MyISAM 的区别

MyISAMInnoDB
事务❌✅
行锁❌✅
外键❌✅
全文搜索✅❌
表空间的大小较小较大，约为 MyISAM 的 2 倍

MyISAM

MyISAM 是 MySQL 5.5 版本之前的默认引擎，支持全文检索、压缩、空间函数等，但是不支持事务和行级锁，所以一般用于有大量查询少量插入的场景来使用。而且 MyISAM 不支持外键，索引和数据是分开存储的。

InnoDB

InnoDB 是 MySQL 5.5 版本之后的默认引擎，基于聚簇索引建立的，支持事务、外键、行级锁，并且通过 MVCC 来支持高并发，索引和数据存储在一起。

如何选择

  1. 如果需要事务，选择 InnoDB，不需要则选择 MyISAM。

  2. 如果大部分表操作都是查询，选择 MyISAM，有写又有读选 InnoDB。

  3. 如果系统崩溃导致数据难以恢复，且成本高，不要选择 MyISAM。

参考链接

MySQL 查看修改存储引擎总结 MySQL 存储引擎 MyISAM 和 InnoDB 的区别

指向原始笔记的链接

什么是索引？

索引（Index）是帮助 MySQL 高效获取数据的数据结构。简单的理解，索引类似于字典里面的目录。

索引的优点和缺点

优点：

  • 可以提高数据检索的效率，降低数据库的 IO 成本，类似于书的目录。

  • 通过索引列对数据进行排序，降低数据排序的成本，降低了 CPU 的消耗。

缺点：

  • 索引会占据磁盘空间

  • 索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL 不仅要保存数据，还有保存或者更新对应的索引文件。

常见的索引类型有哪些？

  • 从数据结构的角度分为：B+ 树索引、hash 索引

  • 从物理存储的角度分为：聚簇索引和非聚簇索引

  • 从逻辑的角度分为：主键索引、普通索引、唯一索引、联合索引以及空间索引

什么是聚簇索引（聚集索引）

聚簇索引（主键索引）并不是一种单独的索引类型，而是一种数据存储方式。聚簇索引将索引和数据行放到了一块，找到索引也就找到了数据。因为无需进行回表操作，所以效率很高。

InnoDB 中必然会有，且只会有一个聚簇索引。通常是主键，如果没有主键，则优先选择非空的唯一索引，如果唯一索引也没有，则会创建一个隐藏的 row_id 作为聚簇索引。至于为啥会只有一个聚簇索引，其实很简单，因为我们的数据只会存储一份。

非聚簇索引（辅助索引）则将数据存储和索引分开，找到索引后，需要通过对应的地址找到对应的数据行。MyISAM 的索引方式就是非聚簇索引。

MySQL 中非聚簇索引分为：

  • 单列索引：即一个索引只包含一个列。
#格式
create index 索引名称 on 表名(列名)
#例子
create index idx_name on employees(name )
  • 唯一索引：索引列的值必须唯一，允许有一个空值。比普通索引的性能要好。
#格式
create unique index 索引名称 on 表名(列名)
#例子
create unique index idx_unique_name on employees( name)
  • 联合索引（组合索引）：一次性为表中的多个字段一起创建索引，最左前缀法则 (如何命中联合索引中的索引列)。
    • 注意：一个联合索引建议不要超过 5 个列
#格式
create index 索引名称 on 表(列1 ,列2 ,列3)
#例子
create index idx_name_age_position on employees(name ,age , position)
  • 全文索引 进行查询的时候，数据源可能来自于不同的字段或者不同的表。比如去百度中查询数据，千锋教育，来自于网页的标题或者网页的内容。MyISAM 存储引擎支持全文索引。在实际生产环境中，并不会使用 MySQL 提供的 MyISAM 存储引擎的全文索引功能来是实现全文查找。而是会使用第三方的搜索引擎中间件比如 ElasticSearch (多) 、Solr。
指向原始笔记的链接

为什么要用 B+ 树存储索引？而不用红黑树、Hash、B 树？

红黑树：如果在内存中，红黑树的查找效率比 B 树更高，但是涉及到磁盘操作，B 树就更优了。因为红黑树是二叉树，数据量大时树的层数很高，从树的根结点向下寻找的过程，每读 1 个节点，都相当于一次 IO 操作，因此红黑树的 I/O 操作会比 B 树多得多。

hash 索引：如果只查询单个值的话，hash 索引的效率非常高。但是 hash 索引有几个问题：

  1. 不支持范围查询
  2. 不支持索引值的排序操作
  3. 不支持联合索引的最左匹配规则

B+ 树相对于 B 树而言，它有两种特性：

  1. B+ 树非叶子节点不存储数据，在相同的数据量下，B+ 树更加矮壮（数据都存储在叶子节点上，非叶子节点的存储能存储更多的索引，所以整棵树就更加矮壮）
  2. B+ 树叶子节点之间组成一个链表，方便于遍历查询（遍历操作在 MySQL 中比较常见）

MySQL 中的索引叶子节点存放的是什么？

MyISAM 和 InnoDB 都是采用的 B+ 树作为索引结构，但是叶子节点的存储上有些不同。

MyISAM：主键索引和辅助索引（普通索引）的叶子节点都是存放 key 和 key 对应数据行的地址。在 MyISAM 中，主键索引和辅助索引没有任何区别。

InnoDB：主键索引存放的是 key 和 key 对应的数据行。辅助索引存放的是 key 和 key 对应的主键值。因此在使用辅助索引时，通常需要检索两次索引，首先检索辅助索引获得主键值，然后用主键值到主键索引中检索获得记录。

什么是回表查询？

InnoDB 中，对于主键索引，只需要走一遍主键索引的查询就能在叶子节点拿到数据。

而对于辅助索引，叶子节点存储的是 key + 主键值，因此需要再走一次主键索引，通过主键索引找到行记录，这就是所谓的回表查询，先定位主键值，再定位行记录。

可以建立联合索引进行索引覆盖，来解决回表问题。

走辅助索引，一定会出现回表查询吗？

不一定，如果查询语句所要求的字段全部命中了索引，那么就不必再进行回表查询。

比如，有一个 user 表，主键为 id，name 为普通索引，在执行：select id, name from user where name = 'joonwhee' 时，通过 name 的索引就能拿到 id 和 name 了，因此无需再回表去查数据行了。

什么是覆盖索引？

覆盖索引

覆盖索引指的是在一次查询中，如果一个索引包含或者说覆盖所有需要查询的字段的值，我们就称之为覆盖索引，而不再需要回表查询。而要确定一个查询是否是覆盖索引，我们只需要 explain sql 语句看 Extra 的结果是否是 Using index 即可。

覆盖索引是一种很常用的优化手段。因为在使用辅助索引的时候，我们只可以拿到主键值，相当于获取数据还需要再根据主键查询主键索引再获取到数据。但是试想下这么一种情况，在上面 abc_innodb 表中的组合索引查询时，如果我只需要 abc 字段的，那是不是意味着我们查询到组合索引的叶子节点就可以直接返回了，而不需要回表。这种情况就是覆盖索引。

指向原始笔记的链接

联合索引（复合索引）的底层实现？最左前缀原则？

联合索引底层还是使用 B+ 树索引，并且还是只有一棵树，只是此时的排序会：首先按照第一个索引排序，在第一个索引相同的情况下，再按第二个索引排序，依次类推。

这也是为什么有“最佳左前缀原则”的原因，因为右边（后面）的索引都是在左边（前面）的索引排序的基础上进行排序的，如果没有左边的索引，单独看右边的索引，其实是无序的。

还是以字典为例，我们如果要查第 2 个字母为 k 的，通过目录是无法快速找的，因为首字母 A - Z 里面都可能包含第 2 个字母为 k 的。

最左前缀原则：当你创建了一个联合索引，该索引的任何最左前缀都可以用于查询。比如创建了一个联合索引 (a, b, c)，该索引的所有前缀为 (a)、(a, b)、(a, b, c)，包含这些列的所有查询都会使用该索引进行查询。

不过，遇到范围查询 (>、<、between、like 左匹配等) 就不能进一步匹配了。比如 a=1 and b<2 and c=3

union 和 union all 的区别

  • union all：对两个结果集直接进行并集操作，记录可能有重复，不会进行排序。

  • union：对两个结果集进行并集操作，会进行去重，记录不会重复，按字段的默认规则排序。

  • 从效率上说，UNION ALL 要比 UNION 更快。

什么是事务？

事务是一系列的操作，要么全部成功，要么全部失败，事务符合 ACID 特性。

事务的基本特性 / 什么是 ACID？

ACID

原子性（Atomic）：一个事务中的操作要么全部成功，要么全部失败，没有中间状态。

  • 在出错时中止事务，并将部分完成的写入全部丢弃。《数据密集型应用系统设计》P214

一致性（Consistency）：在事务执行前后，数据库的一致性约束没有被破坏。

  • 假设用户 A 和用户 B 两者的钱加起来一共是 5000，那么不管 A 和 B 之间转账了几次，事务结束后两个用户的钱加起来还得是 5000

隔离性（Isolation）：事务的执行应该与其他并发执行的事务相互隔离，每个事务都感觉不到其他事务的存在。

持久性（Durability）：一旦事务提交，其对数据库的修改应该是永久性的，即使在系统故障的情况下也不应丢失。

Reference

MySQL 如何保证 ACID

指向原始笔记的链接

MySQL 如何保证 ACID？

MySQL 如何保证 ACID

A 原子性：利用 InnoDB 的 undo log。undo 日志记录需要回滚的日志信息，当事务回滚时能够撤销所有已经成功执行的 SQL 语句

C 一致性：一致性是事务追求的最终目标，原子性、持久性和隔离性都是为了保证一致性的手段。另外，程序代码要保证业务上的一致性，比如购买操作只扣除用户的余额，不减库存，肯定无法保证状态的一致。

I 隔离性：利用锁和 MVCC 机制。

D 持久性：利用 InnoDB 的 redo log。当做数据修改的时候，不仅在内存中操作，还会在 redo 日志中记录这次操作。当事务提交的时候，会将 redo 日志进行刷盘 (redo log 一部分在内存中，一部分在磁盘上)。当数据库宕机重启的时候，会将 redo 日志中的内容恢复到数据库中，再根据 undo log 和 binlog 内容决定回滚数据还是提交数据。

Reference

浅谈 MySQL 如何保证 ACID MySQL 如何保证事务的 ACID 特性的

指向原始笔记的链接

事务并发执行遇到的问题

事务并发执行遇到的问题

脏写（Dirty Write）：一个事务修改了另一个未提交事务修改过的数据

脏读（Dirty Read）：一个事务中多次读取同一字段时，读取到其他事务未提交的数据

不可重复读（Non-Repeatable Read）：一个事务中多次读取同一字段时，读取到其他事务已提交的数据

幻读（Phantom）：在一个事务中使用相同的 SQL 读取两次，第二次读取到了其他事务新插入的行

不可重复读和幻读的区别
  • 不可重复读的重点是内容修改或者记录减少比如多次读取一条记录发现其中某些记录的值被修改；
  • 幻读的重点在于记录新增比如多次执行同一条查询语句（DQL）时，发现查到的记录增加了。

幻读其实可以看作是不可重复读的一种特殊情况，单独把区分幻读的原因主要是解决幻读和不可重复读的方案不一样。

指向原始笔记的链接

MySQL 的隔离级别 / 如何解决脏读、幻读、不可重复读

MySQL 的隔离级别

读未提交（ read uncommitt ）：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

读已提交（ read committed ）：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

可重复读（repeatable read）：InnoDB 默认级别，对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，有可能产生幻读。

可串行化（serializable）：最高的隔离级别，它要求所有的 SQL 按照顺序执行。这样就克服了脏读、幻读、不可重复读。它的效率最低，但也是最安全的。

不同的隔离级别是为了解决 脏读、幻读、不可重复读

脏读不可重复读幻读
读未提交✅✅✅
读已提交❌✅✅
可重复读❌❌✅
可串行化❌❌❌
指向原始笔记的链接

什么是当前读和快照读？

  • 快照读又叫一致性读，读取的是快照数据。不加锁的简单的 SELECT 都属于快照读，即不加锁的非阻塞读；
  • 当前读读取的是记录的最新版本（最新数据，而不是历史版本的数据），读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。加锁的 SELECT，或者对数据进行增删改都会进行当前读。

什么是多版本并发控制（MVCC）

MVCC（Multiversion Concurrency Control），多版本并发控制。MVCC 是通过数据行的多个版本管理来实现数据库的并发控制。这项技术使得在 InnoDB 的事务隔离级别下执行一致性读操作有了保证。换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值，这样在做查询的时候就不用等待另一个事务释放锁。

MVCC 只作用于读已提交（Read Committed）和可重复读（Repeatable Read）级别。因为读未提交（Read Uncommitted）总是读取最新的数据版本，而不是符合当前事务版本的数据行。而可串行化则会对所有读取的行都加锁。这两种级别都不需要 MVCC 的帮助。

为什么使用 MVCC

在早期的数据库中，只有读读之间的操作才可以并发执行，读写，写读，写写操作都要阻塞，这样就会导致 MySQL 的并发性能极差。

采用了 MVCC 机制后，只有写写之间相互阻塞，其他三种操作都可以并行，这样就可以提高 MySQL 的并发性能。

说白了 MVCC 就是为了实现读 - 写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现。

注意 MVCC 仅仅在纯 select 时有效 (不包括 select for update,lock in share mode 等加锁操作,以及 updateinsert 等)。

MVCC 带来的好处

  • 在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能

  • 同时还可以解决脏读，幻读（快照读），不可重复读等事务隔离问题，但不能解决更新丢失问题

MVCC 机制的原理

ReadView

  • m_ids：表示在生成 ReadView 时当前系统中活跃的读写事务的事务 id 列表。

  • min_trx_id：表示在生成 ReadView 时当前系统中活跃的读写事务中最小的事务 id，也就是 m_ids 中的最小值。

  • max_trx_id：表示生成 ReadView 时系统中应该分配给下一个事务的 id 值。

  • creator_trx_id：表示生成该 ReadView 的事务的事务 id。

隐藏列

InnoDB 存储引擎中，它的聚簇索引记录中都包含两个必要的隐藏列，分别是：

  • trx_id：事务 id，每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的事务 id 赋值给 trx_id 隐藏列。

  • roll_pointer：回滚指针，每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到 undo log 中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。

事务链

每次对记录进行修改时，都会记录一条 undo log 信息，每一条 undo log 信息都会有一个 roll_pointer 属性 (INSERT 操作没有这个属性，因为之前没有更早的版本)，可以将这些 undo 日志都连起来，串成一个链表。

判断记录的某个版本是否可见

  • 如果被访问版本的 trx_id 属性值与 ReadView 中的 creator_trx_id 值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。

  • 如果被访问版本的 trx_id 属性值小于 ReadView 中的 min_trx_id 值，表明生成该版本的事务在当前事务生成 ReadView 前已经提交，所以该版本可以被当前事务访问。

  • 如果被访问版本的 trx_id 属性值大于或等于 ReadView 中的 max_trx_id 值，表明生成该版本的事务在当前事务生成 ReadView 后才开启，所以该版本不可以被当前事务访问。

  • 如果被访问版本的 trx_id 属性值在 ReadView 的 min_trx_id 和 max_trx_id 之间，那就需要判断一下 trx_id 属性值是不是在 m_ids 列表中，如果在，说明创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问。

MySQL 的可重复读怎么实现的？

InnoDB 在每行记录后面保存两个隐藏的列，分别保存了数据行的创建版本号和删除版本号。每开始一个新的事务，系统版本号都会递增。事务开始时刻的版本号会作为事务的版本号，用来和查询到的每行记录的版本号对比。在可重复读级别下，MVCC 是如何操作的：

  • SELECT：必须同时满足以下两个条件，才能查询到

    1. 只查版本号早于当前版本的数据行。这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。
    2. 行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。

  • INSERT：为插入的每一行保存当前系统版本号作为创建版本号。

  • DELETE：为删除的每一行保存当前系统版本号作为删除版本号。

  • UPDATE：插入一条新数据，保存当前系统版本号作为创建版本号。同时保存当前系统版本号作为原来的数据行删除版本号。

主键使用自增 ID 还是 UUID?

  • 自增 ID 是有序的，而 UUID 是随机的。如果主键是有序的，数据库可以具有更好的性能
  • 自增 ID 所需的存储空间比 UUID 要小（64 bit < 128 bit）
  • 由于自增 ID 比 UUID 更加简单，因此生成自增 ID 的生成速度也比 UUID 更快
  • 自增 ID 与数据相关，主键会暴露出去的话，自增 ID 会显示当前表中的数据规模；而 UUID 则无此风险
  • 自增 ID 在分布式的环境下无法保证唯一，而 UUID 在分布式环境下也可以保证唯一
  • 综上所述，自增 ID 在性能上更有优势，而 UUID 则更加适应分布式场景

redo log、binlog、undo log 区别与作用

redo log（重做日志）

  • 确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启 mysql 服务的时候，根据 redo log 进行重做，从而达到事务的持久性这一特性。

binlog（二进制日志）

  • 用于复制，在主从复制中，从库利用主库上的 binlog 进行重播，实现主从同步。 用于数据库的基于时间点的还原。

undo log（回滚日志）

  • 保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），即非锁定读（快照读）。

锁的分类

锁的分类

  1. 根据性能划分：乐观锁和悲观锁
    • 悲观锁：悲观锁认为自己在使用数据的时候一定有别的线程来修改数据，因此在获取数据的时候会先加锁，确保数据不会被别的线程修改。（行锁，表锁，读锁，写锁，select...lock in share mode，select…for update）
    • 乐观锁：乐观锁认为自己在使用数据时不会有别的线程修改数据，所以不会添加锁，只是在更新数据的时候去判断之前有没有别的线程更新了这个数据。（版本号机制或 CAS）
  2. 根据数据的操作粒度划分：表锁和行锁
    • 表锁：SQL 没有命中索引，锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突概率高，并发度最低。
    • 行锁： SQL 命中了索引，锁住的就是命中条件的数据行。开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。
    • InnoDB 有行锁和表锁，MyIsam 只有表锁。
  3. 根据数据库的操作类型划分：读锁和写锁
    • 这两种锁都属于悲观锁
    • 读锁(共享锁) ：多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改
    • 写锁(排他锁) ：一个事务给一行数据加了写锁之后，在释放写锁之前，其他事务不能对这行数据进行读和修改
指向原始笔记的链接

InnoDB 的行锁是怎么实现的？

  • 通过索引上的索引项来实现的。意味者：只有通过索引条件检索数据，InnoDB 才会使用行级锁，否则，InnoDB 将使用表锁。

  • 对于主键索引：直接锁住主键索引即可。

  • 对于普通索引：先锁住普通索引，接着锁住主键索引，这是因为一张表的索引可能存在多个，通过主键索引才能确保锁是唯一的，不然如果同时有 2 个事务对同 1 条数据的不同索引分别加锁，那就可能存在 2 个事务同时操作一条数据了。

InnoDB 锁算法（锁模式）有哪几种？

  • Record lock：记录锁，单条索引记录上加锁，锁住的永远是索引，而非记录本身。

  • Gap lock：间隙锁，在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，不包括该索引记录本身。间隙锁是可重复读隔离级别下用来修复幻读才引入的一种锁。

  • Next-key lock：Record lock 和 Gap lock 的结合，不仅锁住记录本身，也锁住索引之间的间隙。

MySQL 主从同步的原理

  1. master 提交完事务后，写入 binlog
  2. slave 连接到 master，获取 binlog
  3. master 创建 dump 线程，推送 binglog 到 slave
  4. slave 启动一个 IO 线程读取同步过来的 master 的 binlog，记录到 relay log 中继日志
  5. slave 再开启一个 sql 线程读取 relay log 事件并在 slave 执行，完成同步
  6. slave 记录自己的 binglog

由于 MySQL 默认的复制方式是异步的，主库把日志发送给从库后不关心从库是否已经处理，这样会产生一个问题就是假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了。由此产生两个概念。

全同步复制 主库写入 binlog 后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这种方式的性能会受到严重影响。

半同步复制 从库写入日志成功后返回 ACK 确认给主库，主库收到至少一个从库的确认就认为写操作完成。

主从同步的延迟怎么解决呢？

  1. 针对特定的业务场景，读写请求都强制走主库
  2. 读请求走从库，如果没有数据，去主库做二次查询

explain 用过吗，有哪些字段分别是啥意思？

  • id：标识符

  • select_type：查询的类型

  • table：输出结果集的表

  • partitions：匹配的分区

  • type：表的连接类型

  • possible_keys：查询时，可能使用的索引

  • key：实际使用的索引

  • key_len：使用的索引字段的长度

  • ref：列与索引的比较

  • rows：估计要检查的行数

  • filtered：按表条件过滤的行百分比

  • Extra：附加信息

type 中有哪些常见的值？

按类型排序，从好到坏，常见的有：const > eq_ref > ref > range > index > ALL

  • const：通过主键或唯一键查询，并且结果只有 1 行（也就是用等号查询）。因为仅有一行，所以优化器的其余部分可以将这一行中的列值视为常量。

  • eq_ref：通常出现于两表关联查询时，使用主键或者非空唯一键关联，并且查询条件不是主键或唯一键的等号查询。

  • ref：通过普通索引查询，并且使用的等号查询。

  • range：索引的范围查找（>=、<、in 等）。

  • index：全索引扫描。

  • All：全表扫描

explain 主要关注哪些字段？

主要关注 type、key、row、extra 等字段。主要是看是否使用了索引，是否扫描了过多的行数，是否出现 Using temporary、Using filesort 等一些影响性能的主要指标。

平时是怎么进行慢 SQL 优化的？

  1. 在 where 条件和 order by 后的字段建立索引
  2. 考虑覆盖索引，避免回表查询
  3. 用 select 需要的列来代替 select *
  4. 避免在索引列使用函数，导致索引失效
  5. 查看 SQL 的执行计划，看看是否走了索引
  6. 考虑联合索引，并遵循最左匹配原则
  7. 如果是单库，考虑升级主从架构，实现读写分离（主写从读）
  8. 避免索引失效，避免 where 条件后进行 null 值的判断
  9. 避免 where 条件后使用 or 关键字来连接
  10. 数据量太大，考虑分库分表

Reference

关系图谱

反向链接