計(jì)算機(jī)軟件工程外文翻譯

1、彼得羅薩尼大學(xué)經(jīng)濟(jì)學(xué)年鑒,2010,X（4）,201-214指導(dǎo)MYSQL優(yōu)化查詢(xún)的策略摘要：本文探討了MySQL勺索引能力。它首先回顧索引的工作原理以及結(jié)構(gòu)。接下來(lái)，它會(huì)回顧每個(gè)主要MySQ散據(jù)存儲(chǔ)引擎特有的索引功能。然后，本文將檢查各種各樣的索引可能有助于加快應(yīng)用程序的情況。除了檢查索引如何幫助。在本文中，我們提供索引使用類(lèi)型：B-樹(shù)，散列和位圖，以?xún)?yōu)化查詢(xún)，盡管MySQL已經(jīng)實(shí)現(xiàn)和索引寬敞的R樹(shù)。索引類(lèi)型對(duì)應(yīng)于用于實(shí)現(xiàn)索引的特定種類(lèi)的內(nèi)部算法和數(shù)據(jù)結(jié)構(gòu)。在MySQL中，對(duì)特定索引類(lèi)型的支持取決于存儲(chǔ)引擎。關(guān)鍵詞：優(yōu)化數(shù)據(jù)庫(kù)結(jié)構(gòu)，數(shù)據(jù)庫(kù)性能，優(yōu)化查詢(xún)，索引，B樹(shù)索引，哈希索引，位圖索引，存

2、儲(chǔ)引擎關(guān)于MYSQL據(jù)庫(kù)索引的考慮數(shù)據(jù)庫(kù)調(diào)優(yōu)是通過(guò)最小化響應(yīng)時(shí)間（完成語(yǔ)句所需的時(shí)間）和最大化數(shù)據(jù)庫(kù)可以每秒同時(shí)處理的語(yǔ)句數(shù)量來(lái)提高數(shù)據(jù)庫(kù)性能的過(guò)程（Schwarty等，2004）調(diào)整是由DBA,數(shù)據(jù)庫(kù)設(shè)計(jì)人員，應(yīng)用程療：設(shè)計(jì)人員和數(shù)據(jù)庫(kù)用片共同執(zhí)行的團(tuán)隊(duì)練習(xí)，對(duì)數(shù)據(jù)庫(kù)的理解。調(diào)整兩者都取決于和影響如下：表格設(shè)計(jì)，關(guān)系，索引設(shè)計(jì)和其他組件;查詢(xún)?cè)O(shè)計(jì)，數(shù)據(jù)讀取大小和檢索和執(zhí)行順序;讀取和插入/更新/刪除操作的性質(zhì)和頻率;數(shù)據(jù)庫(kù)服務(wù)器和客戶(hù)端之間的工作分區(qū);將表，索引或部分加載到內(nèi)存中的時(shí)序，事件和效果;語(yǔ)句的并發(fā)特性。數(shù)據(jù)庫(kù)性能成為存在大量數(shù)據(jù)，復(fù)雜查詢(xún)，處理大量數(shù)據(jù)的查詢(xún)，長(zhǎng)時(shí)間運(yùn)行查詢(xún)，鎖定

3、每個(gè)其他數(shù)據(jù)的查詢(xún)，大量同時(shí)事務(wù)，大量用戶(hù)和有限帶寬的重要問(wèn)題。一般來(lái)說(shuō)，大多數(shù)數(shù)據(jù)庫(kù)系統(tǒng)都是為了表現(xiàn)良好而設(shè)計(jì)的。最初的設(shè)計(jì)階段可以實(shí)現(xiàn)最佳的改進(jìn)，但是有時(shí)候數(shù)據(jù)庫(kù)的特性還沒(méi)有足夠的信息。后來(lái),在生產(chǎn)用途中改變大型數(shù)據(jù)庫(kù)可能是昂貴的，實(shí)際的考慮對(duì)可以改變的內(nèi)容給予約束。調(diào)整可以使查詢(xún)之間的差異需要毫秒或兒分鐘甚至更多的執(zhí)行。數(shù)據(jù)庫(kù)系統(tǒng)是管理信息系統(tǒng)的核心，基于數(shù)據(jù)庫(kù)的在線(xiàn)交易處理（OLTP）和在線(xiàn)分析處理（OLAP）是銀行，商業(yè)，政府等部門(mén)最重要的計(jì)算機(jī)應(yīng)用（Viliams&Lane,2007）。從大多數(shù)系統(tǒng)的應(yīng)用來(lái)看，查詢(xún)操作在各種數(shù)據(jù)庫(kù)操作中占用的最大，而查詢(xún)操作是基于SELECT吾句的

