在MySQL中,优化是十分重要的一个过程,今天主要了解其中的一个小部分:
性能分析之Explain:
使用Explain关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的,从而分析查询语句或是表结构的性能瓶颈。
用法:Explain SQL语句
就能够查看对于该语句的包含的各种信息:
以上,是执行计划中所囊括的10个字段信息。
对于执行Explain能够了解以下信息:
- 表的读取顺序;
- 数据读取操作的操作类型;
- 哪些索引可以使用;
- 哪些索引被实际使用;
- 表之间的使用;
- 每张表有多少行被优化器查询。
以上信息均是从字段中可以得到,所以接下来我们着重了解各字段的含义。
1、id :select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序。
包含以下三种情况:
a、id相同,说明执行顺序由上至下:举例如下
执行语句:explain select * from goods, category;
b、id不同, 如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
执行语句:explain select * from goods where cat_id in (select cat_id from category);
c、id相同不同,同时存在
执行语句:explain select * from goods, member where cat_id in (select cat_id from category);
此时,id的序号越大的越先被执行,id相同的从上往下顺序执行。
2、select_type:指的是查询语句所属类型。
常见的类型有simple、primary、subquery、derived、union、union result 六种。
a、simple:简单的select查询,不包含union以及子查询等。
b、primary:查询中如果包含任何复杂的子部分,则最外层查询被标记为primary。
c、subquery:在select或where列表中包含了子查询。
d、derived:在from列表中包含的子查询被标记为derived(衍生)。
e、union:如果第二个select出现在union之后,则该表被标记为union;若union包含在from子句的子查询中,则外层select被标记为derived。
f、union result:联合查询结果,即两种union的合并
3、table:即显示该行数据所属的表名称。
4、type:显示查询使用了哪一种类型,这一字段的信息与性能优化息息相关。
访问类型主要有以下7种,效果从最好到最差顺序如下:
system > const > eq_ref > ref > range > index > all;一般来说,得保证查询至少达到range级别,最好达到ref级别。
a、system:表只有一行记录(等价于系统表),const类型的特例,一般不出现;
b、const:表示通过索引一次就找到了,常用于primary key或者unique索引,因为只匹配一行数据,所以很快。
举例如下: explain select * from goods where goods_id = 5;
c、eq_ref:唯一性索引,对于每个索引键表中只有一条记录与之匹配,常见于主键或唯一索引扫描。
举例如下:explain select * from goods, category where goods.cat_id = category.cat_id;
上式结果,可以这样理解,针对goods表的每一行数据,匹配cat_id,在category表中只有一条记录。
d、ref:非唯一性索引扫描,返回匹配某个单独值的所有行。
举例如下:explain select * from goods where cat_id = 4;(前提是先建立了索引)
e、range:只检索给定返回的行,使用一个索引来选择行。
举例如下:explain select * from goods where goods_id in (2,3);
f、index:遍历索引,与all的区别主要就是遍历的数量,因为索引文件比数据文件小。
举例如下: explain select goods_id from goods;
g、all:全表扫描
4、possible_key和key、key_len:possible_key指的是可能应用在这张表中的索引,但查询到的索引不一定被实际使用。而key指的是执行该语句实际上被使用的索引。key_len指的是索引中使用的字节数,显示的值为索引字段的最大可能长度,并非实际使用长度。
举例如下:explain select * from goods where goods_id = 5 or cat_id = 3;
5、ref:显示索引的哪一列呗使用了,可能的话是一个常数。哪些列或常量被勇于查找索引列上的值。
举例如下:explain select * from goods, category where goods.cat_id = category.cat_id and goods.shop_price > 2000;
6、rows:指的是表统计信息及索引选用的情况,估算找到所需记录所需要读取的行数。即被优化器查询的行数。
举例如下:explain select * from goods, category where goods.cat_id = category.cat_id and goods.shop_price > 2000;
7、extra:额外的一些非常重要的信息。
a、using filesort:文件内排序。使用一个外部的索引排序,而不是表内的索引顺序,无法利用索引完成的排序操作。这样会增加开销,是不希望看到的。
b、using temporary:新建了内部的临时表,MySQL排序时使用了临时表,最后再删去临时表,常见于order by 和group by查询。
c、using index:使用了索引,这是性能好的体现,使用了索引查询可以大大提高执行速度。
还有一些其他的额外信息,这里不再解释。
未完待续......