MySQL查询优化的研究和改进

华中科技大学硕士学位论文MySQL查询优化的研究和改进姓名：孙辉申请学位级别：硕士专业：计算机软件与理论指导教师：吴恒山20070606IMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLSQLMySQLMySQLINNOTMySQLMySQLMySQLMySQLTPCH10MMySQLIIAbstractQueryisoneofthebasicandcommonlyusingoperationsinDBMS.SowhetherthequeryhasthefastexecutionspeedhasbecomeacoreproblemfortheusersanddesignersofDBMS.Wemainlyfocusonfourmainproblems:MySQLparametersself-tuning,realizationofMySQLqueryreuse,MySQLquerytransformandtheoptimizationofqueryexecutionplaninthebaseoftheresearchontheopensourceDBMSofMySQL.Fortheproblemofparameterstuning,wefocusonthedatabufferandlogbuffertointroducesometuningparametersofMySQL.AndweintroducetwotuningwaysofMySQL:manualtuningandbasedoncasestuning,Thenwewillproposeanewdynamicself-tuningmethods—mountainalgorithmanditsolvestheformerfault.Intheproblemofqueryreuse,weaimattwoproblems—poorlyreuseandunabletodealwithbigresultsituationintheMySQLanddosomeimprovements.Fortheformer,weaddtwofunctionstonormalizethekeywordsandremovesomeinvalidandredundantcharactersintheSQLstatements.Forthelatter,webufferthequeryexecutionplaninsteadofbufferingthequeryresultbyaddingexecutionplanbuffermodule.Fortheproblemofqueryrewrite,wededucethealgorithmofsub-querymerging.Then,weaddtworewriterules—NOToperationrewriteandOuterjointransformstonormaljoin.Bythetransforming,thequeryexecutionspeedreallybecomesfast.Fortheproblemofplanoptimization,wemainlyfocusontwoproblems—baseonruleoptimizationandbaseoncostoptimization.Fortheformer,weintroducesomeredefinejointypesandtheirpriority.Forthelatter,wepresentthegreedyalgorithmwhichdecidestheorderoftables.Alltheexperimentsbaseonthebenchmarktest—TPCHandthetestdatasizeis10M.TheexperimentalresultsobtainedfromthetestsindicatethatourworkreallyeffectlyimprovethespeedofqueriesinMySQL.KeywordsQueryoptimize,Queryreuse,Querytransform,Planoptimization200766_____200766200766111.1863DM5MySQLDM5(DatabaseManagementSystemDBMS)DM[1,2]MySQLSQLC/S[3,4]MySQLMySQLDM521.21.2.11(DatabaseAdministrators,DBA)DBADBA[5,6]DBA[7,8][9]DBMS()2()[10,11,12](1)SQLSQL(2)3SQL3IBMAlmadenStarburst[13][14,15](1)(2)(3)(4)wherehavingwherehavingwherehaving[16,17]4JOIN[18,19]4SYSTEMRPostgres[20]PostgresDBMS[21,22]5(On-lineTransactionProcessingOLTP)(SELECT)(PROJECT)(JOIN)SPJSPJ[23](On-lineAnalyticalProcessingOLAP)(DecisionSupportSystemDSS)SPJGROUPBYGROUPBYSPJSPJSPJ[24]SPJSPJ(GROUPBYPUSHDOWN,PULLUP)SPJSPJ[24,25]6(1)CPU+I/O5[26](2)[27](3)1.2.21OracleOracleOracleOracle[29,30]Oracle(1)(2)(3)OracleOracle(4)OracleOracleOracleOracleCPUI/OCPUI/O6Oracle2IBMDB2DB2IBMDB2[29,30]IBMDB2(1)(2)(3)()DB2()DB2CPUI/O()CPUI/O3[31-34]OpenBASEDMOpenBASEDMDMI/O71.3MySQLMySQLDM51MySQLMySQLMySQLMySQLMySQLMySQL2MySQLMySQLMySQLSQLMySQLMySQL3MySQLMySQLInMySQLNOTMySQL4MySQLMySQLMySQLTPCH10MP3800Hz,256MBMySQL82MySQLMySQLMySQLMySQL2.1MySQLMySQL[3]MySQLjoin_buffer_size()128kBquery_cache_size()0I/O2.2DBMSBrown[35]9Xu[36]ChaudhuriNarasayyaDBMS[37]DBMSMySQLMySQL2.2.1MySQL()MySQLI/O1key_buffer_size4GB25%8MB2join_buffer_size128KB3read_buffer_size60KB4sort_buffer_sizeORDERBYGROUPBY256KB105tmp_table_size20MB6query_cache_sizeMySQLMySQL02.2.2MySQLI/OI/O1binlog_cache_sizeSQLI/O32KB2sync_binlogNN0MySQL2.3MySQL2.3.1MySQL[3,4]1MySQLMySQLDBADBMS11DBADBA2MySQLMySQLMySQL2.3.2DBMSH(h1,h2,h3)h1h2h3V=(C1,C2,C3)C1012C212C391011VV=(1,2,11)C1V=(0,2,11)V=(2,2,11)C2V=(1,1,11)C3V=(1,2,10)V=(1,2,9)SQLSQL122.1step1:step2flagstep3do{flag;=;step4for(i=1;i=n;i++){step5if(i){if(){==flag}}step6elseif(i){if(){=13=flag}}step7else;}step8:while()step9:return;MySQL2.4MySQLTPCHQ4Q7Q121key_buffer_sizeread_buffer_sizesort_buffer_size212.13query_cache_size55112.11key_buffer_sizeread_buffer_sizesort_buffer_size8MB64KB256KB30MB8MB16MB142.21Q4Q7Q120.0425s0.0575s0.6900s0.0325s0.0350s0.4050s1Q4Q7Q12(10M)2222.1key_buffer_size[4MB,32MB]512KBread_buffer_size[64KB,4MB]256KBsort_buffer_size[256KB,2MB]64KB2.12.32key_buffer_sizeread_buffer_sizesort_buffer_size8MB64KB256KB4MB832KB320KB2.42Q4Q7Q120.0425s0.0575s0.6900s0.0240s0.0320s0.4050s2Q4Q7Q12(10M)2115DBA2333MySQLQ4Q7Q122.532.63Q4Q7Q121212120.04s0.00s0.06s0.00s0.69s0.00s31query_cache_size0Q4Q7Q1202query_cache_size30MQ4Q7Q120MySQLMySQLwindowsMySQLmysqlshowMySQlquery_cache_size030MB162.5MySQL6MySQLMySQLMySQL173MySQLMySQLMySQLSQLMySQL3.1MySQLMySQL[3,4]MySQLSQLSQLASCIISQLSQLMySQL[3]MySQLSQLSQL10%38%10%ASCIISQLMySQL(21selects31)MySQLselecto_orderkeyfromorderswhereo_orderdate='1992-12-14';18Selecto_orderkeyfromorderswhereo_orderdate='1992-12-14';selecto_orderkeyfromorderswhereo_orderdate='1992-12-14';MySQL20MMySQL3.2MySQLMySQLMySQL3.2.1MySQLMySQL1SQLHijxiao_Clean_InValid_Char()Hijxiao_Normalize_Keyword()1SQLSQL193.1Hijxiao_Clean_InValid_CharSQLstrlenSQLSTRlenstep1:for(SQLstrstr[i])step2:if(str[i])str[i]STRstep1step3:elseif(str[i]str[i+1])STRstep1step4:else(str[i]str[i+1])STRstr[i+1]strlen1step1step5:returnSTRlenSQLMySQLSQL3.1MySQL2SQLSQLMySQL40SQL40SQL20hash_key()MySQL(Key_Hash[100])SQL3.2Hijxiao_Normalize_KeywordSQLstrSQLstrstep1:for(SQLstr)step2:if()step1step3:hash_key();step4:step3(Key_Hash[100]);step5:if()step1step6:elsestep1step7:returnstr21SQLMy