程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 影響Mysql性能的重要參數詳解

影響Mysql性能的重要參數詳解

編輯：MySQL綜合教程

影響Mysql性能的重要參數詳解

查看MySQL中參數

Mysql服務啟動之後，我們可以使用show variables和show status 命令可以查看mysql服務的靜態參數值和動態運行狀態信息。其中show variables是查看數據庫啟動後不會動彈更改的值，比如緩沖區大小、字符集、數據文件名等。show status是查看數據庫運行期間的動態變化信息，比如鎖等待、當前連接數等。查看服務器含有buffer的參數值

查看服務器含有buffer的運行狀態值

影響MySQL性能的重要參數

主要介紹的是使用MyISAM存儲引擎的key_buffer_size和table_cache，以及使用使用InnoDB存儲引擎的一些以innodb_開頭的參數。

1.key_buffer_size

該參數是用來設置索引塊（Index Blocks）緩存的大小，它被索引線程共享，此參數只使用MyISAM存儲引擎。MySQL5.1之後的版本，可以將指定的表索引緩存入指定的key_buffer,這樣可以降低線程之間的競爭。

索引緩存概述

MyISAM存儲引擎和其他很多數據庫系統一樣，采用了一種將最經常訪問的表保存在內存中的策略。對應索引區塊來說，它維護者一個叫做索引緩存（索引緩沖）的結構體，這個結構體中存放著許多哪些經常使用的索引區塊的緩沖區塊。對應數據區塊來說，Mysql主要依靠系統的本地文件系統緩存。有了索引緩沖後，線程之間不再是串行地訪問索引緩存。多個線程可以並行地訪問索引緩存。可以設置多個索引緩存，同時也能指定數據表索引到特定的緩存中。

創建一個索引緩存

set global 緩存索引名.key_buffer_size=100*1024;

global是全局限制，表示對每一個新的會話（連接）都有效。

修改一個索引緩存

和創建一個索引緩存一樣一樣的，都是set global 緩存索引名.key_buffer_size=100*1024;

將相關表的索引放到自己創建的索引緩存中

格式：cache index 表名1,表名2 in 索引緩存將t1、t2、t3表中的索引放到my_cache索引緩存中

因為t1表式InnoDB表，t2，t3表為MyISAM表，故只有t2、t3表中的索引可以放到my_cache緩存中。

將索引放到默認的kef_buffer中

可以使用load index into cache +表名

刪除索引緩存

將其索引緩沖大小設置為了0，就可以刪除了，注意不能刪除默認的key_buffer。

配置mysql服務器啟動時自動加載索引緩存

在MySQL配置文件中添加如下內容（在Windows下叫my.ini，在Linux下叫my.cnf）
my_cache.key_buffer_size=1G #指定索引緩存區大小 init_file=/usr/local/mysql/init_index.sql#在該文件中指定要加載到緩存區德索引
init_index.sql內容如下：
cache index t2 into my_cache;
cache index t3 into my_cache;

2.table_cache

概述

這個參數表示數據庫用戶打開表的緩沖數量，table_cache與max_connections有關。當某一連接訪問一個表時，MySQL會檢查當前已緩存表的數量，如果該表已經在緩沖中打開，則直接訪問緩存中的表，如果為被緩存，則會將當前表添加進緩存並進行查詢。在執行緩存操作之前，table_cache用於限制緩存表的最大數目，如果當前已經緩存的表未達到table_cache，則會將新表添加進來；若已經達到此值，MySQL將根據緩存表的最後查詢時間、查詢率等規則釋放之前緩存的表，添加新表。

參數調優

通過檢查mysqld的狀態變量open_tables和opend_tables確定table_cache這個參數的大小。open_tables代表當前打開的表緩沖數量，如果執行flush tables，則系統會關閉一些當前沒有使用的表緩存，使得open_tables值減少。opend_tables表示曾經打開的表緩存數，會一直進行累加，不會因為執行flush tables操作，有所減少。如下圖，變化還是很明顯的。

當執行一個查詢之後，發現opend_table和open_table都增加了1

當再次執行同一個查詢之後，發現opend_table和open_table都不再變化

由此可見open_table對於設置table_cache更有價值

3.Innodb_buffer_pool_size

這個參數定義了InnoDB存儲引擎的表數據和索引數據的最大內存緩存區大小。和MyISAM存儲引擎不同，MyISAM的key_buffer_size只緩存索引鍵，而Innodb_buffer_pool_size同時為數據塊和索引塊做了緩存，這個只設的越高，訪問表中的數據需要的磁盤I/O就越少。但是設置的過大，會導致物理內存競爭過大。

4.Innodb_flush_log_at_trx_commit

這個參數是控制緩存區中的數據寫入到日志文件以及日志文件數據刷新到磁盤的操作時機。默認值為1。可以有以下值： 0：日志緩沖每秒一次地寫到日志文件，並對日志文件作向磁盤刷新操作，但事務提交不做任何操作。 1：每個事務提交時，日志緩沖被寫到日志文件，並且對日志文件做向磁盤刷新操作。 2：每個事務提交時候，日志緩沖被寫到日志文件，但是不對日志文件作向磁盤刷新操作，對日志文件每秒向磁盤做一次刷新操作。