После изменения версии БД индекс не будет использоваться автоматически

Question

Недавно мы перенесли наше приложение Magento с частного хоста на веб-сервисы AWS. Мы заметили, что некоторые из внутренних функций Magento требовали слишком много времени для выполнения после миграции, поэтому начали расследование.

Один из рассматриваемых запросов - это простой запрос выбора клиента, включающий около 9-10 регулярных объединений в таблицы атрибутов для получения атрибутов.

Мы провели несколько тестов по запросу и обнаружили, что разница между старым хостом и AWS заключается в том, что на старом хосте оптимизатор MySQL, похоже, использует правильный индекс, тогда как в AWS он прибегает к использованию файловой сортировки, игнорируя индекс.

С помощью FORCE INDEX(index_name) заставляет запрос правильно выполняться в AWS, однако мы не хотим идти по этому пути, и скорее решим проблему в конфигурации базы данных, чем будем делать ручные хаки во всем нашем приложении Magento. Чтобы было ясно, это не проблема с нашими индексами, они настроены правильно.

Для фона:

• Я скопировал все параметры MySQL из файла my.cnf старого хоста в группу параметров в RDS, но ничего не изменило
• Все таблицы InnoDB
• Я запускаю анализ, исправление и оптимизацию запросов и т. Д.
• Запрос занимает около 45 секунд для выполнения на RDS
• Запрос занял около 2 секунд на старом хосте или когда я использую FORCE INDEX() заставить RDS вести себя так же, как старый хост

Старый сервер MySQL работал под управлением версии 5.1.61, а запущенный экземпляр AWS RDS - 5.6.19. Консультативная группа предложила нам понизить наш экземпляр RDS до 5.1.61, однако, опять же, мы не хотим этого делать, поскольку это не является устойчивым решением.

Данный запрос приведен ниже (сокращен путем удаления полей из выбора для экономии места):

SELECT
    `e`.*
    -- various field names here, removed
FROM `customer_entity` AS `e`
LEFT JOIN `customer_entity_int` AS `at_default_billing` ON (`at_default_billing`.`entity_id` = `e`.`entity_id`) AND (`at_default_billing`.`attribute_id` = '13')
LEFT JOIN `customer_address_entity_varchar` AS `at_billing_postcode` ON (`at_billing_postcode`.`entity_id` = `at_default_billing`.`value`) AND (`at_billing_postcode`.`attribute_id` = '30')
LEFT JOIN `customer_address_entity_varchar` AS `at_billing_city` ON (`at_billing_city`.`entity_id` = `at_default_billing`.`value`) AND (`at_billing_city`.`attribute_id` = '26')
LEFT JOIN `customer_address_entity_varchar` AS `at_billing_telephone` ON (`at_billing_telephone`.`entity_id` = `at_default_billing`.`value`) AND (`at_billing_telephone`.`attribute_id` = '31')
LEFT JOIN `customer_address_entity_varchar` AS `at_billing_regione` ON (`at_billing_regione`.`entity_id` = `at_default_billing`.`value`) AND (`at_billing_regione`.`attribute_id` = '28')
LEFT JOIN `customer_address_entity_varchar` AS `at_billing_country_id` ON (`at_billing_country_id`.`entity_id` = `at_default_billing`.`value`) AND (`at_billing_country_id`.`attribute_id` = '27')
LEFT JOIN `core_store` AS `at_store_name` ON (at_store_name.`store_id`=e.store_id)
LEFT JOIN `customer_entity_varchar` AS `firstname` ON e.entity_id = firstname.entity_id AND firstname.attribute_id = 5
LEFT JOIN `customer_entity_varchar` AS `lastname` ON e.entity_id = lastname.entity_id AND lastname.attribute_id = 7 

WHERE (`e`.`entity_type_id` = '1') 

ORDER BY `e`.`entity_id`
DESC LIMIT 20;

Сводка различий между серверами, когда EXPLAIN Этот запрос ниже:

RDS - MySQL v5.6.19:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: e
         type: ref
possible_keys: IDX_CUSTOMER_ENTITY_ENTITY_TYPE_ID
          key: IDX_CUSTOMER_ENTITY_ENTITY_TYPE_ID
      key_len: 2
          ref: const
         rows: 653990
        Extra: Using temporary; Using filesort

Старый хост - MySQL v5.1.61, или когда FORCE INDEX используется на RDS:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: e
         type: ref
possible_keys: IDX_CUSTOMER_ENTITY_ENTITY_TYPE_ID
          key: IDX_CUSTOMER_ENTITY_ENTITY_TYPE_ID
      key_len: 2
          ref: const
         rows: 644775
        Extra: Using where

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

---

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

Answer 1

Если посмотреть на предоставленный вами запрос, то, возможно, вас зацепит ошибка # 74030. Патч был добавлен для более поздней версии (5.6.20 и 5.7.4), но еще не был применен, учитывая примечания к выпуску. Возможно, было бы хорошо войти в систему и проголосовать за эту ошибку.

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

Answer 2

Моя ставка на MySQL conf:

• статистика метаданных: с 5.6 значение по умолчанию для innodb_stats_on_metadata изменено на off. Это действительно может изменить план запроса.
• теперь, когда вы уже выполнили несколько запросов на новой установке, вы все равно можете использовать скрипт mysqltuner, это всегда хорошо.
• обратите внимание, что 5.6 изменил по умолчанию innodb_file_per_table на 1 и Query Cache отключен. Вы можете найти другие изменения по умолчанию здесь: https://blogs.oracle.com/supportingmysql/entry/server_defaults_changes_in_mysql

Answer 3

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

Я не могу говорить о каких-либо изменениях от версии к версии, но вот реклама, которая говорит об этом случае:

В некоторых случаях MySQL не может использовать индексы для разрешения ORDER BY, хотя он все еще использует индексы для поиска строк, которые соответствуют предложению WHERE. Эти случаи включают в себя следующее:

Ключ, используемый для выборки строк, не совпадает с ключом, используемым в ORDER BY:

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

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