MySQL 一些面试题

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

  1. 第一范式（1NF）:列不可再分
  2. 第二范式（2NF）属性完全依赖于主键
  3. 第三范式（3NF）属性不依赖于其它非主属性 属性直接依赖于主键

2. MySQL 中 Select 语句完整的执行顺序

在 MySQL 中，SELECT 语句的执行顺序如下：

  1. FROM 子句：从指定的表或视图中选择数据。
  2. WHERE 子句：基于指定的条件对记录行进行筛选。
  3. GROUP BY 子句：将结果按照指定的列进行分组。
  4. 使用聚集函数进行计算；
  5. HAVING 子句：在分组后，进一步筛选出符合条件的分组。
  6. 计算所有的表达式
  7. SELECT 子句：指定要返回的列。
  8. DISTINCT 关键字（如果存在）：从结果集中消除重复行。
  9. ORDER BY 子句：对结果进行排序。
  10. LIMIT 子句（如果存在）：限制返回的行数。

3. 在 MySQL 中，如何定位慢查询?

具体回答：

我们当时做压测的时候有的接口非常的慢，接口的响应时间超过了 2 秒以上，因为我们当时的系统部署了运维的监控系统 Skywalking ，在展示的报表中可以看到是哪一个接口比较慢，并且可以分析这个接口哪部分比较慢，这里可以看到 SQL 的具体的执行时间，所以可以定位是哪个 sql 出了问题。

如果，项目中没有这种运维的监控系统，其实在 MySQL 中也提供了慢日志查询的功能，可以在 MySQL 的系统配置文件中开启这个慢日志的功能，并且也可以设置 SQL 执行超过多少时间来记录到一个日志文件中，我记得上一个项目配置的是 2 秒，只要 SQL 执行的时间超过了 2 秒就会记录到日志文件中，我们就可以在日志文件找到执行比较慢的 SQL 了。

解析：

方案一：使用开源工具，比如调试工具 Arthas，或者是运维工具 Prometheus 、Skywalking 等

方案二：MySQL 自带慢日志。慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认 10 秒）的所有 SQL 语句的日志

如果要开启慢查询日志，需要在 MySQL 的配置文件（/etc/my.cnf）中配置如下信息：

# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

配置完毕之后，通过以下指令重新启动 MySQL 服务器进行测试，查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log。

4. 如果 SQL 语句执行很慢，该如何分析呢？

具体回答：

如果一条 SQL 执行很慢的话，我们通常可以采用 EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息

  1. 比如在这里面可以通过 key 和 key_len 检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况
  2. 第二个，可以通过 type 字段查看 sql 是否有进一步的优化空间，是否存在全索引扫描或全盘扫描，
  3. 第三个，可以通过 extra 建议来判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

解析：

5. MySQL 支持的存储引擎有哪些，有什么区别 ?

存储引擎就是存储数据、建立索引、查询数据等技术的实现方式 。MySQL 支持多种存储引擎，常见的有 Memory、MyISAM，InnoDB。

  1. Memory 主要把数据存储在内存，支持表级锁，没有外键和事务，用的也不多。
  2. MyISAM 是早期的引擎，它不支持事务、只有表级锁、也没有外键
  3. InnoDB 存储引擎是 mysql5.5 之后是默认的引擎，它支持事务、外键、表级锁和行级锁

6. 存储引擎 MyISAM 与 InnoDB 区别?

  1. MyISAM 里面数据和索引是分开存储的，被存放在三个文件：frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)索引文件。而 InnoDB 是把索引和数据存储在同一个文件里面的
  2. MyISAM 不支持事务，而 InnoDB 支持 ACID 事务。
  3. MyISAM 只支持表锁，而 InnoDB 可以根据不同的情况去支持行锁、表锁。
  4. MyISAM 不支持外键，InnoDB 支持外键。
  5. MyISAM 索引是非聚集索引，InnoDB 索引是聚集索引
  6. MyISAM 的执行速度比 InnoDB 更快。MyISAM 适合频繁查询的情况，InnoDB 适合插入和更新操作较多的情况。

7. 了解过索引吗？（什么是索引？）

索引在项目中还是比较常见的，它是帮助 MySQL 高效获取数据的数据结构，通俗来说它相当于是书的目录。主要是用来提高数据检索的效率，降低数据库的 IO 成本，同时通过索引列对数据进行排序，降低数据排序的成本，也能降低了 CPU 的消耗。

