1、百度文郵-讓每個(gè)人平零地捉升口我廣州中醫(yī)藥大學(xué)醫(yī)學(xué)信息工程學(xué)院實(shí)驗(yàn)報(bào)告課程名稱(chēng)：網(wǎng)絡(luò)數(shù)據(jù)庫(kù)編程專(zhuān)業(yè)班級(jí)：計(jì)算機(jī)科學(xué)與技術(shù)（2012 ）級(jí)學(xué)生姓名： 張鵬燕76薛麗梅80楊曉珠18翁浩彬07實(shí)驗(yàn)名稱(chēng)：數(shù)據(jù)庫(kù)性能優(yōu)化實(shí)驗(yàn)成績(jī)：課程類(lèi)別： 限選E1公選口其它口數(shù)據(jù)庫(kù)系統(tǒng)性能優(yōu)化（基于MySQL數(shù)據(jù)庫(kù)，采用一定的查詢(xún)優(yōu)化方案，用 MySQL的內(nèi)部數(shù)據(jù)說(shuō)明優(yōu)化前與優(yōu)化后CPU的情況）一、實(shí)驗(yàn)背景數(shù)據(jù)庫(kù)系統(tǒng)是管理信息系統(tǒng)的核心，基于數(shù)據(jù)庫(kù)的聯(lián)機(jī)事務(wù)處理（OLTP）以 及聯(lián)機(jī)分析處理（OLAP）是銀行、企業(yè)、政府等部門(mén)最為重要的計(jì)算機(jī)應(yīng)用之一。 從大多數(shù)系統(tǒng)的應(yīng)用實(shí)例來(lái)看，查詢(xún)操作在各種數(shù)據(jù)庫(kù)操作中所占據(jù)的

2、比重最 大，而查詢(xún)操作所基于的SELECT語(yǔ)句在SQL語(yǔ)句中乂是代價(jià)最大的語(yǔ)句。舉例 來(lái)說(shuō)，如果數(shù)據(jù)的量積累到一定的程度，比如一個(gè)銀行的賬戶(hù)數(shù)據(jù)庫(kù)表信息積累 到上百萬(wàn)甚至上千萬(wàn)條記錄，全表掃描一次往往需要數(shù)十分鐘，甚至數(shù)小時(shí)。如 果采用比全表掃描更好的查詢(xún)策略，往往可以使查詢(xún)時(shí)間降為兒分鐘，山此可見(jiàn) 查詢(xún)優(yōu)化技術(shù)的重要性。小組通過(guò)不少的科研文檔中發(fā)現(xiàn)，許多程序員在利用一些前端數(shù)據(jù)庫(kù)開(kāi)發(fā)工 具（如PowerBuilder、Delphi等）開(kāi)發(fā)數(shù)據(jù)庫(kù)應(yīng)用程序時(shí)，只注重用戶(hù)界面的 華麗，并不重視查詢(xún)語(yǔ)句的效率問(wèn)題，導(dǎo)致所開(kāi)發(fā)出來(lái)的應(yīng)用系統(tǒng)效率低下，資 源浪費(fèi)嚴(yán)重。因此，如何設(shè)計(jì)高效合理的查詢(xún)語(yǔ)句就顯

