Mysql性能有哪些調(diào)優(yōu)與測(cè)試的方法

本文主要給大家介紹MySQL性能有哪些調(diào)優(yōu)與測(cè)試的方法，文章內(nèi)容都是筆者用心摘選和編輯的，具有一定的針對(duì)性，對(duì)大家的參考意義還是比較大的，下面跟筆者一起了解下Mysql性能有哪些調(diào)優(yōu)與測(cè)試的方法吧。

我們提供的服務(wù)有：成都網(wǎng)站設(shè)計(jì)、成都網(wǎng)站制作、微信公眾號(hào)開(kāi)發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認(rèn)證、烏蘇ssl等。為上千企事業(yè)單位解決了網(wǎng)站和推廣的問(wèn)題。提供周到的售前咨詢和貼心的售后服務(wù)，是有科學(xué)管理、有技術(shù)的烏蘇網(wǎng)站制作公司

一、關(guān)鍵性指標(biāo)

在數(shù)據(jù)庫(kù)性能評(píng)測(cè)中，有幾項(xiàng)指標(biāo)很重要，用它來(lái)評(píng)估數(shù)據(jù)庫(kù)的能力，不是他們能起著多么關(guān)鍵的作用，而是他們能夠較為明確的代表數(shù)據(jù)庫(kù)在某些方面的能力。

1.IOPS

IOPS：Input/Output operation Per Second, 每秒處理的IO請(qǐng)求次數(shù)。
我們知道I/O就是磁盤的讀寫能力，比如每秒讀 300M，寫 200M，這個(gè)即數(shù)據(jù)的吞吐量（I/O能力的另一個(gè)關(guān)鍵指標(biāo)），但是 IOPS 指的可不是讀寫的數(shù)據(jù)吞吐量，IOPS 指的是每秒能夠處理的 I/O 請(qǐng)求次數(shù)。

如果想I/O 系統(tǒng)響應(yīng)夠快，那么 IOPS 越高越好，因?yàn)镮OPS 和硬件有關(guān)，所以，要提高IOPS，就目前來(lái)看基本只能拼硬件，傳統(tǒng)方案是使用多塊磁盤通過(guò) RAID 條帶后，使 I/O 讀寫能力獲得提升，我們也可以使用固態(tài)硬盤SSD來(lái)提升IOPS，不過(guò)固態(tài)硬盤成本可能比較大。

2.QPS

QPS：Query Per Second，每秒請(qǐng)求（查詢）次數(shù)。
這個(gè)參數(shù)非常重要，可以直觀的反映系統(tǒng)的性能，這就像IOPS衡量磁盤每秒鐘能接收多少次請(qǐng)求。

我們可以在MySQL命令行模式下執(zhí)行 status 命令，返回的最后一行輸出信息中就包含 QPS 指標(biāo)。

3.TPS

TPS：Transaction Per Second，每秒事務(wù)數(shù)。
TPS參數(shù)MySQL原生沒(méi)有提供，如果需要我們自己算，可以利用計(jì)算的公式：

TPS = (Com_commit + Com_rollback) / Seconds

這個(gè)公式有兩個(gè)狀態(tài)變量，分別代表提交次數(shù)和回滾次數(shù)，Seconds 就是我們定義的時(shí)間間隔。

二、TPCC測(cè)試關(guān)鍵性指標(biāo)

TPCC-MySQL 由Percona基于TPCC規(guī)范開(kāi)發(fā)的一套MySQL基準(zhǔn)測(cè)試程序，我們使用這套工具來(lái)測(cè)試前面的三個(gè)重要指標(biāo)。

1.TPCC工具安裝及使用

具體的安裝，可以看這這兩篇博文 mysql壓力測(cè)試工具tpcc-mysql安裝測(cè)試使用，mysql性能測(cè)試-tpcc，TPCC更能模擬線上業(yè)務(wù)。

三、數(shù)據(jù)庫(kù)參數(shù)配置優(yōu)化

如果數(shù)據(jù)庫(kù)參數(shù)配置合理，則可以大大的提高運(yùn)行效率，即最大化利用系統(tǒng)資源。

1.連接相關(guān)參數(shù)

1.1 max_connections

