Comment écrire des instructions de requête SQL de haute qualité et hautes performances

1. Tout d’abord, il faut comprendre qu’est-ce qu’un plan d’exécution ?

Le plan d'exécution est un plan de requête élaboré par la base de données sur la base des informations statistiques de l'instruction SQL et des tables associées. Ce plan est automatiquement analysé par l'optimiseur de requêtes. Par exemple, si une instruction SQL est utilisée pour interroger une table avec. 100 000 enregistrements, 1 enregistrement, l'optimiseur de requêtes choisira la méthode « recherche par index ». Si la table est archivée et qu'il ne reste actuellement que 5 000 enregistrements, l'optimiseur de requêtes modifiera le plan et utilisera la méthode « analyse complète de la table ».

On voit que le plan d'exécution n'est pas figé, il est « personnalisé ». Il y a deux points importants pour générer un « plan d'exécution » correct :

(1) L'instruction SQL indique-t-elle clairement à l'optimiseur de requêtes ce qu'il veut faire ?

(2) Les statistiques de la base de données obtenues par l'optimiseur de requêtes sont-elles à jour et correctes ?

2. Unifiez la manière d'écrire les instructions SQL

Pour les deux instructions SQL suivantes, les programmeurs pensent qu'elles sont identiques, mais l'optimiseur de requêtes de base de données pense qu'elles sont différentes.

select*from dual

select*From dual

En fait, le cas est différent, l'analyseur de requêtes considère qu'il s'agit de deux instructions SQL différentes et doit être analysée deux fois. Générez 2 plans d'exécution. Par conséquent, en tant que programmeur, vous devez vous assurer que la même instruction de requête est cohérente partout, même un espace de plus ne fonctionnera pas !

3. Problèmes à noter lors de l'écriture d'instructions SQL

Ce qui suit est une introduction détaillée à certains problèmes à noter lors de l'écriture d'instructions SQL. Dans ces clauses Where, même s'il existe des index pour certaines colonnes, en raison de la mauvaise qualité du SQL écrit, le système ne peut pas utiliser l'index lors de l'exécution de l'instruction SQL et utilise également une analyse complète de la table, ce qui entraîne une vitesse de réponse extrêmement lente. réduction.

1.IS NULL et IS NOT NULL

Vous ne pouvez pas utiliser null comme index, et toute colonne contenant une valeur nulle ne sera pas incluse dans l'index. Même si l'index comporte plusieurs colonnes, tant que l'une de ces colonnes contient null, la colonne sera exclue de l'index. Autrement dit, s'il y a une valeur nulle dans une colonne, même la création d'un index sur la colonne n'améliorera pas les performances.

Tout optimiseur d'instructions qui utilise est nul ou n'est pas nul dans une clause Where n'est pas autorisé à utiliser des index.

2. Évitez d'utiliser des types de données incompatibles.

Le type de données incompatible représente la conversion de type des données de récupération de la table complète, et l'accès deviendra une analyse de table complète

select * from Employee which last_name = 100 ; Notez que last_name est un type varchar

3.

Pour ceux qui ont des colonnes Join, même si la valeur de jointure finale est une valeur statique, l'optimiseur n'utilisera pas l'index. Jetons un coup d'œil à un exemple. Supposons qu'il existe une table d'employés (employee). Le prénom et le nom d'un employé sont stockés dans deux colonnes (FIRST_NAME et LAST_NAME). Nous voulons maintenant interroger un employé nommé Bill Cliton.

Ce qui suit est une instruction SQL utilisant une requête de jointure,

select * from employss which first_name||''||last_name ='Beill Cliton';

L'instruction ci-dessus peut complètement demander s'il y a un employé Bill Cliton. Mais il convient de noter ici que l'optimiseur système n'utilise pas l'index créé sur la base de last_name.

Lorsque l'instruction SQL suivante est écrite, le système Oracle peut utiliser l'index créé en fonction de last_name.

*** où first_name ='Beill' et last_name ='Cliton';

4. Like déclaration commençant par un caractère générique (%)

La demande actuelle est la suivante, il est nécessaire d'interroger la table des employés pour les noms contenant les gens clitonais. Vous pouvez utiliser l'instruction SQL de requête suivante :

