Форум пользователей MySQL

boa · 22.04.2013 12:52:21

Добрый день!

столкнулся с такой проблемой

есть таблица

CREATE TABLE `schedule_test` (

  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `datetime_processed` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  `datetime_next` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  `sign` bigint(20) unsigned NOT NULL DEFAULT '0',

  `lock` tinyint(4) NOT NULL DEFAULT '0',

  PRIMARY KEY (`id`),

  KEY `idx_lock_dt_nxt` (`lock`,`datetime_next`)

) ENGINE=InnoDB;

она представляет из себя расписание задач и содержит примерно 45к записей.

Выборка задач происходит так

update schedule_test as sa 

set 

    sa.`lock` = 1, 

    sa.sign = 2123123123,

    sa.`datetime_processed` = now()

where 

      sa.`lock` = 0 

and sa.`datetime_next` <= now()

order by sa.`datetime_next`  asc

limit 10;

Проблема в том, что не смотря на наличие составного ключа idx_lock_dt_nxt для выборки, mysql все равно делает fullscan таблицы.
Количество чтений по таблицам смотрю в innotop, тут есть удобный вывод.

Но если написать селект то он работает так как ожидается - по индексам выбирает 10 записей и уже считывает из таблицы именно 10 записей.

select sa.`lock`, sign, datetime_next

from  schedule_test as sa 

where 

      sa.`lock` = 0 

and sa.`datetime_next` <= now()

order by sa.`datetime_next`  asc

limit 10;

explain такой

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE sa ref lock lock 1 const 22716 Using index condition; Using where

Для меня не очень понятна природа такого явления.
Почему update делает фулскан, а селект работает как надо, и что нужно сделать чтобы update работал также хорошо и использовал индекс.

В качестве эксперимента изменил индекс и сделал его таким KEY `idx_lock_dt_nxt` (`datetime_next`)
тогда апдейт начинает его использовать для выборки по дате, но по сути это "отложенный" фуллскан, т.к. постепенно все минимальные даты будут залочены (lock=1) и для выполнения условия where mysql с каждой новой выборкой считывает все больше и больше записей из таблицы.

rgbeast · 22.04.2013 13:22:25

Странно, теоретически, update и select должны следовать одинаковому плану. Попробуйте использовать FORCE INDEX. Обходной вариант - делать сначала SELECT, а потом UPDATE с условием типа id IN (,,,) или с использованием временной таблицы для хранения результатов SELECT.

boa · 22.04.2013 13:56:13

rgbeast написал:
Странно, теоретически, update и select должны следовать одинаковому плану. Попробуйте использовать FORCE INDEX. Обходной вариант - делать сначала SELECT, а потом UPDATE с условием типа id IN (,,,) или с использованием временной таблицы для хранения результатов SELECT.

тут фигня в том что update должен выполнится как атомарная операция.
при выполнении update 10 лучших записей с минимальной датой лочатся (lock=1) и им выставляется некий уникальный сигн.
А потом зная сигн, я просто выбираю залоченные записи и с ними спокойно работаю.
если разбить update на две фазы - выборку и далее update, то когда зайдут несколько клиентов и одновременно попытаются получить задачи, у них может возникнуть коллизия и они получат одинаковые строки.

вот кстати вопрос
если сделать так, получится ли "развести" клиентов и избежать наложений?

start transaction;
select some_rows for update;
update schedule where id in some_rows;
commit;

rgbeast · 22.04.2013 14:08:19

С транзакцией можно избежать наложений, но:
1) нужно использовать высокую степень изоляции (SERIALIZABLE)
2) нужно быть готовым, что транзакция может откатиться с ошибкой (в этом случае ее можно просто повторить)

Можно использовать явную блокировку таблицы: http://webew.ru/articles/1383.webew

boa · 22.04.2013 18:03:47

оформил как баг, думаю поправят
https://mariadb.atlassian.net/browse/MDEV-4410

rgbeast · 22.04.2013 18:51:22

Порадовало, что быстро ответили. Пишите здесь как решится судьба бага.

Форум пользователей MySQL

#1 22.04.2013 12:52:21

update не хочет использовать хороший индекс

#2 22.04.2013 13:22:25

Re: update не хочет использовать хороший индекс

#3 22.04.2013 13:56:13

Re: update не хочет использовать хороший индекс

rgbeast написал:

#4 22.04.2013 14:08:19

Re: update не хочет использовать хороший индекс

#5 22.04.2013 18:03:47

Re: update не хочет использовать хороший индекс

#6 22.04.2013 18:51:22

Re: update не хочет использовать хороший индекс

Board footer