MySQL数据库面试题：如何定位慢查询？

桃扇骨 2023-10-14 13:57 89阅读 0赞

## MySQL数据库面试题：如何定位慢查询？ ##

![在这里插入图片描述][85191c6b60b44439939f1fdb3f1cac29.gif_pic_center]  
![在这里插入图片描述][0ac0622f68724054bb2a68da22da5c3a.gif_pic_center]

![在这里插入图片描述][8ac5bca70148463c9fb6049cbb1884bc.png_pic_center]

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

面试官：那这个SQL语句执行很慢，如何分析呢？  
候选人：如果一条SQL执行很慢的话，我们通常会使用MySQL自带的执行计划`EXPLAIN`来查看这条SQL的执行情况。通过`EXPLAIN`可以查看到SQL的执行计划，包括索引使用情况、表扫描次数、表连接方式等。具体可以从以下几个方面进行分析：

1.  **索引使用情况**：查看`key`和`key_len`，检查是否命中了索引。如果使用了索引，可以判断索引是否有失效的情况。
2.  **查询类型**：查看`type`字段，判断是否存在全索引扫描或全盘扫描，可以进一步优化SQL。
3.  **回表查询**：查看`extra`字段，判断是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复。  
    ![在这里插入图片描述][2bba4e0951fc4afa9dd62b2c57a1504c.png_pic_center]

面试官：了解过索引吗？（什么是索引）  
候选人：嗯，索引在项目中还是比较常见的，它是帮助MySQL高效获取数据的数据结构，主要用于提高数据检索的效率，降低数据库的IO成本。同时，通过索引列对数据进行排序，降低数据排序的成本，也能降低了CPU的消耗。

面试官：索引的底层数据结构了解过嘛?  
候选人：MySQL的默认存储引擎InnoDB采用的是B+树的数据结构来存储索引。选择B+树的主要原因有三个方面：第一，B+树的阶数更多，路径更短；第二，磁盘读写代价更低，非叶子节点只存储指针，叶子节点存储数据；第三，B+树便于扫库和区间查询，叶子节点是一个双向链表。

面试官：B树和B+树的区别是什么呢？  
候选人：B树和B+树都是多路搜索树，但在实际应用中有一些区别：

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

面试官：什么是聚簇索引，什么是非聚簇索引?  
候选人：好的~，聚簇索引主要是指数据与索引放到一块，B+树的叶子节点保存了整行数据，有且只有一个。一般情况下，主键作为聚簇索引的索引字段。

非聚簇索引是数据与索引分开存储，B+树的叶子节点保存对应的主键，可以有多个。一般我们自己定义的索引都是非聚簇索引。  
![在这里插入图片描述][3eee47c6a6da4af59a6bede14ec80221.png_pic_center]

面试官：知道什么是回表查询嘛?  
候选人：嗯，其实跟刚才介绍的聚簇索引和非聚簇索引是有关系的。回表的意思就是通过二级索引找到对应的主键值，然后再通过主键值找到聚簇索引中所对应的整行数据，这个过程就是回表。

【备注：如果面试官直接问回表，则需要先介绍聚簇索引和非聚簇索引】

面试官：知道什么叫覆盖索引嘛?  
候选人：嗯~，清楚的。覆盖索引是指`SELECT`查询语句使用了索引，在返回的列必须在索引中全部能够找到。如果我们使用`id`查询，它会直接走聚簇索引查询，一次索引扫描，直接返回数据，性能高。

如果按照二级索引查询数据时，返回的列中没有创建索引，有可能会触发回表查询。尽量避免使用`SELECT *`，尽量在返回的列中都包含添加索引的字段。

面试官：MySQL超大分页

怎么处理？  
候选人：嗯，超大分页一般是在数据量比较大时，我们使用了`LIMIT`分页查询，并且需要对数据进行排序。这个时候效率就很低。我们可以采用覆盖索引和子查询来解决。

先分页查询数据的`id`字段，确定了`id`之后，再用子查询来过滤，只查询这个`id`列表中的数据就可以了。因为查询`id`的时候，走的覆盖索引，所以效率可以提升很多。  
![在这里插入图片描述][a335356758a445968aefb08d27b0595d.png_pic_center]

面试官：索引创建原则有哪些？  
候选人：嗯，索引的创建需要谨慎，毕竟添加索引也会导致新增改的速度变慢。通常情况下，我们会从以下几个方面考虑：

