Comment sélectionner efficacement des lignes aléatoires à partir de grandes tables PostgreSQL ?

Méthode de sélection de lignes aléatoires PostgreSQL

Les méthodes traditionnelles de sélection de lignes aléatoires sont inefficaces et lentes lorsqu'il s'agit de grandes tables contenant des millions, voire des milliards d'enregistrements. Deux méthodes courantes sont :

• Utilisez random() pour filtrer :

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

• Utilisez order by random() et limit :

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

Cependant, en raison de la nécessité d'une analyse ou d'un tri complet de la table, ces méthodes ne sont pas le meilleur choix pour les tables comportant un grand nombre de lignes et entraîneront des goulots d'étranglement en termes de performances.

Méthodes d'optimisation pour les grandes tables

Pour les types de tableaux suivants, considérez la méthode d'optimisation suivante, qui est nettement plus rapide :

• Colonnes d'identification numérique avec des espaces petits ou moyens (indexées pour des recherches plus rapides)
• Pas ou peu d'opérations d'écriture lors de la sélection

Requête :

<code class="language-sql">WITH params AS (
  SELECT 1 AS min_id, -- 可选：自定义最小ID起始值
       5100000 AS id_span -- 近似ID范围（最大ID - 最小ID + 缓冲）
)
SELECT *
FROM (
  SELECT DISTINCT 1 + trunc(random() * p.id_span)::integer AS id
  FROM params p, generate_series(1, 1100) g
  GROUP BY 1
) r
INNER JOIN big ON r.id = big.id
LIMIT 1000;</code>

Comment ça marche :

• Estimation de la plage d'identification :

  • Si vous ne savez pas exactement, interrogez le tableau pour estimer l'étendue minimale, maximale et totale (max - min) de la colonne ID.

• Génération d'ID aléatoire :

  • Générez un ensemble différent de nombres aléatoires dans la plage d'identification estimée.

• Élimination des redondances et des doublons :

  • Regroupez les numéros générés pour supprimer les doublons, réduisant ainsi la possibilité de sélectionner des lignes manquantes ou des lignes déjà sélectionnées.

• Jointures et restrictions de tables :

  • Joignez les nombres aléatoires au tableau réel en utilisant la colonne ID (doit être indexé). Cette jointure efficace récupère les données correspondantes pour la ligne sélectionnée.
  • Enfin, appliquez une limite pour récupérer le nombre de lignes requis.

Pourquoi c'est rapide :

• Utilisation minimale de l'index :

  • La requête effectue uniquement une analyse d'index sur la colonne ID, ce qui est beaucoup plus rapide qu'une analyse de table complète ou une opération de tri.

• Génération de nombres aléatoires optimisée :

  • Les nombres aléatoires générés sont répartis sur la plage d'ID estimée, minimisant ainsi la possibilité de lignes manquantes ou se chevauchant.

• Élimination des redondances et des doublons :

  • Le regroupement des nombres générés garantit que seules des lignes distinctes sont sélectionnées, réduisant ainsi le besoin de filtrage ou de jointure supplémentaire pour éliminer les doublons.

Autres options :

• CTE récursif pour gérer les lacunes :

  • Pour les tables présentant des lacunes dans la séquence d'identification, ajoutez un CTE supplémentaire pour gérer ces lacunes.

• Wrappers de fonctions à réutiliser :

  • Définissez une fonction qui prend la limite et le pourcentage d'écart comme paramètres, permettant une configuration et une réutilisation faciles avec différentes tables.

• Fonctions universelles pour n'importe quelle table :

  • Créez une fonction générique qui accepte n'importe quelle table avec des colonnes entières comme paramètre.

• Matérialiser les vues pour plus de rapidité :

  • Envisagez de créer une vue matérialisée basée sur une requête optimisée pour une récupération plus rapide des lignes sélectionnées (quasi) aléatoirement.

• TABLE SAMPLE dans PostgreSQL 9.5 :

  • Exploitez la fonctionnalité "TABLE SAMPLE SYSTEM" de PostgreSQL pour implémenter une méthode d'échantillonnage de lignes plus rapide mais moins aléatoire, garantissant qu'un nombre précis de lignes est renvoyé. Cependant, gardez à l’esprit que l’échantillon peut ne pas être complètement aléatoire en raison des effets de regroupement.

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!