MySQL數據庫優化（七）

　　為了能最小化磁盤I/OMyISAM 存儲引擎采用了很多數據庫系統使用的一種策略它采用一種機制將最經常訪問的表保存在內存區塊中

　　對索引區塊來說它維護著一個叫索引緩存(索引緩沖)的結構體這個結構體中放著許多那些最常使用的索引區塊的緩沖區塊
　　對數據區塊來說MySQL沒有使用特定的緩存它依靠操作系統的本地文件系統緩存

　　本章首先描述了 MyISAM 索引緩存的基本操作然後討論在MySQL 中所做的改進它提高了索引緩存性能同時能更好地控制緩存操作

　　線程之間不再是串行地訪問索引緩存多個線程可以並行地訪問索引緩存
　　可以設置多個索引緩存同時也能指定數據表索引到特定的緩存中

　　索引緩存機制對 ISAM 表同樣適用不過這種有效性正在減弱自從MySQL 開始 MyISAM 表類型引進之後ISAM 就不再建議使用了MySQL 更是延續了這個趨勢ISAM 類型默認被禁用了

　　可以通過系統變量 key_buffer_size 來控制索引緩存區塊的大小如果這個值大小為那麼就不使用緩存當這個值小得於不足以分配區塊緩沖的最小數量()時也不會使用緩存

　　當索引緩存無法操作時索引文件就只通過操作系統提供的本地文件系統緩沖來訪問(換言之表索引區塊采用的訪問策略和數據區塊的一致)

　　一個索引區塊在 MyISAM 索引文件中是一個連續訪問的單元通常這個索引區塊的大小和B樹索引節點大小一樣(索引在磁盤中是以B樹結構來表示的這個樹的底部時葉子節點葉子節點之上則是非葉子節點)

　　在索引緩存結構中所有的區塊大小都是一樣的這個值可能等於大於或小於表的索引區塊大小通常這兩個值是不一樣的

　　當必須訪問來自任何表的索引區塊時服務器首先檢查在索引緩存中是否有可用的緩沖區塊如果有服務器就訪問緩存中的數據而非磁盤就是說它直接存取緩存而不是存取磁盤否則服務器選擇一個(多個)包含其它不同表索引區塊的緩存緩沖區塊將它的內容替換成請求表的索引區塊的拷貝一旦新的索引區塊在緩存中了索引數據就可以存取了

　　當發生被選中要替換的區塊內容修改了的情況時這個區塊就被認為髒了那麼在替換之前它的內容就必須先刷新到它指向的標索引

　　通常服務器遵循LRU(最近最少使用)策略當要選擇替換的區塊時它選擇最近最少使用的索引區塊為了想要讓選擇變得更容易索引緩存模塊會維護一個包含所有使用區塊特別的隊列(LRU鏈)當一個區塊被訪問了就把它放到隊列的最後位置當區塊要被替換時在隊列開始位置的區塊就是最近最少使用的它就是第一候選刪除對象

　　共享訪問索引緩存

　　在MySQL 以前訪問索引緩存是串行的兩個線程不能並行地訪問索引緩存緩沖服務器處理一個訪問索引區塊的請求只能等它之前的請求處理完結果新的請求所需的索引區塊就不在任何索引緩存環沖區塊中因為其他線程把包含這個索引區塊的緩沖給更新了

　　從MySQL 開始服務器支持共享方式訪問索引緩存

　　沒有正在被更新的緩沖可以被多個線程訪問

　　緩沖正被更新時需要使用這個緩沖的線程只能等到更新完成之後

　　多個線程可以初始化需要替換緩存區塊的請求只要它們不干擾別的線程(也就是它們請求不同的索引區塊因此不同的緩存區塊被替換)

　　共享方式訪問索引緩存令服務器明顯改善了吞吐量

　　多重索引緩存

