本篇文章給大家分享的是有關(guān)怎么實(shí)現(xiàn)MySQL數(shù)據(jù)庫性能診斷，小編覺得挺實(shí)用的，因此分享給大家學(xué)習(xí)，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

為東麗等地區(qū)用戶提供了全套網(wǎng)頁設(shè)計(jì)制作服務(wù)，及東麗網(wǎng)站建設(shè)行業(yè)解決方案。主營業(yè)務(wù)為成都網(wǎng)站建設(shè)、網(wǎng)站制作、東麗網(wǎng)站設(shè)計(jì)，以傳統(tǒng)方式定制建設(shè)網(wǎng)站，并提供域名空間備案等一條龍服務(wù)，秉承以專業(yè)、用心的態(tài)度為用戶提供真誠的服務(wù)。我們深信只要達(dá)到每一位用戶的要求，就會得到認(rèn)可，從而選擇與我們長期合作。這樣，我們也可以走得更遠(yuǎn)！

一：檢查mysql所在的服務(wù)器的性能，linux環(huán)境使用top和iostat

[root@S243 etc]# top 

top - 11:40:41 up 23 days, 17:06,  2 users,  load average: 0.11, 0.31, 0.32

Tasks: 617 total,   1 running, 616 sleeping,   0 stopped,   0 zombie

Cpu0  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st

Cpu1  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st

Cpu2  :  1.0%us,  0.7%sy,  0.0%ni, 98.0%id,  0.0%wa,  0.0%hi,  0.3%si,  0.0%st

Cpu3  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st

關(guān)于top的結(jié)果主要關(guān)注下 load average: 0.11, 0.31, 0.32 

系統(tǒng)負(fù)載(任務(wù)隊(duì)列的平均長度)三個(gè)值分別為1分鐘、5分鐘、15分鐘前到現(xiàn)在的平均值,小于1正?！具@三個(gè)一般會小于1，如果持續(xù)高于5，請仔細(xì)查看那個(gè)程序影響系統(tǒng)的運(yùn)行】”

[root@S243 etc]# iostat -d -x

Linux 2.6.32-131.0.15.el6.x86_64 (S243)         2016年10月20日  _x86_64_        (24 CPU)

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util

sda               1.02    14.59    6.77   27.88   297.25   519.70    23.58     0.07    1.92   0.34   1.17

輸出信息的含義

rrqm/s：每秒這個(gè)設(shè)備相關(guān)的讀取請求有多少被Merge了（當(dāng)系統(tǒng)調(diào)用需要讀取數(shù)據(jù)的時(shí)候，VFS將請求發(fā)到各個(gè)FS，如果FS發(fā)現(xiàn)不同的讀取請求讀取的是相同Block的數(shù)據(jù)，F(xiàn)S會將這個(gè)請求合并Merge）；wrqm/s：每秒這個(gè)設(shè)備相關(guān)的寫入請求有多少被Merge了。
rsec/s：每秒讀取的扇區(qū)數(shù)；
wsec/：每秒寫入的扇區(qū)數(shù)。
rKB/s：The number of read requests that were issued to the device per second；
wKB/s：The number of write requests that were issued to the device per second；
avgrq-sz 平均請求扇區(qū)的大小
avgqu-sz 是平均請求隊(duì)列的長度。毫無疑問，隊(duì)列長度越短越好。   
await：  每一個(gè)IO請求的處理的平均時(shí)間（單位是微秒毫秒）。這里可以理解為IO的響應(yīng)時(shí)間，一般地系統(tǒng)IO響應(yīng)時(shí)間應(yīng)該低于5ms，如果大于10ms就比較大了。
         這個(gè)時(shí)間包括了隊(duì)列時(shí)間和服務(wù)時(shí)間，也就是說，一般情況下，await大于svctm，它們的差值越小，則說明隊(duì)列時(shí)間越短，反之差值越大，隊(duì)列時(shí)間越長，說明系統(tǒng)出了問題。
svctm    表示平均每次設(shè)備I/O操作的服務(wù)時(shí)間（以毫秒為單位）。如果svctm的值與await很接近，表示幾乎沒有I/O等待，磁盤性能很好，如果await的值遠(yuǎn)高于svctm的值，則表示I/O隊(duì)列等待太長，         系統(tǒng)上運(yùn)行的應(yīng)用程序?qū)⒆兟?，上例?.92高于0.34說明存在一定的等待。

