记一次MySql执行计划(一)

                         答应自己的事就一定要做到

使用 Explain 优化查询

Explain 输出格式

扩展Explain 语法

 

Explain 语法

{EXPLAIN}  table (表名) 

EXPLAIN SQL语句;这里的SQL语句并不会真的执行;比如一个修改的，他只会分析这条Sql;并不会有修改的效果

 

使用效果(简单的)

EXPLAIN select * from position where id = 29;

就可以看到如下的效果;

type=const表示通过索引一次就找到了；

key=primary的话，表示使用了主键

type=all,表示为全表扫描

key=null表示没用到索引

type=ref，因为这时认为是多个匹配行，在联合查询中，一般为REF。

 

MYSQL中的组合索引

假设表有id,key1,key2,key3,把三者形成一个组合索引，则如：

where ke1y1 =

where key1 = 1  and key2 = 2

where key1 = 1 and key2 = 2 and key3 = 3

根据最左前缀原则，这些都是可以使用索引的，如from test where key1=1 order by key3，用explain分析的话，只用到了normal_key索引，但只对where子句起作用，而后面的order by需要排序。

 

MYSQL慢查询记录日志

在my.ini中：
long_query_time=1
log-slow-queries=d:\mysql5\logs\mysqlslow.log
把超过1秒的记录在慢查询日志中
可以用mysqlsla来分析之。也可以在mysqlreport中，有如
DMS分别分析了select ,update,insert,delete,replace等所占的百分比

 

Explain 使用说明 :

在上面的图中有操作和结果截图

id : select 查询的序列号

select_type : select查询的类型,主要是区别普通查询和联合查询，子查询之类的复杂查询

 1 : simple 查询中不包含子查询或者union 

 2 : 查询中若包含任何复杂的子部分,最外层查询侧被标记为 : PRIMARY

 3 : 在SELECT 和 WHERE列表中包含子查询,侧该子查询标记为 : SUBQUERY

 4 : 在 from 列表中包含的子查询被标记为 : DERIVED

 5 : 若在第二个SELECT 出现在UNION之后,测被标记为union,若union 包含在from中子句的查询中.外层的Select将被标记      DERIVED

 6 : 从UNION表获取结果的selecr被标记为 : UNION RESULT

 

table : 表示所用到的表

 

type : 联合查询所使用的类型;表示MySql在表中找到所需行的方式,又称为访问类型

type 显示的是访问类型，是较为重要的一个指标,结果值从好到坏的顺序依次是 : 

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > All ; 就一般的Sql来说的话,保证查询的效率至少达到range级别,最好能达到ref.

All : 全表扫描

index : 扫描全部索引树

range : 扫描部分索引,索引范围扫描,对索引的扫描开始某一个点,返回匹配值域，常见于between , < , > 等查询

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

eq_ref : 唯一行索引扫描,对于每个索引键，表中只有一条记录与之匹配。常见于主键活唯一索引扫描。

const.system : 但MySql对查询某部分进行优化，并转换为一个常量时，使用这些类型访问.如将主键置于where列表中,MySql就能将该查询转换为一个常量。system是const类型的特列,当查询的表只有一行的情况下,使用system.

 

Null : MySql 在优化分析过程中分解语句,执行时甚至不用访问表或者索引.

 

possible_key : 指出Mysql能使用那个索引在该表中找到行。查询涉及到的字段上若存在索引，侧该索引将被列出，但不一定被查询使用。如果是null的话，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段；或者检查字段不是适合索引。

 

Key : 显示Mysql实际决定使用的键。如果没有索引被选择，键就是NULL.

 

key_len : 显示MYSQL决定使用的键长度.表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。如果键是NULL，长度就是NULL。文档提示特别注意这个值可以得出一个多重主键里mysql实际使用了哪一部分。

注意 : key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

 

ref : 显示那个字段或者常数与key一起被使用.

 

rows : 这个数表示mysql要遍历多少数据才能得到，表示mysql根据表统计信息即及索引选用的情况，估算的找到所需的记录所需要读取的行数，在innodb上可能是不准确的。

 

Extra : 包含不适合在其他列中显示但十分重要的额外信息.

 Only index : 只用索引树中的信息检索出来的，比全表扫描要快。

 using where 是使用上了 where 限制。表示mysql服务器在存储引擎受到记录后进行"后过滤"(post-filter);如果查询未能使用索引,Using where 的作用只是提醒我们Mysql将用where子句来过滤结果集。

 impossiable where 表示用不着where,一般就是没查询出来结果。

 Using filesort : 当我们试图对一个没有索引的字段进行排序的时候，就是firesort。它跟文件没任何关系,实际上是内部的一个快速排序。

Using temporary : 使用firesort 和 temporary的话会很吃力，where 和 order by的索引经常无法兼顾，如果按照where来确定索引，那么order by时,就必然会引起Using filesort.这就是看是先过滤在排序划算,还是先排序在过滤划算.

 

MySql 执行计划的局限 : 

   EXPLAIN 不会告诉你关于触发器,存储过程的信息或者用户自定义函数对查询的影响情况。

   EXPLAIN 不考虑各种cache

   EXPLAIN 不能显示MYSQL在执行查询时所做的优化工作。

   部分统计信息是估算的,并非精确值

   EXPLAIN 只能解释SELECT 操作,其他的操作重写SELECT后查看.