本文實(shí)例講述了MySQL數(shù)據(jù)庫(kù)優(yōu)化技術(shù)的配置方法。分享給大家供大家參考，具體如下：

(一)減少數(shù)據(jù)庫(kù)訪問(wèn)

對(duì)于可以靜態(tài)化的頁(yè)面，盡可能靜態(tài)化

對(duì)一個(gè)動(dòng)態(tài)頁(yè)面中可以靜態(tài)的局部，采用靜態(tài)化

部分?jǐn)?shù)據(jù)可以生成XML，或者文本文件形式保存

使用數(shù)據(jù)緩存技術(shù)，例如： MemCached

(二)優(yōu)化的檢測(cè)方法

1.用戶體驗(yàn)檢測(cè)

2.Mysql狀態(tài)檢測(cè)

在Mysql命令行里面使用show status命令，得到當(dāng)前mysql狀態(tài)。

主要關(guān)注下列屬性：

key_read_requests (索引讀的請(qǐng)求數(shù)）(key_buffer_size設(shè)置影響)

key_reads（索引讀響應(yīng)數(shù))

Key_blocks_used

Qcache_*

Open_tables(通過(guò)table_cache的設(shè)置影響)

Opened_tables

table_locks

3. 第三方工具檢測(cè)

mysqlreport http://hackmysql.com/mysqlreport

mytop http://jeremy.zawodny.com/mysql/mytop/

系統(tǒng)及Mysql的Log

系統(tǒng)命令： top, sar

Mysql的Log: slow_query.log

(三)硬件方面的優(yōu)化

硬件方面，最容易成為Mysql瓶頸的部分是磁盤(pán)，其次是CPU和內(nèi)存

磁盤(pán)方面

使用更快的磁盤(pán)，會(huì)對(duì)Mysql有很好的幫助

使用更多的硬盤(pán)，通過(guò)Raid，可以提高單塊磁盤(pán)速度的問(wèn)題

對(duì)于Raid方式，建議采用Raid 0+1 或者 Raid 1+0

CPU

毫無(wú)疑問(wèn)，更高主頻的CPU和更多的CPU數(shù)量可以給Mysql更

高的性能

內(nèi)存

更高的內(nèi)存，往往可以讓Mysql中的更多的數(shù)據(jù)緩存在內(nèi)存中，

但是，一個(gè)重要的因素是，需要有正確的Mysql的配置

網(wǎng)卡

使用千兆網(wǎng)卡及千兆網(wǎng)絡(luò)

(四)操作系統(tǒng)方面的優(yōu)化

1.不使用交換區(qū)。如果內(nèi)存不足，增加更多的內(nèi)存或配置你的系統(tǒng)使用較少內(nèi)存

2. 不要使用NFS磁盤(pán)

3.增加系統(tǒng)和MySQL服務(wù)器的打開(kāi)文件數(shù)量

使用ulimit –n 65535

4.增加系統(tǒng)的進(jìn)程和線程數(shù)量。

5.關(guān)閉不必要的應(yīng)用，優(yōu)化硬盤(pán)參數(shù)，使用hdparm測(cè)試

(五)應(yīng)用級(jí)的優(yōu)化

1.使用多服務(wù)器負(fù)載均衡(多臺(tái)讀和寫(xiě),用復(fù)制技術(shù)進(jìn)行數(shù)據(jù)同步)

2.表的分區(qū) (自定義分區(qū),mysql5.1開(kāi)始支持自帶分區(qū)功能)

3.使用數(shù)據(jù)緩存技術(shù)memcached

(六)Mysql配置的優(yōu)化

1.key_buffer(=512):索引緩沖使用的內(nèi)存數(shù)量

這對(duì)MyISAM表來(lái)說(shuō)非常重要,設(shè)定在可用內(nèi)存的25%-30%較好,通過(guò)檢查狀態(tài)值 Key_read_requests和 Key_reads,

可以知道key_buffer設(shè)置是否合理。比例key_reads / key_read_requests應(yīng)該盡可能的低，至少是1:100，1:1000更好 ,否則說(shuō)明 key_buffer 設(shè)置有點(diǎn)偏小

2.innodb_buffer_pool_size(= 512):索引緩沖使用的內(nèi)存數(shù)量

3.table_cache (=1024):數(shù)據(jù)表緩存區(qū)的尺寸

每當(dāng) MySQL 訪問(wèn)一個(gè)表時(shí)，如果在表緩沖區(qū)中還有空間，該表就被打開(kāi)并放入其中，這樣可以更快地訪問(wèn)表內(nèi)容。