%util： 在統(tǒng)計(jì)時(shí)間內(nèi)所有處理IO時(shí)間，除以總共統(tǒng)計(jì)時(shí)間。例如，如果統(tǒng)計(jì)間隔1秒，該設(shè)備有0.8秒在處理IO，而0.2秒閑置，那么該設(shè)備的%util = 0.8/1 = 80%，所以該參數(shù)暗示了設(shè)備的繁忙程度

。一般地，如果該參數(shù)是100%表示設(shè)備已經(jīng)接近滿負(fù)荷運(yùn)行了（當(dāng)然如果是多磁盤，即使%util是100%，因?yàn)榇疟P的并發(fā)能力，所以磁盤使用未必就到了瓶頸）。

二：查看有沒有阻塞：

mysql> show processlist;        關(guān)注state  有沒有l(wèi)ock的狀態(tài)。有的話kill解決。

+---------+-------------+---------------------+---------------+-------------+---------+-----------------------------------------------------------------------------+------------------+

| Id      | User        | Host                | db            | Command     | Time    | State                                                                       | Info             |

|       3 | mailer      | 192.168.0.225:45135 | mailer        | Sleep       |      26 |                                                                             | NULL             |

|       8 | info_reader | 192.168.0.225:45194 | info          | Sleep       |      56 |                                                                             | NULL             |

|      23 | info_reader | 192.168.0.225:45451 | info          | Sleep       |      13 |                                                                             | NULL             |

|      78 | info_reader | 192.168.0.225:54249 | info          | Sleep       |      68 |                                                                             | NULL             |

|     180 | web_editer  | 192.168.0.225:46200 | info          | Sleep       |      26 |                                                                             | NULL             |

|     226 | web_editer  | 192.168.0.225:46584 | info          | Sleep       |      13 |                                                                             | NULL             |

|    2035 | info_reader | 192.168.0.225:53314 | info          | Sleep       |      36 |                                                                             | NULL             |

|    2052 | info_reader | 192.168.0.225:53447 | info          | Sleep       |      36 |                                                                             | NULL             |

|    2384 | ruby_syncer | 192.168.0.218:41868 | info          | Sleep       |       0 |                                                                             | NULL             |

|    2387 | ruby_syncer | 192.168.0.218:41870 | info          | Sleep       |       0 |                                                                             | NULL             |

三：查看mysql慢sql
1)首先檢查是否開啟了慢sql.  如下紅色顯示為開啟了mysql慢查詢。

mysql> show variables like '%query%';    

+------------------------------+------------------------------+

| Variable_name                | Value                        |

+------------------------------+------------------------------+

| binlog_rows_query_log_events | OFF                          |

| ft_query_expansion_limit     | 20                           |

| have_query_cache             | YES                          |

| long_query_time              | 4.000000                     |

| query_alloc_block_size       | 8192                         |

| query_cache_limit            | 2097152                      |

| query_cache_min_res_unit     | 4096                         |

| query_cache_size             | 104857600                    |

| query_cache_type             | OFF                          |

| query_cache_wlock_invalidate | OFF                          |

| query_prealloc_size          | 8192                         |

| slow_query_log               | ON                           |

| slow_query_log_file          | /mysql/datadir/S243-slow.log |

+------------------------------+------------------------------+

13 rows in set (0.00 sec)

拓展：mysql慢查詢相關(guān)的主要的三個(gè)參數(shù)為

（1）slow_query_log    #指定是否開啟慢查詢?nèi)罩?0/1或者off/on)

（2）slow_query_log_file  # 指定慢日志文件存放位置，可以為空，系統(tǒng)會給一個(gè)缺省的文件host_name-slow.log

（3）long_query_time   #設(shè)定慢查詢的閥值，超出次設(shè)定值的SQL即被記錄到慢查詢?nèi)罩?，缺省值?0s 

（4）log_queries_not_using_indexes: 不使用索引的慢查詢?nèi)罩臼欠裼涗浀剿饕?nbsp; （on/off)

（5）min_examined_row_limit：查詢檢查返回少于該參數(shù)指定行的SQL不被記錄到慢查詢?nèi)罩?nbsp;。

2)mysql>  show status like '%Slow_queries%';     #顯示了當(dāng)前慢查詢的數(shù)量，也就是正在執(zhí)行的。如果顯示0，說明當(dāng)前沒有正在執(zhí)行的時(shí)間超過long_query_time 值的sql.你需要去慢查詢?nèi)罩局胁榭础?br/>+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 2    |
+---------------+-------+
1 row in set (0.00 sec)
3）直接查看慢查詢?nèi)罩镜膬?nèi)用就可以，如下顯示了兩條慢sql.

