Идентично отформатированные запросы с различными параметрами имеют радикально различное время выполнения в MySQL 5.7.

Question

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

SELECT SQL_NO_CACHE DISTINCT pr.Master_Person_ID
FROM liverpool.person_record pr 
JOIN liverpool.person_property_view ppv1 ON (pr.Master_Person_ID = ppv1.Master_Person_ID)
JOIN liverpool.property_type_class ptc1 ON (ptc1.Property_ID = ppv1.Property_ID)
JOIN liverpool.person_property_view ppv2 ON (pr.Master_Person_ID = ppv2.Master_Person_ID)
JOIN liverpool.property_type_class ptc2 ON (ptc2.Property_ID = ppv2.Property_ID)
WHERE ptc1.Property_Class_ID = 2
AND ppv1.Property_Value = 'Ruth'
AND ptc2.Property_Class_ID = 6
AND ppv2.Property_Value = 'Davies'
ORDER BY pr.Year_From_Origin_Record, pr.Recorded_Date
LIMIT 100000;

Возвращает результаты за 0,06 секунды. Более чем достаточно быстро для моих нужд. Но этот запрос:

SELECT SQL_NO_CACHE DISTINCT pr.Master_Person_ID
FROM liverpool.person_record pr
JOIN liverpool.person_property_view ppv1 ON (pr.Master_Person_ID = ppv1.Master_Person_ID)
JOIN liverpool.property_type_class ptc1 ON (ptc1.Property_ID = ppv1.Property_ID)
JOIN liverpool.person_property_view ppv2 ON (pr.Master_Person_ID = ppv2.Master_Person_ID)
JOIN liverpool.property_type_class ptc2 ON (ptc2.Property_ID = ppv2.Property_ID)
WHERE ptc1.Property_Class_ID = 2
AND ppv1.Property_Value = 'Edward'
AND ptc2.Property_Class_ID = 6
AND ppv2.Property_Value = 'Abbott'
ORDER BY pr.Year_From_Origin_Record, pr.Recorded_Date
LIMIT 100000;

Единственное отличие здесь в параметрах поиска. Но этот второй запрос занимает более 9 минут. Еще дольше, если использовать "LIKE" вместо "=". Конечно, в моей базе данных больше "Эдварда", чем "Рут", но, конечно, это само по себе не может объяснить, почему второй запрос на несколько порядков медленнее, чем первый? В запросе, как вы, вероятно, видите, используются самостоятельные соединения. Я понимаю, что это может быть не самый эффективный способ сделать это, но они подходят для того, что мне нужно, и делают мой код переднего плана НАМНОГО проще. И большую часть времени они работают нормально.

Вот ОБЪЯСНЕНИЕ для первого (быстрого) запроса:

id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
1,SIMPLE,ptc1,NULL,ref,"PRIMARY,Property_ID_IDX,Property_Class_ID_IDX",Property_Class_ID_IDX,4,const,2,100.00,"Using index; Using temporary; Using filesort"
1,SIMPLE,pt,NULL,eq_ref,PRIMARY,PRIMARY,4,liverpool.ptc1.Property_ID,1,100.00,NULL
1,SIMPLE,rlt,NULL,eq_ref,PRIMARY,PRIMARY,4,liverpool.pt.Record_Link_Type_ID,1,100.00,"Using where; Using index"
1,SIMPLE,prp,NULL,ref,"PRIMARY,Property_Value_IDX,Person_Record_ID_IDX",Property_Value_IDX,23,"const,liverpool.ptc1.Property_ID",13,100.00,"Using where"
1,SIMPLE,pr,NULL,eq_ref,"PRIMARY,Master_Person_ID_IDX,Person_Record_ID_IDX",PRIMARY,4,liverpool.prp.Person_Record_ID,1,100.00,"Using where"
1,SIMPLE,rt,NULL,eq_ref,"PRIMARY,Record_Type_ID",PRIMARY,4,liverpool.pr.Record_Type_ID,1,100.00,"Using index"
1,SIMPLE,pr,NULL,ref,Master_Person_ID_IDX,Master_Person_ID_IDX,17,liverpool.pr.Master_Person_ID,1,100.00,NULL
1,SIMPLE,pr,NULL,ref,"PRIMARY,Master_Person_ID_IDX,Person_Record_ID_IDX",Master_Person_ID_IDX,17,liverpool.pr.Master_Person_ID,1,100.00,Distinct
1,SIMPLE,rt,NULL,eq_ref,"PRIMARY,Record_Type_ID",PRIMARY,4,liverpool.pr.Record_Type_ID,1,100.00,"Using index; Distinct"
1,SIMPLE,ptc2,NULL,ref,"PRIMARY,Property_ID_IDX,Property_Class_ID_IDX",Property_Class_ID_IDX,4,const,5,100.00,"Using index; Distinct"
1,SIMPLE,pt,NULL,eq_ref,PRIMARY,PRIMARY,4,liverpool.ptc2.Property_ID,1,100.00,Distinct
1,SIMPLE,rlt,NULL,eq_ref,PRIMARY,PRIMARY,4,liverpool.pt.Record_Link_Type_ID,1,100.00,"Using where; Using index; Distinct"
1,SIMPLE,prp,NULL,eq_ref,"PRIMARY,Property_Value_IDX,Person_Record_ID_IDX",PRIMARY,8,"liverpool.ptc2.Property_ID,liverpool.pr.Person_Record_ID",1,5.00,"Using where; Distinct"