4、SQL語(yǔ)句中的一個(gè)語(yǔ)句成本最大的。例如，如果數(shù)據(jù)M在一定程度上累積，如銀行賬號(hào)到數(shù)據(jù)庫(kù)表中的信息就會(huì)累積數(shù)白萬(wàn)甚至數(shù)千萬(wàn)條記錄，全表掃描通常需要兒十分鐘的時(shí)間，棋至兒個(gè)小時(shí)。如果比全表掃描查詢(xún)策略更好，往往可以減少到兒分鐘來(lái)進(jìn)行查詢(xún)，我們可以看到查詢(xún)優(yōu)化技術(shù)的重要性。很多程序員認(rèn)為查詢(xún)優(yōu)化是一個(gè)DBMS（數(shù)據(jù)庫(kù)管理系統(tǒng)）的任務(wù)，用程序員編寫(xiě)的與SQL語(yǔ)句沒(méi)有什么關(guān)系，這是錯(cuò)誤的。良好的查詢(xún)計(jì)劃性能往往可以提高次數(shù)。查詢(xún)訃劃是山用片提交的SQL語(yǔ)句集合，查詢(xún)計(jì)劃經(jīng)過(guò)優(yōu)化處理后的語(yǔ)句收集生成。DBM旌詢(xún)計(jì)劃處理過(guò)程如下：在查詢(xún)后的詞法，語(yǔ)法檢查中，該語(yǔ)句將提交給DBMS勺查詢(xún)優(yōu)化器，優(yōu)化器經(jīng)過(guò)代數(shù)

5、優(yōu)化和優(yōu)化訪(fǎng)問(wèn)路徑后跟預(yù)編譯模塊處理語(yǔ)句并生成查詢(xún)，規(guī)劃，然后在正確的時(shí)間對(duì)系統(tǒng)實(shí)現(xiàn)，最終的結(jié)果將返回給用戶(hù)。在實(shí)際的數(shù)據(jù)庫(kù)產(chǎn)品（如Oracle,Sybase等）都是基于成本優(yōu)化方法的所有版本，這種從系統(tǒng)中優(yōu)化的字典基于信息表來(lái)估計(jì)不同成本的規(guī)劃查詢(xún)，以及然后選擇一個(gè)更好的規(guī)劃（Opell,2006）o雖然在數(shù)據(jù)庫(kù)查詢(xún)優(yōu)化方面已經(jīng)做得更好，但是山用尸根據(jù)SQL語(yǔ)句提交給系統(tǒng)的優(yōu)化，很難想象出原來(lái)的查詢(xún)計(jì)劃在系統(tǒng)優(yōu)化后變得有效，所以寫(xiě)說(shuō)明用戶(hù)的優(yōu)缺點(diǎn)至關(guān)重要。系統(tǒng)我們沒(méi)有討論查詢(xún)優(yōu)化，著重于以下計(jì)劃來(lái)改進(jìn)用戶(hù)查詢(xún)解決方案像美麗一樣，最有吸引力的索引策略非常注重持之以恒。對(duì)于主要，連接和過(guò)濾鍵（一

