MySQL常用存儲引擎優(yōu)化

前言：

MySQL 提供的非常豐富的存儲引擎種類供大家選擇，有多種選擇固然是好事，但是需要我們理解掌握的知識也會增加很多。每一種存儲引擎都有各自的特長，也都存在一定的短處。如何將各種存儲引擎在自己的應(yīng)用環(huán)境中結(jié)合使用，揚(yáng)長避短，也是一門不太簡單的學(xué)問。本章選擇最為常用的兩種存儲引擎進(jìn)行針對性的優(yōu)化建議，希望能夠?qū)ψx者朋友有一定的幫助。

MyI SAM存儲引擎優(yōu)化

我們知道，MyISAM 存儲引擎是 MySQL 最為古老的存儲引擎之一，也是最為流行的存儲引擎之一。對于以讀請求為主的非事務(wù)系統(tǒng)來說，MyISAM 存儲引擎由于其優(yōu)異的性能表現(xiàn)及便利的維護(hù)管理方式無疑是大家最優(yōu)先考慮的對象。這一節(jié)我們將通過分析MyISAM存儲引擎的相關(guān)特性，來尋找提高M(jìn)yISAM存儲引擎性能的優(yōu)化策略。

索引緩存優(yōu)化

MyISAM 存儲引擎的緩存策略是其和很多其他數(shù)據(jù)庫乃至 MySQL 數(shù)據(jù)庫的很多其他存儲引擎不太一樣的最大特性。因?yàn)樗麅H僅緩存索引數(shù)據(jù)，并不會緩存實(shí)際的表數(shù)據(jù)信息到內(nèi)存中，而是將這一工作交給了 OS 級別的文件系統(tǒng)緩存。

所以，在數(shù)據(jù)庫優(yōu)化中非常重要的優(yōu)化環(huán)節(jié)之一“緩存優(yōu)化”的工作在使用 MyISAM 存儲引擎的數(shù)據(jù)庫的情況下，就完全集中在對索引緩存的優(yōu)化上面了。

在分析優(yōu)化索引緩存策略之前，我們先大概了解一下 MyISAM 存儲引擎的索引實(shí)現(xiàn)機(jī)制以及索引文件的存放格式。MyISAM 存儲引擎的索引和數(shù)據(jù)是分開存放于“.MYI”文件中，每個“.MYI”文件由文件頭和實(shí)際的索引數(shù)據(jù)?！?MYI”的文件頭中主要存放四部分信息，分別稱為：state（主要是整個索引文件的基本信息），base（各個索引的相關(guān)信息，主要是索引的限制信息），keydef（每個索引的定義信息）和recinfo（每個索引記錄的相關(guān)信息）。在文件頭后面緊接著的就是實(shí)際的索引數(shù)據(jù)信息了。索引數(shù)據(jù)以Block（Page）為最小單位，每個 block 中只會存在同一個索引的數(shù)據(jù)，這主要是基于提高索引的連續(xù)讀性能的目的。在 MySQL 中，索引文件中索引數(shù)據(jù)的 block 被稱為 Index Block，每個 Index Block 的大小并不一定相等。

在“.MYI”中，Index Block 的組織形式實(shí)際上只是一種邏輯上的，并不是物理意義上的。在物理上，實(shí)際上是以 File Block 的形式來存放在磁盤上面的。在 Key Cache 中緩存的索引信息是以“Cache Block”的形式組織存放的，“Cache Block”是相同大小的，和“.MYI”文件物理存儲的 Block（ File Block ） 一 樣 。 在 一 條 Query 通 過 索 引 檢 索 表 數(shù) 據(jù) 的 時 候 ， 首 先 會 檢 查 索 引 緩 存（key_buffer_cache）中是否已經(jīng)有需要的索引信息，如果沒有，則會讀取“.MYI”文件，將相應(yīng)的索引數(shù)據(jù)讀入 Key Cache 中的內(nèi)存空間中，同樣也是以 Block 形式存放，被稱為 Cache Block。不過，數(shù)據(jù)的讀入并不是以 Index Block 的形式來讀入，而是以 File Block 的形式來讀入的。以 File Block 形式讀入到 Key Cache 之后的 Cache Block 實(shí)際上是于 File Block 完全一樣的。如下圖所示：

