Nivelle 开拓视野冲破艰险看见世界 身临其境贴近彼此感受生活

索引学习之查询性能优化

2017-04-23
nivelle

查询优化

查询生命周期的定义:

从客户端,到服务器,然后在服务器上进行解析,生成执行计划,执行,并返回给客户端。 其中执行包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理,包括排序、分组。

慢查询的两个原因:

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

典型案例

1.常见错误误以为mysql只返回需要的数据,实际上MySQL却是先返回全部结果集再进行计算。最有效的避免方法是使用limit

2.多表关联是返回全部列,应该只取需要的列。

3.总是取出全部列。取出全部列会让优化器无法完成索引覆盖扫描这类优化,还会为服务器带来额外的I/O\,内存和CPU的消耗。

4.重复查询相同的数据。例如在用户评论的地方需要查询用户头像的url,那么用户多次评论的时候,可能会反复查询这个数据。比较好的方案是,当初查询时候将这个数据缓存起来,需要的时候从缓存中取出,这样性能显然会更好。

如果发现查询需要扫描大量的数据但是只返回少数的行,那么可以尝试下面的技巧去优化:

  • 使用索引覆盖扫描,把所有需要用的列都放到索引中,这样存储引擎无须回表获取对应行就可以返回结果。
  • 改变库表接口(????,这个是啥回头弄明白)
  • 重构查询方式,优化器能够以更优化的方式执行这个查询。

重构查询方式

1.关于切分查询:

有时候对于一个大查询我们需要“分而治之”,将大查询切分成小查询,每个查询功能完全一样,只完成一小部分,每次只返回一小部分查询结果。例如删除旧数据,定期删除大量数据时如果用一个大语句的话,容易造成一次锁住很多数据,占满整个事物日志、耗尽系统资源、阻塞很多小的单很重要的查询。同时,如果每次删除完后,暂停一下再做下一次删除,这样也可以将服务器上原本一次的压力分散到一个很长的时间段中,大大降低了对服务器的影响,还可以大大减少删除时锁的持有时间。

2.分解关联查询

很多高性能的应用都会对关联查询进行分解,简单地,可以对每一个表进行一次单表查询,然后将结果应用程序中进行关联。

这么做的优点

  • 让缓存更有效。许多应用程序可以方便地缓存单表查询对应的结果对象。
  • 查询分解后,执行单个查询可以减少锁竞争。
  • 在应用层做关联,可以更容易对数据库进行拆分,更容易做到高性能和可拓展。
  • 查询效率本身也可能会有很大提升。
  • 减少冗余记录的查询,在应用层只需查询一次,而在数据库中关联查询可能会重复地访问同一部分数据,

执行查询的基础

MySQL执行一次查询的流程如图: image

通信协议

MySQL客户端/服务器通信协议是“半双工”的,这意味着,在任何一个时刻,要么由服务器向客户端发送数据,要么客户端向服务器发送数据,着俩动作不能同时发生。所以,我们无法将一个消息切成小块独立来发送。

客户端用一个单独的数据包将查询传递给服务器,这也是为什么查询的语句很长的时候,参数max_allowed_packet就特别重要了。一旦客户端发送了请求,它能做的事情就是等待结果了。

相反,服务器响应给用户的数据通常很多,有多个数据包组成。当服务器开始响应客户端请求时,客户端必须完整接受整个返回结果,而不能简单地只取前面几条结果,然后让服务器停止发送数据。这也是在必要的时候,一定要加上limit限制的原因。

排序优化


  • 如果需要排序的数据量小于“排序缓冲区”,MySQL使用内存进行“快速排序”操作。如果内存不够排序,那么MySQL会将数据先分块,对每个独立的块使用“快速排序”进行排序,并将各个块的排序结果存放在磁盘上,然后将各个排好序的块进行合并,最后返回排序结果。 ***

  • 在关联查询的时候如果需要排序,MySQL会分两种情况来处理这样的文件排序。如果order by 子句中所有列都来自关联的第一个表,那么MySQL的explain 结果中可以看到extra 会有“using filesort”.除此之外的所有情况,MySQL都会将关联的结果存放到一个临时表中,然后在关联结束后进行文件排序。此时explain 结果中可以看到extra 会有“using temporary;using filesort”.当只需要返回部分数据时MySQL5.6 使用limit子句,不再对所有结果进行排序,而是根据实际情况,选择抛弃不满足条件的结果,然后进行排序。


查询优化器的局限性

  • 关联子查询。

例如:where 条件中包含in()的子查询语句:

