MySQL夯实之路-查询性能优化深入浅出

发布时间:2024年01月13日

MySQL调优分析

explain;show status查看服务器状态信息

优化

减少子任务,减少子任务执行次数,减少子任务执行时间(优,少,快

查询优化分析方法

1.访问了太多的行和列:确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行,但有时候也可能是访问了太多的列。
2.分析了太多的数据行:确认 MySQL服务器层是否在分析大量超过需要的数据行。


sql优化

1.减少查询的记录:使用select语句查询大量结果,然后再获取前N行(如新闻网站,取100条记录,只显示前面的10条;取最值),这时可以使用limit(limit 1,10;从1开始10行)

2.减少查询的列:不要总是SELECT *取出全部列,会额外消耗I/O、内存,CPU。

3.重复查询相同的数据:可以将数据缓存起来需要再取出

4.切分查询:有时需要将大查询切分为多个小查询。

删除旧数据:

定期地清除大量数据时,如果用一个大的语句一次性完成的话,则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的 DELETE语句切分成多个较小的查询可以尽可能小地影响MySQL性能,同时还可以减少MySQL复制的延迟。

?分解关联查询:

查询执行的基础

5.尽量用外连接代替子查询:通过测试来验证

6.尽量不要排序,文件排序很损耗性能,尽量使用索引排序。

7.尽量不要使用in,会导致全表扫描,可以用between

确定了查询只返回需要的数据后,看是否扫描了额外的数据

查询开销

对于MySQL,最简单的衡量查询开销的三个指标如下:
1.响应时间

2.扫描的行数

3.返回的行数
?

?

如果发现查询需要扫描大量的数据但只返回少数的行,那么通常可以尝试下面的技巧去优化它:
1.使用索引覆盖扫描,把所有需要用的列都放到索引中,这样存储引擎无须回表获取对应行就可以返回结果了。
2.改变库表结构。例如使用单独的汇总表。
3.重写这个复杂的查询,让 MySQL优化器能够以更优化的方式执行这个查询。

查询执行的过程

?

查询过程

1.客户端发送一条查询给服务器。
2.服务器先检查查询缓存,如果命中了缓存,则立刻返回存储在缓存中的结果。否则
进入下一阶段。
3.服务器端进行SQL解析、预处理,再由优化器生成对应的执行计划。

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

5.将结果返回给客户端。

?

Mysql客服端/服务端通信协议:

“半双工”的,同一时刻只能一方发送数据

查询状态

Show full processlist(返回结果的command列为当前状态)

查询缓存
在解析一个查询语句之前,如果查询缓存是打开的,那么MySQL会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同,那也不会匹配缓存结果#",这种情况下查询就会进入下--阶段的处理。


如果当前的查询恰好命中了查询缓存,那么在返回查询结果之前MySQL会检查一次用户权限。这仍然是无须解析查询SQL语句的,因为在查询缓存中已经存放了当前查询需要访问的表信息。如果权限没有问题,MySQL会跳过所有其他阶段,直接从缓存中拿到结果并返回给客户端。这种情况下,查询不会被解析,不用生成执行计划,不会被执行。

查询优化处理


查询的生命周期的下一步是将一个SQL转推成一个执行计划,MySQL再依照这个执行计划和存储引擎进行交互。这包括多个子阶段:解析SQL、预处理、优化SQL执行计划。这个过程中任何错误(例如语法错误)都可能终止查询。

语法解析器和预处理
首先,MySQL通过关键字将SQL语句进行解析,并生成一棵对应的“解析树”。MySQL解析器将使用MySQL语法规则验证和解析查询。例如,它将验证是否使用错误的关键字,或者使用关键字的顺序是否正确等,再或者它还会验证引号是否能前后正确匹配。
预处理器则根据一些MySQL规则进一步检查解析树是否合法,例如,这里将检查数据表和数据列是否存在,还会解析名字和别名,看看它们是否有歧义。
下一步预处理器会验证权限。这通常很快,除非服务器上有非常多的权限配置。?

查询优化器
现在语法树被认为是合法的了,并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式,最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。MySQL使用基于成本的优化器,它将尝试预测一个查询使用某种执行计划时的成本,并选择其中成本最小的一个。最初,成本的最小单位是随机读取一个4K数据页的成本,后来(成本计算公式)变得更加复杂,并且引入了一些“因子”来估算某些操作的代价,如当执行一次MERE条件比较的成本。可以通过查询当前会话的Last_query_cost的值来得知MySQL计算的当前查询的成本。


?

Mysql能处理的优化类型:

重新定义关联表的顺序,

将外连接转化为内连接,

使用等价变换规则,

优化count,min,max,

预估并转换为常数表达式,

覆盖索引扫描,

子查询优化

提前终止查询

等值传播

列表in()的比较

执行关联查询

当前MySQL关联执行的策略很简单:MySQL对任何关联都执行嵌套循环关联操作,即MySQL先在一个表中循环取出单条数据,然后再嵌套循环到下一个表中寻找匹配的行,依次下去,直到找到所有表中匹配的行为止。然后根据各个表匹配的行,返回查询中需要的各个列。MySQL会尝试在最后一个关联表中找到所有匹配的行,如果最后一个关联表无法找到更多的行以后,MySQL返回到上一层次关联表,看是否能够找到更多的匹配记录,依此类推迭代执行。

关于MylSAM的神话


MyISAM的COUNT()函数总是非常快,不过这是有前提条件的,即只有没有任何WHERE条件的COUNT(*)才非常快,因为此时无须实际地去计算表的行数。MySQL可以利用存储引擎的特性直接获得这个值。如果 MySQL 知道某列col不可能为NULL值,那么MySQL内部会将COUNT(col)表达式优化为COUNT(*)。
?

?

优化关联查询

? ?

使用用户自定义变量

? ?? ? ? ?? ? ? ?

文章来源:https://blog.csdn.net/qq_61282807/article/details/135490004
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。