mysql查询优化（1）

参考：http://dev.mysql.com/doc/refman/5.7/en/select-optimization.html

（1）查询语句的速度

优化查询语句时，主要考虑以下几点：

• 对于select … where 语句，首先where语句中的列是否有索引。
  为了避免浪费磁盘空间，索引集需要尽可能地小，而且满足大多数查询；
  对于多表关联查询，索引非常重要。可以使用explain执行计划，查看select语句是否用到索引。

• 隔离和优化查询的任何部分。
  比如函数调用，可能花掉大量时间。根据查询语句的结构，一个函数可能是被结果集中的每行被调用，也可能是被表中的每一行被调用，后者会造成严重的效率低下；

• 最小化全表扫描查询的数量，尤其是对大表的查询；

• 通过周期性地执行ANALYZE_TABLE语句，确保表的统计数据是最新的，这样优化器就可以获取到需要的信息，并构造有效的执行计划；

• 利用针对表特定存储引擎的优化技术、索引技术、参数配置，进行查询优化，如InnoDB和MyISAM的指导文档；

• 对于InnoDB表，可以使用只读事务，来优化单查询事务；

• 避免把查询转换成很难理解的形式，尤其是优化器也会自动做相同的转换；

• 通过执行计划研究查询的内部细节，并调整索引语句、join语句等；

• 调整MySQL用作缓存的内存区域的大小和属性。有效地使用InnoDB缓冲池、MyISAM key缓存、MySQL查询缓存，重复地查询会运行更快，因为结果是从内存中获取。

• 即使对于使用缓存快速运行的查询，仍然需要进一步优化，使它们需要更少的缓存，从而使得你的应用更具有可扩展性。可扩展性意味着你的应用可以处理更多的并发用户、更大的请求等等，而没有性能大幅地下降；

• 处理锁问题。你的查询速度可能会受到同一时间访问表的其他会话的影响；

（2）MySQL是如何优化where子句的

• 移除不必要的括号

   ((a AND b) AND c OR (((a AND b) AND (c AND d))))
-> (a AND b AND c) OR (a AND b AND c AND d)

• 常量合并

   (a<b AND b=c) AND a=5
-> b>5 AND b=c AND a=5

• 由于常量合并，导致的常量移除

   (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
-> B=5 OR B=6

• 索引用到的常量表达式，只被评估一次

• 单表count(*)，如果没有where语句，并且是MyISAM或内存表，是直接从表信息读取的。

• 无效常量表达式的早期检测。MySQL快速检测出某些语句是不可能的，并且不反回行数。

• 如果没有使用group by语句或聚集函数（count(),min()等），having语句会和where语句合并。

• 对于join中的每个表，mysql会构造出一个比较简单的where子句，并且尽可能快地跳过行数。

• 查询时，常量表会在其他表之前被读取。常量表包括以下几种表：
  （1）. 空表或只有一行数据的表；
  （2）. where语句中使用了主键或唯一索引的表，在where语句中，主键和唯一索引都是和常量作比较的，并且非空；
  例如，下面语句中涉及的表，都是常量表：

SELECT * FROM t WHERE primary_key=1;
SELECT * FROM t1,t2
  WHERE t1.primary_key=1 AND t2.primary_key=t1.id;

• 对于连接中的表，最好的连接组合是通过尝试所有可能后，发现的。如果order by语句中的列和group by语句中的列来自同一张表，这张表在关联时，就会被提升到第一位；

• 如果order by语句和group by语句包含的列不一样，会产生临时表；如果order by和group by包含的列，不是来自join队列中的第一张表，也会产生临时表；

  执行计划中出现临时表时，可以试着调整order by,group by中的列。
  样例参考：
    (1). MySQL语句调优：GROUP BY ORDER BY 语句中出现USING TEMPORARY
    (2). 查看产生临时表的调优

• 如果使用SQL_RESULT_OPTION选项，MySQL使用in-memory临时表；

• 每个表索引都会被查询，最好的索引会被使用，除非优化器认为全表扫描更有效。

    之前的版本，当最好的索引扫描行数超过30%时，优化器会使用全表扫描，但现在一个固定的比例，不再能决定使用全表扫描还是索引。
    现在优化器更复杂了，它基于其他因素做决定，比如：表大小、行数、IO块大小。

• 某些查询可以从索引中返回数据（覆盖索引），无需再查找数据文件。

• 每行数据输出前，不满足having的行，会被跳过。