mysql配置文件如何優化

⑴ 資料庫如何優化

body{

line-height:200%;

}

如何優化Mysql資料庫

當MySQL資料庫邂逅優化，它有好幾個意思，今天我們所指的是性能優化。櫻明棗

我們究竟該如何對MySQL資料庫進行優化呢？下面我就從MySQL對硬體的選擇、Mysql的安裝、myf的優化、MySQL如何進行架構設計及數據切分等方面來說明這個問題。

1.伺服器物理硬體的優化

1)磁碟(I/O)，MySQL每一秒鍾都在進行大量、復雜的查詢操作，對磁碟的讀寫量可想而知，所以推薦使用RAID10磁碟陣列，如果資金允許，可以選擇固態硬碟做RAID10；

2)cpu對Mysql的影響也是不容忽視的，建槐睜議選擇運算能力強悍的CPU。

2.MySQL應該採用編譯安裝的方式

MySQL資料庫的線上環境安裝，我建議採取編譯安裝，這樣性能會較大的提升。

3.MySQL配置文件的優化

1)skip

-name

-resolve，禁止MySQL對外部連接進行DNS解析，使用這一選項可以消除MySQL進行DNS解析的時間；

2)back_log

=

384，back_log指出在MySQL暫時停止響應新請求之前，短時間內的多少個請求可以被存在堆棧中，對於Linux系統而言，推薦設置小於512的整數。

3)如果key_reads太大，則應該把myf中key_buffer_size變大，保持key_reads/key_read_requests至少在1/100以上，越小越好。

4.MySQL上線後根據status狀態進行適當優化

1)打開慢查詢日誌可能會對系統性能有一點點影響，如果你的MySQL是主-從結構，可以考慮打開其中一台從伺服器的慢查詢日誌，這樣既可以監控慢查詢，對系統性能影響也會很小。

2)MySQL伺服器過去的最大連接數是245，沒有達到伺服器連接數的上限256，應該不會出現1040錯誤。比較理想的設置是：Max_used_connections/max_connections

*

100%

=85%

5.MySQL資料庫的可擴展架構方案

1)MySQL

cluster，其特點為可用性非常高，性能非常好，但脊拆它的維護非常復雜，存在部分Bug;

2)DRBD磁碟網路鏡像方案，其特點為軟體功能強大，數據可在底層塊設備級別跨物理主機鏡像，且可根據性能和可靠性要求配置不同級別的同步。

⑵ 怎麼進行mysql資料庫優化(mysql資料庫優化的幾種方法)

主要從以下角度思考優化方向：1，Mysql配置優化主要對查詢緩存，mysql資料庫連接時緩賣長，開啟慢查詢日誌(開啟後還要分析sql)等方面進行優化2

Myslq語句優化3

Mysql索引優化主要是需要注意索引數量和索引失效情況，重復索引橡哪桐4

Mysql引擎優化innodb引擎注重於事務，能保證數據一致性myisam引擎梁坦可以進行全文檢索，但不是事務安全當初在黑馬程序員學過，還用實例進行優化學習

⑶ MySQL資料庫優化（七）

為了能最小化磁碟I/O MyISAM 存儲引擎採用了很多資料庫系統使用的一種策略 它採用一種機制將最經常訪問的表保存在內存區塊中

對索引區塊來說 它維護著一個叫索引緩存(索引緩沖)的結構體 這個結構體中放著許多那些最常使用的索引區塊的緩沖區塊 對數據區塊來說 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 的緩存中

mysql>CACHEINDEXt t t INhot_cache; + + + + + |Table|Op|Msg_type|Msg_text| + + + + + |test t |assign_to_keycache|status|OK| |test t |assign_to_keycache|status|OK| |test t |assign_to_keycache|status|OK| + + + + +

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

CACHE INDEX 語句中用到的索引緩存是根據用 SET GLOBAL 語句的參數設定的值或者伺服器啟動參數指定的值創建的 如下 mysql> SET GLOBAL keycache key_buffer_size= * ;想要刪除索引緩存 只需設置它的大小為 mysql> SET GLOBAL keycache key_buffer_size= ;索引緩存變數是一個結構體變數 由名字和組件構成 例如 keycache key_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 的索引節點(區塊)

mysql>LOADINDEXINTOCACHEt t IGNORELEAVES; + + + + + |Table|Op|Msg_type|Msg_text| + + + + + |test t |preload_keys|status|OK| |test t |preload_keys|status|OK| + + + + +

增加修飾語 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>SETGLOBALcold_cache key_buffer_size= * * ;

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

lishixin/Article/program/Oracle/201311/16615