从MysqlEXPLAIN探寻数据库查询优化

1、从MysqlEXPLAIN探寻数据库查询优化作者：蘑菇日期：2010-07-2016:37:20 阅读:http: 语法1.EXPLAINtbl_name|SELECTselect_optionsEXPLAINtbl_name 和 DESCRIBEtbl_name 勺作用是一样的，用于显示表结构等信息。当我们在 select 语句前加上 EXPLAINS,Mysql 将告诉我们它是如何处理 select 语句的，提供表之间的联结方式、使用索引等有关信息。二.测试环境简单介绍为了节省创建表的时间，我用了 joomla 的文章表做测试，因为要演示优化过程,所以我事先删除了表里除主键之外的所有索引。

2、这里用到了三个表：mysqlexplainjos_content;+-+|Field|Type|Null| Key|Default|Extra|+-+-+|id|int(11)unsigned|NO|PRI|NULL|auto_increment|title|varchar(255)|NO|alias|varchar(255)|NO|title_alias|varchar(255)|NO|introtext|mediumtext|NO|NULL|fulltext|mediumtext|NO|NULL|state|tinyint(3)|NO|0|sectionid|int(11)unsigned

3、|NO|0|mask|int(11)unsigned |NO|0|catid|int(11)unsigned |NO|0|created|datetime|NO|0000-00-0000:00:00|created_by|int(11)unsigned |NO|0|created_by_alias|varchar(255)|NO|modified|datetime|NO|0000-00-0000:00:00|modified_by|int(11)unsigned |NO|0|checked_out|int(11)unsigned |NO|0|checked_out_time1I|datetim

4、e|NO|0000-00-0000:00:00|1|publish_up|datetime|NO|0000-00-0000:00:00|publish_down|datetime|NO|0000-00-0000:00:00|images|text|NO|NULL|urls|text|NO|NULL|attribs|text|NO|NULL|version|int(11)unsigned |NO|1|parentid|int(11)unsigned |NO|0|ordering|int(11)|NO|0|metakey|text|NO|NULL|metadesc|text|NO|NULL|acc

5、ess|int(11)unsigned |NO|0|hits|int(11)unsigned |NO|0|metadata|text|NO|NULL|+-+30rowsinset(0.00sec)mysqlselectcount(*)fromjos_content+|count(*)|+|46585|+1rowinset(0.00sec)mysqldescjos_categories;+|Field|Type|Null|Key|Default|Extra|+|id|int(11)|NO|PRI|NULL|auto_increment|parent_id|int(11)|NO|0|title|v

6、archar(255)|NO|name|varchar(255)|NO|alias|varchar(255)|NO|image|varchar(255)|NO|section|varchar(50)|NO|image_position|varchar(30)|NO|description|text|NO|NULL|published|tinyint(1)|NO|0|checked_out|int(11)unsigned|NO|0|checked_out_time|datetime|NO|0000-00-0000:00:00|editor|varchar(50)|YES|NULL|orderin

7、g|int(11)|NO0|access|0|tinyint(3)unsigned|NO|count|0|int(11)|NO|params|NULL|text|NO+17rowsinset(0.00sec)mysqlselectcount(*)fromjos_categories;+|count(*)|+|18|+1rowinset(0.00sec)+|Field|Type|NullDefault|Extra|+|id|int(11)|NONULL|auto_increment|title|varchar(255)|NO|name|varchar(255)|NO|alias|varchar(

8、255)|NO|image|text|NONULL|scope|varchar(50)|NO|image_position|varchar(30)|NO|description|text|NONULL|published|tinyint(1)|NO|0|checked_out|int(11)unsigned|NO|0|checked_out_time|datetime|NO|0000-00-0000:00:00|ordering|int(11)|NO|0|access|tinyint(3)unsigned|NO|0|count|int(11)|NO|0|params|text|NO|NULL|

9、+15rowsinset(0.00sec)mysqlselectcount(*)fromjos_sections+|count(*)|+|2|+1rowinset(0.00sec)简单说明：jos_sections 我们可以称它为大类表，jos_categories 为小类表，jos_content 为文章表，三个表的记录数分别为 2、18、46585。重要的字段基本上可以见名知义，只对几个联结字段说明一下：jos_categories.secion:这个字段是 jos_section 的主键 id,它表明了大类和小类的父子关系，至于 jos_categories.parent_id,我们可以

