????b+??????????
??????????????????????????29???????????????1???????????棬??????????IO????????????????????29??17??35????????????1??P2?????????????????????????IO?????????????????????1??P2???????????????3???????????棬?????????IO??29??26??30????????????3??P2????????????????8????棬??????????IO?????????????????????29??????????????????IO?????????????3???b+???????????????????????????????????????????IO?????????????????????????????????????????????IO???????????????ε?IO?????????????????
????b+??????
????1.????????????????????IO?????????b+??????h?????赱????????????N????????????????????????m??????h=?(m+1)N??????????N?????????￡?m???h?С????m = ???????С / ????????С?????????С??????????????С????????????????????????С??????????????????????????????????????????????????????????С??????int?4???????bigint8????????????????b+???????????????????????????????????????????????????????????????????????????????????????1????????????????
????2.??b+???????????????????????????(name??age??sex)?????b+??????????????????????????????????統(tǒng)(??????20??F)?????????????????????b+??????????name??????????????????????name????????α??age??sex?????????????????????(20??F)?????????name?????????????b+?????????????ò?????????????????????????name????????????????????????name??????????????????????????????統(tǒng)(??????F)???????????????????b+????????name???????????????????????age??????????????????????????????????????????????????F????????? ???????????????????????????????????
????????????
????????MySQL????????????????????????????????????????????????????÷????????????????????????????????????????????????????????????????????????????????????????????????
??????????????????
????1.?????????????????????mysql????????????????????Χ???(>??<??between??like)?????????a = 1 and b = 2 and c > 3 and d = 4 ???????(a??b??c??d)??????????d???ò?????????????????(a??b??d??c)?????????????????a??b??d?????????????????
????2.=??in????????????a = 1 and b = 2 and c = 3 ????(a??b??c)???????????????mysql??????????????????????????????????
????3.???????????????????????????????????count(distinct col)/count(*)???????β?????????????????????????????????????????????1?????Щ?????????ο????????????????????0???????????????????????????????????ó????????????????????????????join???????????????0.1??????????1?????10?????
????4.?????в???????????????“???”??????from_unixtime(create_time) = ’2014-05-29’?????????????????????b+???д???????????е????????????м?????????????????????ú?????????????????????????????д??create_time = unix_timestamp(’2014-05-29’);
????5.???????????????????????????????????????a???????????????(a??b)???????????????????????????????
?????????????????
?????????????????????sql?????????????status??operator_id??type??operate_time????????????????status??operator_id??type?????????????????????????????????????????????????????????
???????绹?????2??
????select * from task where status = 0 and type = 12 limit 10;
????select count(*) from task where status = 0 ;
?????????????????(status??type??operator_id??operate_time)????????????????????????????????????????????????????????
?????????????? – explain????
????????explain???????????????????????÷?????κ??????ο?????explain-output????????????rows??????????????rowsС??????????????????????????????????????????????????????rows??
????????????????????
????0.?????п?????????????????????SQL_NO_CACHE
????1.where????????飬????С???????????仰??????????????where?????????з????????С??????????????????η?????????????ε???????
????2.explain????м?????????1?????￡???????????????????????
????3.order by limit ?????sql????????????????
????4.????????ó???
????5.????????????????????????
????6.?????????????????????0????
?????????????????
???????漸?????????????????η?????????????
???????????д??
???????????￡?????дSQL??????????????????????????????????д???????Ч???????б????????????????mysql????м????????????з??????????????????????
????select
????distinct cert.emp_id
????from
????cm_log cl
????inner join
????(
????select
????emp.id as emp_id??
????emp_cert.id as cert_id
????from
????employee emp
????left join
????emp_certificate emp_cert
????on emp.id = emp_cert.emp_id
????where
????emp.is_deleted=0
????) cert
????on (
????cl.ref_table='Employee'
????and cl.ref_oid= cert.emp_id
????)
????or (
????cl.ref_table='EmpCertificate'
????and cl.ref_oid= cert.cert_id
????)
????where
????cl.last_upd_date >='2013-11-07 15:03:00'
????and cl.last_upd_date<='2013-11-08 16:00:00';
????0.????????￡?53????? 1.87????????t??????????
????53 rows in set (1.87 sec)
????1.explain
????+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+
????| id | select_type | table      | type  | possible_keys                   | key                   | key_len | ref               | rows  | Extra                          |
????+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+
????|  1 | PRIMARY     | cl         | range | cm_log_cls_id??idx_last_upd_date | idx_last_upd_date     | 8       | NULL              |   379 | Using where; Using temporary   |
????|  1 | PRIMARY     | <derived2> | ALL   | NULL                            | NULL                  | NULL    | NULL              | 63727 | Using where; Using join buffer |
????|  2 | DERIVED     | emp        | ALL   | NULL                            | NULL                  | NULL    | NULL              | 13317 | Using where                    |
????|  2 | DERIVED     | emp_cert   | ref   | emp_certificate_empid           | emp_certificate_empid | 4       | meituanorg.emp.id |     1 | Using index                    |
????+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+
?????????????м????????mysql????idx_last_upd_date???????cm_log????379?????????????????63727?????????????????derived??????????????????????????????????????γ???????????????????????ID??derived2???????ID = 2?????????????????????????63727???????????????????ID = 2????便??????д????????????????????????????????employee??13317?????????????????emp_certificate_empid????emp_certificate???rows = 1?????????????????????????????Ч????????ú????cm_log??379?????????????????????й????????????????????????????????????????cm_log???ò????????cm_log???????379???????
??????????????????????????????????????cm_log??join??????????????????cm_log??join????????????????????????????????cm_log??ref_table??EmpCertificate????emp_certificate??????ref_table??Employee????employee???????????????????????????union???????????????????union????????union all???????????“distinct”?????????????union??t????????????????????????distinct??????????????????????union all?????????union?????????????????SQL?????