????????????????

????A)I/O???

????????????select/poll/epoll???????????????????????Unix/Linux?°????е??豸???????????????I/O??????????????????????????I/O?????????á???? I/O??????????????I/O????????????????????Unix/Linux?????I/O?????(????Linux?μ?I/O???????????????????????????I/O???????????)??

??????????????????I/O???????????

?????????????????????????????fctnl???? O_NONBLOCK ????ɡ?

???????????????select/poll/epoll????????I/O??????????????????????????????I/O??????????????á?

???????????AIO?????????I/O ????????????? I/O ???е?????I/O?????????????????????????????????????????I/O???????????ó??????????????????????????????????????????????????????????????????????? I/O ????????

?????????????????κε?????????????I/O????????????????????????????????????CPU??????????????????????????????????????????????Nginx????????Ч???????????epoll??AIO??????????I/O???

??????????Windows?μ?I/O????

????a)?????WriteFile?????????????????????????????????????????????????????????????????Overlapped?????????????????????????????????????Overlapped??????Overlapped I/O???????WaitForSingleObject????????????д??ɡ??????????????????????

????b)???????WriteFileEx???????????????????I/O?????????????????callback????????I/O??????????? ???????????????Windows???callback?????????APC(Asynchronous Procedure Calls)??????У??????????ó????????????????(Alterable)?????????????е?????????????????????WaitForSingleObjectEx?? WaitForMultipleObjectsEx?? MsgWaitForMultipleObjectsEx??

????SignalObjectAndWait ?? SleepEx?????????Alterable??????????????????????wait????????????????

????c)?????IOCP ? IO Completion Port??IOCP???I/O????????????????У????????????????е?????????????????????????μ??????????????(????????????????????μ????????????????????)??IOCP??????????????????Linux?μ?AIO???????????????????????÷????????????

????????????????I/O???????????????I/O????????????????У???????????????????cache???????????????????????????????????????????д?????cache??д?????????д???Σ?????cache????????????????????????latency?????????????д??????????????????

????B)???CPU????

????????CPU??????????????????CPU0???????????0??CPU????ù??????????CPU???????????????CPU0???е???????????????????????????????????????????????????????????????????????????????????CPU????????????????CPU0?????????????????????????????????

?????? ????Windows?????????????????????????????е??????????????????е????????????????(Set Affinity??)?????ò???????????????????????Щ?????

?????? ????Linux????????????taskset??????????(???????????schedutils????????????apt-get install schedutils)

???????CPU?????????????NUMA????(Non-Uniform Memory Access)??????????????????SMP(Symmetric Multi-Processor )???????????????????????е?洢????I/O???????????????′洢?????????????????????ζ??????????NUMA???￡????????????????node?? ???node??????????洢?????????NUMA???Щ?????????????????????????Linux ?? NUMA ??????????Linux?￡???NUMA????????????numactl ?????????????(???????myprogram arg1 arg2????????node 0 ?????????????node 0 ?? 1??)

1 numactl --cpubind=0 --membind=0??1 myprogram arg1 arg2
???????????????????????????????????????node?????????á??????????ó???????????????????node???磺

1 $ numactl --membind 1 --cpunodebind 1 --localalloc myapplication

????C)?????????

????????????????????????????cache???????????????????д????????????????????????????棬?????????????Linux????????free???????? free/used/buffers/cached???????????buffers??cached?????40%????????????????????????????SCSI??ú???????Intel SSD ?????????????????д?????????

??????????????????????????????????????Linux??Ext3/4????????????????????????????????????????????????????????/etc/fstab????????????? ?????noatime????(???????????)???????????dealloc?????????????????????д?????????????????飬????????д?????????????????????????data=journal??data=ordered??data=writeback?????????data=ordered??????????????????

???????????????Щ?????ext4???????????????????????

??????????????Linux?μ??I/O???????? iotop???????????????????????д??????????

?????????????Щ????NFS??XFS??????????????google?????Щ??????????????????????????????????????????????????Linux???????????????????