3、得非常重要。通過(guò)調(diào)查得出許多程序員認(rèn)為查詢(xún)優(yōu)化是DBMS （數(shù)據(jù)庫(kù)管理系統(tǒng)）的任務(wù), 與程序員所編寫(xiě)的SQL語(yǔ)句關(guān)系不大，這是錯(cuò)誤的。一個(gè)好的查詢(xún)計(jì)劃往往可以 使程序性能提高數(shù)十倍。查詢(xún)計(jì)劃是用戶(hù)所提交的SQL語(yǔ)句的集合，查詢(xún)規(guī)劃是 經(jīng)過(guò)優(yōu)化處理之后所產(chǎn)生的語(yǔ)句集合。本實(shí)驗(yàn)以應(yīng)用實(shí)例為基礎(chǔ)，結(jié)合數(shù)據(jù)庫(kù)理論，介紹查詢(xún)優(yōu)化技術(shù)在現(xiàn)實(shí)系統(tǒng) 中的運(yùn)用。二、實(shí)驗(yàn)優(yōu)化方案DBMS處理查詢(xún)計(jì)劃的過(guò)程是這樣的：在做完查詢(xún)語(yǔ)句的詞法、語(yǔ)法檢查之 后，將語(yǔ)句提交給DBMS的查詢(xún)優(yōu)化器，優(yōu)化器做完代數(shù)優(yōu)化和存取路徑的優(yōu)化 之后，山預(yù)編譯模塊對(duì)語(yǔ)句進(jìn)行處理并生成查詢(xún)規(guī)劃，然后在合適的時(shí)間提交給 系統(tǒng)處理執(zhí)行，最后將執(zhí)

4、行結(jié)果返回給用戶(hù)。在實(shí)際的數(shù)據(jù)庫(kù)產(chǎn)品（如Oracle. Sybase等）的高版本中都是采用基于代價(jià)的優(yōu)化方法，這種優(yōu)化能根據(jù)從系統(tǒng)字 典表所得到的信息來(lái)估計(jì)不同的查詢(xún)規(guī)劃的代價(jià)，然后選擇一個(gè)較優(yōu)的規(guī)劃。雖 然現(xiàn)在的數(shù)據(jù)庫(kù)產(chǎn)品在查詢(xún)優(yōu)化方面已經(jīng)做得越來(lái)越好，但山用戶(hù)提交的SQL 語(yǔ)句是系統(tǒng)優(yōu)化的基礎(chǔ)，很難設(shè)想一個(gè)原本糟糕的查詢(xún)訃劃經(jīng)過(guò)系統(tǒng)的優(yōu)化之后 會(huì)變得高效，因此用戶(hù)所寫(xiě)語(yǔ)句的優(yōu)劣至關(guān)重要。本實(shí)驗(yàn)中，系統(tǒng)所做查詢(xún)優(yōu)化我們暫不討論，下面的實(shí)驗(yàn)過(guò)程我們小組將重 點(diǎn)說(shuō)明改善用戶(hù)查詢(xún)訃劃的解決方案。歸納總結(jié)，MySQL數(shù)據(jù)庫(kù)查詢(xún)優(yōu)化的方法主要分為以下五類(lèi)：1) 使用索引，“CREATE INDEX”。

5、MySQL允許對(duì)數(shù)據(jù)庫(kù)進(jìn)行索引,以此能迅 速查找記錄，從而無(wú)需一開(kāi)始就掃描整個(gè)表，山此顯著的加快查詢(xún)速度。 每個(gè)表最多可以做到16個(gè)索引，此外MySQL還支持多列索引及全文檢索。2) 使用“LIMIT 1”取得唯一行。此方法可以用于查詢(xún)數(shù)據(jù)量較少的數(shù)據(jù)表, 在已知所查詢(xún)的結(jié)果僅有一條記錄時(shí)，在SELEST語(yǔ)句條件下加上LIMIT 1”限制條件可以直接加快查詢(xún)速度。3) 盡量少使用SELECT *”語(yǔ)句中的通配符*,明確寫(xiě)出查詢(xún)內(nèi)容。4) 調(diào)整內(nèi)部變量。MySQL的性能開(kāi)放，因而用戶(hù)可以輕松地進(jìn)一步調(diào)整其 缺省設(shè)置以獲得更優(yōu)的性能及穩(wěn)定性。可變?nèi)笔≡O(shè)置值：1改變緩沖區(qū)長(zhǎng)度(key_buffer)

