MySQL 面试题

---

网页连接收藏

• Java面试：MySQL面试题汇总

MySQL 查询语句的内部执行过程

1. 首先，MySQL 解析查询语句，并将其分解成一个或多个子任务。

2. 然后，MySQL 将子任务交给它的优化器来优化查询计划。

3. 优化器根据数据库中的表结构和索引信息，选择最佳的执行方法来实现目标。

4. 最后，MySQL 执行者将优化器生成的执行计划进行执行，并将最终的结果集返回给用户。

MySQL 查询缓存有什么优缺点？

• MySQL 查询缓存的优点：

1. 减少数据库服务器的压力，提高数据库性能。因为缓存中的结果集不需要重新执行 SQL 语句即可得到，所以大大减轻了数据库服务器的压力。

2. 由于多次使用相同 SQL 语句时，MySQL 会将其结果集保存在缓存中，因此不用重复计算，这样也能够显著地减少 SQL 语句执行时间。

3. 查询结果集会被共享，而不是仅对特定客户端而言。如果一个客户端执行了一条 SQL 语句并将其结果集保存在 MySQL 查询缓存中，那么其他客户端也可以使用这个相同的 SQL 结果集。

• MySQL 查询缓存的缺点：

1. 如果数据底层表内容发生了变化（如 INSERT、UPDATE、DELETE 等 DML 语句对表内容进行修改时，那么 MySQL 查询缓存中对应的内容也会随之失效。这意味着即使是已有的相同 SQL 语句也会重新执行并把最新的数据写入到 MySQL 查询缓存中。因此 MySQL 查询缓存不适合于快速发生数据内容变化的情形。

MySQL 的常用引擎

• MyISAM、InnoDB（MySQL 5.5.5）、Memory、CSV、Archive、Federated。

常用的存储引擎 InnoDB 和 MyISAM 有什么区别？

• InnoDB：支持事务，具有行级锁定，外键等特性；MyISAM：不支持事务，具有表级锁定。

• InnoDB 提供更好的并发性和安全性；MyISAM 提供更好的数据库压缩和速度优化。

• InnoDB 在处理大量数据时能够保证一致性和正确性；MyISAM 在处理大量数据时可能会降低速度。

什么叫回表查询？

• 普通索引查询到主键索引后，回到主键索引树搜索的过程。

如果把一个 InnoDB 表的主键删掉，是不是就没有主键，就没办法进行回表查询了？

• 不是，主键删掉后，InnoDB 会生成长度为 6 字节的 rowid 作为主键。

一张自增表中有三条数据，删除两条数据之后重启数据库，再新增一条数据，此时这条数据的 ID 是几？

• 如果表的引擎是 MyISAM，那么 ID=4，如果是 InnoDB 那么 ID=2（MySQL 8 之前的版本）

什么是独立表空间和共享表空间？它们的区别是什么？

• 共享表空间指的是数据库的所有表数据，索引文件全部放在一个文件中

• 独立表空间：每一个表都将会生成以独立的文件方式来进行存储

• 区别在于如果把表放在共享表空间，即使表删除了空间也不会删除，因此表依然很大，而独立表空间如果删除表就会清除空间

清空表的所有数据性能最好的语句是？

A：delete from t
B：delete t
C：drop table t
D：truncate table t

答：D

唯一索引和普通索引哪个性能更好？

• 对于查询来说两者都是从索引树进行查询，性能几乎没有任何区别；

• 对于更新操作来说，因为唯一索引需要先将数据读取到内存，然后需要判断是否有冲突，因此比唯一索引要多了判断操作，从而性能就比普通索引性能要低。

left join 和 right join 的区别是什么？

• left join（左联结），返回左表全部记录和右表联结字段相等的记录；

• right join（右联结），返回右表全部记录和左表联结字段相等的记录。

什么是最左匹配原则？它的生效原则有哪些？

• 最左匹配原则也叫最左前缀原则，是 MySQL 中的一个重要原则，指的是索引以最左边为起点任何连续的索引都能匹配上，当遇到范围查询（>、<、between、like）就会停止匹配。 生效原则来看以下示例，比如表中有一个联合索引字段 index(a,b,c)：

where a=1 只使用了索引 a；
where a=1 and b=2 只使用了索引 a,b；
where a=1 and b=2 and c=3 使用a,b,c；
where b=1 or where c=1 不使用索引；
where a=1 and c=3 只使用了索引 a；
where a=3 and b like ‘xx%’ and c=3 只使用了索引 a,b。

以下 or 查询有什么问题吗？该如何优化？

select * from t where num=10 or num=20;

答：如果使用 or 查询会使 MySQL 放弃索引而全表扫描，可以改为：

select * from t where num=10
union
select * from t where num=20;

事务是什么，它有什么特性？

• 事务是一种数据库处理机制，它用来保证数据的一致性和完整性。

• 事务具有以下几个特性：原子性（A）、一致性（C）、隔离性（I）、持久性（D）。

1. 原子性表示事务是不可分割的最小工作单位，要么全部成功，要么全部失败；

2. 一致性表示事务必须使所有数据从一个一致状态变更到另外一个一致状态；

3. 隔离性表示在并发的情况下多个事务之间不能相互影响；

4. 持久性表示即使在发生意外的情况下也能保证所做的更改永远不会丢失。

MySQL 中有几种事务隔离级别？分别是什么？