通過(guò)檢查運(yùn)行峰值時(shí)間的 Open_tables 和 Opened_tables 狀態(tài)值，可以決定是否需要調(diào)整 table_cache 的值。

如果你發(fā)現(xiàn) open_tables 的值等于 table_cache，并且發(fā)現(xiàn) opened_tables 狀態(tài)值在不斷增長(zhǎng)，那么你就需要增加 table_cache 參數(shù)值了,

也不能盲目地把 table_cache 參數(shù)設(shè)置成很大的值，如果設(shè)置得太高，可能會(huì)造成文件描述符不足，從而造成性能不穩(wěn)定或者連接失敗。

4.sort_buffer_size (=256):指定排序用緩沖區(qū)的長(zhǎng)度

該參數(shù)對(duì)應(yīng)的分配內(nèi)存是每連接獨(dú)占！如果有100個(gè)連接，那么實(shí)際分配的總共排序緩沖區(qū)大小為100 × 6 ＝ 600MB。

所以，對(duì)于內(nèi)存在4GB左右的服務(wù)器推薦設(shè)置為6-8M

5.join_buffer_size :關(guān)聯(lián)查詢用緩沖區(qū)的長(zhǎng)度

4G內(nèi)存以上，建議大于32M,該參數(shù)對(duì)應(yīng)的分配內(nèi)存也是每連接獨(dú)享！

6.max_connections (=1024):可以復(fù)用的線程數(shù)量

允許同時(shí)連接MySQL服務(wù)器的客戶數(shù)量 ,可以觀察和估計(jì)系統(tǒng)在峰值最大的并發(fā)連接數(shù)來(lái)設(shè)置

7.thread_cache(=*):可以復(fù)用的線程數(shù)量

一般設(shè)置為CPU數(shù)×2

8.innodb_buffer_pool_size(= 512):innodb表緩存池大小

這對(duì)Innodb表來(lái)說(shuō)非常重要。Innodb相比MyISAM表對(duì)緩沖更為敏感。MyISAM可以在默認(rèn)的 key_buffer_size 設(shè)置下運(yùn)行的可以，

然而Innodb在默認(rèn)的innodb_buffer_pool_size 設(shè)置下卻跟蝸牛似的。

由于Innodb把數(shù)據(jù)和索引都緩存起來(lái)，無(wú)需留給操作系統(tǒng)太多的內(nèi)存，因此如果只需要用Innodb的話則可以設(shè)置它高達(dá) 70-80% 的可用內(nèi)存。

一些應(yīng)用于 key_buffer 的規(guī)則有 -- 如果你的數(shù)據(jù)量不大，并且不會(huì)暴增，那么無(wú)需把innodb_buffer_pool_size 設(shè)置的太大了.

9.innodb_flush_logs_at_trx_commit(=1) :事務(wù)提交后的日志刷新模式

是否為Innodb比MyISAM慢1000倍而頭大?看來(lái)也許你忘了修改這個(gè)參數(shù)了。默認(rèn)值是 1，這意味著每次提交的更新事務(wù)(或者每個(gè)事務(wù)之外的語(yǔ)句)都會(huì)刷新到磁盤(pán)中，

而這相當(dāng)耗費(fèi)資源，尤其是沒(méi)有電池備用緩存時(shí)。很多應(yīng)用程序，尤其是從 MyISAM轉(zhuǎn)變過(guò)來(lái)的那些，把它的值設(shè)置為 2 就可以了，也就是不把日志刷新到磁盤(pán)上，

而只刷新到操作系統(tǒng)的緩存上。日志仍然會(huì)每秒刷新到磁盤(pán)中去，因此通常不會(huì)丟失每秒1-2次更新的消耗。如果設(shè)置為0就快很多了，不過(guò)也相對(duì)不安全了,

MySQL服務(wù)器崩潰時(shí)就會(huì)丟失一些事務(wù)。設(shè)置為2指揮丟失刷新到操作系統(tǒng)緩存的那部分事務(wù).

更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《MySQL索引操作技巧匯總》、《MySQL日志操作技巧大全》、《MySQL事務(wù)操作技巧匯總》、《MySQL存儲(chǔ)過(guò)程技巧大全》、《MySQL數(shù)據(jù)庫(kù)鎖相關(guān)技巧匯總》及《MySQL常用函數(shù)大匯總》

希望本文所述對(duì)大家MySQL數(shù)據(jù)庫(kù)計(jì)有所幫助。

最新評(píng)論