　　共享訪問索引緩存改善了性能卻不能完全消除線程間的沖突它們仍然爭搶控制管理存取索引緩存緩沖的結構為了更進一步減少索引緩存存取沖突MySQL 提供了多重索引緩存特性這能將不同的表索引指定到不同的索引緩存

　　當有多個索引緩存服務器在處理指定的 MyISAM 表查詢時必須知道該使用哪個默認地所有的 MyISAM 表索引都緩存在默認的索引緩存中想要指定到特定的緩存中可以使用 CACHE INDEX 語句

　　如下語句所示指定表的索 t t 和 t 引緩存到名為 hot_cache 的緩存中

　　注意如果服務器編譯支持存 ISAM 儲引擎了那麼 ISAM 表也使用索引緩存機制不過ISAM 表索引只能使用默認的索引緩存而不能自定義

　　CACHE INDEX 語句中用到的索引緩存是根據用 SET GLOBAL 語句的參數設定的值或者服務器啟動參數指定的值創建的如下
　　mysql> SET GLOBAL keycachekey_buffer_size=*;
　　想要刪除索引緩存只需設置它的大小為
　　mysql> SET GLOBAL keycachekey_buffer_size=;
　　索引緩存變量是一個結構體變量由名字和組件構成例如 keycachekey_buffer_size keycache
就是緩存名key_buffer_size 是緩存組件
　　默認地表索引在服務器啟動時指定到主(默認的)索引緩存中當一個索引緩存被刪掉後指定到這個緩存的所有索引都被重新指向到了默認索引緩存中去
　　對一個繁忙的系統來說我們建議以下三條策略來使用索引緩存
　　熱緩存占用%的總緩存空間用於繁重搜索但很少更新的表
　　冷緩存占用%的總緩存空間用於中等強度更新的表如臨時表
　　冷緩存占用%的總緩存空間作為默認的緩存用於所有其他表
　　使用三個緩存的一個原因是好處在於存取一個緩存結構時不會阻止對其他緩存的訪問訪問一個表索引的查詢不會跟指定到其他緩存的查詢競爭性能提高還表現在以下幾點原因
　　熱緩存只用於檢索記錄因此它的內容總是不需要變化所以無論什麼時候一個索引區塊需要從磁盤中引入被選中要替換的緩存區塊的內容總是要先被刷新
　　索引被指向熱緩存中後如果沒有需要掃描全部索引的查詢那麼對應到B樹中非葉子節點的索引區塊極可能還保留在緩存中
　　在臨時表裡必須頻繁執行一個更新操作是相當快的如果要被更新的節點已經在緩存中了它無需先從磁盤中讀取出來當臨時表的索引大小和冷緩存大小一樣時那麼在需要更新一個節點時它已經在緩存中存在的幾率是相當高的

　　中點插入策略

　　默認地MySQL 的索引緩存管理系統采用LRU策略來選擇要被清除的緩存區塊不過它也支持更完善的方法叫做中點插入策略

　　使用中點插入策略時LRU鏈就被分割成兩半一個熱子鏈一個溫子鏈兩半分割的點不是固定的不過緩存管理系統會注意不讓溫子鏈部分太短總是至少包括全部緩存區塊的 key_cache_division_limit 比率key_cache_division_limit 是緩存結構體變量的組件部分因此它是每個緩存都可以設置這個參數值

　　當一個索引區塊從表中讀入緩存時它首先放在溫子鏈的末尾當達到一定的點擊率(訪問這個區塊)後它就提升到熱子鏈中去目前要提升一個區塊的點擊率()對每個區塊來說都是一樣的將來我們會讓點擊率依靠B樹中對應的索引區塊節點的級別包含非葉子節點的索引區塊所要求的提升點擊率就低一點包含葉子節點的B索引樹的區塊的值就高點

　　提升起來的區塊首先放在熱子鏈的末尾這個區塊在熱子鏈內一直循環如果這個區塊在該子鏈開頭位置停留時間足夠長了它就會被降級回溫子鏈這個時間是由索引緩存結構體變量的組件 key_cache_age_threshold 值來決定的

