在分析查询性能时,考虑EXPLAIN关键字同样很管用。EXPLAIN关键字一般放在SELECT查询语句的前面,用于描述MySQL如何执行查询操作、以及MySQL成功返回结果集需要执行的行数。explain 可以帮助我们分析 select 语句,让我们知道查询效率低下的原因,从而改进我们查询,让查询优化器能够更好的工作。

一、MySQL 查询优化器是如何工作的
MySQL 查询优化器有几个目标,但是其中最主要的目标是尽可能地使用索引,并且使用最严格的索引来消除尽可能多的数据行。最终目标是提交 SELECT 语句查找数据行,而不是排除数据行。优化器试图排除数据行的原因在于它排除数据行的速度越快,那么找到与条件匹配的数据行也就越快。如果能够首先进行最严格的测试,查询就可以执行地更快。
EXPLAIN 的每个输出行提供一个表的相关信息,并且每个行包括下面的列:

说明
id MySQL Query Optimizer 选定的执行计划中查询的序列号。表示查询中执行 select 子句或操作表的顺序,id 值越大优先级越高,越先被执行。id 相同,执行顺序由上至下。
select_type 查询类型 说明
SIMPLE 简单的 select 查询,不使用 union 及子查询
PRIMARY 最外层的 select 查询
UNION UNION 中的第二个或随后的 select 查询,不 依赖于外部查询的结果集
DEPENDENT UNION UNION 中的第二个或随后的 select 查询,依 赖于外部查询的结果集
SUBQUERY 子查询中的第一个 select 查询,不依赖于外 部查询的结果集
DEPENDENT SUBQUERY 子查询中的第一个 select 查询,依赖于外部 查询的结果集
DERIVED 用于 from 子句里有子查询的情况。 MySQL 会 递归执行这些子查询, 把结果放在临时表里。
UNCACHEABLE SUBQUERY 结果集不能被缓存的子查询,必须重新为外 层查询的每一行进行评估。
UNCACHEABLE UNION UNION 中的第二个或随后的 select 查询,属 于不可缓存的子查询
说明
table 输出行所引用的表
type 重要的项,显示连接使用的类型,按最 优到最差的类型排序 说明
system 表仅有一行(=系统表)。这是 const 连接类型的一个特例。
const const 用于用常数值比较 PRIMARY KEY 时。当 查询的表仅有一行时,使用 System。
eq_ref const 用于用常数值比较 PRIMARY KEY 时。当 查询的表仅有一行时,使用 System。
ref 连接不能基于关键字选择单个行,可能查找 到多个符合条件的行。 叫做 ref 是因为索引要 跟某个参考值相比较。这个参考值或者是一 个常数,或者是来自一个表里的多表查询的 结果值。
ref_or_null 如同 ref, 但是 MySQL 必须在初次查找的结果 里找出 null 条目,然后进行二次查找。
index_merge 说明索引合并优化被使用了。
unique_subquery 在某些 IN 查询中使用此种类型,而不是常规的 ref:value IN (SELECT primary_key FROM single_table WHERE some_expr)
index_subquery 在 某 些 IN 查 询 中 使 用 此 种 类 型 , 与 unique_subquery 类似,但是查询的是非唯一 性索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
range 只检索给定范围的行,使用一个索引来选择 行。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可 以使用 range。
index 全表扫描,只是扫描表的时候按照索引次序 进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。
all 最坏的情况,从头到尾全表扫描。

 

说明
possible_keys 指出 MySQL 能在该表中使用哪些索引有助于 查询。如果为空,说明没有可用的索引。
说明
key MySQL 实际从 possible_key 选择使用的索引。 如果为 NULL,则没有使用索引。很少的情况 下,MYSQL 会选择优化不足的索引。这种情 况下,可以在 SELECT 语句中使用 USE INDEX (indexname)来强制使用一个索引或者用 IGNORE INDEX(indexname)来强制 MYSQL 忽略索引
说明
key_len 使用的索引的长度。在不损失精确性的情况 下,长度越短越好。
说明
ref 显示索引的哪一列被使用了
说明
rows MYSQL 认为必须检查的用来返回请求数据的行数
说明
rows MYSQL 认为必须检查的用来返回请求数据的行数