使用索引可以大大加快数据的检索速度，但是建立索引需要占用物理空间，而且当对数据库进行增、删、改操作时，索引也需要进行维护。所以在选择是否使用索引以及如何使用索引时，需要根据具体的应用场景和数据特点来进行权衡。

8. 索引有哪几种类型？

  1. 主键索引: 数据列不允许重复，不允许为 NULL，一个表只能有一个主键。
  2. 唯一索引: 数据列不允许重复，允许为 NULL 值，一个表允许多个列创建唯一索引。
  3. 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引。
  4. 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引
  5. 普通索引: 基本的索引类型，没有唯一性的限制，允许为 NULL 值。
  6. 可以通过 ALTER TABLE table_name ADD INDEX index_name (column); 创建普通索引
  7. 可以通过 ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3); 创建组合索引
  8. 全文索引： 是目前搜索引擎使用的一种关键技术。可以通过 ALTER TABLE table_name ADD FULLTEXT (column); 创建全文索引。

9. InnoDB 索引的底层数据结构了解过嘛 ?

MySQL 的默认的存储引擎 InnoDB 采用的 B+ 树的数据结构来存储索引，选择 B+ 树的主要的原因是

  1. 第一是阶数更多，路径更短
  2. 第二是磁盘读写代价 B+ 树更低，非叶子节点只存储指针，叶子阶段存储数据，
  3. 第三是 B+ 树便于扫库和区间查询，叶子节点组合成一个双向链表，方便遍历查询

10. B 树和 B+ 树的区别是什么呢？

  1. 在 B 树中，非叶子节点和叶子节点都会存放数据，而 B+ 树的所有的数据都会出现在叶子节点，在查询的时候，B+ 树查找效率更加稳定
  2. 在进行范围查询的时候，B+ 树效率更高，因为 B+ 树都在叶子节点存储，并且叶子节点是一个双向链表

11. 什么是聚簇索引？什么是非聚簇索引 （二级索引）?

  1. 聚簇索引指的是数据与索引放到一块，B+ 树的叶子节点保存了整行数据，有且只有一个，它的选取规则是
    1. 如果存在主键，主键索引就是聚集索引。
    2. 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
    3. 如果表没有主键，或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引。
  2. 非聚簇索引指的是数据与索引分开存储，B+ 树的叶子节点保存对应的主键，可以有多个，一般是我们自己定义的索引，比如复合索引、普通索引，都是非聚簇索引

12. 知道什么是回表查询嘛 ?

  1. 回表查询是和聚簇索引和非聚簇索引是有关系的（直接问回表，则需要先介绍聚簇索引和非聚簇索引）
  2. 回表的过程就是通过二级索引找到对应的主键值，然后再通过主键值找到聚集索引中所对应的整行数据

13. 知道什么叫覆盖索引嘛 ?

  1. 覆盖索引是指 select 查询语句使用了索引，在返回的列，必须在索引中全部能够找到，如果我们使用 id 查询，它会直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。
  2. 如果按照二级索引查询数据的时候，返回的列中没有创建索引，有可能会触发回表查询，所以要尽量避免使用 select *，尽量在返回的列中都包含添加索引的字段

14. MySQL 超大分页怎么处理 ?

具体回答：

  1. 超大分页一般都是在数据量比较大时，我们使用了 limit 分页查询，并且需要对数据进行排序，这个时候效率就很低，我们可以采用覆盖索引和子查询来解决
  2. 先分页查询数据的 id 字段，确定了 id 之后，再用子查询来过滤，只查询这个 id 列表中的数据就可以了
  3. 因为查询 id 的时候，走的覆盖索引，所以效率可以提升很多

解析：

通过覆盖索引加子查询形式进行优化，SQL 可以这么写

select * 
from tb_sku t,
	(select id from tb_sku order by id limit 9000000,10) a
where t.id = a.id;

15. 索引创建原则有哪些？

  1. 这个情况有很多，不过都有一个大前提，就是表中的数据较大，我之前公司要求的是要超过 10 万以上，我们才会创建索引
  2. 并且添加索引的字段是查询比较频繁的字段，一般也是像作为查询条件（where），排序字段（order by）或分组（group by）的字段这些，更新频繁字段不适合创建索引
  3. 尽量选择区分度高的列作为索引来建立唯一索引，区分度越高，使用索引的效率越高。
  4. 还有就是尽量使用联合索引，减少单列索引，后面有新的字段了，尽量去扩展原来的组合索引，并且查询时一条 sql 的返回值，尽量使用覆盖索引，节省存储空间，避免回表，提高查询效率。
  5. 如果某一个字段的内容较长，我们会考虑使用前缀索引来使用
  6. 当然并不是所有的字段都要添加索引，这个索引的数量也要控制，因为添加索引也会导致新增改的速度变慢。