經(jīng)沒有空閑的 Cache Block 可以使用的話，將會通過 MySQL 實(shí)現(xiàn)的 LRU 相關(guān)算法將某些 Cache Block清除出去，讓新進(jìn)來的 File Block 有地方呆。我們先來分析一下與 MyISAM 索引緩存相關(guān)的幾個系統(tǒng)參數(shù)和狀態(tài)參數(shù)：

◆ key_buffer_size，索引緩存大小；

這個參數(shù)用來設(shè)置整個 MySQL 中的常規(guī) Key Cache 大小。一般來說，如果我們的 MySQL 是運(yùn)行在 32 位平臺紙上，此值建議不要超過 2GB 大小。如果是運(yùn)行在 64 位平臺紙上則不用考慮此限制，但也最好不要超過 4GB。

◆ key_buffer_block_size，索引緩存中的 Cache Block Size；

在前面我們已經(jīng)介紹了，在 Key Cache 中的所有數(shù)據(jù)都是以 Cache Block 的形式存在，而key_buffer_block_size 就是設(shè)置每個 Cache Block 的大小，實(shí)際上也同時限定了我們將“.MYI”文件中的 Index Block 被讀入時候的 File Block 的大小。

◆ key_cache_division_limit，LRU 鏈表中的 Hot Area 和 Warm Area 分界值；

實(shí)際上，在 MySQL 的 Key Cache 中所使用的 LRU 算法并不像傳統(tǒng)的算法一樣僅僅只是通過訪問頻率以及最后訪問時間來通過一個唯一的鏈表實(shí)現(xiàn)，而是將其分成了兩部分。一部分用來存放使用比較頻繁的 Hot Cacke Lock（Hot Chain），被成為 Hot Area，另外一部分則用來存放使用不是太頻繁的 Warm Cache Block（Warm Chain），被成為 Warm Area。這樣做的目的主要是為了保護(hù)使用比較頻繁的 Cache Block 更不容易被換出。而 key_cache_division_limit 參數(shù)則是告訴MySQL該如何劃分整個Cache Chain劃分為Hot Chain和 Warm Chain 兩部分，參數(shù)值為 WarmChain 占整個 Chain 的百分比值。設(shè)置范圍 1～100，系統(tǒng)默認(rèn)為 100，也就是只有 Warm Chain。

◆ key_cache_age_threshold，控制 Cache Block 從 Hot Area 降到 Warm Area 的限制；

key_cache_age_threshold參數(shù)控制Hot Area中的Cache Block何時該被降級到Warm Area中。

系統(tǒng)默認(rèn)值為 300，最小可以設(shè)置為 100。值越小，被降級的可能性越大。

通過以上參數(shù)的合理設(shè)置，我們基本上可以完成 MyISAM 整體優(yōu)化的 70％的工作。但是如何的合理設(shè)置這些參數(shù)卻不是一個很容易的事情。尤其是 key_cache_division_limit 和 key_cache_age_threshold這兩個參數(shù)的合理使用。

對于 key_buffer_size 的設(shè)置我們一般需要通過三個指標(biāo)來計算，第一個是系統(tǒng)索引的總大小，第二個是系統(tǒng)可用物理內(nèi)存，第三個是根據(jù)系統(tǒng)當(dāng)前的 Key Cache 命中率。對于一個完全從零開始的全新系統(tǒng)的話，可能出了第二點(diǎn)可以拿到很清楚的數(shù)據(jù)之外，其他的兩個數(shù)據(jù)都比較難獲取，第三點(diǎn)是完全沒有。當(dāng)然，我們可以通過 MySQL 官方手冊中給出的一個計算公式粗略的估算一下我們系統(tǒng)將來的索引大小，不過前提是要知道我們會創(chuàng)建哪些索引，然后通過各索引估算出索引鍵的長度，以及表中存放數(shù)據(jù)的條數(shù)，公式如下：

Key_Size = key_number * (key_length+4)/0.67Max_key_buffer_size < Max_RAM - QCache_Usage - Threads_Usage - System_UsageThreads_Usage=max_connections*(sort_buffer_size+join_buffer_size+read_buffer_size + read_rnd_buffer_size + thread_stack)

