mysql慢查詢怎么排查 mysql 查詢慢

MySQL刪除千萬級數(shù)據(jù)量導(dǎo)致的慢查詢優(yōu)化

有人刪了千萬級的數(shù)據(jù)，結(jié)果導(dǎo)致頻繁的慢查詢。

創(chuàng)新互聯(lián)專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于網(wǎng)站設(shè)計制作、成都做網(wǎng)站、西湖網(wǎng)絡(luò)推廣、小程序制作、西湖網(wǎng)絡(luò)營銷、西湖企業(yè)策劃、西湖品牌公關(guān)、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運營等，從售前售中售后，我們都將竭誠為您服務(wù)，您的肯定，是我們最大的嘉獎；創(chuàng)新互聯(lián)為所有大學(xué)生創(chuàng)業(yè)者提供西湖建站搭建服務(wù)，24小時服務(wù)熱線：13518219792，官方網(wǎng)址：www.cdcxhl.com

線上收到大量慢查詢告警，于是檢查慢查詢的SQL，發(fā)現(xiàn)不是啥復(fù)雜SQL，這些SQL主要針對一個表，基本都是單行查詢，看起來應(yīng)該不會有慢查詢。這種SQL基本上都是直接根據(jù)索引查找出來的，性能應(yīng)該極高。

是否可能慢查詢不是SQL問題，而是MySQL生產(chǎn)服務(wù)器的問題？特殊情況下，MySQL出現(xiàn)慢查詢還真不是SQL問題，而是他自己生產(chǎn)服務(wù)器的負(fù)載太高，導(dǎo)致SQL語句執(zhí)行慢。比如現(xiàn)在MySQL服務(wù)器的

磁盤I/O負(fù)載高，每秒執(zhí)行大量高負(fù)載的隨機I/O，但磁盤本身每秒能執(zhí)行的隨機I/O有限，導(dǎo)致正常SQL在磁盤執(zhí)行時，若跑一些隨機IO，你的磁盤太忙，顧不上你了，導(dǎo)致你本來很快的一個SQL，要等很久才能執(zhí)行完畢，這時就可能導(dǎo)致正常SQL也變成慢查詢。

也許網(wǎng)絡(luò)負(fù)載高，導(dǎo)致你一個SQL語句要發(fā)到MySQL，光是等待獲取一個和MySQL的連接，都很難，要等很久或MySQL自己網(wǎng)絡(luò)負(fù)載太高，帶寬打滿，帶寬打滿后，你一個SQL也許執(zhí)行很快，但其查出來的數(shù)據(jù)返回給你，網(wǎng)絡(luò)都送不出去，也會變成慢查詢。

若CPU負(fù)載過高，也會導(dǎo)致CPU過于繁忙去執(zhí)行別的任務(wù)，沒時間執(zhí)行你的SQL。

所以慢查詢不一定是SQL本身導(dǎo)致，若覺得SQL不應(yīng)該會慢查詢，結(jié)果他那個時間段跑這個SQL 就是慢，應(yīng)排查當(dāng)時MySQL服務(wù)器的負(fù)載，尤其看看磁盤、網(wǎng)絡(luò)及 CPU 的負(fù)載，是否正常。

當(dāng)某個離線作業(yè)瞬間大批量把數(shù)據(jù)往MySQL里灌入的時，他一瞬間服務(wù)器磁盤、網(wǎng)絡(luò)以及CPU的負(fù)載會超高。

此時你一個正常SQL執(zhí)行下去，短時間內(nèi)一定會慢查詢，類似問題，優(yōu)化手段更多是控制你導(dǎo)致MySQL負(fù)載過高的那些行為，比如灌入大量數(shù)據(jù)，最好在業(yè)務(wù)低峰期灌入，別影響高峰期的線上系統(tǒng)運行。

但看了下MySQL服務(wù)器的磁盤、網(wǎng)絡(luò)以及CPU負(fù)載，一切正常，似乎也不是這問題導(dǎo)致?？雌饋頍o解了？

慢 SQL 的頭兩步排查手段：

這兩種辦法都不奏效之后，第三步：用MySQL pro?lling工具去細(xì)致的分析SQL語句的執(zhí)行過程和耗時。

這個工具可以對SQL語句的執(zhí)行耗時進行非常深入和細(xì)致的分析

打開pro?ling，使用

接著MySQL就會自動記錄查詢語句的pro?ling信息。此時若執(zhí)行show pro?les，就會給你列出各種查詢語句的pro?ling信息，會記錄下來每個查詢語句的query id，所以你要針對你需要分析的query找到對他的query id，我們當(dāng)時就是針對慢查詢的那個SQL語句找到了query id。

然后針對單個查詢語句，看其pro?ling信息，使用show pro?le cpu, block io for query xx，這里的xx是數(shù)字，此時就可以看到具體的pro?le信息。

