Анализ ситуации блокировки вспомогательного индекса на базе MySQL

MySQL

1. Создайте таблицу

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;

2. Вставьте данные

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);

3. Тест 1: Эквивалентный запрос

Заявление о блокировке:

delete from tt1 where pubtime =3;

Ситуация блокировки:

Блокировка вторичного индексаpubtime=3И левый и правый клиренс плюс Х замок. Соответствующий первичный индекс плюс блокировка записи

В-четвертых, второй тест: запрос диапазона одного условия

Заявление о блокировке:

delete from tt1 where pubtime > 10;

Ситуация блокировки:

Блокировка вторичного индексаpubtime>10Прицел плюс замок X. Соответствующий первичный индекс плюс блокировка записи

5. Тест 3: Несколько диапазонов условий

Заявление о блокировке:

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

Ситуация блокировки:

Блокировка вторичного индекса5<pubtime<=20区间加X锁。 Соответствующий первичный индекс плюс блокировка записи

Когда количество блокировок вторичного индекса превышает определенное число, оно может быть расширено до полных блокировок индекса. Из-за ограниченного количества записей невозможно измерить конкретное количество строк, но чем больше записей попадет, тем выше вероятность расширения до полной блокировки индекса. Следовательно, чем меньше записей попадает в диапазонный запрос, тем лучше.

6. Тест 4: Добавление условий

Заявление о блокировке:

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Его можно найти в значении параметраindex_condition_pushdown=on, указывая на то, что включено проталкивание условия индекса. Тест блокировки был выполнен в другом сеансе, и было обнаружено, что запись с идентификатором 4 не была заблокирована.

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

После отключения проталкивания условия индекса описанный выше процесс повторяется, и обнаруживается, что в это время запись заблокирована.

Категории