Не нравятся результаты поиска? Попробуйте другой поиск!

DLE FAQ » Все вопросы » MySQL » Как оптимизировать запрос MySQL?

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

qwerty123123 24.03.2020 Все вопросы » MySQL 2200

вопрос

Имеется сайт на 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 - 2020-03-2424 марта 2020 14:02 - #75280

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

Юзер

qwerty123123 - 2020-03-2424 марта 2020 16:47 - #75288

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

PHP-developer

3199

dj-avtosh - 2020-03-2424 марта 2020 14:14 - #75281

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

p.short_story, CHAR_LENGTH(p.full_story) as full_story

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

По заказам пишем сюда: @Rud00y

ЯД: 41001679231462
Заказы в telegram (ремонт модулей, оптимизация нагрузок и т.п.):
В телегу писать сразу задачу и бюджет.

Юзер

qwerty123123 - 2020-03-2424 марта 2020 16:43 - #75286

Здравствуйте, проверил, убрав текстовые поля. Время выполнения стало меньше, но оно все равно достаточно велико.
По поводу индексов. Посмотрел, в 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

PHP-developer

3199

dj-avtosh - 2020-03-2424 марта 2020 17:02 - #75289

Полнотекстовый индекс не используется для оптимизации, посмотрите какие еще поля текстовые в таблице 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

Юзер

qwerty123123 - 2020-03-2424 марта 2020 19:20 - #75295

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

PHP-developer

3199

dj-avtosh - 2020-03-2424 марта 2020 19:38 - #75297

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

Юзер

qwerty123123 - 2020-03-2424 марта 2020 22:59 - #75299

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 секунду

PHP-developer

3199

dj-avtosh - 2020-03-2424 марта 2020 23:01 - #75300

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

Юзер

qwerty123123 - 2020-03-2424 марта 2020 23:10 - #75301

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

PHP-developer

3199

dj-avtosh - 2020-03-2424 марта 2020 23:25 - #75303

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

PHP-developer

3199

dj-avtosh - 2020-03-2424 марта 2020 17:04 - #75290

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

Юзер

qwerty123123 - 2020-03-2424 марта 2020 19:31 - #75296

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

Юзер

cortin - 2020-03-2424 марта 2020 16:22 - #75282

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

Юзер

qwerty123123 - 2020-03-2424 марта 2020 16:46 - #75287

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

Юзер

cortin - 2020-03-2424 марта 2020 19:01 - #75292

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 - 2020-03-2424 марта 2020 19:03 - #75293

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

Юзер

cortin - 2020-03-2424 марта 2020 16:27 - #75283

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

Эксперт

1316

lutskboy - 2020-03-2424 марта 2020 20:00 - #75298

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

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

Юзер

cortin - 2020-03-2425 марта 2020 10:53 - #75309

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


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 - 2020-03-2425 марта 2020 15:13 - #75329

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

Юзер

cortin - 2020-03-2425 марта 2020 15:26 - #75334

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

PHP-developer

3199

dj-avtosh - 2020-03-2425 марта 2020 17:51 - #75346

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

Юзер

cortin - 2020-03-2425 марта 2020 15:38 - #75336

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

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

PHP-developer

3199

dj-avtosh - 2020-03-2425 марта 2020 17:50 - #75345

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