MYSQL优化杂谈二,Query优化

Query 优化基本思路

  • 优化更需要优化的Query,什么语句更需要优化?
1.优化一个高并发Query比一个低并发的Query收益要大很多;
例子:
    比如一个一小时执行1w次,每次需耗20次IO;另外一个每小时执行20次,每次需要10000个IO;
    同样优化节省20000次IO,对于前一个来说每次优化时只需要降低2个IO值便可;
    但对于后者,平均每次需要降低1000个;相比较前者优化来得容易得多;

2.一个频繁执行的高并发Query的危险性比一个低并发的Query要大很多;
当一个低并发的Query走错执行计划,所带来的影响只是该Query的请求者的体验会变差,对整个系统的影响不会特别突出,至少还在可控范围;
(当然了,如果是使用了复杂join语句造成大量锁表操作就另当别论了~);
但是当一个高并发的Query走错了执行计划,可能带来灾难性的后果,很多时候连自救的机会都不给你就让整个系统Crash掉;
  • 定位优化对象的性能瓶颈;
优化要明确Query语句有什么问题,我为什么要优化它?
拿到需要优化的Query语句后,我们首先要判断这个Query的瓶颈到底是IO还是CPU?
到底是因为数据访问耗时太多,还是在数据运算(分组排序)?
  • 明确的优化目标;
做事情都要有目标,没有目的没完没了的优化也不行;
通常情况下,查询响应一般我会为自己设定查询优化响应时间来作为量化的目标;
比如,我最近在做的业务表优化,优化目标就是:百万级至至千万级数据优化单表聚合查询响应时间保持的200ms左右;
  • 从Explain入手;
Explain是用来获取一个Query在当前的数据库中的执行计划,但在查看Explain结果之前,作为Query优化人员,应该要有一个
清晰的目标执行计划‘

Query 优化基本原则:

  • 多使用profile;
Query Profiler是一个非常方便的Query诊断分析工具,通过该工具可以获取一条Query在整个执行过程中对中资源的消耗情况;

1.开启profiling参数:
  >set profiling=1;

2.执行Query:
  >select  count(*) from users;

3.获取系统中保存的所有Query的profile概要信息:
  >show profiles;

4.针对单个Query获取详细的profile信息,比如查看Query_ID为4的数据:
  >show profile cpu,block io for query 4;


  • 永远用小结果集驱动大的结果集;
mysql中的join只有一种实现方式:Nested Loop,即:嵌套循环来实现的;
驱动表的结果集越大,被驱动表的访问次数增加,IO总量及CPU运算次数也随即上升;
减少驱动表的结果集,从而减少IO总量及CPU运算总量
  • 尽可能在索引中完成排序;
  • 只取出自己需要的Columns;
减少Columns数量可以减少传输数据量,如果需要涉及排序,可以减少数据在排序区占用的内存空间
  • 仅仅使用最有效的过滤条件;
最有效的过滤条件可以减少索引所占用的存储空间;
  • 尽可能避免复杂的Join和子查询
Join语句:
mysql在并发性的处理上并不是太好,当并发量太高的时候,系统整体性能可能会急剧下降;
尤其是遇到一些较为复杂的Query的时候更是如此;
当出现复杂的join语句,就意味着需要锁定的资源也就多,所堵塞的其他线程也就多;
相反的,如果我们将比较复杂的Query语句拆成多个较为简单的Query语句分布执行,每次锁定的资源会少很多,
所堵塞的其他线程也要少一些;当然啦,整个查询的时间也会因此增长,这是一个取舍的过程;

子查询:
子查询通常很难得到一个很好的执行计划,很多时候命名有索引可以用,但是Query Optimizer就是不用;

对于复杂的Join和子查询的应用,明白其原理及可能造成的危害;在设计之时,有意识的规避及预估风险;
我也很反感为了优化而优化,为了吐槽而吐槽的行为;在曾经的团队里,笔者也遇到看到join语句就开始吐槽的同学;
毕竟优化本身就是一个系统性能,平衡决策的过程;

推荐阅读更多精彩内容