select * from Employee Where last_name like '%cliton%' Ici, comme le caractère générique (%) apparaît au début du mot recherché, le système Oracle n'utilise pas l'index de nom de famille. Cependant, lorsque des caractères génériques apparaissent ailleurs dans la chaîne, l'optimiseur peut tirer parti de l'index. L'index est utilisé dans la requête suivante :

select * from Employee which last_name like 'c%'

5 L'exécution d'opérations sur le champ d'index invalidera l'index.

Essayez d'éviter d'effectuer des opérations de fonction ou d'expression sur les champs de la clause WHERE, ce qui obligerait le moteur à abandonner l'utilisation de l'index et à effectuer une analyse complète de la table.

Par exemple : SELECT * FROM T1 WHERE F1/2=100 doit être remplacé par : SELECT * FROM T1 WHERE F1=100*2

6. L'instruction Order by

ORDER BY détermine la manière dont Oracle trie les résultats de la requête renvoyés. L'instruction Order by n'a aucune restriction particulière sur les colonnes à trier, et des fonctions peuvent également être ajoutées aux colonnes (telles que des jointures ou des ajouts, etc.). Tous les éléments non indexés ou expressions calculées dans l’instruction Order by ralentiront la requête.

Vérifiez soigneusement les instructions order by pour les éléments ou expressions non indexés, ce qui peut réduire les performances. La solution à ce problème est de réécrire l'instruction order by pour utiliser un index. Vous pouvez également créer un autre index pour la colonne utilisée. En même temps, vous devez absolument éviter d'utiliser des expressions dans la clause order by.

7. NOT

Nous utilisons souvent des expressions logiques dans la clause Where lors d'une requête, telles que supérieur à, inférieur à, égal à, différent de, etc. Nous pouvons également utiliser et (et), ou (ou) et non (pas ). NOT peut être utilisé pour annuler tout signe d’opération logique. Voici un exemple de clause NOT :

... où pas (status ='VALID')

Si vous souhaitez utiliser NOT, vous devez mettre des parenthèses devant la phrase niée et l'opérateur NOT devant la phrase. L'opérateur NOT est contenu dans un autre opérateur logique, qui est l'opérateur différent de (<>). En d'autres termes, même si le mot NOT n'est pas explicitement ajouté à la clause Where de la requête, NOT est toujours dans l'opérateur, voir l'exemple suivant :

...where status <>'INVALID'; Pour cette requête, il peut être réécrit pour ne pas utiliser NOT :

select * from Employee which salaire<3000 ou salaire>3000;

Bien que les résultats de ces deux requêtes soient les mêmes, le deuxième plan de requête sera plus rapide que le premier plan de requête. La deuxième requête permet à Oracle d'utiliser des index sur la colonne salaire, tandis que la première requête ne peut pas utiliser d'index.

8. IN et EXISTS

Parfois, une colonne est comparée à une série de valeurs. Le moyen le plus simple consiste à utiliser une sous-requête dans la clause Where. Deux formats de sous-requêtes peuvent être utilisés dans la clause Where.

Le premier format consiste à utiliser l'opérateur IN :

... où colonne dans (sélectionnez * à partir de ... où ...) 

Le deuxième format consiste à utiliser l'opérateur EXIST :

... ; où existe (sélectionnez 'X' dans ...où ...);

Je pense que la plupart des gens utiliseront le premier format car il est plus facile à écrire, mais en fait le deuxième format est bien meilleur que le premier. Ce format est très efficace. Dans Oracle, presque toutes les sous-requêtes de l'opérateur IN peuvent être réécrites en tant que sous-requêtes à l'aide de EXISTS.