　　這個閥值是這麼描述的一個索引緩存包含了 N 個區塊熱子鏈開頭的區塊在低於 N*key_cache_age_threshold/ 次訪問後就被移動到溫子鏈的開頭位置它又首先成為被刪除的候選對象因為要被替換的區塊還是從溫子鏈的開頭位置開始的

　　中點插入策略就能在緩存中總能保持更有價值的區塊如果更喜歡采用LRU策略只需讓 key_cache_division_limit 的值低於默認值

　　中點插入策略能幫助改善在執行需要有效掃描索引它會將所有對應到B樹中高級別的有價值的節點推出的查詢時的性能為了避免這樣就必須設定 key_cache_division_limit 遠遠低於以采用中點插入策略則在掃描索引操作時那些有價值的頻繁點擊的節點就會保留在熱子鏈中了

　　索引預載入

　　如果索引緩存中有足夠的區塊用來保存全部索引或者至少足夠保存全部非葉子節點那麼在使用前就載入索引緩存就很有意義了將索引區塊以十分有效的方法預載入索引緩存緩沖從磁盤中順序地讀取索引區塊

　　沒有預載入查詢所需的索引區塊仍然需要被放到緩存中去雖然索引區塊要保留在緩存中因為有足夠的緩沖它們可以從磁盤中隨機讀取到而非順序地

　　想要預載入緩存可以使用 LOAD INDEX INTO CACHE 語句如下語句預載入了表 t 和 t 的索引節點(區塊)

　　增加修飾語 IGNORE LEAVES 就只預載入非葉子節點的索引區塊因此上述語句加載了 t 的全部索引區塊但是只加載 t 的非葉子節點區塊

　　如果使用 CACHE INDEX 語句將索引指向一個索引緩存將索引區塊預先放到那個緩存中去否則索引區塊只會加載到默認的緩存中去

　　索引緩存大小

　　MySQL 引進了對每個索引緩存的新變量 key_cache_block_size這個變量可以指定每個索引緩存的區塊大小用它就可以來調整索引文件I/O操作的性能

　　當讀緩沖的大小和本地操作系統的I/O緩沖大小一樣時就達到了I/O操作的最高性能了但是設置索引節點的大小和I/O緩沖大小一樣未必能達到最好的總體性能讀比較大的葉子節點時服務器會讀進來很多不必要的數據這大大阻礙了讀其他葉子節點

　　目前還不能控制數據表的索引區塊大小這個大小在服務器創建索引文件 `MYI 時已經設定好了它根據數據表的索引大小的定義而定在很多時候它設置成和I/O緩沖大小一樣在將來可以改變它的值並且會全面采用變量 key_cache_block_size

　　重建索引緩存

　　索引緩存可以通過修改其參數值在任何時候重建它例如

　　
　　mysql> SET GLOBAL cold_cachekey_buffer_size=**;

　　如果設定索引緩存的結構體變量組件變量 key_buffer_size 或 key_cache_block_size 任何一個的值和它當前的值不一樣服務器就會清空原來的緩存在新的變量值基礎上重建緩存如果緩存中有任何的髒索引塊服務器會先把它們保存起來然後才重建緩存重新設定其他的索引緩存變量並不會重建緩存

　　重建緩存時服務器會把所有的髒緩沖的內容先刷新到磁盤中去之後緩存的內容就無效了不過重建的時候並不阻止那些需要使用指向到緩存中的索引的查詢相反地服務器使用本地文件系統緩存直接訪問數據表索引文件系統緩存不如索引緩存來的高效因此可以預見這時的查詢會比較慢一旦緩存重建完了指向它的索引又可以使用了同時也就不再使用文件系統緩存來訪問索引了

From:http://tw.wingwit.com/Article/program/Oracle/201311/16615.html