【有书共读】《高性能MySQL》第三期《查询性能优化》

 查询性能低下的最基本的原因是访问的数据太多，对于低效的查询，可以从两方面来分析：

1 是否向数据库请求了不需要的数据

       查询不必要的列，解决办法是加上limit

      多表关联时返回全部列，应该只取需要的列

       总是取出全部的列：SELECT *

       重复查询需要的数据。较好的解决方案是使用数据缓存

2 mysql是否在扫描过多的记录

       、响应时间、扫描行数和返回的行数

        MySQL能够以三种方式应用Where条件，从好到坏依次是

1） 在索引中使用Where来过滤数据，这是在存储引擎层实现的

2） 使用了索引覆盖扫描（Extra列中Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果，这是在MySQL服务器层实现的，但是无需回表查询记录

3） 从数据表中返回数据，然后过滤不满足条件的记录，这是在MySQL服务器层实现的，MySQL需要先从数据库中读取记录然后过滤。

2 重构查询方式

确定一个复杂查询还是多个简单查询更加有效

分解关联查询 ：缓存效率更高，单个查询减少锁的竞争，查询效率提升，减少冗余查询记录，易实现哈希

3、查询执行的基础

过程如下图：

MySQL执行查询的过程：

（1）、客户端发送一条查询给服务器

（2）、服务器先检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果，否则进入下一个阶段。

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

（4）、将结果返回给客户端。

 4、查询优化器局限性

 关联子查询： IN()用于子查询性能特别糟，要么分解，进行exists改造。

 Union的限制：限制条件无法从外层到内层，可以将限制条件都放到内层，然后再合并

 索引合并优化：：多个独立索引自动合并成联合索引

等值传递：IN()列表过大，会使优化和执行变慢

并行执行：mysql无法利用多核来并行执行查询

哈希关联：mysql所有的引擎都是嵌套循环关联

松散索引扫描：5.0以后的版本支持，感兴趣可以自行了解下

 最大值和最小值优化：尽量减少扫描的行数

例如：

优化为：

在同一个表上查询和更新：mysql不允许对同一张表查询和更新

5、查询优化器提示hint：

   -- HIGH_PRORITY、LOW_PRIORITY：调整sql语句优先级

     -- DELAYED：日志系统-用于大量写入且客户端不需要等待单条I/O完成

     -- STRAIGHT_JOIN：减少优化器的搜索时间

     -- SQL_SMALL_RESULT、SQL_BIG_RESULT：告诉优化器对group by、distinct使用内存临时表/磁盘临时表

     -- SQL_BUFFER_RESULT：结果放在临时表并尽快释放，服务器需要更多内存

     -- SQL_CACHE、SQL_NO_CACHE：结果是否存在查询缓存

     -- FOR UPDATE、LOCK IN SHARE MODE：排它锁、共享锁

     -- USE INDEX、IGNORE INDEX、FORCE INDEX：使用或不使用哪些索引

6、优化特定类型的查询

简单优化： SELECT COUNT(*) FROM world where id>5;

  改为--->

              SELECT (SELECT COUNT(*) FROM world) - COUNT(*) FROM worldwhere id<=5;

         Explain查看扫描记录从4097下降到5了

  优化关联查询：

无特殊要求，仅在关联查询的第二个表中间索引

        关联查询中在GROUP BY、ORDER BY最好仅涉及一个表的列，这样mysql才能使用索引来优化这个过程

  优化子查询：子查询尽可能用关联查询替代

优化GROUP BY、DISTINCT：使用关联查询时仅使用表标识区分度高的分组效率更高

优化LIMIT分页：偏移量大时，limit分页会进行全表扫描,此时尽量考虑，利用索引覆盖扫描,然后在进行关联查询例如下面：

优化union查询：mysql总是通过临时表填充方式执行union,所以需要手动将where、limit、order by条件下推到子查询。

最后附上sql的语法顺序：

SELECT[DISTINCT] 、FROM、WHERE、GROUP BY、HAVING、UNION、ORDER BY

    FROM、WHERE、GROUP BY、HAVING、SELECT、DISTINCT、UNION、ORDER BY