6、個(gè)通用的索引美容標(biāo)準(zhǔn)，也許？），索引是適用的，應(yīng)用程序A可能是錯(cuò)誤的方法。應(yīng)用程序A可能是一個(gè)事務(wù)系統(tǒng)，可以支持與數(shù)據(jù)庫(kù)的成千上萬(wàn)的快速交互，其數(shù)據(jù)修改必須以毫秒為單位進(jìn)行。應(yīng)用程序B可能是決策支持系統(tǒng)，用片可以在其中創(chuàng)建大量服務(wù)器查詢(xún)。這兩個(gè)應(yīng)用程序需要非常不同的索引策略。MySQL勺優(yōu)化器總是嘗試使用手頭的信息來(lái)開(kāi)發(fā)最有效的查詢(xún)計(jì)劃。然而，要求隨時(shí)間而變化;用戶(hù)和應(yīng)用程序可以隨時(shí)引入不可預(yù)測(cè)的請(qǐng)求。這些請(qǐng)求可能包括新的事務(wù)，報(bào)告，集成等。索引是您加快查詢(xún)最重要的工具。您也可以使用其他技術(shù)，但通常最大的區(qū)別就是正確使用索引。在MySQLSB件列表中，人們經(jīng)常要求幫助使查詢(xún)運(yùn)行更快。在令人驚訝

7、的情況下，表中沒(méi)有索引，添加索引通常會(huì)立即解決問(wèn)題。它并不總是這樣工作，因?yàn)閮?yōu)化并不總是簡(jiǎn)單。不過(guò)，如果您不使用索引，在許多情況下，您只是浪費(fèi)時(shí)間嘗試通過(guò)其他方式提高性能。首先使用索引獲得最大的性能提升,然后查看其他技術(shù)可能有幫助。索引實(shí)現(xiàn)的具體細(xì)節(jié)因不同的MySQ存儲(chǔ)引擎而異。例如，對(duì)于MylSAM表，表的數(shù)據(jù)行保存在數(shù)據(jù)文件中，索引值保存在索引文件中。表上可以有多個(gè)索引，但它們都存儲(chǔ)在同一個(gè)索引文件中。索引文件中的每個(gè)索引都包含用于快速訪(fǎng)問(wèn)數(shù)據(jù)文件的關(guān)鍵記錄的排序數(shù)組。相比之下，BD環(huán)口InnoDB存儲(chǔ)引擎不會(huì)以相同的方式分離數(shù)據(jù)行和索引值，盡管它們將索引維護(hù)為排序值集合。默認(rèn)情況下，BD

8、B引擎使用每個(gè)表中的單個(gè)文件來(lái)存儲(chǔ)數(shù)據(jù)和索引值。InnoDB引擎使用單個(gè)表空間來(lái)管理所有InnoDB表的數(shù)據(jù)和索引存儲(chǔ)。IrmoDB可以配置為使用自己的表空間創(chuàng)建每個(gè)表，但即使如此，表的數(shù)據(jù)和索引也存儲(chǔ)在同一表空間文件中。MySQL以多種方式使用索引。如上所述，索引用于加速搜索與WHER孑句匹配的行或執(zhí)行連接時(shí)與其他表中的行匹配的行。對(duì)于使用()或MAX()函數(shù)的查詢(xún)，可以快速找到索引列中的最小或最大值，而不檢查每一行。MySQLg?？梢允褂盟饕龑?duì)ORDERBY和GROUPB件句進(jìn)行快速排序和分組操作。MylSAM表中的MySQL 從索引文獲得。沒(méi)有理有時(shí)，MySQlM以使用索引來(lái)讀取查詢(xún)所需

