MYSQL查詢策略與優(yōu)化

《MYSQL查詢策略與優(yōu)化》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在教育資源-天天文庫(kù)。

1、查詢是數(shù)據(jù)庫(kù)技術(shù)中最常用的操作。查詢操作的過(guò)程比較簡(jiǎn)單，首先從客戶端發(fā)出查詢的SQL語(yǔ)句，數(shù)據(jù)庫(kù)服務(wù)端在接收到由客戶端發(fā)來(lái)的SQL語(yǔ)句后，執(zhí)行這條SQL語(yǔ)句，然后將查詢到的結(jié)果返回給客戶端。雖然過(guò)程很簡(jiǎn)單，但不同的查詢方式和數(shù)據(jù)庫(kù)設(shè)置，對(duì)查詢的性能將會(huì)有很在的影響。因此，本文就在MySQL中常用的查詢優(yōu)化技術(shù)進(jìn)行討論。討論的內(nèi)容如：通過(guò)查詢緩沖提高查詢速度；MySQL對(duì)查詢的自動(dòng)優(yōu)化；基于索引的排序；不可達(dá)查詢的檢測(cè)和使用各種查詢選擇來(lái)提高性能。一、通過(guò)查詢緩沖提高查詢速度一般我們使用SQL語(yǔ)句進(jìn)行查詢時(shí)，數(shù)據(jù)庫(kù)服務(wù)器每次在收到客戶端發(fā)來(lái)SQL后，都會(huì)執(zhí)行這條SQL

2、語(yǔ)句。但當(dāng)在一定間隔內(nèi)(如1分鐘內(nèi))，接到完全一樣的SQL語(yǔ)句，也同樣執(zhí)行它。雖然這樣可以保證數(shù)據(jù)的實(shí)時(shí)性，但在大多數(shù)時(shí)候，數(shù)據(jù)并不要求完全的實(shí)時(shí)，也就是說(shuō)可以有一定的延時(shí)。如果是這樣的話，在短時(shí)間內(nèi)執(zhí)行完全一樣的SQL就有些得不償失。幸好MySQL為我們提供了查詢緩沖的功能(只能在MySQL4.0.1及以上版本使用查詢緩沖)。我們可以通過(guò)查詢緩沖在一定程度上提高查詢性能。我們可以通過(guò)在MySQL安裝目錄中的my.ini文件設(shè)置查詢緩沖。設(shè)置也非常簡(jiǎn)單，只需要將query_cache_type設(shè)為1即可。在設(shè)置了這個(gè)屬性后，MySQL在執(zhí)行任何SELECT語(yǔ)句之前，

3、都會(huì)在它的緩沖區(qū)中查詢是否在相同的SELECT語(yǔ)句被執(zhí)行過(guò)，如果有，并且執(zhí)行結(jié)果沒(méi)有過(guò)期，那么就直接取查詢結(jié)果返回給客戶端。但在寫SQL語(yǔ)句時(shí)注意，MySQL的查詢緩沖是區(qū)分大小寫的。如下列的兩條SELECT語(yǔ)句:1.SELECT*fromTABLE12.?3.SELECT*FROM?TABLE1上面的兩條SQL語(yǔ)句對(duì)于查詢緩沖是完全不同的SELECT。而且查詢緩沖并不自動(dòng)處理空格，因此，在寫SQL語(yǔ)句時(shí)，應(yīng)盡量減少空格的使用，尤其是在SQL首和尾的空格(因?yàn)?，查詢緩沖并不自動(dòng)截取首尾空格)。雖然不設(shè)置查詢緩沖，有時(shí)可能帶來(lái)性能上的損失，但有一些SQL語(yǔ)句需要實(shí)時(shí)地

4、查詢數(shù)據(jù)，或者并不經(jīng)常使用(可能一天就執(zhí)行一兩次)。這樣就需要把緩沖關(guān)了。當(dāng)然，這可以通過(guò)設(shè)置query_cache_type的值來(lái)關(guān)閉查詢緩沖，但這就將查詢緩沖永久地關(guān)閉了。在MySQL5.0中提供了一種可以臨時(shí)關(guān)閉查詢緩沖的方法:1.SELECTSQL_NO_CACHEfield1,field2FROMTABLE1以上的SQL語(yǔ)句由于使用了SQL_NO_CACHE，因此，不管這條SQL語(yǔ)句是否被執(zhí)行過(guò)，服務(wù)器都不會(huì)在緩沖區(qū)中查找，每次都會(huì)執(zhí)行它。我們還可以將my.ini中的query_cache_type設(shè)成2，這樣只有在使用了SQL_CACHE后，才使用查詢緩

5、沖。1.SELECTSQL_CALHE*FROMTABLE1二、MySQL對(duì)查詢的自動(dòng)優(yōu)化索引對(duì)于數(shù)據(jù)庫(kù)是非常重要的。在查詢時(shí)可以通過(guò)索引來(lái)提高性能。但有時(shí)使用索引反而會(huì)降低性能。我們可以看如下的SALES表:1.CREATETABLESALES2.?3.(4.?5.ID?INT(10)UNSIGNEDNOTNULLAUTO_INCREMENT,6.?7.NAME?VARCHAR(100)NOTNULL,8.?9.PRICE?FLOATNOTNULL,10.?11.SALE_COUNT?INTNOTNULL,12.?13.SALE_DATE?DATENOTNULL,

6、14.?15.PRIMARY?KEY(ID)，16.?17.INDEX?(NAME)，18.?19.INDEX?(SALE_DATE)20.?21.);假設(shè)這個(gè)表中保存了數(shù)百萬(wàn)條數(shù)據(jù)，而我們要查詢商品號(hào)為1000的商品在2004年和2005年的平均價(jià)格。我們可以寫如下的SQL語(yǔ)句:SELECTAVG(PRICE)FROMSALESWHEREID=1000ANDSALE_DATEBETWEEN'2004-01-01'AND'2005-12-31';如果這種商品的數(shù)量非常多，差不多占了SALES表的記錄的50%或更多。那么使用SALE_DATE字段上索引來(lái)計(jì)算平均數(shù)就有

7、些慢。因?yàn)槿绻褂盟饕偷脤?duì)索引進(jìn)行排序操作。當(dāng)滿足條件的記錄非常多時(shí)(如占整個(gè)表的記錄的50%或更多的比例)，速度會(huì)變慢，這樣還不如對(duì)整個(gè)表進(jìn)行掃描。因此，MySQL會(huì)自動(dòng)根據(jù)滿足條件的數(shù)據(jù)占整個(gè)表的數(shù)據(jù)的比例自動(dòng)決定是否使用索引進(jìn)行查詢。對(duì)于MySQL來(lái)說(shuō)，上述的查詢結(jié)果占整個(gè)表的記錄的比例是30%左右時(shí)就不使用索引了，這個(gè)比例是MySQL的開發(fā)人員根據(jù)他們的經(jīng)驗(yàn)得出的。然而，實(shí)際的比例值會(huì)根據(jù)所使用的數(shù)據(jù)庫(kù)引擎不同而不同。三、基于索引的排序MySQL的弱點(diǎn)之一是它的排序。雖然MySQL可以在1秒中查詢大約15,000條記錄，但由于MySQL在查詢時(shí)最多只