Логика выбора индекса mysql?

Question

[WebDev]

Есть таблица posts с вот такими индексами:

PRIMARY	id
UNIQUE	post_url
UNIQUE	fb_id
UNIQUE	tw_id
UNIQUE	yt_video_id
UNIQUE	bit_id
UNIQUE	ticketfly_event_id
UNIQUE	entity_id, amazon_asin
UNIQUE	itunes_collection_id, entity_id
UNIQUE	soundcloud_id
UNIQUE	event_id
UNIQUE	media_id, entity_id
UNIQUE	entity_id, festival_id
INDEX	itunes_release_date
INDEX	amazon_release_date
INDEX	entity_id, post_type, is_duplicate, deleted
INDEX	google_country

И есть вот такой запрос:

explain 

select `bq_posts`.* 
from bq_posts 
where (`entity_id` in ('93', '146', '191', '909', '946', '1444', '1686', '2102', '2129', '2147', '2213', '2236', '2532', '2553', '2591', '3119', '3175', '3283', '4742', '4825', '4906', '5095', '5556' /*Еще несколько тысяч идентификаторов*/))
and `post_type` in ('facebook', 'twitter', 'youtube', 'vevo', 'itunes', 'amazon', 'soundcloud', 'bit', 'ticketfly', 'festival', 'event', 'media')
and `bq_posts`.`deleted` is null 
and `bq_posts`.`is_duplicate` = '0'  
limit 10 offset 0;

То есть в условии участвуют поля entity_id, post_type, deleted и is_duplicate. По моей логике сюда идеально подходит индекс entity_id, post_type, is_duplicate, deleted, но explain показывает, что используется entity_id, amazon_asin, который является UNIQUE. Причем выбор индекса зависит от количества идектификаторов в запросе. Их несколько тысяч, но если я их сокращаю до 10-20, то explain показывает, что используется нужный индекс (entity_id, post_type, is_duplicate, deleted).
А все это началось при переезде с mysql 5.7 на percona server 5.7. До переезда этот запрос был мнгновенным, а теперь стал тормозить.
Что может быть не так?

Answer 1

[Евгений Вольф]

А все это началось при переезде с mysql 5.7 на percona server 5.7. До переезда этот запрос был мнгновенным, а теперь стал тормозить.

Простите за сарказм, "но это же MySQL, чего вы ожидали?". Одной из особенностей данной БД является довольно "топорный" оптимизатор запросов (если его можно так назвать), который не всегда корректно может определить, какой именно индекс следует использовать. Конечно, не корректность определения "оптимального" индекса - это проблема не только MySQL, но и других БД... но, как-то уж очень слабо в MySQL (и его производных) пытаются с этим бороться. Так же проблему усугубляет тот факт, что MySQL может использовать только 1 индекс на запрос + некоторые другие факторы.

"Что с этим делать?" - сказать сложно, вряд ли в обозримом будущем, Вам (или разработчикам MySQL или других БД) удастся решить эту проблему целиком и полностью, но конкретно в Вашем случае, из личного опыта, могу предположить, что наиболее оптимальным решением будет "тыкать пальцем, какой именно индекс использовать" ну и отслеживать/отлаживать/корректировать подобные запросы в будущем.

Так же, хочу обратить Ваше внимание, что проблема тут скорее всего не в "Percona" как таковой, и MySQL и MariaDB и т.д., не редко грешат подобными вещами, при относительно больших объёмах данных.

Comments

[WebDev]

Вся грусть в том, что арендовали более мощный сервер и заодно обновили все ПО, в том числе сменили СУБД на percona и появились проблемы со скоростью там, где проблем не было.

[Евгений Вольф]

WebDev, тут... очень тонкий вопрос... я много разных "MySQL-вариации" перепробовал, с разным объёмом данных и т.д., очень сложно дать однозначный совет из серии "делайте вот так и всё будет хорошо", в ряде случаев, Percona может вести себя более "адекватно", нежели оригинальный MySQL, а иногда наоборот... Что бы дать полноценную рекомендацию - нужно хорошенько проанализировать объём данных, настройки старой БД и новой, как всё это построено внутри и т.д. Даже полноценно ответить на вопрос "по какому принципу MySQL выбирает индекс" - довольно сложно, т.к. на это влияет масса факторов и в конце концов, движки БД делают люди и никто не застрахован от банальных багов... Я думаю, просто заставить БД использовать нужный ("правильный") индекс в запросах, которые у Вас стали тормозить - наиболее быстрое и довольно надежное решение.