?????????????н??????????????Oracle????MySQL???????????????CPU ??????????CRM??????SQL??????????????????????????????????????????????Oracle????MySQL????????????SQL???????????????????????????????汾??MySQL5.1??????汾???????????????????????Oracle????MySQL?????????????????????????????淶?й漲????????????и?????join????????????о??Щ??????Oracle????MySQL?????п????????????
????1). ??????????O??????????????????????????????????????SQL??????????????????????Щ????????飬?????????????????????????????
????2). ?????????з??????????????????????MySQL??????MySQL???????
????1 ???д???
????1.1 ????????
????Oracle????????????????????κ?DML??????DDL????????????ж??С????????Щ??С?????????????????????????С??????????????????????????????Oracle??OLAP????ó????п??????ò??д???????????з???????SQL?????
??????????в??????????
????(1)??????????????hint?????select /*+ parallel(4) / count( ) from test_a ;---?????????ж??4????в????
????(2)??????alter table?????alter table test_a parallel 4;--????oracle???????????????м???????ò??ж?4??
????1.2 ??????
????????MySQL???????д?????????????Oracle????MySQL??????????????????д????SQL?????????飺
????(1)???????????????????ADS????????????????????????Oracle?е???з????????
????(2)????????SQL?????????????????????SQL??????????????????н??д????
????2 SQL????????????????????????
????2.1 ????????
???????MySQL?????????Oracle????????????????????????????????????Oracle???nested loop??hash join??sort-merge join?????? ????MySQL??????????е?nested loop???????????Щ????????????????????????MySQL????????????????????????????????Щ?????????MySQL?????????????SQL????е??Щ????????????????????????????????ж??
???????????????????????????buffer cache?С?
??????????????Buffer Cache?ж??????顣
??????????Oracle??????SQL??????????
????(1)??????β???????t
????select * from t ;
????(2)??????β????
????select * from t??
????????β????6?????????????β????0?????????6???????????????????ζ?????????浽buffer cache ?У???????ζ???????????????????buffer cache ???
????Oracle????????У??????????????????????????????????????????????????????????????????湤???????????????????????SQL???????????????ж?????????
????2.2 ??????
????MySQL???????SQL??????????????????Oracle????????????????????????????????SQL????MySQL??????????????????и???
????(1)??????????????????????????????????????SQL??????????????裨dump????????????????????????????????????dump??????????????????
????(2)????????????????????????????????????????????ADS????????????????????
????(3)???????????????????????????????????????????????????????????SQL??????????????????н??д????
????????? ???????????????????100W??????????5S???????????SQL?????
????3.In (…..)
????3.1 ????????
????Oracle?ж?in(….)???????????1000??????MySQL???????и??????????SQL????????????????????in(…)??????????????????????????????in(...)??????????????????????????in??????????????100????
????3.2 ??????
????Oracle??select * from t where id in(id1??id2…..id1000);
????MySQL??select * from t where id in(id1??id2…..id100);
????4 ????
????4.1 ????????
????MySQL??5.6?汾?????????????????????????????nested loop???????????????????????????????????????????μ????δ????????Oracle????MySQL???????????????汾??5.5???Oracle????д????????????????????MySQL??SQL??г?????????????????????????cpu????????????????????????
????????????
????SELECT first_name
????FROM employees
????WHERE emp_no IN
????(SELECT emp_no FROM salaries_2000 WHERE salary = 5000);
????MySQL?????????????employees???е???????????????????????
????4.2 ??????
??????д????
????SELECT first_name
????FROM employees emp??
????(SELECT emp_no FROM salaries_2000 WHERE salary = 5000) sal
????WHERE emp.emp_no = sal.emp_no;
???????????????5.1??5.5?汾?ж??????????????????????????
???????Mysql 5.6??汾??????????鷳????????д??????
????5 ??????
????5.1 ????????
????????????????д洢?????????????????????????????????????????????????????????????????????????????д?????????????Ч?????????δ??????Oracle ??????????????????????????????????????????????MySQL???????????????????Oracle?е????????MySQL????????????????????????????????
????5.2 ??????
???????????????????????y???????
????6 ????????
????6.1 ????????
??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????￡????????????????????????????????????????????????????????????????????????????????????????????е????
????MySQL?????????????????????Oracle??????ú?????????SQL???????MySQL???????????????????????????????????????
??????????????????SQL???
????select * from emp where date(gmt_create) = '2017-02-20'
?????????gmt_create????????????????????????emp??????????gmt_create????????????????б??????????????????????????????????磺create index emp_upper_idx on emp(date(gmt_create)); ???????????????????????С??????????????rowid???????е???????????????????
????6.2 ??????
???????SQL??д????????????????????????????????????
????select * from emp where gmt_create>='2017-01-20 00:00:00’ and gmt_created<’2017-01-21 00:00:00’
????7 ???
????(1).MySQL???????в??????????и?????????parallel????
????(2).MySQL???????????????????????????????????????SQL??????
????(3).MySQL????in(…)??????????鯔?????100????
????(4).MySQL????????????????????????????????????5.6?????汾??
????(5).MySQL???????????????????????????
????(6).MySQL?????????????????????дSQL?????????????á?