Почему этот запрос к таблице MEMORY медленнее, чем его близнец к InnoDB?

У меня есть версия таблицы InnoDB и MEMORY. Оба имеют одинаковые индексы и одинаковые 30 000 строк данных. Существует конкретный запрос, который выполняется очень медленно при запуске для таблицы MEMORY.

Вот против InnoDB:

SELECT emails.id
FROM emails
LEFT JOIN custom_data_person pd1 ON (pd1.person_id = emails.person_id)
WHERE pd1.field_id = 13

2928 rows in set (0.24 sec)

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pd1
         type: ref
possible_keys: person_id,field_id
          key: field_id
      key_len: 5
          ref: const
         rows: 20240
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: emails
         type: ref
possible_keys: person_id
          key: person_id
      key_len: 4
          ref: test.pd1.person_id
         rows: 1
        Extra: Using index

Вот ПАМЯТЬ:

SELECT emails.id
FROM emails_memory AS emails
LEFT JOIN custom_data_person pd1 ON (pd1.person_id = emails.person_id)
WHERE pd1.field_id = 13

2928 rows in set (1.40 sec)

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emails
         type: ALL
possible_keys: person_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 30000
        Extra: 
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: pd1
         type: ref
possible_keys: person_id,field_id
          key: person_id
      key_len: 10
          ref: test.emails.person_id,const
         rows: 1
        Extra: Using where; Using index

(Обратите внимание, что индекс person_id является индексом BTREE. Я попробовал то же самое с индексом HASH по умолчанию, и результаты были такими же.)

Таким образом, похоже, что MySQL оптимизировал второй запрос по-другому, что ухудшило его работу. Почему это? Могу ли я "исправить" это?


person chroder    schedule 10.02.2011    source источник
comment
Можете ли вы сделать explain tablename и show indexes from tablename для двух разных таблиц. 2 разных движка будут оптимизироваться по-разному, поэтому вам может понадобиться дать подсказки движку.   -  person DeveloperChris    schedule 11.02.2011


Ответы (1)


Тебе все равно. С крошечной таблицей с 30 000 строк все будет очень быстро, даже сканирование таблицы.

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

Возможно, это какая-то плохая пессимизация в оптимизаторе для таблиц памяти.

Я бы избегал таблиц памяти. Если вам нужна таблица, которая ведет себя почти как таблица памяти, используйте таблицу MyISAM и усекайте ее при запуске сервера. Таблицы памяти невероятно плохи, потому что они хранят varchars, дополненные до максимальной длины, поэтому они обычно используют намного больше памяти, чем таблицы другого типа. MyISAM очень эффективно использует хранилище. В качестве альтернативы используйте таблицы InnoDB для всего.

К сожалению, innodb не предоставляет способа установить долговечность для каждой таблицы, поэтому, если fsync для каждой транзакции вас беспокоит, вам придется выполнять более крупные (и, следовательно, меньшие) транзакции.

Использование нескольких движков является компромиссом, поскольку сервер редко может автоматически разделить свой (конечный) объем оперативной памяти между движками. Так что обычно вы хотите использовать только один движок; это включает в себя механизм памяти, который с радостью заберет кучу памяти из вашего innodb (следовательно, сделает его медленнее, поскольку он может уместить меньше вашего db в памяти), если вы настроите его таким образом.

Если серьезно, вам действительно, действительно, действительно не нужны 30 тыс. строк. 30 тысяч строк могут поместиться в самой маленькой памяти, даже если они огромны. Любой движок — это движок памяти, когда вы используете 30 тыс. строк.

person MarkR    schedule 10.02.2011
comment
Существуют вполне веские причины для использования таблиц памяти. - person DeveloperChris; 11.02.2011
comment
Я так и не понял, что они из себя представляют. - person MarkR; 11.02.2011
comment
для временных таблиц в хранимых процедурах куча лучше. Кажется, для завершения создания таблицы требуется 1/2 секунды, если это таблица INNODB. Кроме того, удаление, скажем, 1/2 строк в БОЛЬШОЙ таблице (скажем, 1 000 000 строк) занимает много времени. хотя это звучит нелепо, мне действительно необходимо периодически это делать. Ваш комментарий по поводу использования нескольких движков - это как-то снижает производительность? вы упомянули память (я понимаю), но как насчет присоединения таблиц Innodb к таблицам MyISAM, есть ли штраф за такие вещи? - person Don Wool; 12.05.2012
comment
Это правда, что некоторые операции с метаданными InnoDB могут быть медленными. DROP TABLE особенно плох, когда у вас есть огромный пул буферов (ему необходимо сканировать весь пул буферов, чтобы определить страницы для удаления) - person MarkR; 12.05.2012