16. 什么情况下索引会失效 ?

  1. 这个情况比较多，我说一些自己的经验，以前遇到过的
  2. 比如，索引在使用的时候没有遵循最左前缀原则
  3. 第二个是，like 模糊查询，如果 % 号在前面也会导致索引失效。
  4. 如果在添加索引的字段上进行了运算操作、使用了函数或者数据类型转换也都会导致索引失效。
  5. or 语句前后没有同时使用索引。
  6. 我们之前还遇到过一个就是，如果使用了复合索引，中间使用了范围查询，右边的条件索引也会失效
  7. 在通常情况下，想要判断出这条 sql 是否有索引失效的情况，可以使用 explain 执行计划来分析

拓展：

最左前缀原则就是最左优先，在创建多列索引时，要根据业务需求，Where 子句中使用最频繁的一列放在最左边。

MySQL 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如 a=1 and b=2 and c>3 and d=4 如果建立 (a,b,c,d) 顺序的索引，d 是用不到索引的，如果建立 (a,b,d,c) 的索引则都可以用到，a,b,d 的顺序可以任意调整。

=和 in 可以乱序，比如 a=1 and b=2 and c=3 建立 (a,b,c) 索引可以任意顺序，MySQL 的查询优化器会帮你优化成索引可以识别的形式。

17. SQL 的优化的经验

我们会从这几方面考虑，比如建表的时候、使用索引、SQL 语句的编写、以及主从复制，读写分离

创建表的时候， 我们会参考的阿里出的那个开发手册嵩山版，就比如，在定义字段的时候需要结合字段的内容来选择合适的类型，如果是数值的话，像 tinyint、int 、bigint 这些类型，要根据实际情况选择。如果是字符串类型，也是结合存储的内容来选择 char 和 varchar 或者 text 类型

在使用索引的时候，参考上面索引创建原则那道题回答

SQL 语句编写时候， 比如 SELECT 语句务必指明字段名称，不要直接使用 select * ，还有就是要注意 SQL 语句避免造成索引失效的写法，如避免在 where 子句中对字段进行表达式操作；如果是聚合查询，尽量用 union all 代替 union ，union 会多一次过滤，效率比较低；如果是表关联的话，尽量使用 inner join ，不要使用用 left join right join，如必须使用一定要以小表为驱动（内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join 或 right join，不会重新调整顺序）

主从复制，读写分离， 如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响 可以采用读写分离的架构。读写分离解决的是，数据库的写入，影响了查询的效率。

还有一个是如果数据库量比较大的话，可以考虑分库分表。

18. MySQL 主从同步原理

  1. MySQL 主从复制的核心就是 bingLog 日志（二进制日志），bingLog 日志记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，它的步骤是这样的：
  2. 第一：主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。
  3. 第二：从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log 。
  4. 第三：从库重做中继日志中的事件，将改变反映它自己的数据

19. 事务的特性是什么？可以详细说一下吗？

事务是一组操作的集合，它是一个不可分割的工作单位，即这些操作要么同时成功，要么同时失败。

事务的特性是 ACID，分别指的是：原子性、一致性、隔离性、持久性。

  1. 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。原子性由 undo log 日志来保证，因为 undo log 记载着数据修改前的信息。
  2. 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
  3. 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。通过设置隔离级别来保证
  4. 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。持久性由 redo log 日志来保证

举个例子：

  1. A 向 B 转账 500，转账成功，A 扣除 500 元，B 增加 500 元，原子操作体现在要么都成功，要么都失败
  2. 在转账的过程中，数据要一致，A 扣除了 500，B 必须增加 500
  3. 在转账的过程中，隔离性体现在 A 像 B 转账，不能受其他事务干扰
  4. 在转账的过程中，持久性体现在事务提交后，要把数据持久化（可以说是落盘操作）

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

我们在项目开发中，多个事务并发进行是经常发生的，并发也是必然的，有可能导致一些问题

第一是脏读， 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

第二是不可重复读：比如在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

