Pull to refresh

Ускоряем выборку произвольных записей MySQL

Reading time 3 min
Views 33K
Последнее время оживилась публика с вопросом случайной выборки из таблицы. Решений по оптимизации полно, и нового сейчас я вам наверное ничего не покажу, просто напомню про основные методы оптимизации — упрощение запроса и индексацию. Без предисловий про фриленсеров, сразу к делу ;)

Создаю таблицу:
CREATE TABLE `a` (
`id` int(8) unsigned NOT NULL AUTO_INCREMENT,
`md5` char(32) NOT NULL
PRIMARY KEY (`id`)
)
INSERT INTO `a` (`id`) VALUES (null),(null),(null),(null)... и так 163712 раза ;)
UPDATE `a` SET md5 = MD5(`id`);

Такой таблицы на моём допотопном компьютере достаточно, чтобы проверить эффективность.
Вот простая выборка ORDER BY RAND:
SELECT * FROM `a` ORDER BY RAND() LIMIT 10; -> (10 rows, Query took 0.3345 sec)
SELECT * FROM `a` ORDER BY RAND() LIMIT 10; -> (10 rows, Query took 0.2538 sec)
SELECT * FROM `a` ORDER BY RAND() LIMIT 10; -> (10 rows, Query took 0.2299 sec)

Создаю индексированное поле для того чтобы не делать full-scan всей таблицы:
ALTER TABLE `a` ADD `f` SMALLINT(3) UNSIGNED NOT NULL, ADD INDEX (`f`);
UPDATE `a` SET `f` = RAND()*1000;
Число 1000 — это основной «ускоряющий» фактор. Им я уменьшаю таблицу в которой будет проходить обыкновенный ORDER BY RAND в 1000 раз. При таблице 163712 рядов должно получится примерно 164 ряда на один f. Проверяем:
SELECT COUNT(1) FROM `a` WHERE `f` = 123; -> 169

Рандом есть рандом, равномерное распределение было бы хорошо, но это фантастика (хотя знаете, можно воспользоваться первыми знаками MD5(`id`) и перевести его в INT, равномернее некуда). Итак, сейчас на один f мне попадались и по 200 рядов и по 100. Если этот показатель со временем становится неэффективным, то всегда можно увеличить фактор и получить, скажем, по 25-75 рядов на индекс. Главное чтобы там было как минимум столько рядов, сколько нам нужно вытаскивать рандомно. Колонку f можно перегенерировать периодически, или после 1000 обращений к таблице. При вставке новые ряды получают значение f = 0, что на качество выборок сильно не повлияет, либо задавайте рандомные значения при вставках тоже.

Делаю тестовую выборку 10 рядов используя то что наиндексировал:
SELECT * FROM `a` WHERE `f` = 231 ORDER BY RAND() LIMIT 10; -> (10 rows, Query took 0.0801 sec)
SELECT * FROM `a` WHERE `f` = 231 ORDER BY RAND() LIMIT 10; -> (10 rows, Query took 0.0017 sec)
Ага, второй раз получилась пересортированная выборка из кэша mysql. Если повторение результатов не сильно страшно, то сгодится и такой более шустрый результат, но лучше менять число f при каждом запросе.

Повторяю тест, изменяя f:
SELECT * FROM `a` WHERE `f` = 396 ORDER BY RAND() LIMIT 10; -> (10 rows, Query took 0.0147 sec)
SELECT * FROM `a` WHERE `f` = 753 ORDER BY RAND() LIMIT 10; -> (10 rows, Query took 0.0020 sec)
SELECT * FROM `a` WHERE `f` = 189 ORDER BY RAND() LIMIT 10; -> (10 rows, Query took 0.0019 sec)
SELECT * FROM `a` WHERE `f` = 945 ORDER BY RAND() LIMIT 10; -> (10 rows, Query took 0.0235 sec)

В общем производительность выборки поднялась в 120 раз и это без всякого рода усложнений. Я вижу в таком решении массу удобств:
  • не нужно лазить далеко в дебри и ломать голову «блин как эта у меня была… ах да процедура».
  • простая интеграция ;) код запроса был усложнён всего одним условием.
  • ну и третье назовём расширяемостью: добавь свои условия и ты ещё уменьшишь размер скана, увеличив тем самым скорость выборки.
Если одного промежутка 160 рядов нам не достаточно, то можно включить столько промежутков сколько угодно:
SELECT * FROM `a` WHERE `f` IN (100,500) ORDER BY RAND() LIMIT 10;

Более жизненный пример


Для такого примера возьмём верхний комментарий из соседнего поста, который решается так. Эмитируем таблицу RSS-фидов, добавив поле feed содержащее номер фида:
ALTER TABLE `a` ADD `feed` TINYINT(1) UNSIGNED NOT NULL, ADD INDEX (`feed`);
UPDATE `a` SET feed = RAND()*9;
А теперь, собственно, финт ушами:
(SELECT * FROM `a` WHERE `f` = 283 AND `feed` = 0 ORDER BY RAND() LIMIT 10)
UNION (SELECT * FROM `a` WHERE `f` = 283 AND `feed` = 1 ORDER BY RAND() LIMIT 10)
UNION (SELECT * FROM `a` WHERE `f` = 283 AND `feed` = 2 ORDER BY RAND() LIMIT 10)
UNION (SELECT * FROM `a` WHERE `f` = 283 AND `feed` = 3 ORDER BY RAND() LIMIT 10)
UNION (SELECT * FROM `a` WHERE `f` = 283 AND `feed` = 4 ORDER BY RAND() LIMIT 10)
UNION (SELECT * FROM `a` WHERE `f` = 283 AND `feed` = 5 ORDER BY RAND() LIMIT 10)
UNION (SELECT * FROM `a` WHERE `f` = 283 AND `feed` = 6 ORDER BY RAND() LIMIT 10)
UNION (SELECT * FROM `a` WHERE `f` = 283 AND `feed` = 7 ORDER BY RAND() LIMIT 10)
UNION (SELECT * FROM `a` WHERE `f` = 283 AND `feed` = 8 ORDER BY RAND() LIMIT 10)
UNION (SELECT * FROM `a` WHERE `f` = 283 AND `feed` = 9 ORDER BY RAND() LIMIT 10)
ORDER BY feed; -> (97 rows, Query took 0.7973 sec)
С другим f -> (99 rows, Query took 0.0093 sec)
С другим f -> (98 rows, Query took 0.0197 sec)
Тут желательно выбирать больше чем 10 рядов для верности ;) и фильтровать лишнее на PHP.

На PHP также желательно задавать число f, чтобы не делать по два запроса к MySQL серверу. Хотя, это не критично. Такое тоже будет работать очень быстро:
SET @rnd = RAND(); SELECT * FROM `a` WHERE `f` = @rnd ORDER BY RAND() LIMIT 10;

Как вы видите, не только лишь усложнением можно добитсья оптимизации (в данной статье я оптимизировал скорость). Теперь вопрос вам на подумать, а мне в будущей статье изложить. Как можно оптимизировать качество произвольных выборок, прежде всего избавится от повторений? ;)

С уважением,
Маям.
Tags:
Hubs:
+41
Comments 22
Comments Comments 22

Articles