查询性能优化的十条建议

##三个原则

• 1、单行访问是很慢的。最好读取的块中能尽可能包含多的所需要的行，使用索引可以创建位置引用以提升效率
• 2、按顺序访问范围数据是很快的。
  <原因一>顺序I/O不需要多次磁盘寻道，比随机I/O要快很多;
  <原因二>如果服务器按需要顺序读取数据，那么就不需要在进行额外的排序操作，并且group by 操作也无需要做排序和将行按照组进行聚合计算。
• 3、覆盖索引查询是很快的，因为无需回表查找行，避免了大量的单行访问。
  ###- (一）. 关于COUNT()
  ###- （二）. 关于UNION
  ###- （三）. 关于LIMIT分页
  ###- （四）. 关于关联查询
  ###- （五）. 关于GROUP BY和DISTINCT
  ###- （六）. 关于复杂查询和多个简单查询
  ###- （七）. 在同一个表上查询与更新
  ###- （八）. 关于查询缓存
  ###（一）、关于COUNT()

COUNT()作用有两个作用：其一就是统计某个列值的数量，它在统计列值时要求列值是非空的（不统计 NULL）。
					  其二就是统计结果集中的行数，当COUNT(表达式)中的表达式确认不可能为空时，实际上就是
					  统计行数。最简单清晰的用法就是COUNT(*)用来统计结果集中的数量。（注：mysql在解析
					  通配符‘*’的时候，不会扩展成所有的列，而是忽略所有的列直接统计行数）。
					  
COUNT()都需要扫描大量的行才能得到精确的结果，根据三个原则，访问大量的行，又不需要具体的列的信息，可以
通过覆盖索引来进行优化。

###（二）、关于UNION

MYSQL执行UNION操作的时候：创建并填充临时表的方式类实现。如果没有ALL关键字，MYSQL会给临时表加上
distinct选项，以此来对临时表的数据进行唯一性检查。这个代价非常高！注意：无论有没有ALL关键字,
MYSQL 都是使用临时表来存储结果。

###（三）、关于LIMIT分页
针对系统中需要进行分页操作的时候，通常采用LIMIT+偏移量的方式实现。但是对于偏移量非常大的时候(翻页靠后)例如 limit 10000，20这样的。这个时候mysql需要查询10020条数据，然后抛弃的前10000条。这样就非常不合适了。优化这种有两种：第一种在分页中限制分页的数量，第二种优化大偏移量的性能。
优化最简单的方案是使用索引覆盖扫描例如下面这个查询

可以改成

使用“延迟关联”来提高查询效率，它让mysql尽可能扫描少的页面，获取需要访问的记录后再根据关联回表查询需要的列。
LIMIT和OFFSET的问题，其实就是OFFSET的问题，它会导致mysql扫描大量不需要的数据然后再抛弃掉。有一种是根据单调递增的主键的特性，先记录上次取的数据的位置，作为下次扫描的起始位置。
例如：

	select id,status from orders  order by id asc limit 3000 ,30; 得到最大的id为117196

那下次查询就可以用

	select id,status from orders where id >117196  order by id asc limit 30;

###（四）、关于关联查询

关联查询是查询中的重中之重，以下只是几个原则
	1、确保on或者using 子句中的列上有索引，一般而言，只需要在关联顺序中的第二个表的相应的列上建立索引
	2、确保GROUP BY 和ORDER BY中的表达式只涉及到一个表的列。（若属于两个表，怎么使用
索引来优化这个查询过程）

###（五）、关于GROUP BY和DISTINCT

	在mysql中，当无法使用索引的时候，GROUP BY使用两种策略来完成：使用临时表或者文件排序来做分组，这两种
效率都可以优化。

	如果没有通过ORDER BY 子句显式的指定排序列，当查询使用GROUP BY子句的时候，结果集会自动按照分组的字段
不再进行文件排序，也可以直接在GROUP BY子句中直接使用DESC或者ASC,使分组的结果按照需要的方向排序。确保后面
的列属于同一个表。

###（六）、关于复杂查询和多个简单查询

	mysql在内存中检索数据时可以达到百万行数据，相对于将数据响应给客户端就慢的多了，在其他条件都相同的情况
下，使用尽可能少的查询是更好的，但是在应用设计的时候，一般采用将一个大的查询拆分为多个小的查询。这样有
很多的好处，例如可以多使用缓存，让缓存的效率更高；也可以减少锁的竞争；

	由于mysql的内部实现是“半双工”的，所以在同一件时间，要么服务器向客户端发送数据，要么客户端向服务器端
发送数据，这两个动作无法同时发生。所以在查询的时候，当客户端上送包含查询语句的数据包传给服务器，如果查
询太大，服务端会拒绝接收更多的数据。而服务端响应数据给客户端通常数据很多，由多个数据包组成。一旦服务端
响应客户端请求，客户端必须完整的接收整个返回结果，不能只取前面的几条结果就让服务器中断传输，所以这也是
在必要的时候一定要在查询中加上limit限制的原因。

###（七）、在同一个表上查询与更新
MySQL不允许对同一张表同时进行查询和更新操作。例如

可以使用inner join方式或者给子查询定义一个别名
或者
###（八）、关于查询缓存
MySQL查询缓存是默认开启的可以通过来查看是否开启

show variables like '%query_cache%'; 

• 在打开查询缓存的时候会对服务器的读和写带来额外的消耗：

	1、读查询在开始之前必须检查是否命中缓存
	
	2、如果这个命中缓存，那么直接返回结果，如果这个查询没有被缓存并且可以被缓存，那么当完成执行后，
	MySQL会将结果存入缓存，这是存缓存带来的消耗
		
	3、当对数据表中写入数据的时候，MySQL必须对对应表中的缓存进行失效操作，如果查询缓存非常大或者存储
	缓存的碎片很多，会带来很大的系统消耗（无论写缓存还是失效操作，都是使用排它锁，这个消耗自然是不小的）。

• 也不是所有的查询，MySQL都会缓存

   例如当查询语句中有一些不确定的数据时候，该查询就不会被缓存。如NOW()，CURRENT_DATE(),同时如果查询中
包含有用户自定义函数，存储函数，用户变量，临时表，mysql的系统表这些都不会被缓存。注意：某个查询结果会不会
被缓存跟某个查询会不会去缓存中检查是无关的。MySQL在检查缓存之前，通过一个大小写不敏感的检查来查看SQL语句
是不是以SEL开头。

• 查询缓存的优化：

批量写入只需要做一次缓存失效，所以比单条写入效率更好。
		控制缓存空间的大小，缓存空间太大的话，在失效操作的时候会导致服务器僵死
		对于写密集的应用来说，禁用查询缓存更可能会提高系统的性能
		由于对互斥信号量的竞争，有时候直接关闭查询缓存对读密集型的应用也有好处。

*** 通过以上可以看出，使用查询缓存是一个非常方便的缓存，对应用程序完全透明，也无须额外的编码。但是对于一些高并发压力的环境下，希望有更高的缓存效率，可以利用一些其他替代方案来替换，推荐方案是在客户端实现缓存，具体有使用redis或者MongoDB来实现缓存。***