Не нравятся результаты поиска? Попробуйте другой поиск!
DLE FAQ » Все вопросы » MySQL » Как оптимизировать запрос MySQL?

Как оптимизировать запрос MySQL?


     24.03.2020    Все вопросы » MySQL    131

вопрос
Имеется сайт на DLE 14.0 и база на > 100 000 записей. Если перейти к примеру на 8000 страницу, то запрос
[query] => SELECT p.id, p.autor, p.date, p.short_story, CHAR_LENGTH(p.full_story) as full_story, p.xfields, p.title, p.category, p.alt_name, p.comm_num, p.allow_comm, p.fixed, p.tags, e.news_read, e.allow_rate, e.rating, e.vote_num, e.votes, e.view_edit, e.editdate, e.editor, e.reason FROM dle_post p LEFT JOIN dle_post_extras e ON (p.id=e.news_id) WHERE approve=1 AND allow_main=1 ORDER BY date DESC LIMIT 71550,10
[time] => 3,8447678089142
[num] => 1

занимает аж 3,8 секунды, что несоизмеримо много, если бы этот запрос был выполнен, например, на 40 странице. Где-то читал, что MySQL плохо работает с большими выборками по LIMIT. Как его можно оптимизировать?

Ответа пока нет


26 комментариев

voronoff_dev
Юзер

voronoff_dev - 24 марта 2020 14:02 -

Вот статья как раз под ваш случай https://habr.com/ru/post/217521/

qwerty123123
Юзер

qwerty123123 - 24 марта 2020 16:47 -

Спасибо сейчас гляну

dj-avtosh
PHP-developer

dj-avtosh - 24 марта 2020 14:14 -

Здравствуйте, запрос тормозит скорее всего из-за текстовых полей (они не индексируются):
p.short_story, CHAR_LENGTH(p.full_story) as full_story



Попробуйте их отбросить и с использованием SQL_NO_CACHE повторить запрос и посмотреть время выполнения.

ЯД: 41001679231462
Заказы в telegram: @web_lord_moon
В телегу писать сразу задачу и бюджет.

qwerty123123
Юзер

qwerty123123 - 24 марта 2020 16:43 -


Здравствуйте, проверил, убрав текстовые поля. Время выполнения стало меньше, но оно все равно достаточно велико.
По поводу индексов. Посмотрел, в phpMyAdmin указано что для этих полей стоит полнотекстовый индекс.

Может имеет смысл как-то переписать логику кода с использованием PHP, например, такой запрос выполняется практически мгновенно:
[query] => SELECT p.id, p.autor, p.date, p.short_story, CHAR_LENGTH(p.full_story) as full_story, p.xfields, p.title, p.category, p.alt_name, p.comm_num, p.allow_comm, p.fixed, p.tags, e.news_read, e.allow_rate, e.rating, e.vote_num, e.votes, e.view_edit, e.editdate, e.editor, e.reason FROM dle_post p LEFT JOIN dle_post_extras e ON (p.id=e.news_id) WHERE approve=1 AND allow_main=1 AND id > 78000 ORDER BY date DESC LIMIT 10
[time] => 0,0016498565673828

dj-avtosh
PHP-developer

dj-avtosh - 24 марта 2020 17:02 -

Полнотекстовый индекс не используется для оптимизации, посмотрите какие еще поля текстовые в таблице dle_post_extras и уберите их так же из запроса.

Затем попробуйте выполнить запрос такой и напишите сюда время выполнения:

SELECT SQL_NO_CACHE p.id FROM dle_post p LEFT JOIN dle_post_extras e ON (p.id=e.news_id) WHERE approve=1 AND allow_main=1 ORDER BY date DESC LIMIT 71550,10

ЯД: 41001679231462
Заказы в telegram: @web_lord_moon
В телегу писать сразу задачу и бюджет.

qwerty123123
Юзер

qwerty123123 - 24 марта 2020 19:20 -

Получилось около 0,47214794158936 с LIMIT 71550,10

dj-avtosh
PHP-developer

dj-avtosh - 24 марта 2020 19:38 -

вот теперь нужно полученные отсюда id вставить в исходный запрос (который в заголовке этого вопроса) как WHERE p.id IN (список через запятую) и убрать limit.

ЯД: 41001679231462
Заказы в telegram: @web_lord_moon
В телегу писать сразу задачу и бюджет.

qwerty123123
Юзер

qwerty123123 - 24 марта 2020 22:59 -

SELECT p.id, p.autor, p.date, p.short_story, CHAR_LENGTH(p.full_story) as full_story, p.xfields, p.title, p.category, p.alt_name, p.comm_num, p.allow_comm, p.fixed, p.tags, e.news_read, e.allow_rate, e.rating, e.vote_num, e.votes, e.view_edit, e.editdate, e.editor, e.reason FROM dle_post p LEFT JOIN dle_post_extras e ON (p.id=e.news_id) JOIN (SELECT id FROM dle_post WHERE allow_main=1 AND approve=1 ORDER BY date DESC LIMIT 85150,10) as l ON p.id=l.id

Такой запрос отработал за 0.2768 секунд. Конечно результат на лицо, но я думаю если в базе появится еще 200К записей, то эта цифра может перевалить за 1 секунду

dj-avtosh
PHP-developer