max_connections：指定 MySQL 服務(wù)端最大并發(fā)連接數(shù)，值得范圍從 1~10 萬(wàn)，默認(rèn)值為151.
這個(gè)參數(shù)非常重要，因?yàn)樗鼪Q定了同時(shí)最多能有多少個(gè)會(huì)話連接到 MySQL 服務(wù)。設(shè)定該參數(shù)時(shí)，根據(jù)數(shù)據(jù)庫(kù)云服務(wù)器的配置和性能，一般將參數(shù)值設(shè)置在 500~2000 都沒(méi)太大的問(wèn)題。

1.2 max_connect_errors

max_connect_errors：指定允許連接不成功的最大嘗試次數(shù)，值得范圍從 1~2^64 之間，在 5.6.6 版本默認(rèn)值是 100。

一定不要忽視這個(gè)參數(shù)，如果嘗試連接的錯(cuò)誤數(shù)量超過(guò)該參數(shù)指定值，則云服務(wù)器就不再允許新的連接，沒(méi)錯(cuò)，就是拒絕連接，盡管 MySQL 仍在提供服務(wù)，但無(wú)法創(chuàng)建新的連接了?？梢允褂?FLUSH HOSTS，使?fàn)顟B(tài)清零或重新啟動(dòng)數(shù)據(jù)庫(kù)服務(wù)，不過(guò)這個(gè)代價(jià)太高了，一般不會(huì)這么干，所以，這個(gè)參數(shù)的默認(rèn)值太小，這里建議將之設(shè)置為 10 萬(wàn)以上的量級(jí)。

1.3 interactive_timeout 和 wait_timeout

這兩個(gè)參數(shù)都與連接會(huì)話的自動(dòng)超時(shí)斷開(kāi)有關(guān)，前者用于指定關(guān)閉交互連接前等待的時(shí)間，后者用于指定關(guān)閉非交互連接前的等待時(shí)間，單位均是秒，默認(rèn)值均為 28800，即 8 個(gè)小時(shí)。

1.4 skip-name-resolve

skip-name-resolve：可以將其簡(jiǎn)單的理解為禁用 DNS 解析，注意啊，這個(gè)是服務(wù)端的行為，連接時(shí)不檢查客戶端主機(jī)名，而只使用IP。如果制定了該參數(shù)，那么在創(chuàng)建用戶及授予權(quán)限時(shí)，HOST 列必須是IP而不能是主機(jī)名。建議啟用該參數(shù)，對(duì)于加快網(wǎng)絡(luò)連接有一定的幫助，等于是跳過(guò)了主機(jī)名的解析。

1.5 back_log

back_log：指定 MySQL 連接請(qǐng)求隊(duì)列中存放的最大連接請(qǐng)求數(shù)量，在 5.6.6 版本之前，默認(rèn)是 50 個(gè)，最大值不超過(guò) 65535。在 5.6.6 版本之后，默認(rèn)值為 -1，表示由MySQL自動(dòng)調(diào)節(jié)，所謂自行調(diào)節(jié)其實(shí)也有規(guī)則，即 50+（max_connections/5）。

該參數(shù)主要應(yīng)對(duì)短時(shí)間內(nèi)有大量的連接請(qǐng)求，MySQL 主線程無(wú)法及時(shí)為每一個(gè)連接請(qǐng)求分配（或創(chuàng)建）連接的線程，怎么辦呢，它也不能直接拒絕，于是就將一部分請(qǐng)求放到等待隊(duì)列中待處理，這個(gè)等待隊(duì)列的長(zhǎng)度就是 back_log 的參數(shù)值，若等待隊(duì)列也被放滿了，那么后續(xù)的連接請(qǐng)求才會(huì)被拒絕。

2.文件相關(guān)參數(shù)

2.1 sync_binlog

sync_binlog：指定同步二進(jìn)制日志文件的平率，默認(rèn)為0.
如果要性能，則指定該參數(shù)為0，為了安全起見(jiàn)則指定該參數(shù)值為 1.

2.2 expire_logs_day

expire_logs_day：指定設(shè)置二進(jìn)制日志文件的生命周期，超出則將自動(dòng)被刪除，參數(shù)值以天為單位，值得范圍從0~99，默認(rèn)值是0，建議將該參數(shù)設(shè)置為 7~14 之間，保存一到兩周就足夠了。

