MySQL中如何查看“慢查詢”，如何分析執(zhí)行SQL的效率？

一、MySQL數(shù)據(jù)庫(kù)有幾個(gè)配置選項(xiàng)可以幫助我們及時(shí)捕獲低效SQL語(yǔ)句

創(chuàng)新互聯(lián)客戶idc服務(wù)中心，提供成都服務(wù)器托管、成都服務(wù)器、成都主機(jī)托管、成都雙線服務(wù)器等業(yè)務(wù)的一站式服務(wù)。通過(guò)各地的服務(wù)中心，我們向成都用戶提供優(yōu)質(zhì)廉價(jià)的產(chǎn)品以及開(kāi)放、透明、穩(wěn)定、高性價(jià)比的服務(wù)，資深網(wǎng)絡(luò)工程師在機(jī)房提供7*24小時(shí)標(biāo)準(zhǔn)級(jí)技術(shù)保障。

1，slow_query_log

這個(gè)參數(shù)設(shè)置為ON，可以捕獲執(zhí)行時(shí)間超過(guò)一定數(shù)值的SQL語(yǔ)句。

2，long_query_time

當(dāng)SQL語(yǔ)句執(zhí)行時(shí)間超過(guò)此數(shù)值時(shí)，就會(huì)被記錄到日志中，建議設(shè)置為1或者更短。

3，slow_query_log_file

記錄日志的文件名。

4，log_queries_not_using_indexes

這個(gè)參數(shù)設(shè)置為ON，可以捕獲到所有未使用索引的SQL語(yǔ)句，盡管這個(gè)SQL語(yǔ)句有可能執(zhí)行得挺快。

二、檢測(cè)mysql中sql語(yǔ)句的效率的方法

1、通過(guò)查詢?nèi)罩?/p>

（1）、Windows下開(kāi)啟MySQL慢查詢

MySQL在Windows系統(tǒng)中的配置文件一般是是my.ini找到[mysqld]下面加上

代碼如下

log-slow-queries = F:/MySQL/log/mysqlslowquery。log

long_query_time = 2

（2）、Linux下啟用MySQL慢查詢

MySQL在Windows系統(tǒng)中的配置文件一般是是my.cnf找到[mysqld]下面加上

代碼如下

log-slow-queries=/data/mysqldata/slowquery。log

long_query_time=2

說(shuō)明

log-slow-queries = F:/MySQL/log/mysqlslowquery。

為慢查詢?nèi)罩敬娣诺奈恢?，一般這個(gè)目錄要有MySQL的運(yùn)行帳號(hào)的可寫(xiě)權(quán)限，一般都將這個(gè)目錄設(shè)置為MySQL的數(shù)據(jù)存放目錄；

long_query_time=2中的2表示查詢超過(guò)兩秒才記錄；

2.show processlist 命令

SHOW PROCESSLIST顯示哪些線程正在運(yùn)行。您也可以使用mysqladmin processlist語(yǔ)句得到此信息。

各列的含義和用途：

ID列