1.  **数据量**：表中数据量超过10万以上，考虑创建索引。
2.  **查询频率**：添加索引的字段是查询比较频繁的字段，例如作为查询条件、排序字段或分组的字段。
3.  **复合索引**：使用复合索引来创建，尽量使用覆盖索引，将返回的列都包含在添加索引的字段中。
4.  **类型选择**：字段的类型选择也要结合字段内容来选择合适的类型，例如数值类型和字符串类型。
5.  **索引数量控制**：索引的数量也要控制，避免过多的索引导致查询性能下降。

面试官：什么情况下索引会失效？  
候选人：嗯，索引失效的情况比较多，我说一些自己的经验。常见的失效情况有：

1.  **最左前缀原则**：如果索引的最左前缀没有被用到，索引会失效。
2.  **模糊查询**：如果`%`号在前面，索引会失效。
3.  **运算操作或类型转换**：如果在添加索引的字段上进行了运算操作或者类型转换，索引会失效。
4.  **范围查询**：在复合索引中，如果左边的字段使用了范围查询，右边的条件索引会失效。
5.  **字段类型**：字段类型的选择也会影响索引是否失效。

所以，通常情况下，想要判断这条SQL是否有索引失效的情况，可以使用`EXPLAIN`执行计划来分析。  
![在这里插入图片描述][b1fa2af5b70e4eb1b72d582273c7441c.png_pic_center]

面试官：SQL的优化经验？  
候选人：嗯，这个在项目还是挺常见的，当然如果直说SQL优化的话，我们会从以下几方面考虑：

1.  **建表优化**：在定义字段时结合字段的内容来选择合适的类型，根据实际情况选择数值类型或字符串类型等。
2.  **索引使用**：合理使用索引，选择适当的字段作为索引字段，尽量使用覆盖索引来提高查询性能。
3.  **SQL编写**：优化SQL语句的编写，避免模糊查询的使用，尽量在返回的列中包含添加索引的字段。
4.  **主从复制与读写分离**：考虑主从复制和读写分离来分担数据库的读写压力。
5.  **分库分表**：如果数据量较大，可以考虑分库分表来优化查询性能。

面试官：创建表的时候，你们是如何优化的呢？  
候选人：这个我们主要参考的阿里出的那个开发手册《嵩山版》。例如，在定义字段时需要结合字段的内容来选择合适的类型，如果是数值类型，选择`TINYINT`、`INT`、`BIGINT`等类型，根据实际情况选择。如果是字符串类型，也是结合存储的内容来选择`CHAR`和`VARCHAR`或者`TEXT`类型等。遵循开发手册的规范，可以提高表的查询性能和存储效率。

![在这里插入图片描述][c3842bb699af42dabd624f1dee704a5a.gif_pic_center]

[85191c6b60b44439939f1fdb3f1cac29.gif_pic_center]: https://img-blog.csdnimg.cn/85191c6b60b44439939f1fdb3f1cac29.gif#pic_center
[0ac0622f68724054bb2a68da22da5c3a.gif_pic_center]: https://img-blog.csdnimg.cn/0ac0622f68724054bb2a68da22da5c3a.gif#pic_center
[8ac5bca70148463c9fb6049cbb1884bc.png_pic_center]: https://img-blog.csdnimg.cn/8ac5bca70148463c9fb6049cbb1884bc.png#pic_center
[2bba4e0951fc4afa9dd62b2c57a1504c.png_pic_center]: https://img-blog.csdnimg.cn/2bba4e0951fc4afa9dd62b2c57a1504c.png#pic_center
[3eee47c6a6da4af59a6bede14ec80221.png_pic_center]: https://img-blog.csdnimg.cn/3eee47c6a6da4af59a6bede14ec80221.png#pic_center
[a335356758a445968aefb08d27b0595d.png_pic_center]: https://img-blog.csdnimg.cn/a335356758a445968aefb08d27b0595d.png#pic_center
[b1fa2af5b70e4eb1b72d582273c7441c.png_pic_center]: https://img-blog.csdnimg.cn/b1fa2af5b70e4eb1b72d582273c7441c.png#pic_center
[c3842bb699af42dabd624f1dee704a5a.gif_pic_center]: https://img-blog.csdnimg.cn/c3842bb699af42dabd624f1dee704a5a.gif#pic_center