2.2 max_binlog_size

max_binlog_size： 指定二進(jìn)制日志的大小，值得范圍從 4KB~1GB，默認(rèn)為 1GB。

3.緩存控制參數(shù)

3.1 thread_cache_size

thread_cache_size:指定MySQL為快速重用而緩存的線程數(shù)量。值得范圍從 0~16384，默認(rèn)值為0.
一般當(dāng)客戶端中斷連接后，為了后續(xù)再有連接創(chuàng)建時(shí)，能夠快速創(chuàng)建成功，MySQL 會(huì)將客戶端中斷的連接放入緩存區(qū)，而不是馬上中斷釋放資源。這樣當(dāng)有新的客戶端請(qǐng)求連接時(shí)，就可以快速創(chuàng)建成功。因此，本參數(shù)最好保持一定的數(shù)量，建議設(shè)置在 300~500 之間均可.另外，線程緩存的命中率也是一項(xiàng)比較重要的監(jiān)控指標(biāo)，計(jì)算規(guī)則為（1-Threads_created/Connections）* 100%,我們可以通過(guò)該指標(biāo)來(lái)優(yōu)化和調(diào)整thread_cache_size參數(shù)。

3.2 query_cache_type

sql_cache意思是說(shuō)，將查詢結(jié)果放入查詢緩存中。
sql_no_cache意思是查詢的時(shí)候不緩存查詢結(jié)果。
sql_buffer_result意思是說(shuō)，在查詢語(yǔ)句中，將查詢結(jié)果緩存到臨時(shí)表中。

這三者正好配套使用。sql_buffer_result將盡快釋放表鎖，這樣其他sql就能夠盡快執(zhí)行。

使用 FLUSH QUERY CACHE 命令，你可以整理查詢緩存，以更好的利用它的內(nèi)存。這個(gè)命令不會(huì)從緩存中移除任何查詢。FLUSH TABLES 會(huì)轉(zhuǎn)儲(chǔ)清除查詢緩存。
RESET QUERY CACHE 使命從查詢緩存中移除所有的查詢結(jié)果。

那么mysql到底是怎么決定到底要不要把查詢結(jié)果放到查詢緩存中呢？

是根據(jù)query_cache_type這個(gè)變量來(lái)決定的。

這個(gè)變量有三個(gè)取值：0,1,2，分別代表了off、on、demand。
mysql默認(rèn)為開(kāi)啟 on

意思是說(shuō)，如果是0，那么query cache 是關(guān)閉的。
如果是1，那么查詢總是先到查詢緩存中查找，即使使用了sql_no_cache仍然查詢緩存，因?yàn)閟ql_no_cache只是不緩存查詢結(jié)果，而不是不使用查詢結(jié)果。

select count(*) from innodb;
1 row in set (1.91 sec)

select sql_no_cache count(*) from innodb;
1 row in set (0.25 sec)

如果是2，DEMAND。
在my.ini中增加一行
query_cache_type=2
重啟mysql服務(wù)

select count(*) from innodb;
1 row in set (1.56 sec)

select count(*) from innodb;
1 row in set (0.28 sec)

沒(méi)有使用sql_cache,好像仍然使用了查詢緩存

select sql_cache count(*) from innodb;
1 row in set (0.28 sec)

使用sql_cache查詢時(shí)間也一樣，因?yàn)閟ql_cache只是將查詢結(jié)果放入緩存，沒(méi)有使用sql_cache查詢也會(huì)先到查詢緩存中查找數(shù)據(jù)

結(jié)論：只要query_cache_type沒(méi)有關(guān)閉，sql查詢總是會(huì)使用查詢緩存，如果緩存沒(méi)有命中則開(kāi)始查詢的執(zhí)行計(jì)劃到表中查詢數(shù)據(jù)。

query cache優(yōu)缺點(diǎn)
優(yōu)點(diǎn)很明顯，對(duì)于一些頻繁select query，mysql直接從cache中返回相應(yīng)的結(jié)果集，而不用再?gòu)谋韙able中取出，減少了IO開(kāi)銷。
即使query cache的收益很明顯，但是也不能忽略它所帶來(lái)的一些缺點(diǎn)：

