Принцип повышения приоритетов в циклах с выборками, эффективные рандомные sql выборки с условиями

Хочу показать и зафиксировать принцип рандомных выборок особенно эффективных на огромных таблицах, где не всё оптимизируется и не всё возможно оптимизировать. А иногда условий и хотелок выборок может быть столько, что их априори просто невозможно все оптимизировать — не хватит ключей в бд. Да и ключи — это тоже лишняя нагрузка на операциях записи с таблицами. Но при этом нужно сделать выборку по определенному условию и сделать её быстрой.

Для начала опишу общий принцип в целом по выборке рандомных записей без условий, например такой код php:

list($min_id, $max_id) = $dbh->query("SELECT MIN(`ID`), MAX(`ID`) FROM wp_posts")->fetch(\PDO::FETCH_NUM);
while (true) {
    $seed = mt_rand();
    $id = Random::gen($min_id, $max_id, $seed);
    if (($row = $dbh->query("SELECT * FROM wp_posts WHERE ID = $id AND comment_count < $n")->fetch()) && $row->post_status == 'publish' && $row->post_type == 'post') {
        break;
    }
}

Таким способом мы быстро и эффективно сделаем выборку 1 случайной записи из таблицы будь она хоть размером в 100 млн. записей. Единственный минус такого способа — если в таблице существуют огромные «пробелы» между id записей. В этом случае нужно хотя бы предусматривать остановку цикла.

В данном случае мы не привязываем выборку к какому-то параметру $seed а сделали ее полностью рандомной. Но при желании мы можем назначить его изначально, например path запроса:

list($min_id, $max_id) = $dbh->query("SELECT MIN(`ID`), MAX(`ID`) FROM wp_posts")->fetch(\PDO::FETCH_NUM);
$seed = strtok($_SERVER['REQUEST_URI'], '?');
while (true) {
    $id = Random::gen($min_id, $max_id, $seed);
    if (($row = $dbh->query("SELECT * FROM wp_posts WHERE ID = $id AND comment_count < $n")->fetch()) && $row->post_status == 'publish' && $row->post_type == 'post') {
        break;
    }
    $seed = $id;
}

И у нас всегда на странице будет один и тот же фиксированный рандом, причем он будет разным на разных страницах.

Используемые методы для формирования рандома: https://gist.github.com/avtobys/d0378bd3cd4eee8d3ba9f4868e149f81

Кстати советую класс, там не только цифры, но и строки с массивами можно рандомить.

А теперь рассмотрим пример посложнее. Нам нужно выбрать 1 рандомный пост. При этом нужно отдать приоритет выборки посту с минимальным количеством комментариев. Наше условие может сделать цикл бесконечным или как минимум долгим, поэтому каждую итерацию мы будем его расширять и делать наш приоритет менее «жестким». Вот код:

list($min_id, $max_id) = $dbh->query("SELECT MIN(`ID`), MAX(`ID`) FROM wp_posts")->fetch(\PDO::FETCH_NUM);
$i = $n = 0;
while (true) {
    $id = Random::gen($min_id, $max_id, mt_rand());
    $i++;
    $n = ceil($i / 10);
    if (($row = $dbh->query("SELECT * FROM wp_posts WHERE ID = $id AND comment_count < $n")->fetch()) && $row->post_status == 'publish' && $row->post_type == 'post') {
        break;
    }
}

Тут каждую итерацию мы инкрементим переменную $i которая задаёт впоследствии $n = количество постов, которое нас устраивает. Первые 10 запросов будут осуществлять попытки выбрать запись с 0 комментариев, а далее каждые 10 следующих итераций условие становится менее жестким по такой схеме:

итерации:комменты

• 1-10:0
• 11-20:1
• 21-30:2
• 31-40:3
• 41:50:4
• и так далее, чем больше итераций, тем мягче условие.

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

 

while (true) {
    $id = Random::gen($min_id, $max_id, mt_rand());
    $i++;
    $n = ceil($i / 10);
    if (($row = $dbh->query("SELECT * FROM wp_posts WHERE ID = $id AND comment_count < $n")->fetch()) && $row->post_status == 'publish' && $row->post_type == 'post') {
        break;
    }
    if ($i > 100) {
        exit;
    }
}