mysql explain语句查询索引效率

1.explain作用

exlain 执行结果显示了mysql 存储引擎如何使用索引来处理select语句，能够帮助我们写出效率更高的sql语句，发挥mysql那些被埋没的能力。其实update语句也可以使用explainlai查看其使用情况，需要做的就是将update语句改为select就可以了，后面的条件不变。因为update修改一条记录时也是要先找到这条记录的，故可以替换update为select来用explain查看寻找数据时使用索引的情况。

2.使用方式

在select语句前加上explain就可以了。比如 explain select * from user where uanme="dd";

explain [extended] select ... from ... where ...

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

3.使用实例

mysql> explain select log_id,login_time from stat.stat_user_login_log order by login_time;
+----+-------------+---------------------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table               | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----+-------------+---------------------+-------+---------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | stat_user_login_log | index | NULL          | login_time | 4       | NULL |   32 | Using index |
+----+-------------+---------------------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)

各行的意思如下：

id — 所选定的执行计划中的查询的序列号；
select_type—所使用的查询类型
table －显示此行数据属于哪张表；
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－实际使用的索引，如为null，表示没有用到索引；
key_len－索引的长度，在不损失精确度的情况下，越短越好；
ref－显示索引的哪一列被使用了，如果可能的话，是个常数；列出的是通过常量（const），还是某个表的某个字段（join）来过滤（通过key）
rows－返回请求数据的行数，即通过系统收集到的统计信息估算出来的结果集条数
extra－关于mysql如何解析查询的额外信息

select_type的描述：

simple: 简单的 select （没有使用 union或子查询）
primary: 最外层的 select。
union: 第二层，在select 之后使用了 union。
dependent union: union 语句中的第二个select，依赖于外部子查询
subquery: 子查询中的第一个 select
dependent subquery: 子查询中的第一个 subquery依赖于外部的子查询
derived: 派生表 select（from子句中的子查询）

type的描述：

system:表只有一行记录（等于系统表）。这是 const表连接类型的一个特例。
const:表中最多只有一行匹配的记录，它在查询一开始的时候就会被读取出来。由于只有一行记录，在余下的优化程序里该行记录的字段值可以被当作是一个常数。const表查询起来非常快，因为只要读取一次！const 用于在和 primary key 或unique 索引中有固定值比较的情形。下面的几个查询中，tbl_name 就是 c表了：

select * from tbl_name where primary_key=1;

　　　select * from tbl_namewhere primary_key_part1=1 and primary_key_part2=2;

eq_ref:从该表中会有一行记录被读取出来以和从前一个表中读取出来的记录做联合。与const类型不同的是，这是最好的连接类型。它用在索引所有部分都用于做连接并且这个索引是一个primary key 或 unique 类型。eq_ref可以用于在进行"="做比较时检索字段。比较的值可以是固定值或者是表达式，表达示中可以使用表里的字段，它们在读表之前已经准备好了。以下的几个例子中，mysql使用了eq_ref 连接来处理 ref_table：

　　select * from ref_table,other_table whereref_table.key_column=other_table.column;

　　select * fromref_table,other_table whereref_table.key_column_part1=other_table.column and ref_table.key_column_part2=1;

ref: 该表中所有符合检索值的记录都会被取出来和从上一个表中取出来的记录作联合。ref用于连接程序使用键的最左前缀或者是该键不是 primary key 或 unique索引（换句话说，就是连接程序无法根据键值只取得一条记录）的情况。当根据键值只查询到少数几条匹配的记录时，这就是一个不错的连接类型。 ref还可以用于检索字段使用 =操作符来比较的时候。以下的几个例子中，mysql将使用 ref 来处理ref_table：

　　select * from ref_table where key_column=expr; select * fromref_table,other_table whereref_table.key_column=other_table.column;

　　select * fromref_table,other_table whereref_table.key_column_part1=other_table.column andref_table.key_column_part2=1;

ref_or_null: 这种连接类型类似 ref，不同的是mysql会在检索的时候额外的搜索包含null 值的记录。这种连接类型的优化是从mysql4.1.1开始的，它经常用于子查询。在以下的例子中，mysql使用ref_or_null 类型来处理 ref_table：

　　select * from ref_table where key_column=expr or key_column is null;