1. query語(yǔ)句的hash計(jì)算和hash查找?guī)?lái)的資源消耗。mysql會(huì)對(duì)每條接收到的select類型的query進(jìn)行hash計(jì)算然后查找該query的cache是否存在，雖然hash計(jì)算和查找的效率已經(jīng)足夠高了，一條query所帶來(lái)的消耗可以忽略，但一旦涉及到高并發(fā)，有成千上萬(wàn)條query時(shí)，hash計(jì)算和查找所帶來(lái)的開(kāi)銷就的重視了；

2. query cache的失效問(wèn)題。如果表變更比較頻繁，則會(huì)造成query cache的失效率非常高。表變更不僅僅指表中的數(shù)據(jù)發(fā)生變化，還包括結(jié)構(gòu)或者索引的任何變化；

3. 對(duì)于不同sql但同一結(jié)果集的query都會(huì)被緩存，這樣便會(huì)造成內(nèi)存資源的過(guò)渡消耗。sql的字符大小寫、空格或者注釋的不同，緩存都是認(rèn)為是不同的sql（因?yàn)樗麄兊膆ash值會(huì)不同）；

4. 相關(guān)參數(shù)設(shè)置不合理會(huì)造成大量?jī)?nèi)存碎片，相關(guān)的參數(shù)設(shè)置會(huì)稍后介紹。

合理利用query cache
query cache有利有弊，合理的使用query cache可以使其發(fā)揮優(yōu)勢(shì)，并且有效的避開(kāi)其劣勢(shì)。

1. 并不是所有表都適合使用query cache。造成query cache失效的原因主要是相應(yīng)的table發(fā)生了變更，那么就應(yīng)該避免在變化頻繁的table上使用query cache。mysql中針對(duì)query cache有兩個(gè)專用的sql hint：SQL_NO_CACHE和SQL_CACHE，分別表示強(qiáng)制不使用和強(qiáng)制使用query cache，通過(guò)強(qiáng)制不使用query cache，可以讓mysql在頻繁變化的表上不使用query cache，這樣減少了內(nèi)存開(kāi)銷，也減少了hash計(jì)算和查找的開(kāi)銷；

更多有關(guān)query cache詳情文章，請(qǐng)看這里的原文：mysql query cache優(yōu)化

3.3 query_cache_size

query_cache_size：指定用于緩存查詢結(jié)果集的內(nèi)存區(qū)大小，該參數(shù)值應(yīng)為 1024 的整數(shù)倍。

這個(gè)參數(shù)不能太大，也不能太小，查詢緩存至少會(huì)需要 40KB 的空間分配給其自身結(jié)構(gòu)，太小時(shí)緩存結(jié)果集就沒(méi)有意義，熱點(diǎn)數(shù)據(jù)保存不了多少，而且總是很快就被刷新出去；但也不能太大，否則可能過(guò)多占用內(nèi)存資源，影響整機(jī)性能，再說(shuō)太大也沒(méi)有意義，因?yàn)榧幢銛?shù)據(jù)不被刷新，但只要源數(shù)據(jù)發(fā)生變更，緩存中的數(shù)據(jù)也就自動(dòng)失效了，這種情況下分配多大都沒(méi)有意義。個(gè)人建議設(shè)置不要超過(guò) 256MB。

3.4 query_cache_limit

query_cache_limit：用來(lái)控制查詢緩存，能夠緩存的單條 SQL 語(yǔ)句生成的最大結(jié)果集，默認(rèn)是 1MB，超出的就不要進(jìn)入查詢緩存。這個(gè)大小對(duì)于很多場(chǎng)景都?jí)蛄?，縮小可以考慮，加大就不用了。

3.5 sort_buffer_size

sort_buffer_size：指定單個(gè)會(huì)話能夠使用的排序區(qū)的大小，默認(rèn)值為 256KB，建議設(shè)置為 1~4MB 之間。

3.6 read_buffer_size

read_buffer_size:指定隨機(jī)讀取時(shí)的數(shù)據(jù)緩存區(qū)大小，默認(rèn)是 256KB，最大能夠支持4GB，適當(dāng)加大本參數(shù)，對(duì)于提升全表掃描的效率會(huì)有幫助。

