Comment écrire du SQL hautes performances

Tout d’abord, nous devons comprendre ce qu’est 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. extraire 100 000 enregistrements Pour rechercher 1 enregistrement dans la table, l'optimiseur de requêtes choisira la méthode "recherche par index". Si la table est archivée et qu'il ne reste que 5 000 enregistrements, l'optimiseur de requêtes modifiera le plan et utilisera "l'analyse complète de la table". " "Chemin.

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 :

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

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

Cours recommandé : Tutoriel MySQL.

Manière uniforme d'écrire des instructions SQL

Pour les deux instructions SQL suivantes, les programmeurs pensent qu'elles sont identiques, optimiseur de requêtes de base de données Je pense que c'est différent.

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 doivent être analysées 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 !

N'écrivez pas l'instruction SQL de manière trop complexe

Je vois souvent qu'une instruction SQL capturée à partir de la base de données est imprimée avec 2 feuilles de papier A4 Si longtemps. D’une manière générale, des déclarations aussi complexes posent généralement des problèmes. J'ai demandé cette instruction SQL de 2 pages à l'auteur original, mais il a dit que cela prenait trop de temps et qu'il ne pouvait pas la comprendre pendant un moment. Il est concevable que même l'auteur original puisse être dérouté par l'instruction SQL, et que la base de données le soit également.

Généralement, le résultat d'une instruction Select est utilisé comme sous-ensemble, puis la requête est effectuée à partir du sous-ensemble. Ce type d'instruction imbriquée à un niveau est encore relativement courant, mais selon l'expérience. , plus de trois niveaux d'instructions imbriquées sont définis, l'optimiseur de requêtes peut facilement donner un plan d'exécution incorrect. Parce qu'il était abasourdi. Des choses comme l'intelligence artificielle sont finalement inférieures à la résolution humaine. Si les gens ont le vertige, je peux garantir que la base de données le sera également.

De plus, le plan d'exécution peut être réutilisé. Plus l'instruction SQL est simple, plus la possibilité de réutilisation est élevée. Tant qu'un caractère change dans une instruction SQL complexe, il doit être réanalysé, et beaucoup de déchets seront alors stockés dans la mémoire. Il est concevable à quel point la base de données sera inefficace.

Utilisez une "table temporaire" pour stocker temporairement les résultats intermédiaires

Un moyen important de simplifier les instructions SQL consiste à utiliser des tables temporaires pour stocker temporairement les résultats intermédiaires résultats. Cependant, les avantages des tables temporaires sont bien plus que ceux-ci. Les résultats temporaires sont temporairement stockés dans la table temporaire et les requêtes ultérieures sont dans tempdb, ce qui peut éviter plusieurs analyses de la table principale dans le programme et également réduire considérablement ". "Shared Lock" bloquant pendant l'exécution du programme. Update Lock ", qui réduit le blocage et améliore les performances de concurrence.

Les instructions SQL du système OLTP doivent utiliser des variables de liaison

select*from orderheader where changetime >'2010-10-20 00:00:01' 
select*from orderheader where changetime >'2010-09-22 00:00:01'

Les deux instructions ci-dessus sont considérées par l'optimiseur de requêtes comme des instructions SQL différentes et doivent être analysées deux fois. Si vous utilisez la variable de liaison

select*from orderheader where changetime >@chgtime

@chgtime, vous pouvez transmettre n'importe quelle valeur, afin qu'un grand nombre de requêtes similaires puissent réutiliser le plan d'exécution, ce qui peut réduire considérablement la charge d'analyse des instructions SQL sur le base de données. Analyser une fois et réutiliser plusieurs fois est le principe pour améliorer l'efficacité de la base de données.

Aperçu des variables de liaison

Tout a deux côtés, les variables de liaison sont applicables à la plupart des traitements OLTP, mais il y a des exceptions. Par exemple, lorsque le champ dans la condition Where est un « champ asymétrique ».

« Champ incliné » signifie que la plupart des valeurs de la colonne sont les mêmes. Par exemple, dans un formulaire de recensement, dans la colonne « Origine ethnique », plus de 90 % sont des Han. Ainsi, si une instruction SQL veut interroger la population Han âgée de 30 ans, alors la colonne « ethnique » doit être placée à la condition Where. À l'heure actuelle, il y aura un gros problème si vous utilisez la variable de liaison @nation.

