MySQL效能最佳化

標籤：_id   mysqldump   啟動   解析   insert   使用   進階   查詢語句   如何   

1 儲存引擎的選擇

   InnoDB和MyISAM是許多人在使用MySQL時最常用的兩個表類型，這兩個表類型各有優劣，視具體應用而定。基本的差別為：MyISAM類型不支援交易處理等進階處理，而InnoDB類型支援。MyISAM類型的表強調的是效能，其執行數度比InnoDB類型更快，但是不提供事務支援，而InnoDB提供事務支援以及外部鍵等進階資料庫功能。

 

1.1  InnoDB和MyISAM的差別

　　◆1.InnoDB不支援FULLTEXT類型的索引。

　　◆2.InnoDB 中不儲存表的具體行數，也就是說，執行select count(*)  from table時，InnoDB要掃描一遍整個表來計算有多少行，但是MyISAM只要簡單的讀出儲存好的行數即可。注意的是，當count(*)語句包含 where條件時，兩種表的操作是一樣的。

　　◆3.對於AUTO_INCREMENT類型的欄位，InnoDB中必須包含只有該欄位的索引，但是在MyISAM表中，可以和其他欄位一起建立聯合索引。

　　◆4.DELETE FROM table時，InnoDB不會重建立立表，而是一行一行的刪除。

　　◆5.LOAD TABLE FROM MASTER操作對InnoDB是不起作用的，解決方案是首先把InnoDB表改成MyISAM表，匯入資料後再改成InnoDB表，但是對於使用的額外的InnoDB特性(例如外鍵)的表不適用。

另外，InnoDB表的行鎖也不是絕對的，假如在執行一個SQL語句時MySQL不能確定要掃描的範圍，InnoDB表同樣會鎖全表，例如update table set num=1 where name like “%aaa%”

 

1.2  InnoDB和MyISAM的選擇

　　兩種類型最主要的差別就是Innodb 支援交易處理與外鍵和行級鎖。而MyISAM不支援.所以MyISAM往往就容易被人認為只適合在小項目中使用。

　　作為使用MySQL的使用者角度出發，Innodb和MyISAM都是比較喜歡的，如果資料庫平台要達到需求：99.9%的穩定性，方便的擴充性和高可用性來說的話，MyISAM絕對是首選。

　　原因如下：

　　1、平台上承載的大部分項目是讀多寫少的項目，而MyISAM的讀效能是比Innodb強不少的。

　　2、MyISAM的索引和資料是分開的，並且索引是有壓縮的，記憶體使用量率就對應提高了不少。能載入更多索引，而Innodb是索引和資料是緊密捆綁的，沒有使用壓縮從而會造成Innodb比MyISAM體積龐大不小。

　　3、經常隔1，2個月就會發生應用開發人員不小心update一個表where寫的範圍不對，導致這個表沒法正常用了，這個時候MyISAM的優越性就體現出來了，隨便從當天拷貝的壓縮包取出對應表的檔案，隨便放到一個資料庫目錄下，然後dump成sql再導回到主庫，並把對應的binlog補上。如果是Innodb，恐怕不可能有這麼快速度，別和我說讓Innodb定期用匯出xxx.sql機製備份，因為最小的一個資料庫執行個體的資料量基本都是幾十G大小。

　　4、從接觸的應用邏輯來說，select count(*) 和order by 是最頻繁的，大概能佔了整個sql總語句的60%以上的操作，而這種操作Innodb其實也是會鎖表的，很多人以為Innodb是行級鎖，那個只是where對它主鍵是有效，非主鍵的都會鎖全表的。

　　5、還有就是經常有很多應用部門需要我給他們定期某些表的資料，MyISAM的話很方便，只要發給他們對應那表的frm.MYD,MYI的檔案，讓他們自己在對應版本的資料庫啟動就行，而Innodb就需要匯出xxx.sql了，因為光給別人檔案，受字典資料檔案的影響，對方是無法使用的。

　　6、如果和MyISAM比insert寫操作的話，Innodb還達不到MyISAM的寫效能，如果是針對基於索引的update操作，雖然MyISAM可能會遜色Innodb,但是那麼高並發的寫，從庫能否追的上也是一個問題，還不如通過多執行個體分庫分表架構來解決。

　　7、如果是用MyISAM的話，merge引擎可以大大加快應用部門的開發速度，他們只要對這個merge表做一些select count(*)操作，非常適合大項目總量約幾億的rows某一類型(如日誌，調查統計)的業務表。

　　當然Innodb也不是絕對不用，用事務的項目就用Innodb的。另外，可能有人會說你MyISAM無法抗太多寫操作，但是可以通過架構來彌補。

 

2 參數配置

2.1  串連

串連通常來自Web伺服器，下面列出了一些與串連有關的參數，以及該如何設定它們。

1、max_connections

這是Web伺服器允許的最大串連數，記住每個串連都要使用會話記憶體。

 

2、max_packet_allowed

最大資料包大小，通常等於你需要在一個大塊中返回的最大資料集的大小，如果你在使用遠程mysqldump，那它的值需要更大。

 

