mysql效能優化指南

本文的主要目標是介紹如何最佳化MySQL資料庫，透過定期分析表格和檢視表格、定期最佳化表格以及一些常用SQL語句的最佳化手段，可以幫助你學會在MySQL開發中寫出更有效率的SQL。

1、定期分析表和檢查表

分析表的語法如下：

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name[, tbl_name]...

語和表的關鍵字分佈，分析的結果將可以使得系統得到準確的統計信息，使得SQL能夠產生正確的執行計劃。如果使用者感覺實際執行計劃並不是預期的執行計劃，執行一次分析表可能會解決問題。在分析期間，使用一個讀取鎖定對錶進行鎖定。這對於MyISAM，DBD和InnoDB表有作用。

例如分析一個資料表

analyze table table_name

   

檢查表的語法如下：N55%_5%

檢查表的語法如下：？ | FAST | MEDIUM | EXTENDED | CHANGED}

   

檢查表的作用是檢查一個或多個表是否有錯誤，CHECK TABLE 對MyISAM 和InnoDB表有作用，對於MyISAM表，統計數據被更新

CHECK TABLE 也可以檢查視圖是否有錯誤，例如在視圖定義中被引用的表不存在。

2. 定期優化表

優化表的語法如下：

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name [,tbl_name]...

如果已經對含有可變長度行的表(含有VARCHAR、BLOB或TEXT列的表)進行更多更改，則應使用OPTIMIZE TABLE命令來進行表優化。這個指令可以將表中的空間碎片進行合併，並且可以消除因為刪除或更新造成的空間浪費，但OPTIMIZE TABLE 指令只對MyISAM、 BDB 和InnoDB表運作。

例如： optimize table table_name

注意： analyze、check、optimize執行期間將對表進行鎖定，因此一定注意要在MySQL資料庫不繁忙的時候執行相關的操作。

常用的SQL最佳化

我們在開發的時候常常用到的SQL語句，只不過是INSERT、GROUPBY等等。對於這些SQL語句，我們要怎麼進行最佳化？

1. 大量插入資料

當用load指令匯入資料的時候，適當的設定可以提高導入的速度。

對於MyISAM儲存引擎的表，可以透過以下方式快速的匯入大量的資料

程式碼如下:

ALTER TABLE tb1_name DISABLE KEYS;
loading the data
ALTER TABLE tb1_name ENABLE KEYS;

    

DISABLE KEYS 和 ENABLE KEYS 用來開啟或是唯一為非關閉索引的資料表。在匯入大量的資料到一個非空的MyISAM表時，透過設定這兩個指令，可以提高匯入的效率。

對於匯入大量的資料到一個空的MyISAM表時，預設就是先匯入資料然後才建立索引的，索引不用進行設定。

load data infile '/home/mysql/text_txt' into table text

   

對於InnoDB類型的表，這種方式不能提高導入資料的效率，但也有幾種類型的表優化的方式。

1. 因為InnoDB類型的表式按照主鍵的順序保存的，所以將導入的資料按照主鍵的順序排序，可以有效提高導入資料的效率。

2. 在匯入資料前執行 SET UNIQUE_CHECKS=0，關閉唯一性校驗，在導入結束後執行SET UNIQUE_CHECKS=1，恢復唯一性校驗，可以提高導入的效率。

3. 如果應用程式使用自動提交的方式，建議在導入前執行SET AUTOCOMMIT=0，關閉自動提交，導入結束後執行SET AUTOCOMMIT=1，開啟自動提交，也可以提高導入效率。

優化INSERT語句

當進行資料INSERT的時候，可以考慮採用以下幾種方式進行最佳化

1. 如果同時從一個客戶插入很多行，盡量使用多個值表的INSERT語句，這種方式將大幅縮短客戶端與MySQL資料庫的連結、關閉等消耗，使得效率比分開執行的單一INSERT語句快.

例如：

i

nsert into test values(1,2)
insert into test values(3,4)
insert into test values(5,6)

    

將上面三句改為下三句1,2),(3,4),(5,6)……

2. 如果從不同客戶插入很多行，能透過使用INSERT DELAYED 語句得到更高的速度。

DELAYED 的意思是讓INSERT 語句馬上執行，其實資料都被放在記憶體的佇列中，並沒有真正寫入磁碟，這比每個語句分別插入要快得多；LOW_PRIORITY剛好相反，在所有其他使用者對表的讀寫完後才進行插入。

3. 將索引檔案和資料檔案分在不同的磁碟上存放

4. 如果進行批次插入，可以增加bulk_insert_buffer_size變數值的方法來提高速度，但是，這只能對於MyISAM表使用。

5. 從一個文字檔案裝載一個表格時，使用LOAD DATA INFILE。 這通常比使用很多insert語句快20倍左右。

以上是MySQL 效能最佳化的一些其它需要注意的地方，大家結合和，充分發揮mysql的效能。