MySQL數(shù)據(jù)庫(kù)服務(wù)器的性能分析

小編給大家分享一下MySQL數(shù)據(jù)庫(kù)服務(wù)器的性能分析，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

3.1簡(jiǎn)介

性能：為完成某件任務(wù)所需要的時(shí)間度量，in other words 性能即響應(yīng)時(shí)間

吞吐量：?jiǎn)挝粫r(shí)間內(nèi)的查詢數(shù)據(jù)（性能定義的倒數(shù)）

第一步：弄清楚時(shí)間都去哪了，在哪消耗了時(shí)間

如果通測(cè)量沒(méi)有找到答案，測(cè)量方式錯(cuò)了或不夠完善，只測(cè)量需要優(yōu)化的活動(dòng)

不要在錯(cuò)誤的時(shí)間啟動(dòng)或停止測(cè)試，測(cè)量的是聚合后的信息而不是目標(biāo)活動(dòng)本身；需要定位和優(yōu)化子任務(wù)

原則：無(wú)法測(cè)量便無(wú)法有效地優(yōu)化

3.1.1通過(guò)性能剖析進(jìn)行優(yōu)化

性能剖析：測(cè)量、分析時(shí)間花費(fèi)在哪里的主要方法

1、測(cè)量任務(wù)所花費(fèi)的時(shí)間；2、對(duì)結(jié)果統(tǒng)計(jì)、排序（重要前排）

可將相似任務(wù)分組匯總，通過(guò)性能剖析報(bào)告獲需要的結(jié)果；報(bào)告會(huì)列出all任務(wù)，每行記錄一個(gè)任務(wù)：

任務(wù)名、執(zhí)行時(shí)間、消耗時(shí)間、平均執(zhí)行時(shí)間，執(zhí)行占全部時(shí)間的百分比；按照任務(wù)的消耗時(shí)間降序排序；

性能剖析類型：

基于執(zhí)行時(shí)間的分析：什么任務(wù)的執(zhí)行時(shí)間最長(zhǎng)

基于等待的分析：判斷任務(wù)在什么地方唄阻塞的時(shí)間最長(zhǎng)

3.1.2理解性能剖析

性能剖析中缺失但是重要的信息：

1、值得優(yōu)化的查詢

占總響應(yīng)時(shí)間比重很小的查詢不值得優(yōu)化；成本大于收益、停止優(yōu)化

2、異常情況

沒(méi)有顯式要優(yōu)化的也要優(yōu)化，如執(zhí)行次數(shù)少但每次都特別慢的任務(wù)

3、未知的未知

丟失時(shí)間：任務(wù)總時(shí)間與實(shí)際測(cè)量到的時(shí)間的差，即使沒(méi)有發(fā)現(xiàn)也要注意這類問(wèn)題存在的可能性

4、被掩藏的細(xì)節(jié)

無(wú)法顯示all響應(yīng)時(shí)間的分布，更多信息、直方圖、百分比、標(biāo)準(zhǔn)差、偏差指數(shù)

5、無(wú)法再更高層次的堆棧中進(jìn)行交互式 分析

3.2對(duì)應(yīng)用程序進(jìn)行性能剖析：自上而下

性能瓶頸的影響因素：

1、外部資源，調(diào)用外部web服務(wù)或搜索引擎

2、應(yīng)用需要處理大量數(shù)據(jù)，分析一個(gè)超大的xml文件

3、循環(huán)中執(zhí)行昂貴的操作：濫用正則

4、使用低效的算法：暴力搜索算法

建議：新的項(xiàng)目中應(yīng)考慮包含性能剖析的代碼

3.2.1測(cè)量PHP應(yīng)用程序：空

3.3剖析MySQL查詢

3.3.1剖析服務(wù)器負(fù)載

鋪獲MySQL查詢到日志文件：

1、慢查詢?nèi)罩荆洪_(kāi)銷低、精度高，大的磁盤(pán)空間，長(zhǎng)期開(kāi)啟 注意部署日志輪轉(zhuǎn)工具，只在收集負(fù)載樣本期間開(kāi)啟即可，5.1后微秒級(jí)別；

2、通用日志，查詢請(qǐng)求到服務(wù)器時(shí)進(jìn)行記錄，不包含響應(yīng)時(shí)間和執(zhí)行計(jì)劃

分析查詢?nèi)罩?/h4>

自頂向下，先生成剖析報(bào)告（pt-query-digest），查看特別關(guān)注的部分

3.3.2剖析單條查詢