????4.5)????????

???????????????????????????????????????????Щ?ɡ???????????Щ?????????????????????????????????????????????????????????????????????????????????Щ?????????????????????????????

????A)????????????

???????????????治?????????м????????????????????????

?????? ??????????????????????????????????????￡???????????????????????????????????????????????д???????????????????д????????????????????????????????????????????????????????????????????????Ч??????????NoSQL??????????????????????????????????????????????????

?????? ??????洢????????????????????????????????洢??????????????????????洢????????????????????????????????Oracle????????????????Σ???????????????????????????I/O????????磺MySQL?????show engines;???????????洢??????????????洢?????в?????????????????????????????????в?????????
?????? ????????????????????????????????????????????????????????????????????????????????????????????????????????????

????B)SQL??????

????????SQL???????????????????ù???????磺MySQL SQL Query Analyzer??Oracle SQL Performance Analyzer?????????SQL Query Analyzer????????????????е?RMDB??????????????????????????????е?SQL?????????? ?????????explain??????SQL?????Execution Plan???????????

????????????????????????????????????????棬????????????????????????????Щ???????SQL??????????????檔

?????????????????????????SQL??????????????????????SQL??

?????? ???????????磺select * from user where lastname = ??xxxx??????????SQL?????????????????????????O(n)??????????????????(?磺100???????????50ms???????????????5????)???????????????????????????????????????????????????????????????????????????????(?lastname??????)??????????key-value????????????key??where???????Σ?value???????к????????????????????????????O(log(n)) ????B-Tree???????(?磺100???????????50ms???????????????100ms)??

?????? ????????????????Σ??ò??????????????????????????????????ж????????????????Щ???????????????????????????????????????????Where????Order by????У??????Where??Order by????е???κò?????м?????????????????NOT????????????????????

?????? ??????????????????????????????????????????????????EXISTS??IN??JOIN(???????join??????ο????SQL??Join???)?????????????????????????SQL??????????????JOIN??IN/EXISTS????????Щ????????????????????????????EXISTS???????????IN??IN???????????JOIN???????????????????????????schema??SQL????????????????????????????????????????????ù?????????????????SQL????????????ü???????SQL????????????????????N????SQL???????????????????????????????Exists?????????????In??In????????Join?????????????????С??????????У?Exists?????In????С????????????????????????????????????ɡ????????????SQL Server?????′??????????IN vs JOIN vs EXISTS??

?????? JOIN?????????????Join?????????????????Join??????????????????join??????????????????????????????????????Join?????????????????????????鰱????Hash???Join??(MySQL????????)
?????? ???????????????????????????????????????????????????????????????????????B-Tree??????O(log(n))????????????????????????????O(log(n)) * O(log(m)) ???????

?????? Hash???Join????????????????O(log(n))?????????????????hash????????

?????? ????鰱???????????????????????????????????????????????????????????

??????????仰??????????????????????????SQL???????????????????????

?????? ?????????????????MySQL???Limit??????Oracle???rownum??SQL Server???Top?????????????????????????????????????????????????????????????????top n??????????????????order by???????????????????order by????ν??????????????????????order by???????????select???????????????????????????????????????????????????????????????????Mysql?????OFFSET??SQL Server?????FETCH NEXT??????Fetch??????????????????????????????????????????order by??ε?????????????????????where???????????>=???????select???????????seek????????fetch??seek???????fetch?????

?????? ????????????????????????????????????????з???????Σ????????????????????????????磺???????????

?????? ??????????????Like???????????????????????????????????????????Sphinx??

?????? ??????

?????? ???select *??????????????????Σ?????ж??????????????????????????????????????

?????? ?????Having???????????????е??????????ò?????

?????? ??????????UNION ALL ??? UNION??

?????? ????????insert??delete?????????update???update?????????????????????????update???????????????????????

?????? ????

????????SQL??????????????к?????￡? ???????????????в???????????????籾??????????MySQL??????????20+?????頃

??????д????????????????????