mysql query优化原则

1、优化更需要优化的query

2、定位优化对象的性能瓶颈

3、明确优化目标

4、从Explain入手

5、多使用profile

6、永远小结果集驱动大结果集

7、尽可能在索引中完成排序

8、只取出租金需要的Columns

9、仅仅使用最有效的过滤条件

10、尽可能避免复杂的Join和子查询

永远小结果集驱动大结果集

在mysql中的join，只有Nested Loop一种join方式，也就是join都是通过嵌套循环来实现的。驱动结果集越大，所需要的循环就越多，那么被驱动表的访问次数自然就越多，而每次访问被驱动表，即使需要的逻辑IO很少，循环的次数多了，总量自然也就不能小，而且每次循环都不能避免的需要消耗cpu。

在优化JOIN QUERY的时候，最基本的原则就是“小结果集驱动大结果集”,通过这个原则来减少嵌套循环中的循环次数，达到减少IO总量以及cpu运算的次数。

只取出自己需要的Columns

任何时候在query中只取出自己需要的Columns，尤其在需要排序的query中。

对任何query，返回的数据都是需要通过网络数据包传会客户端，取出越多需要传送的数据里就越大。

如果是排序的query，影响就更大了。在mysql中有两种排序算法：一种是mysql4.1之前的老算法，实现方式是先将需要排序的字段和可以直接定位到相关数据的指针信息取出，然后在设定的排序区（通过sort_buffer_size设定）中进行排序，完成排序之后再次通过行指针信息取出所需要的Columns，也就是说这种算法需要访问两次数据。

第二种排序算法：一次性将所有需要的Columns全部取出，在排序区中进行排序后直接将数据返回给请求客户端。改行算法只需要访问一次数据，减少了大量的随机IO，提高了带有排序的query语句效率。但是，这种改进后的算法需要一次性取出并缓存的数据比第一种算法要多很多，如果将不需要的Columns取出，会极大的浪费排序过程中所需要的内存。

避免复杂join

mysql子在并发这一块做的并不是很好，并发量太高的时候，系统整体性能会极具下降，遇到复杂的query更是如此。这主要与mysql内部资源的争用锁定控制有关，如读写相斥等。对于INNODB存储引擎用于实现了行级锁性能还好点，如果用myisam存储引擎，并发高的时候，性能下降的厉害。所以，query语句所涉及到的表越多，所需要锁定的资源就越多。拆分复杂的query语句，分步执行，每次锁定的资源也就会少很多，所阻塞的其他线程也要少一些。在高并发系统中，牺牲单个query的短暂响应时间提高整体处理也是值得的。优化本身就是一门平衡与取舍的艺术，只有懂得取舍，平衡整体，才能让系统更优化