1. 說到mysql，我們立刻想起它體積小、速度快、還開源的特點(diǎn)，所以它應(yīng)用頗廣。今天我們來總結(jié)一下mysql中最頻繁的兩個操作：插入和查詢，的優(yōu)化方法。
2.插入：
3.一、文本導(dǎo)入
4.使用LOAD DATA INFILE從文本下載數(shù)據(jù)這將比使用插入語句快20倍。
5.示例：
6.load data local infile 'C:/Users/DELL/Desktop/Description&Data/news1.txt' into table news （文件位置）
7.fields terminated by ',' （每一字段由‘，’分隔開）
8.lines terminated by '\r\n' （每一組數(shù)據(jù)由 換行符 分隔開）
9.(content,date,ip,author,subject); （解釋txt中每一行由這幾個字段組成）
10.二、一次插多條
11.使用帶有多個VALUES列表的INSERT語句一次插入幾行比使用一個單行插入語句快幾倍。
12.示例：
13.INSERT INTO food VALUES
14.(NULL,'EE果凍','EE果凍廠', 1.5 ,'2007', 2 ,'北京') ,
15.(NULL,'FF咖啡','FF咖啡廠', 20 ,'2002', 5 ,'天津') ,
16.(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.第一種方式減少了與數(shù)據(jù)庫之間的連接等操作，所以其速度比第二種方式要快。
三、使用varchar
之所以使用varchar，而不是char，因?yàn)関archar是按實(shí)際數(shù)據(jù)的長度存儲的；而char在存儲完實(shí)際數(shù)據(jù)后，還把空白的空間自動補(bǔ)全。所以明顯char插入會比varchar慢。實(shí)驗(yàn)說明，無論插入數(shù)據(jù)涉及的列是否建立索引，char的效率都明顯低于varchar。
四、控制字段長度
這個很明顯，越小的數(shù)據(jù)類型占用的空間越小，從磁盤讀或打包到內(nèi)存的效率都更好，但也不要太過執(zhí)著減小數(shù)據(jù)類型，要是以后應(yīng)用程序發(fā)生什么變化就沒有空間了，所以要綜合考慮決定字段長度。
五、非空+默認(rèn)值
NULL對于大多數(shù)數(shù)據(jù)庫都需要特殊處理，MySQL也不例外，它需要更多的代碼，更多的檢查和特殊的索引邏輯，所以我們最好把屬性盡量設(shè)置成非空，同時賦予它一個默認(rèn)值，比如 0。
六、禁用事務(wù)
MySQL數(shù)據(jù)庫表有兩種類型，一種是支持事務(wù)處理，一種是不支持事務(wù)處理。MySQL在處理這兩種表時，分別使用了不同類型的數(shù)據(jù)庫引擎，因此數(shù)據(jù)庫引擎在插入時效率不同，理論上說啟用了事務(wù)功能后會比較慢。
示例：
Create Table(
….. /*字段說明*/
) ENGINE=InnoDB/MyISAM (帶事務(wù)、不帶事務(wù))
事實(shí)證明是否禁用事務(wù)對插入數(shù)據(jù)的速度影響很大。
不過還是要多說一句，事務(wù)并非只會讓我們的插入變慢。用了事務(wù)，就不可避免的要犧牲一部分速度，但是可以很大程度上保證數(shù)據(jù)的正確性。
七、禁用索引
插入記錄時，MySQL會根據(jù)表的索引對插入的記錄進(jìn)行排序。如果插入大量數(shù)據(jù)時，這些排序會降低插入記錄的速度。為了解決這種情況，在插入記錄之前先禁用索引。等到記錄都插入完畢后再開啟索引。（雖然對于先插數(shù)據(jù)還是先建索引可能有一點(diǎn)爭議）
示例：
1.ALTER TABLE 表名 DISABLE KEYS ; （禁用索引）
2.
1.ALTER TABLE 表名 ENABLE KEYS ; （啟用索引）
1.
1.
八、禁用唯一性檢查
我們知道，插入數(shù)據(jù)時MySQL會對插入的記錄進(jìn)行唯一性校驗(yàn)。這種校驗(yàn)也會降低插入記錄的速度?？梢栽诓迦胗涗浿敖梦ㄒ恍詸z查。等到記錄插入完畢后再開啟。禁用唯一性檢查的語句如下：
1.SET UNIQUE_CHECKS=0;
2.重新開啟唯一性檢查的語句如下：
1.SET UNIQUE_CHECKS=1;
九、先鎖定表再插入
這將提高數(shù)據(jù)庫性能，因?yàn)樗饕彌_區(qū)只是在所有的插入語句完成后才對磁盤進(jìn)行一次刷新。通常情況下，有多少個插入語句就會有多少次索引緩沖區(qū)刷新。如果你可以用一個插入語句實(shí)現(xiàn)所有行的插入，則無需使用顯式鎖定語句。
示例：
LOCK TABLES； （鎖定表）
十、啟用并行插入
可以對myisam表并行插入Concurrent_insert系統(tǒng)變量可以被設(shè)置用于修改concurrent-insert處理。該變量默認(rèn)設(shè)置為1。如果concurrent_insert被設(shè)置為0，并行插入就被禁用。如果該變量被設(shè)置為2，在表的末端可以并行插入，即便該表的某些行已經(jīng)被刪除。
十一、延遲插入
如果你的客戶不能或無需等待插入完成的時候，這招很有用。當(dāng)你使用MySQL存儲，并定期運(yùn)行需要很長時間才能完成的SELECT和UPDATE語句的時候，你會發(fā)現(xiàn)這種情況很常見。當(dāng)客戶使用插入延遲，服務(wù)器立刻返回，如果表沒有被其他線程調(diào)用，則行會列隊(duì)等待被插入。使用插入延遲的另一個好處就是從多個客戶插入的情況會被綁定并記錄在同一個block中。這將比處理多個獨(dú)立的插入要快得多。
查詢：
一、優(yōu)化數(shù)據(jù)類型
查詢優(yōu)化方面，數(shù)據(jù)類型是查詢的基礎(chǔ)，所以我們首先得優(yōu)化我們的數(shù)據(jù)類型。實(shí)際上，數(shù)據(jù)類型方面，查詢所需要的優(yōu)化和插入差不多，主要也是避免null和盡量使用小的字段。
二、使用連接查詢
使用連接查詢效率一般都優(yōu)于子查詢。遇到子查詢時，MySQL查詢優(yōu)化引擎并不是總是最有效的，這就是為什么經(jīng)常將子查詢轉(zhuǎn)換為連接查詢的原因了，優(yōu)化器已經(jīng)能夠正確處理連接查詢了，當(dāng)然要注意的一點(diǎn)是，確保連接表(第二個表)的連接列是有索引的，在第一個表上MySQL通常會相對于第二個表的查詢子集進(jìn)行一次全表掃描，這是嵌套循環(huán)算法的一部分。
三、索引
索引是對數(shù)據(jù)庫表中一列或多列的值進(jìn)行排序的一種結(jié)構(gòu)，使用索引可快速訪問數(shù)據(jù)庫表中的特定信息。
索引可以加快表與表之間的鏈接，可以大大加快數(shù)據(jù)的檢索速度。
但是索引會帶來額外的開銷，所以我們一般在經(jīng)常搜索的列和經(jīng)常需要連接的列上建立索引。
四、為表設(shè)置id屬性
我們應(yīng)該為數(shù)據(jù)庫里的每張表都設(shè)置一個ID做為其主鍵，而且最好的是一個INT型的（推薦使用UNSIGNED），并設(shè)置上自動增加的 AUTO_INCREMENT標(biāo)志。
因?yàn)樵?a target="_blank">mysql的數(shù)據(jù)引擎下，很多操作都需要主鍵，所以死主鍵的性能和設(shè)置變得非常重要，比如，集群，分區(qū)……
五、Explain
使用 EXPLAIN 關(guān)鍵字可以讓你知道MySQL是如何處理你的SQL語句的。這可以幫你分析你的查詢語句或是表結(jié)構(gòu)的性能瓶頸。EXPLAIN 的查詢結(jié)果還會告訴你你的索引主鍵被如何利用的，你的數(shù)據(jù)表是如何被搜索和排序的……等等，等等。
六、避免select *
從數(shù)據(jù)庫里讀出越多的數(shù)據(jù)，那么查詢就會變得越慢。并且，如果你的數(shù)據(jù)庫服務(wù)器和WEB服務(wù)器是兩臺獨(dú)立的服務(wù)器的話，這還會增加網(wǎng)絡(luò)傳輸?shù)呢?fù)載。
所以，你應(yīng)該養(yǎng)成一個需要什么就取什么的好的習(xí)慣。
七、調(diào)整內(nèi)部變量
MySQL是如此的開放，所以可輕松地進(jìn)一步調(diào)整其缺省設(shè)置以獲得更優(yōu)的性能及穩(wěn)定性。需要優(yōu)化的一些關(guān)鍵變量如下：
改變索引緩沖區(qū)長度(key_buffer)：
一般，該變量控制緩沖區(qū)的長度在處理索引表（讀/寫操作）時使用。MySQL使用手冊指出該變量可以不斷增加以確保索引表的最佳性能，并推薦使用與系統(tǒng)內(nèi)存25％的大小作為該變量的值。這是MySQL十分重要的配置變量之一，如果你對優(yōu)化和提高系統(tǒng)性能有興趣，可以從改變 key_buffer_size變量的值開始。
改變表長(read_buffer_size)：
當(dāng)一個查詢不斷地掃描某一個表，MySQL會為它分配一段內(nèi)存緩沖區(qū)。read_buffer_size變量控制這一緩沖區(qū)的大小。如果你認(rèn)為連續(xù)掃描進(jìn)行得太慢，可以通過增加該變量值以及內(nèi)存緩沖區(qū)大小提高其性能。
設(shè)定打開表的數(shù)目的最大值(table_cache)：
該變量控制MySQL在任何時候打開表的最大數(shù)目，由此能控制服務(wù)器響應(yīng)輸入請求的能力。它跟max_connections變量密切相關(guān)，增加 table_cache值可使MySQL打開更多的表，就如增加max_connections值可增加連接數(shù)一樣。當(dāng)收到大量不同數(shù)據(jù)庫及表的請求時，可以考慮改變這一值的大小。
對緩長查詢設(shè)定一個時間限制(long_query_time)：
MySQL帶有“慢查詢?nèi)罩尽?，它會自動地記錄所有的在一個特定的時間范圍內(nèi)尚未結(jié)束的查詢。這個日志對于跟蹤那些低效率或者行為不端的查詢以及尋找優(yōu)化對象都非常有用。long_query_time變量控制這一最大時間限定，以秒為單位。
八、使用 Limit 1
當(dāng)我們要一行數(shù)據(jù)時應(yīng)該使用 LIMIT 1，這樣，MySQL數(shù)據(jù)庫引擎會在找到一條數(shù)據(jù)后停止搜索，而不是繼續(xù)往后查少下一條符合記錄的數(shù)據(jù)。
文章到此就結(jié)束了，但是作者對于mysql優(yōu)化的理解僅僅是管中窺豹，而更多的優(yōu)化策略需要大家親自去探索去實(shí)踐喔。
