Все подряд О чем здесь

MySQL SELECT-запросы с большим LIMIT

Выборка данных при больших значениях LIMIT

Многие, кто работает с реляционными SQL-базами сталкивается с проблемами больших таблиц. Одной из таких острых проблем является использование SELECT одновременно с LIMIT [x,] y при больших значениях x и y.

Предположим что у нас есть блог в котором 100000 страниц. Пользователь просматривает архив и для отображения первой страницы архива сервер выполнит примерно такой запрос:

SELECT p.title FROM page p LIMIT 0,10

MySQL считает первые 10 строк из файла, запрос выполнится за милисекунды. Но если пользователь перейдет на 9001-ую страницу архива, запрос будет такой:

SELECT p.title FROM page p LIMIT 90000,10

В этом случае MySQL-движок будет считывать первые 90010 строк и возьмет только последние 10 из них. Как видим, серверу приходится считывать в 9000 раз больше информации чем необходимо. По грубым прикидкам такой запрос будет работать во много раз дольше чем первый, а при длинных строках и/или необходимости делать JOIN - как раз в 9000 раз медленнее и получится.

Ограничение количества страниц в выборке.

Вариант из серии "Не знаем как убрать прыщик - ампутируем руку". Из плюсов - только достижение нашей цели в том чтоб все запросы отдавались быстро. Из очевидных минусов - урезание функционала. Из менее очевидных - при использовании fullscan поиска проблема чтения большого количества строк всё равно не решена, а ограничение сверху возможного количества страниц в выгрузке может привести к проблемам в SEO, т.к. "дальние" страницы информации не будут доступны для поисковых ботов из архивов вашего сайта.

Фильтрация по первичному ключу

Вариант простой и работает превосходно быстро. Использование LIMIT x,y заменяем на WHERE id >= x AND id < (x+y)

SELECT p.title FROM page p WHERE id >= 90000 AND id < 90010

Плюсы:

Минусы:

Использование индексов

Для того чтоб избавиться от недостатков работы фильтра по первичному ключу, мы можем добавить дополнительные индексы. Всё что нужно - пронумеровать записи MySQL-таблицы по номерам выводимых страниц или просто порядковыми номерами. Во втором случае получим возможность легко изменять количество выгружаемых за один раз записей. Из сложностей - необходимо как-то пронумеровать данные. Из минусов - лишний индекс, или несколько индексов в БД, которые к тому же надо пересчитывать "вручную" при изменении записей в таблице. Но если мы речь завели об архивном разделе сайта, то пересчитывать индексы можно по крону, а на главную страницу и последние обновления (первые страницы архива) выводить по актуальным данным. Остаётся найти способ пронумеровать записи. Если делать в лоб - то получится медленно и неоптимально.

Нумерация MySQL-таблицы средствами PHP

Зато все банально просто. Берем отсортированные данные из таблицы, и в цикле обновляем данные в БД:

$rows = $db->fetchAll('SELECT id FROM article ORDER BY title'); $i = 1; foreach ($rows as $row) { $db->query('UPDATE article SET num_index = '.$i.' WHERE id = '.$row['id']); $i++; }

Никогда так не делайте.

Если вспомнить, что любой запрос в MySQL - это по сути реализация for-цикла по данным в БД, то можно это использовать для того чтоб "сделать все как надо" не доставая данные из базы данных.

Правильная нумерация MySQL-таблицы

С точки зрения рассмотрения JOIN-ов как циклов есть отличная статья на хабре. Но не будем забывать что единичный SELECT несет в себе функционал обычного JOIN (т.е. работа в цикле), но без других вложенных циклов. И если совместить это с встроенной поддержкой переменных в SELECT-запросах - мы получаем возможность "пробегаться" по таблице одним лишь SELECT'ом.

Итак, предположим у нас есть таблица article с полями id, title, txt. В таблице 2М (2 000 000) строк. Мы хотим получать "мгновенный" доступ к отсортированной по title таблице к любому ее сегменту. Т.е. нам нужно написать быстрый аналог для стандартного запроса

SELECT id, title, txt FROM article ORDER BY title LIMIT x, y

В таком виде запрос при большом значении x может выполняться до нескольких десятков секунд даже при наличии индекса по title. Более того, т.к. поле title имеет текстовый формат - индекс по нему будет занимать много дискового пространства, а при миллионах записей индекс по одному лишь столбцу легко перевалит за несколько сотен мегабайт и если вылезет из пределов key_buffer_size в разы замедлит вышеприведенный запрос.

Итак, добавим в таблицу дополнительный столбец.

ALTER TABLE article ADD COLUMN num INT UNSIGNED NOT NULL DEFAULT 0 ADD UNIQUE INDEX (num);

Инициализируем переменную

SET @a = 0;

Затем создадим временную таблицу с пронумерованными id записей.

CREATE TEMPORARY TABLE tmp_art_id ENGINE = MyISAM SELECT id, @a := @a+1 num FROM page ORDER BY title;

Далее обновим основную таблицу.

UPDATE article a, tmp_art_id t SET a.num = t.num WHERE a.id = t.id

Здесь хотелось бы сделать оговорку почему приходится прибегать к созданию временной таблицы, ведь мы могли бы сразу сделать аналогичный UPDATE, вписав num = (@a := @a+1) и добавив ORDER BY title. На самом деле вы не получите ожидаемого результата. Все дело в том, что порядок выполнения update-запроса отличается от порядка выполнения SELECT-запроса, поэтому в столбец num не попадут ожидаемые данные. Судя по официальной документации часть "SELECT" у SELECT-запроса рассчитывается в момент отправки клиенту, именно поэтому можем манипулировать переменными уже после сортировки.

На самом деле нет 100%-ной уверенности что и этот вариант будет безотказно работать, т.к. оптимизатор MySQL может меняться от версии к версии и, если, по какой-то причине, рассчет выражения @a := @a+1 будет выполняться до сортировки (как это делается при UPDATE), мы получим не то что ожидаем. Об этом же говорится и в официальной документации:

As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed. The order of evaluation for expressions involving user variables is undefined and may change based on the elements contained within a given statement; in addition, this order is not guaranteed to be the same between releases of the MySQL Server. In

SELECT @a, @a:=@a+1, ...,

you might think that MySQL will evaluate @a first and then do an assignment second. However, changing the statement (for example, by adding a GROUP BY, HAVING, or ORDER BY clause) may cause MySQL to select an execution plan with a different order of evaluation.

Этот способ я проверял на MySQL 5.3, MySQL 5.4 и MySQL 5.5, т.к. в настоящее время весьма успешно его использую на разных проектах.

Вся SQL-последовательность действий выглядит так:

#Добавляем столбец нумерации в основную таблицу ALTER TABLE article ADD COLUMN num INT UNSIGNED NOT NULL DEFAULT 0 ADD UNIQUE INDEX (num); #инициализируем переменную SET @a := 0; #Создаем временную таблицу, где для каждого исходного id #будет его порядковый номер для отсортиированной таблицы CREATE TEMPORARY TABLE tmp_art_id ENGINE = MyISAM SELECT id, @a := @a+1 num FROM page ORDER BY title; #Обновляем исходную таблицу UPDATE article a, tmp_art_id t SET a.num = t.num WHERE a.id = t.id;

Читайте так же как с помощью TEMPORARY таблиц оптимизировать JOIN