И вот ОБЪЯСНЕНИЕ для второго (медленного) запроса:

id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
1,SIMPLE,ptc1,NULL,ref,"PRIMARY,Property_ID_IDX,Property_Class_ID_IDX",Property_Class_ID_IDX,4,const,2,100.00,"Using index; Using temporary; Using filesort"
1,SIMPLE,pt,NULL,eq_ref,PRIMARY,PRIMARY,4,liverpool.ptc1.Property_ID,1,100.00,NULL
1,SIMPLE,rlt,NULL,eq_ref,PRIMARY,PRIMARY,4,liverpool.pt.Record_Link_Type_ID,1,100.00,"Using where; Using index"
1,SIMPLE,prp,NULL,ref,"PRIMARY,Property_Value_IDX,Person_Record_ID_IDX",Property_Value_IDX,23,"const,liverpool.ptc1.Property_ID",13,100.00,"Using where"
1,SIMPLE,pr,NULL,eq_ref,"PRIMARY,Master_Person_ID_IDX,Person_Record_ID_IDX",PRIMARY,4,liverpool.prp.Person_Record_ID,1,100.00,"Using where"
1,SIMPLE,rt,NULL,eq_ref,"PRIMARY,Record_Type_ID",PRIMARY,4,liverpool.pr.Record_Type_ID,1,100.00,"Using index"
1,SIMPLE,pr,NULL,ref,Master_Person_ID_IDX,Master_Person_ID_IDX,17,liverpool.pr.Master_Person_ID,1,100.00,NULL
1,SIMPLE,pr,NULL,ref,"PRIMARY,Master_Person_ID_IDX,Person_Record_ID_IDX",Master_Person_ID_IDX,17,liverpool.pr.Master_Person_ID,1,100.00,Distinct
1,SIMPLE,rt,NULL,eq_ref,"PRIMARY,Record_Type_ID",PRIMARY,4,liverpool.pr.Record_Type_ID,1,100.00,"Using index; Distinct"
1,SIMPLE,ptc2,NULL,ref,"PRIMARY,Property_ID_IDX,Property_Class_ID_IDX",Property_Class_ID_IDX,4,const,5,100.00,"Using index; Distinct"
1,SIMPLE,pt,NULL,eq_ref,PRIMARY,PRIMARY,4,liverpool.ptc2.Property_ID,1,100.00,Distinct
1,SIMPLE,rlt,NULL,eq_ref,PRIMARY,PRIMARY,4,liverpool.pt.Record_Link_Type_ID,1,100.00,"Using where; Using index; Distinct"
1,SIMPLE,prp,NULL,eq_ref,"PRIMARY,Property_Value_IDX,Person_Record_ID_IDX",PRIMARY,8,"liverpool.ptc2.Property_ID,liverpool.pr.Person_Record_ID",1,5.00,"Using where; Distinct"