select * from film where film_id in (select film_id from film.actor where actor_id =1);

错误理解,因为对in()有专门的优化策略,一般认为会先执行子查询返回所有的actor_id 为1的film_id:

select group_concat(film_id) freom film_actor where actor_id =1;
select * freom film where film_id in(1,23,25,106,140,277,37,978,980);

其实MySQL会把相关的外层表压倒子查询中,他认为这样可以更高效查询数据

select * from film where exists(select * from film_actor where actor_id =1 and 
film_actor.film_id = film.film.id)

另一个办法是使用group_concat() 在in()中构造一个由逗号分隔的列表。有时会更快。因为使用in()加子查询,性能会很糟糕,所以通常建议使用exists()等效的改写查询来获取更好的效率。

  • union的限制

如果希望union的各个子句能够根据limit只取部分结果集,或者希望能够先排好序在合并结果集的话,就需要在各个union子句中分别使用这些子句。此外,从临时表中取出数据顺序并不是一定的,所以如果想正确的获取顺序,还需加上一个全局的order by 和limit 操作。

  • 等值传递

例如有个非常大的in()列表,mysql发现存在where、on或者using子句,将这个列表值和另一个表的某个列 相关联。那么优化器会将in()列表都复制应用到关联的各表中。

  • 最大值和最小值优化

对于min()和max()查询,MySQL的优化做的并不好。

select min(acor_id) from actor where first_name="nivelle";

因为first_name 字段上并没有索引,因此会进行全表扫描。如果能进行主键扫描,那么理论上,读到的第一个满足条件的记录,就是我们需要的最小值了,因为主键是严格按照actor_id 字段的大小顺序排列的。

优化方法:移除min()然后使用limit来查询:

select actor_id from actor use index(primary) where first_name ="nivelle" limit 1;

优化特定类型的查询

  • 优化count() 查询

    它有两个不同的作用:用以统计某个列值得数量,也可以统计行数。在统计列值时要求列值时非空的(不统计null)

    另一种作用是统计结果集的行数。当确认空号内的表达式不能为空时,实际就是在统计行数。count(*) 它会忽略所有的列值直接统计所有行数。

如果希望知道的是结果集的行数,直接使用count(*) ,这样写意义清晰,性能也会很好

  • 优化关联查询

    1.确保on或者using子句中的列上有索引。创建索引的时候就熬考虑到关联的顺序。一般来说,除非有其他理由,否则只需要在关联顺序中的第二张表上相应的列上创建索引

    2.确保任何的group by和order by 中的表达式只涉及到一个表中的列。这样MySQL才有可能使用索引来优化这个过程。

  • 优化group by 和 distinct

    无法使用索引的时候,group by 使用两种方式实现功能:临时表或者文件排序。

    如果需要对关联查询做分组,并且是按照查找表中的某个列进行分组,那么通常采用查找表的标识列分组效率会比其他列高。

    如果没有通过order by 子句显示滴指定排序列,当使用group by 子句的时候,结果集会自动按照分组的字段进行排序。如果不关心结果集的顺序,这种默认排序又导致了需要文件排序,而这种默认排序又导致了需要文件排序,则可以 使用order by null,让mysql 不再进行文件排序。也可以在group by 子句中直接使用desc 或者 asc 关键字,使分组的结果集按照需要的方向排序。

  • limit 分页优化

    优化此类分页查询的一个简单方法就是尽可能地使用覆盖索引扫描,而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候,这样做的效率会提升非常大。

    如果这个表非常大,那么这个查询最好改写成下面的样子:

    select film.film_id,film.description 
       
    from  film 
       
    inner join(
       select film_id freom film order by title limit 50,5
    )as lim using (film_id);
       
    

    这里的“延迟关联”将大大提升查询效率,它让mysql 扫描尽可能少的页面,获取需要访问的记录后再根据关联列回原表查询需要的所有列。

    image

  • 优化union

    除非确实需要服务器消除重复的行,否则就一定要使用union all ,这一点很重要。如果没有all 关键字,mysql 会给临时表加上distinct 选项,这回导致对整个临时表的数据唯一性检查。这样做代价非常高。即使有all 关键字,MySQL任然会使用临时表存储结果。事实上,mysql 总是将结果放入临时表,然后再读出,再返回给客户端。

小知识:group by,文件排序,union操作都会有临时表产生,在from子句中遇到子查询的时候,先执行的子查询并将其结果放到一个临时表中,然后将其当做一个普通表对待。


更新中。。。。


评论