【年后找工作】黑马程序员2023Java八股文整理 -- MySQL篇

1、面试题大概方向

2、MySQL中，如何定位慢查询?

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

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

---

在 MySQL 中，可以通过以下几种方式来定位慢查询：

1. 使用慢查询日志：MySQL 提供了慢查询日志功能，可以记录执行时间超过设定阈值的查询语句。通过配置和分析慢查询日志，可以找出执行时间较长的查询语句。

2. 使用性能分析工具：例如 MySQL 自带的 Performance Schema 或第三方的性能分析工具，如 pt-query-digest、Percona Toolkit 等，这些工具可以帮助分析数据库的性能瓶颈，并找出执行时间较长的查询语句。

3. EXPLAIN 分析：使用 “EXPLAIN” 关键字可以获取 SQL 查询语句的执行计划，从而分析查询语句的性能瓶颈，优化查询语句和索引。

4. 监控工具：使用监控工具对数据库进行实时监控，通过监控工具可以查看数据库的实时性能指标，如查询响应时间、锁等待情况，从而找出慢查询。

5. 使用慢查询分析工具：一些数据库管理工具或者监控工具提供了慢查询分析功能，可以帮助用户快速定位慢查询并提供优化建议。

通过以上方法，可以有效地定位慢查询，并针对性地进行优化，提高数据库的性能。

3、那这个SQL语句执行很慢, 如何分析呢？

黑马回答：
如果一条sql执行很慢的话，我们通常会使用mysql自动的执行计划
explain来去查看这条sql的执行情况；

比如在这里面可以通过key和key_len检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况；

第二个，可以通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描；

第三个可以通过extra建议来判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复。

4、MYSQL支持的存储引擎有哪些, 有什么区别 ?

存储引擎是数据库管理系统中用于存储、处理和检索数据的核心组件之一。它定义了如何在磁盘上存储数据、以及如何通过查询和操作来访问这些数据。

5、索引在项目中的使用方式

在项目中，索引是非常重要的数据库技术之一，它可以显著提高数据库的查询性能。以下是索引在项目中的一些常见使用方式：

1. 在频繁查询的列上创建索引：对于经常被用来查询的列，比如主键、外键或经常用于 WHERE 子句的列，可以考虑在这些列上创建索引，以加快查询速度。

2. 避免在列上进行运算或函数操作：在 WHERE 子句中对列进行函数操作或运算会使索引失效，应尽量避免这种情况。

3. 注意多列索引的使用：如果查询涉及多个列的条件，可以考虑创建多列索引，以提高这类查询的性能。

4. 定期维护索引：随着数据的增加和修改，索引的性能可能会下降，因此需要定期进行索引的优化和重建。

5. 利用 Explain 分析查询执行计划：通过 Explain 命令可以查看查询的执行计划，从而优化查询语句和索引的使用。

6. 使用覆盖索引：覆盖索引是指查询结果可以直接从索引中获取，而无需再访问数据表，可以减少查询的 IO 操作，提高查询性能。

7. 避免过度索引：过多的索引可能会增加写操作的开销，并占用额外的存储空间，应根据实际需求选择合适的索引。

总的来说，索引的使用需要根据具体的项目需求和查询模式进行优化，合理的索引设计可以显著提升数据库的性能和响应速度。

---

在项目中，索引的使用方式是非常重要的，可以显著提升数据库查询性能。除了索引外，还有其他一些常见的技术和工具可以用于优化项目性能和提高系统可靠性，例如缓存、分布式锁、消息队列和延迟队列等。以下是这些技术在项目中的使用方式：

1. 缓存：

   • 使用缓存可以减少数据库访问次数，提高系统响应速度。常见的缓存工具包括 Redis、Memcached 等。
   • 在读取频繁但不经常变化的数据时，可以考虑使用缓存来存储这些数据，避免反复查询数据库。
   • 需要注意缓存的命中率和过期策略，以保证数据的实时性和一致性。

2. 分布式锁：

   • 在分布式系统中，为了确保数据的一致性和避免并发冲突，可以使用分布式锁来控制对共享资源的访问。
   • 分布式锁可以基于数据库实现（如乐观锁或悲观锁）、基于缓存实现（如 Redis 分布式锁）等方式。

3. 消息队列、延迟队列：

   • 消息队列可以用于异步处理任务，解耦系统各个模块，提高系统的可伸缩性和稳定性。
   • 延迟队列可以用于处理需要延迟执行的任务，如定时任务、重试任务等。

6、了解过索引吗？（什么是索引），索引的底层数据结构了解过嘛 ?

7、什么是聚簇索引什么是非聚簇索引 ?什么是回表查询？