9、的所有信息。假設(shè)您從索引數(shù)字列中選擇值，而不是從表中選擇其他列。在這種情況下，當(dāng)件中讀取索引值時(shí)，它將獲得與該索引值相同的值將通過(guò)讀取數(shù)據(jù)文件由讀取值兩次，所以甚至不需要查詢(xún)數(shù)據(jù)文件(Dubois,2008)o一般來(lái)說(shuō)，如果MySQL夠找出如何使用索引來(lái)更快地處理查詢(xún)，那么它會(huì)。有缺點(diǎn)。時(shí)間和空間都有成本。在實(shí)踐中，這些缺點(diǎn)往往被優(yōu)勢(shì)所超越，但你應(yīng)該知道它們是什么。首先，索引加快檢索速度，但是減慢插入和刪除速度，以及更新索引列中的值。也就是說(shuō)，指數(shù)減緩了涉及寫(xiě)作的大多數(shù)操作。發(fā)生這種情況是因?yàn)閷?xiě)入記錄需要不僅寫(xiě)入數(shù)據(jù)行，還需要更改任何索引。表的索引越多，需要做的更多變化，平均性能下降越大(Du

10、bois,2008)o其次，索引占用磁盤(pán)空間，多個(gè)索引占用相應(yīng)的空間。這可能會(huì)導(dǎo)致比沒(méi)有索引更快地達(dá)到表大小限制：對(duì)于MylSAM表，對(duì)其進(jìn)行高度索引可能會(huì)導(dǎo)致索引文件比數(shù)據(jù)文件更快地達(dá)到其最大大小。對(duì)于將數(shù)據(jù)和索引值存儲(chǔ)在同一文件中的BDB表，添加索引會(huì)使表格更快地達(dá)到最大文件大小。位于InnoDB共享表空間內(nèi)的所有InnoDB表都會(huì)競(jìng)爭(zhēng)相同的公共空間池，并且添加索引會(huì)更快地在此表空間內(nèi)刪除存儲(chǔ)空間。但是，與MylSAM和BDB表使用的文件不同，InnoDB共享表空間不受操作系統(tǒng)的文件大小限制的約束，因?yàn)樗梢耘渲脼槭褂枚鄠€(gè)文件。只要您有額外的磁盤(pán)空間，您可以通過(guò)向其添加新組件來(lái)擴(kuò)展表空間。

11、使用單個(gè)表空間的InnoDB表格與BDB表格相同，因?yàn)閿?shù)據(jù)和索引值一起存儲(chǔ)在單個(gè)文件中。AnnalsoftheUniversity7ofPetrosaniEconomics,2010,X(4):201-214.INDEXINGSTRATEGIESFOROPTIMIZINGQUERIESONMYSQLANCAMEHEDINTU,CERASELAPIRVU,CRISTIETEGANABSTRACT:ThisarticleinvestigatesMySQLsindexcapabilities?Itbeginsbyreviewinghowindexeswork,aswellastheirstructu

12、re?Next,itreviewsindexingfeaturesspecifictoeachofthemajorMySQLdatastorageengines?Thisarticlethenexaminesabroadrangeofsituationsinwhichindexesmighthelpspeedupyourapplication?Inadditiontoexamininghowindexescanbeofassistance?Inthisarticlewepresentindexusagetype:B-trees,hashandbitmap,inordertooptimizequ

13、eries,althoughMySQLhasimplementedandindexesspaciousR-trees?Theindextypecorrespondstotheparticularkindsofinternalalgorithmsanddatastructuresusedtoimplementtheindex?InMySQL,supportforaparticularindextypeisdependentuponthestorageengine?KEYWORD：SOptimizingdatabasestructure,Databaseperformance,Optimizing

14、queries,Indexes,B-treeIndex,HashIndex,BitmapIndex,StorageenginesCONSIDERATIONSONTHEMYSQLDATABASEINDEXINGDatabasetuningistheprocessofimprovingdatabaseperformancebyminimizingresponsetime(thetimeittakesastatementtocomplete)andmaximizingthroughputthenumberofstatementsadatabasecanhandleconcurrentlypersec

15、ond)(Schwarty,etal?,2004).Tuningisateamexercise-collectivelyperformedbyDBAs,databasedesigners,applicationdesignersanddatabaseusers,basedunderstandingofthedatabase?Tuningbothdependsonandimpactsthefollowing:tabledesign,relationships,indexdesign,andothercomponents;querydesign,sizeofdatareadandretrieved

