A, table

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;
Copy the code

Insert data

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);
Copy the code

Three, test one: equivalent query

Lock statement:

delete from tt1 where pubtime =3;
Copy the code

Locking situation:

Secondary index lock pubtime=3 and X lock between left and right. Corresponding primary index with record lock

Test two: single condition range query

Lock statement:

delete from tt1 where pubtime > 10;
Copy the code

Locking situation:

Secondary index lock pubtime>10 range with X lock. Corresponding primary index with record lock

Five, test three: multi-condition range

Lock statement:

delete from tt1 where pubtime > 5 and pubtime < 20;
Copy the code

Locking situation:

Secondary index lock 5

When the number of secondary index locks exceeds a certain number, it is possible to extend full index locks. The exact number of rows cannot be measured due to the number of records, but the more records that are hit, the higher the probability of extending a perfect indexed lock. Therefore, the smaller the number of records hit by a range query, the better.

Test 4: Add conditions

Lock statement:

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

Locking situation:

Enable index condition push down

#optimizer_switch Switch of parameters related to optimization
mysql> show VARIABLES like 'optimizer_switch'\G;
# close the ICP
SET optimizer_switch = 'index_condition_pushdown=off';
# on ICP
SET optimizer_switch = 'index_condition_pushdown=on';
Copy the code

View the Optimizer_switch parameter:

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,i ndex_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semij oin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,con dition_fanout_filter=on,derived_merge=on 1 row inset (0.00 sec)
Copy the code

Index_condition_pushdown =on on optimizer_switch value index_condition_pushdown=on The lock test is performed on another session, and the record with ID 4 is not locked.

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

After closing the index condition, repeat the above process and find that the record is locked.