????MySQL?????????????
??????1?????????——????κ?????
????# ??????????
????create index indexName on tableName(columnName(length));
????# ?????char??varchar?????length???????С????????????
????# ?????blob??text????????????length??
????# ?????
????alter tableName add index indexName on (columnName(length));
????# ?????????????
????create table tableName(
????id int not null??
????username varchar(16) not null??
????index indexName (columnName(length))
????);
????# ???????
????drop index indexName on tableName;
????# ??????
????show index from tableName;
??????2??????——?????е???????????п???????????????????????????????
????# ??????????
????create unique index indexName on tableName(columnName(length));
????# ?????
????alter tableName add unique indexName on (columnName(length));
????# ?????????????
????create table tableName(
????id int not null??
????username varchar(16) not null??
????unique indexName columnName(length)
????);
??????3??????????——?????????????????????п????????????????????????????????
????create table tableName(
????id int not null??
????username varchar(16) not null??
????primary key(id)
????);
??????4?????????——??????????????
????# ???????????name?? age?? address?? zip??????Σ?????name?? age?? zip?????????????
????create index indexName on tableName(name(10)?? age?? zip);
????# ??
????alter table tableName add index indexName (name(10)?? age?? zip);
????????varchar??????Σ???????????????????????????????????????????????????????????????С??
???????????
???????????name??age??zip??????????????μ???????????????????????explain?????з?????
????select * from tableName where name="lee" and age=20 and zip="050000";
????select * from tableName where name="lee" and age=20;
????select * from tableName where name="lee";
????# ??????????????order by??group by????????????????????????????????Hash????????order by??Ч????
????select * from tableName order by name?? age?? zip;
????select * from tableName where name="lee" order by age?? zip;
????# ????group by???????????????????????μ???????У????????к?????????count()??sum()??max()???
????# ???????????????????????????????????????
????select count(id) from tableName where sex='m' group by age?? zip;
????????????????????????????????????????????????
???????????????????????(key1?? key2)??(key2?? key1)????????
?????????????????
??????????????where??join?г???????????????????mysql???<??<=??=??>??>=??between??in????Щ???????????%??_???????????like????????????
????select a.name from table1 as a left join table2 b on a.name=b.username where b.age=20 and b.zip='053000';
????# ??????????username??age??zip??????????
????????????????
?????????к???????????????????????????????????????????????????????????Щ??ν?????????????????????????????????
??????????????±????????insert??update??delete?????????±??????????????????????????????????
????????????????????????????????????????????????洢??????????????????ε???????????????????????????????????????????????????MySQL???????????????????????use index(key_list)?????????????????????
????????????У?????Ч???????????ж?????????У???????????????????????e???
??????????????????
?????????????????NULL????У???????????????????????ε??????NULL??????????????????ε???????
????????????????varchar??????ν??????????????????????n???????????????????????????????????????I/O??????
????MySQL???????????????????????????????????ж?????????????????e????????????????????????
????like?????????????????????????????￡????????????????????????????????%??????????棬??like "abc%"??
??????????????????????select * from users where YEAR(datetime)<2015;???????????Ч???????????裻
??????????NOT IN??IN??
?????????????
????????ο???MySQL????????????????
????3. join??????
????join???????????????????
???????????
????select * from a inner join b on a.id = b.id;
????# ?????
????select * from a??b where a.id = b.id;
???????????????????????????????????????У?
???????????
????select id?? name?? action from user as u left join user_action a on u.id = a.user_id;
????????????????????????????????????????????????????NULL?????
????????????????
????????????????????????????on??????????????????n???????????????????γ?n?????????????????У??????????????on?????????????????????ζ???null???????????????
?????????????????????????е?????
????# ???a.user_id?????????NOT NULL?????a??u?????????????е??????о??????????????????using(col);
????select id?? name?? action from user as u
????left join user_action a on u.id = a.user_id
????where a.user_id is NULL;
????# ??????????????
????select * from table1 use index (key1?? key2) where key1=1 and key2=2 and key3=3;
????select * from table1 ignore index (key3) where key1=1 and key2=2 and key3=3;
????????????
??????????????????
????# ??my.cnf???????????????
????long_query_time = 1
????log-slow-queries = /var/log/mysql/mysql_slow.log
????# ???????????????????????????????????????????????????? log-queries-not-using-indexes
?????????????mysqlsla???????????????????????з?????
????# mysqlsla -lt slow /var/log/mysql/mysql_slow.log
???????????????????????????ò??????????????????????????????????в??????????????????????????????????????????
??????????????????
???????????????????????????????????
???????????儷????????????????
????1. ????????????????????????????
????2. ???????????????????????????
????????????????
????MyISAM????????????????????????mysqlreport??????????????????????????
????MyISAM???????????????????????????select????????????????
??????????2???????update??insert??delete????????????????????в??????????2????????????????????????????????????2???????????????????????????????????????????????????д???????????????????????????????????????????????
????????????????????????????????
????show processlistG;
?????????
???????????????????????????2?????????????????????????????????????????MyISAM?洢???棻
???????????????????2????????????????????????????????????????Innodb?洢???棻
??????????
????Innodb?洢????????????????????
?????????????????select??update????????￡?????????????????д?????????????update??????select?????????????????????????????е???????????????У?
????????????????????????
????1. ??????????????????????С??
????2. ???????????2?????????￡????????????????????????????????????????????Updating??????????????????????Updating?????????????????????????????????????????Updating????????????????д?????????????е??
????3. ?????????????????????????????????????????????????????????
?????洢?????
????show table status from DataBaseName where name='TableName';
????alter table tableName type=myisam;
???????????????
??????????????????
????Innodb?洢????????????????????????????????????????????????????????????ACID?????????????????????????????????????
????Innodb??????д?????WAL???????????????????????????????????д??????е??????????????????????????д????????Innodb????????????????????
?????????????????????????????д???????????????????
????# ????????????????????д???????????????????????￡????????????
????innodb_flush_log_at_trx_commit = 1
????# ?????????????д?????????1Sд??????????Σ?????μ???????????????????????
????# ???mysql????????????????????н?1???????????????
????innodb_flush_log_at_trx_commit = 0
????# ???????????д???????????????1S?????′?????????????????????
????# ?????????????????????????
????innodb_flush_log_at_trx_commit = 2
???????
????“д????????”????????д??λ??????????е???????????“??μ?????“????????????е?????????д???????
??????innodb_flush_log_at_trx_commit?????0???????ú??????????????????????????????????????????????????????2??
????# ????Innodb?????????????滺????С?????????????????????????80%??
????innodb_buffer_pool_size = 12G
??????ò??????
??????????select?????????????????У?????′????????
????query_cache_type = 1
????query_cache_size = 64M
????query_cache_limit = 1M
??????????????????MySQL???????????????????и??2??????漰?????????в?????涼???Ч??
??????????????????????select???????????μ???????????ò?????棻????select??update???????????????ò?????棻
?????????
????????????????????????????????????????????????????????????????
????# ??????????????y????????????????????MySQL???????????????????????
????tmp_table_size = 512M
????????
????MySQL???????????????????????????ζ?????????????????μ????????????????????????MySQL???????
????# ??????ó????????????????????????
????thread_cache_size = 100
????# ?????MySQL????100??????