MySQL: случайный эффект на ANALYZE TABLE

У меня есть 3 таблицы innodb, скажем, A, B и C. Есть запрос на объединение этих трех таблиц для получения результатов.

SELECT A.a, B.b, C.c
from A 
join B on A.id = B.a_id 
join C on C.id = B.c_id
where A.a = 'example' and B.b < 10;

В начале, когда я тестировал запрос с помощью команды «EXPLAIN», он дает мне следующий порядок:

B -- C -- A

Однако это не оптимально. Итак, я запускаю «АНАЛИЗ ТАБЛИЦЫ» для всех таблиц, и это дает мне:

A -- B -- C

, что я считаю правильным порядком.

Затем я развернул SQL в рабочей среде, и через 1 месяц план выполнения снова переключился на плохой вариант, то есть B-C-A. После этого я попытался снова запустить ANALYZE TABLE несколько раз, хотя на этот раз результаты меня сбивают с толку. Иногда он дает мне также B--C--A, иногда он дает мне A--B--C, а иногда даже другой план выполнения.

Итак, мой вопрос:

  1. почему план выполнения меняется после развертывания?
  2. помимо закрепления плана выполнения (данные обновляются и меняются быстро, поэтому оптимальный план может измениться в будущем), есть ли способ гарантировать, что оптимальный план всегда будет обеспечиваться?

mysql indexing sql-execution-plan explain
person Chen Xie    schedule 30.01.2013    source источник

Ответы (1)


arrow_upward
6
arrow_downward

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

InnoDB время от времени обновляет свою статистику, и это то, что вы можете вызвать, когда запускаете ANALZYE TABLE.

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

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

Вы можете переопределить алгоритм оптимизатора по умолчанию для переупорядочения таблиц, указав STRAIGHT_JOIN. Это означает, что вы хотите, чтобы он читал таблицы в том порядке, в котором вы указали их в предложении FROM, и не переупорядочивал их.

Вы можете использовать STRAIGHT_JOIN в качестве модификатора запроса (например, DISTINCT). Поместите его сразу после SELECT:

SELECT STRAIGHT_JOIN A.a, B.b, C.c
from A 
join B on A.id = B.a_id 
join C on C.id = B.c_id
where A.a = 'example' and B.b < 10;

Но будьте осторожны при использовании подсказок индекса или подсказок соединения слишком свободно. Оптимизатор может избежать поведения триггеров на следующей неделе, после того как размер и распределение данных немного изменятся. Если в вашем коде слишком много переопределений, вы можете помешать оптимизатору работать лучше!

person Bill Karwin    schedule 30.01.2013
comment
Спасибо за конкретное объяснение! Однако, учитывая, что innoDB время от времени обновляет свою статистику, и учитывая, что таблицы базы данных обновляются/вставляются с высокой частотой (50 секунд), я заметил, что как только она падает до cusp, она не возвращается легко. Мне нужно явно запустить ANALYZE TABLE, как это может произойти? - person Chen Xie; 31.01.2013
comment
Статистика обновляется после ANALYZE TABLE, SHOW TABLE STATUS или запроса к INFORMATION_SCHEMA.TABLES. Таким образом, вы можете выполнять одно из этих утверждений каждые 1 час или каждые 10 минут или по любому другому расписанию. Влияние обновления статистики довольно мало, но достаточно велико, чтобы я не стал делать это после каждого обновления. - person Bill Karwin; 31.01.2013