聚簇索引（Clustered Index）和非聚簇索引（Non-Clustered Index）是数据库中两种常见的索引类型，它们在数据存储和索引结构上有所不同。

1. 聚簇索引：

   • 聚簇索引是一种索引结构，将数据行存储顺序和索引顺序绑定在一起，即索引的叶子节点存储了实际的数据行。
   • 在聚簇索引中，表的数据行按照索引列的顺序进行物理存储，这样相邻的数据行在磁盘上也是相邻的，可以减少磁盘 I/O 操作次数，提高查询性能。
   • 每张表只能有一个聚簇索引，通常是主键索引。

2. 非聚簇索引：

   • 非聚簇索引是另一种索引结构，索引的叶子节点存储了指向实际数据行的指针，而不是实际的数据行。
   • 在非聚簇索引中，表的数据行在磁盘上的物理存储顺序与索引顺序无关，数据行可能是分散存储的。
   • 一个表可以有多个非聚簇索引，通过创建不同的非聚簇索引可以加快特定查询的速度。

总的来说，聚簇索引和非聚簇索引在数据存储方式和索引结构上有所区别。聚簇索引适合范围查询和覆盖查询，可以减少磁盘 I/O 操作；而非聚簇索引适合查找单个记录，可以提高特定查询的效率。

回表查询

回表查询（Lookup）是数据库查询优化中的一个概念，指的是在使用非聚簇索引时，当数据库引擎通过索引定位到数据行后，需要再次回到原始表中查找数据的过程。

具体来说，当执行一条 SQL 查询语句时，如果条件涉及到了非聚簇索引中未包含的列，那么当数据库引擎使用该索引快速定位到符合条件的数据行后，还需要去主表中查找其他列的值，这个过程就称为回表查询。

回表查询会增加额外的 I/O 操作和计算成本，因为数据库引擎需要根据主键或行 ID 再次访问原始数据表才能获取完整的数据行。

为了减少回表查询的次数，可以考虑以下优化方法：

• 覆盖索引（Covering Index）：创建包含所有查询所需字段的索引，可以避免回表查询，提高查询性能。
• 聚簇索引的选择：合理选择聚簇索引可以减少回表查询的发生。
• 适当的索引设计：根据查询需求和模式设计合适的索引，避免不必要的回表操作。

对于频繁进行回表查询的情况，可以通过优化查询语句、调整索引设计等方式来尽可能减少回表查询的次数，提高数据库查询性能。

8、知道什么叫覆盖索引嘛 ?

覆盖索引（Covering Index）是指在数据库中创建一个包含查询所需的所有字段的索引，这样索引就可以完全覆盖到查询的需求，从而避免回表查询，提高查询性能。

通常情况下，数据库查询语句会涉及到多个字段的筛选条件和返回结果，如果针对这些字段创建了合适的索引，但索引并未包含所有查询需要的字段，那么在使用索引定位数据行后，数据库引擎仍然需要进行回表操作，从原始表中获取缺失的字段信息，这会增加额外的 I/O 操作和计算成本。

通过创建覆盖索引，即包含了查询所需的所有字段，可以使得查询的信息都包含在索引中，数据库引擎无需再进行回表操作，直接从索引中获取所有查询所需的数据，从而提高查询效率和性能。

覆盖索引可以带来以下几点优势：

• 减少磁盘 I/O 操作：避免了额外的回表查询，减少了磁盘读取次数。
• 提高查询性能：减少了数据访问的时间，加快了查询速度。
• 减少内存消耗：覆盖索引通常比完整数据行更小，可以减少内存的占用。

在设计数据库索引时，考虑到覆盖索引的使用可以有效提升查询性能，特别是对于频繁使用的查询语句。但需要注意的是，创建过多的索引会增加数据库维护的开销。

9、MYSQL超大分页怎么处理 ?

嗯，超大分页一般都是在数据量比较大时，我们使用了limit分页查
询，并且需要对数据进行排序，这个时候效率就很低；

我们可以采用覆盖索引和子查询来解决：

先分页查询数据的id字段，确定了id之后，再用子查询来过滤，只查询这个id列表中的数据就可以了；

因为查询id的时候，走的覆盖索引，所以效率可以提升很多。

10、索引创建原则有哪些？

1. 针对于数据量较大，且查询比较频繁的表建立索引。
2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6. 控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

11、什么情况下索引会失效 ?

1. 违反最左前缀法则：当创建了联合索引时，如果查询没有按照索引的最左前缀顺序进行，那么索引可能会失效。

2. 范围查询右边的列：在执行范围查询时，如果范围查询的列不是索引的最左列，那么索引可能会失效。