6、2改變表長(zhǎng)(read_buffer_size)3設(shè)定打開(kāi)表的數(shù)|_|的最大值(table_cache)4對(duì)緩長(zhǎng)查詢(xún)?cè)O(shè)定一個(gè)時(shí)間限制(1 ong_query_t ime)5) 用連接查詢(xún)替代子查詢(xún)通過(guò)以上五種方法可以對(duì)MySQL數(shù)據(jù)庫(kù)的查詢(xún)操作作出優(yōu)化，以提高用戶(hù)的 體驗(yàn)。三、實(shí)驗(yàn)過(guò)程實(shí)驗(yàn)準(zhǔn)備：建立兩個(gè)測(cè)試數(shù)據(jù)表：city country (如下)建立city表：cityjdcitycount ryjd1A Conua (La Corua)872Abha823處u Dhabi1014Acua605Adana976Addis Abeba317Aden1078Adoni449Ahmadnagar44

7、10Akishima50建立country表:country Jdcountry1Afgha nistan2Algeria3American Samoa4Angola5Anguilla6Argentina7Armenia8Australia9Austria10Azetbaija n11Bahrain注：show profile是由Jeremy Cole捐獻(xiàn)給MySQL社區(qū)版本的。默認(rèn)的是關(guān)閉 的，但是會(huì)話(huà)級(jí)別可以開(kāi)啟這個(gè)功能。開(kāi)啟它可以讓MySQL收集在執(zhí)行語(yǔ)句的時(shí) 候所使用的資源。為了統(tǒng)計(jì)報(bào)表，把profiling設(shè)為1。hys：(ll SET prof iling = 1 ；Quepy O

8、H, 0 rows affec1 ijai*niing 0-00 sec)說(shuō)明：具體為大家解釋以下三種方法，說(shuō)明MySQL查詢(xún)優(yōu)化的結(jié)果。1. 建立索引。MySQL允許對(duì)數(shù)據(jù)庫(kù)進(jìn)行索引，以此能迅速查找記錄，從而無(wú)需一開(kāi)始就掃 描整個(gè)表，山此顯著的加快查詢(xún)速度。每個(gè)表最多可以做到16個(gè)索引，此外MySQL 還支持多列索引及全文檢索。3百度文庫(kù)讓每個(gè)人平等地捉升口我例：查詢(xún)city表中的Ottawa的信息 優(yōu)化前：語(yǔ)句：SELECT * FROM where city 二 SELECT * FROM sakila.city where city = JOttawaJ ；! c itid ! cit

9、y ! coun七h(yuǎn)*y_:id ? i Ottawa !3 ：1 pow in set C0.00 secCPU使用情況:mj/sql show profile cpu for quepy 1；Status _Diu*a.tioni iCPU_useFi iCPU_systemi if-fstarting0.00008200000003 000000iichecking permissions 0090012 0.000000 0.000000i iOpening tables0000036i i0.000000i i0 000000i iin it0.090045 0L000000 0 0

10、00000 iSysten lock0000019 0.015600 0 000000i aopt imiz incj 0 08001.6i 8000000i 0 000000i istatist ic&0000025i i0L000000i i0 000000i ipreparing0090018 0.000000 0 000000i iexecuting0.000003 i0000000 i0000000a iSending data0000110i 0L000000i 0 000000 iend0 000007i i0000000i i 000000i iqueii/ end 00000

11、丄80.0000000 000000i iclosing tables0000010 i0.000000 i0.000000i ifreeing items0000174i i8 000000i i0 000000i icleaning up0001059 i0L000000 i0 000000i i15 pows in set, 1 warning shov/ prof ile block io for query 1；Status11 Duration 11 Block_ops_in 11 Bloc k_o ps _o utstarting11 0000082 :!NULL 1:NULLc

12、hecking permissions 11 0.000012 11HULL 11NULLOpeninsf tables1 0.00003& 11NULL 1:NULLin it11 0000045 1:NULL 1!NULLSystem lock1 0.000019 11NULL 11NULLoptini 乙 inq1 0.000016 11NULL 1:NULLstatistics1 0000025 :NULL 1:NULLpreparing! 0.000018 1:NULL 1!NULLexecuting1 0.000003 1:NULL 11NULLSending data1 0000