思考為什么花費(fèi)這么長(zhǎng)時(shí)間、如何去優(yōu)化

使用SHOW PROFILE:MySQL5.1后

查看： show variables like "%pro%";【源】

默認(rèn)禁用，開(kāi)啟：set profiling=1；然后在服務(wù)器執(zhí)行語(yǔ)句（關(guān)閉 set profiling=off;）

語(yǔ)法：

SHOW PROFILE [type [, type] ... ]  
    [FOR QUERY n]  
    [LIMIT row_count [OFFSET offset]]  
  
type:  
    ALL                --顯示所有的開(kāi)銷信息  
  | BLOCK IO           --顯示塊IO相關(guān)開(kāi)銷  
  | CONTEXT SWITCHES   --上下文切換相關(guān)開(kāi)銷  
  | CPU                --顯示CPU相關(guān)開(kāi)銷信息  
  | IPC                --顯示發(fā)送和接收相關(guān)開(kāi)銷信息  
  | MEMORY             --顯示內(nèi)存相關(guān)開(kāi)銷信息  
  | PAGE FAULTS        --顯示頁(yè)面錯(cuò)誤相關(guān)開(kāi)銷信息  
  | SOURCE             --顯示和Source_function，Source_file，Source_line相關(guān)的開(kāi)銷信息  
  | SWAPS              --顯示交換次數(shù)相關(guān)開(kāi)銷的信息

實(shí)質(zhì)是這些開(kāi)銷信息被記錄到information_schema.profiling表

show profiles；查看
show profile for query 2; 獲取指定查詢的開(kāi)銷（第二條查詢開(kāi)銷明細(xì)）
show profile cpu for query 2 ;查看特定部分的開(kāi)銷，如下為CPU部分的開(kāi)銷 
show profile block io,cpu for query 2;  同時(shí)查看不同資源開(kāi)銷

使用SHOW STATUS:計(jì)數(shù)器

全局show global status、基于某個(gè)連接會(huì)話級(jí)別，作用域要注意

計(jì)數(shù)器顯示活動(dòng)的頻繁程度，常用：句柄計(jì)數(shù)器、臨時(shí)文件、表計(jì)數(shù)器

會(huì)創(chuàng)建臨時(shí)表，通過(guò)句柄操作（引用、指針？）訪問(wèn)此臨時(shí)表，影響show status結(jié)果中對(duì)應(yīng)的數(shù)字

使用慢查詢?nèi)罩荆骸驹础俊驹础?/h4>

將MySQL中響應(yīng)時(shí)間超過(guò)閾值long_query_time的語(yǔ)句記錄到慢查詢?nèi)罩局校ㄈ罩究梢詫?xiě)入文件或者數(shù)據(jù)庫(kù)表，如果對(duì)性能要求高的話，建議寫(xiě)文件），默認(rèn)是10s，需要手動(dòng)開(kāi)啟

查看：

（1）slow_query_log的值為ON為開(kāi)啟慢查詢?nèi)罩?，OFF則為關(guān)閉慢查詢?nèi)罩尽?/p>

（2）slow_query_log_file 的值是記錄的慢查詢?nèi)罩镜轿募校?strong>注意：默認(rèn)名為主機(jī)名.log，慢查詢?nèi)罩臼欠駥?xiě)入指定文件中，需要指定慢查詢的輸出日志格式為文件，相關(guān)命令為：show variables like ‘%log_output%’；去查看輸出的格式）。

（3）long_query_time 指定了慢查詢的閾值，即如果執(zhí)行語(yǔ)句的時(shí)間超過(guò)該閾值則為慢查詢語(yǔ)句，默認(rèn)值為10秒。

（4）log_queries_not_using_indexes 如果值設(shè)置為ON，則會(huì)記錄所有沒(méi)有利用索引的查詢（注意：如果只是將log_queries_not_using_indexes設(shè)置為ON，而將slow_query_log設(shè)置為OFF，此時(shí)該設(shè)置也不會(huì)生效，即該設(shè)置生效的前提是slow_query_log的值設(shè)置為ON），一般在性能調(diào)優(yōu)的時(shí)候會(huì)暫時(shí)開(kāi)啟，開(kāi)啟后使用full index scan的sql也會(huì)被記錄到慢查詢?nèi)罩尽?/p>

//上述命令只對(duì)當(dāng)前生效，當(dāng)MySQL重啟失效，如果要永久生效，需要配置my.cnf

查看輸出格式：文件？表show variables like ‘%log_output%’;

