mysql最佳化小結（插入和查詢）

        說到mysql，我們立刻想起它體積小、速度快、還開源的特點，所以它應用頗廣。今天我們來總結一下mysql中最頻繁的兩個操作：插入和查詢，的最佳化方法。

        

插入： 

一、文本匯入

    使用LOAD DATA INFILE從文本下載資料這將比使用插入語句快20倍。

樣本：

load data local infile 'C:/Users/DELL/Desktop/Description&Data/news1.txt' into table news         （檔案位置）
fields terminated by ','                                            （每一欄位由‘，’分隔開）
lines terminated by '\r\n'                                       （每一組資料由 分行符號 分隔開）
(content,date,ip,author,subject);                           （解釋txt中每一行由這幾個欄位組成）

二、一次插多條

    使用帶有多個VALUES列表的INSERT語句一次插入幾行比使用一個單行插入語句快幾倍。

樣本：

1. INSERT  INTO  food  VALUES  
2.     (NULL,'EE果凍','EE果凍廠', 1.5 ,'2007', 2 ,'北京') ,  
3.     (NULL,'FF咖啡','FF咖啡廠', 20 ,'2002', 5 ,'天津') ,  
4.     (NULL,'GG奶糖','GG奶糖', 14 ,'2003', 3 ,'廣東') ;

而不是：