Imaginez si la première valeur transmise par @nation est "Han", alors l'ensemble du plan d'exécution sélectionnera inévitablement l'analyse de la table. Ensuite, la deuxième valeur transmise est "Buyei". Il va de soi que la proportion de "Buyi" peut n'être qu'un dix millième, la recherche par index doit donc être utilisée. Cependant, puisque le plan d'exécution de « Han » analysé pour la première fois est réutilisé, la méthode de scan de table sera également utilisée pour la deuxième fois. Ce problème est le fameux « espionnage des variables de liaison ». Il est recommandé de ne pas utiliser de variables de liaison pour les « champs asymétriques ».

Utilisez start tran uniquement lorsque cela est nécessaire

Une instruction SQL dans SQL Server est une transaction par défaut, et elle est validée par défaut après l'exécution de l'instruction. En fait, il s'agit d'une forme minimisée de start tran, tout comme un start tran est implicite au début de chaque instruction et un commit est implicite à la fin.

Dans certains cas, nous devons déclarer explicitement start tran. Par exemple, lors de l'exécution d'opérations « d'insertion, de suppression et de modification », nous devons modifier plusieurs tables en même temps. tables est réussie ou aucune des modifications n'a réussi. begin tran peut jouer un tel rôle. Il peut exécuter plusieurs instructions SQL ensemble et finalement les valider ensemble. L’avantage est que la cohérence des données est garantie, mais rien n’est parfait. Le prix payé par Begin tran est qu'avant la soumission, toutes les ressources verrouillées par les instructions SQL ne peuvent pas être libérées tant qu'elles ne sont pas validées.

On peut voir que si Begin tran piège trop d'instructions SQL, les performances de la base de données seront terribles. Avant qu'une transaction importante ne soit validée, d'autres déclarations seront inévitablement bloquées, ce qui entraînera de nombreux blocages.

Le principe d'utilisation de Begin tran est que, dans le but d'assurer la cohérence des données, moins il y a d'instructions SQL piégées par Begin tran, mieux c'est ! Dans certains cas, des déclencheurs peuvent être utilisés pour synchroniser les données, et commencer la transmission n'est pas nécessairement utilisé.

Certaines instructions de requête SQL doivent être ajoutées avec nolock

L'ajout de nolock aux instructions SQL est un moyen important d'améliorer les performances de concurrence de SQL Server. Ceci n'est pas requis dans. Oracle, car la structure d'Oracle est plus raisonnable et il existe un espace table d'annulation pour enregistrer les "données précédentes". Si les données n'ont pas été validées lors de la modification, alors ce que vous lisez est la copie avant qu'elle ne soit modifiée, et le la copie est placée dans l'espace table d'annulation. De cette façon, la lecture et l'écriture d'Oracle peuvent être indépendantes les unes des autres, c'est pourquoi Oracle est largement salué. La lecture et l'écriture de SQL Server se bloquent mutuellement. Afin d'améliorer les performances de concurrence, nolock peut être ajouté à certaines requêtes, afin que l'écriture puisse être autorisée pendant la lecture. Cependant, l'inconvénient est que des données sales non validées peuvent être lues. Il existe trois principes d'utilisation de nolock.

(1) Si les résultats de la requête sont utilisés pour "l'insertion, la suppression et la modification", aucun verrou ne peut être ajouté !

(2) La table interrogée est celle où les divisions de pages se produisent fréquemment, alors utilisez nolock avec prudence !

(3) Vous pouvez également utiliser des tables temporaires pour enregistrer des "préfigurations de données", qui fonctionnent comme l'espace table d'annulation d'Oracle

Si vous pouvez utiliser des tables temporaires pour améliorer les performances de concurrence, n'utilisez pas. pas de verrouillage.

L'index clusterisé n'est pas construit sur le champ de séquence de la table, et la table est sujette aux fractionnements de pages

Par exemple, dans la table de commande, il y a le numéro de commande orderid et le numéro de client contactid, puis sur quel champ faut-il ajouter l'index clusterisé ? Pour cette table, les numéros de commande sont ajoutés séquentiellement. Si un index clusterisé est ajouté à l'ID de commande, les nouvelles lignes seront ajoutées à la fin, afin que les fractionnements de page ne se produisent pas fréquemment. Cependant, comme la plupart des requêtes sont basées sur des numéros de clients, il est logique d'ajouter un index clusterisé à contactid. Pour la table de commande, contactid n'est pas un champ séquentiel.

