之前看了餓了么團(tuán)隊(duì)寫的一篇博客：等等！這兩個(gè) Spring-RabbitMQ 的坑我們已經(jīng)替你踩了虫蝶。深受啟發(fā)章咧，一定要取個(gè)能吸引讀者眼球的標(biāo)題，當(dāng)然除了響當(dāng)當(dāng)?shù)臉?biāo)題以外能真，內(nèi)容也要是干貨赁严。為什么會(huì)想取這樣一個(gè)標(biāo)題，因?yàn)榭戳死碚撋系穆樵儍?yōu)化粉铐，今天Ｌ墼肌！蝙泼！終于在生產(chǎn)上實(shí)戰(zhàn)了

(一)慢sql一

問題發(fā)現(xiàn)

將應(yīng)用發(fā)布到生產(chǎn)環(huán)境后程剥，前端頁面請求后臺(tái)API返回?cái)?shù)據(jù)，發(fā)現(xiàn)至少需要6s踱承。查看到慢sql：

慢sql定位.png

復(fù)現(xiàn)慢sql

執(zhí)行sql：

select count(*) from sync_block_data
where unix_timestamp(sync_dt) >= 1539101010
AND unix_timestamp(sync_dt) <= 1539705810

查看耗時(shí)：

慢查詢耗時(shí).png

一共耗時(shí)為2658ms
查看執(zhí)行計(jì)劃：

explain select count(*) from sync_block_data
where unix_timestamp(sync_dt) >= 1539101010
AND unix_timestamp(sync_dt) <= 1539705810

執(zhí)行計(jì)劃結(jié)果：

慢查詢執(zhí)行計(jì)劃.png

優(yōu)化慢sql一

sync_dt的類型為datetime類型倡缠。換另外一種sql寫法哨免，直接通過比較日期而不是通過時(shí)間戳進(jìn)行比較茎活。將sql中的時(shí)間戳轉(zhuǎn)化為日期，分別為2018-10-10 00:03:30和2018-10-17 00:03:30
執(zhí)行sql：

select count(*) from sync_block_data
where sync_dt >= "2018-10-10 00:03:30"
AND sync_dt <= "2018-10-17 00:03:30"

查看耗時(shí)：

快查詢耗時(shí).png

一共耗時(shí)419毫秒琢唾，和慢查詢相比速度提升六倍多
查看執(zhí)行計(jì)劃：

explain select count(*) from sync_block_data
where sync_dt >= "2018-10-10 00:03:30"
AND sync_dt <= "2018-10-17 00:03:30"

執(zhí)行計(jì)劃結(jié)果：

快查詢執(zhí)行計(jì)劃.png

訪問頁面载荔，優(yōu)化完成后請求時(shí)間平均為900毫秒

image.png

執(zhí)行計(jì)劃中慢查詢和快查詢唯一的區(qū)別就是type不一樣：慢查詢中type為index，快查詢中type為range采桃。

優(yōu)化慢查詢二

這條sql的業(yè)務(wù)邏輯為統(tǒng)計(jì)出最近七天該表的數(shù)據(jù)量懒熙，可以去掉右邊的小于等于
執(zhí)行sql：

select count(*) from sync_block_data
where sync_dt >= "2018-10-10 00:03:30"

查看耗時(shí)：

image.png

一共耗時(shí)275毫秒，又將查詢時(shí)間減少了一半
查看執(zhí)行計(jì)劃：

explain select count(*) from sync_block_data
where sync_dt >= "2018-10-10 00:03:30"

執(zhí)行計(jì)劃結(jié)果：

image.png

type仍是range普办。但是通過少比較一次將查詢速度提高一倍

優(yōu)化慢查詢?nèi)?/h3>

新建一個(gè)bigint類型字段sync_dt_long存儲(chǔ)sync_dt的毫秒值工扎，并在sync_dt_long字段上建立索引
測試環(huán)境下：
優(yōu)化慢查詢二sql

select count(*) from copy_sync_block_data
where sync_dt >="2018-10-10 13:15:02"

耗時(shí)為34毫秒
優(yōu)化慢查詢?nèi)齭ql

select count(*) from copy_sync_block_data
where sync_dt_long >= 1539148502916

耗時(shí)為22毫秒
測試環(huán)境中速度提升10毫秒左右

優(yōu)化慢查詢?nèi)齭ql測試小結(jié)：在InnoDB存儲(chǔ)引擎下，比較bigint的效率高于datetime
完成三步優(yōu)化以后生產(chǎn)環(huán)境中請求耗時(shí)：

image.png

速度又快了200毫秒左右衔蹲。通過給查詢的數(shù)據(jù)加10s緩存肢娘，響應(yīng)速度最快平均為20s

explain使用介紹

