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