#mysql #optimization #join #query-optimization
#mysql #оптимизация #Присоединиться #оптимизация запросов
Вопрос:
Я работаю с некоторыми операторами MySQL, которые используют объединения. Производительность для этих запросов кажется довольно низкой и может снижаться в течение времени, в течение которого выполняется запрос. Ниже приведены несколько примеров запросов, которые я использую. Я новичок в операторах соединения MySQL, и мне было любопытно, может ли кто-нибудь помочь мне оптимизировать их для повышения производительности.
Мы используем оба из них для запроса базы данных нашей системы продажи билетов для создания некоторых отчетов о типах обработанных билетов, местоположении, из которого они были обработаны, И т. Д.
SELECT * FROM tickets t
LEFT Join customfieldvalues cv1 ON t.ticketid = cv1.typeid
LEFT Join customfields cf1 ON cv1.customfieldid = cf1.customfieldid
LEFT Join customfieldoptions co1 on cv1.fieldvalue = co1.customfieldoptionid
WHERE t.dateline BETWEEN 1314853200 AND 1317445199
Group by t.ticketid
Order by t.Dateline asc;
Это базовый запрос (без добавления фильтрации) с 1 по 30 сентября. Время выполнения ~ 140 сек. Если вы уберете три строки соединения, время выполнения сократится до ~ 0,01 секунды.
SELECT * FROM tickets t
LEFT Join customfieldvalues cv1 ON t.ticketid = cv1.typeid
LEFT Join customfields cf1 ON cv1.customfieldid = cf1.customfieldid
LEFT Join customfieldoptions co1 on cv1.fieldvalue = co1.customfieldoptionid
LEFT Join customfieldvalues cv2 ON cv1.typeid = cv2.typeid
LEFT Join customfields cf2 ON cv2.customfieldid = cf2.customfieldid
LEFT Join customfieldoptions co2 on cv2.fieldvalue = co2.customfieldoptionid
WHERE t.dateline BETWEEN 1314853200 AND 1317445199
AND cf1.title ='Customer Type' AND co1.optionvalue = 'Staff'
And cf2.title ='Building or Hall' AND co2.optionvalue like '%Stroupe%'
Group by t.ticketid
Order by t.Dateline asc;
Этот запрос будет основным запросом с добавлением 2 фильтров: тип клиента (т. Е. Персонал) и местоположение здания или зала (Stroupe). Используя те же временные рамки, что и выше, время выполнения составляет ~ 0,1 секунды.
==============================
РЕДАКТИРОВАТЬ: вот результат команды EXPLAIN для первого из перечисленных запросов.
INSERT INTO `table_name` (`id`,`select_type`,`table`,`type`,`possible_keys`,`key`,`key_len`,`ref`,`rows`,`Extra`) VALUES (1,'SIMPLE','t','range','tickets7','tickets7','4',NULL,601,'Using where; Using temporary; Using filesort');
INSERT INTO `table_name` (`id`,`select_type`,`table`,`type`,`possible_keys`,`key`,`key_len`,`ref`,`rows`,`Extra`) VALUES (1,'SIMPLE','cv1','ALL',NULL,NULL,NULL,NULL,104679,'');
INSERT INTO `table_name` (`id`,`select_type`,`table`,`type`,`possible_keys`,`key`,`key_len`,`ref`,`rows`,`Extra`) VALUES (1,'SIMPLE','cf1','eq_ref','PRIMARY','PRIMARY','4','DB.cv1.customfieldid',1,'');
INSERT INTO `table_name` (`id`,`select_type`,`table`,`type`,`possible_keys`,`key`,`key_len`,`ref`,`rows`,`Extra`) VALUES (1,'SIMPLE','co1','eq_ref','PRIMARY','PRIMARY','4','DB.cv1.fieldvalue',1,'');
Вот результат EXPLAIN из второго запроса.
id,select_type, table,type,possible_keys,key,key_len,ссылка, строки, Дополнительный 1,ПРОСТОЙ, cf1, ссылка,»ОСНОВНОЙ, title1″,title1,767,const,1,»Использование where; Использование temporary; Использование filesort» 1,ПРОСТОЙ,co1,ссылка,»PRIMARY,optionvalue1″,optionvalue1,767,const,1,»Используя where» 1,SIMPLE,cf2,ref,»PRIMARY,title1″,title1,767,const,1,»Используя where» 1,SIMPLE,t, диапазон,»PRIMARY,tickets7,tickets15,tickets16″,tickets7,4, NULL,601, «Использование where» 1,ПРОСТОЙ, cv1, ссылка, customfieldvalues1,customfieldvalues1,8,»DB.cf1.customfieldid,DB.t.ticketid»,1,»Использование where» 1, ПРОСТОЙ,cv2, ссылка, customfieldvalues1,customfieldvalues1,8, «DB.cf2.customfieldid,DB.t.ticketid», 1, «Использование where» 1, ПРОСТОЙ,co2,eq_ref,ПЕРВИЧНЫЙ, ПЕРВИЧНЫЙ,4, DB.cv2.fieldvalue,1, «Использование where»
Комментарии:
1. Вы создали индексы для столбцов, участвующих в операциях объединения?
2. @ Joe — Я только что посмотрел, и да, похоже, что для каждого из них есть индексы.
3. две вещи помогли бы получить лучшие ответы: определение таблицы (чтобы мы могли проверить, есть ли индексы в столбцах, которые мы ожидаем) и вывод EXPLAIN
4. @ Neville — Как мне создать эту информацию, чтобы я мог поделиться ею?
5. Добавьте слово EXPLAIN перед запросом, запустите его и покажите нам результаты.
Ответ №1:
Это неправильные соединения, ваше левое соединение предложение where = внутреннее соединение, вам нужно переписать запрос.
Вы говорите, что получите мне все строки из cf1, даже если они не существуют, и только те, которые имеют заголовок «Тип клиента».
Если вы измените левые соединения на внутренние соединения, это будет более производительным (хотя может и не возвращать то, что вы хотите)
Комментарии:
1. @ Kevin — Спасибо за идеи. Я все еще новичок в этом. Как мне создать план запроса? Кроме того, будет ли внутреннее соединение здесь лучше? Я добавил два индекса (я думаю, что я сделал это правильно), как вы предлагаете для title и optionvalue.
2. переключение на внутренние соединения имело бы больше смысла для базы данных в сочетании с вашим предложением where, на данный момент конфликтуют 2 раздела.
3. Существует ли формула для понимания типов соединений. Например, вы упоминаете, что внутреннее соединение в основном — INNER JOIN = LEFT JOIN ПРЕДЛОЖЕНИЕ WHERE ? Существуют ли другие подобные формулы для других типов?
4. на самом деле это не формула, это распространенная ошибка, которая может привести к плохим планам выполнения