MySQL基于辅助索引加锁情况分析当辅助索引锁定的数量超过一定数量后，有可能会扩展成全索引加锁。由于记录数量所限，具体的

一、建表

CREATE TABLE `tt1` (
  `id` int(11) NOT NULL,
  `userid` varchar(5) DEFAULT NULL,
  `blogid` varchar(5) DEFAULT NULL,
  `pubtime` int(11) DEFAULT NULL,
  `comment` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_t1_pub` (`pubtime`,`userid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

二、插入数据

INSERT INTO `hello`.`tt1`(`id`, `userid`, `blogid`, `pubtime`, `comment`) VALUES (1, 'hdc', 'a', 10, null);
INSERT INTO `hello`.`tt1`(`id`, `userid`, `blogid`, `pubtime`, `comment`) VALUES (4, 'yyy', 'b', 3, null);
INSERT INTO `hello`.`tt1`(`id`, `userid`, `blogid`, `pubtime`, `comment`) VALUES (6, 'hdc', 'c', 100, null);
INSERT INTO `hello`.`tt1`(`id`, `userid`, `blogid`, `pubtime`, `comment`) VALUES (8, 'hdc', 'd', 5, 'good');
INSERT INTO `hello`.`tt1`(`id`, `userid`, `blogid`, `pubtime`, `comment`) VALUES (10, 'hdc', 'e', 1, null);
INSERT INTO `hello`.`tt1`(`id`, `userid`, `blogid`, `pubtime`, `comment`) VALUES (100, 'bbb', 'f', 20, null);
INSERT INTO `hello`.`tt1`(`id`, `userid`, `blogid`, `pubtime`, `comment`) VALUES (110, 'bbd', 'g', 140, 'hello');
INSERT INTO `hello`.`tt1`(`id`, `userid`, `blogid`, `pubtime`, `comment`) VALUES (121, 'bbe', 'h', 250, 'world');
INSERT INTO `hello`.`tt1`(`id`, `userid`, `blogid`, `pubtime`, `comment`) VALUES (130, 'bbf', 'i', 360, 'kkb');
INSERT INTO `hello`.`tt1`(`id`, `userid`, `blogid`, `pubtime`, `comment`) VALUES (188, 'bbg', 'j', 470, null);
INSERT INTO `hello`.`tt1`(`id`, `userid`, `blogid`, `pubtime`, `comment`) VALUES (201, 'hdc', 'a', 510, null);
INSERT INTO `hello`.`tt1`(`id`, `userid`, `blogid`, `pubtime`, `comment`) VALUES (204, 'yyy', 'b', 933, null);
INSERT INTO `hello`.`tt1`(`id`, `userid`, `blogid`, `pubtime`, `comment`) VALUES (216, 'hdc', 'c', 1100, null);
INSERT INTO `hello`.`tt1`(`id`, `userid`, `blogid`, `pubtime`, `comment`) VALUES (218, 'hdc', 'd', 545, 'good');
INSERT INTO `hello`.`tt1`(`id`, `userid`, `blogid`, `pubtime`, `comment`) VALUES (310, 'hdc', 'e', 490, null);
INSERT INTO `hello`.`tt1`(`id`, `userid`, `blogid`, `pubtime`, `comment`) VALUES (321, 'bbb', 'f', 620, null);
INSERT INTO `hello`.`tt1`(`id`, `userid`, `blogid`, `pubtime`, `comment`) VALUES (333, 'bbd', 'g', 740, 'hello');
INSERT INTO `hello`.`tt1`(`id`, `userid`, `blogid`, `pubtime`, `comment`) VALUES (421, 'bbe', 'h', 520, 'world');
INSERT INTO `hello`.`tt1`(`id`, `userid`, `blogid`, `pubtime`, `comment`) VALUES (458, 'bbf', 'i', 603, 'kkb');
INSERT INTO `hello`.`tt1`(`id`, `userid`, `blogid`, `pubtime`, `comment`) VALUES (480, 'bbg', 'j', 870, null);

三、测试一：等值查询

加锁语句：

delete from tt1 where pubtime =3;

加锁情况：

辅助索引锁pubtime=3及左右间隙加X锁。对应的主索引加记录锁

四、测试二：单条件范围查询

加锁语句：

delete from tt1 where pubtime > 10;

加锁情况：

辅助索引锁pubtime>10范围加X锁。对应的主索引加记录锁

五、测试三：多条件范围

加锁语句：

delete from tt1 where pubtime > 5 and pubtime < 20;

加锁情况：

辅助索引锁5<pubtime<=20区间加X锁。对应的主索引加记录锁

当辅助索引锁定的数量超过一定数量后，有可能会扩展成全索引加锁。由于记录数量所限，具体的行数无法测得，但是命中的记录数量越多，扩展成全索引锁的概率越高。所以，范围查询命中的记录数量越小越好。

六、测试四：增加条件

加锁语句：

delete from tt1 where pubtime > 1 and pubtime < 20 and userid = 'hdc' and comment is not null;

加锁情况：

开启索引条件下推

#optimizer_switch优化相关参数开关
mysql> show VARIABLES like 'optimizer_switch'\G;
#关闭ICP
SET optimizer_switch = 'index_condition_pushdown=off';
#开启ICP
SET optimizer_switch = 'index_condition_pushdown=on';

查看optimizer_switch参数：

mysql> show VARIABLES like 'optimizer_switch'\G;
*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: 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,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
1 row in set (0.00 sec)

可以从optimizer_switch参数的value中可以发现其中index_condition_pushdown=on，说明索引条件下推已经开启。在另外一个session中进行加锁测试，发现id为4的记录并没有加锁。

mysql> select * from tt1 where id = 4 for update;
+----+--------+--------+---------+---------+
| id | userid | blogid | pubtime | comment |
+----+--------+--------+---------+---------+
|  4 | yyy    | b      |       3 | NULL    |
+----+--------+--------+---------+---------+

关闭索引条件下推后，重复以上处理，发现此时记录处于锁定状态。