• read uncommited，未提交读，一个事务可以看到其他事务未提交的修改；
• read committed，读已提交，也叫不可重复读，两次读取到的数据不一致；
• repetable read，可重复读，事务可以多次读取同一数据而得到相同的结果；
• serializable，串行化，所有的事务都必须一个接一个地执行，读写数据都会锁住整张表，并发性能极低，开发中很少用到。

MySQL 默认使用 repetable read 的事务隔离级别。

InnoDB 为什么要使用 B+ 树，而不是 B 树、Hash、红黑树或二叉树？

1. 可以高效地存储数据，B+ 树可以将多个数据存储在一个节点中，而不是其他树结构中的一个节点。这样可以减少树的高度，使得搜索和遍历时更快。
2. 可以高效地进行范围查询，因为 B+ 树中所有关键字都存储在叶子节点中，所以我们可以快速找到大于或小于特定值的所有关键字。
3. B+ 树还有一些内部性能优化来加快对数据的读取速度。例如，B+ 树可以将相邻的叶子节点连接到一起来加快遍历速度

MySQL 是如何处理死锁？

• 通过 innodb_lock_wait_timeout 来设置超时时间，一直等待直到超时；
• 发起死锁检测，发现死锁之后，主动回滚死锁中的某一个事务，让其他事务继续执行。

什么是全局锁？它的应用场景有哪些？

• 全局锁就是对整个数据库实例加锁，它的典型使用场景就是做全量逻辑备份，这个时候整个库会处于完全的只读状态。

使用全局锁会导致什么问题？

• 使用全局锁会使整个系统不能执行更新操作，所有的更新业务会出于等待状态；如果你是在从库进行备份，则会导致主从同步严重延迟。

InnoDB 存储引擎有几种锁算法？

• 行级锁和表级锁

• 行级锁是InnoDB存储引擎的最常用的锁定机制，它能够将冲突访问控制在单独的记录上。这样可以有效地避免因冲突而导致的性能问题。

• 表级锁则更加彻底，它会锁住整个表，使得其他事务无法对该表进行任何操作。

InnoDB 如何实现行锁？

• 通过索引条件检索数据，InnoDB 才使用行级
• 使用 for update 来实现行锁，具体脚本如下：

select * from t where id=1 for update

其中 id 字段必须有索引。

MySQL 中的重要日志分为哪几个？

1. Error log：错误日志，记录服务器发生的所有错误。
2. Slow query log：慢查询日志，记录执行时超过一定值的查询。
3. General log: 普通日志，记录数据库服务器的活动情况。
4. Binary log: 二进制日志，记录数据库中所有变化的信息。
5. Relay log: 转发日志，记录复制环境中master-slave之间传递的数据

如何定位慢查询？

• 使用 MySQL 中的 explain 分析执行语句，比如： explain select * from t where id=5;

MySQL 中常见的读写分离方案有哪些？

• 使用 MySQL 官方提供的数据库代理产品 MySql ProxySQL 搭建自动分配的数据库读写分离环境；

• 在程序层面配置多数据源使用代码实现读写分离。

怎样保证主备数据库无延迟？

1. 检查网络带宽：可以检查两个数据库之间的带宽是否足够，如果不够则需要增加带宽。

2. 数据库性能：可以优化数据库的相关性能参数，以便尽可能减少无用的IO开销。

表的优化策略有哪些？

1. 采用合理的数据类型：尽量使用最小的字节来表示数据，从而减少存储空间的占用。

2. 合理创建索引：根据查询需要选择合适的索引方式，这将有助于快速定位记录。

3. 合理规划表与表之间的关系：正确规划数据库中表与表之间的关系，可以减少数据冗余。

4. 优化SQL语句：使用合理、高效的SQL语句，避免不必要的运行时间浪费。

5. 精确定义字段长度：如字符串字段可以根据实际情况来定义最大长度来减少存储量。

数据库分片方案有哪些？

1. 水平分片：基于某个表的某一个字段来进行分片，将数据库中同一类型的记录存储在不同的数据库实例中；

2. 垂直分片：将一张表拆分成多张表，根据具体的业务需要将不同的字段存储在不同的数据库实例中；

3. 分区分片：将数据库表根据时间或者其他规则划分成不同的区间，然后将这些区间映射到不同的数据库实例上。

查询语句的优化方案有哪些？

1. 使用索引：通过在表中创建索引来改善SQL查询的性能，特别是在执行复杂的查询时。

2. 合并查询：尽量减少查询的数量，将多个查询合并成一个查询。

3. 使用内联子查询：使用内联子查询可以减少代码量并改善性能。

4. 限制检索记录数：限制检索的记录数可以减少SQL的运行时间。

5. 简化条件：尽量减少where子句中的条件个数，避免复杂耗时的运算。

6. 排序优化：选择最佳的字段进行升序或降序优化。

MySQL 毫无规律的异常重启，可能产生的原因是什么？该如何解决？

• 可能是积累的长连接导致内存占用太多，被系统强行杀掉导致的异常重启

• 因为在 MySQL 中长连接在执行过程中使用的临时内存对象，只有在连接断开的时候才会释放，这就会导致内存不断飙升

• 定期断开空闲的长连接；

• 如果是用的是 MySQL 5.7 以上的版本，可以定期执行 mysql_reset_connection 重新初始化连接资源