extra 中出现以下 2 项意味着 MYSQL 根本不能使用索引,效率会受到重大影响。应尽可能对此进行优化。

extra 项 说明
Using filesort 表示 MySQL 会对结果使用一个外部索引排序,而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。MySQL 中无法利用索引完成的排序操作称为“文件排序”
Using temporary 表示 MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

下面来举一个例子来说明下 explain 的用法。
先来一张表:

代码如下:

 

再插几条数据:

代码如下:

需求:
查询 category_id 为 1 且 comments 大于 1 的情况下,views 最多的 article_id。
先查查试试看:

代码如下:

看看部分输出结果:

代码如下:

很显然,type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。

嗯,那么最简单的解决方案就是加索引了。好,我们来试一试。查询的条件里即 where 之后共使用了 category_id,comments,views 三个字段。那么来一个联合索引是最简单的了。

代码如下:

结果有了一定好转,但仍然很糟糕:

代码如下:

 

type 变成了 range,这是可以忍受的。但是 extra 里使用 Using filesort 仍是无法接受的。但是我们已经建立了索引,为啥没用呢?这是因为按照 BTree 索引的工作原理,先排序 category_id,如果遇到相同的 category_id 则再排序 comments,如果遇到相同的 comments 则再排序 views。当 comments 字段在联合索引里处于中间位置时,因comments > 1 条件是一个范围值(所谓 range),MySQL 无法利用索引再对后面的 views 部分进行检索,即 range 类型查询字段后面的索引无效。
那么我们需要抛弃 comments,删除旧索引:

代码如下:

然后建立新索引:

代码如下:

接着再运行查询:

代码如下:

可以看到,type 变为了 ref,Extra 中的 Using filesort 也消失了,结果非常理想。
再来看一个多表查询的例子。
首先定义 3个表 class 和 room。

代码如下:

然后再分别插入大量数据。插入数据的php脚本:

代码如下:

然后来看一个左连接查询:

代码如下:

分析结果是:

代码如下:

显然第二个 ALL 是需要我们进行优化的。
建立个索引试试看:

代码如下:
代码如下:

可以看到第二行的 type 变为了 ref,rows 也变成了 1741*18,优化比较明显。这是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行,左边一定都有,所以右边是我们的关键点,一定需要建立索引。
删除旧索引:

代码如下:

建立新索引。

复制代码 代码如下:

 

结果

代码如下:

 

基本无变化。
然后来看一个右连接查询:

代码如下:

 

分析结果是:

代码如下:

 

优化较明显。这是因为 RIGHT JOIN 条件用于确定如何从左表搜索行,右边一定都有,所以左边是我们的关键点,一定需要建立索引。
删除旧索引:

代码如下:

建立新索引。

代码如下:

 

结果

代码如下:

 

基本无变化。

最后来看看 inner join 的情况:

代码如下:

 

结果:

代码如下:

 

删除旧索引:

代码如下:

 

结果

代码如下:

建立新索引。

代码如下:

结果

代码如下:

 

综上所述,inner join 和 left join 差不多,都需要优化右表。而 right join 需要优化左表。

我们再来看看三表查询的例子

添加一个新索引:

代码如下:

 

 

代码如下:
代码如下:

 

后 2 行的 type 都是 ref 且总 rows 优化很好,效果不错。

MySql 中的 explain 语法可以帮助我们改写查询,优化表的结构和索引的设置,从而最大地提高查询效率。当然,在大规模数据量时,索引的建立和维护的代价也是很高的,往往需要较长的时间和较大的空间,如果在不同的列组合上建立索引,空间的开销会更大。因此索引最好设置在需要经常查询的字段中。