一個(gè)標(biāo)識(shí)，你要kill一個(gè)語(yǔ)句的時(shí)候很有用，用命令殺掉此查詢 /*/mysqladmin kill 進(jìn)程號(hào)。

user列

顯示單前用戶，如果不是root，這個(gè)命令就只顯示你權(quán)限范圍內(nèi)的sql語(yǔ)句。

host列

顯示這個(gè)語(yǔ)句是從哪個(gè)ip的哪個(gè)端口上發(fā)出的。用于追蹤出問(wèn)題語(yǔ)句的用戶。

db列

顯示這個(gè)進(jìn)程目前連接的是哪個(gè)數(shù)據(jù)庫(kù)。

command列

顯示當(dāng)前連接的執(zhí)行的命令，一般就是休眠（sleep），查詢（query），連接（connect）。

time列

此這個(gè)狀態(tài)持續(xù)的時(shí)間，單位是秒。

state列

顯示使用當(dāng)前連接的sql語(yǔ)句的狀態(tài)，很重要的列，后續(xù)會(huì)有所有的狀態(tài)的描述，請(qǐng)注意，state只是語(yǔ)句執(zhí)行中的某一個(gè)狀態(tài)，一個(gè) sql語(yǔ)句，以查詢?yōu)槔?，可能需要?jīng)過(guò)copying to tmp table，Sorting result，Sending data等狀態(tài)才可以完成

info列

顯示這個(gè)sql語(yǔ)句，因?yàn)殚L(zhǎng)度有限，所以長(zhǎng)的sql語(yǔ)句就顯示不全，但是一個(gè)判斷問(wèn)題語(yǔ)句的重要依據(jù)。

這個(gè)命令中最關(guān)鍵的就是state列，mysql列出的狀態(tài)主要有以下幾種：

Checking table

　正在檢查數(shù)據(jù)表（這是自動(dòng)的）。

Closing tables

　正在將表中修改的數(shù)據(jù)刷新到磁盤(pán)中，同時(shí)正在關(guān)閉已經(jīng)用完的表。這是一個(gè)很快的操作，如果不是這樣的話，就應(yīng)該確認(rèn)磁盤(pán)空間是否已經(jīng)滿了或者磁盤(pán)是否正處于重負(fù)中。

Connect Out

　復(fù)制從服務(wù)器正在連接主服務(wù)器。

Copying to tmp table on disk

　由于臨時(shí)結(jié)果集大于tmp_table_size，正在將臨時(shí)表從內(nèi)存存儲(chǔ)轉(zhuǎn)為磁盤(pán)存儲(chǔ)以此節(jié)省內(nèi)存。

Creating tmp table

　正在創(chuàng)建臨時(shí)表以存放部分查詢結(jié)果。

deleting from main table

　服務(wù)器正在執(zhí)行多表刪除中的第一部分，剛刪除第一個(gè)表。

deleting from reference tables

　服務(wù)器正在執(zhí)行多表刪除中的第二部分，正在刪除其他表的記錄。

Flushing tables

　正在執(zhí)行FLUSH TABLES，等待其他線程關(guān)閉數(shù)據(jù)表。

Killed

　發(fā)送了一個(gè)kill請(qǐng)求給某線程，那么這個(gè)線程將會(huì)檢查kill標(biāo)志位，同時(shí)會(huì)放棄下一個(gè)kill請(qǐng)求。MySQL會(huì)在每次的主循環(huán)中檢查kill標(biāo)志位，不過(guò)有些情況下該線程可能會(huì)過(guò)一小段才能死掉。如果該線程程被其他線程鎖住了，那么kill請(qǐng)求會(huì)在鎖釋放時(shí)馬上生效。

Locked

　被其他查詢鎖住了。

Sending data

　正在處理SELECT查詢的記錄，同時(shí)正在把結(jié)果發(fā)送給客戶端。

Sorting for group

　正在為GROUP BY做排序。

　Sorting for order

　正在為ORDER BY做排序。

Opening tables

　這個(gè)過(guò)程應(yīng)該會(huì)很快，除非受到其他因素的干擾。例如，在執(zhí)ALTER TABLE或LOCK TABLE語(yǔ)句行完以前，數(shù)據(jù)表無(wú)法被其他線程打開(kāi)。正嘗試打開(kāi)一個(gè)表。

Removing duplicates

　正在執(zhí)行一個(gè)SELECT DISTINCT方式的查詢，但是MySQL無(wú)法在前一個(gè)階段優(yōu)化掉那些重復(fù)的記錄。因此，MySQL需要再次去掉重復(fù)的記錄，然后再把結(jié)果發(fā)送給客戶端。

Reopen table

　獲得了對(duì)一個(gè)表的鎖，但是必須在表結(jié)構(gòu)修改之后才能獲得這個(gè)鎖。已經(jīng)釋放鎖，關(guān)閉數(shù)據(jù)表，正嘗試重新打開(kāi)數(shù)據(jù)表。

Repair by sorting

　修復(fù)指令正在排序以創(chuàng)建索引。

Repair with keycache

　修復(fù)指令正在利用索引緩存一個(gè)一個(gè)地創(chuàng)建新索引。它會(huì)比Repair by sorting慢些。

Searching rows for update

　正在講符合條件的記錄找出來(lái)以備更新。它必須在UPDATE要修改相關(guān)的記錄之前就完成了。

Sleeping

　正在等待客戶端發(fā)送新請(qǐng)求.

System lock

　正在等待取得一個(gè)外部的系統(tǒng)鎖。如果當(dāng)前沒(méi)有運(yùn)行多個(gè)mysqld服務(wù)器同時(shí)請(qǐng)求同一個(gè)表，那么可以通過(guò)增加--skip-external-locking參數(shù)來(lái)禁止外部系統(tǒng)鎖。

Upgrading lock

　INSERT DELAYED正在嘗試取得一個(gè)鎖表以插入新記錄。

Updating

　正在搜索匹配的記錄，并且修改它們。

User Lock

　正在等待GET_LOCK()。

Waiting for tables

　該線程得到通知，數(shù)據(jù)表結(jié)構(gòu)已經(jīng)被修改了，需要重新打開(kāi)數(shù)據(jù)表以取得新的結(jié)構(gòu)。然后，為了能的重新打開(kāi)數(shù)據(jù)表，必須等到所有其他線程關(guān)閉這個(gè)表。以下幾種情況下會(huì)產(chǎn)生這個(gè)通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。

waiting for handler insert

　INSERT DELAYED已經(jīng)處理完了所有待處理的插入操作，正在等待新的請(qǐng)求。

　大部分狀態(tài)對(duì)應(yīng)很快的操作，只要有一個(gè)線程保持同一個(gè)狀態(tài)好幾秒鐘，那么可能是有問(wèn)題發(fā)生了，需要檢查一下。

　還有其他的狀態(tài)沒(méi)在上面中列出來(lái)，不過(guò)它們大部分只是在查看服務(wù)器是否有存在錯(cuò)誤是才用得著。

例如如圖：

3、explain來(lái)了解SQL執(zhí)行的狀態(tài)

explain顯示了mysql如何使用索引來(lái)處理select語(yǔ)句以及連接表?？梢詭椭x擇更好的索引和寫(xiě)出更優(yōu)化的查詢語(yǔ)句。

使用方法，在select語(yǔ)句前加上explain就可以了：

例如：

explain select surname,first_name form a,b where a.id=b.id

結(jié)果如圖

EXPLAIN列的解釋

table

顯示這一行的數(shù)據(jù)是關(guān)于哪張表的

type

這是重要的列，顯示連接使用了何種類(lèi)型。從最好到最差的連接類(lèi)型為const、eq_reg、ref、range、indexhe和ALL

possible_keys

顯示可能應(yīng)用在這張表中的索引。如果為空，沒(méi)有可能的索引?？梢詾橄嚓P(guān)的域從WHERE語(yǔ)句中選擇一個(gè)合適的語(yǔ)句

key

實(shí)際使用的索引。如果為NULL，則沒(méi)有使用索引。很少的情況下，MYSQL會(huì)選擇優(yōu)化不足的索引。這種情況下，可以在SELECT語(yǔ)句 中使用USE INDEX（indexname）來(lái)強(qiáng)制使用一個(gè)索引或者用IGNORE INDEX（indexname）來(lái)強(qiáng)制MYSQL忽略索引

key_len

使用的索引的長(zhǎng)度。在不損失精確性的情況下，長(zhǎng)度越短越好

ref

顯示索引的哪一列被使用了，如果可能的話，是一個(gè)常數(shù)

rows

MYSQL認(rèn)為必須檢查的用來(lái)返回請(qǐng)求數(shù)據(jù)的行數(shù)

Extra

關(guān)于MYSQL如何解析查詢的額外信息。將在表4.3中討論，但這里可以看到的壞的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，結(jié)果是檢索會(huì)很慢

extra列返回的描述的意義

Distinct

一旦MYSQL找到了與行相聯(lián)合匹配的行，就不再搜索了

Not exists

MYSQL優(yōu)化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標(biāo)準(zhǔn)的行，就不再搜索了

Range checked for each Record（index map:#）

沒(méi)有找到理想的索引，因此對(duì)于從前面表中來(lái)的每一個(gè)行組合，MYSQL檢查使用哪個(gè)索引，并用它來(lái)從表中返回行。這是使用索引的最慢的連接之一

Using filesort

看到這個(gè)的時(shí)候，查詢就需要優(yōu)化了。MYSQL需要進(jìn)行額外的步驟來(lái)發(fā)現(xiàn)如何對(duì)返回的行排序。它根據(jù)連接類(lèi)型以及存儲(chǔ)排序鍵值和匹配條件的全部行的行指針來(lái)排序全部行

Using index

列數(shù)據(jù)是從僅僅使用了索引中的信息而沒(méi)有讀取實(shí)際的行動(dòng)的表返回的，這發(fā)生在對(duì)表的全部的請(qǐng)求列都是同一個(gè)索引的部分的時(shí)候

Using temporary

看到這個(gè)的時(shí)候，查詢需要優(yōu)化了。這里，MYSQL需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來(lái)存儲(chǔ)結(jié)果，這通常發(fā)生在對(duì)不同的列集進(jìn)行ORDER BY上，而不是GROUP BY上

Where used

使用了WHERE從句來(lái)限制哪些行將與下一張表匹配或者是返回給用戶。如果不想返回表中的全部行，并且連接類(lèi)型ALL或index，這就會(huì)發(fā)生，或者是查詢有問(wèn)題不同連接類(lèi)型的解釋?zhuān)ò凑招矢叩偷捻樞蚺判颍?/p>

const

表中的一個(gè)記錄的最大值能夠匹配這個(gè)查詢（索引可以是主鍵或惟一索引）。因?yàn)橹挥幸恍校@個(gè)值實(shí)際就是常數(shù)，因?yàn)镸YSQL先讀這個(gè)值然后把它當(dāng)做常數(shù)來(lái)對(duì)待

eq_ref

在連接中，MYSQL在查詢時(shí)，從前面的表中，對(duì)每一個(gè)記錄的聯(lián)合都從表中讀取一個(gè)記錄，它在查詢使用了索引為主鍵或惟一鍵的全部時(shí)使用

ref

這個(gè)連接類(lèi)型只有在查詢使用了不是惟一或主鍵的鍵或者是這些類(lèi)型的部分（比如，利用最左邊前綴）時(shí)發(fā)生。對(duì)于之前的表的每一個(gè)行聯(lián)合，全部記錄都將從表中讀出。這個(gè)類(lèi)型嚴(yán)重依賴于根據(jù)索引匹配的記錄多少—越少越好

range

這個(gè)連接類(lèi)型使用索引返回一個(gè)范圍中的行，比如使用或查找東西時(shí)發(fā)生的情況

index

這個(gè)連接類(lèi)型對(duì)前面的表中的每一個(gè)記錄聯(lián)合進(jìn)行完全掃描（比ALL更好，因?yàn)樗饕话阈∮诒頂?shù)據(jù)）

ALL

這個(gè)連接類(lèi)型對(duì)于前面的每一個(gè)記錄聯(lián)合進(jìn)行完全掃描，這一般比較糟糕，應(yīng)該盡量避免

如何用SQLyog來(lái)分析MySQL數(shù)據(jù)庫(kù)

用SQLyog來(lái)分析MySQL數(shù)據(jù)庫(kù) SOLyog的下載 安裝以及使用很簡(jiǎn)單 我去了相關(guān)網(wǎng)站下載 它只有 K字節(jié)大小 它把兩個(gè)文件(一個(gè)可執(zhí)行文件 exe和一個(gè)動(dòng)態(tài)鏈接庫(kù)文件 dll)安裝到C:\Program Files\SQLyog路徑下 然后運(yùn)行可執(zhí)行文件

安裝后沒(méi)有必要再訪問(wèn)該網(wǎng)站了 我訪問(wèn)該網(wǎng)站是得到了一個(gè)消息 說(shuō)它的域名沒(méi)有設(shè)置(configured) 登記 或正在建設(shè)中 我不清楚這個(gè)問(wèn)題是暫時(shí)的還是一直是這樣 該軟件是免費(fèi)的 并且沒(méi)有標(biāo)志廣告(banner ads) 所以它可能是一個(gè)特定的尚未最終定型的商業(yè)模型 最終可能還是要負(fù)費(fèi)的

數(shù)據(jù)庫(kù) 表格(table)和列樹(shù)(column tree)

該程序一啟動(dòng)就開(kāi)始詢問(wèn)我的登錄到MySOL服務(wù)器的口令 我只需要輸入我的服務(wù)器名字 用戶id和登錄密碼 所有其它的設(shè)置都是正確的默認(rèn)值 然后(當(dāng)我開(kāi)始其它事務(wù) 重啟幾次 睡了一會(huì)之后) 我重新運(yùn)行該程序 這時(shí)只需要再次輸入我的登錄密碼 該程序沒(méi)有保存密碼的選項(xiàng) 你可以認(rèn)為這是該程序的一個(gè)bug 也可以說(shuō)是程序的保密特性

一旦你登錄之后 界面就是很值得注意 MySOL服務(wù)器上所有的數(shù)據(jù)庫(kù)都顯示在一個(gè)樹(shù)型控件上 你只能訪問(wèn)你在登錄時(shí)授權(quán)的那個(gè)數(shù)據(jù)庫(kù) 如果你點(diǎn)開(kāi)代表授權(quán)給你的那個(gè)數(shù)據(jù)庫(kù)的樹(shù)型結(jié)構(gòu) 你就可以看到一系列代表表格的節(jié)點(diǎn) 點(diǎn)開(kāi)表格節(jié)點(diǎn)后 你就可以看到一系列顯示字段名的節(jié)點(diǎn)和另一個(gè)代表索引的節(jié)點(diǎn)集合

索引界面絕對(duì)是個(gè)好東東 這樣你就可以CRUD查詢索引和關(guān)鍵字了 這相對(duì)前端數(shù)據(jù)庫(kù)如Microsoft Access來(lái)說(shuō)是個(gè)提高 如果考慮到MySOL剛剛開(kāi)始提供對(duì)主(primary)和非相關(guān)(foreign)關(guān)鍵字關(guān)系的支持 本程序這部分的設(shè)計(jì)是很成熟的 在右下方的面板上 有四個(gè)標(biāo)簽頁(yè) 即 結(jié)果(Result) 消息(Message) 對(duì)象(Object)和歷史(History)

有什么缺點(diǎn)？

我試圖發(fā)現(xiàn)該程序的缺點(diǎn) 不過(guò)只發(fā)現(xiàn)了一個(gè) 如果你在Win Dependency Walker下運(yùn)行程序的 exe文件 你會(huì)發(fā)現(xiàn)它引用了DLG dll文件 而DLG dll又輪流引用AppHelp 實(shí)事上 CommDlg調(diào)用AppHelp 而當(dāng)AppHelp沒(méi)有請(qǐng)求函數(shù)時(shí) CommDlg這么做根本就是浪費(fèi)資源

過(guò)于簡(jiǎn)單？

在SQLyog FAQ上 有一種觀點(diǎn)認(rèn)為該軟件沒(méi)有正式歸檔的必要 當(dāng)然 FAQ(常見(jiàn)問(wèn)題解答)本身就是一種歸檔 SQLyog的界面非常直觀 我建議你打印一份MySOL文檔(包括SQL特殊語(yǔ)法擴(kuò)展) 我就是這么做的 它只用了一個(gè)半英寸的活頁(yè)封面

最后一步？

FAQ還讓人想到一個(gè)讓人耳朵起了老繭卻又是正確的Occam s Razor準(zhǔn)則——一切超出必要的復(fù)雜性都是沒(méi)有必要的 我之所以到處 推銷(xiāo) 這個(gè)工具 就是因?yàn)樗梢詾槲覀兲峁┮粋€(gè)可以管理MySOL服務(wù)器上許多數(shù)據(jù)庫(kù)的 簡(jiǎn)單的 圖形化的界面 它的速度極快 并且它的拷貝很小(可以放在一張軟盤(pán)上)

lishixinzhi/Article/program/SQL/201404/30537

MySQL數(shù)據(jù)分析常用函數(shù)方法

執(zhí)行順序：

適用結(jié)構(gòu)相同的表聯(lián)結(jié)成一張大表

內(nèi)連接：返回兩個(gè)表共同的行

左連接：以表 1 為基礎(chǔ)，匹配表 2 的相同行

右連接：以表 2 為基礎(chǔ)，匹配表 1 的相同行

全連接：返回全部數(shù)據(jù)，可以理解為左連接和右連接的結(jié)合

mysql 沒(méi)有全連接

常用于組內(nèi)排序，具體寫(xiě)法如下

窗口函數(shù)可以用 rank 相關(guān)函數(shù)或者聚合函數(shù)

當(dāng)前日期+時(shí)間（date + time）函數(shù)：now()

當(dāng)前時(shí)間戳函數(shù)：current_timestamp()

日期或時(shí)間轉(zhuǎn)換為字符串 函數(shù)：date_format(date,format), time_format(time,format)

lower(str)：將字符串參數(shù)值轉(zhuǎn)換為全小寫(xiě)字母后返回

upper(str)：將字符串參數(shù)值轉(zhuǎn)換為全大寫(xiě)字母后返回

concat(str1, str2,...)：將多個(gè)字符串參數(shù)首尾相連后返回

concat_ws(separator,str1,str2,...)：將多個(gè)字符串參數(shù)以給定的分隔符 separator 首尾相連后返回

substr(str,pos)：截取從 pos 位置開(kāi)始到最后的所有 str 字符串

substr(str, pos, len)：截取 str 字符串，從 pos 位置開(kāi)始的 len 個(gè)字符

length(str)：返回字符串的存儲(chǔ)長(zhǎng)度

char_length(str)：返回字符串中的字符個(gè)數(shù)

format(X,D,locale)：以格式 ‘#,###,###.##’ 格式化數(shù)字 X，D 指定小數(shù)位數(shù)，locale 指定國(guó)家語(yǔ)言(默認(rèn)的 locale 為 en_US)

left(str, len)：返回最左邊的len長(zhǎng)度的子串

right(str, len)：返回最右邊的len長(zhǎng)度的子串

ltrim(str)，rtrim(str)：去掉字符串的左邊或右邊的空格

repeat(str, count)：將字符串 str 重復(fù) count 次后返回

reverse(str)：將字符串 str 反轉(zhuǎn)后返回

通俗易懂的學(xué)會(huì)：SQL窗口函數(shù)

mysql format時(shí)間格式化說(shuō)明

MySQL常用字符串函數(shù)

五款常用mysql slow log分析工具的比較

mysql slow log 是用來(lái)記錄執(zhí)行時(shí)間較長(zhǎng)(超過(guò)long_query_time秒)的sql的一種日志工具

啟用 slow log

有兩種啟用方式:

在f 里 通過(guò) log slow queries[=file_name]

在mysqld進(jìn)程啟動(dòng)時(shí) 指定–log slow queries[=file_name]選項(xiàng)

比較的五款常用工具

mysqldumpslow mysqlsla myprofi mysql explain slow log mysqllogfilter

mysqldumpslow mysql官方提供的慢查詢?nèi)罩痉治龉ぞ?輸出圖表如下:

主要功能是 統(tǒng)計(jì)不同慢sql的

出現(xiàn)次數(shù)(Count)

執(zhí)行最長(zhǎng)時(shí)間(Time)

累計(jì)總耗費(fèi)時(shí)間(Time)

等待鎖的時(shí)間(Lock)

發(fā)送給客戶端的行總數(shù)(Rows)

掃描的行總數(shù)(Rows)

用戶以及sql語(yǔ)句本身(抽象了一下格式 比如 limit 用 limit N N 表示)

mysqlsla 推出的一款日志分析工具(該網(wǎng)站還維護(hù)了 mysqlreport mysqlidxc 等比較實(shí)用的mysql工具)

整體來(lái)說(shuō) 功能非常強(qiáng)大 數(shù)據(jù)報(bào)表 非常有利于分析慢查詢的原因 包括執(zhí)行頻率 數(shù)據(jù)量 查詢消耗等

格式說(shuō)明如下:

總查詢次數(shù) (queries total) 去重后的sql數(shù)量 (unique)

輸出報(bào)表的內(nèi)容排序(sorted by)

最重大的慢sql統(tǒng)計(jì)信息 包括 平均執(zhí)行時(shí)間 等待鎖時(shí)間 結(jié)果行的總數(shù) 掃描的行總數(shù)

Count sql的執(zhí)行次數(shù)及占總的slow log數(shù)量的百分比

Time 執(zhí)行時(shí)間 包括總時(shí)間 平均時(shí)間 最小 最大時(shí)間 時(shí)間占到總慢sql時(shí)間的百分比

% of Time 去除最快和最慢的sql 覆蓋率占 %的sql的執(zhí)行時(shí)間

Lock Time 等待鎖的時(shí)間

% of Lock %的慢sql等待鎖時(shí)間

Rows sent 結(jié)果行統(tǒng)計(jì)數(shù)量 包括平均 最小 最大數(shù)量

Rows examined 掃描的行數(shù)量

Database 屬于哪個(gè)數(shù)據(jù)庫(kù)

Users 哪個(gè)用戶 IP 占到所有用戶執(zhí)行的sql百分比

Query abstract 抽象后的sql語(yǔ)句

Query sample sql語(yǔ)句

除了以上的輸出 官方還提供了很多定制化參數(shù) 是一款不可多得的好工具

mysql explain slow log 德國(guó)人寫(xiě)的一個(gè)perl腳本

功能上有點(diǎn)瑕疵 不僅把所有的 slow log 打印到屏幕上 而且統(tǒng)計(jì)也只有數(shù)量而已 不推薦使用

mysql log filter google code上找到的一個(gè)分析工具 提供了 python 和 php 兩種可執(zhí)行的腳本

log filter/

功能上比官方的mysqldumpslow 多了查詢時(shí)間的統(tǒng)計(jì)信息(平均 最大 累計(jì)) 其他功能都與 mysqldumpslow類(lèi)似

特色功能除了統(tǒng)計(jì)信息外 還針對(duì)輸出內(nèi)容做了排版和格式化 保證整體輸出的簡(jiǎn)潔 喜歡簡(jiǎn)潔報(bào)表的朋友 推薦使用一下

myprofi 純php寫(xiě)的一個(gè)開(kāi)源分析工具 項(xiàng)目在 sourcefe 上

功能上 列出了總的慢查詢次數(shù)和類(lèi)型 去重后的sql語(yǔ)句 執(zhí)行次數(shù)及其占總的slow log數(shù)量的百分比

從整體輸出樣式來(lái)看 比mysql log filter還要簡(jiǎn)潔 省去了很多不必要的內(nèi)容 對(duì)于只想看sql語(yǔ)句及執(zhí)行次數(shù)的用戶來(lái)說(shuō) 比較推薦

總結(jié)

工具/功能 一般統(tǒng)計(jì)信息 高級(jí)統(tǒng)計(jì)信息 腳本 優(yōu)勢(shì) mysqldumpslow 支持 不支持 perl mysql官方自帶 mysqlsla 支持 支持 perl 功能強(qiáng)大 數(shù)據(jù)報(bào)表齊全 定制化能力強(qiáng) mysql explain slow log 支持 不支持 perl 無(wú) mysql log filter 支持 部分支持 python or php 不失功能的前提下 保持輸出簡(jiǎn)潔 myprofi 支持 不支持 php 非常精簡(jiǎn)

lishixinzhi/Article/program/MySQL/201311/29428

MySQL數(shù)據(jù)庫(kù)服務(wù)器逐漸變慢 該怎么分析與解決

我們先來(lái)看第一個(gè)階段，MySQL慢的診斷思路，一般我們會(huì)從三個(gè)方向來(lái)做：

第一個(gè)方向是MySQL內(nèi)部的觀測(cè)

第二個(gè)方向是外部資源的觀測(cè)

第三個(gè)方向是外部需求的改造

1.1 MySQL 內(nèi)部觀測(cè)

我們來(lái)看MySQL內(nèi)部的觀測(cè)，常用的觀測(cè)手段是這樣的，從上往下看，第一部分是Processlist，看一下哪個(gè)SQL壓力不太正常，第二步是explain，解釋一下它的執(zhí)行計(jì)劃，第三步我們要做Profilling，如果這個(gè)SQL能再執(zhí)行一次的話, 就做一個(gè)Profilling，然后高級(jí)的DBA會(huì)直接動(dòng)用performance_schema ，MySQL 5.7 以后直接動(dòng)用sys_schema，sys_schema是一個(gè)視圖，里面有便捷的各類(lèi)信息，幫助大家來(lái)診斷性能。再高級(jí)一點(diǎn)，我們會(huì)動(dòng)用innodb_metrics進(jìn)行一個(gè)對(duì)引擎的診斷。

除了這些手段以外，大家還提出了一些亂七八糟的手段，我就不列在這了，這些是常規(guī)的一個(gè)MySQL的內(nèi)部的狀態(tài)觀測(cè)的思路。除了這些以外，MySQL還陸陸續(xù)續(xù)提供了一些暴露自己狀態(tài)的方案，但是這些方案并沒(méi)有在實(shí)踐中形成套路，原因是學(xué)習(xí)成本比較高。

1.2 外部資源觀測(cè)

外部資源觀測(cè)這部分，我引用了一篇文章，這篇文章的二維碼我貼在上面了。這篇文章是國(guó)外的一個(gè)神寫(xiě)的，標(biāo)題是：60秒的快速巡檢，我們來(lái)看一下它在60秒之內(nèi)對(duì)服務(wù)器到底做了一個(gè)什么樣的巡檢。一共十條命令，這是前五條，我們一條一條來(lái)看。

1.uptime，uptime告訴我們這個(gè)機(jī)器活了多久，以及它的平均的負(fù)載是多少。

2.dmesg -T | tail，告訴我們系統(tǒng)日志里邊有沒(méi)有什么報(bào)錯(cuò)。

3.vmstat 1，告訴我們虛擬內(nèi)存的狀態(tài)，頁(yè)的換進(jìn)換出有沒(méi)有問(wèn)題，swap有沒(méi)有使用。

4. mpstat -P ALL，告訴我們CPU壓力在各個(gè)核上是不是均勻的。

5.pidstat 1，告訴我們各個(gè)進(jìn)程的對(duì)資源的占用大概是什么樣子。

我們來(lái)看一下后五條：

首先是iostat-xz 1，查看IO的問(wèn)題，然后是free-m內(nèi)存使用率，之后兩個(gè)sar，按設(shè)備網(wǎng)卡設(shè)備的維度，看一下網(wǎng)絡(luò)的消耗狀態(tài)，以及總體看TCP的使用率和錯(cuò)誤率是多少。最后一條命令top，看一下大概的進(jìn)程和線程的問(wèn)題。

這個(gè)就是對(duì)于外部資源的診斷，這十條命令揭示了應(yīng)該去診斷哪些外部資源。

1.3 外部需求改造

第三個(gè)診斷思路是外部的需求改造，我在這里引用了一篇文檔，這篇文檔是MySQL的官方文檔中的一章，這一章叫Examples of Common Queries，文檔中介紹了常規(guī)的SQL怎么寫(xiě), 給出了一些例子。文章的鏈接二維碼在slide上。

我們來(lái)看一下它其中提到的一個(gè)例子。

它做的事情是從一個(gè)表里邊去選取，這張表有三列，article、dealer、price，選取每個(gè)作者的最貴的商品列在結(jié)果集中，這是它的最原始的SQL，非常符合業(yè)務(wù)的寫(xiě)法，但是它是個(gè)關(guān)聯(lián)子查詢。

關(guān)聯(lián)子查詢成本是很貴的，所以上面的文檔會(huì)教你快速地把它轉(zhuǎn)成一個(gè)非關(guān)聯(lián)子查詢，大家可以看到中間的子查詢和外邊的查詢之間是沒(méi)有關(guān)聯(lián)性的。

第三步，會(huì)教大家直接把子查詢拿掉，然后轉(zhuǎn)成這樣一個(gè)SQL，這個(gè)就叫業(yè)務(wù)改造，前后三個(gè)SQL的成本都不一樣，把關(guān)聯(lián)子查詢拆掉的成本，拆掉以后SQL會(huì)跑得非常好，但這個(gè)SQL已經(jīng)不能良好表義了，只有在診斷到SQL成本比較高的情況下才建議大家使用這種方式。

為什么它能夠把一個(gè)關(guān)聯(lián)子查詢拆掉呢？

這背后的原理是關(guān)系代數(shù)，所有的SQL都可以被表達(dá)成等價(jià)的關(guān)系代數(shù)式，關(guān)系代數(shù)式之間有等價(jià)關(guān)系，這個(gè)等價(jià)關(guān)系通過(guò)變換可以把關(guān)聯(lián)子查詢拆掉。

上面的這篇文檔是一個(gè)大學(xué)的教材，它從頭教了關(guān)于代數(shù)和SQL之間的關(guān)系。然后一步步推導(dǎo)怎么去簡(jiǎn)化這句SQL。

第一，MySQL本身提供了很多命令來(lái)觀察MySQL自身的各類(lèi)狀態(tài)，大家從上往下檢一般能檢到SQL的問(wèn)題或者服務(wù)器的問(wèn)題。

第二，從服務(wù)器的角度，我們從巡檢的腳本角度入手，服務(wù)器的資源就這幾種，觀測(cè)手法也就那么幾種，我們把服務(wù)器的資源全部都觀察一圈就可以了。

第三，如果實(shí)在搞不定，需求方一定要按照數(shù)據(jù)庫(kù)容易接受的方式去寫(xiě)SQL，這個(gè)成本會(huì)下降的非?？欤@個(gè)是常規(guī)的MySQL慢的診斷思路。

分享題目：mysql怎么找分析 mysql有分析函數(shù)嗎
當(dāng)前地址：http://aaarwkj.com/article0/hhhgoo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供標(biāo)簽優(yōu)化、外貿(mào)網(wǎng)站建設(shè)、定制網(wǎng)站、移動(dòng)網(wǎng)站建設(shè)、自適應(yīng)網(wǎng)站、企業(yè)建站

廣告

聲明：本網(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)