数据库MYSQL面试篇（1）

MYSQL——优化

一，如何定位慢查询

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

1. 使用慢查询日志: MySQL 提供了慢查询日志功能，可以记录执行时间超过指定阈值的查询语句。你可以通过修改 my.cnf（或 my.ini）配置文件来启用慢查询日志，并设置慢查询阈值。然后，查看慢查询日志文件，找出执行时间较长的查询语句。

2. 使用 Performance Schema: MySQL 的 Performance Schema 提供了丰富的性能监控数据，包括慢查询、锁等待、IO 等信息。你可以查询 Performance Schema 中的相关表来获取慢查询的信息，例如 events_statements_summary_by_digest 表可以提供按语句摘要统计的信息。

3. 使用 EXPLAIN: 对于特定的查询语句，你可以使用 EXPLAIN 命令来分析查询的执行计划，以确定是否存在潜在的性能问题。EXPLAIN 命令可以展示 MySQL 查询优化器对查询的执行计划进行的估算，帮助你发现可能存在的性能瓶颈。

4. 使用性能分析工具: 除了 MySQL 自带的工具之外，还有许多第三方的性能分析工具可以帮助你定位慢查询，例如 Percona Toolkit、pt-query-digest 等。这些工具可以对慢查询日志进行分析，并生成相应的报告，帮助你更直观地了解哪些查询语句存在性能问题。

2.情景再现

面试官:MySQL中，如何定位慢查询?
候选人:
嗯~，我们当时做压测的时候有的接口非常的慢，接口的响应时间超过了2秒以上，因为我们当时的系统部署了运维的监控系统Skywalking，在展示的报表中可以看到是哪一个接口比较慢，并且可以分析这个接口哪部分比较慢，这里可以看到SQL的具体的执行时间，所以可以定位是哪个sql出了问题
如果，项目中没有这种运维的监控系统，其实在MYSQL中也提供了慢日志查询的功能，可以在MVSQL的系统配置文件中开启这个慢日志的功能，并且也可以设置SQL执行超过多少时间来记录到一个日志文件中，我记得上一个项目配置的是2秒，只要SQL执行的时间超过了2秒就会记录到日志文件中，我们就可以在日志文件找到执行比较慢的SQL了。

二，sql语句执行的很慢

1.解决方案

当 SQL 语句执行较慢时，可以通过以下步骤来进行分析和优化：

1. 使用数据库管理工具查看执行计划：大多数数据库管理工具（如 MySQL Workbench、SQL Server Management Studio）可以生成 SQL 查询的执行计划。执行计划显示了数据库是如何执行查询的，包括使用的索引、连接方式等。通过查看执行计划，可以发现可能存在的性能瓶颈。

2. 检查索引：确保查询中涉及的列上有合适的索引。缺少索引或者索引设计不当会导致查询性能下降。可以通过 EXPLAIN 命令（MySQL）或者查询数据库系统的元数据来查看索引情况。

3. 避免全表扫描：尽量避免使用没有条件限制的查询，以免导致全表扫描。确保 WHERE 子句中使用了索引列，并且条件能有效地过滤数据。

4. 优化 SQL 查询：审查 SQL 查询语句，确保它们是最优的。避免在查询中使用通配符（%），使用合适的 JOIN 类型，避免嵌套查询等。

5. 监控数据库性能：使用数据库性能监控工具（如 MySQL Performance Schema、pg_stat_statements 等）来监控数据库的性能指标，识别哪些查询消耗了较多资源。

6. 分析慢查询日志：启用数据库的慢查询日志功能，记录执行时间较长的查询语句。根据慢查询日志中的信息，找出哪些查询需要优化。

7. 优化磁盘 IO：如果数据库服务器的磁盘 IO 较高，可能会影响查询性能。考虑升级硬件、优化磁盘配置等方式来改善磁盘 IO 性能。

2.情景再现

面试官:那这个SQL语句执行很慢,如何分析呢?

候选人:如果一条sq!执行很慢的话，我们通常会使用mysql自动的执行计划explain来去查看这条sql的执行情况，比如在这里面可以通过key和keylen检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况，第二个，可以通过type字段查看sql!是否有进一步的优化空间，是否存在全索引扫描或全盘扫描，第三个可以通过extra建议来判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复。

三，索引

1.概念

1.索引是帮助MYSQL高效获取数据的数据结构，在数据之外，数据库系统还维护着满足特定查找算法的数据结构（MYSQL默认使用B+树），这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法的功能，这种数据结构就是索引。

2.B+树与红黑树

B+树和红黑树是两种常见的数据结构，它们在实际应用中有着不同的特点和适用场景，下面是它们之间的一些主要区别：

1. 结构：

   • B+树是一种多路搜索树，内部节点不存储数据，只存储键值信息用于索引，所有数据都存储在叶子节点上，叶子节点之间使用指针连接形成有序链表。
   • 红黑树是一种自平衡二叉搜索树，每个节点包含键值以及颜色信息，通过颜色约束和旋转操作来保持树的平衡。

2. 查询效率：

   • B+树适用于范围查询，因为相邻的数据在叶子节点上是有序的，可以通过叶子节点之间的指针进行高效的范围查找。
   • 红黑树适用于单次查询或插入删除操作，其查询、插入和删除的时间复杂度都是 O(log n)，其中 n 是节点数。

