Comment puis-je sélectionner efficacement des lignes aléatoires dans une grande table PostgreSQL ?

La sélection aléatoire de lignes dans de grandes bases de données telles que PostgreSQL peut être une tâche gourmande en performances. Cet article explore deux méthodes courantes pour atteindre efficacement cet objectif et discute de leurs avantages et inconvénients.

Méthode 1 : Filtrer par valeur aléatoire

<code class="language-sql">select * from table where random() < 0.01;</code>

Cette méthode trie les lignes de manière aléatoire, puis filtre en fonction d'un seuil. Cependant, cela nécessite une analyse complète de la table et peut être lent pour les grands ensembles de données.

Méthode 2 : Trier par valeurs aléatoires et limiter les résultats

<code class="language-sql">select * from table order by random() limit 1000;</code>

Cette méthode trie les lignes de manière aléatoire et sélectionne les n premières lignes. Elle fonctionne mieux que la première méthode, mais elle présente une limitation : elle peut ne pas être en mesure de sélectionner un sous-ensemble aléatoire lorsqu'il y a trop de lignes dans le groupe de lignes.

Solutions d'optimisation pour les grands ensembles de données

Pour les tables comportant un grand nombre de lignes (comme 500 millions de lignes dans votre exemple), l'approche suivante fournit une solution optimisée :

<code class="language-sql">WITH params AS (
   SELECT 1       AS min_id,           -- 最小ID（小于等于当前最小ID）
        5100000 AS id_span          -- 四舍五入。(max_id - min_id + buffer)
    )
SELECT *
FROM  (
   SELECT p.min_id + trunc(random() * p.id_span)::integer AS id
   FROM   params p
        , generate_series(1, 1100) g  -- 1000 + buffer
   GROUP  BY 1                        -- 去除重复项
) r
JOIN   big USING (id)
LIMIT  1000;                          -- 去除多余项</code>

Cette requête utilise l'index de la colonne ID pour une récupération efficace. Il génère une série de nombres aléatoires dans l'espace des identifiants, garantissant que les identifiants sont uniques, et joint les données à la table principale pour sélectionner le nombre de lignes requis.

Autres considérations

Requête de limite :
Il est crucial que la colonne d'ID de table comporte relativement peu d'espaces pour éviter d'avoir besoin de tampons volumineux lors de la génération de nombres aléatoires.

Vue matérialisée :
Si vous devez accéder de manière répétée à des données aléatoires, envisagez de créer des vues matérialisées pour améliorer les performances.

SYSTÈME TABLESAMPLE pour PostgreSQL 9.5 :
Cette technique d'optimisation introduite dans PostgreSQL 9.5 permet un échantillonnage rapide d'un pourcentage spécifié de lignes.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!