MySQL调优(八)-查询优化

在编写快速的查询之前,需要清楚一点,真正重要的是响应时间,而且要知道在整个SQL语句的执行过程中每个步骤都花费了多长时间,要知道哪些步骤是拖垮执行效率的关键步骤,想要做到这点,必须要知道查询的生命周期,然后进行优化,不同的应用场景有不同的优化方式,不能一概而论,具体情况具体分析;
下面主要从sql 查询诊断,数据访问优化,执行过程优化,特定类型的查询优化几个维度了解MySQL的查询优化过程:

一、Query Profiler

query profiler 是一个使用非常方便的 Query 诊断分析工具:
使用:

set profiling=1; -- 开启SQL语句剖析功能。0或OFF表示关闭(默认模式)。1或ON表示开启
set profiling_history_size = 15 -- 设置保留profiling的数目,缺省为15,范围为0至100,为0时将禁用profiling。
show PROFILES;

SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]      --如果不指定,只是显示最近执行的语句;如果指定会显示语句n,n对应query_id的值
    [LIMIT row_count [OFFSET offset]]
    
type:
    ALL                --显示所有的开销信息
  | BLOCK IO           --显示块IO相关开销
  | CONTEXT SWITCHES   --上下文切换相关开销
  | CPU                --显示CPU相关开销信息
  | IPC                --显示发送和接收相关开销信息
  | MEMORY             --显示内存相关开销信息
  | PAGE FAULTS        --显示页面错误相关开销信息
  | SOURCE             --显示和Source_function,Source_file,Source_line相关的开销信息
  | SWAPS              --显示交换次数相关开销的信息

show profile; -- 查看上一条SQL语句的开销信息 --注,show profile之类的语句不会被profiling,即自身不会产生Profiling
show profile all for query 297; --显示所有性能信息

二、查询慢的原因分析

网络,CPU,IO,上下文切换,系统调用,生成统计信息,锁等待时间(注意Innodb,MyISAM的锁机制)

三、优化数据访问

3.1 查询性能低下的主要原因是访问的数据太多,某些查询不可避免的需要筛选大量的数据,我们可以通过减少访问数据量的方式优化:
确认程序是否在检索超过需要的数据
确认mysql服务器层是否在分析大量超过需要的数据行
3.2 是否向数据库请求了不需要的数据

  • 查询不需要的记录:我们常常会误以为mysql会只返回需要的数据,实际上mysql却是先返回全部结果再进行计算,在日常的开发习惯中,经常是先用select语句查询大量的结果,然后获取前面的N行后关闭结果集。
    优化方式是在查询后面添加limit
  • 多表关联时返回全部列:
select * from actor inner join film_actor using(actor_id) inner join film using(film_id) where film.title='Academy Dinosaur';
select actor.* from actor...;

在公司的企业需求中,禁止使用select *,虽然这种方式能够简化开发,但是会影响查询的性能,所以尽量不要使用。

  • 重复查询相同的数据:如果需要不断的重复执行相同的查询,且每次返回完全相同的数据,因此,基于这样的应用场景,我们可以将这部分数据缓存起来,这样的话能够提高查询效率。

四、优化执行过程

-- 查看sql执行成本
show status like 'last_query_cost';
-- 当需要排序的列的总大小超过max_length_for_sort_data定义的字节,mysql会选择双次排序,反之使用单次排序,当然,用户可以设置此参数的值来选择排序的方式(max_length_for_sort_data默认为1M)
show variables like '%max_length_for_sort_data%';

1)查询缓存
在解析一个查询语句之前,如果查询缓存是打开的,那么mysql会优先检查这个查询是否命中查询缓存中的数据,如果查询恰好命中了查询缓存,那么会在返回结果之前会检查用户权限,如果权限没有问题,那么mysql会跳过所有的阶段,就直接从缓存中拿到结果并返回给客户端

2)查询优化处理
mysql查询完缓存之后会经过以下几个步骤:解析SQL、预处理、优化SQL执行计划,这几个步骤出现任何的错误,都可能会终止查询

  • 语法解析器和预处理
    mysql通过关键字将SQL语句进行解析,并生成一颗解析树,mysql解析器将使用mysql语法规则验证和解析查询,例如验证是否使用了错误的关键字或者顺序是否正确等等,预处理器会进一步检查解析树是否合法,例如表名和列名是否存在,是否有歧义,还会验证权限等等
  • 查询优化器
select count(*) from film_actor;
show status like 'last_query_cost';