1. INSERT INTO food VALUES (NULL,'EE果凍','EE
   果凍廠', 1.5 ,'2007', 2 ,'北京');  
2. INSERT INTO food VALUES (NULL,'FF咖啡','FF
   咖啡廠', 20 ,'2002', 5 ,'天津');  
3. INSERT INTO food VALUES (NULL,'GG奶糖','GG
   奶糖', 14 ,'2003', 3 ,'廣東'); 
4. 
   

第一種方式減少了與資料庫之間的串連等操作，所以其速度比第二種方式要快。

三、使用varchar

    之所以使用varchar，而不是char，因為varchar是按實際資料的長度儲存的；而char在儲存完實際資料後，還把空白的空間自動補全。所以明顯char插入會比varchar慢。實驗說明，無論插入資料涉及的列是否建立索引，char的效率都明顯低於varchar。

四、控制欄位長度

    這個很明顯，越小的資料類型佔用的空間越小，從磁碟讀或打包到記憶體的效率都更好，但也不要太過執著減小資料類型，要是以後應用程式發生什麼變化就沒有空間了，所以要綜合考慮決定欄位長度。

五、非空+預設值

    NULL對於大多數資料庫都需要特殊處理，MySQL也不例外，它需要更多的代碼，更多的檢查和特殊的索引邏輯，所以我們最好把屬性盡量設定成非空，同時賦予它一個預設值，比如 0。

六、禁用事務

    MySQL資料庫表有兩種類型，一種是支援交易處理，一種是不支援交易處理。MySQL在處理這兩種表時，分別使用了不同類型的資料庫引擎，因此資料庫引擎在插入時效率不同，理論上說啟用了事務功能後會比較慢。

樣本：

Create Table(

….. /*欄位說明*/

) ENGINE=InnoDB/MyISAM                             (帶事務、不帶事務)

事實證明是否禁用事務對插入資料的速度影響很大。

不過還是要多說一句，事務並非只會讓我們的插入變慢。用了事務，就不可避免的要犧牲一部分速度，但是可以很大程度上保證資料的正確性。

七、禁用索引

    插入記錄時，MySQL會根據表的索引對插入的記錄進行排序。如果插入大量資料時，這些排序會降低插入記錄的速度。為瞭解決這種情況，在插入記錄之前先禁用索引。等到記錄都插入完畢後再開啟索引。（雖然對於先插資料還是先建索引可能有一點爭議）

樣本：

1. ALTER TABLE 表名 DISABLE KEYS ;                            （禁用索引）     
2. 1. ALTER TABLE 表名 ENABLE KEYS ;                               （啟用索引）
   
   
   1. 1. 
         
          

八、禁用唯一性檢查

    我們知道，插入資料時MySQL會對插入的記錄進行唯一性校正。這種校正也會降低插入記錄的速度。可以在插入記錄之前禁用唯一性檢查。等到記錄插入完畢後再開啟。禁用唯一性檢查的語句如下：

1.        SET UNIQUE_CHECKS=0; 
2. 重新開啟唯一性檢查的語句如下：                                    

1.         SET UNIQUE_CHECKS=1; 

九、先鎖定表再插入

    這將提高資料庫效能，因為索引緩衝只是在所有的插入陳述式完成後才對磁碟進行一次重新整理。通常情況下，有多少個插入語句就會有多少次索引緩衝重新整理。如果你可以用一個插入語句實現所有行的插入，則無需使用顯式鎖定語句。

樣本：

           LOCK TABLES；                                     （鎖定表）

十、啟用並行插入

    可以對myisam表並行插入Concurrent_insert系統變數可以被設定用於修改concurrent-insert處理。該變數預設設定為1。如果concurrent_insert被設定為0，並行插入就被禁用。如果該變數被設定為2，在表的末端可以並行插入，即便該表的某些行已經被刪除。

十一、延遲插入

    如果你的客戶不能或無需等待插入完成的時候，這招很有用。當你使用MySQL儲存，並定期運行需要很長時間才能完成的SELECT和UPDATE語句的時候，你會發現這種情況很常見。當客戶使用插入延遲，伺服器立刻返回，如果表沒有被其他線程調用，則行會列隊等待被插入。使用插入延遲的另一個好處就是從多個客戶插入的情況會被綁定並記錄在同一個block中。這將比處理多個獨立的插入要快得多。

查詢：

一、最佳化資料類型

    查詢最佳化方面，資料類型是查詢的基礎，所以我們首先得最佳化我們的資料類型。實際上，資料類型方面，查詢所需要的最佳化和插入差不多，主要也是避免null和盡量使用小的欄位。

二、使用串連查詢

    使用串連查詢效率一般都優於子查詢。遇到子查詢時，MySQL查詢最佳化引擎並不是總是最有效，這就是為什麼經常將子查詢轉換為串連查詢的原因了，最佳化器已經能夠正確處理串連查詢了，當然要注意的一點是，確保串連表(第二個表)的串連列是有索引的，在第一個表上MySQL通常會相對於第二個表的查詢子集進行一次全表掃描，這是嵌套迴圈演算法的一部分。

三、索引

    索引是對資料庫表中一列或多列的值進行排序的一種結構，使用索引可快速存取資料庫表中的特定資訊。

索引可以加快表與表之間的連結，可以大大加快資料的檢索速度。

    但是索引會帶來額外的開銷，所以我們一般在經常搜尋的列和經常需要串連的列上建立索引。

四、為表設定id屬性

    我們應該為資料庫裡的每張表都設定一個ID做為其主鍵，而且最好的是一個INT型的（推薦使用UNSIGNED），並設定上自動增加的 AUTO_INCREMENT標誌。 

    因為在mysql的資料引擎下，很多操作都需要主鍵，所以死主鍵的效能和設定變得非常重要，比如，叢集，分區……

五、Explain

    使用 EXPLAIN 關鍵字可以讓你知道MySQL是如何處理你的SQL語句的。這可以幫你分析你的查詢語句或是表結構的效能瓶頸。EXPLAIN 的查詢結果還會告訴你你的索引主鍵被如何利用的，你的資料表是如何被搜尋和排序的……等等，等等。 

六、避免select *

    從資料庫裡讀出越多的資料，那麼查詢就會變得越慢。並且，如果你的資料庫伺服器和WEB伺服器是兩台獨立的伺服器的話，這還會增加網路傳輸的負載。 
所以，你應該養成一個需要什麼就取什麼的好的習慣。 

七、調整內部變數

MySQL是如此的開放，所以可輕鬆地進一步調整其預設設定以獲得更優的效能及穩定性。需要最佳化的一些關鍵變數如下：

改變索引緩衝長度(key_buffer)：

　　一般，該變數控制緩衝區的長度在處理索引表（讀/寫操作）時使用。MySQL使用手冊指出該變數可以不斷增加以確保索引表的最佳效能，並推薦使用與系統記憶體25％的大小作為該變數的值。這是MySQL十分重要的組態變數之一，如果你對最佳化和提高系統效能有興趣，可以從改變 key_buffer_size變數的值開始。

改變表長(read_buffer_size)：

　　當一個查詢不斷地掃描某一個表，MySQL會為它分配一段記憶體緩衝區。read_buffer_size變數控制這一緩衝區的大小。如果你認為連續掃描進行得太慢，可以通過增加該變數值以及記憶體緩衝區大小提高其效能。

設定開啟表的數目的最大值(table_cache)：

　　該變數控制MySQL在任何時候開啟表的最大數目，由此能控制伺服器響應輸入請求的能力。它跟max_connections變數密切相關，增加 table_cache值可使MySQL開啟更多的表，就如增加max_connections值可增加串連數一樣。當收到大量不同資料庫及表的請求時，可以考慮改變這一值的大小。

對緩長查詢設定一個時間限制(long_query_time)：

MySQL帶有“慢查詢日誌”，它會自動地記錄所有的在一個特定的時間範圍內尚未結束的查詢。這個日誌對於跟蹤那些低效率或者行為不端的查詢以及尋找最佳化對象都非常有用。long_query_time變數控制這一最大時間限定，以秒為單位。

八、使用 Limit 1

    當我們要一行資料時應該使用 LIMIT 1，這樣，MySQL資料庫引擎會在找到一條資料後停止搜尋，而不是繼續往後查少下一條符合記錄的資料。

文章到此就結束了，但是作者對於mysql最佳化的理解僅僅是管中窺豹，而更多的最佳化策略需要大家親自去探索去實踐喔。