當(dāng)然，考慮到活躍數(shù)據(jù)的問題，我們并不需要將 key_buffer_size 設(shè)置到可以將所有的索引都放下的大小，這時候我們就需要 Key Cache 的命中率數(shù)據(jù)來幫忙了。下面我們再來看一下系統(tǒng)中記錄的與 KeyCache 相關(guān)的性能狀態(tài)參數(shù)變量。

◆ Key_blocks_not_flushed，已經(jīng)更改但還未刷新到磁盤的 Dirty Cache Block；

◆ Key_blocks_unused，目前未被使用的 Cache Block 數(shù)目；

◆ Key_blocks_used，已經(jīng)使用了的 Cache Block 數(shù)目；

◆ Key_read_requests，Cache Block 被請求讀取的總次數(shù)；

◆ Key_reads，在 Cache Block 中找不到需要讀取的 Key 信息后到“.MYI”文件中讀取的次數(shù)；

◆ Key_write_requests，Cache Block 被請求修改的總次數(shù)；

◆ Key_writes，在 Cache Block中找不到需要修改的Key 信息后到“.MYI”文件中讀入再修改的次數(shù)；由于上面各個狀態(tài)參數(shù)在 MySQL 官方文檔中都有較為詳細(xì)的描述，所以上面僅做基本的說明。當(dāng)我們的系統(tǒng)上線之后，我們就可以通過上面這些狀態(tài)參數(shù)的狀態(tài)值得到系統(tǒng)當(dāng)前的 Key Cache 使用的詳細(xì)情況和性能狀態(tài)。

Key_buffer_UsageRatio = (1 - Key_blocks_used/(Key_blocks_used + Key_blocks_unused)) *100%Key_Buffer_Read_HitRatio = (1 - Key_reads/Key_read_requests) * 100%Key_Buffer_Write_HitRatio = (1 - Key_writes/Key_Write_requests) * 100%

通過上面的這三個比率數(shù)據(jù)，就可以很清楚的知道我們的 Key Cache 設(shè)置是否合理，尤其是Key_Buffer_Read_HitRatio 參 數(shù) 和 Key_buffer_UsageRatio 這 兩 個 比 率 。 一 般 來 說Key_buffer_UsageRatio 應(yīng)該在 99%以上甚至 100%，如果該值過低，則說明我們的 key_buffer_size 設(shè)置過大，MySQL 根本使用不完。Key_Buffer_Read_HitRatio 也應(yīng)該盡可能的高。如果該值較低，則很有可能 是 我 們 的 key_buffer_size 設(shè) 置 過 小 ， 需 要 適 當(dāng) 增 加 key_buffer_size 值 ， 也 有 可 能 是key_cache_age_threshold和key_cache_division_limit的設(shè)置不當(dāng)，造成Key Cache cache失效太快。

一般來說，在實(shí)際應(yīng)用場景中，很少有人調(diào)整 key_cache_age_threshold 和 key_cache_division_limit這兩個參數(shù)的值，大都是使用系統(tǒng)的默認(rèn)值。

多 Key Cache 的使用從 MySQL4.1.1 版本開始，MyISAM 開始支持多個 Key Cache 并存的的功能。也就是說我們可以根據(jù)不同的需要設(shè)置多個 Key Cache 了，如將使用非常頻繁而且基本不會被更新的表放入一個 Key Cache 中以防止在公共Key Cache中被清除出去，而那些使用并不是很頻繁而且可能會經(jīng)常被更新的Key 放入另外一個 Key Cache 中。這樣就可以避免出現(xiàn)某些場景下大批量的 Key 被讀入 Key Cache 的時候，因?yàn)?KeyCache 空間問題使本來命中率很高的 Key 也不得不被清除出去。

MySQL 官方建議在比較繁忙的系統(tǒng)上一般可以設(shè)置三個 Key Cache：一個 Hot Cache 使用 20%的大小用來存放使用非常頻繁且更新很少的表的索引；一個 Cold Cache 使用 20%的大小用來存放更新很頻繁的表的索引；一個 Warm Cache 使用剩下的 60%空間，作為整個系統(tǒng)默認(rèn)的 Key Cache；多個Key Cache的具體使用方法在MySQL 官方手冊中有比較詳細(xì)的介紹，這里就不再累述了，有興趣的讀者朋友可以自行查閱研究。