mysql-查询性能优化

不要取出全部列，取出全部列，会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的I/O、内存和CPU的消耗。应该严格禁止SELECT * 的写法。MySQL使用如下三种方式应用WHERE条件，从好到坏依次为：

　　1.1 在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。

　　1.2 使用索引覆盖扫描(Extra列中出现了Using index)来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的，但无须在回表查询记录。

　　1.3 从数据表中返回数据，然后过滤不满足条件的记录(在Extra列中出现Using Where)。这在MySQL服务器层完成，MySQL需要先从数据表独处记录然后过滤。

上面的例子说明了好的索引很重要。

如果发现查询需要扫描大量的数据但值返回少数的行，那么可以尝试下面的技巧优化它：

　　2.1 使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无须回表获取对应行就可以返回结果了。

　　2.2 改变库表结构。例如使用单独的汇总表。

　　2.3 重写这个复杂的查询，让MySQL优化器能够以更优化的方式执行这个查询。

重构查询：

　　1、一个复杂查询还是多个简单查询；

　　2、切分查询；

　　例如：

DELECT FROM message WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH);

改成：

rows_affected=0
do{
    rows_affected=do_query(
        "DELECT FROM message WHERE create created < DATE_SUB(NOW(),INTERVAL 3 MONTH) LIMIT 10000"
    )
} while rows_affected > 0

　　一次删除一万行数据一般来说比较高效，如果每次删除数据后，都暂停一会儿再做下一次删除，这样也可以将服务器上原本一次性的压力分散到一个很长的时间段中，可以大大的减少对服务器的影响

　　3、分解关联查询

查询优化处理

　　查询优化处理包括多个子阶段：解析SQL、预处理、优化SQL执行计划。这个过程中的任何错误(例如语法错误)都可能终止查询。

　　优化策略可以简单的分为两种：

　　1、静态优化：直接对解析树进行分析，并完成优化

　　2、动态优化：动态优化跟查询上下文有关，也可能和很多其他因素有关，例如WHERE条件中的取值、索引中条目对应的数据行数等。

　　MySQL能够处理的优化类型：

• 重新定义关联表的顺序。
• 将外连接转化成内连接。
• 使用等价变换规则。
• 优化COUNT()、MIN()和MAX()。
• 预估并转化为常数表达式
• 覆盖索引扫描
• 子查询优化
• 提前终止查询
• 等值传播
• 列表IN()的比较

　　怎么查看优化器重写后的SQL？

　　用到了explain extended和showwarnings

　　用ORDER BY排序，如果查询中有LIMIT的话，LIMT也会在排序之后应用，所以即使需要返回较少的数据，历史表和需要排序的数据量仍然会非常大。MySQL5.6在这里做了很多重要的改进。当只需要返回部分排序结果的时候，例如使用LIMIT子句，MySQL不再对所有的结果进行排序，而是根据实际情况，选择抛弃不满足条件的结果，然后再进行排序。

参考：

[1]Baron Schwartz等 著，宁海元等 译 ；《高性能MySQL》（第3版）； 电子工业出版社 ，2013