說到mysql，我們立刻想起它體積小、速度快、還開源的特點(diǎn)，所以它應(yīng)用頗廣。今天我們來總結(jié)一下mysql中最頻繁的兩個(gè)操作：插入和查詢，的優(yōu)化方法。

插入： 

一、文本導(dǎo)入

    使用LOAD DATA INFILE從文本下載數(shù)據(jù)這將比使用插入語句快20倍。

示例：

load data local infile 'C:/Users/DELL/Desktop/Description&Data/news1.txt' into table news         （文件位置）
fields terminated by ','                                            （每一字段由‘，’分隔開）
lines terminated by '\r\n'                                       （每一組數(shù)據(jù)由 換行符 分隔開）
(content,date,ip,author,subject);                           （解釋txt中每一行由這幾個(gè)字段組成）

二、一次插多條

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

示例：

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，因?yàn)関archar是按實(shí)際數(shù)據(jù)的長(zhǎng)度存儲(chǔ)的；而char在存儲(chǔ)完實(shí)際數(shù)據(jù)后，還把空白的空間自動(dòng)補(bǔ)全。所以明顯char插入會(huì)比varchar慢。實(shí)驗(yàn)說明，無論插入數(shù)據(jù)涉及的列是否建立索引，char的效率都明顯低于varchar。

四、控制字段長(zhǎng)度

    這個(gè)很明顯，越小的數(shù)據(jù)類型占用的空間越小，從磁盤讀或打包到內(nèi)存的效率都更好，但也不要太過執(zhí)著減小數(shù)據(jù)類型，要是以后應(yīng)用程序發(fā)生什么變化就沒有空間了，所以要綜合考慮決定字段長(zhǎng)度。

五、非空+默認(rèn)值

    NULL對(duì)于大多數(shù)數(shù)據(jù)庫都需要特殊處理，MySQL也不例外，它需要更多的代碼，更多的檢查和特殊的索引邏輯，所以我們最好把屬性盡量設(shè)置成非空，同時(shí)賦予它一個(gè)默認(rèn)值，比如 0。

六、禁用事務(wù)

    MySQL數(shù)據(jù)庫表有兩種類型，一種是支持事務(wù)處理，一種是不支持事務(wù)處理。MySQL在處理這兩種表時(shí)，分別使用了不同類型的數(shù)據(jù)庫引擎，因此數(shù)據(jù)庫引擎在插入時(shí)效率不同，理論上說啟用了事務(wù)功能后會(huì)比較慢。

示例：

Create Table(

….. /*字段說明*/

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

事實(shí)證明是否禁用事務(wù)對(duì)插入數(shù)據(jù)的速度影響很大。

不過還是要多說一句，事務(wù)并非只會(huì)讓我們的插入變慢。用了事務(wù)，就不可避免的要犧牲一部分速度，但是可以很大程度上保證數(shù)據(jù)的正確性。

七、禁用索引

    插入記錄時(shí)，MySQL會(huì)根據(jù)表的索引對(duì)插入的記錄進(jìn)行排序。如果插入大量數(shù)據(jù)時(shí)，這些排序會(huì)降低插入記錄的速度。為了解決這種情況，在插入記錄之前先禁用索引。等到記錄都插入完畢后再開啟索引。（雖然對(duì)于先插數(shù)據(jù)還是先建索引可能有一點(diǎn)爭(zhēng)議）

示例：

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

八、禁用唯一性檢查

    我們知道，插入數(shù)據(jù)時(shí)MySQL會(huì)對(duì)插入的記錄進(jìn)行唯一性校驗(yàn)。這種校驗(yàn)也會(huì)降低插入記錄的速度?？梢栽诓迦胗涗浿敖梦ㄒ恍詸z查。等到記錄插入完畢后再開啟。禁用唯一性檢查的語句如下：

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

1.         SET UNIQUE_CHECKS=1; 

九、先鎖定表再插入

    這將提高數(shù)據(jù)庫性能，因?yàn)樗饕彌_區(qū)只是在所有的插入語句完成后才對(duì)磁盤進(jìn)行一次刷新。通常情況下，有多少個(gè)插入語句就會(huì)有多少次索引緩沖區(qū)刷新。如果你可以用一個(gè)插入語句實(shí)現(xiàn)所有行的插入，則無需使用顯式鎖定語句。

示例：

           LOCK TABLES；                                     （鎖定表）

十、啟用并行插入

    可以對(duì)myisam表并行插入Concurrent_insert系統(tǒng)變量可以被設(shè)置用于修改concurrent-insert處理。該變量默認(rèn)設(shè)置為1。如果concurrent_insert被設(shè)置為0，并行插入就被禁用。如果該變量被設(shè)置為2，在表的末端可以并行插入，即便該表的某些行已經(jīng)被刪除。

十一、延遲插入

    如果你的客戶不能或無需等待插入完成的時(shí)候，這招很有用。當(dāng)你使用MySQL存儲(chǔ)，并定期運(yùn)行需要很長(zhǎng)時(shí)間才能完成的SELECT和UPDATE語句的時(shí)候，你會(huì)發(fā)現(xiàn)這種情況很常見。當(dāng)客戶使用插入延遲，服務(wù)器立刻返回，如果表沒有被其他線程調(diào)用，則行會(huì)列隊(duì)等待被插入。使用插入延遲的另一個(gè)好處就是從多個(gè)客戶插入的情況會(huì)被綁定并記錄在同一個(gè)block中。這將比處理多個(gè)獨(dú)立的插入要快得多。

查詢：