可以看到这条查询语句大概需要做1104个数据页才能找到对应的数据,这是经过一系列的统计信息计算来的(每个表或者索引的页面个数,索引的基数,索引和数据行的长度,索引的分布情况)
在很多情况下mysql会选择错误的执行计划,原因如下:
统计信息不准确:InnoDB因为其mvcc的架构,并不能维护一个数据表的行数的精确统计信息
执行计划的成本估算不等于实际执行的成本:有时候某个执行计划虽然需要读取更多的页面,但是他的成本却更小,因为如果这些页面都是顺序读或者这些页面都已经在内
存中的话,那么它的访问成本将很小,mysql层面并不知道哪些页面在内存中,哪些在磁盘,所以查询之际执行过程中到底需要多少次IO是无法得知的
mysql的最优可能跟你想的不一样:mysql的优化是基于成本模型的优化,但是有可能不是最快的优化
mysql不考虑其他并发执行的查询
mysq不会开了不受其控制的操作成本:执行存储过程或者用户自定义函数的成本

  • 优化器的优化策略:

静态优化,直接对解析树进行分析,并完成优化
动态优化,动态优化与查询的上下文有关,也可能跟取值、索引对应的行数有关
mysql对查询的静态优化只需要一次,但对动态优化在每次执行时都需要重新评估

  • 优化器的优化类型:
    1)重新定义关联表的顺序:数据表的关联并不总是按照在查询中指定的顺序进行,决定关联顺序时优化器很重要的功能
    2)将外连接转化成内连接,内连接的效率要高于外连接
    3)使用等价变换规则,mysql可以使用一些等价变化来简化并规划表达式
    4)优化count(),min(),max():索引和列是否可以为空通常可以帮助mysql优化这类表达式:例如,要找到某一列的最小值,只需要查询索引的最左端的记录即可,不需要全文扫描比较
    预估并转化为常数表达式,当mysql检测到一个表达式可以转化为常数的时候,就会一直把该表达式作为常数进行处理:
explain select film.film_id,film_actor.actor_id from film inner join film_actor using(film_id) where film.film_id = 1

5)索引覆盖扫描,当索引中的列包含所有查询中需要使用的列的时候,可以使用覆盖索引
6)子查询优化:mysql在某些情况下可以将子查询转换一种效率更高的形式,从而减少多个查询多次对数据进行访问,例如将经常查询的数据放入到缓存中
7)等值传播:如果两个列的值通过等式关联,那么mysql能够把其中一个列的where条件传递到另一个上:

explain select film.film_id from film inner join film_actor using(film_id
) where film.film_id > 500;
-- 这里使用film_id字段进行等值关联,film_id这个列不仅适用于film表而且适用于film_actor表
explain select film.film_id from film inner join film_actor using(film_id
) where film.film_id > 500 and film_actor.film_id > 500;   

五、优化特定类型的查询

5.1 优化count()查询
1)总有人认为myisam的count函数比较快,这是有前提条件的,只有没有任何where条件的count(*)才是比较快的
2)在某些应用场景中,不需要完全精确的值,可以参考使用近似值来代替,比如可以使用explain来获取近似的值其实在很多OLAP的应用中,需要计算某一个列值的基数,有一个计算近似值的算法叫hyperloglog。
3)一般情况下,count()需要扫描大量的行才能获取精确的数据,其实很难优化,在实际操作的时候可以考虑使用索引覆盖扫描,或者增加汇总表,或者增加外部缓存系统。

5.2 优化关联查询
1)确保on或者using子句中的列上有索引,在创建索引的时候就要考虑到关联的顺序:当表A和表B使用列C关联的时候,如果优化器的关联顺序是B、A,那么就不需要再B表的对应列上建上索引,没有用到的索引只会带来额外的负担,一般情况下来说,只需要在关联顺序中的第二个表的相应列上创建索引
2)确保任何的groupby和order by中的表达式只涉及到一个表中的列,这样mysql才有可能使用索引来优化这个过程

5.3 优化子查询
子查询的优化最重要的优化建议是尽可能使用关联查询代替;以为子查询会产生过程中的临时表,如果能用join和子查询实现尽量用join

5.4 优化limit分页
在很多应用场景中我们需要将数据进行分页,一般会使用limit加上偏移量的方法实现,同时加上合适的orderby 的子句,如果这种方式有索引的帮助,效率通常不错,否则的化需要进行大量的文件排序操作,还有一种情况,当偏移量非常大的时候,前面的大部分数据都会被抛弃,这样的代价太高。要优化这种查询的话,要么是在页面中限制分页的数量,要么优化大偏移量的性能
优化此类查询的最简单的办法就是尽可能地使用覆盖索引,而不是查询所有的列:

--查看执行计划查看扫描的行数
select film_id,description from film order by title limit 50,5
explain select film.film_id,film.description from film inner join (select film_id from film order by title limit 50,5) as lim using(film_id);

5.5 优化union查询
除非确实需要服务器消除重复的行,否则一定要使用union all,因此没有all关键字,mysql会在查询的时候给临时表加上distinct的关键字,这个操作的代价很高。

5.6 推荐使用用户自定义变量
在这里插入图片描述

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值