[MySQL]--查询性能优化

阳光穿透心脏的1/2处 2022-07-12 15:53 251阅读 0赞

> 对于高性能数据库来说：库表结构优化、索引优化和查询优化需要齐头并进

#  1.为什么查询的速度会慢？ #

查询其实就是一系列的子任务组成，优化查询实际上就是：要么消除一些子任务，要么减少子任务执行的次数。

#  2.慢查询基础：优化数据访问 #

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

1）查询了不需要的数据：  
比如我们通过select 查询出了大量的结果，获取前面的N行之后就关闭结果集，实际上MySQL会查询出所有的结果集，客户端接收部分数据后丢弃剩余的数据，这里就存在查询冗余。所以我们只需要查询前面的n条记录就好，利用 limit 关键字限制。

2）多表关联时返回全部的列  
我们在进行多表查询时，经常会碰到  
mysql>select \* from …….  
这样的查询其实是非常非常影响性能的，应该用具体的字段名来代替通配符 \*

3）总是取出全部的列  
禁止写出 select \* 这样的语句。

## （2）MySQL是否扫描了额外的记录 ##

在确定了查询只返回了需要的数据之后（也就是定制查询的具体字段不要使用通配符 \* ）

接下来关注的应该是返回结果是否扫描了过多的数据。对于MySQL最简单的三个指标如下：  
（1）响应时间

（2）扫描的行数

（3）返回的行数。

响应时间  
响应时间：包括服务时间（真正的查询时间）和排队时间(阻塞等待的时间)。

扫描行数和返回的行数  
分析查询时，查看该查询扫描的行数是非常有帮助的，一定程度上说明该查询的效率高不高。

扫描的行数和访问类型  
MySQL有好几种访问方式可以查找并返回一行结果：全表扫描、索引扫描、范围扫描、唯一索引查询、常数引用等。

这里加索引的作用就出来了，索引可以让MySQL以最高效、扫描行数最少的方式找到记录。

#  3.重构查询的方式 #

目的就是：找到一个更加优的方法获得实际需要的结果。

（1）一个复杂查询还是多个简单查询  
我们在写SQL的时候经常需要考虑的一个问题就是：是否需要将一个复杂的查询分成多个简单的查询？

对于MySQL来说，连接和断开都是非常轻量级的，在返回一个小的查询结果方面很高效。虽然说尽可能少的查询当然好，但是在衡量了工作量是否明显减少之后，将大的查询分解成小的查询有时还是很有必要的。

（2）切分查询  
分而治之的思想。有时候我们需要将一个大的查询切分成片，分部分执行，而且分步之间做一个延时，这样避免了长时间的锁住很多的数据。

比如我们在删除数据时 delete， 如果一次删除所有需要删除的数据，可能长时间占用事务，但是我们可以分片，将一个大的delete，通过条件限制，分成多个delete执行，这样就能提高效率。

（3）分解关联查询  
很多高性能的应用都会对关联查询拆分，比如：

mysql>select * from tag left join tag_post on tag_post.tag_id=tag.id left join post on tag_post.post_id = post.id where tag.tag='mysql';

可以分解成

mysql>select * from tag where tag='mysql';
    
    mysql>select * from tag_post where tag_id=1234;
    
    mysql>select * from post where post.id in (123,345,456,8933);

这么分解的原因是什么呢？  
（1）让缓存的效率更高；（比如上面查询的tag已经被缓存了，那么应用就可以跳过第一个查询了。）

（2）将查询分解后，执行单个查询可以减少锁的竞争。

（3）某些情况下效率也会更高，比如上面的分解后用 in 关键字查询，效率更高。

#  4.查询执行的基础 #

首先来看看查询执行的路径的示意图：  
![这里写图片描述][SouthEast]

步骤如下：  
（1）客户端发送一条查询给服务器；

（2）服务器先检查查询缓存，如果命中了缓存，则立刻返回存在缓存中的结果，否则进入下一步。

（3）服务器对SQL进行解析、预处理、再由优化器生成对应的执行计划。

（4）MySQL会根据优化器生成的执行计划、调用存储引擎的API来执行查询。

（5）将结果返回给客户端。

##  （1）MySQL客户端/服务器通信协议 ##

我们不需要了解通信协议内部是如何实现的，只需理解通信协议是如何工作的。

MySQL的客户端和服务器通信协议是半双工的，意味着同一时刻，只能有一方向另一方发送数据。

##  （2）查询缓存 ##

在解析一个SQL语句之前，如果缓存是打开的，MySQL会优先检查这个查询是否命中查询缓存中的数据。如果命中了缓存就会直接从缓存中拿到结果集并返回给客户端。如果没有命中缓存就会进入下一阶段。

##  （3）查询优化器 ##

在这一部分最重要的就是查询优化器了，一条查询语句可以有很多种执行方式，最后都将返回相同的结果，优化器的作用就是找到最高效的执行计划。

> 下面给出MySQL查询优化器能够自动处理的优化类型：  
> （1）重新定义关联表的顺序：数据表的关联顺序并不总是按照在查询中指定的顺序进行，这个与优化器有关。
> 
> （2）将外连接转换成内连接：
> 
> （3）使用等价变换规则：可以减少一些比较或则移除一些恒等的判断。比如（5=5 and a>5）将被改写成（a > 5）。
> 
> （4）优化 COUNT()、 MIN() 和 MAX() 函数：索引和列是否允许为空可以帮助优化这类表达式：比如求最小值，利用B-Tree结构特点，只需要查询B-Tree的最左端记录就OK了。同理对于求max()函数也是一样。但是对于COUNT(\*)这个函数，MyISAM存储类型维护了一个变量来专门存储表中记录行的总数。
> 
> （5）覆盖索引扫描：当索引中的列包含所有查询中需要使用的列时候，MySQL可以直接使用索引返回需要的数据，无需再查询对应的数据行。
> 
> （6）子查询优化
> 
> （8）提前终止查询：在发现已经满足查询需求的时候，MySQL总是能够立即终止查询。比如 limit 关键字。
> 
> （9）列表 IN 的比较代替OR：MySQL会先将IN语句中的数据排序，再通过二分查找来确定列表中的数据是否满足需求，这是一个O(logn)的复杂度的操作。 如果等价转换成 OR 就会变成O(n)的时间复杂度。

##  （4）排序优化 ##

不管怎么说，排序都是一个成本很高的操作，一定要避免对大数据排序。所以我们一定要利用索引列来进行排序，当不能利用索引生成排序结果时候，肯定就会存在回表查询记录的情况，这时候数据量巨大，会使用文件排序。

[SouthEast]: /images/20220709/ee7e511437f84052b9a0beee26cf6197.png