????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?????