現(xiàn)象:列表頁因超時(shí)查不出來東西,使用postman模擬請花費(fèi)40多秒,將sql語句單獨(dú)提出來后查詢速度非常慢,40多秒
先上結(jié)論:
在兩個(gè)表關(guān)聯(lián)字段上建立索引解決此問題，下面的內(nèi)容比這句話爽多了，請繼續(xù)看
表結(jié)構(gòu)如下:
users(用戶)表:id,name
integal_record(分?jǐn)?shù)記錄)表:id,user_id,integal_id
其中,integal_record表的user_id關(guān)聯(lián)著users表的id,業(yè)務(wù)目的是查詢每個(gè)員工閱讀次數(shù)、評(píng)論次數(shù)和積分總和,查詢語句如下:
(里面寫死的值是我從mybatis里扣出來的)
integral_id=1代表著閱讀,integral_id=2代表著評(píng)論
SELECT u.`name`
, IF(SUM(ir.integral_id = 1) > 0, SUM(ir.integral_id = 1), 0) AS 'read'
, IF(SUM(ir.integral_id = 2) > 0, SUM(ir.integral_id = 2), 0) AS 'comment'
, IF(SUM(ir.integral_id = 1) > 0, SUM(ir.integral_id = 1), 0) + IF(SUM(ir.integral_id = 2) > 0, SUM(ir.integral_id = 2), 0) AS count
FROM users u
LEFT JOIN integral_record ir ON ir.user_id = u.id
WHERE u.role_code = 1
AND u.actived = 1
AND u.deleted_at > now()
AND u.network_id = 29
AND u.id NOT IN (
SELECT user_id
FROM roles
WHERE role_item_id = 7
)
GROUP BY u.id
ORDER BY ifnull(SUM(ir.integral_id = 1), 0) + ifnull(SUM(ir.integral_id = 2), 0) DESC, u.pinyin ASC;
共11644條 查詢時(shí)間:27.976s
查詢時(shí)間快半分鐘是無法讓人接受的，從表象的SQL語句來說,首先我們看到有很多SUM函數(shù),因此我們來測試下SUM函數(shù)的計(jì)算時(shí)間，如下查詢每個(gè)人閱讀的累計(jì)次數(shù):
select SUM(integral_id=1) as 'count' from integral_record group by user_id;
共13000條 查詢時(shí)間:0.084s
可以看出來計(jì)算時(shí)間很長和SUM函數(shù)沒什么關(guān)系,再看整個(gè)SQL除了一些SUM函數(shù)外就剩下users表左聯(lián)integal_record表,我們把條件去掉，直接就寫個(gè)左查詢試試
select * from users u LEFT JOIN integral_record mtemp on u.id =mtemp.user_id;
共13001條 查詢時(shí)間:69.919s
amazing，我的users表里有13400條數(shù)據(jù)，關(guān)聯(lián)的integral_record 表里也有13000條數(shù)據(jù)，僅僅做了左連接竟然花費(fèi)了70秒，這肯定是超時(shí)的元兇；所以我們explain一下，看看mysql對(duì)這條數(shù)據(jù)的查詢策略:
EXPLAIN select * from users u LEFT JOIN integral_record mtemp on u.id =mtemp.user_id;
查看結(jié)果:
我們看到type字段的結(jié)果是All，也就是代表全表掃描，那么就好辦了，建立索引即可，其中users表中的id屬于主鍵,策略自增,有默認(rèn)的索引,不在考慮范圍內(nèi)，我們僅需對(duì)integral_record表增加索引即可:
alter table integral_record add index user_id_index(user_id)
繼續(xù)執(zhí)行左聯(lián)語句查看運(yùn)行時(shí)間:
select * from users u LEFT JOIN integral_record mtemp on u.id =mtemp.user_id;
共13406條 查詢時(shí)間:67.549s
時(shí)間仍然很久，索引并沒有起作用，這里排除一些逃避困難時(shí)的迷信想法:mysql有bug 或者navicat有bug，有網(wǎng)絡(luò)問題 哈哈哈.....我們來看左聯(lián)的關(guān)鍵屬性:
user_id竟然是varchar的 ，但是users表中的id是int，這就造成了left join on users.id =integral_record.user_id時(shí)有類型轉(zhuǎn)換的問題而不能使用索引，因此，我們把user_id更改成int，再看看時(shí)間....(盡管兩三句就寫完了，這個(gè)數(shù)據(jù)類型煩擾了我整整找了一上午去找)
select * from users u LEFT JOIN integral_record mtemp on u.id =mtemp.user_id;
共13406條 查詢時(shí)間:0.375s
執(zhí)行時(shí)間從60多秒變成了0.375s,整個(gè)世界都安靜了，再次explain一下
EXPLAIN select * from users u LEFT JOIN integral_record mtemp on u.id =mtemp.user_id;
發(fā)現(xiàn)mtemp(integal)表的檢索方式從all變成了ref，索引，快的一批，深呼吸....
后記
希望繼續(xù)遇到這種sql需要優(yōu)化的問題來增加sql調(diào)優(yōu)相關(guān)經(jīng)驗(yàn)
