EXPLAIN-Demystified

《EXPLAIN-Demystified》由会员上传分享，免费在线阅读，更多相关内容在学术论文-天天文库。

1、EXPLAINDemystifiedBaronSchwartzPerconaIncOutline•WhatisEXPLAIN?•HowMySQLexecutesqueries•HowtheexecutionplanbecomesEXPLAIN•Howtoreverse-engineerEXPLAIN•Hopelesslycomplexstuffyou'llneverremember•CooltricksEXPLAINDemystifiedWhatisEXPLAIN?•ShowsMySQL'sestimatedqueryp

2、lan•OnlyworksforSELECTqueriesmysql>explainselecttitlefromsakila.filmwherefilm_id=5G***************************1.row***************************id:1select_type:SIMPLEtable:filmtype:constpossible_keys:PRIMARYkey:PRIMARYkey_len:2ref:constrows:1Extra:EXPLAINDemystifi

3、edButfirst...•HowdoesMySQLexecutequeries?•SQL=>ParseTree=>ExecutionPlan•ExecutionerlooksatExecutionPlan•ExecutionermakescallstoStorageEngines•MySQLdoesNOTgeneratebyte-code!EXPLAINDemystifiedTheExecutionPlan•SELECT...sakila.filmJOINsakila.film_actorUSING(film_id)J

4、OINsakila.actorUSING(actor_id)OnewaytodoitTheMySQLWay(TM)JOINJOINJOINactorfilmfilm_actoractorfilmfilm_actorEXPLAINDemystifiedWhereEXPLAINcomesfromEXPLAINDemystifiedGeneratingEXPLAIN•MySQLactuallyexecutesthequery•ButateachJOIN,insteadofexecuting,itfillstheEXPLAINr

5、esultset•WhatisaJOIN?–EverythingisaJOIN,becauseMySQLalwaysusesnested-loops–Evenasingle-tableSELECToraUNIONorasubqueryEXPLAINDemystifiedTheColumnsinEXPLAIN•id:whichSELECTtherowbelongsto–IfonlyoneSELECTwithnosubqueryorUNION,theneverythingis1–Otherwise,generallynumb

6、eredsequentially–Simple/complextypes•simple:thereisonlyoneSELECTinthewholequery•3subtypesofcomplex:subquery,derived,union.–subquery:numberedaccordingtopositioninSQLtext–derived(subqueryintheFROMclause):executedasatemptable–union:rowsarespooledintoatemptable,thenr

7、eadoutwithaNULLidinarowthatsaysUNIONRESULTEXPLAINDemystifiedTheColumnsinEXPLAIN•simplesubquerymysql>EXPLAINSELECT(SELECT1FROMsakila.actorLIMIT1)FROMsakila.film;+----+-------------+-------+...

8、id

9、select_type

10、table

11、...+----+-------------+-------+...

12、1

13、PRIMARY

14、film

15、

16、...

17、2

18、SUBQUERY

19、actor

20、...+----+-------------+-------+...EXPLAINDemystifiedTheColumnsinEXPLAIN•derivedtablemysql>EXPLAINSELECTfilm_idFROM(SELECTfilm_idFROMsakila