一、優(yōu)化數(shù)據(jù)類型

    查詢優(yōu)化方面，數(shù)據(jù)類型是查詢的基礎(chǔ)，所以我們首先得優(yōu)化我們的數(shù)據(jù)類型。實(shí)際上，數(shù)據(jù)類型方面，查詢所需要的優(yōu)化和插入差不多，主要也是避免null和盡量使用小的字段。

二、使用連接查詢

    使用連接查詢效率一般都優(yōu)于子查詢。遇到子查詢時(shí)，MySQL查詢優(yōu)化引擎并不是總是最有效的，這就是為什么經(jīng)常將子查詢轉(zhuǎn)換為連接查詢的原因了，優(yōu)化器已經(jīng)能夠正確處理連接查詢了，當(dāng)然要注意的一點(diǎn)是，確保連接表(第二個(gè)表)的連接列是有索引的，在第一個(gè)表上MySQL通常會(huì)相對(duì)于第二個(gè)表的查詢子集進(jìn)行一次全表掃描，這是嵌套循環(huán)算法的一部分。

三、索引

    索引是對(duì)數(shù)據(jù)庫表中一列或多列的值進(jìn)行排序的一種結(jié)構(gòu)，使用索引可快速訪問數(shù)據(jù)庫表中的特定信息。

索引可以加快表與表之間的鏈接，可以大大加快數(shù)據(jù)的檢索速度。

    但是索引會(huì)帶來額外的開銷，所以我們一般在經(jīng)常搜索的列和經(jīng)常需要連接的列上建立索引。

四、為表設(shè)置id屬性

    我們應(yīng)該為數(shù)據(jù)庫里的每張表都設(shè)置一個(gè)ID做為其主鍵，而且最好的是一個(gè)INT型的（推薦使用UNSIGNED），并設(shè)置上自動(dòng)增加的 AUTO_INCREMENT標(biāo)志。 

    因?yàn)樵趍ysql的數(shù)據(jù)引擎下，很多操作都需要主鍵，所以死主鍵的性能和設(shè)置變得非常重要，比如，集群，分區(qū)……

五、Explain

    使用 EXPLAIN 關(guān)鍵字可以讓你知道MySQL是如何處理你的SQL語句的。這可以幫你分析你的查詢語句或是表結(jié)構(gòu)的性能瓶頸。EXPLAIN 的查詢結(jié)果還會(huì)告訴你你的索引主鍵被如何利用的，你的數(shù)據(jù)表是如何被搜索和排序的……等等，等等。 

六、避免select *

    從數(shù)據(jù)庫里讀出越多的數(shù)據(jù)，那么查詢就會(huì)變得越慢。并且，如果你的數(shù)據(jù)庫服務(wù)器和WEB服務(wù)器是兩臺(tái)獨(dú)立的服務(wù)器的話，這還會(huì)增加網(wǎng)絡(luò)傳輸?shù)呢?fù)載。 
所以，你應(yīng)該養(yǎng)成一個(gè)需要什么就取什么的好的習(xí)慣。 

七、調(diào)整內(nèi)部變量

MySQL是如此的開放，所以可輕松地進(jìn)一步調(diào)整其缺省設(shè)置以獲得更優(yōu)的性能及穩(wěn)定性。需要優(yōu)化的一些關(guān)鍵變量如下：

改變索引緩沖區(qū)長(zhǎng)度(key_buffer)：

　　一般，該變量控制緩沖區(qū)的長(zhǎng)度在處理索引表（讀/寫操作）時(shí)使用。MySQL使用手冊(cè)指出該變量可以不斷增加以確保索引表的最佳性能，并推薦使用與系統(tǒng)內(nèi)存25％的大小作為該變量的值。這是MySQL十分重要的配置變量之一，如果你對(duì)優(yōu)化和提高系統(tǒng)性能有興趣，可以從改變 key_buffer_size變量的值開始。

改變表長(zhǎng)(read_buffer_size)：

　　當(dāng)一個(gè)查詢不斷地掃描某一個(gè)表，MySQL會(huì)為它分配一段內(nèi)存緩沖區(qū)。read_buffer_size變量控制這一緩沖區(qū)的大小。如果你認(rèn)為連續(xù)掃描進(jìn)行得太慢，可以通過增加該變量值以及內(nèi)存緩沖區(qū)大小提高其性能。

設(shè)定打開表的數(shù)目的最大值(table_cache)：

　　該變量控制MySQL在任何時(shí)候打開表的最大數(shù)目，由此能控制服務(wù)器響應(yīng)輸入請(qǐng)求的能力。它跟max_connections變量密切相關(guān)，增加 table_cache值可使MySQL打開更多的表，就如增加max_connections值可增加連接數(shù)一樣。當(dāng)收到大量不同數(shù)據(jù)庫及表的請(qǐng)求時(shí)，可以考慮改變這一值的大小。

對(duì)緩長(zhǎng)查詢?cè)O(shè)定一個(gè)時(shí)間限制(long_query_time)：

MySQL帶有“慢查詢?nèi)罩?rdquo;，它會(huì)自動(dòng)地記錄所有的在一個(gè)特定的時(shí)間范圍內(nèi)尚未結(jié)束的查詢。這個(gè)日志對(duì)于跟蹤那些低效率或者行為不端的查詢以及尋找優(yōu)化對(duì)象都非常有用。long_query_time變量控制這一最大時(shí)間限定，以秒為單位。

八、使用 Limit 1

    當(dāng)我們要一行數(shù)據(jù)時(shí)應(yīng)該使用 LIMIT 1，這樣，MySQL數(shù)據(jù)庫引擎會(huì)在找到一條數(shù)據(jù)后停止搜索，而不是繼續(xù)往后查少下一條符合記錄的數(shù)據(jù)。