3、aborted_connects

檢查系統狀態的計數器，確定其沒有增長，如果數量增長說明用戶端串連時遇到了錯誤。

 

4、thread_cache_size

入站串連會在MySQL中建立一個新的線程，因為MySQL中開啟和關閉串連都很廉價，速度也快，它就沒有象其它資料庫，如Oracle那麼多持續串連了，但線程預先建立並不會節約時間，這就是為什麼要MySQL線程緩衝的原因了。

如果在增長請密切注意建立的線程，讓你的線程緩衝更大，對於2550或100的thread_cache_size，記憶體佔用也不多。

 

2.2  查詢快取

MySQL查詢快取可以跳過SQL解析最佳化查詢等階段,直接返回緩衝結果給使用者。緩衝存在一個hash表中,通過查詢SQL,查詢資料庫,用戶端協議等作為key.在判斷是否命中前,MySQL不會解析SQL,而是直接使用SQL去查詢快取,SQL任何字元上的不同,如空格,注釋,都會導致緩衝不命中.

如果查詢中有不確定資料,例如CURRENT_DATE()和NOW()函數,那麼查詢完畢後則不會被緩衝.所以,包含不確定資料的查詢是肯定不會找到可用緩衝的。

工作流程

1. 伺服器接收SQL,以SQL和一些其他條件為key尋找緩衝表(額外效能消耗)

2. 如果找到了緩衝,則直接返回緩衝(效能提升)

3. 如果沒有找到緩衝,則執行SQL查詢,包括原來的SQL解析,最佳化等.

4. 執行完SQL查詢結果以後,將SQL查詢結果存入緩衝表(額外效能消耗)

緩衝失效

當某個表正在寫入資料,則這個表的緩衝(命中檢查,緩衝寫入等)將會處於失效狀態.在Innodb中,如果某個事務修改了表,則這個表的緩衝在事務提交前都會處於失效狀態,在這個事務提交前,這個表的相關查詢都無法被緩衝。

緩衝的記憶體管理

緩衝會在記憶體中開闢一塊記憶體(query_cache_size)來維護快取資料,其中有大概40K的空間是用來維護緩衝的中繼資料的,例如空間記憶體,資料表和查詢結果的映射,SQL和查詢結果的映射等.

MySQL將這個大記憶體塊分為小的記憶體塊(query_cache_min_res_unit),每個小塊中儲存自身的類型,大小和查詢結果資料,還有指向前後記憶體塊的指標.

MySQL需要設定單個小儲存塊的大小,在SQL查詢開始(還未得到結果)時就去申請一塊空間,所以即使你的快取資料沒有達到這個大小,也需要用這個大小的資料區塊去存(這點跟Linux檔案系統的Block一樣).如果結果超出這個記憶體塊的大小,則需要再去申請一個記憶體塊.當查詢完成發現申請的記憶體塊有富餘,則會將富餘的空間釋放掉,這就會造成記憶體片段問題。

緩衝配置參數

1. query_cache_type: 是否開啟緩衝

1) OFF: 關閉

2) ON: 總是開啟

3) DEMAND: 只有明確寫了SQL_CACHE的查詢才會吸入緩衝

2. query_cache_size: 緩衝使用的總記憶體空間大小,單位是位元組,這個值必須是1024的整數倍,否則MySQL實際分配可能跟這個數值不同(感覺這個應該跟檔案系統的blcok大小有關)

3. query_cache_min_res_unit: 分配記憶體塊時的最小單位大小

4. query_cache_limit: MySQL能夠緩衝的最大結果,如果超出,則增加 Qcache_not_cached的值,並刪除查詢結果

5. query_cache_wlock_invalidate: 如果某個資料表被鎖住,是否仍然從緩衝中返回資料,預設是OFF,表示仍然可以返回

 

2.3  暫存資料表

記憶體速度是相當快的，因此我們希望所有的排序操作都在記憶體中進行，我們可以通過調整查詢讓結果集更小以實現記憶體排序，或將變數設定得更大。

tmp_table_size

max_heap_table_size

無論何時在MySQL中建立暫存資料表，它都會使用這兩個變數的最小值作為臨界值，除了在磁碟上構建暫存資料表外，還會建立許多會話，這些會話會搶佔有 限制的資源，因此最好是調整查詢而不是將這些參數設定得更高，同時，需要注意的是有BLOB或TEXT欄位類型的表將直接寫入磁碟。

 

2.4  會話記憶體

MySQL中每個會話都有其自己的記憶體，這個記憶體就是分配給SQL查詢的記憶體，因此你想讓它變得儘可能大以滿足需要。但你不得不平衡同一時間數 據庫內一致性會話的數量。這裡顯得有點黑色藝術的是MySQL是按需分配緩衝的，因此，你不能只添加它們並乘以會話的數量，這樣估算下來比MySQL典型 的使用要大得多。