16、,andorderofexecution;natureandfrequencyofreadandinsert/update/deleteoperations;partitioningoftheworkbetweentheDatabaseserverandtheclient;timing,eventsandeffectofloadingtables,indexesorpartsthereofintomemory;concurrencycharacteristicsofstatements?Databaseperformancebecomesanimportantissueinthepresenc

17、eoflargeamountsofdata,complexqueries,queriesmanipulatinglargeamountsofdata,longrunningqueries,queriesthatlockeveryoneelseout,largenumberofsimultaneoustransactions,largenumbersofusersandlimitedbandwidth?Ingeneral,mostdatabasesystemsaredesignedforgoodperformance?Thebestimprovementscanbeachievedinthein

18、itialdesignphasebutsometimesnotenoughinformationisavailableaboutthecharacteristicsofadatabase?Later,alteringalargedatabaseinproductionusecanbeexpensiveandpracticalconsiderationsputconstraintsonwhatcanbechanged?Tuningcanmakethedifferencebetweenaquerytakingmillisecondsorminutesorevenmoretoexecute?Data

19、basesystemisthecoreofmanagementinformationsystems,databasebasedonlinetransactionprocessing(OLTP)andonlineanalyticalprocessing(OLAP)isabanking,business,governmentandotherdepartmentsofthemostimportantoneofcomputerapplications(Williams&Lane,2007)?Fromtheapplicationofmostsystems,thequeryoperationinavari

20、etyofdatabaseoperationsinthelargestoccupied,andthequeryoperationisbasedontheSELECTstatementintheSQLstatementisastatementofthecostofthelargest?Forexample,iftheamountofdataaccumulatedtoacertainextent,suchasabankaccounttothedatabasetableofinformationontheaccumulationofmillionsoreventensofmillionsofreco

21、rds,fulltablescanoftenrequirestensofminutestime,andevenafewhours?Ifbetterthanthefulltablescanquerystrategycanoftenbereducedtoafewminutestomakeinquiries,wecanseetheimportanceofqueryoptimizationtechnology?ManyprogrammersthinkthatqueryoptimizationisaDBMS(databasemanagementsystem)tasks,preparedwiththepr

22、ogrammerhaslittletodowithSQLstatement,whichiswrong?Agoodqueryplanperformanceoftencanimprovethenumberoftimes?QueryplanissubmittedbyusersacollectionofSQLstatements,queryplanisoptimizedtodealwiththestatementafterthecollectionofproduce?DBMSqueryplantodealwiththeprocessisasfollows：inthequeryafterthelexic

23、al,syntaxcheck,thestatementwillbesubmittedtotheDBMS*squeryoptimizer,optimizerafteralgebraicoptimizationandoptimizationofaccesstothepathfollowedbypre-compiledmodulesprocessingofstatementsandgenerateinquiries,planning,andthenattherighttimetothesystemimplementation,thefinalresultswillbereturnedtotheuse

24、r?Intheactualdatabaseproducts(suchasOracle,Sybase,etc?)areallversionsofthehighcost-basedoptimizationmethod,thisoptimizationofthedictionaryfromthesystembasedontheinformationtabletoestimatethedifferentcostsofplanninginquiries,andthenselectabetterplanning(Opell,2006)?Whileitisinthedatabasequeryoptimiza

25、tionhasbeendonebetter,butbytheuseroftheSQLstatementsubmittedtothesystembasedonoptimization,itisdifficulttoimagineaworseoriginalqueryplanafterthesystemhasbecomeefficientafteroptimization,sowrittenstatementoftheadvantagesanddisadvantagesofusersisessentia1?Systemwedidnotdiscussqueryoptimization,focusin

26、gonthefollowingplantoimprovetheusersquerysolution?Likebeauty,themostattractiveindexingstrategyisverymuchintheeyeofthebeholder?Afterindexesareinplaceforprimary,join,andfilterkeys(auniversalstandardofindexingbeauty,perhaps?),whatworksforapplicationAmightbethewrongapproachforapplicationB?ApplicationAmi

