?????? explain??????￡??????????????????????????????????????????????select ???и????????????????????????????Щ???????????????? analyze table?????????????????????????????????????????????????????????
????explain ????????м???????????? select???????????????????????Щ???????а???mysql??????е????ж????????г?????mysql???????????????single- sweep??multi-join?????????????????????ζ??mysql?????????ж??????????????????????в????????????????????????? ?в??????????????????е??????????????????????β?????????е???????????????????е???п????ж???????????????????????? ????????????????????????
??????mysql version 4.1?У?explain?????????????????????????????? union?????????????????????????????????????????2????Σ? id?? select_type?????????????mysql4.1??汾????????Щ??????
????explain???????м???????????????????????м?????????????????Σ?
????id
???????? select ????????????????? select????????????????
????select_type
????select ??????????????????????
????· simple: ???? select ???????? union????????
????· primary: ???? select??
????· union: ???????select ???????? union??
????· dependent union: union ????е?????select??????????????
????· subquery: ?????е????? select
????· dependent subquery: ?????е????? subquery??????????????
????· derived: ?????? select??from????е???????
????table
?????????????????
????type
????????????????????г?????????????????????????????????
????system:???????м??????????????????? const????????????????????
????const:???ж?????????????????????????????????????????????????м?????????μ????????????м?????????????????????? ?????const???????????????????????Σ?const ??????? primary key ??unique ???????й??????????Ρ?????????????У?tbl_name ?? c?????
????select * from tbl_name where primary_key=1;
????select * from tbl_namewhere primary_key_part1=1 and primary_key_part2=2;
????eq_ref:?????л?????м??????????????????????ж???????????????????const???????????????????????????????????????в? ????????????????????????????primary key ?? unique ?????
????eq_ref?????????????”=”????????????Ρ???????????????????????????????п????????????Σ???????????????????? ??????μ?????????У?mysql?????eq_ref ?????????? ref_table??
????select * from ref_table??other_table whereref_table.key_column=other_table.column;
????select * fromref_table??other_table whereref_table.key_column_part1=other_table.column andref_table.key_column_part2=1;
????ref: ????????з????????????????????????????????????????????????ref?????????????ü?????????????ü????? primary key ?? unique?????????仰????????????????????????????????????????
???????????????????????????????????????????????????????? ref??????????????????? =??????????????????μ?????????У?mysql????? ref ??????ref_table??
????select * from ref_table where key_column=expr;
????select * fromref_table??other_table whereref_table.key_column=other_table.column;
????select * fromref_table??other_table whereref_table.key_column_part1=other_table.column andref_table.key_column_part2=1;
????ref_or_null: ???????????????? ref?????????mysql????????????????????????null ??????????????????????????mysql4.1.1???????????????????????????μ??????У?mysql???ref_or_null ?????????? ref_table??
????select * from ref_table where key_column=expr or key_column is null;
????unique_subquery: ?????????????????????? in ???????滻 ref??
????value in (select primary_key from single_table where some_expr)
????unique_subquery: ???????????滻?????????????????Ч????????
????index_subquery: ???????????????? unique_subquery????????????????in?????????????????????????????????￡??????????????
????value in (select key_column from single_table where some_expr)
????range: ??????????Χ???????????????????????????????????key??α????????????????key_len?????????????????????????????? ref ?????? null??range??????????κ??????????????κβ????????? =?? <>?? >??>=?? <?? <=?? is null?? <=>?? between?? ?? in??
????select * from tbl_name where key_column = 10; select * fromtbl_name where key_column between 10 and 20; select * from tbl_namewhere key_column in (10??20??30); select * from tbl_name wherekey_part1= 10 and key_part2 in (10??20??30);
????index: ????????? all ?????????????????????????????????? all?????????????????????????С??mysql??????????????????????????????????????????????????
????all: ????????????????????????????????????????????????????????б?????const????????????????????????????????????????????????????????????????и??????ü???????all??
????possible_keys
????possible_keys?????? mysql?????????????????????????????????????????????explain ?????????????ζ?? possible_keys?????????????????????????????????????????????ε????null???????????????????
????????????￡??????? where???????Щ?????Щ??????????????????????????????????????????????????????explain ????????????????”14.2.2 alter tablesyntax”???????????????????????? show index from tbl_name??????
????key
????key????????mysql?????????????????????κ????????????????????ε????null???????mysql?????????????? possible_keys????е??????б?????????????????ù????force index?? use index???? ignore index??
????????? myisam ?? bdb ????????????? analyzetable ??????????????????????????á?????? myisam?????????????? myisamchk –analyze????????Ч????????????????”14.5.2.1 analyze tablesyntax”??”5.7.2 table maintenance and crash recovery”??
????key_len
????key_len ????????mysql??????????????? key ??ε??? null?????????????? null?????key_len????????????????????????mysql?????????????Щ??????
????ref
????ref ??????????Щ??λ?????????????? key??????в???????????
????rows
????rows ????????mysql???????????ü???????????