MySQL查询优化

　　1、基本原则：优化数据访问。查询性能低下的最基本原因就是访问了太多数据。

　　在MySQL中，最简单的开销指标有：执行时间、检查的行数、返回的行数。

 

　　2、mysql执行计划

　　下文中t1的表结构：

　　

　　explain select ...的输出：

　　

　　mysql> help explain

　　MySQL从优化器获得关于查询的执行计划的信息。即MySQL解释它将如何处理select语句，包括表是如何关联的、以怎样的顺序关联等信息。

　　1）id：SELECT标识符，表示查询中执行select子句或操作表的顺序。若id值相同，执行顺序由上至下；id值越大越先被执行。另外，它的值可以为NULL（如select_type是UNION RESULT时）。

　　

 　　2）select_type：SELECT类型。常见的几个值：

　　（1）SIMPLE：简单查询。不包含UNION或子查询。

　　（2）PRIMARY：（查询中包含复杂的子部分时）最外层的select。

　　（3）SUBQUERY：（where子句中）子查询的第一个select。

　　

　　（4）DEPENDENT SUBQUERY：（where子句中）子查询的第一个select，依赖于外查询。

　　

　　（5）DERIVED：在from子句中包含的子查询。

　　（6）UNION：（from子句中）UNION中第二个及第二个之后的select语句。第一个被标记为DERIVED。

　　

　　（7）UNION RESULT：UNION的结果。

 

　　3）table

 

　　4）type：连接类型，描述了表是如何被连接的。常见的几种类型如下，性能从最坏到最好：

　　（1）ALL：全表扫描。

　　（2）index：纯索引扫描。涉及一个概念：覆盖所有满足查询需要的数据的索引叫覆盖索引（即索引的字段覆盖了select的字段），通过它可以不用读取行数据。注意以下两个SQL的区别：

　　

　　

　　（3）range：使用索引扫描一定范围的行。如以下SQL：

　　

　　（4）ref：使用非唯一索引扫描匹配某个值的所有行。如以下SQL：

　　

　　（5）eq_ref：当一个索引的所有部分都用于join且该索引是PRIMARY KEY或UNIQUE NOT NULL索引时。

　　

　　（6）const、system：

　　const：表中最多只有一个匹配行，常用于PRIMARY KEY或UNIQUE索引。可被优化成常量。

　　system：表中只有一行数据，是const的特例。

　　

　　（7）NULL

　　

　　

　　5）possible_keys

 

　　6）key

 

　　7）key_len：表示使用到的索引的“长度”。可以根据这个值判断索引（尤其是多字段索引）的使用情况。

　　计算方法：utf8的每个字符占3个字节，而latin1只占1个字节；若字段允许为空，则需要再占1个字节，而NOT NULL的字段不需要；变长的字段需要再占2个字节，而不变长的不需要。如对于以下两个表：　　 

　　

　　

　　两个SQL用到的多字段索引(f1, f2, f3)中的f1部分。对于SQL1，key_len = 255 * 1 + 2（变长）；对于SQL2，key_len = 255 * 3 + 1（DEFAULT NULL）。

 

 

　　8）ref

 

　　9）rows：为得到查询结果，需要检查的行数。估算值，不一定准确。

 

　　10）Extra

 

　　3、关于索引

　　1）索引是在存储引擎层实现的，而不是服务器层。

　　2）索引类型

　　（1）B-Tree索引

　　（2）哈希索引

　　（3）空间（R-Tree）索引

　　3）多字段索引的“最左前缀原则”（MySQL只能高效地搜索索引的最左前缀）。t4的表结构见上文7）key_len。

　　“正确的”使用方式：f1、f1+f2、f1+f2+f3，此时使用的索引符合预期，分别是f1、f1+f2、f1+f2+f3。

　　“不正确的”使用方式：

　　

　　下面两例均只使用了f1：

　　

　　

 

 

 

　　参考资料：

　　《高性能MySQL》

　　http://www.cnblogs.com/ggjucheng/archive/2012/11/11/2765237.html

　　http://dev.mysql.com/doc/refman/5.1/en/using-explain.html

 

 

不断学习中。。。