Есть ли способ сделать эти SQL-запросы быстрее?

У меня есть база данных MySQL и таблица, содержащая около 128 000 строк (довольно мало, насколько я понимаю). У меня также есть приложение, подключенное к нему, которое настроено для пейджинга. Мои SQL-запросы выглядят примерно так:

SELECT * FROM Documents WHERE PortfolioId = ? LIMIT ?,?
SELECT * FROM Documents WHERE PortfolioId = ? ORDER BY Date DESC LIMIT ?,?

Моя проблема двояка. Во-первых, независимо от того, какой запрос используется, чем выше первое число «LIMIT», тем медленнее возвращается запрос, что в конечном итоге приводит к неприемлемой задержке. Например, если я зайду в phpmyadmin и выполню:

SELECT * FROM Documents WHERE PortfolioId = 1 LIMIT 0,20

Запрос занимает 0,001 секунды. Однако, когда я выполняю это:

SELECT * FROM Documents WHERE PortfolioId = 1 LIMIT 120000,20

Запрос занимает 14,8 секунды.

Моя вторая проблема заключается в том, что второй запрос, в котором я упорядочиваю по дате (которая также проиндексирована в таблице), заставляет соответствующие запросы выполняться намного дольше (0,1 секунды для первого примера, 2 минуты и 23 секунды< /em> для второго примера).

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


mysql sql database database-performance
person J Ellis    schedule 15.07.2012    source источник
comment
Если вы делаете это для подкачки, вы можете сохранить последний возвращенный идентификатор из таблицы Documents и использовать его в последующих запросах. то есть SELECT * FROM Documents WHERE PortfolioId = 1 AND DocumentId > ? LIMIT 20. Однако обратите внимание, что это даст результаты, отличные от вашего текущего метода, если таблица обновляется между запросами.   -  person Michael Mior    schedule 15.07.2012
comment
Единственная проблема заключается в том, что если я упорядочиваю по разным вещам (например, по дате в моем примере), последний возвращенный идентификатор из таблицы «Документы» будет бесполезен.   -  person J Ellis    schedule 15.07.2012
comment
Вы можете использовать аналогичный подход с датой или любым другим выражением, которое вы хотите использовать для заказа.   -  person Michael Mior    schedule 15.07.2012
comment
@Майкл Миор - я попробую. Это не будет работать так просто, как вы говорите, потому что многие документы могут иметь одну и ту же дату. Поэтому, если у меня есть 100 документов, датированных 14 июля 2012 года, а размер моей страницы всего 20, я не могу просто взять страницу, которая заканчивается 14 июля, и на следующей странице указать, чтобы она давала мне строки с большими датами. Однако, если я упорядочу сначала по дате, а затем по идентификатору в пределах даты, я смогу построить запрос после этого (что-то, что ограничивает только Id › x, если дата одинакова, и возвращает все идентификаторы в противном случае).   -  person J Ellis    schedule 15.07.2012
comment
@Nerd-Herd — создание индекса для всех столбцов не помогает.   -  person J Ellis    schedule 15.07.2012
comment
@user1319571 user1319571 Очень хороший момент. У меня есть привычка часто добавлять идентификатор в порядок запросов, чтобы обеспечить согласованность порядка. Как вы упомянули, что-то вроде WHERE Date >= LAST_DATE AND DocumentId > LAST_ID должно работать, если вы используете ORDER BY Date, DocumentId.   -  person Michael Mior    schedule 15.07.2012
comment
@Michael Mior - я создал несколько новых запросов, используя WHERE (Date = X AND Id › Y) ИЛИ (Date › x) ORDER BY Date ASC, Id ASC LIMIT 20, и скорость намного лучше. Единственная проблема, с которой я столкнулся, заключается в том, что изначально пользователь должен был иметь возможность сортировать информацию по широкому кругу полей, а не только по дате, в порядке возрастания или убывания. Возможно, мне придется отключить эту функцию на данный момент, пока я не придумаю еще лучшее решение или не выясню, как справиться со сложностями, которые могут возникнуть.   -  person J Ellis    schedule 16.07.2012
comment
@Michael Mior. Кроме того, запрос, который вы предлагаете в своем комментарии, не будет работать, поскольку запись может иметь более позднюю дату, но меньший идентификатор, и в этом случае она не будет возвращена.   -  person J Ellis    schedule 16.07.2012
comment
@ user1319571 Вы правы, здесь много сложностей :)   -  person Michael Mior    schedule 16.07.2012

Ответы (1)


arrow_upward
1
arrow_downward

LIMIT всегда будет работать медленнее по мере того, как вы «продвигаетесь дальше» в наборе результатов. По сути, это не слишком отличается от извлечения первых X строк и их отбрасывания, потому что вас интересует только X+1 -> X+10. Единственная разница в том, что нет необходимости тратить время на отправку необработанных строк по сети — MySQL по-прежнему должен построить весь набор результатов, отсортировать его, а затем прокрутить вниз до точки смещения LIMIT. Чем глубже ваше смещение, тем больше работы должен выполнить MySQL, а затем, по сути, просто выбросить.

person Marc B    schedule 15.07.2012
comment
Хорошо, если это так, то как разработчики, разбивающие миллионы строк, делают свои запросы, чтобы не столкнуться с этой проблемой? - person J Ellis; 15.07.2012
comment
кеши результатов запроса, поэтому вы не выполняете весь запрос повторно каждый раз, когда меняете страницы, улучшаете дизайн базы данных, «большие» базы данных, чем mysql, и т. д. - person Marc B; 15.07.2012
comment
Можете ли вы подробно рассказать об этих методах, чтобы предоставить что-то полезное для моего случая? Я не понимаю, как в этой ситуации помогут лучшие конструкции баз данных или «большие» базы данных, поскольку мы имеем дело с одной таблицей, без объединений и только с 128 000 записей, но может ли использование какой-либо другой базы данных значительно ускорить запросы LIMIT? Что касается кешированных результатов запроса, они обычно кэшируют результаты всей таблицы и используют это, чтобы просто вернуть любую нужную страницу? Кэшируются ли результаты в базе данных или на среднем уровне? - person J Ellis; 16.07.2012