unique_subquery: 这种类型用例如一下形式的 in 子查询来替换 ref：value in (select primary_key from single_table where some_expr)
unique_subquery: 只是用来完全替换子查询的索引查找函数效率更高了。
index_subquery: 这种连接类型类似 unique_subquery。它用子查询来代替in，不过它用于在子查询中没有唯一索引的情况下，例如以下形式：value in (select key_column from single_table where some_expr)
range: 只有在给定范围的记录才会被取出来，利用索引来取得一条记录。key字段表示使用了哪个索引。key_len字段包括了使用的键的最长部分。这种类型时 ref 字段值是 null。range用于将某个字段和一个定植用以下任何操作符比较时 =, <>, >,>=, <, <=, is null, <=>, between, 或 in：

　　select * from tbl_name where key_column = 10; select * fromtbl_name where key_column between 10 and 20; select * from tbl_namewhere key_column in (10,20,30); select * from tbl_name wherekey_part1= 10 and key_part2 in (10,20,30);

index: 连接类型跟 all 一样，不同的是它只扫描索引树。它通常会比 all快点，因为索引文件通常比数据文件小。mysql在查询的字段知识单独的索引的一部分的情况下使用这种连接类型。
all: 将对该表做全部扫描以和从前一个表中取得的记录作联合。这时候如果第一个表没有被标识为const的话就不大好了，在其他情况下通常是非常糟糕的。正常地，可以通过增加索引使得能从表中更快的取得记录以避免all。

possible_keys描述

　　注意，这个字段完全独立于explain 显示的表顺序。这就意味着 possible_keys里面所包含的索引可能在实际的使用中没用到。如果这个字段的值是null，就表示没有索引被用到。这种情况下，就可以检查 where子句中哪些字段那些字段适合增加索引以提高查询的性能。就这样，创建一下索引，然后再用explain 检查一下。详细的查看章节"14.2.2 alter tablesyntax"。想看表都有什么索引，可以通过 show index from tbl_name来看。

key 描述

　　key字段显示了mysql实际上要用的索引。当没有任何索引被用到的时候，这个字段的值就是null。想要让mysql强行使用或者忽略在 possible_keys字段中的索引列表，可以在查询语句中使用关键字force index, use index,或 ignore index。如果是 myisam 和 bdb 类型表，可以使用 analyzetable 来帮助分析使用使用哪个索引更好。如果是 myisam类型表，运行命令 myisamchk --analyze也是一样的效果。详细的可以查看章节"14.5.2.1 analyze tablesyntax"和"5.7.2 table maintenance and crash recovery"。

extra行的描述：