除了cpu以及block io以外，還能指定去看這個SQL語句執(zhí)行時候的其他各項負(fù)載和耗時。

會給你展示出來SQL語句執(zhí)行時候的各種耗時，比如磁盤IO的耗時，CPU等待耗時，發(fā)送數(shù)據(jù)耗時，拷貝數(shù)據(jù)到臨時表的耗時等，SQL執(zhí)行過程中的各種耗時都會展示。

檢查該SQL語句的pro?ling信息后，發(fā)現(xiàn)問題，其Sending Data耗時最高，幾乎使用1s，占據(jù)SQL執(zhí)行耗時的99%！其他環(huán)節(jié)耗時低可以理解，畢竟這種簡單SQL執(zhí)行速度真的很快，基本就是10ms級別，結(jié)果跑成1s，那肯定Sending Data就是問題根源！

這Sending Data在干啥呢？

MySQL官方釋義：為一個SELECT語句讀取和處理數(shù)據(jù)行，同時發(fā)送數(shù)據(jù)給客戶端的過程，簡單來說就是為你的SELECT語句把數(shù)據(jù)讀出來，同時發(fā)送給客戶端。

但這過程為啥這么慢？pro?ling確實是提供給我們更多的線索了，但似乎還是沒法解決問題。但已經(jīng)捕獲到異常關(guān)鍵點，就是Sending Data的耗時很高！

接著：

看innodb存儲引擎的一些狀態(tài)，此時發(fā)現(xiàn)一個奇怪的指標(biāo)：history list length，值特別高，達(dá)到上萬。

MVCC就是多個事務(wù)在對同一個數(shù)據(jù)， 有人寫，有人讀，此時可以有多種隔離級別，對一個數(shù)據(jù)有個多版本快照鏈條，才能實現(xiàn)MVCC和各種隔離級別。

所以當(dāng)你有大量事務(wù)執(zhí)行時，就會構(gòu)建這種undo多版本快照鏈條，此時history list length就會很高。然后在事務(wù)提交后，會有一個多版本快照鏈條的自動purge清理機制，清理了，該值就會降低。一般該值不應(yīng)過高，所以注意到第二個線索：history list length過高，即大量的undo多版本鏈條數(shù)據(jù)沒有清理。推測可能有的事務(wù)長時間運行，所以其多版本快照不能被purge清理，進而導(dǎo)致history list length過高。

經(jīng)過這倆線索推測，在大量簡單SQL變成慢查詢時，SQL因為Sending Data環(huán)節(jié)異常，耗時過高；同時此時出現(xiàn)一些長事務(wù)長時間運行，大量的頻繁更新數(shù)據(jù)，導(dǎo)致有大量undo多版本快照鏈條，還無法purge清理。

因為發(fā)現(xiàn)有大量的更新語句在活躍，而且有那種長期活躍的長事務(wù)一直在跑而沒有結(jié)束，問了下系統(tǒng)負(fù)責(zé)人，在后臺跑了個定時任務(wù)：他居然開了一個事務(wù)，然后在一個事務(wù)里刪除上千萬數(shù)據(jù)，導(dǎo)致該事務(wù)一直在運行。

這種長事務(wù)的運行會導(dǎo)致你刪除時，僅只是對數(shù)據(jù)加了一個刪除標(biāo)記，事實上并沒有徹底刪除。此時你若和長事務(wù)同時運行的其它事務(wù)里再查詢，他在查詢時可能會把那上千萬被標(biāo)記為刪除的數(shù)據(jù)都掃描一遍。因為每次掃描到一批數(shù)據(jù)，都發(fā)現(xiàn)標(biāo)記為刪除了，接著就會再繼續(xù)往下掃描，所以才導(dǎo)致一些查詢語句很慢。

那為何你啟動一個事務(wù)，在事務(wù)里查詢，憑什么就要去掃描之前那個長事務(wù)標(biāo)記為刪除狀態(tài)的上千萬的垃圾數(shù)據(jù)？講道理，那些數(shù)據(jù)都被刪了，跟你沒關(guān)系了呀，你可以不去掃描他們 嘛！

而問題癥結(jié)在于，那個 刪除千萬級數(shù)據(jù)的事務(wù)是個長事務(wù) ！即當(dāng)你啟動新事務(wù)查詢時，那個刪除千萬級數(shù)據(jù)的長事務(wù)一直在運行，它是活躍的！結(jié)合MVCC的Read View機制，當(dāng)你啟動一個新事務(wù)查詢時，會生成一個Read View。你的新事務(wù)查詢時，會根據(jù)ReadView去判斷哪些數(shù)據(jù)可見及可見的數(shù)據(jù)版本號，因為每個數(shù)據(jù)都有個版本鏈條，有時你能可見的僅是這個數(shù)據(jù)的一個 歷史 版本。