3. 存储方式：

   • B+树对于数据库索引等应用更为常见，因为其叶子节点形成有序链表，适合范围查询和顺序访问。
   • 红黑树常用于内存数据结构的实现，如 C++ STL 中的 std::map 和 std::set，以及操作系统中的进程调度等。

4. 插入删除效率：

   • B+树的插入和删除操作可能需要进行节点分裂和合并，相对而言比红黑树更复杂和耗时。
   • 红黑树由于是平衡二叉搜索树，插入删除操作会通过旋转等操作来维持树的平衡，效率较高。

3.另外还有一种叫B树

B+树的优势：

1.磁盘读写代价B+树更低

2.查询效率稳定

3.便于扫描数据库和区间查询

2.情景再现

四，聚簇索引与非聚簇索引

1.概念

聚簇索引（Clustered Index）和非聚簇索引（Non-clustered Index）是数据库中两种不同类型的索引结构，它们在索引的组织方式和存储方式上有所不同：

1. 聚簇索引（Clustered Index）：

   • 聚簇索引是一种索引结构，它决定了表中数据的物理排序方式。
   • 在聚簇索引下，表的数据行按照索引的顺序存储在磁盘上。因此，每张表只能有一个聚簇索引。
   • 聚簇索引可以提高范围查询的性能，因为相关数据在物理上相邻存储，减少了磁盘 I/O 次数。
   • 一般情况下，主键约束会自动创建一个聚簇索引，如果没有显式指定主键，则数据库系统会选择一个唯一索引来作为聚簇索引。

2. 非聚簇索引（Non-clustered Index）：

   • 非聚簇索引是另一种索引结构，它在索引树中保存了索引字段的值，以及指向实际数据行的指针（或者叫做簇化索引键）。
   • 在非聚簇索引下，索引树的叶子节点并不存储实际的数据行，而是存储指向对应数据行的指针。
   • 表可以有多个非聚簇索引，用于加速特定的查询操作，如 WHERE 子句、JOIN 操作等。
   • 非聚簇索引的优点是不会影响表的物理存储顺序，插入、更新和删除数据时的开销相对较小。

2.情景再现

五，覆盖索引

1.概念

覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经能够全部找到。

2.情景再现

面试官：知道什么叫覆盖索引嘛?

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

面试官:MYSQL超大分页怎么处理?

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

六，索引的创建原则

面试官:索引创建原则有哪些?

候选人:嗯，这个情况有很多，不过都有一个大前提，就是表中的数据要超过10万以上，我们才会创建索引，并且添加索引的字段是查询比较频繁的字段，一般也是像作为查询条件，排序字段或分组的字段这些。

还有就是，我们通常创建索引的时候都是使用复合索引来创建，一条sql的返回值，尽量使用覆盖索引，如果字段的区分度不高的话，我们也会把它放在组合索引后面的字段。
如果某一个字段的内容较长，我们会考虑使用前缀索引来使用，当然并不是所有的字段都要添加索引，这个索引的数量也要控制，因为添加索引也会导致新增改的速度变慢。

七，索引失效

面试官:什么情况下索引会失效?

候选人:嗯，这个情况比较多，我说一些自己的经验，以前遇到过的
比如，索引在使用的时候没有遵循最左匹配法则，第二个是，模糊查询，如果%号在前面也会导致索引失效。如果在添加索引的字段上进行了运算操作或者类型转换也都会导致索引失效。


之前还遇到过一个就是，如果使用了复合索引，中间使用了范围查询，右边的条件索引也会失效，所以，通常情况下，想要判断出这条sq!是否有索引失效的情况，可以使用explain执行计划来分析。

面试官：什么是最左匹配原则？

候选人：

1. 查询条件必须从索引的最左边开始匹配： 如果查询条件中的列不是索引的最左边列，那么该索引将无法被使用。例如，对于一个复合索引 (A, B, C)，如果查询条件只涉及到列 B 或列 C，而没有涉及到列 A，那么该索引将无法被利用。

2. 查询条件可以包含索引的连续前缀： 当查询条件从索引的最左边开始，并且连续涵盖索引的前缀列时，索引将被有效利用。例如，对于复合索引 (A, B, C)，如果查询条件涉及到列 A 和列 B，那么该索引可以被利用。

3. 索引的后续列可作为回表（Lookup）操作的依据： 当索引的最左前缀已经被匹配，但查询条件中涉及到了索引的后续列时，数据库系统可能需要进行回表操作来获取完整的数据行。回表操作意味着数据库需要根据索引中的指针（或簇化索引键）去主表中查找对应的数据行，这可能引入额外的开销。

八，sql优化总结

面试官:sql的优化的经验

候选人:嗯，这个在项目还是挺常见的，当然如果直说sq!优化的话，我们会从这几方面考虑，比如建表的时候、使用索引、sql语句的编写、主从复制，读写分离，还有一个是如果量比较大的话，可以考虑分库分表

面试官:创建表的时候，你们是如何优化的呢?

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

面试官:那在使用索引的时候，是如何优化呢?

面试官:你平时对sql语句做了哪些优化呢?
候选人:嗯，这个也有很多，比如SELECT语句务必指明字段名称，不要直接使用select*，还有就是要注意SQL语句避免造成索引失效的写法;如果是聚合查询，尽量用unionall代替union，union会多一次过滤，效率比较低;如果是表关联的话，尽量使用immerioin，不要使用用leftjoinrightjoin，如必须使用 一定要以小表为驱动.

1.

2.