Я знаю, что это почти невозможно прочитать, но я не могу понять, как вставить / импортировать что-либо в табличной форме в этот сайт...

Важной частью является то, что, насколько я вижу, эти два EXPLAINs показывают функционально идентичный план запроса! И все же один намного быстрее, чем другой. Есть ли что-то в том, как планировщик упорядочивает эти утверждения, возможно? Я вполне способен работать с SQL, но этот инструмент планирования / индексации запросов углубляется в Dark Arts для меня слишком далеко. Может ли кто-нибудь там помочь?

Я пытался добавлять и удалять индексы. Я пытался переписать запросы, используя FORCE INDEX, но это только сделало их медленнее. Я в конце моего остроумия здесь.

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

В основной таблице данных (с псевдонимом prp в EXPLAIN) имеется 24 771 запись с Property_Class, соответствующим "First_Name" и Property_Value "Edward", и 567 записей с Property_Class, соответствующим "Last_Name" и с Property_Value "" Abbott. Запрос, который выполняет поиск этих параметров, выполняется в течение многих минут и, как правило, останавливается на веб-сервере до его завершения.

И наоборот, есть 916 записей с Property_Class, соответствующим "First_Name" и Property_Value "Ruth", и 15,054 записей с Property_Class, соответствующим "Last_Name" и с Property_Value "Davies". Запрос, который ищет эти параметры, выполняется за 0,6 секунды.

Как видите, оба запроса, вероятно, будут включать одинаковое количество перекрестных совпадений (~14 000 000). И все же один ледниковый, а другой нет.

В любом случае, я попытался увеличить любые вероятные переменные типа буфера в my.ini, чтобы увидеть, помогает ли это, но я немного неохотно слишком усердно экспериментирую в этом отношении, учитывая, что я действительно не знаю, что делаю, Я больше программист, чем администратор сервера баз данных!

Так что, если у кого-то есть понимание для меня, я был бы рад услышать это!

Спасибо за ваше время.

РЕДАКТИРОВАТЬ: VIEW, используемый для сшивания Property_Type, Person и Property_Value в последовательную запись, выглядит следующим образом:

CREATE VIEW liverpool.person_property_view AS
SELECT 
prp.Person_Record_ID, 
pr.Record_Of_Origin_ID,
pr.Relationship_To_Origin_Record, 
pr.Recorded_Date,
pr.Year_From_Origin_Record,
pr.Master_Person_ID,
pr.Composite_Record_ID,
pr.Has_Been_Matched,
pr.First_Name,
pr.Other_Names,
pr.Last_Name,
pt.Property_ID,
pt.Property_Type_Name,
pt.Property_Type_Display_Name,
pt.Show_Property,
prp.Property_Value,
prp.Property_Display_Value,
prp.Property_Date_Value,
pt.Is_Downloadable,
pt.Is_Person_Record_Link,
pt.Is_Record_Link,
pt.Display_Only_Once,
pt.Property_Display_Order,
rt.Record_Type_Description,
rt.Record_Type_Sort_Order,
rt.Record_Type_Precedence,
rlt.Record_Link_Type_Code
FROM liverpool.person_record_property_value prp 
JOIN liverpool.person_record pr ON prp.Person_Record_ID = pr.Person_Record_ID
JOIN liverpool.property_type pt ON prp.Property_ID = pt.Property_ID
LEFT OUTER JOIN liverpool.record_link_type rlt ON pt.Record_Link_Type_ID = rlt.Record_Link_Type_ID
LEFT OUTER JOIN liverpool.record_type rt ON rt.Record_Type_ID = pr.Record_Type_ID;

Answer 1

Это "чрезмерно нормализовано".

Это "EAV".

Положите эти два вместе, и вы получите много JOINs это не может быть оптимизировано. Обработка должна идти вперед и назад много.

(Нет, я не знаю, почему один запрос значительно медленнее другого.)

Для получения дополнительной помощи, пожалуйста, предоставьте SHOW CREATE TABLE а также SHOW CREATE VIEW,