第三是幻读（Phantom read）：幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

21. 怎么解决并发事务造成的这些问题呢？MySQL 的默认隔离级别是？

解决方案是对事务进行隔离，MySQL 支持四种隔离级别，分别有

  1. 第一个是，未提交读（read uncommitted）它解决不了刚才提出的所有问题，一般项目中也不用这个。
  2. 第二个是读已提交（read committed）它能解决脏读的问题的，但是解决不了不可重复读和幻读。
  3. 第三个是可重复读（repeatable read）它能解决脏读和不可重复读，但是解决不了幻读，这个也是 mysql 默认的隔离级别。
  4. 第四个是串行化（serializable）它可以解决刚才提出来的所有问题，但是由于让是事务串行执行的，性能比较低。

22. MySQL 中 undo log 和 redo log 的区别

  1. redo log 日志记录的是数据页的物理变化，服务宕机可用来同步数据，该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，我们可以根据这个日志文件来对数据进行恢复。
  2. 而 undo log 不同，它主要记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据，比如我们删除一条数据的时候，就会在 undo log 日志文件中新增一条 delete 语句，如果发生回滚就执行逆操作；
  3. redo log 保证了事务的持久性，undo log 保证了事务的原子性和一致性

23. 事务中的隔离性是如何保证的呢？(你解释一下 MVCC)

事务的隔离性是由锁和 MVCC 实现的。

MVCC 的意思是多版本并发控制，指维护一个数据的多个版本，使得读写操作没有冲突，它的底层实现主要是分为了三个部分，第一个是隐藏字段，第二个是 undo log 日志，第三个是 readView 读视图

隐藏字段是指：在 mysql 中给每个表都设置了隐藏字段，有一个是 trx_id(事务 id)，记录每一次操作的事务 id，是自增的；另一个字段是 roll_pointer(回滚指针)，指向上一个版本的事务版本记录地址。

undo log 主要的作用是记录回滚日志，存储老版本数据，在内部会形成一个版本链，在多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过 roll_pointer 指针形成一个链表

readView 解决的是一个事务查询选择版本的问题（里面有几个重要的字段，分别是：trx_ids（尚未提交 commit 的事务版本号集合），up_limit_id（下一次要生成的事务 ID 值），low_limit_id（尚未提交版本号的事务 ID 最小值）以及 creator_trx_id（当前的事务版本号）），它在内部定义了一些匹配规则和当前的一些事务 id 判断该访问那个版本的数据，规定了高版本能够看到低版本的事务变更，低版本看不到高版本的事务变更，并且不同的隔离级别快照读是不一样的，所以最终的访问的结果不一样。

如果是读已提交隔离级别，每一次执行快照读时生成 ReadView，如果是可重复读隔离级别仅在事务中第一次执行快照读时生成 ReadView，后续复用

24. 说一下数据库分库分表？

主从复制分担了访问压力，分库分表解决存储压力

当项目业务数据逐渐增多（例如单表的数据量达 1000W 或 20G 以后），优化已解决不了性能问题（主从读写分离、查询索引…），或者是遇到了 IO 瓶颈（磁盘 IO、网络 IO）、CPU 瓶颈（聚合查询、连接数太多），可以考虑使用分库分表

分库分表拆分策略有四种：

  1. 垂直分库：以表为依据，根据业务将不同表拆分到不同库中。在微服务项目中常用，一个服务一个单独的数据库
  2. 垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中。可以将冷热数据进行分离
  3. 水平分库：将一个库的数据拆分到多个库中。解决了单库大数量，高并发的性能瓶颈问题
  4. 水平分表：将一个表的数据拆分到多个表中(可以在同一个库内)。优化单一表数据量过大而产生的性能问题

但是在使用水平分库分表时，会遇到很多问题，比如 l 跨节点关联查询、跨节点分页，主键避重等问题，我们一般会引用分库分表中间件，现在比较常用的是 mycat。

25. 你们项目用过 MySQL 的分库分表吗？

因为我们都是微服务开发，每个微服务对应了一个数据库，是根据业务进行拆分的，这个其实就是垂直拆分。

对于水平分库，这个也是使用过的，我们当时的业务是(xxx)，一开始，我们也是单库，后来这个业务逐渐发展，业务量上来的很迅速，其中(xx)表已经存放了超过 1000 万的数据，我们做了很多优化也不好使，性能依然很慢，所以当时就使用了水平分库。

