Le système de base de données est au cœur du système d'information de gestion. Le traitement des transactions en ligne basé sur une base de données (OLTP) et le traitement analytique en ligne (OLAP) sont l'une des applications informatiques les plus importantes dans les banques, les entreprises, les gouvernements et d'autres ministères. Basé sur des exemples d'application et combiné à la théorie des bases de données, cet article présente l'application de la technologie d'optimisation des requêtes dans des systèmes réels. À en juger par les exemples d'application de la plupart des systèmes, les opérations de requête représentent la plus grande proportion des diverses opérations de base de données, et l'instruction SELECT sur laquelle l'opération de requête est basée est l'instruction la plus coûteuse parmi les instructions SQL. Par exemple, si la quantité de données s'accumule jusqu'à un certain niveau, comme les informations d'une table de base de données de comptes bancaires s'accumulant en millions, voire en dizaines de millions d'enregistrements, une analyse complète de la table prend souvent des dizaines de minutes, voire des heures. Si vous adoptez une meilleure stratégie de requête qu'une analyse complète de table, vous pouvez souvent réduire le temps de requête à quelques minutes, ce qui montre l'importance de la technologie d'optimisation des requêtes.
Lors de la mise en œuvre du projet d'application, l'auteur a constaté que lors du développement d'applications de base de données à l'aide de certains outils de développement de bases de données frontaux (tels que PowerBuilder, Delphi, etc.), de nombreux programmeurs se concentrent uniquement sur la beauté de l'interface utilisateur et ne paient pas. attention à l'efficacité des instructions de requête, ce qui entraîne tous les problèmes. Le système d'application développé est inefficace et entraîne un grave gaspillage de ressources. Par conséquent, il est très important de savoir comment concevoir des instructions de requête efficaces et raisonnables. Basé sur des exemples d'application et combiné à la théorie des bases de données, cet article présente l'application de la technologie d'optimisation des requêtes dans des systèmes réels.
Analyser le problème
De nombreux programmeurs pensent que l'optimisation des requêtes relève de la tâche du SGBD (système de gestion de base de données) et n'a pas grand-chose à voir avec les instructions SQL écrites par les programmeurs. C'est faux. Un bon plan de requête peut souvent améliorer les performances du programme des dizaines de fois. Le plan de requête est un ensemble d'instructions SQL soumises par l'utilisateur et le plan de requête est un ensemble d'instructions générées après optimisation. Le processus de traitement du plan de requête du SGBD est le suivant : après avoir terminé la vérification lexicale et syntaxique de l'instruction de requête, l'instruction est soumise à l'optimiseur de requête du SGBD. Une fois que l'optimiseur a terminé l'optimisation algébrique et l'optimisation du chemin d'accès, le module précompilé traite le. et générer un plan de requête, puis le soumettre au système pour traitement et exécution au moment approprié, et enfin renvoyer les résultats de l'exécution à l'utilisateur. Dans les versions élevées des produits de base de données actuels (tels qu'Oracle, Sybase, etc.), des méthodes d'optimisation basées sur les coûts sont utilisées. Cette optimisation peut estimer le coût de différents plans de requête en fonction des informations obtenues à partir de la table du dictionnaire système, puis sélectionner. une Meilleure planification. Bien que les produits de base de données actuels s'améliorent de plus en plus en matière d'optimisation des requêtes, les instructions SQL soumises par les utilisateurs constituent la base de l'optimisation du système. Il est difficile d'imaginer qu'un plan de requête initialement médiocre deviendra efficace après l'optimisation du système. Les déclarations que les utilisateurs écrivent sont cruciales. Nous ne discuterons pas de l'optimisation des requêtes effectuée par le système pour l'instant. Ce qui suit se concentre sur les solutions permettant d'améliorer les plans de requête des utilisateurs.
résoudre des problèmes
Ce qui suit prend le système de base de données relationnelle Informix comme exemple pour présenter des méthodes permettant d'améliorer les plans de requête des utilisateurs.
1. Utilisation raisonnable des index
L'index est une structure de données importante dans la base de données et son objectif fondamental est d'améliorer l'efficacité des requêtes. La plupart des produits de bases de données utilisent désormais la structure d'index ISAM proposée pour la première fois par IBM. L'utilisation des index doit être appropriée et les principes de son utilisation sont les suivants :
●Créez des index sur les colonnes fréquemment connectées mais qui ne sont pas désignées comme clés étrangères, tandis que l'optimiseur génère automatiquement des index pour les champs rarement connectés.
● Créez des index sur les colonnes qui sont fréquemment triées ou regroupées (c'est-à-dire regrouper par ou trier par opérations).
●Créez des recherches sur des colonnes avec de nombreuses valeurs différentes qui sont souvent utilisées dans les expressions conditionnelles. Ne créez pas d'index sur des colonnes avec peu de valeurs différentes. Par exemple, il n'y a que deux valeurs différentes dans la colonne « Genre » de la table des employés, « Homme » et « Femme », il n'est donc pas nécessaire de créer un index. Si vous créez un index, non seulement cela n'améliorera pas l'efficacité des requêtes, mais cela réduira considérablement la vitesse de mise à jour.
●S'il y a plusieurs colonnes à trier, vous pouvez créer un index composé sur ces colonnes.
●Utilisez les outils système. Par exemple, la base de données Informix dispose d'un outil tbcheck qui peut vérifier les index suspects. Sur certains serveurs de base de données, l'index peut être invalide ou l'efficacité de la lecture peut être réduite en raison d'opérations fréquentes. Si une requête utilisant l'index ralentit sans raison apparente, vous pouvez essayer d'utiliser l'outil tbcheck pour vérifier l'intégrité de l'index. et réparez-le si nécessaire. De plus, lorsqu'une table de base de données met à jour une grande quantité de données, la suppression et la reconstruction de l'index peuvent améliorer la vitesse des requêtes.
2. Eviter ou simplifier le tri
Le tri répété des grandes tables doit être simplifié ou évité. L'optimiseur évite l'étape de tri lorsqu'il peut utiliser un index pour produire automatiquement une sortie dans le bon ordre. Voici quelques facteurs d’influence :
●L'index ne comprend pas une ou plusieurs colonnes à trier ;
●L'ordre des colonnes dans la clause group by ou order by est différent de l'ordre de l'index ;
●Les colonnes triées proviennent de tables différentes.
Afin d'éviter des tris inutiles, il est nécessaire d'ajouter correctement les index et de fusionner raisonnablement les tables de la base de données (même si cela peut parfois affecter la normalisation de la table, l'amélioration de l'efficacité en vaut la peine). Si le tri est inévitable, vous devriez essayer de le simplifier, par exemple en réduisant la plage de colonnes à trier, etc.
3. Éliminez l'accès séquentiel aux données de grandes lignes de tableau
Dans les requêtes imbriquées, l'accès séquentiel aux tables peut avoir un impact fatal sur l'efficacité des requêtes. Par exemple, en utilisant une stratégie d'accès séquentiel, si une requête à trois niveaux imbriqués interroge 1 000 lignes à chaque niveau, cette requête interrogera 1 milliard de lignes de données. Le principal moyen d’éviter cela est d’indexer les colonnes jointes. Par exemple, deux tables : table des étudiants (numéro d'étudiant, nom, âge...) et table de sélection de cours (numéro d'étudiant, numéro de cours, notes). Si deux tables doivent être connectées, un index doit être créé sur le champ de connexion "numéro d'élève".
Vous pouvez également utiliser des unions pour éviter les accès séquentiels. Bien qu'il existe des index sur toutes les colonnes de contrôle, certaines formes de clauses Where obligent l'optimiseur à utiliser un accès séquentiel. La requête suivante forcera les opérations séquentielles sur la table des commandes : SELECT * FROM commandes WHERE (customer_num=104 AND order_num>1001) OR order_num=1008
Bien qu'il existe des index sur customer_num et order_num, l'optimiseur utilise toujours le chemin d'accès séquentiel pour analyser l'intégralité de la table dans l'instruction ci-dessus. Étant donné que cette instruction récupère une collection distincte de lignes, elle doit être remplacée par l'instruction suivante :
SELECT * FROM commandes WHERE numéro_client=104 ET numéro_commande>1001
UNION
SELECT * FROM commandes WHERE numéro_commande=1008
Cela permet d'utiliser le chemin d'index pour traiter les requêtes.
4. Évitez les sous-requêtes corrélées
Si une étiquette de colonne apparaît à la fois dans la requête principale et dans la requête de la clause Where, il est probable que la sous-requête doive être réinterrogeée lorsque la valeur de la colonne dans la requête principale change. Plus une requête comporte de niveaux imbriqués, plus son efficacité est faible. Les sous-requêtes doivent donc être évitées autant que possible. Si une sous-requête est inévitable, filtrez autant de lignes que possible dans la sous-requête.
5. Évitez les expressions régulières difficiles
Les mots-clés MATCHES et LIKE prennent en charge la correspondance par caractères génériques, techniquement appelées expressions régulières. Mais ce type de mise en correspondance prend particulièrement du temps. Par exemple : SELECT * FROM customer WHERE code postal LIKE « 98_ _ _ »
Même si un index est créé sur le champ du code postal, l'analyse séquentielle est toujours utilisée dans ce cas. Si vous modifiez l'instruction en SELECT * FROM customer WHERE zipcode > "98000", l'index sera utilisé pour interroger lors de l'exécution de la requête, ce qui améliorera évidemment considérablement la vitesse.
Évitez également les sous-chaînes qui ne démarrent pas. Par exemple, l'instruction : SELECT * FROM customer WHERE zipcode[2, 3]>"80" utilise une sous-chaîne qui ne démarre pas dans la clause Where, cette instruction n'utilise donc pas l'index.
6. Utilisez des tables temporaires pour accélérer les requêtes
Trier un sous-ensemble de la table et créer une table temporaire peut parfois accélérer les requêtes. Cela permet d'éviter plusieurs opérations de tri et simplifie par ailleurs le travail de l'optimiseur. Par exemple : SELECT cust.name, rcVBles.balance,...autres colonnes
SELECT cust.name,rcVBles.balance,...autres colonnes
DE Cust,rcvbles
OÙ cust.customer_id = rcvlbes.customer_id
ET rcvblls.balance>0
ET client.code postal>"98000"
COMMANDER PAR nom de client
Si cette requête doit être exécutée plusieurs fois au lieu d'une seule, vous pouvez rechercher tous les clients impayés dans un fichier temporaire et les trier par nom de client : SELECT cust.name, rcvbles.balance,...other columns
SELECT cust.name,rcvbles.balance,...autres colonnes
DE Cust,rcvbles
OÙ cust.customer_id = rcvlbes.customer_id
ET rcvblls.balance>0
COMMANDER PAR nom de client
INTO TEMP cus_with_balance
Effectuez ensuite une requête dans la table temporaire de la manière suivante : SELECT * FROM cust_with_balance
OÙ code postal>"98000"
Il y a moins de lignes dans la table temporaire que dans la table principale, et l'ordre physique est l'ordre requis, ce qui réduit les E/S disque, ce qui permet de réduire considérablement la charge de travail des requêtes.
Remarque : Une fois la table temporaire créée, elle ne reflétera pas la modification de la table principale. Lorsque les données de la table principale sont fréquemment modifiées, veillez à ne pas perdre de données.
7. Utiliser le tri pour remplacer l'accès non séquentiel
L'accès au disque non séquentiel est l'opération la plus lente et est représenté par le mouvement de va-et-vient du bras d'accès au disque. Les instructions SQL masquent cette situation, ce qui nous permet d'écrire facilement des requêtes nécessitant l'accès à un grand nombre de pages non séquentielles lors de l'écriture d'applications. Parfois, l'utilisation des capacités de tri de la base de données au lieu d'un accès non séquentiel peut améliorer les requêtes.
-