MySQL查詢的性能優(yōu)化基礎(chǔ)教程_MySQL教程

      編輯Tag賺U幣
      教程Tag:暫無Tag,歡迎添加,賺取U幣!

      推薦:MySQL 在windows上的安裝詳細介紹
      使用自動化安裝程序自動安裝MySQL 新的MySQL 5.0用戶可以使用MySQL安裝向?qū)Ш蚆ySQL配置向?qū)г赪indows上安裝Mysql.設(shè)計這兩個向?qū)У哪康木褪菫榱朔奖阈掠脩裟軌蚝芸斓氖褂肕ySQL。在MySQL完全安裝包中,MySQL安裝向?qū)Ш蚆ySQL配置向?qū)Ф际强捎玫,同時這兩個向?qū)г诖蟛糠?/p>

         查詢是數(shù)據(jù)庫技術(shù)中最常用的操作。查詢操作的過程比較簡單,首先從客戶端發(fā)出查詢的SQL語句,數(shù)據(jù)庫服務(wù)端在接收到由客戶端發(fā)來的SQL語句后,執(zhí)行這條SQL語句,然后將查詢到的結(jié)果返回給客戶端。雖然過程很簡單,但不同的查詢方式和數(shù)據(jù)庫設(shè)置,對查詢的性能將會有很在的影響。

        因此,本文就在MySQL中常用的查詢優(yōu)化技術(shù)進行討論。討論的內(nèi)容如:通過查詢緩沖提高查詢速度;MySQL對查詢的自動優(yōu)化;基于索引的排序;不可達查詢的檢測和使用各種查詢選擇來提高性能。

        一、 通過查詢緩沖提高查詢速度

        一般我們使用SQL語句進行查詢時,數(shù)據(jù)庫服務(wù)器每次在收到客戶端發(fā)來SQL后,都會執(zhí)行這條SQL語句。但當在一定間隔內(nèi)(如1分鐘內(nèi)),接到完全一樣的SQL語句,也同樣執(zhí)行它。雖然這樣可以保證數(shù)據(jù)的實時性,但在大多數(shù)時候,數(shù)據(jù)并不要求完全的實時,也就是說可以有一定的延時。如果是這樣的話,在短時間內(nèi)執(zhí)行完全一樣的SQL就有些得不償失。

        幸好MySQL為我們提供了查詢緩沖的功能(只能在MySQL 4.0.1及以上版本使用查詢緩沖)。我們可以通過查詢緩沖在一定程度上提高查詢性能。

        我們可以通過在MySQL安裝目錄中的my.ini文件設(shè)置查詢緩沖。設(shè)置也非常簡單,只需要將query_cache_type設(shè)為1即可。在設(shè)置了這個屬性后,MySQL在執(zhí)行任何SELECT語句之前,都會在它的緩沖區(qū)中查詢是否在相同的SELECT語句被執(zhí)行過,如果有,并且執(zhí)行結(jié)果沒有過期,那么就直接取查詢結(jié)果返回給客戶端。但在寫SQL語句時注意,MySQL的查詢緩沖是區(qū)分大小寫的。如下列的兩條SELECT語句: SELECT * from TABLE1

        SELECT * FROM TABLE1

        上面的兩條SQL語句對于查詢緩沖是完全不同的SELECT。而且查詢緩沖并不自動處理空格,因此,在寫SQL語句時,應(yīng)盡量減少空格的使用,尤其是在SQL首和尾的空格(因為,查詢緩沖并不自動截取首尾空格)。

        雖然不設(shè)置查詢緩沖,有時可能帶來性能上的損失,但有一些SQL語句需要實時地查詢數(shù)據(jù),或者并不經(jīng)常使用(可能一天就執(zhí)行一兩次)。這樣就需要把緩沖關(guān)了。當然,這可以通過設(shè)置query_cache_type的值來關(guān)閉查詢緩沖,但這就將查詢緩沖永久地關(guān)閉了。在MySQL 5.0中提供了一種可以臨時關(guān)閉查詢緩沖的方法: SELECT SQL_NO_CACHE field1, field2 FROM TABLE1

        以上的SQL語句由于使用了SQL_NO_CACHE,因此,不管這條SQL語句是否被執(zhí)行過,服務(wù)器都不會在緩沖區(qū)中查找,每次都會執(zhí)行它。

        我們還可以將my.ini中的query_cache_type設(shè)成2,這樣只有在使用了SQL_CACHE后,才使用查詢緩沖。 SELECT SQL_CALHE * FROM TABLE1二、MySQL對查詢的自動優(yōu)化

        索引對于數(shù)據(jù)庫是非常重要的。在查詢時可以通過索引來提高性能。但有時使用索引反而會降低性能。我們可以看如下的SALES表: CREATE TABLE SALES

        (

        ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

        NAME VARCHAR(100) NOT NULL,

        PRICE FLOAT NOT NULL,

        SALE_COUNT INT NOT NULL,

        SALE_DATE DATE NOT NULL,

        PRIMARY KEY(ID),

        INDEX (NAME),

        INDEX (SALE_DATE)

        )

        假設(shè)這個表中保存了數(shù)百萬條數(shù)據(jù),而我們要查詢商品號為1000的商品在2004年和2005年的平均價格。我們可以寫如下的SQL語句: SELECT AVG(PRICE) FROM SALES

        WHERE ID = 1000 AND SALE_DATE BETWEEN '2004-01-01' AND '2005-12-31';

        如果這種商品的數(shù)量非常多,差不多占了SALES表的記錄的50%或更多。那么使用SALE_DATE字段上索引來計算平均數(shù)就有些慢。因為如果使用索引,就得對索引進行排序操作。當滿足條件的記錄非常多時(如占整個表的記錄的50%或更多的比例),速度會變慢,這樣還不如對整個表進行掃描。因此,MySQL會自動根據(jù)滿足條件的數(shù)據(jù)占整個表的數(shù)據(jù)的比例自動決定是否使用索引進行查詢。

        對于MySQL來說,上述的查詢結(jié)果占整個表的記錄的比例是30%左右時就不使用索引了,這個比例是MySQL的開發(fā)人員根據(jù)他們的經(jīng)驗得出的。然而,實際的比例值會根據(jù)所使用的數(shù)據(jù)庫引擎不同而不同。

        三、 基于索引的排序

        MySQL的弱點之一是它的排序。雖然MySQL可以在1秒中查詢大約15,000條記錄,但由于MySQL在查詢時最多只能使用一個索引。因此,如果WHERE條件已經(jīng)占用了索引,那么在排序中就不使用索引了,這將大大降低查詢的速度。我們可以看看如下的SQL語句: SELECT * FROM SALES WHERE NAME = “name” ORDER BY SALE_DATE DESC;

        在以上的SQL的WHERE子句中已經(jīng)使用了NAME字段上的索引,因此,在對SALE_DATE進行排序時將不再使用索引。為了解決這個問題,我們可以對SALES表建立復(fù)合索引: ALTER TABLE SALES DROP INDEX NAME, ADD INDEX (NAME, SALE_DATE)

        這樣再使用上述的SELECT語句進行查詢時速度就會大副提升。但要注意,在使用這個方法時,要確保WHERE子句中沒有排序字段,在上例中就是不能用SALE_DATE進行查詢,否則雖然排序快了,但是SALE_DATE字段上沒有單獨的索引,因此查詢又會慢下來。

        四、 不可達查詢的檢測

        在執(zhí)行SQL語句時,難免會遇到一些必假的條件。所謂必假的條件是無論表中的數(shù)據(jù)如何變化,這個條件都為假。如WHERE value < 100 AND value > 200。我們永遠無法找到一個既小于100又大于200的數(shù)。

        如果遇到這樣的查詢條件,再去執(zhí)行這樣的SQL語句就是多此一舉。幸好MySQL可以自動檢測這種情況。如我們可以看看如下的SQL語句: SELECT * FROM SALES WHERE NAME = “name1” AND NAME = “name2”

        以上的查詢語句要查找NAME既等于name1又等于name2的記錄。很明顯,這是一個不可達的查詢,WHERE條件一定是假。MySQL在執(zhí)行SQL語句之前,會先分析WHERE條件是否是不可達的查詢,如果是,就不再執(zhí)行這條SQL語句了。為了驗證這一點。我們首先對如下的SQL使用EXPLAIN進行測試: EXPLAIN SELECT * FROM SALES WHERE NAME = “name1”

        上面的查詢是一個正常的查詢,我們可以看到使用EXPLAIN返回的執(zhí)行信息數(shù)據(jù)中table項是SALES。這說明MySQL對SALES進行操作了。再看看下面的語句: EXPLAIN SELECT * FROM SALES WHERE NAME = “name1” AND NAME = “name2”

      分享:sql server 2005時發(fā)生18452錯誤解決方法
      ODBC 測試sql server 2005時發(fā)生18452錯誤解決方法 用.net開發(fā)的網(wǎng)站連接數(shù)據(jù)庫里發(fā)生錯誤,當訪問靜態(tài)頁面里顯示正常。 1.首先想到的是測試ODBC數(shù)據(jù)源,當用sql用戶和密碼登錄時出現(xiàn)如下圖錯誤: 2.因為是在客戶服務(wù)器上安裝,所以未設(shè)置sa的密碼,由于安裝時未勾選擇

      來源:模板無憂//所屬分類:MySQL教程/更新時間:2015-02-04
      相關(guān)MySQL教程