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

Sélectionnez des lignes aléatoires dans une grande table PostgreSQL

Lorsque vous travaillez avec de grands ensembles de données, la sélection de lignes aléatoires peut être une tâche gourmande en calcul. Cet article explore différentes méthodes permettant de récupérer des lignes aléatoires à partir d'une table contenant environ 500 millions de lignes, et discute de leurs performances et de leur précision.

Méthode 1 : utilisez RANDOM() et LIMIT

La première méthode consiste à utiliser la fonction RANDOM() pour générer des nombres aléatoires, puis à utiliser la clause LIMIT pour filtrer les résultats afin d'obtenir le nombre de lignes requis.

<code class="language-sql">SELECT * FROM table WHERE RANDOM() < 0.000002 LIMIT 1000;</code>

Cette approche a l'avantage d'être facile à mettre en œuvre, mais peut s'avérer inefficace pour les grandes tables. En raison de la clause LIMIT, la base de données doit analyser toutes les lignes de la table pour sélectionner des lignes aléatoires et ignorer le reste.

Méthode 2 : utilisez ORDER BY RANDOM() et LIMIT

Une autre approche consiste à trier d'abord les lignes par la fonction RANDOM(), puis à utiliser la clause LIMIT pour obtenir des lignes aléatoires.

<code class="language-sql">SELECT * FROM table ORDER BY RANDOM() LIMIT 1000;</code>

Cette méthode est similaire à la première méthode, mais le tri garantit une sélection plus efficace de lignes aléatoires. Il réduit le nombre d'analyses requises, ce qui en fait un meilleur choix pour les grandes tables. Cependant, ce n’est toujours pas le meilleur choix pour les tableaux comportant un nombre extrêmement important de lignes.

Approche efficace : utilisez des colonnes et des index d'identification numérique

Pour les tableaux avec des colonnes d'ID numériques et moins d'espaces, une approche plus efficace peut être utilisée. Cela implique de générer des nombres aléatoires dans une plage d'identifiants et de les utiliser pour se joindre à la table.

<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   table USING (id)
LIMIT  1000;</code>

Cette approche exploite l'accès aux index pour réduire considérablement le nombre d'analyses requises. Il est idéal pour les tableaux comportant un grand nombre de lignes et peu d’espaces dans la colonne ID.

Considérations et recommandations

La meilleure façon de sélectionner des lignes aléatoires dépend des caractéristiques spécifiques de la table et des exigences de performances. Pour les petites tables, les méthodes RANDOM() ou ORDER BY RANDOM() peuvent suffire. Cependant, pour les grandes tables avec des colonnes d'ID numériques et peu d'espaces, il est recommandé d'utiliser la méthode d'optimisation ci-dessus pour de meilleures performances.

Il convient de noter qu'en raison de la nature de la génération de nombres pseudo-aléatoires dans les ordinateurs, aucune de ces méthodes ne peut garantir un véritable caractère aléatoire. Cependant, ils constituent un moyen pratique d’obtenir un échantillon aléatoire de lignes à partir d’une grande table avec une efficacité et une précision raisonnables.

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!