Optimisation des requêtes SQL : comment écrire des instructions SQL hautes performances

1. 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é et généré par l'optimiseur de requêtes. Par exemple, si une instruction SQL est utilisée pour interroger 1 enregistrement à partir de. une table avec 100 000 enregistrements, 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 informations statistiques de la base de données obtenues par l'optimiseur de requêtes sont-elles à jour et correctes ?

2. Unifiez la méthode d'écriture des 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, si 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. N'écrivez pas d'instructions SQL trop compliquées

Je vois souvent qu'une instruction SQL capturée à partir de la base de données est aussi longue que 2 feuilles de papier A4 une fois imprimée. 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, les résultats d'une instruction Select sont utilisés comme sous-ensemble, puis la requête est effectuée à partir du sous-ensemble. Ce type d'instruction imbriquée à un niveau est relativement courant, mais selon l'expérience, s'il existe plus de trois niveaux d'imbrication. , l'optimiseur de requêtes donnera facilement un mauvais plan d'exécution. Parce que c'é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é, et 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.

4. Utilisez "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. Cependant, les avantages des tables temporaires sont bien plus que ceux-ci. Les résultats temporaires sont stockés dans des tables temporaires et les requêtes ultérieures sont dans tempdb, ce qui peut éviter plusieurs requêtes. fois dans le programme. L'analyse de la table principale réduit également considérablement le blocage du « verrouillage partagé » et du « verrouillage de mise à jour » pendant l'exécution du programme, réduisant ainsi le blocage et améliorant les performances de concurrence.

5. 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 des variables de liaison

select*from orderheader where changetime >@chgtime

La variable @chgtime peut transmettre n'importe quelle valeur, de sorte 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 de la 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.

6. Variables liées à l'observation

Il y a deux côtés à tout, et les variables de liaison sont applicables à la plupart des processus OLTP, mais il existe 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 tableau de recensement, dans la colonne « Origine ethnique », plus de 90 % des valeurs sont 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 simplement si la première valeur transmise par @nation est "Han", alors l'ensemble du plan d'exécution choisira inévitablement l'analyse de 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 ».

7. 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. Il est nécessaire que toutes les modifications de plusieurs tables soient réussies. ou aucun d’entre eux ne réussit. 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 intercepte trop d'instructions SQL, les performances de la base de données seront médiocres. Avant qu'une transaction importante ne soit validée, d'autres instructions 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é.

8. 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. Cela n'est pas nécessaire dans Oracle car Oracle a une structure plus raisonnable et dispose d'un espace table d'annulation pour enregistrer les "données préfigurées". modifié Il n'a pas encore été validé, donc ce que vous lisez est la copie avant sa modification, qui 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 fractionnements de pages se produisent fréquemment, alors utilisez nolock avec prudence !

(3) L'utilisation d'une table temporaire peut également enregistrer les "données précédentes", qui ont une fonction similaire à l'espace table d'annulation d'Oracle,

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

9. L'index clusterisé n'est pas construit sur les champs séquentiels de la table et la table est sujette aux fractionnements de pages

Par exemple, dans la table des commandes, il y a le numéro de commande orderid et le numéro de client contactid. Alors, à quel champ l'index clusterisé doit-il être ajouté ? 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 identifiants 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 » passe une nouvelle commande aujourd'hui, alors la commande. Les informations 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.

Les index de SQL Server sont différents des index 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 en 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 déjà rencontré 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 la table. Les espaces vides d'origine ont disparu et le taux de remplissage des pages est très élevé. Lors de l'insertion de données, des divisions de pages se produisent souvent, ce qui diminue les performances. diminue 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 !

10、加nolock后查询经常发生页分裂的表，容易产生跳读或重复读

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

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

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

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

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

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

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

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

13、SQL Server 表连接的三种方式

(1) Merge Join

(2) Nested Loop Join

(3) Hash Join

SQL Server 2000只有一种join方式——Nested Loop Join，如果A结果集较小，那就默认作为外表，A中每条记录都要去B中扫描一遍，实际扫过的行数相当于A结果集行数x B结果集行数。所以如果两个结果集都很大，那Join的结果很糟糕。

SQL Server 2005新增了Merge Join，如果A表和B表的连接字段正好是聚集索引所在字段，那么表的顺序已经排好，只要两边拼上去就行了，这种join的开销相当于A表的结果集行数加上B表的结果集行数，一个是加，一个是乘，可见merge join 的效果要比Nested Loop Join好多了。

如果连接的字段上没有索引，那SQL2000的效率是相当低的，而SQL2005提供了Hash join，相当于临时给A，B表的结果集加上索引，因此SQL2005的效率比SQL2000有很大提高，我认为，这是一个重要的原因。

总结一下，在表连接时要注意以下几点：

(1)    连接字段尽量选择聚集索引所在的字段

(2)    仔细考虑where条件，尽量减小A、B表的结果集

(3)    如果很多join的连接字段都缺少索引，而你还在用SQL Server 2000，赶紧升级吧。

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!