Почему MySQL использует только часть индекса?

Имеется таблица, помогающая проводить обработку заданий (более 300 млн. записей):
CREATE TABLE `test` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `job_id` int(10) unsigned NOT NULL,
 `lock` mediumint(6) unsigned DEFAULT '0',
 `time` timestamp NULL DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `job_id` (`job_id`),
 KEY `lock` (`lock`),
 KEY `time` (`time`),
 KEY `lock_time` (`lock`,`time`)
) ENGINE=MyISAM;

в поле lock записывается 0 (при ожидании), ИД процесса (при выборке), 1 (при выполнении) и наконец снова 0.
в поле time записываться время выполнения задания.
Необходимо замкнуть цикл обработки заданий, в голову пришло ORDER BY `time` ASC LIMIT n.
123456 - process id
UPDATE `test` SET `lock` = 123456 WHERE `lock` = 0 ORDER BY `time` LIMIT 1000
SELECT * FROM `test` WHERE `lock` = 123456
UPDATE `test` SET `lock` = 1 WHERE `lock` = 123456

Под запрос UPDATE был создан ключ: KEY `lock_time` (`lock`,`time`)
Но почему то при выполнении EXPLAIN получается что используется только часть индекса `lock_time` а именно lock:

e9117b192d1f43148d1df747841301c0.png
Хоть запрос выборки отрабатывает относительно быстро (0.0048 сек.):
SELECT SQL_NO_CACHE * FROM `test` WHERE `lock` = 0 ORDER BY `time` LIMIT 1000

Но аналогичный запрос UPDATE выпоняется недопустимо долго (286.0228 сек.):
UPDATE `test` SET `lock` = 123456 WHERE `lock` = 0 ORDER BY `time` LIMIT 1000

Жду ваших советов!
  • Вопрос задан
  • 305 просмотров
Решения вопроса 1
@mr_creo Автор вопроса
Все очень просто:
UPDATE `test` USE INDEX (`time`) SET `lock` = 123456 WHERE `lock` = 0 ORDER BY `time` LIMIT 1000

Затронуто 1000 строк. (Запрос занял 0.0736 сек.)
Всем спасибо!
Ответ написан
Комментировать
Пригласить эксперта
Ответы на вопрос 4
bigton
@bigton
Web-программист
Но аналогичный запрос UPDATE выполняется недопустимо долго (286.0228 сек.)
UPDATE `test` SET `lock` = 123456 WHERE `lock` = 0 ORDER BY `time` LIMIT 1000


Может быть потому что по lock есть индекс и это время получается из-за его перестроения?
Ответ написан
Комментировать
Четыре раза перечитал - так и не понял, зачем вам time при айдейте поля с ID процесса на ноль. Если вы сначала делаете так:
UPDATE `test` SET `lock` = 1 WHERE `lock` = 123456
то почему потом не сделать с точностью до наборот?
UPDATE `test` SET `lock` = 0 WHERE `lock` = 1

Если вы скажете, что несколько процессов могут выполнять задание, и нельзя просто так проставить нули всем заданиям, у кого 1, то я вам скажу, что вам нужна нормальная структура таблицы, и стоит разделить "lock" и "process_id". Хотя конечно непонятно, зачем вам такой переход: 0 -> process_id -> 1 -> 0, сделайте 0 -> process_id -> 0, и у вас будут нормальные запросы:
UPDATE `test` SET `process_id` = 123456 WHERE `process_id` = 0 ORDER BY `time` LIMIT 1000
UPDATE `test` SET `process_id` = 0 WHERE `process_id` = 123456
Ответ написан
Комментировать
Rsa97
@Rsa97
Для правильного вопроса надо знать половину ответа
Тут можно сделать так:
SET @id = 0;
UPDATE `test` SET `lock` = 1 WHERE @id := `id` AND `lock` = 0 ORDER BY `time` LIMIT 1;
SELECT @id;

В пределах одного подключения переменная сохраняется между запросами, а атомарность запроса UPDATE позволяет выбрать id строки и заблокировать её одновременно.
После обработки достаточно сбросить lock и, при успешной обработке, установить время завершения в time. Тогда задание снова встанет в конец очереди. Если time не менять, то задание окажется в начале очереди.
Ответ написан
Комментировать
Order by lock asc, time asc не пробовали? Планировщик mysql не всегда хорошо срабатывает, возможно, один из таких случаев.
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы