首页 > 数据库 > MySQL >

MySQL性能优化与架构设计学习

2017-04-01

MySQL性能优化与架构设计学习。

MySQL性能优化与架构设计学习

8.2 Query 语句优化基本思路和原则

1、优化更需要优化的Query;

什么Query的优化能给系统整体带来更大的收益,这样的Query就更需要优化;一般来说,高并发低消耗(相对)的Query对整个系统的影响远比低并发高消耗的Query大;高并发低消耗的Query只要节省一小块资源,整个系统就将节省一大块资源;而低并发高消耗的Query要达到同等的效果,则需要该Query节省很大一块资源;且高并发低消耗的Query的危险性比低并发高消耗的Query大的多

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

在拿到一条需要优化的Query之后,我们首先需要判断这个Query的瓶颈到底是IO还是CPU。到底是因为在数据访问消耗了太多的时间,还是在数据的运算(如分组排序等)方面花费的了太多资源;

3、明确的优化目的;

1、要清楚数据库能承受的最大压力,能接受的最悲观情况;2、要清楚该Query在最理想的情况下要消耗多少资源,最糟糕又需要消耗多少资源;3、改Query实现的功能在系统中的重要地位得出该Query能占用的资源比例,了解该Query的效率给客户带来的体验影响有多大;

4、从Explain入手;

再优化任何一个目标SQL前,需要有一个清晰的目标执行计划,再借助Explain来检测结果是否满足自己预定的执行计划。对于不符合预期的执行计划需要不断分析Query的写法和数据库对象的信息,继续调整尝试,直至得到预期的结果;

5、多使用profile;

6、永远用小结果集驱动大的结果集;

mysql中join只有Nested Loop一种方式,也就是说Mysql的join都是通过嵌套循环来实现的。驱动结果集越大,所需循环的次数就越多,被驱动表的访问次数也增多,每次循环都不能避免CPU的消耗和IO资源的消耗;

nested Loop嵌套循环链接:驱动表中选一条记录,遍历被驱动表找匹配记录,然后驱动表中再选下一条记录,遍历被驱动表找匹配记录......

http://www.2cto.com/database/201301/186885.html nested loop原理

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

8、只取出自己需要的Columns;

在任何Query中只取自己需要的columns,尤其是在需要排序的情况下:1、取出的colum越多需要传送的数据包越大,需要更大的网络带宽和网络传输缓冲区;2、在需要排序的情况下有两种排序算法:老算法1、现将需要排序的字段和可以直接定位到相关行数据的指针信息取出,然后在我们设定的排序区(通过sort_buffer_size参数设定)中进行排序,完成之后再根据行指针信息取出所需的columns,这种方法需要访问两次数据;新算法2、一次性将所有的columns取出,在排序区进行排序后将排完序的数据作为结果集返回客户端;改进后的方法,减少了IO消耗,但是排序需要的内存增大很多,如果不需要的column太多,则势必浪费很多内存资源。(当需要排序的列的总大小加上order by列的大小超过了 max_length_for_sort_data定义的字节)则会选择老的算法,这样效率低;

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

where子句中并非条件越多越好,如果两个条件和一个条件得出的结果一致那么请用一个,并且请使用索引长度更短的那个,因为索引长度越长代表所用的索引空间更大,说明访问该索引需要读取的数据越多。例如:id 和 name 是一一对应的,id是int类型,name是varchar(20)的那么 where id=1 and name='刘' 或者where name='刘'都不如where id =1的访问效率高;

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

由于mysql内部资源锁争用,Query语句涉及到的表越多,锁定的资源越多,阻塞其他线程也越多,这将导致并发量高的时候系统整体性能急剧下降;可以考虑将复杂的Query拆分成相对简单的语句;当然这也可能导致网络传输方面资源消耗增加,所以这就应该根据每个系统的情况平衡取舍;

前四条是Query优化的基本思路;后面6条是优化中的基本原则。

相关文章
最新文章
热点推荐