mysql最佳化之索引最佳化

標籤：

 

 

Posted by Money Talks on 2012/02/23 |

第一篇 序章
第二篇 串連最佳化
第三篇 索引最佳化
第四篇 查詢最佳化
第五篇 到實戰中去

索引最佳化

索引最佳化涉及到幾個方面，包括了索引的類型、如何讓查詢使用索引，查詢是索引演算法的選擇等等操作。(原文連結http://ddbiz.com/?p=961)
涉及到資料庫的查詢時，大多數情況都是要建立索引的，MySQL的索引類型以及建立索引方式，可以參考其文檔或者這裡。

1. 伺服器參數設定

   在於索引有關係的資料庫參數中，有一些特別重要，如下：

   1. key_buffer_size

      命令列參數: –key_buffer_size=#
      ini/cnf參數: key_buffer_size
      mysql 變數: key_buffer_size
      全域變數，可動態調整，取值範圍 8到4G(32bitOS), 在64bitOS上可以設定更高，只要你的系統有那麼多實體記憶體。

      key_buffer_size 主要作用於MyISAM表的索引。我們知道，MyISAM的索引控制代碼是對全部串連使用者共用的。狀態參數 key_read_requests(響應使用者請求時從緩衝中擷取的資料)和key_reads(響應使用者請求時從檔案中載入的資料) 可以用來檢測key_buffer_size是否太小，根據文檔, key_reads/key_read_request 應該遠小於0.01(當然我們的測試環境可能完全無法達到此設定，只能盡量接近了). 如果一個MyISAM的資料表的索引檔案過G，除非實體記憶體遠遠高於索引大小，否則讓key_reads/key_read_request接近0.01也是一件不可能的任務。

      MySQL還支援把索引提前載入到記憶體，本節後面將描述此方法及其限制.

      受如下參數影響: 系統記憶體
      將影響如下參數: 無
      調整觸發條件: key_reads/key_read_request >0.01

   2. key_cache_block_size

      與key_buffer_size相關聯的另外一個參數是 key_cache_block_size
      命令列參數: –key_cache_block_size=#
      ini/cnf參數: key_cache_block_size
      mysql 變數: key_cache_block_size
      全域變數，可動態調整，取值範圍 512bytes到16k。

      key_cache_block_size表明key_buffer_size被分割的地區的大小。可以通過 show status 來判斷當前key_buffer_size被分為多少個塊：

      Key_blocks_unused, 目前剩餘可用的索引緩衝空間
      Key_blocks_used(曆史上的最大峰值)

      對於一個key_buffer_size=4G的資料庫來說， key_cache_block_size*Key_blocks_unused 就是剩餘的可用的緩衝空間。只要這個空間允許，我們都應該儘可能的把索引預裝入緩衝中（見LOAD INDEX INTO CACHE

      理論上講，key_cache_block_size與作業系統的I/O buffer相同大小時工作（讀取、寫入）效率最高，比如linux中i/o緩衝一般為1k. 對於mysql來說，可以通過 –myisam-block-size來定義MyISAM索引檔案的塊大小，其最小為1k。

   3. key_cache_division_limit

      命令列參數: –key_cache_division_limit=1~100
      設定檔參數: key_cache_division_limit=#
      MySQL變數: key_cache_division_limit
      全域可調整變數，預設為100(即LRU隊列)。
      當MyISAM的key_buffer_size中設定了key_cache_division_limit(

   4. key_cache_age_threshold

      命令列參數: –key_cache_age_threshold=100~4G(32bitOS)或者100~~(64bitOS)
      設定檔參數: key_cache_age_threshold=#
      MySQL變數: key_cache_age_threshold
      全域可調整變數，預設為300。
      當MyISAM的key_buffer_size被分為熱鏈和溫鏈時，key_cache_age_threshold就被用來控制什麼樣的情況下處於熱鏈的資料會被轉移到溫鏈中。key_cache_age_threshold是指的block個數，如果在最近的 （key_cache_age_threshold * key_cache_division_limit/100）次訪問中，處於熱鏈頂部的block沒有被訪問到，那麼這些塊將被移到溫鏈的頂部（處於溫鏈頂部的block會很快被移除出key cache）。

      說明:
      key_buffer_size, key_cache_block_size, key_cache_division_limit, key_cache_age_threshold 這4個變數是一個結構變數中的一組變數。本文後續有部分詳細介紹.

   5. read_buffer_size

      命令列參數: –read_buffer_size=#
      ini/cnf參數: read_buffer_size
      mysql 變數: read_buffer_size
      全域變數，可動態調整，預設128k，取值範圍8k到2G.

      read_buffer_size主要作用於順序讀取一批資料時，減少資料庫檔案訪問的次數。

      受如下參數影響:
      將影響如下參數: KEY_BLOCK_SIZE（表建立時的參數）
      調整觸發條件:

   6. read_rnd_buffer_size

      命令列參數: –read_rnd_buffer_size=#
      ini/cnf參數: read_rnd_buffer_size
      mysql 變數: read_rnd_buffer_size
      全域變數，可動態調整，預設256k，取值範圍8k到4G.

      read_rnd_buffer_size用於使用鍵/索引進行排序時的磁碟檔案預讀，一個足夠大的read_rnd_buffer_size對ORDER BY語句會有很大影響。
      注意:read_rnd_buffer_size是和每一個session相關的，因此其大小需要謹慎處理，當並發串連很大時，尤其要小心。

2. LOAD CACHE INTO CACHE

   MySQL可以把索引檔案積極式載入到指定的緩衝中:

   LOAD INDEX INTO CACHE table_name;
   LOAD INDEX INTO CACHE table_name INDEX (index_name_1, index_name_2);
   LOAD INDEX INTO CACHE table_name INDEX (index_name_1, index_name_2) IGNORE LEAVES;

   對於查詢型的超大型的表，當沒有足夠的記憶體時，可以使用 IGNORE LEAVES 的方式載入索引到緩衝。比如一個接近1Billion的資料表，其索引可能也是幾G大小的。下面這個例子就顯示了當積極式載入資料索引時，查詢的速度變化，這個資料表有接近1億條記錄，3個索引，有超過3G的大小，對於同一個查詢：

   #mysql> select * from tbigdatatable where username in (‘ABCDEF’, ‘19740821’)

   在非積極式載入索引以及預載索引的情況下，查詢的響應區別:

   #mysqld_safe –key-buffer-cache=1073741824
   ####不預載索引#### 5 rows in set (0.65 sec)
   ######預載索引#### 5 rows in set (1.94 sec)

   當然一個查詢有很大的偶然性，不過從一個大量的查詢平均來看，這個效果還是有很大的差別的。

   如果一個表的索引包含不同的key_block_size,那麼這個預裝載將會遇到困難, 如：

   alter table tbigdatatable add index idx_bigdatatable_username(username) key_block_size=8192, add index idx_bigdatatable_email(email) key_block_size=8192, add index idx_bigdatatable_id(id) key_block_size=1024;
   load index into cache tbigdatatable index (idx_bigdatatable_username, idx_bigdatatable_email) ignore leaves;
   +————————+————–+———-+————————————-+
   | Table | Op | Msg_type | Msg_text |
   +————————+————–+———-+————————————-+
   | coredata.tbigdatatable | preload_keys | error | Indexes use different block sizes |
   | coredata.tbigdatatable | preload_keys | error | Subpartition p178sp0 returned error |
   | coredata.tbigdatatable | preload_keys | status | Operation failed |
   +————————+————–+———-+————————————-+

   因為.MYI中包含不同block_size的索引，所以無法預先載入！

   MySQL 5.1 對分區表不能預裝載索引：
   preload_keys | note | The storage engine for the table doesn’t support preload_keys
   真是不幸, 這些版本包括: 5.1.47, 5.1.61-community-log

   因為手頭上沒有MySQL5.5的資料庫可供使用，所以關於預裝載索引到記憶體的方法只能暫時放一放，這也導致了我的另外一個測試專案差點夭折：9千萬資料的分區表查詢，每個查詢要耗時1m左右，傷不起啊。

3. CACHE INDEX

   LOAD INDEX INTO CACHE可以把索引裝入緩衝中，除此外，MySQL還可以更有針對性的把索引裝入制定的緩衝中。key_buffer_size是一個結構中一個變數，這個結構是系統的預設緩衝空間，我們還可以設定其他的緩衝空間，比如：

   set global key1.key_buffer_size=128*1024*1024;
   set global key2.key_buffer_size=512*1024*1024;

   這樣我們就能定義不同的緩衝空間，配合 CACHE INDEX IN cache_region語句，就可以把不同的索引放入不同的緩衝中。

   (接上)
   cache index table_name index (index_name_1) in key1;
   cache index table_name index (index_name_2) in key2;
   load cache into cache table_name index (index_name_1);
   load cache into cache table_name index (index_name_2) ignore leaves;

   上面的語句就把 index_name_1放入了 key1, 把 index_name_2放入了 key2;至於何時使用不同的緩衝，我們在後面的實戰中再做討論。

   結構化的變數，在mysql中沒辦法用 show variables like ‘structure_name_1.key_buffer%’ ; 的方式來查詢。假如我們定義了不同的結構變數（建立不同的緩衝空間的）: staticdb.*，則可以用下面的方式來查詢該空間的設定情況：

   set global staticdb.key_buffer_size=1000*1024*1024;
   —————————————————
   mysql> select @@global.staticdb.key_buffer_size KBS
   , @@global.staticdb.key_cache_division_limit KCDL
   , @@global.staticdb.key_cache_age_threshold KCAT
   , @@global.staticdb.key_cache_block_size KCBS;
   +————+——+——+——+
   | KBS | KCDL | KCAT | KCBS |
   +————+——+——+——+
   | 1048576000 | 100 | 300 | 1024 |
   +————+——+——+——+
   1 row in set (0.00 sec)

   目前我還沒有辦法查看指定一個結構空間的使用方式，如果你知道如何查看的話，請留言給我。

4. 要點總結

   本節的要點是：

   對於MyISAM來說：設定儘可能大的緩衝，最好把MyISAM表的索引能夠全部裝入緩衝中

mysql最佳化之索引最佳化