Как я могу оптимизировать этот запрос, выполнение занимает больше минуты

У меня есть следующий запрос, выполнение которого занимает больше минуты, как я могу его оптимизировать. Это медленно из-за порядка по описанию o.id, если я удалю его, запрос выполнит его несколько мс.


select o.*, per.email, p.name
from order o 
inner join product p 
on o.product_id=p.id 
inner join person per 
on o.person_id=per.id 
order by o.id desc 
limit 100;

Ниже приведен результат объяснения


1   SIMPLE  p   index   PRIMARY FK2EFC6C1E5DE2FC    8   NULL    6886    Using index; Using temporary; Using filesort
1   SIMPLE  o   ref FK67E9050121C383DB,FK67E90501FC44A17C   FK67E90501FC44A17C  8   dev.p.id    58  
1   SIMPLE  per eq_ref  PRIMARY PRIMARY 8   dev.o.person_id 1   Using index

Все таблицы являются InnoDB, а соединения выполняются по первичным и внешним ключам. Кроме этого индексы находятся в столбце email в столбце Person и столбце status в Order.

Количество записей в каждой таблице

Человек: 1 300 000 Товар: 7 000 Заказ: 70 000


mysql sql
person Hussain Fakhruddin    schedule 29.03.2012    source источник
comment
Пожалуйста, опубликуйте свою структуру таблицы, а также   -  person Chetter Hummin    schedule 29.03.2012
comment
Вы добавили индекс для каждого атрибута, который вы используете для отношений? (person_id, id, product_id,..)?   -  person blejzz    schedule 29.03.2012
comment
Пожалуйста, опубликуйте все индексы, которые существуют в этих таблицах.   -  person MatBailie    schedule 29.03.2012
comment
все объединения выполняются по первичному и внешнему ключу, а поскольку типы таблиц относятся к InnoDB, все присоединяемые столбцы должны индексироваться по умолчанию, верно?   -  person Hussain Fakhruddin    schedule 29.03.2012

Ответы (3)


arrow_upward
1
arrow_downward

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

Попробуй это:

select o.* from
(select * order order by id desc limit 100) o
inner join product p 
on o.product_id=p.id 
inner join person per 
on o.person_id=per.id 
order by o.id desc limit 100;

РЕДАКТИРОВАТЬ: это будет работать только в том случае, если существует ограничение, гарантирующее наличие соответствующих строк в таблицах Product и Person.

person Dojo    schedule 29.03.2012
comment
Хотя это, вероятно, ускорит процесс, это не даст точного результата. Что, если одну из этих 100 строк нельзя соединить? Тогда вы получите 99 строк вместо 100. - person Robin Castlin; 29.03.2012
comment
Ты прав. Не думал об этом. Но это также зависит от того, как настроены столы. Если для этих столбцов есть ограничение внешнего ключа, это не будет проблемой. - person Dojo; 29.03.2012
comment
Если вы предполагаете, что все эти таблицы связаны правильно, это соединение вообще не понадобится, так как извлекается только o.*. - person Robin Castlin; 29.03.2012
comment
на самом деле это не просто o.*, а per.email и p.name... извините пропустил это - person Hussain Fakhruddin; 29.03.2012
comment
Попробуйте этот запрос, но с limit 100 внизу и только order by id desc во 2-й строке (ничего внизу). - person Robin Castlin; 29.03.2012
comment
@RobinCastlin - если вы заказываете подзапрос, а затем присоединяетесь к нему, порядок в подзапросе становится случайным. SQL компилируется в план, и план может не присоединяться к данным в порядке запроса. Все это означает, что данные могут появляться в совершенно другом порядке и нуждаться в повторном упорядочении. - person MatBailie; 29.03.2012
comment
@Priyank - На самом деле оператор говорит, что присоединения - это FK к PK. Записи существуют по определению, и каждый заказ соответствует ровно одному продукту и ровно одному человеку. Добавьте per.email and p.name к выбору, и это может быть хорошим ответом. - person MatBailie; 29.03.2012
comment
@Dems: И все же ничего не было сказано о том, могут ли FK обнуляться или нет. (Хотя допускаю, что в данном конкретном случае отсутствующий продукт и/или отсутствующий человек вряд ли имели бы какой-либо смысл.) - person Andriy M; 29.03.2012
comment
@AndriyM - Да, ты прав. То, что было указано, означает только: 1 запись заказа на 0 или 1 запись о человеке и 0 или 1 запись о продукте. Упс. - person MatBailie; 29.03.2012

arrow_upward
0
arrow_downward

да, я понимаю ваш вопрос, в этом случае прежде всего сохраните свой запрос в файле .sql. вы можете использовать утилиту сервера sql под названием «Советник по настройке ядра базы данных» в меню «Инструменты» студии управления сервером Sql.

сначала откройте сервер sql, перейдите к инструментам и выберите опцию «Советник по настройке ядра базы данных». затем выберите файл, который вы сохранили ранее. теперь отметьте свою базу данных, которую вы используете, и таблицу, которая используется в запросе, а затем нажмите «Начать анализ» в главном меню. он показывает вам возможные индексы и статистические данные, которые нужно создать сейчас, чтобы применить эту рекомендацию к вашему запросу, перейдите в меню действий и выберите «Применить рекомендацию», которая создаст индексы и статистические данные для вашей таблицы и сократит время выполнения запроса.

person Nilesh Chauhan    schedule 29.03.2012

arrow_upward
0
arrow_downward

Увеличьте значение этой переменной и перезапустите сервер MySQL read_rnd_buffer_size установка большого значения переменной может значительно улучшить производительность ORDER BY.

Также вы можете сослаться на http://www.mysqlperformanceblog.com/2007/07/24/what-exactly-is-read_rnd_buffer_size/

person Mahesh Patil    schedule 29.03.2012