????1.???????? MySQL 5.0
????1.1 ???????????????????select????????????
????1.2 ???洢?????????????????16???????????????????????256????
????1.3 MyISAM??InnoDB??????BTREE??????
????1.4 MySQL??????????????????????????????????ε??N???????????????MyISAM N=1000B??InnoDB N=767B;???????????????????????λ????????????????????????????
????1.5 MyISAM?????????????FULLTEXT?????????char??varchar??text?У????????н??У?????????????????????????
????1.6 MyISAM??????????????????????????
????1.7 MEMORY?洢???????HASH??????????BTREE??????
????1.8 ??????
????create [unique | fulltext | spatial ] index index_name
????[using index_type ]
????on tablename (index_col_name(10));
??????????? alter table;
????drop index index_name on table_name;
????2.????????????
????2.1 ???????У???????select??????У????????????????У??????????????where????У??????????????У?
????2.2 ???????????????????????????????е???????????Ч???????ó????????????????????????
????2.3 ????????????????????????????+???????????????д??????????
????2.4 ?????????????????n?е???????????????MySQL???????n????????
????2.5 ?????????????
????2.6 InnoDB????????????????????????????????????????????
????3.BTREE??HASH????
????3.1 hash?????????
??????????????=????<=>??????????????
????????????????hash????????order by??????
????MySQL??????????????????ж????У?
???????????????????????????У?
????3.2 btree????????;
?????????>??<??>=??<=??bettween??!=??<>??????like 'pattern'(pattern???????????)?????????????????????????
?????????????????????
????select * from t1 where key_col = 1 or key key_col in (15??18??20);
??????????btree??????
????select * from t1 where key_col > 1 and key_col <10 ;
????select * from t1 where key_col like 'ab%' and key_col ='lisa' and key_col ='limon' ;
??????????????memory???????hash??????
???????explain select * from t2 where key_col > 1 and key_col <10 ;
???????explain select * from t1 where key_col > 1 and key_col <10 ;
????//key_col ???????
??????????????? t2???hash??????key??null?? t1???btree??????key??key_col ; hash???????t2?????????
???????
?????????memory??????????hash?????????sql?????д???????????÷?Χ?????????????btree??????
??????????????????????????????????У???????????????MySQL????????м??????????????????
????------------------------------------------------------------------
????MySQL????е???????
????1.MyISAM??????????????????洢?????????????????????
????InnoDB?????????????洢?????????????棬???????ж?????????
????MyISAM??InnoDB????btree??memory??heap?洢??????????hash??BTREE??
????MySQL?????????????????????????
????2.?????????
???????????????????????????????
????create index index_name on sales2(company_id?? moneys);
?????????????????????where company_id=2006;????????????????????
????where moneys=1;??????????????????????
????where name like '%3' ?? where name like '3%' ??%??????λ?????????????????name??????С?
????where name is null??????????????name??????С?
????3.?????????????????????
????3.1 ???????????????????????????????????
????select * from t1 where key_col > 1 and key_col <90 ;??key_col????????1---100???
????3.2 memory/heap????????????в????=?????????е????
????3.3 ??????or??????????????????????????????
????where year=2006 or country='China';year???????У?country?????????У?
????3.4 ????????????е????????
????3.5 like??%λ???
????3.6 where name=294?????name???????????????????????????????????where name=‘294’??
??????????????????
????show status like 'Handler_read%';
???????У?
????Handler_read_key:???????????????????????Ч?????
????Handler_read_rnd_next:??????????в?????е???????????????????????????裬????Ч???????????????