最佳做法是啟動MySQL，串連所有會話，然後繼續關注頂級會話的VIRT列，mysqld行的數目通常保持相對穩定，這就是實際的記憶體 總用量，減去所有的靜態MySQL記憶體地區，就得到了實際的所有會話記憶體，然後除以會話的數量就得到平均值。

1、read_buffer_size

緩衝連續掃描的塊，這個緩衝是跨儲存引擎的，不只是MyISAM表。

2、sort_buffer_size

執行排序緩衝區的大小，最好將其設定為1M-2M，然後在會話中設定，為一個特定的查詢設定更高的值。

3、join_buffer_size

執行聯集查詢分配的緩衝區大小，將其設定為1M-2M大小，然後在每個會話中再單獨按需設定。

4、read_rnd_buffer_size

用於排序和order by操作，最好將其設定為1M，然後在會話中可以將其作為一個會話變數設定為更大的值。

 

2.5  慢查詢日誌

慢速查詢日誌是MySQL很有用的一個特性。

1、log_slow_queries

MySQL參數中log_slow_queries參數在my.cnf檔案中設定它，將其設定為on，預設情況下，MySQL會將檔案放到資料目錄，檔案以“主機名稱-slow.log”的形式命名，但你在設定這個選項的時候也可以為其指定一個名字。

2、long_query_time

預設值是10秒，你可以動態設定它，值從1到將其設定為on，如果資料庫啟動了，預設情況下，日誌將關閉。截至5.1.21和安裝了 Google補丁的版本，這個選項可以以微秒設定，這是一個了不起的功能，因為一旦你消除了所有查詢時間超過1秒的查詢，說明調整非常成功，這樣可以協助 你在問題變大之前消除問題SQL。

3、log_queries_not_using_indexes

開啟這個選項是個不錯的主意，它真實地記錄了返回所有行的查詢。

 

2.6  小結

我們介紹了MySQL參數的五大類設定，平時我們一般都很少碰它們，在進行MySQL效能調優和故障診斷時這些參數還是非常有用的。

MySQL中的緩衝查詢包括兩個解析查詢計劃，以及返回的資料集，如果基礎資料表資料或結構有變化，將會使查詢快取中的項目無效。

1、query_cache_min_res_unit

MySQL參數中query_cache_min_res_unit查詢快取中的塊是以這個大小進行分配的，使用下面的公式計算查詢快取的平均大小，根據計算結果設定這個變數，MySQL就會更有效地使用查詢快取，緩衝更多的查詢，減少記憶體的浪費。

2、query_cache_size

這個參數設定查詢快取的總大小。

3、query_cache_limit

這個參數告訴MySQL丟掉大於這個大小的查詢，一般大型查詢還是比較少見的，如運行一個批處理執行一個大型報表的統計，因此那些大型結果集不應該填滿查詢快取。

 

3  查詢語句最佳化 

Explain用於顯示當前sql語句的執行情況，包括調用了索引，所以我們可以根據這個語句給表加需要的索引。

explain 顯示內容的時候最重要的是keys這個欄位，這個欄位表示該查詢中用到了那個索引，如果沒有用到索引的話就顯示Null，這時候我們根據要求增加合適的索引，如果用到了索引就顯示用到的索引。

下面只說兩個explain列出的三個重要的參數：

Type 表連線類型 依次是從最好的到最差的

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般來說，得保證查詢至少達到range層級，最好能達到ref。

Const該類型用在 表中最多隻有一行匹配的記錄，它在一開始查詢的時候就被讀取出來。並且用到了primary或者unique的時候。（也就是說只要用到了primary或者unique索引的並且匹配的記錄只有一條的，所用的類型就是const）這裡通篇以ecshop中的ecs_goods表為例

Explain select * from ecs_goods where goods_id = 10這種情況用的類型是const因為其中goods_id是主鍵 primary key

Explain select * from ecs_goods where goods_id < 10 這種用的就不是const

Explain select * from ecs_goods where add_time = 123445324  這種情況用的類型也是const，因為add_time是unique索引

Eq_ref  適用於有表串連的sql語句，並且只能從表中讀取傳一條語句，並且用到的索引必須是primary_key或者unique

Ref 該表中所有匹配的記錄都會被讀取出來，並且用到的索引不能是primary或者unique，這是最普通最常用的一種類型

All 這種情況非常糟糕，是因為表中沒有建立索引，所以要對該表做全部掃描

Possible_keys 指的是該sql語句在搜尋表時，坑內會使用哪個索引，但可能不會使用。如果該參數為空白，則表示沒有索引被用到。這種情況就可以檢查where子句中那些欄位適合加索引以提高查詢效能。

Key 表示在查詢中實際用到的索引，如果當前沒有任何索引被用到則該參數為null，這時候同上，查看where字句中那些欄位適合加索引來提高查詢效能。

好的索引非常重要，好的索引讓查詢有良好的訪問類型並且只檢查需要的行。但是添加索引並不意味著mysql會訪問並且返回同樣的行。

Extra 包含不適合在其他列中顯示但十分重要的額外資訊。如果是using temporary或者using filesort，則表示有需要改進的地方。

 

MySQL效能最佳化