LIMIT 1 очень медленный, для конкретных записей, используя разные клавиши

Я диагностирую прерывистый медленный запрос и обнаружил странное поведение в MySQL, которое я не могу объяснить. Он выбирает другую, неоптимальную стратегию ключа для одного конкретного случая, только при выполнении LIMIT 1.

Таблица (некоторые удаленные столбцы данных без ссылок удалены для краткости)

CREATE TABLE `ch_log` (
    `cl_id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `cl_unit_id` INT(11) NOT NULL DEFAULT '0',
    `cl_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    `cl_type` CHAR(1) NOT NULL DEFAULT '',
    `cl_data` TEXT NOT NULL,
    `cl_event` VARCHAR(255) NULL DEFAULT NULL,
    `cl_timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `cl_record_status` CHAR(1) NOT NULL DEFAULT 'a',
    PRIMARY KEY (`cl_id`),
    INDEX `cl_type` (`cl_type`),
    INDEX `cl_date` (`cl_date`),
    INDEX `cl_event` (`cl_event`),
    INDEX `cl_unit_id` (`cl_unit_id`),
    INDEX `log_type_unit_id` (`cl_unit_id`, `cl_type`),
    INDEX `unique_user` (`cl_user_number`, `cl_unit_id`)
)
ENGINE=InnoDB
AUTO_INCREMENT=419582094;

Это запрос, который работает только медленно для одного конкретного cl_unit_id:

EXPLAIN
SELECT *
FROM `ch_log`
WHERE `ch_log_type` ='I' and ch_log_event = 'G'  
AND cl_unit_id=1234
ORDER BY cl_date DESC 
LIMIT 1;

id|select_type|table |type |possible_keys                               |key    |key_len|ref|rows|Extra
1 |SIMPLE     |ch_log|index|cl_type,cl_event,cl_unit_id,log_type_unit_id|cl_date|8      |\N |5295|Using where

Для всех остальных значений cl_unit_id он использует клавишу log_type_unit_id, которая намного быстрее.

id|select_type|table |type|possible_keys                                           |key             |key_len|ref        |rows|Extra
1 |SIMPLE     |ch_log|ref |ch_log_type,ch_log_event,ch_log_unit_id,log_type_unit_id|log_type_unit_id|5      |const,const|3804|Using where; Using filesort
  • Все запросы занимают около 0,01 секунды.
  • Запрос "медленный блок" занимает 10-15 минут!

Я не вижу ничего странного в данных для этой "единицы":

  • У блока 1234 только 6 записей типа я и события G.
  • Другие устройства имеют гораздо больше.
  • Единица 1234 имеет всего 32 000 журналов, что типично.
  • сами данные являются нормальными, не более или более старыми.
  • В базе данных содержится около 3000 единиц, которые представляют собой записи в устройствах. Cl_unit_id - это их уникальный PK (хотя и не ограничение).

Общая информация

  • Всего 30 миллионов записей, около 12 ГБ.
  • mysql 5.1.69-log
  • Centos 64bit
  • Данные постепенно меняются (30 м = 3 месяца журналов), но я не знаю, произошло ли это раньше.

Вещи, которые я пробовал, и могут "решить" проблему с помощью:

  • Удаление LIMIT 1 - запрос выполняется в миллисекундах и возвращает данные.

  • Переход на LIMIT 2 или другие комбинации, например. 2,3 - работает в миллисекундах.

  • Добавление указателя индекса - решает его:

    FROM `ch_log` USE INDEX (log_type_unit_id)

    но... Я не хочу жестко закодировать это в приложении.

  • Добавление второго порядка по первичному ключу также "решает" его:

    ORDER BY cl_id, cl_date DESC

    объясняя:

    id|select_type|table |type|possible_keys                                           |key             |key_len|ref        |rows|Extra
1 |SIMPLE     |ch_log|ref |ch_log_type,ch_log_event,ch_log_unit_id,log_type_unit_id|log_type_unit_id|5      |const,const|6870|Using where

    который немного отличается от намеченного типа, с большим количеством зарегистрированных записей (6000), но все еще работает в течение 10 миллисекунд.

Снова я могу это сделать, но мне не нравятся побочные эффекты, которые я не понимаю.

Итак, я думаю, что мой главный вопрос:

a) почему это происходит только для LIMIT 1?

b) как сами данные могут влиять на ключевую стратегию? И какой аспект данных, видя, что количество и распространение в индексах кажется типичным.

Ответы

Ответ 1

Mysql выберет план объяснения и использует разные индексы в зависимости от того, что, по его мнению, является статистически лучшим выбором. Для всех ваших первых вопросов это ответ:

  • Удаление LIMIT 1 - запрос выполняется в миллисекундах и возвращает данные. и → Да, проверьте это, план объяснения хорош.
  • Переход на LIMIT 2 или другие комбинации, например. 2,3 - работает в миллисекундах. → то же самое. Оптимизатор выбирает другой индекс, потому что внезапно ожидаемые чтения блоков становятся вдвое большими, чем при LIMIT 1 (это только одна возможность).
  • Добавление подсказки индекса решает ее → Конечно, вы заставляете хороший план объяснений
  • Добавление второго порядка по первичному ключу также "решает" его → да, потому что по совпадению результат - лучший план объяснения.

Теперь это отвечает только на половину вопросов.

a) почему это происходит только для LIMIT 1?

На самом деле это происходит не только из-за LIMIT 1, но из-за

  • Перераспределение статистики данных (ориентирует решения оптимизатора)
  • Предложение ORDER BY DESC. Попробуйте ORDER BY ... ASC, и вы также увидите улучшение.

Это явление прекрасно понимается. читать.

Одно из принятых решений (ниже в статье) - заставить индекс так же, как и вы. Да, иногда это оправдано. В противном случае этот намек был бы полностью уничтожен давно. Роботы не всегда могут быть идеальными: -)

b) как сами данные могут влиять на ключевую стратегию? И что аспект данных, видя как количество и распространение в индексах кажется типичным.

Вы сказали это, распространение - это то, что обычно трахается. Не только оптимизатор может просто принять неправильное решение с точной статистикой, но он также может быть полностью отключен только потому, что дельта на таблице находится под 1/16-м общее количество строк...