【面试专题】MySQL篇①

忘是亡心i 2024-02-05 13:22 13阅读 0赞

#### 1.MySQL中，如何定位慢查询? ####

①介绍一下当时产生问题的场景（我们当时的一个接口测试的时候非常的慢，压测的结果大概5秒钟）

②我们系统中当时采用了运维工具（ Skywalking ），可以监测出哪个接口，最终因为是sql的问题

③在mysql中开启了慢日志查询，我们设置的值就是2秒，一旦sql执行超过2秒就会记录到日志中（调试阶段）

**面试回答：**

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

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

**扩展：**

**方案一：开源工具**

调试工具：Arthas

运维工具：Prometheus 、Skywalking

以Skywalking为例：

![3e3b82569572481f9503d685ac5d93fc.png][]

**方案二：MySQL自带慢日志**

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

SHOW VARIABLES LIKE 'slow_query_log';

我的之前配置过，所以显示的是ON（off为关，on为开）:

![c760b0d12f6a452e8e893bbc3e4ed9e3.png][]

SHOW VARIABLES LIKE 'long_query_time';

# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
    long_query_time=2

![09a12e7b7e584133b01c1deadc5eb725.png][]

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log。

![d8cd5cb020294d788855cb69c494328c.png][]

#### 2.那这个SQL语句执行很慢, 如何分析呢？ ####

**面试回答：**

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

①可以通过key和key\_len检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况

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

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

**扩展：**

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

![7c0a9595e68543bdaa1d01acd0e97046.png][]

Explain 执行计划中各个字段的含义:

![d1a9f9a4439a471c965b3afcce064a2c.png][]

![80dacea6403f4323b9bcce3147e2a144.png][]

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

**面试回答：**

索引在项目中还是比较常见的，它是帮助MySQL高效获取数据的数据结构，主要是用来提高数据检索的效率，降低数据库的IO成本，同时通过索引列对数据进行排序，降低数据排序的成本，也能降低了CPU的消耗。

**扩展：**

[【MySQL进阶】索引的结构及分类\_mysql索引数据结构有哪些-CSDN博客][MySQL_mysql_-CSDN]

#### 4.索引的底层数据结构了解过嘛? ####

**面试回答：**

MySQL的默认的存储引擎InnoDB采用的B+树的数据结构来存储索引，选择B+树的主要的原因是：

①阶数更多，路径更短

②磁盘读写代价B+树更低，非叶子节点只存储指针，叶子阶段存储数据

③B+树便于扫库和区间查询，叶子节点是一个双向链表

**扩展：**

[【MySQL进阶】索引的结构及分类\_mysql索引数据结构有哪些-CSDN博客][MySQL_mysql_-CSDN]

#### 5.B树和B+树的区别是什么呢？ ####

**面试回答：**

第一：在B树中，非叶子节点和叶子节点都会存放数据，而B+树的所有的数据都会出现在叶子节点，在查询的时候，B+树查找效率更加稳定

第二：在进行范围查询的时候，B+树效率更高，因为B+树都在叶子节点存储，并且叶子节点是一个双向链表

**扩展：**

[【MySQL进阶】索引的结构及分类\_mysql索引数据结构有哪些-CSDN博客][MySQL_mysql_-CSDN]

#### **6.****InnoDB为什么使用B+树实现索引？** ####

**面试回答：**

**首先看看B+树有哪些特点：**

1.  B+树是一棵**平衡树**，每个叶子节点到根节点的路径长度相同，查找效率较高；
2.  B+树的**所有关键字都在叶子节点上**，因此范围查询时只需要遍历一遍叶子节点即可；
3.  B+树的叶子节点都按照关键字大小**顺序存放**，因此可以快速地支持按照关键字大小进行排序；
4.  B+树的**非叶子节点不存储实际数据**，因此可以存储更多的索引数据；
5.  B+树的非叶子节点使用指针连接子节点，因此可以快速地支持范围查询和倒序查询。
6.  **B+树的叶子节点之间通过双向链表链接**，方便进行范围查询。

**那么，使用B+树实现索引，就有以下几个优点：**

1.  **支持范围查询**，B+树在进行范围查找时，只需要从根节点一直遍历到叶子节点，因为数据都存储在叶子节点上，而且叶子节点之间有指针连接，可以很方便地进行范围查找。
2.  **支持排序**，B+树的叶子节点按照关键字顺序存储，可以快速支持排序操作，提高排序效率；
3.  **存储更多的索引数据**，因为它的非叶子节点只存储索引关键字，不存储实际数据，因此可以存储更多的索引数据；
4.  **在节点分裂和合并时，IO操作少**。B+树的叶子节点的大小是固定的，而且节点的大小一般都会设置为一页的大小，这就使得节点分裂和合并时，IO操作很少，只需读取和写入一页。
5.  **有利于磁盘预读**。由于B+树的节点大小是固定的，因此可以很好地利用磁盘预读特性，一次性读取多个节点到内存中，这样可以减少IO操作次数，提高查询效率。
6.  **有利于缓存**。B+树的非叶子节点只存储指向子节点的指针，而不存储数据，这样可以使得缓存能够容纳更多的索引数据，从而提高缓存的命中率，加快查询速度。

#### 7.什么是聚簇索引什么是非聚簇索引 ? ####

**面试回答：**

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

**聚簇索引**，简单点理解就是将数据与索引放到了一起，找到索引也就找到了数据。也就是说，对于聚簇索引来说，他的非叶子节点上存储的是索引字段的值，而他的叶子节点上存储的是这条记录的整行数据。

