深入分析MySQL Sending data查詢慢問題

通過一個(gè)實(shí)例給大家分享了MySQL Sending data表查詢慢問題解決辦法。

最近在代碼優(yōu)化中，發(fā)現(xiàn)了一條sql語句非常的慢，于是就用各種方法進(jìn)行排查，最后終于找到了原因。

一、事故現(xiàn)場(chǎng)

SELECT og.goods_barcode, og.color_id, og.size_id, SUM(og.goods_number) AS sold_number FROM order o 
LEFT JOIN order_goods og ON o.order_id = og.order_id WHERE o.is_send = 0 AND o.shipping_status = 0 
AND o.create_time > '2017-10-10 00:00:00' AND o.ck_id = 1 AND og.goods_id = 13421 AND o.is_separate = 1 AND o.order_status IN (0, 1) AND og.is_separate = 1 
GROUP BY og.color_id, og.size_id

上面的這條語句是一個(gè)聯(lián)表分組查詢語句。

執(zhí)行結(jié)果：

我們可以看到，這條語句用了 1.300 秒, 而 Sending data 就用了 1.28 秒，占用了將近 99% 的時(shí)間，所以，我們對(duì)這個(gè)進(jìn)行優(yōu)化。

怎么優(yōu)化呢？

二、SQL語句分析三板斧

1、explain分析

對(duì)上邊的語句進(jìn)行 explain 分析：

explain SELECT og.goods_barcode, og.color_id, og.size_id, SUM(og.goods_number) AS sold_number FROM order o 
LEFT JOIN order_goods og ON o.order_id = og.order_id WHERE o.is_send = 0 AND o.shipping_status = 0 
AND o.create_time > '2017-10-10 00:00:00' AND o.ck_id = 1 AND og.goods_id = 13421 AND o.is_separate = 1 AND o.order_status IN (0, 1) AND og.is_separate = 1 
GROUP BY og.color_id, og.size_id

執(zhí)行結(jié)果：

通過explain, 我們可以看到上邊的語句，有用到索引key。

2、show processlist

explain看不出問題，那到底慢在哪里呢？

于是想到了使用 show processlist 查看sql語句執(zhí)行狀態(tài)，查詢結(jié)果如下：

發(fā)現(xiàn)很長(zhǎng)一段時(shí)間，查詢都處在 “Sending data”狀態(tài)

查詢一下“Sending data”狀態(tài)的含義，原來這個(gè)狀態(tài)的名稱很具有誤導(dǎo)性，所謂的“Sending data”并不是單純的發(fā)送數(shù)據(jù)，而是包括“收集 + 發(fā)送 數(shù)據(jù)”。

這里的關(guān)鍵是為什么要收集數(shù)據(jù)，原因在于：mysql使用“索引”完成查詢結(jié)束后，mysql得到了一堆的行id，如果有的列并不在索引中，mysql需要重新到“數(shù)據(jù)行”上將需要返回的數(shù)據(jù)讀取出來返回個(gè)客戶端。

3、show profile

為了進(jìn)一步驗(yàn)證查詢的時(shí)間分布，于是使用了 show profile 命令來查看詳細(xì)的時(shí)間分布

首先打開配置：set profiling=on;

執(zhí)行完查詢后，使用show profiles查看query id;

使用show profile for query query_id查看詳細(xì)信息;

三、排查優(yōu)化

1.排查對(duì)比

經(jīng)過以上步驟，已經(jīng)確定查詢慢是因?yàn)榇罅康臅r(shí)間耗費(fèi)在了Sending data狀態(tài)上，結(jié)合Sending data的定義，將目標(biāo)聚焦在查詢語句的返回列上面

經(jīng)過一 一排查，最后定為到一個(gè)description的列上，這個(gè)列的設(shè)計(jì)為：descriptionvarchar(8000) DEFAULT NULL COMMENT '游戲描述',

于是采取了對(duì)比的方法，看看“不返回description的結(jié)果”如何。show profile的結(jié)果如下：

【解決方法】

找到了問題的根本原因，解決方法也就不難了。有幾種方法：

1）查詢時(shí)去掉description的查詢，但這受限于業(yè)務(wù)的實(shí)現(xiàn)，可能需要業(yè)務(wù)做較大調(diào)整

2）表結(jié)構(gòu)優(yōu)化，將descripion拆分到另外的表，這個(gè)改動(dòng)較大，需要已有業(yè)務(wù)配合修改，且如果業(yè)務(wù)還是要繼續(xù)查詢這個(gè)description的信息，則優(yōu)化后的性能也不會(huì)有很大提升。