MySQL查询性能优化

在我网站上线之前,也就是今年之前,我所有的笔记都是存在Wiz笔记中,网站上线后,也把一些特别重要的笔记导入了网站,但MySQL这块没动。最近趁着准备面试的机会,也把这方面的知识在捋一遍。

本篇文章主要讲述查询性能优化方面的知识。

要想真正对查询性能进行优化,那首先是要了解查询的生命周期的,清楚整个查询都经历了哪些过程,看到底哪些过程消耗了时间。

一般整个查询周期包括:

1、客户端发送查询给服务器;

2、服务器先查询缓存,如果有就返回缓存中结果,没有就进入下一阶段;

3、服务器进行SQL解析、预处理、再由优化器生成对应的执行计划;

4、MySQL跟好友优化器生成的执行计划,调用存储引擎的PAI来执行查询。

 

其实对查询性能优化可以从以下几个方面入手:

  1. SQL语句本身;
  2. 使用缓存。当然MySQL本身是默认开启缓存的,当有缓存时,会通过哈西查找找到之前查查过的同样的数据。我们也可以使用Redis,Memcached等;
  3. 采用MySQL主从复制,集群MySL,将读写分离;

这篇文章也主要说一下SQL语句的优化。

 

 

1、join语句代替子语句。

这是我之前在工作汇总遇到的问题。

当要查两个表或以上的数据时,使用嵌套查询比较慢,此时要使用联接查询。
我在应用汇的一个例子:(也叫联合查询,即使用两个及以上的select语句。)
嵌套查询:用时25分钟 
                  
   create table tmp_6 as SELECT date,sum(count)as count,`block_id` FROM `downloadstat_all` WHERE date BETWEEN '2014-06-01' and '2014-06-30' AND `block_id` IN (select block_id from game_app_new ) GROUP BY 3 ORDER BY 2 desc limit 0,100;

联查:用时96.43秒

select sum(a.count) as count,a.block_id from downloadstat_all a INNER JOIN game_app_new b  using(id) where a.date between '2014-01-01' and '2014-01-31' GROUP BY 2 order by 1 DESC limit 100;

 

查过资料,给出的解释是:当我们用子查询时,并不是先执行上面的game_app_new表,MySQL会把in转换成exists相关子查询,而exists的执行原理就是循环取出downloadstat_all中的每一条记录与b表进行比较,比较的是downloadstat_all.block_id和game_app_new.block_id,如果相同就返回该记录。

我们使用不了downloadstat_all的索引。虽然我们可以使用game_app_new的索引,但其实提高的效率并不高。我们使用join可以大大提高查询效率。

 

当我们在联查中的列也是索引的时候,同样会大大提高效率。联查的执行策略(来自高性能MySQL):MySQL对任何关联都执行嵌套循环关联操作,即MySQL先在一个表中循环取出单条数据,然后再嵌套循环到下一个表中寻找匹配的行,一次下去,直到找到所有匹配的行为止。

 

 

2、从索引的角度去优化查询语句

 

  1. 如果想对某个列建立所以,那么就不要设置为NULL,否则索引就会失效,也尽量避免在where子句中进行null值判断,否则也会使索引失效;
  2. 应尽量避免在 where 子句中使用!=或<>操作符,否则引擎将放弃使用索引而进行全表扫描。(我今天对null进行操作,发现无论怎样,它都返回FALSE);
  3. where自居尽量不使用 in或者not in;
  4. where子句尽量不用表达式;

 

3、返回列数

尽量不要返回所有的列数,一般情况下,我们并不需要把所有列的数据都取出来,只需取几列即可,这会大大提高MySQL的查询性能。

 

4、分离关联查询

在上面说了如何使用join语句提高性能。但我们想进一步优化可以将join语句分离。这会提高性能。这是因为:

  • 让缓存效率更高。可能某一个分解的语句已经有缓存了;
  • 可以减少冗余数据的查询;
  • 可以减少锁的竞争。

5、count

尽量使用count(*),因为这样它就不会关照具体的某一个列,而直接统计行数。但假如你count(column),那性能就会下降。

 

 

 

 

--------EOF---------
本文微信分享/扫码阅读