開(kāi)啟通用日志查詢： set global general_log=on;

關(guān)閉通用日志查詢： set globalgeneral_log=off;

設(shè)置通用日志輸出為表方式： set globallog_output=’TABLE’;

設(shè)置通用日志輸出為文件方式： set globallog_output=’FILE’;

設(shè)置通用日志輸出為表和文件方式：set global log_output=’FILE,TABLE’;

查詢慢查詢語(yǔ)句的個(gè)數(shù)：show global status like ‘%slow%’;

日志部分內(nèi)容簡(jiǎn)介：

哪條語(yǔ)句導(dǎo)致慢查詢（sql_text），該慢查詢語(yǔ)句的查詢時(shí)間（query_time），鎖表時(shí)間（Lock_time），以及掃描過(guò)的行數(shù)（rows_examined）等信息。

利用自帶的慢查詢?nèi)罩痉治龉ぞ撸簃ysqldumpslow

perl mysqldumpslow –s c –t 10 slow-query.log

-s 表示按何種方式排序，c、t、l、r分別是按照記錄次數(shù)、時(shí)間、查詢時(shí)間、返回的記錄數(shù)來(lái)排序，ac、at、al、ar，表示相應(yīng)的倒敘；-t 表示top的意思，后面跟著的數(shù)據(jù)表示返回前面多少條；-g 后面可以寫(xiě)正則表達(dá)式匹配，大小寫(xiě)不敏感。

使用Performance Schema:【源】【源】

監(jiān)視MySQL服務(wù)器，收集性能參數(shù)，且表的存儲(chǔ)引擎PERFORMANCE_SCHEMA，低耗能

本地服務(wù)器，表是內(nèi)存表，表內(nèi)容在服務(wù)器啟動(dòng)時(shí)重新填充，關(guān)閉時(shí)丟棄，更改不會(huì)被復(fù)制或?qū)懭攵M(jìn)制日志

特性：

性能方案配置可被動(dòng)態(tài)的執(zhí)行SQL修改，立即影響到數(shù)據(jù)收集

監(jiān)控服務(wù)事件：事件是服務(wù)做并被感知到的任何事，時(shí)間信息可被收集

數(shù)據(jù)庫(kù)性能方案，提供對(duì)運(yùn)行時(shí)數(shù)據(jù)庫(kù)服務(wù)進(jìn)行內(nèi)部檢查的方式，關(guān)注性能數(shù)據(jù)

特定于一個(gè)數(shù)據(jù)庫(kù)服務(wù)，數(shù)據(jù)庫(kù)表關(guān)聯(lián)到數(shù)據(jù)服務(wù)，修改不會(huì)被備份也不寫(xiě)進(jìn)二進(jìn)制日志

存儲(chǔ)引擎用“感知點(diǎn)”收集事件數(shù)據(jù)，且存儲(chǔ)在performance_schema數(shù)據(jù)庫(kù)，可通過(guò)select語(yǔ)句進(jìn)行查詢

補(bǔ)充：數(shù)據(jù)庫(kù)初始安裝有三個(gè)基本庫(kù)

mysql

包含權(quán)限配置，事件，存儲(chǔ)引擎狀態(tài)，主從信息，日志，時(shí)區(qū)信息，用戶權(quán)限配置等

information_schema

對(duì)數(shù)據(jù)庫(kù)元數(shù)據(jù)的抽象分析，由此提供了SQL語(yǔ)句方式來(lái)查詢數(shù)據(jù)庫(kù)運(yùn)行時(shí)狀態(tài)，每次對(duì)information_schema的查詢都產(chǎn)生對(duì)metadata的互斥訪問(wèn)，影響其他數(shù)據(jù)庫(kù)的訪問(wèn)性能。

performance_schema

內(nèi)存型數(shù)據(jù)庫(kù)，使用performance_schema 存儲(chǔ)引擎，通過(guò)事件機(jī)制將mysql服務(wù)的運(yùn)行時(shí)狀態(tài)采集并存儲(chǔ)在performace_schema數(shù)據(jù)庫(kù)。注意，兩個(gè)單詞之間用下劃線連接時(shí)，表示performance_schema是一個(gè)數(shù)據(jù)庫(kù)；用空格分開(kāi)時(shí)，表示一個(gè)數(shù)據(jù)庫(kù)性能方案，也表示一個(gè)存儲(chǔ)引擎。

以上是“MySQL數(shù)據(jù)庫(kù)服務(wù)器的性能分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！