MySQL????????????????
???????????? ???????[ 2015/8/11 11:02:30 ] ????????????? ???????????
????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?????
???????????????????????婕�???????????????????SPASVO小??(021-61079698-8054)?????????????????????????
??????
???mysql?????????????mysql????? distinct?梅?MySQL?薪???????????蟹??Mysql?????????MySQL Cluster??未????????????????谓?MySQL??????????300???????MySQL???Uber???????????????????MySql SQL??????????MySQL?????????????????位效?????SQL???Java???????:?????MySQL???????linux?掳??mysql????MySQL ???????????????????binlog??MySQL????????????些??????MySQL???????Mysql???????JDBC?????mysql?????

???路???
??????????????????
2023/3/23 14:23:39???写?貌??????????
2023/3/22 16:17:39????????????????????些??
2022/6/14 16:14:27??????????????????????????
2021/10/18 15:37:44???????????????
2021/9/17 15:19:29???路???????路
2021/9/14 15:42:25?????????????
2021/5/28 17:25:47??????APP??????????
2021/5/8 17:01:11????????
?????????App Bug???????????????????????Jmeter?????????QC??????APP????????????????app?????械????????jenkins+testng+ant+webdriver??????????????JMeter????HTTP???????Selenium 2.0 WebDriver ??????