Как оптимизировать update в mysql?

Question

[Vitaly Serov]

Имеется таблица:

CREATE TABLE test.sites_list (
  id int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  url varchar(255) NOT NULL COMMENT 'Домен',
  reg varchar(100) DEFAULT NULL COMMENT 'Регистратор',
  domain_created date DEFAULT NULL COMMENT 'Дата регистрации домена',
  last_http_code int(11) NOT NULL COMMENT 'Последний статус ответа сервера',
  created_at timestamp DEFAULT CURRENT_TIMESTAMP COMMENT 'Время создания',
  updated_at timestamp DEFAULT '0000-00-00 00:00:00',
  status int(11) NOT NULL COMMENT 'Статус',
  marker varchar(255) NOT NULL DEFAULT '',
  proc varchar(255) DEFAULT NULL,
  PRIMARY KEY (id),
  INDEX domainCreated_updatedAt (domain_created, updated_at),
  INDEX Status_Proc_dCreated (status, proc, domain_created, updated_at),
  INDEX UpdatedAt_Status (updated_at, status)
)
ENGINE = INNODB
AUTO_INCREMENT = 19795014
AVG_ROW_LENGTH = 447
CHARACTER SET utf8
COLLATE utf8_general_ci;

Имеется запрос:

UPDATE `sites_list` SET `proc` = '2cc16b53f60654d97bcfb265eb0082a4'  WHERE `status` = 0 AND `proc` = '' ORDER BY `domain_created` DESC, `updated_at` LIMIT 50;

Ситуация:
В таблице, больше 3 млн. записей.
Этот запрос выполняется несколько минут.
Запрос SHOW PROFILE показывает, что все время уходит на init.

Как можно оптимизировать UPDATE или найти компромисс?

Answer 1

[Vit]

Составной индекс по proc и status имеется?

Answer 2

[Vitaly Serov]

INDEX Status_Proc_dCreated (status, proc, domain_created, updated_at),

Comments

[PhantomBlade]

Если я не ошибаюсь, сортировка происходит после выборки, так что вам нужно выделить status и proc в отдельный индекс.

[Vitaly Serov]

Вот все индексы:
INDEX domainCreated_updatedAt (domain_created, updated_at),
INDEX Status_Proc_dCreated (status, proc, domain_created, updated_at),
INDEX UpdatedAt_Status (updated_at, status)

status, proc можно взять из второго, а domain_created, updated_at - из первого, хотя EXPLAIN SELECT показывает только Status_Proc_dCreated

Answer 3

[Vitaly Serov]

Вот все индексы:

INDEX domainCreated_updatedAt (domain_created, updated_at),
INDEX Status_Proc_dCreated (status, proc, domain_created, updated_at),
INDEX UpdatedAt_Status (updated_at, status)

status, proc можно взять из второго, а domain_created, updated_at - из первого, хотя EXPLAIN SELECT показывает только Status_Proc_dCreated

Answer 4

[Александр Мелеховец]

Карты говорят, что дело может быть в разном порядке сортировки в ORDER BY. Попробуйте ORDER BY `domain_created` DESC, `updated_at` DESC , если логика приложения позволяет.

А вообще хорошо бы увидеть

EXPLAIN SELECT * FROM `sites_list`
WHERE `status` = 0 AND `proc` = '' 
ORDER BY `domain_created` DESC, `updated_at` LIMIT 50

Comments

[Vitaly Serov]

cdn.joxi.ru/uploads/prod/2014/02/12/a72/d40/674a26...

[Александр Мелеховец]

@serovvitaly
Using filesort - плохо. Сортировка миллиона записей быстрой не будет. Таки попробуйте ORDER BY `domain_created` DESC, `updated_at` DESC , в таком виде сортировка производиться не будет, выборка пойдёт сразу в нужном порядке. Если нельзя - поиграйте с max_heap_table_size и tmp_table_size , есть подозрение, что сортировка происходит с использованием временной таблицы на диске.

Далее, какой размер buffer_pool ? Обновляется много строк, будет перестраиваться большой кусок индексов. Если индексы не влезают в buffer_pool, mysql будет вынужден постоянно подгружать их страницы с диска, что опять же медленно.

И отпишитесь о результатах, пожалуйста.

Answer 5

[Vitaly Serov]

EXPLAIN SELECT * FROM `sites_list`
WHERE `status` = 0 AND `proc` = '' 
ORDER BY `domain_created` DESC, `updated_at` LIMIT 50

Результат:

Answer 6

[Дэн]

Попробуйте также посмотреть на профиль запроса с помощью SHOW PROFILE.