3. 在索引列上进行运算操作：如果在索引列上进行数学运算或函数操作，索引可能会失效。因此，最好不要在索引列上进行运算操作。

4. 字符串不加单引号：在进行字符串类型的查询时，如果漏掉了单引号，会导致类型转换，从而使索引失效。

5. 以%开头的Like模糊查询：当使用LIKE '%keyword’这样以通配符开头的模糊查询时，索引通常会失效，因为无法利用索引快速定位匹配项。

最左前缀法则

最左前缀法则是指在使用联合索引（composite index）的情况下，只有当查询中涉及到索引的最左侧连续字段，索引才会被使用。换句话说，如果一个查询涉及到了联合索引的前两个字段，那么这个索引可以被用到；但如果查询只涉及到了联合索引的第二个或后面的字段，那么这个索引将不会被使用。

举个例子，假设有一个联合索引 (A, B, C)，按照最左前缀法则：

• 当查询条件包含 A 和 B 时，索引可以被使用。
• 当查询条件只包含 B 或者只包含 C 时，索引将不会被使用。

这个规则的存在是因为数据库系统通常是按照索引的顺序进行存储的，所以只有能够利用索引的最左侧连续字段，才能快速定位到数据。

12、谈一谈你对sql的优化的经验

当涉及 SQL 优化时，以下是一些经验和技巧：

1. 合适的索引：确保表上有正确的索引，可以加快查询速度。根据查询需求和频率创建合适的索引，避免创建过多或不必要的索引。

2. 避免全表扫描：尽量避免全表扫描，通过索引或者优化查询条件来减少扫描的数据量。

3. 最左前缀法则：在设计联合索引时，考虑最左前缀法则，确保查询能够充分利用索引。

4. 避免在索引列上进行运算：避免对索引列进行运算操作，会导致索引失效，影响查询性能。

5. 适当使用 JOIN：合理使用 JOIN 操作，避免多次嵌套查询，可以提高查询效率。

6. 避免大数据量的排序和分组：尽量避免对大数据量进行排序和分组操作，可以考虑通过索引优化或者合理拆分查询来减少数据处理量。

7. 定期分析查询计划：通过分析查询计划，可以了解查询的执行情况，发现潜在的性能瓶颈并进行优化。

8. 避免频繁的提交：减少频繁的提交操作，可以减少日志量和锁竞争，提高数据库性能。

9. 缓存查询结果：对于一些查询结果不经常变化的场景，可以考虑缓存查询结果，减少数据库查询压力。

10. 定期维护数据库：定期进行数据库性能优化和维护，包括索引重建、统计信息更新等操作，以保持数据库的高效性能。

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

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

事务（Transaction）是数据库管理系统中的一个重要概念，具有以下四个特性，通常被称为 ACID 特性：

1. 原子性（Atomicity）：事务是一个不可分割的工作单位，要么全部执行成功，要么全部执行失败。如果在事务执行过程中发生错误，会回滚（Rollback）到事务开始前的状态，保持数据的一致性。

2. 一致性（Consistency）：事务在执行前后，数据库的完整性约束没有被破坏。也就是说，事务执行前后，数据库从一个一致性状态转换到另一个一致性状态。

3. 隔离性（Isolation）：多个事务并发执行时，每个事务的操作应当与其他事务相互隔离，互不干扰。数据库管理系统需要保证事务之间的隔离性，以防止并发执行时出现问题。

4. 持久性（Durability）：一旦事务提交成功，对数据库的修改将会永久保存，即使系统发生故障或断电，也能够保证数据的持久性。数据库管理系统会将事务的提交结果持久化到磁盘上。

这些特性保证了事务的可靠性和稳定性，确保数据库在并发操作和故障恢复等情况下仍能保持数据的一致性和完整性。

除了以上 ACID 特性外，还有一个补充的 CAP 理论：

• 一致性（Consistency）：所有节点在同一时间具有相同的数据视图。
• 可用性（Availability）：系统提供一致的、可靠的响应，即使出现部分故障。
• 分区容忍性（Partition Tolerance）：系统能够容忍网络分区导致消息丢失或延迟。

根据 CAP 理论，一个分布式系统最多只能同时满足其中的两项，无法同时满足所有三个特性。

14、并发事务带来哪些问题？怎么解决这些问题呢？MySQL的默认隔离级别是？

并发事务可以带来一些常见的问题，主要包括以下几点：

1. 脏读（Dirty Read）：一个事务读取到了另一个事务未提交的数据，可能导致不一致性。

2. 不可重复读（Non-Repeatable Read）：一个事务在多次读取同一数据时，由于其他事务的修改导致数据内容不一致。

3. 幻读（Phantom Read）：一个事务在多次查询同一范围数据时，由于其他事务的插入或删除导致数据行数不一致。

为了解决并发事务可能带来的问题，数据库系统提供了不同的隔离级别。MySQL 默认的隔离级别是 Repeatable Read（可重复读），其解决方案是通过锁定读取的数据，防止其它事务对该数据进行修改。

除了 Repeatable Read 外，MySQL 还提供了以下几个隔离级别：

1. READ UNCOMMITTED：允许事务读取未提交的数据，可能导致脏读、不可重复读和幻读问题。

2. READ COMMITTED：保证一个事务提交后对其它事务可见，解决了脏读问题，但仍可能出现不可重复读和幻读问题。

3. REPEATABLE READ：保证在同一事务中多次读取数据的结果是一致的，解决了脏读和不可重复读问题，但仍可能出现幻读问题。

4. SERIALIZABLE：最高的隔离级别，通过强制事务串行执行来避免所有并发问题，但会降低并发性能。

选择合适的隔离级别取决于业务需求和性能要求。在实际应用中，需要根据具体情况来权衡隔离级别和性能之间的关系，以保证数据的一致性和并发性能。

如果需要修改 MySQL 的默认隔离级别，可以使用如下 SQL 语句：

SET SESSION TRANSACTION ISOLATION LEVEL <isolation_level>;

其中 <isolation_level> 可以是 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 或 SERIALIZABLE。

15、undo log和redo log的区别

Undo log（回滚日志）和 Redo log（重做日志）是数据库系统中用于恢复和保证事务持久性的重要组件，它们有一些重要的区别：

1. Undo Log（回滚日志）：

   • 用途：用于事务的回滚操作，即将事务修改的数据恢复到事务开始前的状态。
   • 记录内容：记录了事务执行过程中对数据的修改操作，包括修改前的数据内容。
   • 存储位置：通常和数据页一起存储在数据库内部，用于事务回滚时查找和应用修改前的数据。

2. Redo Log（重做日志）：

   • 用途：用于事务的持久化，确保事务提交后对数据的修改不会丢失。
   • 记录内容：记录了事务执行过程中对数据的修改操作，但不包括修改前的数据内容。
   • 存储位置：通常单独存储在磁盘上，用于在数据库发生故障后重新执行事务的修改操作以恢复数据一致性。

关于两者的具体区别可以总结如下：

• 目的不同：Undo log 用于事务回滚，Redo log 用于事务的持久性和数据库恢复。
• 记录内容不同：Undo log 记录修改前的数据内容，Redo log 不记录修改前的数据内容。
• 存储位置不同：Undo log 通常和数据页一起存储在数据库内部，Redo log 单独存储在磁盘上。
  

通过 Undo log 和 Redo log 的配合，数据库系统可以保证事务的原子性、一致性、持久性和隔离性，同时在系统发生故障时能够进行数据恢复和数据库的一致性恢复。这两种日志在数据库系统的运行中发挥着重要的作用。

16、事务中的隔离性是如何保证的呢？以及解释一下MVCC

事务的隔离性是通过数据库管理系统（DBMS）中的并发控制机制来保证的。并发控制的目标是在多个并发执行的事务之间维持数据的一致性和隔离性，避免并发执行导致的数据访问冲突和不一致性问题。

以下是常见的并发控制技术来保证事务隔离性：

1. 锁机制：数据库使用锁来控制对数据的访问，以确保事务的隔离性。当一个事务访问某个数据时，会获取相应的锁，并在事务结束后释放锁。锁可以分为共享锁和排他锁，用于控制读和写操作的并发性。

2. 多版本并发控制（MVCC）：MVCC 是一种基于时间戳的并发控制技术，每个事务在开始时都会被分配一个唯一的时间戳。读操作只能看到在该时间戳之前已提交的数据，写操作会创建新的版本并分配新的时间戳，保证读写操作的隔离性。

3. 快照隔离（Snapshot Isolation）：快照隔离是一种基于多版本并发控制的隔离级别。每个事务在开始时会创建一个读取的快照，该快照保持事务开始时数据库的一致状态，其他并发事务的修改不会影响该快照。

4. 串行化：串行化是最高级别的隔离级别，它通过强制事务串行执行来避免并发冲突。串行化可以确保数据的一致性和隔离性，但会牺牲并发性能。

DBMS 根据事务的隔离级别设置来决定使用何种并发控制技术。常见的隔离级别包括 READ UNCOMMITTED（读取未提交数据）、READ COMMITTED（读取已提交数据）、REPEATABLE READ（可重复读）和 SERIALIZABLE（串行化）。每个隔离级别都有不同程度的并发控制，以提供合适的隔离性和性能平衡。

