02-MySQL 面试题-mk

1.如何定位慢查询？

什么是慢查询？
页面加载过慢、接口压测响应时间过长(超过1s)

慢查询出现的情况有哪些？

• 聚合查询
• 多表查询
• 表数据量过大查询
• 深度分页查询

如何定位慢查询？
方案一：开源工具

• 调试工具：Arthas
• 运维工具：Prometheus、Skywalking

Arthas：可以使用命令的方式来监控已经上线的项目，来跟踪比较慢的方法，查看方法执行时间，最终可以确定哪里出现问题
Skywalking：可以查看接口的响应情况（slow endpoints-访问越慢的接口拍在最前面），可以通过“追踪”功能来详细的查看接口的响应情况，可以查看在代码方法中和SQL查询中的耗时


方案二：MySQL自带慢查询日志
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志，如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

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

配置完毕之后，重启MySQL服务器后进行测试，查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log
如何定位慢查询？

1. 介绍一下当时产生问题的场景（我们当时的一个接口测试的时候非常的慢，压测的结果大概5秒钟）
2. 我们系统中当时采用了运维工具（ Skywalking ），可以监测出哪个接口，最终因为是sql的问题
3. 在mysql中开启了慢日志查询，我们设置的值就是2秒，一旦sql执行超过2秒就会记录到日志中（调试阶段，生产阶段不会开启）

面试参考回答：

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

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

2.SQL语句执行很慢，如何分析？

• 聚合查询：优化SQL 语句，新增一个临时表解决
• 多表查询：优化SQL 语句的结构
• 表数据量过大查询：添加索引
• 深度分页查询：提到覆盖索引再解决

聚合查询、多表查询、表数据量过大查询都可以通过SQL执行计划来找到执行慢的原因

一个SQL语句执行很慢, 如何分析？

可以采用EXPLAIN 或者 DESC命令获取 MySQL执行 SELECT 语句的信息

语法：

-直接在select语句之前加上关键字 explain/desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

• possible_keys 当前sql可能会使用到的索引
• key 当前sql实际命中的索引
• key_len 索引占用的大小（通过key 和key_len 两个查看是否会命中索引和索引是否失效）
• Extra 额外的优化建议 （也可以获取其它信息，如某条sql 使用索引的过程中，是否进行回表。如果Extra 返回的是 “Using index condition”表示当前索引的使用有优化的空间）
  
• type 这条sql的连接的类型，性能由好到差为NULL、system、const、eq_ref、ref、range、 index、all （左边性能越好，右边性能越差）
  • NULL：sql语句执行的时候，没有使用到表，平时开发中比较少见（无需关注）
  • system：查询系统中的表（mysql 中内置的表，很少用到）
  • const：根据主键查询
  • eq_ref：主键索引查询或唯一索引查询（查询出来是一条）
  • ref：索引查询（查询出来是多条）
  • range：范围查询
  • index：索引树扫描（全索引查询，才会遍历整个索引树）
  • all：全盘扫描（不走索引，并且是全盘扫描）

总结：在实际开发中最低限制在range，如果某个sql 连接类型是index和all 那这条sql需要优化

那这个SQL语句执行很慢, 如何分析呢？
可以采用MySQL自带的分析工具 EXPLAIN

• 通过key和key_len检查是否命中了索引（索引本身存在是否有失效的情况）
• 通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描（index）或全盘扫描（all）
• 通过extra建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

面试参考回答：

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

3.索引概念以及索引底层的数据结构

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

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

查找age=45的数据，左边会遍历整张表查找数据。右边是一个二叉树（二叉搜索树，左边小，右边大，查找age=45数据，比对次数少，效率提高，减少IO操作）
索引的底层数据结构了解过嘛 ?

MySQL默认使用的索引底层数据结构是B+树

数据结构对比：
二叉树

二叉搜索树：时间复杂度O(logn)

最坏的二叉树：时间复杂度O(n)，二叉树不太稳定，mysql底层没有使用二叉树

红黑树：节点可以保持平衡，时间复杂度相对稳定O(logn)，假如一张表的数据量很大（1000万），红黑树也是一种二叉树，每个节点只能有两个子节点，如果把1000万的数据存入红黑树中，红黑树变得特别的高，如果要查找数据的话，需要找很多的层级才能找到想要的数据，所以红黑树的效率也不高

B树

B-Tree，B树是一种多叉路平衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。
以一颗最大度数（max-degree）为5(5阶，每个节点最多有5个子节点)的b-tree为例，那这个B树每个节点最多存储4个key（根节点上，20、30、62、89），和二叉树特点一样，左边小右边大，每个key都有指针（灰色部分），指向子节点的数据，每个key都存储了数据（绿色部分），相对二叉树，b树是一个矮胖树，由于他的分支比较多，查找层级较短，效率比较高。

B+树

B+Tree是在BTree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎（MySQL默认）就是用B+Tree实现其索引结构。
非叶子节点：只存储指针，不存储数据，起导航作用，导航找到下面叶子节点
叶子节点：存储数据

B树与B+树对比:

• 磁盘读写代价B+树更低
• 查询效率B+树更加稳定
• B+树便于扫库和区间查询

为什么磁盘读写代价B+树更低？

非叶子节点只存储指针，不存储数据，相对来说存储压力较小。例如要查找12这条数据：

如果是b树，首先会找到根节点，比如找到38，然后把38的数据给查询出来，其中12要比38小，所以从左边找到16和29两个节点，进行对比，最终才能定位到12这个节点，这些节点是包含数据的，也会把38、16、29的数据给加载出来，现在我们只要12这个数据，它会额外加载其它数据。

如果是b+树，会在叶子节点存储数据，非叶子节点存储的是指针，这些指针为了方便查找叶子节点的数据，相当于是个导航，所以它的效率高很多。 整体来说b+树的磁盘读写更低一些。

为什么查询效率B+树更加稳定？

因为b+树所有的数据都存储在叶子节点上，在查找数据时都要从根节点一个一个往下对比，最终在叶子节点获取数据，查找路径是差不多的，效率比较稳定

B+树便于扫库和区间查询？

叶子节点之间使用双向指针进行连接，范围查询的时候更加方便，例如要查询6-34之间的数据，先从根节点上对比一次，从左边找到16，再从左边找到6，由于节点之间有双向指针，所有一次性把数据都能拿到，16、18、29、34不需要从根节点再找一次。

了解过索引吗？&#x