MySQL面试：查询性能的优化方法

拼搏现实的明天。 2022-09-16 12:25 112阅读 0赞

**减少请求的数据量**

*  只返回必要的列：最好不要使用 SELECT \* 语句。
 *  只返回必要的行：使用Limit语句来限制返回的数据
 *  缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别是要查询的数据经常被重复查询时，缓存提升将会是非常明显的。

**减少服务端扫描的行数**

*  最有效的方式是使用索引来覆盖查询

--------------------

# 查询性能 #

查询的生命周期分为：从客户端、到服务端、然后在服务器上进行解析、生成执行计划、执行、并将结果返回给客户端。

查询包含一系列子任务，如果要优化查询，实际上是要优化其子任务，可以：

*  减少子任务执行次数
 *  消除子任务的方式
 *  让子任务运行的更快

通过上面三种方法来提高查询效率。

> 在mysql中，能够使用过下面三种方式的where条件，由好到坏依次为：

*  索引中使用where来过滤不需要的记录，这是在存储引擎中实现的
 *  覆盖索引扫描来返回记录：直接在索引中过滤掉不需要的记录并返回命中结果，这是在mysql服务器实现的
 *  从数据表中返回数据，然后过滤不满足的记录

# 优化数据访问 #

查询性能低下最基本的原因是访问的数据太多。对于低效的查询，下面两个步骤的分析总是很有效：

*  **确认应用程序是否检索大量超过需要的数据**。这意味着访问了太多的行或者列
 *  确认MySQL服务器层是否在分析大量超过需要的数据行。

## 是否向数据库请求了不需要的数据 ##

### 查询不需要的记录 ###

如果我们不需要展示全部记录，最好在查询后加上`limit`

### 多表关联时，返回全部列 ###

例如，我们要查询一个员工的工号，姓名，和部门名称：

select
    	*
    from
    	employees e
    inner join dept_emp ed
    		using (emp_no)
    inner join departments d
    		using(dept_no);

上面的查询会返回三个表的全部列，尽量不要这样查询，我们应该指定查询的列：

select
    	e.emp_no ,e.first_name ,e.last_name ,d.dept_name
    from
    	employees e
    inner join dept_emp ed
    		using (emp_no)
    inner join departments d
    		using(dept_no);

### 总是取出全部列 ###

每次看到SELECT \*时，都要用怀疑的眼光来审视。我们需要根据实际情况来判断是否真的需要查询所有的列，取出全部的列，我们无法完成 索引完全覆盖 这样的优化，还会对服务器资源带来额外消耗。

### 重复查询相同的列 ###

如果存在重复查询，我们可以将这个查询结果缓存

## MySQL是否在扫描额外的数据 ##

在确定查询只返回需要的数据**之后**，接下来应该看看查询为了返回这些结果是否扫描了过多的数据。

对于MySQL，最简单的衡量查询开销的三个指标：

*  响应时间
 *  扫描的行数
 *  返回的行数

这三个指标都会记录到慢日志中，所以 检查慢日志记录 是找出扫描行数过得的查询的好办法。

**查询最重要的是响应时间**

### 响应时间 ###

响应时间=等待时间+排队时间

*  服务时间：指执行某条具体sql查询时所消耗的时间
 *  排队时间：指服务器因为等待某些资源而没有真正执行查询的时间

### 扫描行数和返回的行数 ###

*  分析查询时，查看扫描的行数是非常有帮助的，这在一定程度上说明了该查询找到需要的数据的效率。
 *  理想情况下扫描的行数与返回的行数应该是相同的，但是实际中这种情况并不多，比如关联查询，需要扫描多表的多条记录才能产生一条结果记录

### 扫描行数与访问类型 ###

在EXPLAIN语句中的type反应了访问类型，访问类型有很多，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。这里的类型速度从慢到快，扫描的行数从多到少。

**如果发现查询需要扫描大量的数据而只返回少量的行，可以通过下面方法进行优化：**

*  使用索引覆盖扫描
 *  改变库表结构，比如使用单独的汇总表
 *  重写这个复杂的查询

# 重构查询的方式 #

## 一个复杂查询还是多个简单查询 ##

在传统实现中，总是强调需要数据库完成尽可能多的工作。但是对于MySQL而言，这并不适用，MySQL从设计上让连接和断开都非常轻量级，在返回一个小的查询结果方面很高效。

## 切分查询 ##

我们可以将一个大的查询“分而治之”，将大查询分成小查询，每个小查询**功能相同**，只完成一小部分，每次只返回一小部分查询结果。这样可以减轻服务器负担，避免一次性锁住很多数据，占用过多事务日志等等。

如果是事务性引擎，很多时候小事务可能更高效

## 分解关联查询 ##

很多高性能的应用都会对关联查询进行分解：可以对每一个表进行一次单标查询，然后将结果在应用程序中进行管理。比如：

select
    	e.emp_no ,
    	e.first_name ,
    	e.last_name ,
    	d.dept_name
    from
    	employees e
    inner join dept_emp ed
    		using (emp_no)
    inner join departments d
    		using(dept_no)
    where
    	e.first_name = 'Mary'

可以分解为：

select e.emp_no,e.first_name,e.last_name from employees e where e.first_name = 'Mary'；
    select d.dept_no from dept_emp de where de.emp_no = 10011;
    select d.ept_name from departments d where d.dept_no = 'd009';

分解关联查询的方式重构查询有如下优势：

*  让缓存的效率更高。不管是应用程序缓存还是MySQL查询缓存，对单表查询结果进行缓存，都将提高缓存的利用率
 *  将查询分解后，执行单个查询可以减少锁的竞争
 *  在应用层做关联，可以更容易对数据进行拆分，更容易做到高性能和可扩展
 *  查询本身的效率也可能会有提升（单表查询更容易实现顺序查询，这可能比随机的关联更高效）
 *  可以减少冗余记录的时间。在应用层做关联，意味着对于某条记录应用只需要查询一次；而在关联查询中，可能需要重复的查询某一部分数据
 *  这样做相当于在应用中实现了hash关联

在如下场景中，将关联放在应用程序中将会更加高效：

*  当应用能够方便的缓存单个查询结果时
 *  当可以将数据分布到不同的MySQL服务器上时
 *  当能够用`In()`的方式代替关联查询时
 *  当查询中使用同一个数据表时

# 查询执行的基础 #

如果我们弄清楚MySQL是如何优化和执行查询的，我们就可以理解：很多查询优化工作实际上就是遵循一些原则让优化器能够按照预想的合理的方式运行  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_18_color_FFFFFF_t_70_g_se_x_16]

## MySQL客户端/服务端通信协议 ##

MySQL客户端/服务端通信协议是**半双工**的，这种协议让MySQL通信变得简单，但是也从很多地方限制了MySQL。一个明显的限制就是无法进行**流量控制**，一旦一段开始发送消息，另一端要接收完整的消息之后才能响应它

客户端用**一个**单独的数据包将查询传给服务器。这也是为什么当查询语句很长时，参数`max_allowd_packet`就特别重要了

相反，服务器响应的数据通常比较多，由**多个**数据包组成。客户端必须完整的接收整个返回结果，所以**查询中使用`Limit`限制是很有必要的**

MySQL通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源。所以缓存通常可以减少数据库压力，让查询早点结束、早点释放响应的资源。

## 查询状态 ##

对于一个MySQL连接，或者说一个线程任何时候都有一个状态，该状态表示了MySQL当前正在做什么。我们可以通过`SHOW FULL PROCESSLIST`命令结果中的Command列来查看连接的当前状态：

show  full processlist;

*  Sleep: 线程正在等待客户端发送新请求。
 *  Query：线程正在执行查询或者正在将结果发送给客户端
 *  Locked：在MySQL服务器层，该线程正在等待 表锁
 *  Analyzing and statistics:线程正在搜集存储引擎的统计信息，并生成查询的执行计划
 *  Copying to tmp table \[on disk\]: 线程正在执行查询，并且将其结果集都复制到一个零时表中，这种状态一般要么是在做GROUP BY操作，要么是文件排序操作，或者是UNION操作。
 *  Sorting result:线程正在对结果集进行排序。
 *  Sending data:线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据。

## 查询缓存（Query Cache） ##

> MySQL8.0这个功能以及删除

## 查询优化处理 ##

当MySQL不能通过索引得到排序结果时，就需要自己进行排序，如果数据量小就在内存中进行，如果数据量大就需要使用硬盘，MySQL将这个过程称为“文件排序（sort）”

如果需要排序的数据量小于“排序缓冲区”，MySQL使用内存进行“快速排序”操作。如果内存不够排序，那么MySQL会先将数据分块，对每个独立的块使用“快速排序”进行排序，并向每个块的排序结果放在磁盘上，然后将各个排好序的块进行合并，最后返回排序结果。

MySQL有如下两种排序算法：

*  两次传输排序
    
     *  **读取行指针和需要排序的字段**，对其进行排序，然后在根据排序结果读取所需数据行。
     *  该算法在第二次读取数据时会造成大量的随机I/O，所以两次传输排序的数据传输成本非常高。
     *  但是这个算法在排序时存储尽可能少的数据，使的“排序缓冲区”中能够容纳更多的行数进行排序。
 *  单次传输排序
    
     *  先**读取查询所需的所有列**，然后在根据给定列进行排序，最后直接返回排序结果。
     *  该算法的优缺点与两次传输相反。

当查询需要所有列的总长度不超过`max_length_sort_data`时，MySQL会使用“单次传输排序”，我们可以通过调整该参数来影响MySQL排序算法的选择。

在关联查询的时候如果需要排序：

*  如果ORDER BY子句的所有列都来自关联的第一个表，那么MySQL在关联处理第一个表的时候就会进行文件排序。这种情况下，EXPLAIN 的Extra字段可以看到"Using filesort"。
 *  除去第一种情况的其它情况，MySQL都会先将关联存放在一个临时表中，然后在所有的关联结束后，再进行文件排序。XPLAIN 的Extra字段可以看到"Using temporary;Using filesort"。

如果有LIMIT，LIMIT也会在排序之后应用，所以即使需要返回较少的数据，临时表和需要排序的数据量仍然会非常大

## 查询执行引擎 ##

MySQL的查询执行引擎根据执行计划给出的指令逐步执行，在执行计划逐步执行的过程中，大量的操作需要通过调用存储引擎实现的接口来完成。

## 返回结果给客户端 ##

查询执行的最后一个阶段是将结果返回给客户端。如果查询可以被缓存，那么查询结果也在这个节点被存放在查询缓存中。

MySQL将结构返回给客户端是一个增量、逐步返回的过程。如关联查询，一旦查询处理完最后一个管理表，开始生成第一条结果时，MySQL就可以开始向客户端逐步返回结果集了。这样做有两个好处：

*  服务端无需存储太多结果
 *  客户端可以在第一时间得到返回结果

结果集中的每一个都会以满足MySQL客户端/服务端通信协议的封包发送，再通过TCP协议进行传输。

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_18_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/faafd3c3f692437eb0f53f764e9cfe65.png