13、110 11NULL 1:NULLend:0000007 1:NULL 1:NULLquery end1 0.000010 1:NULL 1:NULLclosing tables! 0000010 1!NULL 1:NULLfreeincj items! 0.000174 :NULL 1:NULLGleaning up1 0.001059 11MULL 1:NULL15 fovjs in set, 1 wanning 優(yōu)化后:語(yǔ)句:alter table add index (city);SELECT * FROM where city =4Ottawa結(jié)果截屏：mys：ql altei* t

14、able sakila-city add indexcitiF)； iQuery OK, 0 rows affected SELECT 務(wù) FROM sakila.citi/ where cit = JOttawa1 ；S city_id ! city ! country_id ；7 : Ottawa :31 pow in set C0.00 sec)CH；使用情況17show profile cpu for query 3；! Status! Duration ! CPUuser ! CPUsystem ! startingi checkinsOpening tablesin it! 0.0

15、00099 permissions 0.0000125 0.0000355 0.000060i System lockI optinizing! statistics；0.000013；0.000022；0.000198I preparing；executingi Sending data ；end；0.000024；0.000005；0.000072；0.000007I query end:closing tables:fpeeing items:cleaning up；0.000012:0.000013:0.000080:0.0000200.000000 !0.000000 50.0000

16、00 !0.000000 50.000000 ；0.000000 ；0.000009 I0.000000 ；0.000000 ；0.000000 ；0.000000 I0.000000 ；0.000000 :0.000003 :0.000000 :15 p&us in set 1 warning show proflie block io for query 3；I Statusi Duration : Blockops_in JBlock_OpS_OLlt Istartingchecking permissions Opening tables in itSystem lock optimi

17、zing statistics9 29 1010preparing executing Sending data end005172queiy end closing tables freeing items cleaning up2 3 0 0118 2 0101010NULL ! NULL ! NULL !NULL ! NULL !NULL ! NULL !NULL ! NULL !NULL !NULL ! NULL !NULL ! NULL !NULL ；NULL ! NULL ! NULL ! NULL ! NULL ! NULL ! NULL ! NULL ! NULL ! NULL

18、 ! NULL ! NULL ! NULL ! NULL ! NULL 15 rows in set, 1 warning 優(yōu)化前后對(duì)比:mysql show prof iles；! Query_ID ! Duration ! Quers?1 !0.00162350!SELECT * FROM sakila.city where city = 9 Ottawa!2 !007259950!alter table sakila.city add index!3 !0T00066925j!SELECT * FROM sakila.city where city = 9 Ottawa!3 rows i

19、n set, 1 warning 2. 盡母不使用通配符化盡量不用通配符*來(lái)進(jìn)行查詢(xún)。山于使用通配符*會(huì)取出所有的列，加大了 數(shù)據(jù)庫(kù)的匚作量。所以?xún)?yōu)化的原則為：需要哪列就取哪列，不要為了方便而加 大數(shù)據(jù)庫(kù)的壓力。例：查詢(xún)表country有哪些國(guó)家優(yōu)化前：語(yǔ)句：SELECT * FROM ;結(jié)果截屏:mys：qlSELECT* FROM s-aila.country；,A*! coiunti*y_id! country11-r-rrr-r丁 T111! China11112! Australia11113! Canada1114! England1 115i France11116! Germa

20、ny11117! Italy1118! Japan1 119i Russia111110J Turkey1111li! USA111112Spain111113Sv/eden111114! Switzerland111115S Denmark111116S Egi/pt111117i Mexico111118；Thailand111119S Poland1111-20S Belgium11,亠十20 rowsin set 0-0? sec)TCPU使用情況:show profile cpu for query 1 ；-+4Statusi Duration-r-rj-r-r-r-T-riI1a

21、_GPU.user ；GPU_systemstarting ；0.000100110.000000 ；乩000000checking permissions；0.000015110.000000 1乩000000Opening tablesS 0.000046110000000 ；乩000000in it1 0.000032110.000000 ；0.000000System lock；B.000019110.000000 ；0.000000optimi2ing! 0.07334111a.000000 !0.000000statistics! 0.00004411a.300000 :0.000