32条回应:“MySQL查询优化之explain的深入解析”

  1. decided to keep tee pretty sure its authenticjust misprinted lots red on white tee made 2010 for lady gaga shoot

    adidas womens tiro training pantstim sepak bola jersey belanja online desain football jersey online murah womens football jerseysjovani 28219 a line knee length dresslavender solid baby polo t shirt

  2. Nike Air Max 2017 Hombre

    Nike City Loop Mujer Hombre

  3. short hair wigs for african american

    air jordan 1 blue black hoopjordanwomens red and black tennis shoeswomens under armour outlet sale 50 offnike slipper shoes

  4. rel贸gio dumont sz891454t rosa compre agora

    freya pure underwired moulded nursing bra ombre brown 1581 new select sizeholiday savings on womens 2 pack comfortflex fit cotton pullover braadidas stronger for it soft printed brathe top 6 ways branding is like a bra

  5. givenchy star backpack mini black

    chelsea blue kit polo training jersey 2018 2019 sky goalkids rapha team sky replica jerseyredcard debit and credit card holders will receive an extra 30 days to return nearly all items purchased with their redcard at target and targetcustom ravens jers…

  6. skinny capri jean

    adidas 2017 mens 3 stripe fleece golf beanie winter thermal hat mid grey sports outdoorsnew titleist cotton trucker baseball cap hat with adjustable backmlb american needle tokyo yomiuri giants black relaxed clip buckle hat cap sport amazon.co.uk sport…

  7. nfl houston texans 47 frost mvp camo adjustable hat

    nike donta foreman mens white elite jersey 27 nfl road houston texans vapor untouchablereggae boyz set to playway too early new york giants 53 man roster projection big blue viewofficial store mens nfl pro line jim brown brown cleveland browns retired…

  8. je veux trouver un beau manteau dhiver femme qui tient chaud pas cher ici parka femme columbia

    asos femme noir chaussures 脿 talon talon daim daim bout pointubasket femme compensee plateforme montante bottine plates cheville cuir chic 4.5cm boots hiver chelsea chaussures de sport bleu rose kaki 34 43caterpillar colorado honey bottes homme amazon.…

  9. score big savings on dollhouse womens size genesis plus denim

    evogues apparel plus size chiffon long gaucho chiffon pants olivesexy women pu leather body shaper lace up trainer waist cincher corset shapewearmissguided jackets coats plus size black military faux leather biker jacketchantelle irresistible strapless…

  10. best grey men reebok classic leather s shoes ol

    beach sport slides sneakers black slip on casual men mens sandals 2018 summer outdoor slippers designer flat shoes mesh fashionsandals dual sandal coral brand zaxyknotty champagne liliana sandalslyst fitflop gogh sport slide sandal in black for men

  11. bodyshaper by marialye bodyshaper by marialye

    converse x off white virgil abloh vulc black low topsvans chocolate torte true white old skool trainersbuy new balance team oversize holdall small bag online on zalorabrooks adrenaline gts 18 mens poobie naidoos

  12. 2017 new spring autumn leather jacket women long sleeve zipper brown faux leather coats ladies motorcycle biker jacket

    moncler childrens small jacket down jacket with patches uk official storeoversized plus size hand knit sweater tunic loose knit etsyibtom castle newborn infant baby girl princess party romper suits costume my 1st christmas black orangecherokee purple j…

  13. hello winter 41 off soma cotton and lace bralette

    vans shiny sequins old skool womens shoescanvas 2way tote baglyst converse all star hi platinum leather wedge sneaker in metallicadidas black flip flops slipper for men

  14. touca gorro masculino feminino infantil bebe personalizado bordado azul marinho mesclado

    ralph lauren churston pure white his trainersthe classic petite cornwall in whitelyst polo ralph lauren slim fit stretch chino pant in red for meninc international concepts i.n.c. womens reesie rhinestone bow evening sandals

  15. red suede block heeled sandals shoes prettylittlething

    nike womens air huarache run running sneakers from finish linefree knitting patterns cropped cardigan vest knitting patterncrocs womens classic navy clogadidas originals mens adilette slides us8.5 blue

  16. rel贸gios technos pulseira de a莽o montecarlo

    贸culos de sol oakley probation no mercado livre brasil贸culos gatinho colorido transparente cores vintage. carregando zoom贸culos de sol sabrina sato 7006. carregando zoomgafas ray ban en oferta

  17. manteau armani jeans homme

    evoke for you ds13 贸culos de sol a03 black matte gray lente 5oculos de sol nina ricci贸culos de sol isabella piu ponte dupla tartaruga caramelo compre agoraoticas diniz sindicato dos trabalhadores em telemarketing de

  18. KATRUS Sweter narzutka Pra膰 w 30*C Prawdziwy bestseller 45% we艂na 136737 SHYVEJU

    MAJE Giubbotto Pelle Avorio senza applicazioni modello monopetto senza tasche gancetti 41839665VN

  19. Sandals teva 6673 1

    maloka hotel boutique spa terraza de la habitaci贸n con piscina privadazapatillas nike hombre air zoom pegasus 34 33 correr running. cargando zoomdecoraciones para navidadimagenes de gorras personalizadas

  20. printemps 茅t茅 2018 ray ban 59 lunettes de

    topgrowth scarpe da corsa ragazzo ragazza shoes bambina sportive da ginnasticascarpe con il calzino adidasfila 44 scarpe da uomo scarpe scarpe e borseprezzo pazzesco autunno inverno nike jordan flight origin 3 bg

  21. unisa womens robine pw platform heels amazon.co.uk shoes bags

    youll turn heads in this hot christian siriano for payless heel it features a perforated vamp made of fine faux suede and side cut outs that add to theblock heel shoes fashion accessories heelsgold glitter sandalsjimmy choo edina 85 lyst

  22. silver dollar morocco eyeglasses silver dollar authorized retailer

    red and white off the shoulder dress whitenike down jacket pinkgucci pink slippersmens nike vapor team canada singlet

  23. vans sk8 low说道:

    vans sk8 low

    kids dresses baby clothing girls cotton frock castle princess print yellowzara mens jumpsuit boiler suit. really nice jumpsuit with depoplovers friends radiance romperyour israel packing list a season by season guide the family voyage

  24. tommy hilfiger sanda艂y 2016

    reebok classic biale vintedportfele marki michael michael kors kolekcja wiosna 2019 moda w womens healthnike portfel basic wallet czarnyramoneski. damskie kurtki sk贸rzane na wiosn臋. a偶 12 propozycji

  25. stussy stussy curtis tie dye quilted pant olive

    mens fisherman sandals sale up to 40 offworld wide market sneakers wedge sole heel casual shoes shoes shoesgucci tiger slides menpretty you pom pom afton slipper gift box

  26. amazing spring deals sam edelman packer booties

    brooks running shoes puregrit 3 mens fashionnike air force 1 foamposite bhm qsnike air vapormax 2018 women pink colorful new year dealsadidas adidas ultra boost uncaged price discount up 80 off

  27. gucci womens designer high heel sandals bloomingdales

    vans boys era hi mte nubuck grey skateboarding shoes 13 little kidvans era wingtip black red whitebuy custom adidas off71 discountedtyler the creator is banned from the uk tyler

  28. tanie teczki m臋skie

    艣liczna zielona torebka greenpoint w torebki na co dzie艅poznali艣my najlepsze sklepy internetowe wed艂ug klient贸wbuty olivier sklep internetowy z obuwiem sk贸rzanym buty m臋skiemichael kors harrison sm flight bag torba na rami臋 black. torby na laptopa dams…

  29. roman originals damen online exklusiv kontrast floral muster kleid blau gr枚脽e 48

    zapatillas deportivas de ni帽o tanjun psv nike de color negro con cierre de cordoneschancla adilette chancla adilettebolsa tommy hilfiger nas lojasropa deportiva online shop

  30. sand谩lia de prata

    chinelo havaianas brasil amarelo havaianascomprar piccadilly com descontosapato oxford conforto beira rio tratoradoasics fantasy infantil

  31. gilet long gris gilet torsad茅 femme

    discount jual nike free 5.0 original murahsales quality assurance nike free run 5.5 mens running shoes wool skin for whitenike free rn flyknit 2018 university red running shoes mens 12 new 942838 601nike huarache mens black and white calculated

  32. 35cm hermes graphite ultra violet clemence leather custom birkin

    gucci womens clothing online at mytheresagaga mens long sleeve denim solid shirt cotton casual double pocket shirt blue work slim fit shirt at amazon mens clothing storecrepe sleeveless blazer playsuit khakicalvin klein womens seductive comfort demi at…