Par exemple, le "contactid" de "Zhang San" est 001, alors les informations de commande de "Zhang San" doivent être placées sur la première page de données de ce tableau. Si "Zhang San" en place un nouveau. commander aujourd'hui Une commande, alors les informations de commande ne peuvent pas être placées sur la dernière page du tableau, mais sur la première page ! Et si la première page est pleine ? Désolé, toutes les données de ce tableau doivent être déplacées pour faire de la place à cet enregistrement.

L'index de SQL Server est différent de l'index clusterisé d'Oracle. L'index clusterisé de SQL Server trie en fait la table dans l'ordre des champs d'index clusterisé, ce qui est équivalent à la table organisée par index d'Oracle. L'index clusterisé de SQL Server est une forme organisationnelle de la table elle-même, son efficacité est donc très élevée. Pour cette raison, lorsqu'un enregistrement est inséré, son emplacement n'est pas placé de manière aléatoire, mais sur la page de données où il doit être placé dans l'ordre. S'il n'y a pas d'espace sur cette page de données, cela entraînera des fractionnements de page. Il est donc évident que l'index clusterisé n'est pas construit sur les champs séquentiels de la table et que la table est sujette aux fractionnements de pages.

J'ai rencontré une fois une situation dans laquelle l'efficacité d'insertion d'un ami diminuait considérablement après la réindexation d'une certaine table. On estime que la situation est probablement la suivante. L'index clusterisé de la table ne peut pas être construit sur les champs séquentiels de la table. La table est souvent archivée, de sorte que les données de la table existent dans un état clairsemé. Par exemple, Zhang San a passé 20 commandes, mais il n'y en a que 5 au cours des trois derniers mois. La stratégie d'archivage consiste à conserver 3 mois de données. Ensuite, les 15 dernières commandes de Zhang San ont été archivées, laissant 15 postes vacants. saisis dans l'encart Réutilisés au fur et à mesure de leur apparition. Dans ce cas, puisqu’il y a des espaces libres disponibles, aucun fractionnement de page ne se produira. Cependant, les performances de la requête seront relativement faibles, car la requête doit analyser ces positions vides sans données.

La situation a changé après la reconstruction de l'index clusterisé, car la reconstruction de l'index clusterisé signifie réorganiser les données dans le tableau. Les postes vacants d'origine ont disparu et le taux de remplissage des pages est souvent très élevé lors de l'insertion de données. . , donc les performances diminuent considérablement.

Pour les tables dont les index clusterisés ne sont pas construits sur des champs séquentiels, devons-nous donner un taux de remplissage de page inférieur ? Voulez-vous éviter de reconstruire l’index clusterisé ? C’est une question qui mérite réflexion !

Ajoutez nolock aux tables de requêtes où des divisions de pages se produisent souvent, ce qui peut facilement entraîner des lectures sautées ou répétées

加nolock后可以在“插、删、改”的同时进行查询，但是由于同时发生“插、删、改”，在某些情况下，一旦该数据页满了，那么页分裂不可避免，而此时nolock的查询正在发生，比如在第100页已经读过的记录，可能会因为页分裂而分到第101页，这有可能使得nolock查询在读101页时重复读到该条数据，产生“重复读”。同理，如果在100页上的数据还没被读到就分到99页去了，那nolock查询有可能会漏过该记录，产生“跳读”。

上面提到的哥们，在加了nolock后一些操作出现报错，估计有可能因为nolock查询产生了重复读，2条相同的记录去插入别的表，当然会发生主键冲突。

使用like进行模糊查询时应注意

有的时候会需要进行一些模糊查询比如

select*from contact where username like ‘%yue%’

关键词%yue%，由于yue前面用到了“%”，因此该查询必然走全表扫描，除非必要，否则不要在关键词前加%，

数据类型的隐式转换对查询效率的影响

sql server2000的数据库，我们的程序在提交sql语句的时候，没有使用强类型提交这个字段的值，由sql server 2000自动转换数据类型，会导致传入的参数与主键字段类型不一致，这个时候sql server 2000可能就会使用全表扫描。Sql2005上没有发现这种问题，但是还是应该注意一下。

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!