• <source id="g5nzt"><menu id="g5nzt"></menu></source>
    <rp id="g5nzt"><menu id="g5nzt"></menu></rp>
    
    
    欄目列表



    發布時間:2008-08-10 12:40:48 點擊數: 更新時間:2008-08-10
    MySQL數據庫性能優化完全功略

    1. 估算查詢性能

      在大多數情況下,可以通過統計磁盤搜索次數來估算查詢的性能。對小表來說,通常情況下只需要搜索一次磁盤就能找到對應的記錄(因為索引可能已經緩存起來了)。對大表來說,大致可以這么估算,它使用B樹做索引,想要找到一條記錄大概需要搜索的次數為:log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1。

      在MySQL中,一個索引塊通常是1024bytes,數據指針通常是4bytes。對于一個有500,000條記錄、索引長度為3bytes(medium integer)的表來說,根據上面的公式計算得到需要做 log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 次搜索。

      這個表的索引大概需要 500,000 * 7 * 3/2 = 5.2MB的存儲空間(假定典型的索引緩沖區的2/3),因此應該會有更多的索引在內存中,并且可能只需要1到2次調用就能找到對應的記錄。

      對于寫來說,大概需要4次(甚至更多)搜索才能找到新的索引位置,更新記錄時通常需要2次搜索。

      請注意,前面的討論中并沒有提到應用程序的性能會因為log N的值越大而下降。只要所有的東西都能由操作系統或者SQL服務器緩存起來,那么性能只會因為數據表越大而稍微下降。當數據越來越大之后,就不能全部放到緩存中去了,就會越來越慢了,除非應用程序是被磁盤搜索約束的(它跟隨著的log N值增加而增加)。為了避免這種情況,可以在數據量增大以后也隨著增大索引緩存容量。對 MyISAM 類型表來說,索引緩存容量是由系統變量 key_buffer_size 控制的。

      2. SELECT 查詢的速度

      通常情況下,想要讓一個比較慢的 SELECT ... WHERE 查詢變得更快的第一件事就是,先檢查看看是否可以增加索引。所有對不同表的訪問都通常使用索引。可以使用 EXPLAIN 語句來判斷 SELECT 使用了哪些索引。詳情請看"7.4.5 How MySQL Uses Indexes"和"7.2.1 EXPLAIN Syntax (Get Information About a SELECT)"。

      以下是幾個常用的提高 MyISAM 表查詢速度的忠告:

      想要讓MySQL將查詢優化的速度更快些,可以在數據表已經加載完全部數據后執行行 ANALYZE TABLE 或運行 myisamchk --analyze 命令。它更新了每個索引部分的值,這個值意味著相同記錄的平均值(對于唯一索引來說,這個值則一直都是 1)。MySQL就會在當你使用基于一個非恒量表達式的兩表連接時,根據這個值來決定使用哪個索引。想要查看結果,可以在分析完數據表后運行 SHOW INDEX FROM tbl_name 查看 Cardinality 字段的值。myisamchk --description --verbose 顯示了索引的分布信息。

      想要根據一個索引來排序數據,可以運行 myisamchk --sort-index --sort-records=1 (如果想要在索引 1 上做排序)。這對于有一個唯一索引并且想根據這個索引的順序依次讀取記錄的話來說是一個提高查詢速度的好辦法。不過要注意的是,第一次在一個大表上做排序的話將會耗費很長時間。

      3. MySQL如何優化 WHERE 子句

      這個章節講述了優化程序如何處理 WHERE 子句。例子中使用了 SELECT 語句,但是在 DELETE 和 UPDATE 語句中對 WHERE 子句的優化是一樣的。注意,關于MySQL優化的工作還在繼續,因此本章節還沒結束。MySQL做了很多優化工作,而不僅僅是文檔中提到的這些。

      MySQL的一些優化做法如下:

      去除不必要的括號:

      ((a AND b) AND c OR (((a AND b) AND (c AND d))))

      -> (a AND b AND c) OR (a AND b AND c AND d)

      展開常量:

      (a > b>5 AND b=c AND a=5

      去除常量條件(在展開常量時需要):

      (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)

      -> B=5 OR B=6

      常量表達示在索引中只計算一次

      在單獨一個表上做 COUNT(*) 而不使用 WHERE 時, 對于 MyISAM 和 HEAP 表就會直接從表信息中檢索結果。在單獨一個表上做任何表 NOT NULL 達式查詢時也是這樣做。

      預先探測無效的常量表達式。MySQL會快速探測一些不可能的 SELECT 語句并且不返回任何記錄。

      當沒用 GROUP BY 或分組函數時,HAVING 和 WHERE 合并(COUNT(), MIN() 等也是如此)。

      為表連接中的每個表構造一個簡潔的 WHERE 語句,以得到更快的 WHERE 計算值并且盡快跳過記錄。

      查詢中所有的常量表都會比其他表更早讀取。一個常量表符合以下幾個條件:

      空表或者只有一條記錄。

      與在一個 UNIQUE 索引、或一個 PRIMARY KEY 的 WHERE 子句一起使用的表,這里所有的索引部分和常數表達式做比較并且索引部分被定義為 NOT NULL。

      以下的幾個表都會被當成常量表:

        SELECT*FROMtWHEREprimary_key=1;
        SELECT*FROMt1,t2
        WHEREt1.primary_key=1ANDt2.primary_key=t1.id;

      MySQL會進各種可能找到表連接最好的連接方法。 如果在 ORDER BY 和 GROUP BY 子句中的所有字段都來自同一個表的話,那么在連接時這個表就會優先處理。

      如果有 ORDER BY 子句和一個不同的 GROUP BY 子句,或者如果 ORDER BY 或 GROUP BY 中的字段都來自其他的表而非連接順序中的第一個表的話,就會創建一個臨時表了。

      如果使用 SQL_SMALL_RESULT,MySQL就會使用內存臨時表了。

      所有的表索引都會查詢,最好的情況就是所有的索引都會被用到,除非優化程序認為全表掃描的效率更高。同時,數據表掃描是基于判斷最好的索引范圍超過數據表的30%。 現在,優化程序復雜多了,它基于對一些附加因素的估計,例如表大小,記錄總數,I/O塊大小,因此就不能根據一個固定的百分比來決定是選擇使用索引還是直接掃描數據表。

      在某些情況下,MySQL可以直接從索引中取得記錄而無需查詢數據文件。如果所有在索引中使用的字段都是數字類型的話,只需要用索引樹就能完成查詢。

      每條記錄輸出之前,那些沒有匹配 HAVING 子句的就會被跳過。

      以下幾個查詢速度非常快:

      SELECTCOUNT(*)FROMtbl_name;
      SELECTMIN(key_part1),MAX(key_part1)FROMtbl_name;
      SELECTMAX(key_part2)FROMtbl_name
      WHEREkey_part1=constant;
      SELECT...FROMtbl_name
      ORDERBYkey_part1,key_part2,...LIMIT10;
      SELECT...FROMtbl_name
      ORDERBYkey_part1DESC,key_part2DESC,...LIMIT10;

      以下幾個查詢都是使用索引樹,假使那些索引字段都是數字型:

      SELECTkey_part1,key_part2FROMtbl_nameWHEREkey_part1=val;
      SELECTCOUNT(*)FROMtbl_name
      WHEREkey_part1=val1ANDkey_part2=val2;
      SELECTkey_part2FROMtbl_nameGROUPBYkey_part1;

      以下幾個查詢使用索引來取得經過順序排序后的記錄而無需經過獨立的排序步驟:

      SELECT...FROMtbl_name
      ORDERBYkey_part1,key_part2,...;
      SELECT...FROMtbl_name
      ORDERBYkey_part1DESC,key_part2DESC,...;

      4. MySQL 如何優化 OR 子句

      Index Merge 方法用于使用 ref, ref_or_null, 或 range 掃描取得的記錄合并起來放到一起作為結果。這種方法在表條件是或條件 ref, ref_or_null, 或 range ,并且這些條件可以用不同的鍵時采用。

      "join"類型的優化是從 MySQL 5.0.0 開始才有的,代表者在索引的性能上有著標志性的改進,因為使用老規則的話,數據庫最多只能對每個引用表使用一個索引。
     在 EXPLAIN 的結果中,這種方法在 type 字段中表現為 index_merge。這種情況下,key 字段包含了所有使用的索引列表,并且 key_len 字段包含了使用的索引的最長索引部分列表。

      例如:

      SELECT*FROMtbl_nameWHEREkey_part1=10ORkey_part2=20;
      SELECT*FROMtbl_name
      WHERE(key_part1=10ORkey_part2=20)ANDnon_key_part=30;
      SELECT*FROMt1,t2
      WHERE(t1.key1IN(1,2)ORt1.key2LIKE'value%')
      ANDt2.key1=t1.some_col;
      SELECT*FROMt1,t2
      WHEREt1.key1=1
      AND(t2.key1=t1.some_colORt2.key2=t1.some_col2);

      5. MySQL 如何優化 IS NULL

      MySQL在 col_name IS NULL 時做和 col_name = constant_value 一樣的優化。例如,MySQL使用索引或者范圍來根據 IS NUL L搜索 NULL。

      SELECT*FROMtbl_nameWHEREkey_colISNULL;
      SELECT*FROMtbl_nameWHEREkey_col<=>NULL;
      SELECT*FROMtbl_name
      WHEREkey_col=const1ORkey_col=const2ORkey_colISNULL;

      如果一個 WHERE 子句包括了一個 col_name IS NULL 條件,并且這個字段聲明為 NOT NULL,那么這個表達式就會被優化。當字段可能無論如何都會產生 NULL 值時,就不會再做優化了;例如,當它來自一個 LEFT JOIN 中右邊的一個表時。

      MySQL 4.1.1或更高會對連接 col_name = expr AND col_name IS NULL 做額外的優化, 常見的就是子查詢。EXPLAIN 當優化起作用時會顯示 ref_or_null。

      優化程序會為任何索引部分處理 IS NULL。

      以下幾個例子中都做優化了,假使字段 a 和 表 t2 中 b 有索引了:

      SELECT*FROMt1WHEREt1.a=exprORt1.aISNULL;
      SELECT*FROMt1,t2WHEREt1.a=t2.aORt2.aISNULL;
      SELECT*FROMt1,t2
      WHERE(t1.a=t2.aORt2.aISNULL)ANDt2.b=t1.b;
      SELECT*FROMt1,t2
      WHEREt1.a=t2.aAND(t2.b=t1.bORt2.bISNULL);
      SELECT*FROMt1,t2
      WHERE(t1.a=t2.aANDt2.aISNULLAND...)
      OR(t1.a=t2.aANDt2.aISNULLAND...);

      ref_or_null 首先讀取引用鍵,然后獨立掃描鍵值為 NULL 的記錄。

      請注意,優化程序只會處理一個 IS NULL 級別。下面的查詢中,MySQL只會使用鍵來查詢表達式(t1.a=t2.a AND t2.a IS NULL) 而無法使在 b 上使用索引部分:

      SELECT*FROMt1,t2
      WHERE(t1.a=t2.aANDt2.aISNULL)
      OR(t1.b=t2.bANDt2.bISNULL);

      6. MySQL 如何優化 DISTINCT

      在很多情況下,DISTINCT 和 ORDER BY 一起使用時就會創建一個臨時表。

      注意,由于 DISTINCT 可能需要用到 GROUP BY,就需要明白MySQL在 ORDER BY 或 HAVING 子句里的字段不在選中的字段列表中時是怎么處理的。詳情請看"13.9.3 GROUP BY with Hidden Fields"。

      當 LIMIT row_count 和 DISTINCT 一起使用時,MySQL在找到 row_count 不同記錄后就會立刻停止搜索了。

      如果沒有用到來自查詢中任何表的字段時,MySQL在找到第一個匹配記錄后就會停止搜索這些沒沒用到的表了。在下面的情況中,假使 t1 在 t2 前就使用了(可以通過 EXPLAIN 分析知道),MySQL就會在從 t2 中找到第一條記錄后就不再讀 t2 了(為了能和中 t1 的任何特定記錄匹配):

      SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;

      7. MySQL 如何優化 LEFT JOIN 和 RIGHT JOIN

      A LEFT JOIN B join_condition 在MySQL中實現如下:

      表 B 依賴于表 A 以及其依賴的所有表。

      表 A 依賴于在 LEFT JOIN 條件中的所有表(除了 B)。

      LEFT JOIN 條件用于決定如何從表 B 中讀取記錄了(換句話說,WHERE 子句中的任何條件都對此不起作用)。

      所有標準的連接優化都會執行,例外的情況是有一個表總是在它依賴的所有表之后被讀取。如果這是一個循環的依賴關系,那么MySQL會認為這是錯誤的。

      所有的標準 WHERE 優化都會執行。

      如果 A 中有一條記錄匹配了 WHERE 子句,但是 B 中沒有任何記錄匹配 ON 條件,那么就會產生一條 B 記錄,它的字段值全都被置為 NULL。

      如果使用 LEFT JOIN 來搜索在一些表中不存在的記錄,并且 WHERE 部分中有檢測條件:col_name IS NULL,col_name 字段定義成 NOT NULL 的話,MySQL就會在找到一條匹配 LEFT JOIN 條件的記錄(用于和特定的索引鍵做聯合)后停止搜索了。

      RIGHT JOIN 的實現和 LEFT JOIN 類似,不過表的角色倒過來了。

      連接優化程序計算了表連接的次序。表讀取的順序是由 LEFT JOIN 強行指定的,而且使用 STRAIGHT_JOIN 能幫助連接優化程序更快地執行,因為這就會有更少的表排隊檢查了。注意,這里是指如果你執行下面這種類型的查詢后,MySQL就會對 b 做一次全表掃描,因為 LEFT JOIN 強制要求了必須在讀 d 之前這么做:

      SELECT*
      FROMa,bLEFTJOINcON(c.key=a.key)LEFTJOINdON(d.key=a.key)
      WHEREb.key=d.key;

      解決這種情況的方法是按照如下方式重寫查詢:

      SELECT*
      FROMb,aLEFTJOINcON(c.key=a.key)LEFTJOINdON(d.key=a.key)
      WHEREb.key=d.key;

      從4.0.14開始,MySQL做如下 LEFT JOIN 優化:如果對產生的 NULL 記錄 WHERE 條件總是 假,那么 LEFT JOIN 就會變成一個普通的連接。

      例如,下面的查詢中如果 t2.column1 的值是 NULL 的話,WHERE 子句的結果就是假了:

      SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;

      因此,這就可以安全的轉換成一個普通的連接查詢:

      SELECT * FROM t1,t2 WHERE t2.column2=5 AND t1.column1=t2.column1;

      這查詢起來就更快了,因為如果能有一個更好的查詢計劃的話,MySQL就會在 t1 之前就用到 t2 了。想要強行指定表順序的話,可以使用 STRAIGHT_JOIN。

    | 設為首頁 | 加入收藏 | 網站地圖 | 管理系統 |

    版權所有 2008-2010 遂寧市中心醫院〖原遂寧市人民醫院〗(備案號:蜀ICP備15033154號 遂公網安備51090002000072)
    本站信息僅供參考 不能作為診斷及醫療的依據
    地址:四川省遂寧市船山區德勝西路127號(本部) 遂寧市河東新區東平北路27號(河東分部) 遂寧市河東新區慈航路2號(市傳染病醫院)
    郵編:629000 院長信箱:
    snszxyy@126.com 站長信箱:47936697@qq.com
    電話(0825) 急救:2222995(河東) 2292611(本部) 咨詢:2292105 黨辦:2292241 院辦:2292270 醫務:2292370 戒煙咨詢:2292556

    健康博濟微信公眾號
     

    掃描關注遂寧市中心醫院官方微信公眾號
    掛號交費不排隊

    掃描安裝遂寧市中心醫院官方APP
    掛號交費不排隊

    收藏遂寧市中心醫院網站
     
       
    遂寧市互聯網不良與違法信息舉報中心電話(傳真):0825-2988759 舉報郵箱:sn_wgb@126.com
    774477好运平特论坛