Медленный запрос после обновления mysql с 5.5 до 5.6

Мы обновили mysql с 5.5 до 5.6, и некоторые запросы сейчас работают очень медленно.

Запросы, которые занимали 0,005 секунды, теперь занимают 49 секунд.

Запросы на 5.6 пропускают индексы, кажется:

+----+-------------+-------+-------+----------------------------------------------------+---------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys                                      | key     | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+----------------------------------------------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | pens  | index | index_contents_on_slug,index_contents_on_slug_hash | PRIMARY | 4       | NULL | 471440 | Using where |
+----+-------------+-------+-------+----------------------------------------------------+---------+---------+------+--------+-------------+
1 row in set (0.00 sec)

Но не пропускаются на 5.5:

+----+-------------+-------+-------------+----------------------------------------------------+----------------------------------------------------+---------+------+------+----------------------------------------------------------------------------------------------+
| id | select_type | table | type        | possible_keys                                      | key                                                | key_len | ref  | rows | Extra                                                                                        |
+----+-------------+-------+-------------+----------------------------------------------------+----------------------------------------------------+---------+------+------+----------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | pens  | index_merge | index_contents_on_slug,index_contents_on_slug_hash | index_contents_on_slug_hash,index_contents_on_slug | 768,768 | NULL |    2 | Using union(index_contents_on_slug_hash,index_contents_on_slug); Using where; Using filesort |
+----+-------------+-------+-------------+----------------------------------------------------+----------------------------------------------------+---------+------+------+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Обе БД были созданы из одного и того же mysql-дампа.

Разве эти индексы не создаются, когда я делаю импорт на 5.6? Как заставить форсировать создание индекса?

Запрос:

SELECT  `pens`.* FROM `pens`  WHERE (slug_hash = 'style' OR slug = 'style') ORDER BY `pens`.`id` DESC LIMIT 1

Изменить: Удалена схема

2 ответа

Решение

В конечном итоге принятый ответ является правильным.

Помощь @RandomSeed заставила меня задуматься в правильном направлении. По сути, планы оптимизации, созданные в 5.6, значительно отличаются от планов в 5.5, поэтому вам, вероятно, придется переделать ваш запрос, как и я.

Я не в конечном итоге с помощью FORCE INDEX, но вместо этого удалил части запроса, пока я не определил, что заставило 5.6 пропустить индекс. Затем я переработал логику приложения, чтобы справиться с этим.

Медленный запрос в v5.6 вызван тем, что движок не может или не решает объединить два соответствующих индекса (index_contents_on_slug_hash, index_contents_on_slug) для обработки вашего запроса. Помните, что запрос может использовать только один индекс на таблицу. Чтобы иметь возможность использовать несколько индексов в одной таблице, необходимо на лету предварительно объединить эти индексы в один (в памяти). Это значение index_merge а также Using union(...) уведомления в вашем плане выполнения. Это требует времени и памяти, очевидно.

Быстрое исправление (и, возможно, предпочтительное решение в любом случае): добавьте индекс с двумя столбцами на slug а также slug_hash,

ALTER TABLE pens ADD INDEX index_contents_on_slug_and_slug_hash (
    slug, slug_hash
);

Теперь ваш новый сервер, вероятно, не может объединить эти индексы, поскольку это приводит к тому, что индекс слишком велик для размещения в буфере. Ваш новый сервер, вероятно, имеет гораздо меньшее значение для key_buffer_size (если таблица MyISAM) или для innodb_buffer_pool_size (если InnoDB), чем раньше в вашей старой установке.

Другие вопросы по тегам