Dans le deuxième format, la sous-requête commence par « select 'X ». En utilisant la clause EXISTS, quelles que soient les données extraites de la table par la sous-requête, elle examine uniquement la clause Where. De cette façon, l'optimiseur n'a pas besoin de parcourir la table entière et peut effectuer le travail en fonction de l'index uniquement (cela suppose que la colonne utilisée dans l'instruction Where possède un index). Par rapport à la clause IN, EXISTS utilise des sous-requêtes connectées, qui sont plus difficiles à construire que les sous-requêtes IN.

En utilisant EXIST, le système Oracle vérifie d'abord la requête principale, puis exécute la sous-requête jusqu'à ce qu'il trouve la première correspondance, ce qui permet de gagner du temps. Lorsque le système Oracle exécute la sous-requête IN, il exécute d'abord la sous-requête et stocke la liste de résultats obtenue dans une table temporaire indexée. Avant d'exécuter la sous-requête, le système suspend d'abord la requête principale. Une fois la sous-requête terminée, elle est stockée dans la table temporaire, puis la requête principale est exécutée. C'est pourquoi l'utilisation de EXISTS est plus rapide que l'utilisation de IN pour les requêtes générales.

En même temps, NOT EXISTS doit être utilisé autant que possible à la place de NOT IN. Bien que les deux utilisent NOT (l'index ne peut pas être utilisé pour réduire la vitesse), NOT EXISTS est plus efficace que la requête NOT IN.

9. Essayez d'éviter d'utiliser ou dans la clause Where pour connecter les conditions, sinon le moteur abandonnera l'utilisation de l'index et effectuera une analyse complète de la table,

Par exemple : sélectionnez l'identifiant de l'employé où num=10 ou num=20.

OK Requête comme celle-ci : sélectionnez l'identifiant de l'employé où num=10 union tous sélectionnez l'identifiant de l'employé où num=20

10 Vous devriez essayer d'éviter les opérations d'expression sur les champs de la clause Where

Cela entraînera le moteur. abandonnez l'utilisation de l'index et effectuez des opérations complètes d'analyse de la table. Par exemple : sélectionnez l'identifiant de t où num/2=100 doit être remplacé par : sélectionnez l'identifiant de t où num=100*2

11 Essayez d'éviter d'effectuer des opérations fonctionnelles sur les champs de la clause Where

Cela provoquera le problème. moteur pour abandonner Effectuer une analyse complète de la table à l'aide d'un index. Par exemple : sélectionnez l'identifiant de t où substring(name,1,3)='abc', l'identifiant dont le nom commence par abc doit être remplacé par :

sélectionnez l'identifiant de t où le nom ressemble à 'abc%'

12. N'utilisez pas où Le côté gauche de "=" dans la clause doit effectuer des fonctions, des opérations arithmétiques ou d'autres opérations d'expression, sinon le système pourrait ne pas être en mesure d'utiliser l'index correctement.

13. Lors de l'utilisation d'un champ d'index comme condition, si l'index est un index composite, le premier champ de l'index doit être utilisé comme condition pour garantir que le système utilise l'index, sinon l'index ne sera pas utilisé. Et l’ordre des champs doit être autant que possible cohérent avec l’ordre de l’index.

14. Plus il y a d'index, mieux c'est

Bien que les index puissent améliorer l'efficacité de la sélection correspondante, ils réduisent également l'efficacité de l'insertion et de la mise à jour, car l'index peut être reconstruit lors de l'insertion ou de la mise à jour, alors comment construire l'index est nécessaire. Examinez attentivement et au cas par cas. Il est préférable de ne pas avoir plus de 6 index sur une table. S'il y en a trop, vous devez vous demander s'il est nécessaire de créer des index sur certaines colonnes qui ne sont pas couramment utilisées.

15. Essayez d'utiliser des champs numériques. Si les champs contiennent uniquement des informations numériques, essayez de ne pas les concevoir comme des champs de caractères, cela réduirait les performances des requêtes et des connexions et augmenterait la surcharge de stockage. En effet, le moteur comparera chaque caractère de la chaîne un par un lors du traitement des requêtes et des connexions, et une seule comparaison suffit pour les types numériques.

16. Utilisez autant que possible varchar/nvarchar au lieu de char/nchar, car premièrement, l'espace de stockage des champs de longueur variable est petit, ce qui peut économiser de l'espace de stockage. Deuxièmement, pour les requêtes, l'efficacité de la recherche dans un champ relativement petit. est évidemment plus élevé.

17. N'utilisez pas select * fromt n'importe où, remplacez "*" par une liste de champs spécifique et ne renvoyez aucun champ inutilisé.

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!