10、先无视它的存在。jos_content.sectionid:这个字段也是 jos_section 的主键 id,它表明了文章所后的大类。一jos_content.catid:这个字段是 jos_categories 的主键 id,它表明了文章所属向小类。至于为什么会用两个表来存储类别，而不用 parent_id 的方式实现无限级分类，我们也不必深究，因为这个就是 joomla 的结构，我们只是用来测试 EXPLAIN 而已。mysqldescjos_sections+Key|PRI|测试过程下面我们说明如何使用EXPLAINS看执行过程并对表结构和sql语句进行优化。在下面的测试过程中，我会用

11、一些看来根本就没有意义的 SQL句，只是为了演示 SQL语句结构和输出效果，其可用价值我们大可不必过于追究，在实际生产中还要以实际应用为准。mysqlexplainselect*fromjos_contentwhereid=16;+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+|1|SIMPLE|jos_content|const|PRIMARY|PRIMARY4|const|1|+1 rowinset(0.00sec)因为使用了 EXPLAIN 关键字，所以 Mysql 为我们列出了一些查询信息，它包

12、含以下列：id:SELECT 勺识别符，这是 SELECT 勺查询序列号。select_type:SELECT1 型，有以下几种不同的类型(1) .SIMPLE:简单的 SELECT(不使用 UNIONS 子查询)(2) .PRIMARY:最外面的 SELECT 如果我们使用 UNIONS 子查询，第一个查询将会是这个类型(3) .UNION:使用 UNIONS 询时，除第一个语句外的所有语句会返回这个类型(4) .DEPENDENTUNION:UNIONS 的第二个或后面的 SELECTS 旬，取决于外面的查询。(5) .UNIONRESULT:UNION 的结果。(6) .SUBQUERY

13、:子查询中的第一个 SELECT(7) .DEPENDENTSUBQUERYf 查询中的第一个 SELECT 取决于外面的查询。(8) .DERIVED:衍生表会返回这个类型。如：select*from(select*fromjos_content)asA;。table:输出引用的表。type:联接类型，从这个选项我们可以初步判断查询效率，有以下几种不同的类型(按从最佳到最坏排序)：(1) .system:表中仅有一行记录，这是 const 的一个特例。(2) .const:表中最多有一行符合查询条件，它在查询开始时被读取。因为只有一行，这行的列值可被优化器剩余部分认为是常数。const 表很

14、快，因为它们只被读取一次！(如上面的查询)(3) .eq_ref:对于每个来自于前面的表的行组合，从该表中读取一行。例如：select*fromA,BwhereA.id=B.id，如果 id 在 B 表中是 unique 或 primarykey,会返回这个类型。它是说对于 A 表中的每一行，在 B 表中读取符合记录的一行。除了 const 之外，这是最好的联接类型。(4) .ref:这个类型跟 eq_ref 类似，不同的是 eq_ref 能根据 unique 或主键在后面的表中选择出唯一的行，而不能确定唯一行，则根用这个类型。(5) .ref_or_null:该联接类型如同 ref，但是添加

15、了 MySQL 可以专门搜索包含 NULLfli 的行。在解决子查询中经常使用该联接类型的优化。(6) .index_merge:索引合并方法用于通过 range 扫描搜索行并将结果合成一个。合并会产生并集、交集或者正在进行的扫描的交集的并集。在 EXPLAIN 输出中，该方法表现为 type 列内的 index_merge。在这种情况下，key 列包含一列使用的索引，key_len 包含这些索引的最长的关键元素。(7) .unique_subquery:unique_subquery 是一个索引查找函数， 可以完全替换子查询，效率更高。 explainselect*fromjos_conte

16、ntwhereidin(selectidfromjos_categories);会使用这个类型。(8) .index_subquery:该联接类型类似于 unique_subquery。可以替换 IN子查询，但只适合子查询中的非唯一索引。(9) .range:只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引。key_len 包含所使用索引的最长关键元素。在该类型中 ref 列为 NULL 当使用=、=、=、ISNULL=、BETWEEN 者 IN 操作符，用常量比较关键字列时，可以使用这个类型。(10) .index:这与 ALL 相同，除了只有索引树被扫描。这通常比 A