我们一开始先做了 3 台服务器对应了 3 个数据库，由于库多了，需要分片，我们当时采用的 mycat 来作为数据库的中间件。数据都是按照 id（自增）取模的方式来存取的。

当然一开始的时候，那些旧数据，我们做了一些清洗的工作，我们也是按照 id 取模规则分别存储到了各个数据库中，好处就是可以让各个数据库分摊存储和读取的压力，解决了我们当时性能的问题。

26. MySQL 和 Oracle 不同之处

一、 对于这两个数据库产品而言不同之处在于

  1. Oracle 是商业软件，需要购买相应的许可证才能使用，成本相对较高。而 MySQL 是开源的，可以免费使用和修改。
  2. 在性能、功能和稳定性方面上，Oracle 在通常优于 MySQL。在处理大量数据和复杂查询时 Oracle 通常表现出更好的性能，通常适用于大型企业级应用、关键业务系统等场景。MySQL 由于其灵活性和易用性，通常适用于中小型应用等场景。

二、 在两个数据库在具体使用方面也有很多不同，我下面举一些例子，比如

  1. 在写法上，MySQL 的函数名不区分大小写，而 Oracle 的函数名必须大写
  2. MySQL 支持主键自增长，指定主键为 auto increment，插入时会自动增长。Oracle 主键一般使用序列；
  3. 在数据类型上，两者有许多相同的数据类型，例如 INT、FLOAT、CHAR 等。然而，它们也各自具有一些特有的数据类型。例如，Oracle 有 NUMBER(p,s)，MySQL 有 ENUM 和 SET。
  4. 在进行分页查询上，在 MySQL 中用 OFFSET，LIMIT 用于限制结果集的大小，而在 Oracle 中是 ROWNUM 用于限制结果集的大小，而且只能使用小于，不能使用大于。
  5. MySQL 字符串可以使用双引号包起来，而 Oracle 只可以单引号；
  6. 在日期格式上，Oracle 使用 DD-MON-RR 格式，而 MySQL 使用 YYYY-MM-DD 格式。
  7. 在 SQL 语句扩展和灵活性方面，MySQL 对 SQL 语句有很多非常实用而方便的扩展，例如 insert 可以一次插入多行数据，select 1+1 可以不加 from 哪个表。Oracle 在这方面都是不支持的。
  8. 两者所支持的函数也不完全相同。Oracle 提供了许多内置函数，如 NVL、DECODE 等，而 MySQL 也有自己的一套内置函数，如 IFNULL、SUBSTRING 等。
  9. 在事务方面，MySQL 默认可重复读的隔离级别，而 Oracle 默认是读已提交的隔离级别。事务 MySQL 默认是自动提交，而 Oracle 默认不自动提交，需要用户手动提交。此外，MySQL 在 innodb 存储引擎的行级锁的情况下才可支持事务，而 Oracle 则完全支持事务。

27. MySQL 悲观锁和乐观锁的怎么实现？

悲观锁：

select...for update 是 MySQL 提供的实现悲观锁的方式。例如：select price from item where id=100 for update 此时在 items 表中，id 为 100 的那条数据就被我们锁定了，其它的要执行 select price from items where id=100 for update 的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改。MySQL 有个问题是 select...for update 语句执行中所有扫描过的行都会被锁上，因此在 MySQL 中用悲观锁务必须确定走了索引，而不是全表扫描，否则将会将整个数据表锁住。

乐观锁：

乐观锁相对悲观锁而言，它认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回错误信息，让用户决定如何去做。

利用数据版本号（version）机制是乐观锁最常用的一种实现方式。一般通过为数据库表增加一个数字类型的 “version” 字段，当读取数据时，将 version 字段的值一同读出，数据每更新一次，对此 version 值 +1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的 version 值进行比对，如果数据库表当前版本号与第一次取出来的 version 值相等，则予以更新，否则认为是过期数据，返回更新失败。

# 1: 查询出商品信息
select (quantity,version) from items where id=100; 

# 2: 根据商品信息生成订单
insert into orders(id,item_id) values(null,100); 

# 3: 修改商品的库存
update items set quantity=quantity-1,version=version+1 where id=100 and version=#{version}
```MySQL 一些面试题
posted @ 2023-11-12 09:22  YssyanguniuanU  阅读(3)  评论(0)  编辑  收藏  举报
Live2D 看板娘 / Demo