6explain显示SQL语句的执行计划

in命

IN|DESCRIBE| ||FORMAT= indeletefromchildwhere |id|select_type|table|type|possible_keys|key|key_len| |rows| |1| |child|ALL| |NULL|NULL|NULL |Usingwhere

format_name:{|} SELECT|DELETE 1rowinset(0.01

}

|INSERT| CE|UPDATE in输 含

mysql> inpartitionsselect*

JOIN

employeeswhereid:table:type:key:PRIMARYkey_len:4ref:constrows:11rowinset(0.00 in输出—— UNIONRESULTDEPENDENTSUBQUERY

SimpleSELECT(notusingUNIONorSecondorlaterSELECTstatementinaSecondorlaterSELECTstatementinaUNION,dependentonouterResultofaFirstSELECTinFirstSELECTinsubquery,dependentonouter

DerivedtableSELECT(subqueryinFROMAsubqueryforwhichtheresultcannotbecachedandmustbere-evaluatedforeachrowoftheouterqueryThesecondorlaterselectinaUNIONthatbelongstoanuncacheablesubquery(seeUNCACHEABLESUBQUERY) in输出——mysql> inselect*fromlineitemwherel_shipdate<='1995-12-31'unionselect*fromlineitemwherel_shipdate>='1997-01- |id|select_type| |partitions|type|possible_keys|key|key_len|ref| |filtered| | | |lineitem|

|ALL|i_l_shipdate|NULL| |NULL 50.00|Using | | | | |ALL|i_l_shipdate|NULL|NULL|NULL |50.00|Using |NULL|UNIONRESULT|<union1,2>| |ALL| |NULL| |NULL NULL NULL|Usingtemporary 3rowsinset,1warning(0.00mysql> inSELECT->(SELECTCOUNT(1)FROMdept_emp->WHEREt1.emp_no<=t2.emp_no)AS->FROMdept_emp |id| |table|type|possible_keys| |key_len|ref|rows| |1| | |index| |emp_no| |NULL|331570|Using |2|DEPENDENTSUBQUERY| |ALL|PRIMARY,emp_no|NULL| |NULL|331570|Rangecheckedforeachrecord(indexmap:0x3) 2rowsinset(0.03 in输出——mysql> INSELECT*FROMordersWHEREo_orderdateIN(SELECTMAX(l_shipdate)FROMlineitemGROUPBY(DATE_FORMAT(l_shipdate,'%Y%M')) |id|select_type|table|partitions|type|possible_keys| |key_len|ref|rows|filtered| |1|

|orders| |ALL|

| |NULL|NULL |100.00|Using |2|SUBQUERY|lineitem| |index|i_l_shipdate|i_l_shipdate| |NULL |100.00|Usingindex;Usingtemporary;Usingfilesort 2rowsinset,1warning(0.01mysql> INSELECT*FROM->WHEREp_partkey (SELECTl_partkeyFROM WHEREl_shipdateBETWEEN'1997-01-01'AND'1997-02-01'->ORDERBYp_retailpriceDESCLIMIT |id|select_type| |partitions|type| | |key_len| |rows|filtered| |1| | | |ALL| | |NULL| |196810|100.00|Usingwhere;Usingfilesort|1| |<subquery2>| |eq_ref| |<auto_key>| |dbt3_s1.part.p_partkey 1|100.00| |2|MATERIALIZED|lineitem| ||

|139172|100.00|Usingindex 3rowsinset,1warning(0.00 in输出——<unionM,NUNION>:mysql> INSELECT*FROM->WHEREp_partkey (SELECTl_partkeyFROM WHEREl_shipdateBETWEEN'1997-01-01'AND'1997-02-01'->ORDERBYp_retailpriceDESCLIMIT |id|select_type| |partitions|type| | |key_len| |rows|filtered| |1||1||

| | | | | | ||<subquery2>| |eq_ref| |<auto_key>|

|196810|100.00|Usingwhere;Usingfilesort|dbt3_s1.part.p_partkey 1|100.00||2|MATERIALIZED|lineitem indexcondition 3rowsinset,1warning(0.00

|range|i_l_shipdate,i_l_suppkey_partkey,i_l_partkey|i_l_shipdate| | |139172|100.00| 进行通过最多只有进行通过最多只有一录，如主键查or查询会使用到的型使用全文索引进行询只有一行记录表使用普通索引询 子查询的列是唯一引 in输出——Extra说Extra说UsingUsing UsingUsingindex 优化器使用IndexConditionPushdownUsingIndexforgroup UsingjoinUsingjoin 优化器需要使用joinUsing UsingUsing Using

s_suppkeyIN)

ANDp