17、LL 快，因为索引文件通常比数据文件小。(11) .all:对于每个来自于先前的表的行组合，将要做一个完整的表扫描。如果表格是第一个没标记 const 的表，效果不是很好，并且在所有的其他情况下很差。你可以通过增加更多的索引来避免 ALL.,使得行能从早先的表中基于常数值或列值被检索出来。possible_keys:possible_keys 列指出 MySQ 能使用哪个索引在该表中找到行。注意，该列完全独立于 EXPLAIN 输出所示的表的次序。这意味着在 possible_keys 中的某些键实际上不能按生成的表次序使用。如果该列是 NULL 则没有相关的索引。在这种情况下，可以通过检查

18、WHERE 旬看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用 EXPLAIN 检查查询。key:key 列显示 MySQ 国际决定使用的键(索引)。如果没有选择索引，键是 NULL 要想强制 MySQ 使用或忽视 possible_keys 列中的索弓 I,在查询中使用 FORCBNDEXUS 日NDEXlE 者 IGNORENDEX 对于 MyISAM 和 BDB 表，运行 ANALYZETABLE 可以帮助优化器选择更好的索引。对于 MyISAM 表，可以使用 myisamchk-analyze。key_len:此列显示 MySQ 映定使用的键

19、长度。如果键是 NULL 则长度为 NULL 注意通过key_len 值我们可以确定 MySQ!实际使用一个多部关键字的几个部分。在不损失布确性的情况下，长度越短越好。ref:此列显示使用哪个列或常数与key一起从表中选择行rows:此列显示了MySQ认为它执行查询时必须检查的行数。Extra:该列包含 MySQ 解决查询的详细信息。(1) .Distinct:一旦 MYSQ 找到了与行相联合匹配的行，就不再搜索了。(2) .Notexists:MYSQL 优化了 LEFTJOIN,一旦它找到了匹配 LEFTJOIN 标准的行，就不再搜索了。(3) .Rangecheckedforeach:R