通过上述并发控制技术，DBMS 能够确保事务之间的隔离性，防止脏读、不可重复读和幻读等问题的发生。这样可以保证事务的独立性和一致性，提升数据库系统的并发性能和可靠性。

MVCC-实现原理

MVCC 的具体实现主要依赖于数据库记录中的隐式字段、undo log 日志和 read view。

1. 隐式字段：在 MVCC 中，每个数据库记录都会包含一个或多个隐式字段，用于存储数据版本号、创建时间戳和删除标记等元信息。这些隐式字段不会直接暴露给应用程序，但在并发控制过程中起到了重要的作用。
   

2. Undo log 日志：当需要执行写入操作时，数据库系统会先将原始数据项写入 undo log 日志中，然后再进行更新操作。这样可以保留原始数据的历史版本，并在事务回滚时使用。
   

3. Read view：为了实现事务的隔离性，MVCC 需要维护一个 read view，用于记录当前事务开始时已提交的数据版本和删除标记等信息。Read view 包含两个部分：一个是 transaction ID，表示当前事务的时间戳；另一个是 active version list，表示当前事务开始时已经提交但未被删除的数据版本列表。

在并发控制过程中，MVCC 会根据当前事务的时间戳和数据库记录的版本号进行比较，来判断该事务是否能够读取或写入数据。如果读取操作的时间戳晚于数据库记录的最新版本创建时间戳，则说明该数据已经被其他事务修改，当前事务无法读取该数据；如果写入操作的时间戳早于数据库记录的最新版本创建时间戳，则说明当前事务需要等待其他事务完成对该数据的修改，再进行写入操作。

在事务提交时，MVCC 会将当前事务的时间戳加入到 active version list 中，表示该事务已经提交并且可以被其他事务读取。而在事务回滚时，MVCC 会使用 undo log 日志将数据恢复到原始状态，并从 active version list 中删除该事务的时间戳。

总之，MVCC 的具体实现依赖于数据库记录中的隐式字段、undo log 日志和 read view，通过比较事务的时间戳和数据版本号来实现并发控制和事务隔离性，从而支持高并发的数据库应用。

17、MySQL主从同步原理

MySQL 主从同步是一种常用的数据库复制技术，用于将一个 MySQL 主服务器上的数据变更实时地复制到一个或多个从服务器上。主从同步的原理如下：

1. 二进制日志（Binary Log）：MySQL 的主服务器会将所有的数据变更操作记录在二进制日志中，包括插入、更新和删除等操作。这些二进制日志文件以及日志文件的位置信息会被存储在主服务器上。

2. 从服务器连接主服务器：从服务器通过配置文件指定主服务器的地址和端口，然后与主服务器建立连接。

3. 从服务器请求备份：从服务器向主服务器发送请求，要求获取主服务器上的二进制日志文件以及日志文件的位置信息。

4. 主服务器响应：主服务器接收到从服务器的请求后，开始将二进制日志文件中的数据变更操作发送给从服务器。

5. 从服务器应用日志：从服务器接收到主服务器发送的二进制日志文件后，将其应用到本地的 MySQL 数据库中，并记录自己已经应用的日志文件的位置信息。

6. 持续同步：主服务器会不断地产生新的二进制日志文件，从服务器会持续地向主服务器请求新的日志文件，并进行应用，实现实时的数据同步。

需要注意的是，主从同步并不是完全实时的，存在一定的延迟时间。延迟时间取决于主服务器上的数据变更操作的频率、网络传输的速度以及从服务器的处理能力等因素。

主从同步在实际应用中具有多种用途，例如数据备份、读写分离和负载均衡等。通过配置合适的主从复制拓扑结构，可以提高数据库系统的可靠性、性能和可扩展性。

18、你们项目用过分库分表吗

分库分表的主要思想是将一个单一的数据库拆分成多个数据库（分库）或将一个大表拆分成多个小表（分表），以此来分散数据存储和查询压力，提高数据库的并发处理能力和扩展性。

拆分策略：

1. 水平拆分（分表）：

   • 按照某个字段（如时间、地域等）将原表拆分成多个子表，每个子表包含部分数据。
   • 适用于数据量很大但单表记录数不是很多的情况，可以减轻单表的查询压力。

2. 垂直拆分（分库）：

   • 将原表按照字段功能进行拆分，将相关性不高的字段存放在不同的表中，每个表只包含相关字段。
   • 可以提高查询效率，降低查询的 IO 成本。
     

---