22、000ppepacing! 0.000026110.000000 :0.000000executing! 0.00000G11a.000909 !0.000000Sending data! 0.000183119.900030 10.000000end! 0.000013110000000 !0.000000query end! 0.00001?11a.000000 s0.000000closing tables! 0.00003511a.000000 s0.000000fi*eeing items! 0.00017?110000000 !0.000000cleaning up! 0.0000

23、72-4110000000 !0.00000015 rovs in set. 1 v/arnins Io使用情況:mysql show profile block io for* query 1 ；Sta.tus亠3)urat io nBlocls_ops_in S. _, ， , x Block_ops_oi.itstciF tingi0.000100iNULL !NULLchecking permissions：0.000015NULL !NULLOpening tables0000046 NULL 5NULLin it0.000032 iNULL ；NULLSystem lock0.00

24、0019i NULL !NULLopt imizing0.073341NULL !HULLstatistics0.000044NULL !NULLpreparing0.000026NULL ；NULLexecuting0.000006 iNULL 1NULLSending data0.000183NULL !NULLend0.000013NULL !NULLquery end 0000017 NULL !NULLclosing tables0.008035 iNULL ；NULLfreeing items0.000177NULL ；NULLcleaning up0.000072i NULL !

25、NULL15 rows in set, 1 warning SELECT country FROM sakila-country；4+ counthy!China!Aus 七 r* 日 Ha!Canada!England!France!Germany!Italyi Japan!Russia!Turkey!USA!Spain!SSv/edenS；Swit乙巳Eland ； SDenmark；!Egypt；SMexicoS；Thailand；Poland；!Belgium； 20 rows in set shaw prof ile cpu for query 2 ；*Statusi Durat i

26、o n-r-r-r-r -r-.i iGPU_use：r i _CPU_systemstartingT；0.000106T -10&a10-000000checking permissions；0.00001311D000009i10-000000Opening tablesS 0.000036110L &i10.000000in it:0.0000321 i0.000000i i0-000000System lock:0.000013110L &Qi i0.000000optimizing! 0.000004110&1 i0.000000statistics! 0.0000211100000

27、001i0.000000preparing! 0.00001411.0000001i0-000000executing! 0.0000021100000001 i0-000000Sendincf data! 0.0000731100000001 i0-000000end! 0.000008110.000000110.000000query end! 0.000010110.000000110.000000closing tablesS B.000009110.000000i18.000000freeing items1 0.00006011i10.000000cleaning up；0.000

28、015-+11+ -0.&Q&i1-t-乩00000015 rows in set. 1 warningsec)Io使用情況:mysql show profile block io or quer 2；! Status! Durat ion ! Block_ops_in ! Block_ops_out ! statt inff1 0.000106 11NULL 11NULL；checking pernissions 1i 0.000813 1:NULL 1:NULL! Opening tables1 0.00003G 11NULL 11NULLin it1! 0000032 :1NULL :1

29、NULL! System lock:0.000013 :NULL :NULL! optimizing1 8000004 :NULL 1:NULL；statistics:8.000021 1:NULL 11NULL! preparing11 0000014 11NULL 11NULLi executing11 0.000002 11NULL 11NULL! Sending data1 0.000073 11NULL 11NULLS end1:0.000008 1:NULL 1:NULL! quepy end1 0000010 11NULL 11NULL! closing tables:00000

30、09 1:NULL 1:NULL! Freeing items:0000060 :NULL :NULL! clean incf up! 8000015 1!NULL :NULL+15 rows in set, 1 wa.rni.ng 優(yōu)化前后執(zhí)行時(shí)間對(duì)比，截圖如下:nys：ql show prof iles ；! Quepy_ID : Duration ! Querj;1 ! 0.07412375 ! SELECT * FROM sakila.mimWy2 i 0.00041425 ! SELECT counti*y FROM sakila.counlti-y2 rows in set,. 1