[root@S243 datadir]# tail -n 10  S243-slow.log

# Time: 161020 15:19:39

# User@Host: mailer[mailer] @  [192.168.0.226]  Id: 1372832

# Query_time: 12.617656  Lock_time: 0.000072 Rows_sent: 1  Rows_examined: 19796776

SET timestamp=1476947979;

select count(*) as col_0_0_ from mailer.kehuguanzhus_logs_meta kehuguanzh0_ where kehuguanzh0_.state<>100 and kehuguanzh0_.guanzhu_id='4f41910e-0ec6-4042-8c4a-b2f0f9c8' and kehuguanzh0_.last_modify>='2016-09-20' and kehuguanzh0_.last_modify<'2016-10-21' order by kehuguanzh0_.id desc limit 2;

# Time: 161020 15:21:19

# User@Host: mailer[mailer] @  [192.168.0.226]  Id: 1372832

# Query_time: 12.489680  Lock_time: 0.000155 Rows_sent: 1  Rows_examined: 19796842

SET timestamp=1476948079;

select count(*) as col_0_0_ from mailer.kehuguanzhus_logs_meta kehuguanzh0_ where kehuguanzh0_.state<>100 and kehuguanzh0_.guanzhu_id='53b344cf-6239-4882-afbb-772b90a4' and kehuguanzh0_.last_modify>='2016-09-20' and kehuguanzh0_.last_modify<'2016-10-21' order by kehuguanzh0_.id desc limit 2;

我們也可以利用mysqldumpslow來格式化慢查詢?nèi)罩镜母袷?，便于我們查閱?/p>

#獲取mysqldumpslow的幫助信息 

[root@S243 datadir]# mysqldumpslow --help 

Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

--verbose    verbose

  --debug      debug

  --help       write this text to standard output

  -v           verbose

  -d           debug

  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default

                al: average lock time

                ar: average rows sent

                at: average query time

                 c: count

                 l: lock time

                 r: rows sent

                 t: query time 

  -r           reverse the sort order (largest last instead of first)

  -t NUM       just show the top n queries

  -a           don't abstract all numbers to N and strings to 'S'

  -n NUM       abstract numbers with at least n digits within names

  -g PATTERN   grep: only consider stmts that include this string

  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),

               default is '*', i.e. match all

  -i NAME      name of server instance (if using mysql.server startup script)

  -l           don't subtract lock time from total time

例如：

以下是按照最大耗用時(shí)間排最后，只顯示2條的方式格式化日志文件  

[root@S243 datadir]# mysqldumpslow -r -t 2  /mysql/datadir/S243-slow.log

Reading mysql slow query log from /var/lib/mysql/suse11b-slow.log  

Count: 1  Time=1.57s (1s)  Lock=0.00s (0s)  Rows=83.0 (83), root[root]@localhost  

select table_schema,table_name,count(*) from tb_slow  

group by table_schema,table_name order by N,N  

Count: 4  Time=16.87s (67s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost  

 insert into tb_slow select * from tb_slow  

[root@S243 datadir]# mysqldumpslow -s   /mysql/datadir/S243-slow.log   ###最耗時(shí)間的依次排序。

由于這個(gè)慢查詢?nèi)罩臼菬o限增大的，他是好長時(shí)間的一個(gè)累計(jì)，而數(shù)據(jù)庫性能慢的時(shí)候，往往你只需要查詢當(dāng)時(shí)的那段時(shí)間的慢sql語句，然后針對性的去優(yōu)化，所以沒必要使用mysqldumpslow 去做什么排序之類的，因?yàn)樽詈臅r(shí)的sql可能僅僅是在很久以前執(zhí)行過一次，直接用tail -n 去看某個(gè)時(shí)間段的就可以了。

確定了影響性能的慢sql之后，然后針對性的去優(yōu)化，加索引，改寫sql,。。。。。

體會：mysql性能診斷過程和oracle類似，也是首先查看mysql的服務(wù)器的性能，然后看mysql數(shù)據(jù)的性能（有沒有鎖之類的）最后確定性能慢的sql.然后針對性的去優(yōu)化。

以上就是怎么實(shí)現(xiàn)mysql數(shù)據(jù)庫性能診斷，小編相信有部分知識點(diǎn)可能是我們?nèi)粘９ぷ鲿姷交蛴玫降摹ＯＭ隳芡ㄟ^這篇文章學(xué)到更多知識。更多詳情敬請關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。