**非聚簇索引**，就是将数据与索引分开存储，叶子节点包含索引字段值及指向数据页数据行的逻辑指针。

![1d9bafb7ee6a4fd49c598d2c28ba899f.png][]

*  **对于聚簇索引来说，他的非叶子节点上存储的是索引值，而它的叶子节点上存储的是整行记录。**
 *  **对于非聚簇索引来说，他的非叶子节点上存储的都是索引值，而它的叶子节点上存储的是主键的值。**

**所以，通过非聚簇索引的查询，需要进行一次回表，就是先查到主键ID，在通过ID查询所需字段。**

**扩展：**

![53cec5616d9649b28bc5ddc8dbac909b.png][]

**聚集索引选取规则:**

如果存在主键，主键索引就是聚集索引。

如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。

如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

#### 8.知道什么是回表查询吗? ####

在 InnoDB 里，索引B+ Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引。而索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引。

在存储的数据方面，主键（聚簇）索引的B+树的叶子节点直接就是我们要查询的整行数据了。而非主键（非聚簇）索引的叶子节点是主键的值。

那么，**当我们根据非聚簇索引查询的时候，会先通过非聚簇索引查到主键的值，之后，还需要再通过主键的值再进行一次查询才能得到我们要查询的数据。而这个过程就叫做回表。**

所以，在InnoDB 中，**使用主键查询**的时候，是效率更高的， 因为这个过程不需要回表。另外，依赖**覆盖索引**、**索引下推**等技术，我们也可以通过优化索引结构以及SQL语句减少回表的次数。

#### 9.知道什么叫覆盖索引嘛 ? ####

覆盖索引是指select查询语句使用了索引，在返回的列，必须在索引中全部能够找到，如果我们使用id查询，它会直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。

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

当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。

![4f89479a54444d059ccef3272a3a19c5.png][]

![28bf36cb83534ea19fb6ff1d0aae191a.png][]

![ae012c8a43c1496fbd7ab7ab6e82171f.png][]

![fb4d6fcf5ddf410cbd8d2e2f03b25d84.png][]

#### 10.MySQL超大分页怎么处理 ? ####

超大分页一般都是在数据量比较大时，我们使用了limit分页查询，并且需要对数据进行排序，这个时候效率就很低，我们可以采用覆盖索引和子查询来解决

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

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

**扩展：**

我们一起来看看执行limit分页查询耗时对比：

![05804da224e043389667118e29eb212c.png][]

因为，当在进行分页查询时，如果执行 limit 9000000,10 ，此时需要MySQL排序前9000010 记录，仅仅返回 9000000 - 9000010 的记录，其他记录丢弃，查询排序的代价非常大 。

优化思路: 一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化

![7ac04b78054c432bb6d760b6ee373e96.png][]

[3e3b82569572481f9503d685ac5d93fc.png]: https://img-blog.csdnimg.cn/direct/3e3b82569572481f9503d685ac5d93fc.png
[c760b0d12f6a452e8e893bbc3e4ed9e3.png]: https://img-blog.csdnimg.cn/direct/c760b0d12f6a452e8e893bbc3e4ed9e3.png
[09a12e7b7e584133b01c1deadc5eb725.png]: https://img-blog.csdnimg.cn/direct/09a12e7b7e584133b01c1deadc5eb725.png
[d8cd5cb020294d788855cb69c494328c.png]: https://img-blog.csdnimg.cn/direct/d8cd5cb020294d788855cb69c494328c.png
[7c0a9595e68543bdaa1d01acd0e97046.png]: https://img-blog.csdnimg.cn/direct/7c0a9595e68543bdaa1d01acd0e97046.png
[d1a9f9a4439a471c965b3afcce064a2c.png]: https://img-blog.csdnimg.cn/direct/d1a9f9a4439a471c965b3afcce064a2c.png
[80dacea6403f4323b9bcce3147e2a144.png]: https://img-blog.csdnimg.cn/direct/80dacea6403f4323b9bcce3147e2a144.png
[MySQL_mysql_-CSDN]: https://blog.csdn.net/Javascript_tsj/article/details/128273533
[1d9bafb7ee6a4fd49c598d2c28ba899f.png]: https://img-blog.csdnimg.cn/direct/1d9bafb7ee6a4fd49c598d2c28ba899f.png
[53cec5616d9649b28bc5ddc8dbac909b.png]: https://img-blog.csdnimg.cn/direct/53cec5616d9649b28bc5ddc8dbac909b.png
[4f89479a54444d059ccef3272a3a19c5.png]: https://img-blog.csdnimg.cn/direct/4f89479a54444d059ccef3272a3a19c5.png
[28bf36cb83534ea19fb6ff1d0aae191a.png]: https://img-blog.csdnimg.cn/direct/28bf36cb83534ea19fb6ff1d0aae191a.png
[ae012c8a43c1496fbd7ab7ab6e82171f.png]: https://img-blog.csdnimg.cn/direct/ae012c8a43c1496fbd7ab7ab6e82171f.png
[fb4d6fcf5ddf410cbd8d2e2f03b25d84.png]: https://img-blog.csdnimg.cn/direct/fb4d6fcf5ddf410cbd8d2e2f03b25d84.png
[05804da224e043389667118e29eb212c.png]: https://img-blog.csdnimg.cn/direct/05804da224e043389667118e29eb212c.png
[7ac04b78054c432bb6d760b6ee373e96.png]: https://img-blog.csdnimg.cn/direct/7ac04b78054c432bb6d760b6ee373e96.png