31、 v/arning 3. 用連接?xùn)嗽?xún)替代子?xùn)嗽?xún)。因?yàn)樽硬樵?xún)會(huì)多次遍歷表中所有的數(shù)據(jù)(視你的子查詢(xún)的層次而定)，而 連接查詢(xún)只會(huì)遍歷一次。例：查找中國(guó)的所有城市的信息優(yōu)化前：語(yǔ)句：Select * from where country_id in(Select country_id from where country = 4China);結(jié)果截屏：mysql Select * from sakila. city where country_id. in Select counthy_id from sakila .couwhei*e country =, Chinas ；! country_i

32、d !1 ! Beijing !2 ! Shanghai !3 ! Guangzhou !3 rows in set CPU使用情況:pysql show ppofile cpu for query 1；Sta七usii_ i _Dura七 ioni iCPU_us：ei*_ _CPll_systemstar七ing-r-110000080T -110000900WI0.000000checking permissions110.000005110.000000110.000000cJiecking permissions110.000005110.000000 I0.000000Openin

33、g tables110.000031110.000000I10.000000in it110.00005110.00000010.000000Ssrstem lock110.000011110.00000010.000000optimizing110000013110 000000 I0-000000statistics110.000034110.00000010000000preparing11000001810L 00000010.000000executing11000000311D0000091乩000000Sending data11000017710L 0000000.000000

34、end110.0000041 i0.0000000-000000quepy end110000009110L 903000I I0.000000closing tables110000010110090999I0.000000freeing items110000061110000009I0-000000cleaning up1I-4-0.000015110.000000I-fr-0-00000016 rows in setr 1 warning 0.00 sec Io使用情況:hysql show profile block io for quer 1；Status! Dur at ion

35、: Block_ops_in : Block_ops_out :start ing11 0.0000S0 11NULL 1!NULLchecking permissions 1:0.000005 :!NULL :!NULLchecking permissions 11000005 1!NULL !NULLOpening tables1 0.000031 1:NULL 1iNULLin it1:0.000051 11NULL 1iNULLSystem lock1 0.000011 11NULL 1!NULLopt imizing1 0.000013 11NULL 11NULLstatistics

36、! 0.000034 1:NULL :NULLpreparing:0.000018 :!NULL :!NULLexecuting1 0.000003 1:NULL 1iNULLSending data:0.000177 1:NULL 1iNULLend:0.000004 :NULL :NULLquery end1 0.000009 11NULL 1!NULLclosing tables1 0.000010 :NULL I:NULLfreeing items! 0.000061 1:NULL :NULLclean ing up:0.000015 :!NULL :!NULL16 rows in s

37、et1 warning Se lect * ftom saklla. c it y inner Join Se lect countt*yid f rom sakila .country where count i*yChina-country on city. connid = count.counti*y_id.；4! city_ida _-+-i city-*i country._id-+5 country.-TT-rT-r._idT- 1T-11Beijing寸I1i寸111 211ShanghaiI Ii111S311-+-Guang乙hou1-111-+13 rov?s in se

38、t CPU使用情況:R5rs：ql show profile cpu for query 2 ；Status Duration CPU_user CPUstarting0.0000660.000000! checking permissions0.0000030.000000；Gheckirig permissions0.0000040.000000；Opening tables0.0000630.000000S in it0.0000130.0000001 Ssrstem lock0.0000080.000000；optinizing0.0000030.000000；aptinizing0.

39、00000?0000000i statistics0.00001?00000001 preparing00000120.000000i statistics0.0000200000000；preparing0-0000060000000i executing0.0000120000000S Sending data00000270000000；executing0.0000B30.0000001 Sending data0.0000540000000S end0-0000020000000S query end00000060000000i closing tables0.0000610.000000: penouing tmp table0.0000050000000! closing tables0.0000050000000! fpeeing items0.0000450.000000! cleaning up0.00001Q000000023 pow& in set. 1 warning show proflie block io for query 2:! Status! Duratio