背景
由于整體的歷史原因胸墙，導(dǎo)致部分設(shè)備存在COMMON類型無法進行區(qū)分胰伍，需要根據(jù)對應(yīng)的通道數(shù)進行區(qū)分，導(dǎo)致需要device 和camera的兩個表需要級聯(lián)查詢妻献，進行分頁操作贪庙。
未優(yōu)化前SQL語句為：
SELECT a.serial_uuid, a.user_id, a.device_name
FROM device a
LEFT JOIN camera b
USING (serial_uuid)
WHERE a.user_id = 1
GROUP BY b.serial_uuid
HAVING count(b.serial_uuid) > 1
LIMIT 0, 10;
;
device表的索引
PRIMARY KEY (`id`),
UNIQUE KEY `uidx_device_serial` (`serial_uuid`),
KEY `idx_user_id` (`user_id`)
camera表的索引
UNIQUE KEY `uidx_serial_channel` (`serial_uuid`,`channel_no`),
KEY `idx_user_id` (`user_id`)
我們來explain一下這個sql語句
這里看到索引是有的蜘犁，但是IP攻擊次數(shù)表device 也用上了臨時表。那么這SQL不優(yōu)化直接第一次執(zhí)行需要多久（這里強調(diào)第一次是因為MYSQL帶有緩存功能止邮，執(zhí)行過一次的同樣SQL这橙，第二次會快很多。）
查詢時間達到1s中左右
那么我們怎么優(yōu)化呢导披，索引既然走了屈扎，我嘗試一下避免臨時表，這時我們先了解一下臨時表跟group by的使聯(lián)系:
查找了網(wǎng)上一些博客分析GROUP BY 與臨時表的關(guān)系 :
1. 如果GROUP BY 的列沒有索引,產(chǎn)生臨時表.
2. 如果GROUP BY時,SELECT的列不止GROUP BY列一個,并且GROUP BY的列不是主鍵 ,產(chǎn)生臨時表.
3. 如果GROUP BY的列有索引,ORDER BY的列沒索引.產(chǎn)生臨時表.
4. 如果GROUP BY的列和ORDER BY的列不一樣,即使都有索引也會產(chǎn)生臨時表.
5. 如果GROUP BY或ORDER BY的列不是來自JOIN語句第一個表.會產(chǎn)生臨時表.
6. 如果DISTINCT 和 ORDER BY的列沒有索引,產(chǎn)生臨時表.
ROWS的行數(shù)770W而且還是有臨時表撩匕，看來這復(fù)合索引也是不可取鹰晨。
到此，避免臨時表方法失敗了止毕，我們得從其他角度想想如何優(yōu)化模蜡。
其實，9W的臨時表并不算多扁凛，那么為什么導(dǎo)致會這么久的查詢呢忍疾？我們想想這沒優(yōu)化的SQL的執(zhí)行過程是怎么樣的呢？
網(wǎng)上搜索得知內(nèi)聯(lián)表查詢一般的執(zhí)行過程是：
1谨朝、執(zhí)行FROM語句
2卤妒、執(zhí)行ON過濾
3、添加外部行
4字币、執(zhí)行where條件過濾
5则披、執(zhí)行g(shù)roup by分組語句
6、執(zhí)行having
7洗出、select列表
8士复、執(zhí)行distinct去重復(fù)數(shù)據(jù)
9、執(zhí)行order by字句
10翩活、執(zhí)行l(wèi)imit字句
這里得知判没，Mysql 是先執(zhí)行內(nèi)聯(lián)表然后再進行條件查詢的最后再分組,那么想想這SQL的條件查詢和分組都只是一個表的蜓萄，內(nèi)聯(lián)后數(shù)據(jù)就變得臃腫了，這時候再進行條件查詢和分組是否太吃虧了澄峰，我們可以嘗試一下提前進行分組和條件查詢嫉沽，實現(xiàn)方法就是子查詢聯(lián)合內(nèi)聯(lián)查詢。
group的也是 索引,根據(jù)之前group by的第一條如果GROUP BY 的列沒有索引,產(chǎn)生臨時表.
這里子查詢可以使用group利用serial_uuid的索引（不太清楚為什么級聯(lián)查詢沒有使用上）俏竞，可以使用mysql默認(rèn)的b+tree 來避免排序分組绸硕，避免產(chǎn)生臨時表。
camera表的索引
UNIQUE KEY `uidx_serial_channel` (`serial_uuid`,`channel_no`),
KEY `idx_user_id` (`user_id`,`serial_uuid`)
增加索引后的explain
優(yōu)化后的sql語句為：
SELECT a.serial_uuid
FROM
(SELECT
b.id,
b.dvc_device_id,
b.serial_uuid
FROM camera b
WHERE b.user_id = 1
GROUP BY b.serial_uuid
HAVING COUNT(b.serial_uuid) > 1
) c
LEFT JOIN device a
ON c.serial_uuid = a.serial_uuid
WHERE a.user_id = 1
LIMIT 0, 10;
這樣查詢速度提升了到了97ms魂毁，提升了10倍玻佩。
要是有其它更好的方案希望指出進行交流，來增加對mysql的學(xué)習(xí)和了解
總結(jié)：
由于級聯(lián)查詢的中間表會非常大席楚，最好能限制連的表大小的條件都先用上了咬崔，同時盡量讓條件查詢和分組執(zhí)行的表盡量小。