dj-avtosh - 24 марта 2020 23:01 -

прочитайте внимательно что я вам предлагал. Сперва выполняете запрос первый и пооучаете id, потом вторрой, в который id вставляете.

ЯД: 41001679231462
Заказы в telegram: @web_lord_moon
В телегу писать сразу задачу и бюджет.

qwerty123123
Юзер

qwerty123123 - 24 марта 2020 23:10 -

Первый запрос с выборкой ID выполнился за 0.2676, а второй с IN за 0.0006. Итого - 0,2682 секунд

dj-avtosh
PHP-developer

dj-avtosh - 24 марта 2020 23:25 -

вот такими двумя запросами нагрузки и не будет в итоге даже при миллионе, т.к. в ход будет идти индекс.

ЯД: 41001679231462
Заказы в telegram: @web_lord_moon
В телегу писать сразу задачу и бюджет.

dj-avtosh
PHP-developer

dj-avtosh - 24 марта 2020 17:04 -

тот человек, который вам написал про id > вряд ли дружит с логикой, не факт что в вашей таблице все начинается с id 1.

ЯД: 41001679231462
Заказы в telegram: @web_lord_moon
В телегу писать сразу задачу и бюджет.

qwerty123123
Юзер

qwerty123123 - 24 марта 2020 19:31 -

Согласен с вами.
А если переделать пагинацию и, например, передавать в ссылке ID публикации на которой остановился пользователь (убрав страницы и сделав кнопки вперед/назад)?

cortin
Юзер

cortin - 24 марта 2020 16:22 -

Я решал такие вопросы через WHERE ID > .... LIMIT 50

qwerty123123
Юзер

qwerty123123 - 24 марта 2020 16:46 -

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

cortin
Юзер

cortin - 24 марта 2020 19:01 -

SELECT p.id FROM dle_post p LEFT JOIN dle_post_extras e ON (p.id=e.news_id) WHERE approve=1 AND allow_main=1 ORDER BY date DESC LIMIT 1

cortin
Юзер

cortin - 24 марта 2020 19:03 -

но как уже писали выше, пагинацию делать надо иначе, мне для крона это подходило, чтобы сделать пагинацию такую хитрую, надо подумать :) я пока мимо, домой поеду :)

cortin
Юзер

cortin - 24 марта 2020 16:27 -

Хотел добавить на больших базах, мы например ограничивали пагинацию, макс 200 страниц ставили, иначе гугл индекс заходит в глубь и начинаются тормоза, ведь пока mysql подгрузит с диска все пока обработает... ух. Конечно если второй раз зайти то все быстро ведь уже все в mysql кэше, а так только WHERE ID > ... помогает

lutskboy
Эксперт

lutskboy - 24 марта 2020 20:00 -

согласен на счет WHERE ID >...
сам такое делал. только без пагинации. у меня было кнопка типа "Показать еще..."
так совсем нет никакой нагрузки даже если там миллионы записей. правда какой дурак будет нажимать на ету кнопку чтоб добраться до последних записей
такой вариант идеален для чата чтоб получить историю переписки скажем 5 лет назад

в вашем случае лучше ограничить например 1000страниц

cortin
Юзер

cortin - 25 марта 2020 10:53 -

Вот пример как можно сделать, псевдокод

recordsPerPage = 50;
for( page = 1; page < 200; page++ )
{
    if ( page == 1 )
    {
        pageID = SELECT id FROM dle_posts ORDER BY id DESC LIMIT 1;
    }else{
        pageID = SELECT id FROM dle_posts WHERE id < prevID ORDER BY id DESC LIMIT recordsPerPage, 1;
    }
    // tut formiruem paginaciju dlja stranici, naprimer https://site.com/page/<pageID>
    prevID = pageID;
}

qwerty123123
Юзер

qwerty123123 - 25 марта 2020 15:13 -

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

cortin
Юзер

cortin - 25 марта 2020 15:26 -

подключите сфинкс

dj-avtosh
PHP-developer

dj-avtosh - 25 марта 2020 17:51 -

тогда лучше уж postgres?

ЯД: 41001679231462
Заказы в telegram: @web_lord_moon
В телегу писать сразу задачу и бюджет.

cortin
Юзер

cortin - 25 марта 2020 15:38 -

Еще хотел добавить что на больших базах придется использовать memcache, redis ( его нет но можно установить ) и допилить эти страници через кеш, чтобы в бэкграунде крутился один процесс которые все эти страница -> ид находил, в одном потоке это уже не нагрузка. а остальные будут уже бегать по страницам, для это нужно или кроном или без крона через popen запускать скрипт.

У нас стоят серьезные сервера, но все равно мы сталкнулись что на больших базах не все в памяти и тормозит, но правильно созданый кэш это решение

dj-avtosh
PHP-developer

dj-avtosh - 25 марта 2020 17:50 -

Почему это нет memcache?

ЯД: 41001679231462
Заказы в telegram: @web_lord_moon
В телегу писать сразу задачу и бюджет.

cortin
Юзер

cortin - 25 марта 2020 18:23 -

я про редис, в дле 14 я не нашел сорсов где в качестве кеша используется редиска

Чтобы комментировать - войдите или зарегистрируйтесь на сайте

Похожие вопросы

наверх