????????
????Mysql?????????????????洢????????????????ù??????????????????SQL???????????????????????Mysql??????innodb????????????B-Tree????????????????????????????????“??”??????????????????????????????????????????????O2O?????????????????????Щ????????SQL?????????????innodb??B-Tree???????Щ??????????
????????????????????????￡?
????1???????????????????
????2??????????????????????
????3????????explain???????????
????4???????????????????
??????????ι???
????????????Mysql????????????????????????????????????????COST???????????????????COST??????Mysql??????????????в????Mysql???????????????????????α???????????
min_cost = INIT_VALUE
min_cost_index = NONE
for(index in all_indexs):
if (index match WHERE_CLAUSE):
cur_cost = COST(index)
if(cur_cost < min_cost):
min_cost = cur_cost
min_cost_index = index
????INIT_VALUE?????????????????
????all_indexs????????????е?????COST??????????“???????????????”??rows???????
????WHERE_CLAUSE?????SQL?е?WHERE???
???????μ??????Mysql??????ò????????table??????????????????ж????????????β??????possible_keys????????????????????Mysql??????????????в????cost?????????????????????????key????в????
???????????(match)
?????????ж???????????match??SQL?????
????1?????????????????????е?????????????????????м????в????????????????в??????в??????á?
???????磬????????3??????(str_col1??col2??col3)??????str_col1???????????(str_col1)??(str_col1??col2)??(str_col1??col2??col3)?????????????????
????????в??????????????????MySQL???????????????????????????SELECT???
????SELECT * FROM tbl_name WHERE str_col1=val1;
????SELECT * FROM tbl_name WHERE str_col1=val1 AND col2=val2;
????SELECT * FROM tbl_name WHERE col2=val2;
????SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;
??????? (str_col1??col2??col3)???????????????2????????????????3?????4??????????????????У???(col2)??(col2??col3)???? (col1??col2??col3)??????????
????2??where??????е?????=??>??>=??<??<=??BETWEEN??ISNULL????LIKE ’pattern’??????’pattern’?????????????
????3?????AND??????????????????
????SELECT * FROM tbl_name WHERE (str_col1=val1 OR col4 =val4) AND col2=val2;
???????str_col1=val1 OR col4 =val4?????飬col4??????????е??У??????????????????
????4??????????д???????????????????и?????????????в?????????е??С?
????SELECT * FROM tbl_name WHERE str_col1=1;
????SELECT * FROM tbl_name WHERE SUBSTRING(str_col1??1??8) = ‘title’;
??????1??????????1????????str_col1?????????????????????????2?????str_col1?и??????????????????????
??????????COST
????Mysql??μ?????????COST??
??????????cost????????“???????????????”??rows??????????????????information_schema????Mysql?????????????棬????????????????洢????????????Щ???
??????????????explain????“??????????????”?????????optimizer_trace???????????SQL??????cost???explain?????????????????????????????????
???????????????
????????????????????????where??????????????Mysql?????????潫???????????????????????????????ε????????SQL?е?where???????????????????????????????????????????
?????????????????????????????????????????????(????????????????????????InnoDB??????????????????????????????????????innodb)
????create table t1 (a int primary key?? b int?? c int?? d int?? e varchar(20));
????create index idx_t1_bcd on t1(b?? c?? d);
????insert into t1 values (4??3??1??1??’d’);
????insert into t1 values (1??1??1??1??’a’);
????insert into t1 values (8??8??8??8??’h’):
????insert into t1 values (2??2??2??2??’b’);
????insert into t1 values (5??2??3??5??’e’);
????insert into t1 values (3??3??2??2??’c’);
????insert into t1 values (7??4??5??5??’g’);
????insert into t1 values (6??6??4??4??’f’);
????t1???洢??????????(???????idx_t1_bcd??????t1???????а???t1???????????)??

??????????????idx_t1_bcd????????[b??c??d]??????Σ???????[a??e]??Ρ?idx_t1_bcd?????????????b???????b????????????c???????????????
????????????SQL??
????select * from t1 where b >= 2 and b < 8 and c > 1 and d != 4 and e != ‘a’;
???????????where??????????[b??c??d??e]?????Σ???t1???idx_t1_bcd??????????????[b??c??d]????????Σ??????idx_t1_bcd?????????????????????????????????
????????SQL??where??????????????3????Index Key (First Key & Last Key)??Index Filter??Table Filter??
?????????????????????????????3??????????ζ??壬????????????
????l Index Key
???????????SQL??????????е???????Χ(?????Χ+??????Χ)????????????????Index Key???????????Χ?????????????????????????Index Key???????Index First Key??Index Last Key??????????λ???????????????????????????????????
????Index First Key
??????????????????????????????????????where???????????????????????????=??>=?????????????????Index First Key??У?????????????????????????????????????????????????????>?????????????????Index First Key?У??????Index First Key?????????????????????Index First Key???????
????????????SQL????????????????????????Index First Key?(b >= 2?? c > 1)??????c??????? >?????????????????d??
????Index Last Key
??????????????????????????????????????where???????????????????????????=??<=???????????????Index Last Key?У????????????????????????????????????????????????????? < ????????????Index Last Key?У?????????????????????????Index Last Key???????
????????????SQL????????????????????????Index Last Key?(b < 8)???????? < ???????????b????????
????2 Index Filter
?????????Index Key???????????????where???????????????????Χ????????Χ?е???????????????????????????????SQL?????У?(3??1??1)??(6??4??4)???????Χ?У??????????????SQL??????????
????Index Filter???????????????????е????п???????????where?????????????????????where??????? =????????????м??????????????У?????????в???????????????????????????where????? >=??>??<??<= ???е???????????????????У???????where??????????????????????Index Filter??У???????????е?where???????? =??>=??>??<??<= ?????????????????????????where??????????????????????Index Filter??У??????в?????????????????????????????????????Index Filter??С?
????????????????SQL???????????????? >=??< ????????????????п????????????μ?c??d???м???Index Filter?С???????Index Filter? c > 1 and d != 4 ??
????3 Table Filter
????Table Filter?????????????????????????????????в??????????е??????????????Table Filter??С?
?????????????????????SQL??Table Filter? e != ‘a’??
???????????????????????????Щ?????WHERE??便?????????????????????????????????????????????????????????????????????У???WHERE?????????????????????????????????????????????????????????????????Χ????????????????ж??????????????????????????????Like???????????????AND???飬???????B-Tree??????????????