通過explain，可以查看sql語句的執(zhí)行情況（比如查詢的表，使用的索引以及mysql在表中找到所需行的方式等）
用explain查詢mysql查詢計(jì)劃的輸出參數(shù)有:

重點(diǎn)關(guān)注type颤专，type類型的不同竟然導(dǎo)致性能差六倍ＥΩ纭！栖秕！

type顯示的是訪問類型春塌，是較為重要的一個(gè)指標(biāo)，結(jié)果值從好到壞依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 簇捍，一般來說只壳，得保證查詢至少達(dá)到range級(jí)別，最好能達(dá)到ref暑塑。

出現(xiàn)慢查詢的原因

在where子句中使用了函數(shù)操作
出現(xiàn)慢查詢的sql語句中使用了unix_timestamp函數(shù)統(tǒng)計(jì)出自'1970-01-01 00:00:00'的到當(dāng)前時(shí)間的秒數(shù)差衣厘。導(dǎo)致索引全掃描統(tǒng)計(jì)出近七天的數(shù)據(jù)量的

解決方案

盡量避免在where子句中對字段進(jìn)行函數(shù)操作如蚜，這將導(dǎo)致存儲(chǔ)引擎放棄使用索引而進(jìn)行全表掃描压恒。對于需要計(jì)算的值最好通過程序計(jì)算好傳入而不是在sql語句中做計(jì)算影暴，比如這個(gè)sql中我們將當(dāng)前的日期和七天前的日期計(jì)算好傳入

后記

這個(gè)問題當(dāng)時(shí)在測試環(huán)境沒有發(fā)現(xiàn)，測試環(huán)境的請求速度還是可以的探赫。沒有被發(fā)現(xiàn)可以歸結(jié)為數(shù)據(jù)量型宙。生產(chǎn)數(shù)據(jù)量為百萬級(jí)別，測試環(huán)境數(shù)據(jù)量為萬級(jí)伦吠，數(shù)據(jù)量差50倍妆兑，數(shù)據(jù)量的增大把慢查詢的問題也放大了魂拦。

(二)慢sql二

因?yàn)榫€上出現(xiàn)了很明顯的請求響應(yīng)慢的問題，又去看了項(xiàng)目中的其他sql搁嗓，發(fā)現(xiàn)還有sql執(zhí)行的效率比較低

復(fù)現(xiàn)慢sql

執(zhí)行sql

select FROM_UNIXTIME(copyright_apply_time/1000,'%Y-%m-%d') point,count(1) nums
from resource_info where copyright_apply_time >= 1539336488355 and copyright_apply_time <= 1539941288355 group by point

查看耗時(shí)：

image.png

耗時(shí)為1123毫秒
查看執(zhí)行計(jì)劃：

explain select FROM_UNIXTIME(copyright_apply_time/1000,'%Y-%m-%d') point,count(1) nums
from resource_info where copyright_apply_time >= 1539336488355 and copyright_apply_time <= 1539941288355 group by point

執(zhí)行計(jì)劃結(jié)果：

image.png

索引是命中了芯勘，但是extra字段中出現(xiàn)了Using temporary和Using filesort

優(yōu)化慢sql一

group by實(shí)質(zhì)是先排序后分組，也就是分組之前必排序腺逛。通過分組的時(shí)候禁止排序優(yōu)化sql
執(zhí)行sql：

select FROM_UNIXTIME(copyright_apply_time/1000,'%Y-%m-%d') point,count(1) nums
from resource_info where copyright_apply_time >= 1539336488355 and copyright_apply_time <= 1539941288355 group by point order by null

查看耗時(shí)：

image.png

一共耗時(shí)1068毫秒荷愕，提高100毫秒左右，效果并不是特別明顯
查看執(zhí)行計(jì)劃：

image.png

優(yōu)化慢sql二

慢查詢的sql業(yè)務(wù)邏輯為根據(jù)時(shí)間段分類統(tǒng)計(jì)出條件范圍內(nèi)各個(gè)時(shí)間段的數(shù)量
比如給定的條件范圍為2018-10-20~2018-10-27的時(shí)間戳，這條sql就會(huì)統(tǒng)計(jì)出2018-10-20～2018-10-27每天的數(shù)據(jù)增量∽旅保現(xiàn)在優(yōu)化成一天一天查玉罐，分別查七次數(shù)據(jù)，去掉分組操作

select FROM_UNIXTIME(copyright_apply_time/1000,'%Y-%m-%d') point,count(1) nums
from resource_info where copyright_apply_time >= 1539855067355 and copyright_apply_time <= 1539941467355 

查看耗時(shí)：

image.png

耗時(shí)為38毫秒潘拨，即使查7次所用時(shí)間也比1123毫秒少
查看執(zhí)行計(jì)劃：

image.png

就這樣第一次經(jīng)歷了真正的慢查詢以及慢查詢優(yōu)化，終于理論和實(shí)踐相結(jié)合了