4.InnoDB專用參數(shù)

4.1 innodb_buffer_pool_size

innodb_buffer_pool_size：指定InnoDB引擎專用的緩存區(qū)大小，用來(lái)緩存表對(duì)象的數(shù)據(jù)及索引信息，默認(rèn)值為 128MB，最大能夠支持（2^64 -1）B.

如果你有很多事務(wù)的更新，插入或刪除很操作，通過(guò)修改innodb_buffer_pool 大小這個(gè)參數(shù)會(huì)大量的節(jié)省了磁盤I / O。

innodb_buffer_pool_size 是個(gè)全局參數(shù)，其所分配的緩存區(qū)將供所有被訪問(wèn)到的InnoDb表對(duì)象使用，若MySQL數(shù)據(jù)庫(kù)中的表對(duì)象以 InnoDb 為主，那么本參數(shù)的值就越大越好，官方文檔中建議，可以將該參數(shù)設(shè)置為云服務(wù)器物理內(nèi)存的70%~80%。

4.2 innodb_buffer_instances

innodb_buffer_instances：指定 InnoDB 緩存池分為多少個(gè)區(qū)域來(lái)使用，值得范圍從 1~64，默認(rèn)值為-1，表示由 InnoDB 自行調(diào)整。

只有當(dāng)innodb_buffer_pool_size參數(shù)值大于1GB時(shí)，本參數(shù)才有效，那么本參數(shù)怎么設(shè)置呢？個(gè)人感覺(jué)可以參照 InnoDB 緩存池的大小，以 GB 為單位，每GB指定一個(gè)instances。例如當(dāng)innodb_buffer_pool_size設(shè)置為16GB時(shí)，則指定 innodb_buffer_instances 設(shè)置為 16 即可。

5.參數(shù)優(yōu)化案例

測(cè)試云服務(wù)器有 16GB的物理內(nèi)存，假定其峰值最大的連接數(shù)為 500 個(gè)，表對(duì)象使用InnoDB 存儲(chǔ)引擎，我們的內(nèi)存參數(shù)如何配置呢？

具體配置如下：
（1）、首先，為操作系統(tǒng)預(yù)留 20% 的內(nèi)存，約為 3GB。
（2）、與線程相關(guān)的幾個(gè)關(guān)鍵參數(shù)設(shè)置如下：

  sort_buffer_size=2m
  read_buffer_size=2m
  read_rnd_buffer_size=2m
  join_buffer_size=2m

預(yù)計(jì)連接數(shù)達(dá)到峰值時(shí)，線程預(yù)計(jì)最大將有可能占用 500 *（2+2+2+2）= 4GB內(nèi)存（理論最大值）。

（3）、剩下的空間 16-3-4=9GB，就可以全部都分配給InnoDB 的緩存池，設(shè)定相關(guān)的參數(shù)如下：

innodb_buffer_pool_size=9g
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT
innodb_log_buffer_size=16m
innodb_flush_log_at_trx_commit=2

四、MySQL系統(tǒng)狀態(tài)

想要了解MySQL服務(wù)當(dāng)前在做什么，有個(gè)非常重要并且極為常用的命令：

SHOW [FULL] PROCESSLIST

SHOW PROCESSLIST 命令將每一個(gè)連接的線程，作為一條獨(dú)立的記錄輸出。

還有相似的語(yǔ)句，
SHOW PROFILES 和 SHOW PROFILE可以獲取會(huì)話執(zhí)行語(yǔ)句過(guò)程中，資源的使用情況。

看完以上關(guān)于Mysql性能有哪些調(diào)優(yōu)與測(cè)試的方法，很多讀者朋友肯定多少有一定的了解，如需獲取更多的行業(yè)知識(shí)信息 ，可以持續(xù)關(guān)注我們的行業(yè)資訊欄目的。

當(dāng)前文章：Mysql性能有哪些調(diào)優(yōu)與測(cè)試的方法
轉(zhuǎn)載來(lái)源：http://bm7419.com/article30/goedso.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供軟件開(kāi)發(fā)、建站公司、外貿(mào)建站、微信小程序、微信公眾號(hào)、外貿(mào)網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)