前文關於ICP最佳化的實驗貌似有誤
http://blog.itpub.net/29254281/viewspace-1398273/

實驗環境

select @@optimizer_switch\G
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,matery_materialization_cost_based=on,use_index_extensions=on
1 row in set (0.00 sec)

實驗資料
CREATE TABLE t1 (
id int(11) NOT NULL,
userid varchar(10) DEFAULT NULL,
blogid varchar(10) DEFAULT NULL,
pubtime int(11) DEFAULT NULL,
comment varchar(10) DEFAULT NULL,
PRIMARY KEY (id),
KEY inx_t1_pu (pubtime,userid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO t1 VALUES
(1,'hdc','a',10,NULL),
(4,'yyy','b',3,NULL),
(6,'hdc','c',100,NULL),
(8,'hdc','d',5,'good'),
(10,'hdc','e',1,NULL),
(100,'bbb','f',20,NULL);
commit;

下圖示識的情況實際上有錯誤.

按照前文的說法,啟用ICP二級索引pubtime=3,userid=yyy 和主鍵索引id=4的節點不會被鎖.

首先確定這個SQL會應用ICP

可以看到由Delete改寫的SQL應用了ICP最佳化

執行文中的SQL
delete from t1 where pubtime>1 and pubtime<20 and userid='hdc' and comment is not null;

然後show engine innodb status\G

解析上鎖節點
輔助索引
select conv('000000a',16,10); 10
select conv('0000003',16,10); 3
select conv('0000005',16,10); 5
select conv('0000014',16,10); 20

主鍵索引
select conv('0000001',16,10); 1
select conv('0000004',16,10); 4
select conv('0000008',16,10); 8
select conv('0000064',16,10); 100

實際的情況應該如下圖所示

也就是說,被ICP過濾的資料也會被上鎖.
並且根據MySQL Next-Key Locking,會對間隙鎖右側的資料上鎖,所以輔助索引pubtime=20,userid=bbb的節點和主鍵索引id=100的節點也會被上鎖.

Next Key Locking參考
http://blog.itpub.net/29254281/viewspace-1090160/

糾正錯誤如下
1.ICP不會釋放不符合條件記錄的鎖
按文中的例子,
輔助索引Index key(pubtime > 1 and puptime < 20)選中的節點 3,5,10 會被上鎖.
並且3,5,10產生的4個間隙會被上鎖.
根據Next Key Locking,右側間隙的值會被上鎖.(10,20],也就是說輔助索引pubtime=20,userid=bbb的節點會被上鎖.
最後輔助索引對應的主鍵索引都會被上鎖(id 1,4,8,100)

2.前文圖中間隙鎖的右側邊界節點應該被上鎖,但是圖中沒有標識出來.

MySQL選用可重複讀之前一定要想到的事情(ICP驗證和勘誤)

相關文章