???MySQL?е?Explain
???????????? ???????[ 2016/6/30 11:46:27 ] ????????????? MySQL
????extra
??????????????????mysql?????????????????????ε??????????????
????distinct:mysql????????????????????????????????????????????????????
????not exists:mysql?????????? left join??????????????????????????????????? left join?????????????????????????????????????????????????
????select * from t1 left join t2 on t1.id=t2.id where t2.id isnull;
??????? t2.id ????? not null??????????£?mysql???????? t1?????? t1.id ????? t2 ?в??????????? t2?????????????????????ζ?? t2.id ?????????null?????????? t2 ?в?????? id???????????????????????????? t1 ?е?????????mysql??????t2 ??????β???????????? t2 ??????ж???????????
????range checked for each record (index map: #)
????mysql??????????????????????????????????????????????????????????????????????????????????????е?????????????????????????????ü?????????????????????????κ???????????????????
????using filesort: mysql??????????????????????????ü?????????????????????????????е?????????????з??? where?????????????????????????????洢????????Щ??????????????????????????????????????????????”7.2.9how mysql optimizes order by”??
????using index
??????ε???????????????е??????????????????????????????????????????????????????????????????
????using temporary: mysql????????????洢?????????????????????????????????????groupby ?? order by ??????????????г????????Ρ?
????using where
????where??佫??????????Щ????????????????????????????????????????????????????????м?????????????????extra ???????? using where ??????????????? all ?? index?????????????
?????????????ò??????????????????? extra ??ε???usingfilesort ?? using temporary ???????
???????????? explain ?????? rows??ε??????????????????????????Ρ??????????????????mysql?????????л???????????????????????????? max_join_size ????ò?????????????????????????????????Щ???select ???
??????????????????????? explain??????????????????????????????????
??????????????? select ??????????? explain ?????
????explain select tt.ticketnumber?? tt.timein?? tt.projectreference??tt.estimatedshipdate??
????tt.actualshipdate?? tt.clientid??tt.servicecodes?? tt.repetitiveid?? tt.currentprocess??
????tt.currentdppers tt.recordvolume?? tt.dpprinted?? et.country??et_1.country??
????do.custname from tt?? et?? et as et_1?? do wherett.submittime is null and tt.actualpc = et.employid
????andtt.assignedpc = et_1.employid and tt.clientid = do.custnmbr;
??????????????У???????????裺
???????????ζ??????£?
????table column columntype
????tt actualpc char(10)
????tt assignedpc char(10)
????tt clientid char(10)
????et employid char(15)
????do custnmbr char(15)
?????????????????£?
????table index
????tt actualpc
????tt assignedpc
????tt clientid
????et employid (primary key)
????do custnmbr (primary key)
????tt.actualpc ???????????????
???????κ???????δ??????????? explain??????????????£?
????table type possible_keys key key_len ref rows extra
????et all primarynull null null 74
????do all primary null null null 2135
????et_1 allprimary null null null 74
????tt all assignedpc?? null null null 3872 clientid?? actualpc range checked for each record (key map: 35)
??????????? type ???????????????all??????????ζ??mysql?????е?????????????????????????????????????????????????????????????????? ??????????????????????£????????74 * 2135 * 74 * 3872 = 45??268??558??720????????????????????????????????????????????
???????????и??????????ζ???????????mysql????????Щ?????????????????????isam?????????????????ζ?????????????????????? ???????????????£?varchar?? char?????????????????????????????? tt.actualpc ?????char(10)??et.employid ????? char(15)??????????????
????????????????????? alter table ????? actualpc??????10??15???????
????mysql> alter table tt modify actualpc varchar(15);
???????? tt.actualpc ?? et.employid ???? varchar(15)
????????????????? explain ??俴???????
????table type possible_keys key key_len ref rows extra
????tt allassignedpc?? null null null 3872 using clientid?? where actualpc
????do all primary null null null 2135 range checked for each record (keymap: 1)
????et_1 all primary null null null 74 range checked for eachrecord (key map: 1) et eq_ref primary primary 15 tt.actualpc 1
???????????????????????????????? rows???????????74??????β???????2?????
?????????????????????? tt.assignedpc = et_1.employid ?? tt.clientid= do.custnmbr ????ε????????????
????mysql> alter table tt modify assignedpc varchar(15)?? ->modify clientid varchar(15);
???????? explain ???????£?
????table type possible_keys key key_len ref rows extra
????et all primary null null null 74
????tt ref assignedpc?? actualpc 15 et.employid 52 using clientid?? where actualpc
????et_1 eq_ref primary primary 15 tt.assignedpc 1
????do eq_ref primary primary 15 tt.clientid 1
????????????????????????????
?????????????????????mysql?????????? tt.actualpc?????????????????? tt??????????????????????????mysql??????????????
????mysql> analyze table tt;
??????????????????????????????????explain ???????£?
????table type possible_keys key key_len ref rows extra
????tt all assignedpc null null null 3872 using clientid?? where actualpc
????et eq_ref primary primary 15 tt.actualpc 1
????et_1 eq_ref primary primary 15 tt.assignedpc 1
????do eq_ref primary primary 15 tt.clientid 1
?????????explain ????е? rows??ε?????mysql???????????????22????????????????????????????????????????????select ???????? straight_join ??????????????????????????from??????ò????????г????????
???????????????????????漰???????????????????SPASVOС??(021-61079698-8054)?????????????????????????
??????
???mysql?????????????mysql????? distinct?÷?MySQL?н???????????з??Mysql?????????MySQL Cluster??δ????????????????ν?MySQL??????????300???????MySQL???Uber???????????????????MySql SQL??????????MySQL?????????????????λЧ?????SQL???Java???????:?????MySQL???????linux?°??mysql????MySQL ???????????????????binlog??MySQL????????????Щ??????MySQL???????Mysql???????JDBC?????mysql?????
???·???
??????????????????
2023/3/23 14:23:39???д?ò??????????
2023/3/22 16:17:39????????????????????Щ??
2022/6/14 16:14:27??????????????????????????
2021/10/18 15:37:44???????????????
2021/9/17 15:19:29???·???????·
2021/9/14 15:42:25?????????????
2021/5/28 17:25:47??????APP??????????
2021/5/8 17:01:11????????
?????????App Bug???????????????????????Jmeter?????????QC??????APP????????????????app?????е????????jenkins+testng+ant+webdriver??????????????JMeter????HTTP???????Selenium 2.0 WebDriver ??????