所以正是因為該長事務(wù)一直在運行，還在刪除大量數(shù)據(jù)，而且這些數(shù)據(jù)僅是邏輯刪除，所以此時你新開事務(wù)的查詢還是會讀到所有邏輯刪除數(shù)據(jù)，也就會出現(xiàn)千萬級的數(shù)據(jù)掃描，導(dǎo)致了慢查詢！

所以禁止在業(yè)務(wù)高峰期運行那種刪除大量數(shù)據(jù)的語句，因為這可能導(dǎo)致一些正常的SQL都變慢查詢，因為那些SQL也許會不斷掃描你標(biāo)記為刪除的大量數(shù)據(jù)，好不容易掃描到一批數(shù)據(jù)，結(jié)果發(fā)現(xiàn)是標(biāo)記為刪除的，于是繼續(xù)掃描下去，導(dǎo)致慢查詢！

直接kill那個正在刪除千萬級數(shù)據(jù)的長事務(wù)，所有SQL很快恢復(fù)正常。此后，大量數(shù)據(jù)清理全部放在凌晨執(zhí)行，那個時候就沒什么人使用系統(tǒng)了，所以查詢也很少。

mysql如何找出慢sql

long_query_time 參數(shù)的查看

默認(rèn)是10秒，10秒以上的sql會記錄?？蛇M行值的修改，

long_query_time 默認(rèn)不開啟 ，如果不是需要進行開始調(diào)優(yōu)，一般不建議開啟此參數(shù)。

永久開啟：

在my點吸煙 f中的

1.查看慢查詢的時長

看此圖默認(rèn)10秒，是大于10秒，不等于10秒。

2.修改此時長

臨時修改，重啟mysql后失效，修改后需要新開連接才能查詢到

永久在配制文件中修改

查看慢sql個數(shù)

將所有沒有使用帶索引的查詢語句全部寫到慢查詢?nèi)罩局?/p>

設(shè)置沒帶索引的慢sql進行記錄

最后匯總my點吸煙 f配制

Mysql中如何查看慢查詢以及查看線程

1，slow_query_log

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

2，long_query_time

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

3，slow_query_log_file

記錄日志的文件名。

4，log_queries_not_using_indexes

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

二、檢測mysql中sql語句的效率的方法

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

（1）、Windows下開啟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點吸煙 f找到[mysqld]下面加上

代碼如下

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

long_query_time=2

說明

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

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

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

2.show processlist 命令

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

各列的含義和用途：

ID列

一個標(biāo)識，你要kill一個語句的時候很有用，用命令殺掉此查詢 /*/mysqladmin kill 進程號。

user列

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

host列

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

db列

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

command列

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

time列

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

state列

顯示使用當(dāng)前連接的sql語句的狀態(tài)，很重要的列，后續(xù)會有所有的狀態(tài)的描述，請注意，state只是語句執(zhí)行中的某一個狀態(tài)，一個

sql語句，以查詢?yōu)槔赡苄枰?jīng)過copying to tmp table，Sorting result，Sending

data等狀態(tài)才可以完成

info列

顯示這個sql語句，因為長度有限，所以長的sql語句就顯示不全，但是一個判斷問題語句的重要依據(jù)。

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

Checking table

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

Closing tables

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

Connect Out

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

Copying to tmp table on disk

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

Creating tmp table

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

deleting from main table

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

deleting from reference tables

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

Flushing tables

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

Killed

發(fā)送了一個kill請求給某線程，那么這個線程將會檢查kill標(biāo)志位，同時會放棄下一個kill請求。MySQL會在每次的主循環(huán)中檢查kill標(biāo)志

位，不過有些情況下該線程可能會過一小段才能死掉。如果該線程程被其他線程鎖住了，那么kill請求會在鎖釋放時馬上生效。

Locked

被其他查詢鎖住了。

Sending data

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

Sorting for group

正在為GROUP BY做排序。

Sorting for order

正在為ORDER BY做排序。

Opening tables

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

Removing duplicates

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

Reopen table

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

Repair by sorting

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

Repair with keycache

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

Searching rows for update

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

Sleeping

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

System lock

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

Upgrading lock

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

Updating

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

User Lock

正在等待GET_LOCK()。

Waiting for tables

該線程得到通知，數(shù)據(jù)表結(jié)構(gòu)已經(jīng)被修改了，需要重新打開數(shù)據(jù)表以取得新的結(jié)構(gòu)。然后，為了能的重新打開數(shù)據(jù)表，必須等到所有其他線程關(guān)閉這個表。以下幾

種情況下會產(chǎn)生這個通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR

TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。

waiting for handler insert

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

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

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

網(wǎng)頁名稱：mysql慢查詢怎么排查 mysql 查詢慢
當(dāng)前網(wǎng)址：http://m.jiaotiyi.com/article/ddggcgj.html