查询优化
![](https://images2018.cnblogs.com/blog/1257088/201711/1257088-20171128155615894-990919589.png)
1.查询主流程: ![]()
a.客户端发送一条查询给服务器
b.服务器先检查查询缓存,如果命中了缓存,则立刻返回存储在缓存中的结果,否则进入下一阶段。
c.服务器进行SQL解析,预处理,再由优化器生成对应的执行计划,
d.mysql根据优化器生成的执行计划,调用存储引擎的API来执行查询。
e.将结果返回给客户端。
2.MySQL连接状态:
对于一个MYSQL连接线程,任何时刻都有一个状态,在一个查询的周期中,状态可能会变化很多次,这些状态有:
sleep: 线程正在等待客户端发送新的请求
query:线程正在执行查询或者正在将结果发送给客户端
locked:在mysql服务器层,该线程正在等待表锁。在存储引擎级别实现的锁,例如innoDB的行锁,并不会体现在线程状态中。对于myisam来说这是个比较典型的状态,但在其他没有行锁的引擎也经常会出现。
analyzing and statistics:线程正在收集存储引擎的统计信息,并生成查询的执行计划。
copying to tmp table [on disk]:线程正在执行查询,并且将结果集复制到一个临时表,这种状态一般要么是在做 group by 操作,要么是文件排序操作,或者是union操作。如果这个状态后面还有 on disk 标记,那表示mysql正在将一个内存临时表放到磁盘上。
sorting result:线程正在对结果集进行排序
sending data:这表示多种情况,线程可能在多个状态之间传送数据,或者在生成结果集,或者在客户端发送请求。
3.解决查询效率低方法
a.确认应用程序是否在检索大量的超过需要的数据,有时候是访问了太多的行,有时候是访问了太多的列
b.确认mysql服务器是否在分析大量超过需要的数据航行。mysql能够使用下面三种方式应用where条件,从好到坏依次是
1.在索引中使用where条件来过滤不匹配的记录,这是在存储引擎层完成的
2.使用索引覆盖扫描来返回记录,直接在索引中过滤不需要的记录并返回命中的结果,这是在mysql服务器层完成的,但无需再回表查询记录
3.从数据库中返回数据,然后过滤不满足条件的记录,这也是在mysql 服务器层完成的。
c如果发现查询需要扫描大量的额数据但只返回少数的行,可以如下优化
1.使用索引覆盖扫描
2.改变库表结构,例如使用单独的汇总表
3.重写这个复杂的查询
4.重构查询的几种方法
a.一个复杂查询 or 多个简单查询:设计查询的时候一个需要考虑的重要问题是,是否需要将一个复杂的查询分成多个简单的查询。
b.切分查询:有时候对于一个大查询我们需要“分而治之”,将大查询切分为小查询,每个查询功能完全一样,只完成一小部分,每次只返回一小部分查询结果。
c分解关联查询:
select * from tag
join tag_post on tag_post.tag_id = tag.id
join post on tag_post.post_id = post.id
where tag.tag = 'mysql'
可以分解成下面这些查询来代替:
> select * from tag where tag = 'mysql'
> select * from tag_post where tag_id = 1234
> select * from post where post_id in (123, 456, 567, 9098, 8904)
优势:
让缓存的效率更高。
将查询分解后,执行单个查询可以减少锁的竞争。
在应用层做关联,可以更容易对数据库进行拆分,更容易做到高性能和可扩展。
查询本身效率也可能会有所提升。
可以减少冗余记录的查询,
更进一步,这样做相当于在应用中实现了哈希关联,而不是使用mysql的嵌套循环关联。
5.MYsql的一些优化类型
重新定义关联表的顺序,命中率高的表可能被先查询
将外链接转换为连接
使用等价变换规则
优化Count() MIN() MAX():
预估并转换为常数表达式
覆盖索引扫描
子查询优化
提前终止查询
等值传播
列表IN()的比较
排序优化,尽可能避免排序或者对大量数据进行排序,如果要排序的数据量小于“排序缓存区”,使用内存进行快排,如果内存不够,先将数据分块,每个分块使用快排后将结果存放在磁盘上,最后将各排好序的块进行合并
limit优化,要么在应用中限制分页的数量,要么根据索引覆盖扫描找出对应的行索引,然后在根据索引到数据库中查询具体的记录
union优化,除非真的需要消除重复的行,否则一定要使用union all。因为如果没有all关键字,mysql会给零时表加上distinct选项,这回导致对整个零时表的数据做唯一性检查
如果查询的两个表大小相当,那么用in和exists差别不大。 如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in: