場景
一個消息表哟忍，需要被多個節(jié)點抓取狡门，存在并發(fā)的情況，要求節(jié)點抓取的數(shù)據(jù)不能重復锅很。
消息表定義
-- 備注：mysql5.5
CREATE TABLE `msg_tbl` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
`state` tinyint(4) DEFAULT NULL COMMENT '消息狀態(tài).0=未抓取,1=已抓取',
`type` int(11) DEFAULT NULL COMMENT '消息類型',
`content` varchar(128) DEFAULT NULL COMMENT '消息內(nèi)容',
`create_time` datetime DEFAULT NULL COMMENT '消息產(chǎn)生時間',
PRIMARY KEY (`id`),
KEY `idx-query` (`state`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
嘗試1
SELECT `id` FROM `msg_tbl` where `state`=0 and `type`=1 order by id asc limit 20 for update;
結(jié)論：可以解決需求融撞，但會導致表鎖，原因是for update只有在限制主鍵ID時粗蔚，才會采用行鎖尝偎，否則會采用表鎖。所以要使用for update鹏控，必須限制查詢表的主鍵ID致扯。
嘗試2
SELECT * FROM `msg_tbl` where `id` in (SELECT `id` FROM `msg_tbl` where `state`=0 and `type`=1 order by `id` asc ) limit 20 for update;
結(jié)論：不能解決問題，且會造成DEPENDENT SUBQUERY当辐，從而導致慢查詢抖僵。原因是子查詢的查詢次數(shù)依賴于外層查詢，當外查詢數(shù)據(jù)過多時缘揪，會嚴重影響查詢性能耍群。
子查詢擴展
mysql 在處理子查詢時，會改寫子查詢找筝。
通常情況下蹈垢，我們希望由內(nèi)到外，先完成子查詢的結(jié)果袖裕，然后再用子查詢來驅(qū)動外查詢的表曹抬，完成查詢。
例如：
select * from test where tid in (select fk_tid from sub_test where gid=10)
通常我們會感性地認為該 sql 的執(zhí)行順序是：
sub_test 表中根據(jù) gid 取得 fk_tid(2,3,4,5,6)記錄急鳄，
然后再到 test 中谤民，帶入 tid=2,3,4,5,6堰酿，取得查詢數(shù)據(jù)。
但是實際mysql的處理方式為：
select * from test where exists (select * from sub_test where gid=10 and sub_test.fk_tid=test.tid)
mysql 將會掃描 test 中所有數(shù)據(jù)张足，每條數(shù)據(jù)都將會傳到子查詢中與 sub_test 關聯(lián)触创，子查詢不會先被執(zhí)行，所以如果 test 表很大的話为牍，那么性能上將會出現(xiàn)問題哼绑。
嘗試3
SELECT * FROM `msg_tbl` a,(SELECT `id` FROM `msg_tbl` where `state`=0 and `type`=1 order by `id` asc limit 20) b where a.`id`=b.`id` for update;
結(jié)論：不會造成慢查詢，但會造成數(shù)據(jù)重復抓取吵聪。原因是臨時表的查詢沒有采用for update，依然可以讀取到正在修改的數(shù)據(jù)兼雄，所以當有并發(fā)請求時吟逝，可能會取到已被修改過的數(shù)據(jù)，造成臟讀赦肋。
嘗試4（最終解決方案）
-- 在嘗試3基礎上外層where語句增加state條件限制
SELECT * FROM `msg_tbl` a,(SELECT `id` FROM `msg_tbl` where `state`=0 and `type`=1 order by id asc limit 20) b where a.`id`=b.`id` and `state`=0 for update;
結(jié)論：能滿足需求块攒，且在百萬級數(shù)據(jù)下仍然做到毫秒級查詢(當然也跟機器配置有關)。
希望能幫到有需要的人佃乘。
