Оптимізовуємо LIMIT offset

  Скрізь, де використовується LIMIT offset для великих таблиць, рано чи пізно починаються гальма. Запити виду
 
 
SELECT * FROM test_table ORDER BY id LIMIT 100000, 30

можуть виконаються дуже довго. Наприклад, в моєму випадку, на одному з сайтів кол коментарів перевалило за 200к і посторінкова навігація по коментарях почала відчутно гальмувати, а в mysql-slow.log все частіше стали потрапляти запити з часом виконання 3-5сек.
 
Проблема полягає в тому, що використовуючи LIMIT 100000, 30 — mysql спочатку пройдеться по перших 100 000 записів і тільки потім вибере потрібні 30. Уникнути цього досить просто, достатньо використовувати подзапрос виду, який в загальному випадку виглядає так:
 
SELECT * FROM test_table JOIN (SELECT id FROM test_table ORDER BY id LIMIT 100000, 30) as b ON b.id = test_table.id

Давайте розглянемо конкретний приклад. У моєму випадку використовується движок DLE і в ньому запит виглядає наступним чином:
 
SELECT dle_comments.*... FROM dle_comments LEFT JOIN dle_post ON dle_comments.post_id=dle_post.id LEFT JOIN dle_users ON dle_comments.user_id=dle_users.user_id  ORDER BY id desc LIMIT 101000,30

Виправлений запит виглядає так:
 
SELECT dle_comments.*... FROM dle_comments LEFT JOIN dle_post ON dle_comments.post_id=dle_post.id LEFT JOIN dle_users ON dle_comments.user_id=dle_users.user_id JOIN (select id FROM dle_comments ORDER BY id desc LIMIT 101000,30 ) as t ON t.id = dle_comments.id

На графіку можна побачити результат такої заміни:
 
 
Як видно, з використанням JOIN продуктивність зберігається на потрібному рівні не залежно від того як далеко забрався користувач в нетрі сайту використовуючи посторінкову навігацію.
 
PS. Фікс для DLE для коментарів (аналогічним чином можна зробити для всіх навігацій). У файлі comments.class.php
знайти
 
$sql_result = $this->db->query(  $this->query . " LIMIT " . $this->cstart . "," . $this->comments_per_pages );

замінити цю строчку на:
 
$sql_result = $this->db->query( str_replace("ORDER BY id desc", "JOIN (select id FROM " . PREFIX . "_comments ORDER BY id desc" . " LIMIT " . $this->cstart . "," . $this->comments_per_pages .") as t ON t.id = " . PREFIX . "_comments.id",$this->query) );

  
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.