20、ecord(indexmap:#)没有找到理想的索引， 因此对于从前面表中来的每一个行组合，MYSQ 检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一。(4) .Usingfilesort:MYSQL 需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。(5) .Usingindex:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候。(6) .Usingtemporary:看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结

21、果，这通常发生在对不同的列集进行 ORDERY 上，而不是 GROUPBY。(7) .Usingwhere:使用了 WHERE 旬来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型 ALL 或 index,这就会发生，或者是查询有问题。上面的解释，不必全部看懂，有些可以一带而过，因为确实有些抽象，有些我也只是摘抄，但是下面我们用几个具体的实例来更加深入地理解一下。mysqlexplainselectAid,Atitle,B.titlefromjos_contentA,jos_categoriesBwhereA.catid=B.id;+- -+|id|selec

22、t_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+- -+|1|SIMPLE|A|ALL|NULL|NULL|NULL|NULL|46585|1|SIMPLE|B|eq_ref|PRIMARY|PRIMARY|4|joomla_test.A.catid|1|Usingwhere|+- -+2rowsinset(0.00sec)这个是我们经常使用的一种查询方式，对 B 表的联接类型使用了 eq_ref,索引使用了 PRIMARY 但是对于 A 表， 却没有使用任何索引， 这可能不是袤们想要的。查看以上 SQL 语句，我们可能会

23、想到，有必要给 A.catid 加个索引了。mysqlaltertablejos_contentaddindexidx_catid(catid);QueryOK46585rowsaffected(0.75sec)Records:46585Duplicates:0Warnings:0mysqlexplainselectAid,A.title,B.titlefromjos_contentA,jos_categoriesBwhereAcatid=B.id;+-+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+-

24、+|1|SIMPLE|A|ALL|idx_catid|NULL|NULL|NULL|46585|1|SIMPLE|B|eq_ref|PRIMARY|PRIMARY4|joomla_test.Acatid|1|Usingwhere|+-+2rowsinset(0.00sec)这样表 A 便使用了 idx_catid 索引下面我们做一次三个表的联合查询mysqlexplainselectAid,A.title,B.titlefromjos_contentA,jos_categoriesB,jos_sectionsCwhereAcatid=B.idandA.sectionid=C.id;+|id|s

25、elect_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+-+|1|SIMPLE|C|index|PRIMARY|PRIMARY4NULL|2|Usingindex|1|SIMPLE|A|ALL|idx_catid|NULL|NULL|NULL|46585|Usingwhere;Usingjoin|1|SIMPLE|B|eq_ref|PRIMARY|PRIMARY4|joomla_test.Acatid|1|Usingwhere|+-+3rowsinset(0.00sec)这里显示了 Mysql 先将 C 表读入查询，并使用

26、 PRIMAR 索引，然后联合 A 表进行查询，这时候 type 显示的是 ALL,可以用的索引有 idx_catid，但是实际没有用。原因非常明显，因为使用的连接条件是 A.sectionid=C.id,所以我们给 A.sectionid 加个索引先。mysqlaltertablejos_contentaddindexidx_section(sectionid);QueryOK46585rowsaffected(0.89sec)Records:46585Duplicates:0Warnings:0mysqlexplainselectAid,A.title,B.titlefromjos_con

27、tentA,jos_categoriesB,jos_sectionsCbufferwhereAcatid=B.idandA.sectionid=C.id;+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+|1|SIMPLE|C|index|PRIMARY|PRIMARY|4|NULL|2|Usingindex|1|SIMPLE|A|ref|idx_catid,idx_section|idx_section|4|joomla_test.C.id|23293|Usingwhere|1|SIMPLE|B|eq_

28、ref|PRIMARY|PRIMARY|4|joomla_test.Acatid|1|Usingwhere|+3rowsinset(0.00sec)这时候显示结果告诉我们，效果很明显，在连接 A 表时 type 变成了 ref,索引使用了idx_section,如果我们注意看后两列，对 A 表的查询结果后一次明显少了一半左右，而且没有用到 joinbuffer。这个表读入的顺序是 Mysql 优化器帮我们做的，可以得知，用记录数少的表做为基础表进行联合，将会得到更高的效率。对于上面的语句，我们换一种写法mysqlexplainselectA.id,A.title,B.titlefromjos_

29、contentAleftjoinjos_categoriesBonA.catid=B.idleftjoinjos_sectionsConA.sectionid=C.id;+-+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+-+|1|SIMPLE|A|ALL|NULL|NULL|NULL|NULL|46585|1|SIMPLE|B|eq_ref|PRIMARY|PRIMARY|4|joomla_test.A.catid|1|1|SIMPLE|C|eq_ref|PRIMARY|PRIMARY|4|jooml

30、a_test.A.sectionid|1|Usingindex|+-+3rowsinset(0.00sec)Mysql 读入表的顺序被改变了，这意味着，如果我们用 leftjoin 来做连接查询，Mysql 会按 SQL句中表出现的顺序读入，还有一个有变化的地方是联接 B 和 C的 type 都变成了 eq_ref,前边我们说过，这样说明 Mysql 可以找到唯一的行，这个效率是比 ref 要高的。再来看一个排序的例子:mysqlexplainselectA.id,Atitle,B.titlefromjos_contentAleftjoinjos_categoriesBonA.catid=B.

31、idleftjoinjos_sectionsConA.sectionid=C.idorderbyB.id;+-+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+-+46585|Usingtemporary;Using|PRIMARY|PRIMARY|4|PRIMARY|PRIMARY|4|joomla_test.A.sectionid|1|Usingindex|+|1|SIMPLE|A|ALL|NULL|NULL|NULL|NULL|filesort|1|SIMPLE|B|joomla_test.A.ca

32、tid|1|SIMPLE|C|eq_ref1|eq_ref-+-十3rowsinset(0.00sec)mysqlexplainselectA.id,Atitle,B.titlefromjos_contentAleftjoinjos_categoriesBonA.catid=B.idleftjoinjos_sectionsConA.sectionid=C.idorderbyA.id;+- -+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+- -+|1|SIMPLE|A|ALL|NULL|NULL|NUL

33、L|NULL|46585|Usingfilesort|1|SIMPLE|B|eq_ref|PRIMARY|PRIMARY|4|joomla_test.A.catid|1|1|SIMPLE|C|eq_ref|PRIMARY|PRIMARY|4|joomla_test.A.sectionid|1|Usingindex|+- -+对于上面两条语句，只是修改了一下排序字段，而第一个使用了 Usingtemporary,而第二个却没有。在日常的网站维护中，如果有 Usingtemporary 出现，说明需要做一些优化措施了。而为什么第一个用了临时表，而第二个没有用呢？因为如果有 ORDERBY句和一个不

34、同的 GROUPBY 旬，或者如果ORDERBYGROUPBY 的字段都来自其他的表而非连接顺序中的第一个表的话，就会创建一个临时表了。那么，对于上面例子中的第一条语句，我们需要对 jos_categories 的 id 进行排序，可以将 SQL 做如下改动：mysqlexplainselectB.id,B.title,Atitlefromjos_categoriesAleftjoinjos_contentBonA.id=B.catidleftjoinjos_sectionsConB.sectionid=C.idorderbyA.id;+-+|id|select_type|table|type

35、|possible_keys|key|key_len|ref|rows|Extra|+-+|1|SIMPLE|A|ALL|NULL|NULL|NULL |NULL|18|Usingfilesort|1|SIMPLE|B|ref|idx_catid|idx_catid4|joomla_test .A.id|3328|1|SIMPLE|C|eq_ref|PRIMARY|PRIMARY|4|joomla_test.B.sectionid|1|Usingindex|+3rowsinset(0.00sec)这样我们发现，不会再有 Usingtemporary 了，而且在查询 jos_content 时，

36、查询的记录明显有了数量级的降低，这是因为 jos_content 的 idx_catid 起了作用。所以结论是：尽量对第一个表的索引键进行排序，这样效率是高而。我们还会发现，在排序的语句中都出现了 Usingfilesort,字面意思可能会被理解为：使用文件进行排序或中文件中进行排序。实际上这是不正确的，这是一个让人产生误解的词语。当我们试图对一个没有索引的字段进行排序时，就是 filesofto 它跟文件没有任何关系，实际上是内部的一个快速排序。然而，当我们回过头来再看上面运行过的一个 SQL 的时候会有以下发现：mysqlexplainselectAid,A.title,B.titlefr

37、omjos_contentA,jos_categoriesB,jos_sectionsCwhereA.catid=B.idandA.sectionid=C.idorderbyC.id;+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+|1|SIMPLE|C|index|PRIMARY|PRIMARY|4|NULL|1|Usingindex|1|SIMPLE|A|ref|idx_catid,idx_section|idx_section|4|joomla_test.C.id|23293|Usingwhere

38、|1|SIMPLE|B|eq_ref|PRIMARY|PRIMARY|4|joomla_test.Acatid|1|Usingwhere|+3rowsinset(0.00sec)这是我们刚才运行过的一条语句，只是加了一个排序，而这条语句中 C 表的主键对排序起了作用，我们会发现 Usingfilesort 没有了。而尽管在上面的语句中也是对第一个表的主键进行排序，却没有得到想要的效果(第一个表的主键没有用到)，这是为什么呢？实际上以上运行过的所有 leftjoin 的语句中， 第一个表的索引都没有用到， 尽管对第一个表的主键进行了排序也无济于事。不免有些奇怪！于是我们继续测试了下一条 SQLm

39、ysqlexplainselectA.id,A.title,B.titlefromjos_contentAleftjoinjos_categoriesBonA.catid=B.idleftjoinjos_sectionsConA.sectionid=C.idwhereA.idexplainselectA.id,Atitle,B.titlefromjos_contentAleftjoinjos_categoriesBonA.catid=B.idleftjoinjos_sectionsConA.sectionid=C.idwhereA.id100orderbyA.id;+-+|id|select_

40、type|table|type|possible_keys|key|key_len|ref|rows|Extra|+|1|SIMPLE|A|range |PRIMARY|PRIMARY|4NULL|105|Usingwhere|1|SIMPLE|B|eq_ref|PRIMARY|PRIMARY|4joomla_test.A.catid|1|1|SIMPLE|C|eq_ref|PRIMARY|PRIMARY|4joomla_test.A.sectionid|1|Usingindex|+-+3rowsinset(0.00sec)这个结果表明：对 where 条件里涉及到的字段，Mysql 会使用索引进行搜索，而这个索引的使用也对排