27、ghtbeatransactionalsystemthatsupportstensofthousandsofquickinteractionswiththedatabase,anditsdatamodificationsmustbemadeinmilliseconds?ApplicationBmightbeadecisionsupportsysteminwhichuserscreateanampleassortmentofserver-hoggingqueries?Thesetwoapplicationsrequireverydifferentindexingtactics?MySQLsopt

28、imizeralwaystriestousetheinformationathandtodevelopthemostefficientqueryplans?However,requirementschangeovertime;usersandapplicationscanintroduceunpredictedrequestsatanypoint?Theserequestsmightincludenewtransactions,reports,integration,andsoforth?Indexingisthemostimportanttoolyouhaveforspeedingupque

29、ries?Othertechniquesareavailabletoyou,too,butgenerallytheonethingthatmakesthemostdifferenceistheproperuseofindexes?OntheMySQLmailinglist,peopleoftenaskforhelpinmakingaqueryrunfaster?Inasurprisinglylargenumberofcases,therearenoindexesonthetablesinquestion,andaddingindexesoftensolvestheproblemimmediat

30、ely.Itdoesntalwaysworklikethat,becauseoptimizationisntalwayssimple?Nevertheless,ifyoudon，tuseindexes,inmanycasesyou*rejustwastingyourtimetryingtoimproveperformancebyothermeans?Useindexingfirsttogetthebiggestperformanceboostandthenseewhatothertechniquesmightbehelpful?Theparticulardetailsofindeximplem

31、entationsvaryfordifferentMySQLstorageengines?Forexample,foraMyISAMtable,thetable，sdatarowsarekeptinadatafile,andindexvaluesarekeptinanindexfile?Youcanhavemorethanoneindexonatable,butthey*reallstoredinthesameindexfile?Eachindexintheindexfileconsistsofasortedarrayofkeyrecordsthatareusedforfastaccessin

32、tothedatafile?Bycontrast,theBDBandInnoDBstorageenginesdonotseparatedatarowsandindexvaluesinthesameway,althoughbothmaintainindexesassetsofsortedvalues?Bydefault,theBDBengineusesasinglefilepertabletostorebothdataandindexvalues?TheInnoDBengineusesasingletablespacewithinwhichitmanagesdataandindexstorage

33、forallInnoDBtables?InnoDBcanbeconfiguredtocreateeachtablewithitsowntablespace,butevenso,atable*sdataandindexesarestoredinthesametablespacefile?MySQLusesindexesinseveralways?Asjustdescribed,indexesareusedtospeedupsearchesforrowsmatchingtermsofaWHEREclauseorrowsthatmatchrowsinothertableswhenperforming

34、joins?ForqueriesthatusetheMINOorMAX0functions,thesmallestorlargestvalueinanindexedcolumncanbefoundquicklywithoutexaminingeveryrow?MySQLcanoftenuseindexestoperformsortingandgroupingoperationsquicklyforORDERBYandGROUPBYclausesSometimesMySQLcanuseanindextoreadingalltheinformationrequiredforaquery.Suppo

35、sethatyou*reselectingvaluesfromanindexednumericcolumninaMylSAMtable,andyou，renotselectingothercolumnsfromthetable?Inthiscase,whenMySQLreadsanindexvaluefromtheindexfile,itobtainsthesamevaluethatitwouldgetbyreadingthedatafile?There*snoreasontoreadvaluestwice,sothedatafileneednotevenbeconsulted(Dubois,

36、2008)?Ingeneral,ifMySQLcanfigureouthowtouseanindextoprocessaquerymorequickly,itwil1.Thereareanddisadvantages?Therearecostsbothintimeandinspace?Inpractice,thesedrawbackstendtobeoutweighedbytheadvantages,butyoushouldknowwhattheyare?First,indexesspeedupretrievalsbutslowdowninsertsanddeletes,aswellasupdatesofvalue