背景
由于整體的歷史原因，導(dǎo)致部分設(shè)備存在COMMON類型無(wú)法進(jìn)行區(qū)分，需要根據(jù)對(duì)應(yīng)的通道數(shù)進(jìn)行區(qū)分，導(dǎo)致需要device 和camera的兩個(gè)表需要級(jí)聯(lián)查詢，進(jìn)行分頁(yè)操作。
未優(yōu)化前SQL語(yǔ)句為：
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`)
我們來(lái)explain一下這個(gè)sql語(yǔ)句
這里看到索引是有的，但是IP攻擊次數(shù)表device 也用上了臨時(shí)表。那么這SQL不優(yōu)化直接第一次執(zhí)行需要多久（這里強(qiáng)調(diào)第一次是因?yàn)镸YSQL帶有緩存功能，執(zhí)行過(guò)一次的同樣SQL，第二次會(huì)快很多。）
查詢時(shí)間達(dá)到1s中左右
那么我們?cè)趺磧?yōu)化呢，索引既然走了，我嘗試一下避免臨時(shí)表，這時(shí)我們先了解一下臨時(shí)表跟group by的使聯(lián)系:
查找了網(wǎng)上一些博客分析GROUP BY 與臨時(shí)表的關(guān)系 :
1. 如果GROUP BY 的列沒(méi)有索引,產(chǎn)生臨時(shí)表.
2. 如果GROUP BY時(shí),SELECT的列不止GROUP BY列一個(gè),并且GROUP BY的列不是主鍵 ,產(chǎn)生臨時(shí)表.
3. 如果GROUP BY的列有索引,ORDER BY的列沒(méi)索引.產(chǎn)生臨時(shí)表.
4. 如果GROUP BY的列和ORDER BY的列不一樣,即使都有索引也會(huì)產(chǎn)生臨時(shí)表.
5. 如果GROUP BY或ORDER BY的列不是來(lái)自JOIN語(yǔ)句第一個(gè)表.會(huì)產(chǎn)生臨時(shí)表.
6. 如果DISTINCT 和 ORDER BY的列沒(méi)有索引,產(chǎn)生臨時(shí)表.
ROWS的行數(shù)770W而且還是有臨時(shí)表，看來(lái)這復(fù)合索引也是不可取。
到此，避免臨時(shí)表方法失敗了，我們得從其他角度想想如何優(yōu)化。
其實(shí)，9W的臨時(shí)表并不算多，那么為什么導(dǎo)致會(huì)這么久的查詢呢？我們想想這沒(méi)優(yōu)化的SQL的執(zhí)行過(guò)程是怎么樣的呢？
網(wǎng)上搜索得知內(nèi)聯(lián)表查詢一般的執(zhí)行過(guò)程是：
1、執(zhí)行FROM語(yǔ)句
2、執(zhí)行ON過(guò)濾
3、添加外部行
4、執(zhí)行where條件過(guò)濾
5、執(zhí)行g(shù)roup by分組語(yǔ)句
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)表然后再進(jìn)行條件查詢的最后再分組,那么想想這SQL的條件查詢和分組都只是一個(gè)表的，內(nèi)聯(lián)后數(shù)據(jù)就變得臃腫了，這時(shí)候再進(jìn)行條件查詢和分組是否太吃虧了，我們可以嘗試一下提前進(jìn)行分組和條件查詢，實(shí)現(xiàn)方法就是子查詢聯(lián)合內(nèi)聯(lián)查詢。
group的也是 索引,根據(jù)之前group by的第一條如果GROUP BY 的列沒(méi)有索引,產(chǎn)生臨時(shí)表.
這里子查詢可以使用group利用serial_uuid的索引（不太清楚為什么級(jí)聯(lián)查詢沒(méi)有使用上），可以使用mysql默認(rèn)的b+tree 來(lái)避免排序分組，避免產(chǎn)生臨時(shí)表。
camera表的索引
UNIQUE KEY `uidx_serial_channel` (`serial_uuid`,`channel_no`),
KEY `idx_user_id` (`user_id`,`serial_uuid`)
增加索引后的explain
優(yōu)化后的sql語(yǔ)句為：
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倍。
要是有其它更好的方案希望指出進(jìn)行交流，來(lái)增加對(duì)mysql的學(xué)習(xí)和了解
總結(jié)：
由于級(jí)聯(lián)查詢的中間表會(huì)非常大，最好能限制連的表大小的條件都先用上了，同時(shí)盡量讓條件查詢和分組執(zhí)行的表盡量小。
