那位先生

个人站

前世五百次回眸,才能换得今生的一次擦肩而过。


MySQL-04-查询性能优化

1.为什么查询速度会慢

查询这一任务包含一系列子任务,优化的实质是优化这些子任务,要么消除一些子任务,要么减少任务的执行次数,要么让子任务运行的更快。
查询的生命周期包括:客户端到服务器,然后服务器上进行解析,生成执行计划,然后查询,最后返回结果给客户端。其中执行阶段最为重要。
完成任务的时候查询需要在不同的地方花费时间,包括网络,CPU计算,生成统计信息和执行计划,锁互斥等操作。
优化的目的是减少不必要的额外操作、减少额外的重复操作次数、优化执行慢的操作。

2.优化数据访问

1.概述

查询性能低下的根本原因是访问的数据太多。大多性能低下的查询都可以通过减少访问数据量的方式进行优化。对于低效的查询的优化方式:
1.确认程序是否在检索大量超过需要的数据。
2.确认MYSQL服务层是否在分析大量超过需要的数据行。

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

有些查询会请求超过实际需要的数据,然后这些多余的数据会被丢弃。这会给MySQL服务器带来额外的负担,并增加网络开销,另外也会消耗应用服务器的CPU和内存资源。比如:
1.查询不需要的记录。比如查询全部列后在前段只取前10条之类的。解决办法:查询语句后加上LIMIT。
2.多表关联时返回全部列。比如三表关联的语句把3个表的列全部返回,尽管实际只需要几列。解决办法:只返回需要的列。
3.总是取出全部列。比如用select*。解决办法:除了考虑复用性外,尽量只去需要的列。
4.重复查询相同的数据。比如不断的重复执行相同的查询,然后每次都返回相同的数据。解决办法:太你家缓存。

3.是否在扫描额外的记录

1.扫描的行数和返回的行数。这两者接近是最好的。
2.扫描的行数和访问类型。看explain中的type,解决办法是添加索引。
如果发现查询需要扫描大量的数据单只返回少数行,解决办法是:使用索引覆盖扫描,把所有需要用的列都放在索引中。改变库表结构。例如使用单独的汇总表。重写这个复杂的查询。

3.重构查询的方式

1.切分查询

比如讲一次性删除100w条数据的删除语句,改成10条删除语句。这样可以减少锁的影响和对 服务器的影响。

2.分解关联查询

比如将:

select *from tag 
	join tag_post on tag_post.tag_id=tag.id
	join post on tag_post.post_id=post.id

分解成:

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

好处:
1.让缓存的效率更高。因为有很多查询需要单表查询的结果。
2.减少锁的竞争。
3.高性能和可扩展。
4.查询本身的效率也更高。用IN代替关联查询,让MySQL按ID顺序查询比原来的随机查询效率高。
5.减少冗余查询的记录。
6.在应用中实现hash关联比在MySQL中的嵌套查询关联效率高。

4.查询执行的基础

1.一个MySQL的查询过程

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

2.查询缓存

MySQL查询缓存是通过大小写敏感的哈希查找实现的,因此即使查询和缓存中的查询只有一个字节不同,那也不会匹配缓存结果。

3.查询优化处理

语法解析和预处理:MySQL通过关键字将SQL语句进行解析,并生成一颗对应的“解析树”,mysql解析器使用MySQL语法验证和解析查询,比如判断解析树是否合法。
查询优化器:一个查询可以有很多种执行方式,最后都返回相同的结果。查询优化器的目的就是找到最后的执行计划。优化类型包括以下:
重新定义关联表的顺序;将外连接转为内连接;使用等价变化规则;优化count()、min()、max();预估并转化为常熟表达式;索引覆盖扫描;子查询优化;提前终止查询;等值传播; 列表IN()的比较:将in中的数据进行排序,然后根据二分查找法判断,复杂度降低为O(logn)。
MySQL关联查询的策略:执行嵌套循环关联操作。
排序:如果oeder by 字句的所有列都来自关联的第一个表,namemysql在关联处理第一个表的时候就进行文件排序,explain中的extra出现using filesort。除此之外的所有情况,MySQL都 将关联的结果放到一个临时表,然后在所有的关联都结束后再进行文件排序,explain 的extra 出现的是 using temporary;using filesort。

5.优化特定类型的查询

1.优化count查询

count(*)忽略列直接统计所有的行数。count(列):统计行有值的结果数。
myinsam中只在没有where条件的时候才是最快。
优化:where条件中>或<要匹配的列很多时,可以使用总数减去相反的;使用近似值:用explain的估算行。

2.优化关联查询

确保on的列上有索引;如果A关联B,而且顺序是B、A,那么就不需要在B上建立索引。一般来说除非有其它理由,否则只需要在关联顺序中的第二个表的相应列上创建索引。
确保任何的group by 和 order by中的表达式只涉及一个表中的列。这样MySQL才有可能使用索引优化。

3.优化子查询

尽量用关联查询替代

4.优化group by和distinct

都是用索引来优化,是最有效的方法。
如果需要对关联查询做group by,并且是按照表中的某个列进行分组,那么通常采用查找表的标识列分组效率更高。比如:

film_actor fa inner join actor a on fa.id = a.id group by fa.id

5.优化limit分页

优化大偏移量的分页,比如(limit 1000,20)一般使用索引覆盖扫描查询列(如id),然后再做一次关联查询。也称为迟延关联。比如:

select film_id,description from film order by title limit 50,5;

优化成:

select film_id,description from film inner join (select film_id from film order by title limit 50,5) as t1 on t1.film_id = film_id;

或者改为between,或者算出上次取数据的位置,下次直接order by xxx desc + limit

6.优化union查询

如果没必要消除重复行,尽量加上union all。因为这样MySQL不用加上distinct来做唯一性检查。

取消

感谢您的支持,我会继续努力的!

扫码支持
扫码支持
你说多少就多少

比五毛钱特效专业哦