distinct－mysql找到了域行联合匹配的行，就不再搜索了；
not exists－mysql优化了left join，一旦找到了匹配left join的行，就不再搜索了；
range checked for each－没找到理想的索引，一次对于从前面表中来的每一个行组合；
record(index map: #)－检查使用哪个索引，并用它从表中返回行，这是使用索引最慢的一种；
using filesort－看到这个就需要优化查询了，mysql需要额外的步骤来发现如何对返回的行排序。他根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。
using index－列数据是从单单使用了索引中的信息而没有读取实际行的表返回的，这发生在对表的全部的请求列都是同一个索引时；
using temporary－看到这个就需要优化查询了，mysql需要创建一个临时表来查询存储结果，这通常发生在多不同的列表进行order by时，而不是group by；
where used－使用了where从句来限制哪些行将与下一张表匹配或是返回给用户。如不想返回表中用的全部行，并连接类型是all或index，这就会发生，也可能是查询有问题。

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

使用explain分析语句时，主要关注索引相关及rows。若rows数据很大，页面查询出来后将会很慢。

例1：单表

建emp表并插入部分数据

CREATE TABLE emp(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
deptno INTEGER,
col3 INTEGER,
col4 INTEGER
);
INSERT INTO emp VALUES(1,1,2,3);
INSERT INTO emp VALUES(2,3,4,5);
INSERT INTO emp VALUES(3,4,5,6);

分析查询语句：

EXPLAIN SELECT id FROM emp WHERE deptno=2 AND col3>1 ORDER BY col4 DESC LIMIT 1；

type为all类型，using filesort，此时需要优化。

优化措施1：建立联合索引

ALTER TABLE emp ADD INDEX index_1(deptno,col3,col4);

查看优化结果:

type类型变为了ref，但是仍使用using filesort，建立了索引，但似乎是没起作用？

原因是：按照BTree索引的工作原理，先排序detpno，如果遇到相同的deptno就会对col3进行排序，如果遇到相同col3就会对col4排序。上边col3>1，则索引无法对后面的col4进行排序。

优化措施2：调整联合索引

drop index index_1 on emp;

ALTER TABLE emp ADD INDEX index_1(deptno,col4);

查看优化结果:

例2：多表的

烂sql不仅直接影响sql的响应时间，更影响db的性能，导致其它正常的sql响应时间变长。如何写好sql，学会看执行计划至关重要。下面我简单讲讲mysql的执行计划，只列出了一些常见的情况，希望对大家有所帮助。

测试表结构：

CREATE TABLE `t1` (

`c1` int(11) NOT NULL DEFAULT '0',

`c2` varchar(128) DEFAULT NULL,

`c3` varchar(64) DEFAULT NULL,

`c4` int(11) DEFAULT NULL,

PRIMARY KEY (`c1`),

KEY `ind_c2` (`c2`),

KEY `ind_c4` (`c4`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `t2` (

`c1` int(11) NOT NULL DEFAULT '0',

`c2` varchar(128) DEFAULT NULL,

`c3` varchar(64) DEFAULT NULL,

`c4` int(11) DEFAULT NULL,

PRIMARY KEY (`c1`),

KEY `ind_c2` (`c2`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `t3` (

`c1` int(11) NOT NULL DEFAULT '0',

`c2` varchar(128) DEFAULT NULL,

`c3` varchar(64) DEFAULT NULL,

`c4` int(11) DEFAULT NULL,

PRIMARY KEY (`c1`),

KEY `ind_c2` (`c2`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1.查看mysql执行计划

explain select ......

2.执行计划包含的信息

(1).id

含义，指示select字句或操作表的顺序。

eg1：id相同，执行顺序从上到下，下面的执行计划表示，先操作t1表，然后操作t2表，最后操作t3表。

eg2：若存在子查询，则子查询（内层查询）id大于父查询(外层查询)，先执行子查询。id越大，优先级越高。

(2).select_type

含义：select语句的类型

类型：

a.SIMPLE：查询中不包含子查询或者UNION

b.查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY

c.在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY

d.在FROM列表中包含的子查询被标记为：DERIVED（衍生）

e.若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在 FROM子句的子查询中，

外层SELECT将被标记为：DERIVED

f.从UNION表获取结果的SELECT被标记为：UNION RESULT

eg：

id为1的table显示<derived2>,表示结果来源于衍生表2。

id为2表示子查询，读取t3表

id为3类型为union，是union的第二个select，最先执行；

id为NULL的类型为union result, <union 1,3>表示id为1的操作和id为3的操作进行结果集合并。

执行顺序3->2->1->NULL

(3).type

含义：获取记录行采用的方式，亦即mysql的访问方式。

a.ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

b.index：Full Index Scan，index与ALL区别为index类型只遍历索引，索引一般比记录要小。

因为索引中含有c1，查询c1,c2可以通过索引扫描实现。

c.range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询

备注：range类型肯定是使用了索引扫描，否则type为ALL

d.ref：非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找

t2.c4为非唯一索引

e.eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

t2.c1为主键索引，主键索引也是唯一索引

f.const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，

MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system

(4).possible_keys

含义：指出MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

(5).key

含义：显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL

(6)key_len

含义：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度

(7)ref

含义：用于连接查询，表示具体某个表的某列被引用

(8)rows

含义：MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数,这个值是不准确的，只有参考意义。

(9)Extra

含义：显示一些辅助的额外信息

a.Using index,表示使用了索引

b.Using where，表示通过where条件过滤

c.Using temporary，表示使用了临时表，常见于分组和排序

d.Using filesort，表示无法使用索引排序，需要文件排序

eg1:t1.c3列没有索引

eg2：使用索引列t1.c2

参考：

http://www.cnblogs.com/cchust/p/3426927.html

http://www.cnitblog.com/aliyiyi08/archive/2008/09/09/48878.html

http://www.blogjava.net/persister/archive/2008/10/27/236813.html

http://www.cnblogs.com/xianqingzh/articles/2074462.html

http://dev.mysql.com/doc/refman/5.1/zh/optimization.html#explain

posted @ 2014-02-11 18:20 milkty 阅读(2720) 评论(0) 编辑收藏举报

刷新页面返回顶部

milkty

芝兰生于深谷，不以无人而不芳。君子修身养德，不以穷困而改志。

mysql explain语句查询索引效率

例1：单表

例2：多表的

公告