mysql explain介绍-优快云博客

原文地址 mysql explain介绍 作者：jordan

优化：

1思想的优化想出一种降低业务逻辑的实现方法。

2软件执行效率优化

环境优化：
1.如果order by 没有利用到索引，那么将会出现fileSort，如果sort_buffer不够大，fileSort过程则需要使用临时文件 ,fileSort优化，主要通过调整环境来达到，如下
2.设置参数，优化order by 时可能出现的file sort：
将sort_buffer_size = 1M read_rnd_buffer_size = 1M
修改为sort_buffer_size = 16M read_rnd_buffer_size = 16M
避免order by 过程进行fileSort排序过程临时文件的产生。从3秒->0.7秒左右

3.去掉distinct,因为distinct加order by,mysql将自动使用临时表
distinct的优化方式详见：http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html

4.修改jdbc的url，增加参数useServerPrepStmts=false，使得query cache生效，
这个参数就是让参数与sql连接成整一个字符串，调试对参数中的单引号做了转义，应该
不用担心sql注入攻击了。另外，是否会导致服务端对查询重复的编译而导致的性能下降就不清楚了.

explain是用来分析sql语句，帮助优化的一个命令。

explain的语法如下：

explain [extended] select … from … where …

如果使用了extended，那么在执行完explain语句后，可以使用show warnings语句查询相应的优化信息。

比如我们执行 select uid from user where uname=’scofield’ order by uid 执行结果会有

+—-+————-+——-+——-+——————-+———+———+——-+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+——————-+———+———+——-+——+——-+

这些东西。

id: SELECT的识别符，这是SELECT的查询序列号。

select_type: SELECT类型，有以下几种不同的类型
    (1).SIMPLE: 简单的SELECT（不使用UNION或子查询）
    (2).PRIMARY: 最外面的SELECT，如果我们使用UNION或子查询，第一个查询将会是这个类型
    (3).UNION: 使用UNION查询时，除第一个语句外的所有语句会返回这个类型
    (4).DEPENDENT UNION:　UNION中的第二个或后面的SELECT语句，取决于外面的查询。
    (5).UNION RESULT: UNION的结果。
    (6).SUBQUERY: 子查询中的第一个SELECT。
    (7).DEPENDENT SUBQUERY: 子查询中的第一个SELECT，取决于外面的查询。
    (8).DERIVED : 衍生表会返回这个类型。如：select * from (select * from jos_content) as A;。

其中 table 表示是哪个表的数据。

type : 表示表的连接类型，性能由好到差的类型类型为
(System(表中仅有一行，即常量表)，
const(单表中最多有一个匹配行)，
eq_ref(对于前面的每一行,在此表中只查询一条记录)，
ref(使用普通的索引)，
ref_or_null(和ref类似，但是条件中包含对于NULL查询),
index_merge(索引合并优化),
unique_subquery(in的后面是一个查询主键字段的子查询),
index_subquery(类似unique_subquery，主要是in的后面是查询非唯一索引字段的子查询),
range(单表中的范围查询),
index(对于当前的每一行，都通过查询索引来得到数据),
all(对于当前的每一行,都通过全表扫描来得到数据))

type比较重要。表示链接的类型。链接类型由好到坏的，依次是 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般情况，至少要达到 range 级别，最好是 ref 级别。否则可能会有性能问题。

possible_keys 是指可以应用到该表的索引，如果为NULL则没有。

key 是指用到的索引。

key_len 是索引的长度，在不影响查询精度的情况下，值越小越好。

ref 是指索引的那一列被使用了。一般会是个常数。

rows MYSQL认为必须检查的用来返回请求数据的行数。

extra 是指额外的信息。也是比较重要的。

但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢

(1).Distinct
一旦MYSQL找到了与行相联合匹配的行，就不再搜索了
(2).Not exists
MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了
(3).Range checked for each
Record（index map:#）
没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一
(4).Using filesort
看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
(5).Using index
列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候
(6).Using temporary
看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上
(7).Using where
使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题

如果为 where used 说明使用了where语句。如果 type为 all 或者 index ，一般会出现这样的结果。这样的问题，一般是查询需要改进。

在一般稍大的系统中，基本尽可能的减少join ，子查询等等。mysql就使用最简单的查询，这样效率最高。至于 join 等，可以放在应用层去解决。

3. 使用EXPLAIN分析低效SQL的执行计划。
在查询到效率低的SQL语句后，那我们可以使用explain或者DESC命令获取Myswl如何执行SELECT语句的信息，包括在Select语句执行过程中表如何连接和连接的顺序。
例如你想计数xxxx年公司的销售额，那么需要操作sales和comapny table，并对money字段进行sum操作。看看怎么使用explain：

explain select sum(moneys) from sales a company b where a.company_id = b.id and a.year=XXXX \G;(注意加上\G是为了更好的看)