Mysql进阶_查询优化
2021/12/31 19:09:10
本文主要是介绍Mysql进阶_查询优化,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
Mysql进阶_查询优化
最近感觉自己数据库技术知识储备有点薄弱,很多东西只知其然,不知其所以然,对一些比较复杂的SQL查询也没好的查询方案。一咬牙,一跺脚,决定拿起在书架上落灰已久的《高性能Mysql》一书,为了避免走马观花式的阅读,给自己定一个读书任务,每看完一章,写一篇总结博客,用来记录当前章节的重点难点知识,供日后翻阅查看,权当读书笔记。
1. 为什么查询速度会慢
在一个查询中,最重要的是响应时间,如果要优化查询,实际上要优化其子任务,要么消除其中一些子任务,要么减少子任务的执行次数,要么让子任务运行的更快,要对一个查询进行优化,首先要理解它为什么会慢。
对于低效的查询,可以从以下两个方面优化分析:
- 确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行,但有时候也可能访问了太多的列。
- 确认MySQL服务器层是否存在分析大量超过需要的数据行。
1.1 查询不需要的记录
- 查询不需要的记录:比如说只需要10条数据,却将整个表的数据都查询出来。
- 多表关联时返回全部列:多表关联查询的时候,只需要几列,却直接select *
- 总是取出全部列:只需某几列的数据,直接select *
- 重复查询相同的数据:不断地重复执行相同的查询,然后每次都返回相同数据。
1.2 MySQL是否扫描额外的记录
1.响应时间
响应时间是服务时间和排队时间之和。
服务时间:数据库处理这个查询真正花费的时间。
排队时间:服务器因为等待某些资源而没有真正执行查询的时间,可能等I/O操作完成,也可能等待行锁等。
2.扫描的行数和返回行数
3. 扫描行数和访问类型
扫描表、扫描索引、范围访问和单值访问的概念。
一般情况下mysql的where条件从好到坏依次是:
- 在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎完成的。
- 使用索引覆盖扫描(在Extra列中出现了Using index)来返回记录,直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的,但无须再回表查询记录。
- 从数据库表中返回数据,然后过滤不满足条件的记录(在Extra列中出现Using Where)。这在MySQL服务处完成,MySQL需要先从数据表读出记录然后过滤。
如果查询需要扫描大量的数据但只返回少数的行,可以采用以下方法进行优化:
- 使用索引覆盖扫描,把所有需要用的列都放到索引中,这样存储引擎无须回表获取对应行就可以返回结果了。
- 改变库表结构。例如使用单独的汇总表。
- 重写这个复杂的查询,让MySQL优化器能够以更优化的方式执行这个查询。
2 重构查询的方式
优化有问题的查询时,目标应该是找到一个更优的方法获得实际需要的结果,而不一定总是需要从MySQL获取一模一样的结果集。
2. 1 一个复杂查询还是多个简单查询
设计查询的时候有一个需要考虑的重要问题是,是否将一个复杂的查询分成多个简单的查询?
在其他条件都相同的时候,使用尽可能少的查询当然是更好的。但是有时候,将一个大查询分解为更多的小查询是很有必要的。
2.2 切分查询
将大查询切分成小查询,每个查询功能完全一样,只完成一小部分,每次只返回一小部分查询结果。
比如说删除旧数据,定期清除大量数据时,如果用一个大的语句一次性完成的话,则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的delete语句切分成多个较小的查询可以尽可能小地影响MySQL性能,同时还可以减少MySQL复制的延迟。
# 优化前 mysql>delete from messages where created < date_sum(now(), interval 3 month); # 优化后 rows_affected = 0 do{ rows_affected = do_query( "mysql>delete from messages where created < date_sum(now(), interval 3 month) limit 10000") } while rows_afffected > 0
2.3 分解关联查询
用分解关联查询的方式重构查询有如下的优势:
- 让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。
- 将查询分解后,执行单个查询可以减少锁的竞争。
- 在应用层做关联,可以更容易对数据库进行拆分,更容易做到高性能和可扩展。
- 查询本身效率也可能会有所提升。
- 可以减少冗余记录的查询。
更容易对数据库进行拆分, 更容易做到高性能和可扩展
减少冗余记录的查询
3 查询执行的基础
当向MySQL发送一个请求时,MySQL执行如下图所示:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GD5W93wA-1640933725518)(file://C:/Users/Lenovo/AppData/Roaming/Typora/typora-user-images/image-20211031133439960.png?lastModify=1637802602)]
- 客户端发送一条查询给服务器。
- 服务器先检查查询缓存,如果命中了缓存,则立刻返回存储在缓存中的结果。否则进入下一阶段。
- 服务器端进行SQL解析、预处理,再由优化器生成对应的执行计划。
- MySQL根据优化器生成的执行计划,调用存储引擎的API来执行查询。
- 将结果返回给客户端。
3.1 MySQL 客户端/服务器通信协议
MySQL客户端和服务器之间的通信协议是"半双工",在任何一个时刻,要么服务器向客户端发送数据,要么客户端向服务器发送数据,这两个动作不可能同时发生。
服务器-> 客户端 或客户端->服务器
查询状态
对于一个MySQL链接,任何时刻都有一个状态,该状态表示了MySQL当前正在做什么。可以使用命令SHOW FULL PROCESSLIST
命令查看(Command列表示当前的状态),
sleep: 线程正在等待客户端发送新的请求
query: 线程正在执行查询或者正在将结果发送给客户端
locked: 等待锁
ayalyzing and statistics : 线程正在收集存储引擎的统计信息, 并生成查询的执行计划
copying to tmp table [on disk]: 线程正在执行查询, 并将结果都赋值到一张临时表,一般是在做group by操作、文件排序操作或union操作。
sorting result: 线程正在对结果集进行排序
Sending data: 线程在多个状态之间传送数据或生成结果集,或在向客户端返回数据
3.2 查询缓存
若查询缓存是打开的,则先查询缓存,没有就进入下一阶段。如果查询缓存命中,就检查用户权限,权限验证成功直接从缓存中拿取结果返回客户端。
注:缓存是大小写敏感的哈希查找,查询和缓存中的查询即时只有一个字节不同,也不会匹配缓存结果。
3.3 查询优化处理
语法解析器和预处理
MySQL通过关键字将SQL语句进行解析,并生成一颗对应的“解析树”。MySQL解析器将使用MySQL语法规则验证和解析查询。
查询优化器
一条查询可以有很多种执行方式,最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
MySQL使用基于成本的优化器,它将尝试预测一个查询使用某种执行计划时的成本,并选择其中成本最小的一个。
静态优化: 直接对解析树进行分析,并完成优化(编译时优化) 。比如,优化器可以通过一些简单的代数变换将WHERE条件转化为另一种等价形式。
动态优化:(运行时优化)
- 重新定义关联表的顺序:数据表的关联并不总是按照在查询中指定的顺序执行。
- **将外链接转化为内连接:**并不是所有的OUTER JOIN语句都必须以外连接的方式执行。比如WHERE条件、库表结构都可能会让外连接等价于一个内连接。
- 使用等价变化规则:MySQL可以使用一些等价变化来简化并规范表达式。它可以合并和减少一些比较,还可以移除一些恒成立和一些恒不成立的判断。
- 优化count(), min() 和max():索引和列是否可为空通常可以帮助MySQL优化这类表达式。
- 预估并转化为常数表达式:当MySQL检测到一个表达式可以转化为常数的时候,就会一直把该表达式作为常数进行优化处理。
- 覆盖索引扫描
- 子查询优化
- 提前终止查询
- 等值传播
- 列表IN()的比较
数据和索引的统计信息
MySQL服务器层没有任何统计信息,所以查询优化器在生成查询的执行计划时,需要向存储引擎获取相应的统计信息。
MySQL如何执行关联查询
任何一次查询都是一次关联
MySQL对任何关联都执行嵌套循环关联操作, 即mysql先在一个表中循环取出单挑数据, 然后在嵌套循环到下一个表中寻找匹配的行
执行计划
关联查询优化器
排序优化
3.4 查询执行引擎
在解析和优化阶段,MySQL将生成查询对应的执行计划,MySQL的查询执行引擎则根据这个执行计划来完成整个查询。
3.5 返回结果给客户端
查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果集给客户端,MySQL仍然会返回这个查询的一些信息,如该查询影响到的行数。
如果查询可以被缓存,那么MySQL在这个阶段也会将结果放到查询缓存中。
4 MySQL查询优化器的局限性
4.1 关联子查询
WHERE条件中包含IN()的子查询语句非常影响查询效率。
4.2 UNION的限制**
4.3 索引合并优化
4.4 等值传递
4.5 并行执行
4.6 哈希关联
4.7 松散索引扫描
4.8 最大值和最小值优化
4.9 在同一个表上查询和更新
5 优化特定类型的查询
5.1 优化COUNT()查询
5.2 优化关联查询
5.3 优化子查询
5.4 优化GOUP BY和DISTANCE
5.5 优化LIMIT分页
5.6 优化SQL_CALC_FOUND_ROWS
5.7 优化UNION查询
5.8 静态查询分析
5.9 使用用户自定义变量
这篇关于Mysql进阶_查询优化的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-09-21MySQL集群部署资料:新手入门教程
- 2024-09-21MySQL集群资料:初学者入门指南
- 2024-09-21部署MySQL集群资料:新手入门教程
- 2024-09-20MySQL集群部署教程:新手入门指南
- 2024-09-20MySQL集群教程:初学者必备指南
- 2024-09-20部署MySQL集群项目实战:新手入门教程
- 2024-09-20如何部署MySQL集群:简单教程
- 2024-09-20MySQL集群部署:新手入门指南
- 2024-09-20部署MySQL集群学习:入门指南
- 2024-09-20部署MySQL集群入门教程