52 stratégies d'optimisation SQL à collecter pour améliorer les performances

Les instructions SQL peuvent effectuer des manipulations et des définitions de données, ce qui peut apporter une grande commodité aux utilisateurs. Cet article mentionnera 52 stratégies d'optimisation des performances des instructions SQL. Il est recommandé aux amis dans le besoin de le récupérer.

Stratégie d'optimisation des performances des instructions SQL

1. Lors de l'optimisation de la requête, essayez d'éviter l'intégralité. table Lors de l'analyse, vous devez d'abord envisager de créer des index sur les colonnes impliquées dans WHERE et ORDER BY.

2. Essayez d'éviter le jugement de valeur NULL sur les champs de la clause WHERE est la valeur par défaut lors de la création d'une table, mais la plupart du temps, vous devez utiliser NOT NULL, ou utiliser une valeur spéciale, telle que. comme 0 , -1 comme valeurs par défaut.

3. Essayez d'éviter d'utiliser les opérateurs != ou a8093152e673feb7aba1828c43532094 MySQL utilise des index uniquement pour les opérateurs suivants : e39901eede002d95a8e5b5997969f66f, >=, BETWEEN, IN et parfois LIKE.

4. Essayez d'éviter d'utiliser OR 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. Vous pouvez utiliser UNION pour fusionner les requêtes.

5. IN et NOT IN doivent également être utilisés avec prudence, sinon cela entraînera une analyse complète de la table. Pour les valeurs continues, n’utilisez pas IN si vous pouvez utiliser BETWEEN.

6. La requête suivante provoquera également une analyse complète de la table :

select id from t where name like‘%abc%’//用到索引

ou

select id from t where name like‘%abc’//若要提高效率，可以考虑全文检索

7. provoque également une analyse complète de la table.

8. Essayez d'éviter les opérations d'expression et les opérations de fonction sur les champs de la clause WHERE.

9. Dans de nombreux cas, utiliser EXISTS au lieu de IN est un bon choix.

10. Bien que l'index puisse améliorer l'efficacité du SELECT correspondant, il réduit également l'efficacité de INSERT et UPDATE . Étant donné que l'index peut être reconstruit lors d'INSERT ou UPDATE, il est préférable de ne pas avoir plus de 6 index sur une table.

11. Vous devez éviter autant que possible de mettre à jour les colonnes de données d'index clustered , car l'ordre des colonnes de données d'index groupées est l'ordre de stockage physique des enregistrements de la table. changements, cela entraînera la modification de l'ensemble de l'enregistrement de la table. L'ajustement de la séquence consommera des ressources considérables.

12. Essayez d'utiliser des types numériques. Si les champs qui contiennent uniquement des informations numériques, essayez de ne pas les concevoir comme des types de caractères, cela réduira les performances des requêtes et des connexions et augmentera le stockage.

13. Utilisez varchar, nvarchar au lieu de char, nchar autant que possible. Parce que tout d'abord, les champs longs ont un petit espace de stockage et peuvent économiser de l'espace de stockage. Pour les requêtes, la recherche dans un champ relativement petit est évidemment plus efficace.

14. Il est préférable de ne pas utiliser return all : select from t, remplacez "*" par une liste de champs spécifique et ne renvoyez aucun champ inutilisé.

15. Essayez d'éviter de renvoyer de grandes quantités de données au client. Si la quantité de données est trop importante, vous devez vous demander si les exigences correspondantes sont raisonnables.

16. Utiliser des alias de table (Alias) : Lorsque vous connectez plusieurs tables dans une instruction SQL, veuillez utiliser des alias de table et préfixer l'alias à chaque Column. Cela réduit le temps d'analyse et réduit les erreurs de syntaxe causées par les ambiguïtés des colonnes.

17. 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. Stockez temporairement les résultats temporaires dans la table temporaire, et les requêtes suivantes seront dans tempdb . Cela peut éviter plusieurs analyses de la table principale dans le programme et réduit également considérablement le « verrouillage partagé » bloquant le « verrouillage de mise à jour » pendant l'exécution du programme. , réduisant le blocage, améliore les performances de concurrence.

18. Certaines instructions de requête SQL doivent être ajoutées avec nolock La lecture et l'écriture se bloqueront afin d'améliorer les performances de concurrence. Pour certaines requêtes, vous pouvez ajouter nolock, qui permet d'écrire lors de la lecture, mais l'inconvénient est que des données sales non validées peuvent être lues.

Il existe trois principes d'utilisation de nolock :

• Si les résultats de la requête sont utilisés pour "l'insertion, la suppression et la modification", nolock ne peut pas être ajouté ;
  

• La table interrogée est celle où les divisions de pages se produisent fréquemment, utilisez donc nolock avec prudence

• L'utilisation d'une table temporaire peut également enregistrer le " ; data foreshadow". Il fonctionne comme l'espace table d'annulation d'Oracle et peut utiliser des tables temporaires pour améliorer les performances de concurrence. N'utilisez pas nolock.

19. Les règles de simplification courantes sont les suivantes : Ne pas avoir plus de 5 connexions de tables (JOIN), pensez à utiliser des tables temporaires ou des variables de table pour stocker les résultats intermédiaires. Utilisez moins de sous-requêtes et n'imbriquez pas les vues trop profondément. En règle générale, il convient de ne pas imbriquer plus de deux vues.

20. Pré-calculez les résultats qui doivent être interrogés et mettez-les dans le tableau, puis Select lors de l'interrogation.

21、用 OR 字句可以分解成多个查询，并且通过 UNION 连接多个查询。他们的速度与是否使用索引有关，如果查询需要用到联合索引，用 UNION all 执行的效率更高。多个 OR 的字句没有用到索引，改写成 UNION 的形式再试图与索引匹配。

22、在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断次数。

23、尽量将数据的处理工作放在服务器上，如使用存储过程。存储过程是编译好、优化过、并且被组织到一个执行规划、且存储在数据库中的 SQL 语句，是控制流语言的集合，速度当然快。反复执行的动态 SQL，可以使用临时存储过程，该过程（临时表）被放在 Tempdb 中。

24、当服务器的内存够多时，配制线程数量 = 最大连接数+5，这样能发挥最大的效率；否则使用配制线程数量cdc8cd205a17bcb60976ab750b49d81b=”，不要使用 “>”。

28、索引的使用规范：

• 索引的创建要与应用结合考虑，建议大的 OLTP 表不要超过 6 个索引；
         

• 尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过 index index_name 来强制指定索引；

• 避免对大表查询时进行 table scan，必要时考虑新建索引；

• 在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用；

• 要注意索引的维护，周期性重建索引，重新编译存储过程。　　

29、下列 SQL 条件语句中的列都建有恰当的索引，但执行速度却非常慢：

SELECT * FROM record WHERE substrINg(card_no, 1, 4) = '5378' --13秒
SELECT * FROM record WHERE amount/30 < 1000 --11秒
SELECT * FROM record WHERE convert(char(10), date, 112) = &#39;19991201&#39; --10秒

分析： WHERE 子句中对列的任何操作结果都是在 SQL 运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索引。如果这些结果在查询编译时就能得到，那么就可以被 SQL 优化器优化，使用索引，避免表搜索，因此将 SQL 重写成下面这样：

SELECT * FROM record WHERE card_no like &#39;5378%&#39; -- < 1秒 
SELECT * FROM record WHERE amount < 1000*30 -- < 1秒 
SELECT * FROM record WHERE date = &#39;1999/12/01&#39; -- < 1秒

30、当有一批处理的插入或更新时，用批量插入或批量更新，绝不会一条条记录的去更新。

31、在所有的存储过程中，能够用 SQL 语句的，绝不用循环去实现。

32、选择最有效率的表名顺序（只在基于规则的优化器中有效）： 

Oracle 的解析器按照从右到左的顺序处理 FROM 子句中的表名，FROM 子句中写在最后的表（基础表 driving table）将被最先处理，在 FROM 子句中包含多个表的情况下，必须选择记录条数最少的表作为基础表。如果有 3 个以上的表连接查询，那就需要选择交叉表（intersection table）作为基础表，交叉表是指那个被其他表所引用的表。

33、提高 GROUP BY 语句的效率，可以通过将不需要的记录在 GROUP BY 之前过滤掉。

34、SQL 语句用大写，因为 Oracle 总是先解析 SQL 语句，把小写的字母转换成大写的再执行。

35、别名的使用，别名是大型数据库的应用技巧，就是表名、列名在查询中以一个字母为别名，查询速度要比建连接表快 1.5 倍。

36、避免死锁，在你的存储过程和触发器中访问同一个表时总以相同的顺序；事务应尽可能地缩短，减少数据量的涉及；永远不要在事务中等待用户输入。

37、避免使用临时表，除非有需要，可以使用表变量代替。大多数时候（99%），表变量驻扎在内存中，因此速度比临时表更快，临时表驻扎在 TempDb 数据库中，因此临时表上的操作需要跨数据库通信，速度自然慢。

38、最好不要使用触发器：

• 触发，执行一个触发器事件本身就是一个耗费资源的过程；
         

• 如果能够使用约束实现的，尽量不要使用触发器；

• 不要为不同的触发事件（Insert、Update 和 Delete）使用相同的触发器；

• N'utilisez pas de code transactionnel dans les déclencheurs.

39. Règles de création d'index :

• La clé primaire et la clé étrangère de la table doivent avoir des index ;
  

• Les tables contenant plus de 300 volumes de données doivent avoir des index

• Les tables qui sont fréquemment connectées à d'autres tables doivent avoir des index sur les champs de connexion ; .

• Les champs qui apparaissent souvent dans la clause WHERE, en particulier les champs des grandes tables, doivent être indexés

• L'index doit être construit ; dans Sur les champs à haute sélectivité ;

• Les index doivent être construits sur de petits champs. Pour les champs de texte volumineux ou même très longs, ne construisez pas d'index ; >

  L'établissement d'un index composite nécessite une analyse minutieuse, et essayez plutôt d'envisager d'utiliser un index à champ unique

• Choisissez correctement le champ de la colonne principale dans l'index composite ; est généralement un champ avec une meilleure sélectivité

• Est-ce que plusieurs champs de l'index composite apparaissent souvent dans la clause WHERE en mode AND en même temps ? Y a-t-il peu ou pas de requêtes à champ unique ? Si c'est le cas, vous pouvez construire un index composite ; sinon envisagez un index à champ unique

• Si les champs inclus dans l'index composite apparaissent souvent seuls dans la clause WHERE, divisez-le en plusieurs index composites ; champs Index ;

• Si l'index composé contient plus de 3 champs, réfléchissez attentivement à la nécessité et réduisez le nombre de champs composés

• Si ; Il existe à la fois des index à champ unique et des index composites sur ces champs. Généralement, l'index composite peut être supprimé

• Pour les tables qui effectuent fréquemment des opérations de données, ne créez pas trop d'index ;

• Supprimez les index inutiles pour éviter un impact négatif sur le plan d'exécution

• Chaque index créé sur la table augmentera la surcharge de stockage, et l'index augmentera la surcharge de stockage. Les opérations d’insertion, de suppression et de mise à jour augmenteront également la surcharge de traitement. De plus, trop d'index composés n'ont généralement aucune valeur lorsqu'il existe des index à champ unique ; au contraire, ils réduiront également les performances lors de l'ajout et de la suppression de données, en particulier pour les tables fréquemment mises à jour, l'impact négatif est encore plus important. .

• Essayez de ne pas indexer un champ de la base de données qui contient un grand nombre de valeurs en double.

• 40. Résumé de l'optimisation des requêtes MySQL :

Utilisez les journaux de requêtes lentes pour découvrir les requêtes lentes et utilisez des plans d'exécution pour déterminer si les requêtes sont Pour fonctionner correctement, testez toujours vos requêtes pour voir si elles s'exécutent de manière optimale.

• 
  Les performances changeront avec le temps, évitez d'utiliser

  sur la table entière, cela peut verrouiller la table entière, rendant la requête cohérente afin que les requêtes similaires ultérieures puissent utiliser le cache de requêtes, dans Utilisez
au lieu de
• le cas échéant, utilisez des colonnes indexées dans les clauses WHERE, GROUP BY et ORDER BY, gardez les index simples et n'incluez pas la même colonne dans plusieurs index.

  count(*)GROUP BYDISTINCT
  Parfois, MySQL utilisera le mauvais index. Dans ce cas, utilisez

  Vérifiez le problème d'utilisation de
Pour les champs d'index avec moins de 5 enregistrements, dans UNION When. en utilisant
• , n'utilisez pas OR.

  USE INDEXSQL_MODE=STRICTLIMIT
  Pour éviter SELECT avant la mise à jour, utilisez

  ou
 ; n'utilisez pas UPDATE pour implémenter, n'utilisez pas de champs d'index et de clauses

•  ; , N peut en fait ralentir les requêtes dans certains cas, utilisez-le avec parcimonie, utilisez UNION dans la clause WHERE au lieu de la sous-requête, avant de redémarrer MySQL, n'oubliez pas de réchauffer votre base de données pour garantir que les données sont en mémoire et la vitesse des requêtes est rapide, envisagez des connexions persistantes au lieu de plusieurs connexions pour réduire les frais généraux. INSERT ON DUPLICATE KEY INSERT IGNOREORDER BYLIMIT M
  Requêtes de référence, y compris l'utilisation de la charge sur le serveur, parfois une simple requête peut affecter d'autres requêtes, lorsque la charge augmente sur le serveur, utilisez

  pour afficher les requêtes lentes et problématiques celles Requêtes, toutes les requêtes suspectes sont testées sur les données d'image générées dans l'environnement de développement.

• SHOW PROCESSLIST
  41. Processus de sauvegarde MySQL :

Sauvegarde à partir du serveur de réplication secondaire ;

• Arrêtez la réplication pendant la sauvegarde pour éviter les incohérences dans les dépendances de données et les contraintes de clé étrangère
  

• Arrêtez complètement MySQL et supprimez la base de données du fichier de base de données ; pour la sauvegarde ;

• Si vous utilisez le dump MySQL pour la sauvegarde, veuillez sauvegarder le fichier journal binaire en même temps pour vous assurer que la réplication n'est pas interrompue

Ne faites pas confiance aux instantanés LVM, qui sont susceptibles de produire des incohérences de données, vous causeront des problèmes à l'avenir
Afin de faciliter la récupération d'une seule table, si ; les données sont isolées des autres tables, utilisez la table comme unité d'exportation des données.
Utilisez
• lorsque vous utilisez

• Vérifiez et optimisez les tables avant de sauvegarder mysqldump ;

• Afin d'importer plus rapidement, les contraintes de clé étrangère et la détection d'unicité sont temporairement désactivées lors de l'importation

• Calculez la base de données et la table après chaque sauvegarde ainsi que la taille ; de l'index pour pouvoir surveiller la croissance de la taille des données

• Effectuer des sauvegardes régulières.

42. La mise en mémoire tampon des requêtes ne gère pas automatiquement les espaces. Par conséquent, lors de l'écriture d'instructions SQL, vous devez essayer de réduire l'utilisation des espaces, en particulier des espaces au début et à la fin de SQL (car mise en mémoire tampon des requêtes (les espaces de début et de fin ne sont pas automatiquement interceptés).

43. Est-il facile d'interroger les membres en utilisant mid comme standard pour diviser les tables en tables ? Dans les exigences commerciales générales, le nom d'utilisateur est essentiellement utilisé comme base de requête. Normalement, le nom d'utilisateur doit être utilisé comme module de hachage pour diviser les tables. Quant au fractionnement des tables, la fonction partition de MySQL le fait et est transparente pour le code ; il semble déraisonnable de l'implémenter au niveau du code ;

44. Nous devons définir un identifiant comme clé primaire pour chaque table de la base de données, et le meilleur est un type INT (UNSIGNED est recommandé), et définir un logo AUTO_INCREMENT automatiquement augmenté.

45. Définissez SET NOCOUNT ON au début et SET NOCOUNT OFF à la fin de toutes les procédures stockées et déclencheurs. Pas besoin d'envoyer des DONE_IN_PROC messages au client après l'exécution de chaque instruction de procédures stockées et de déclencheurs.

46. Les requêtes MySQL peuvent permettre une mise en cache des requêtes à grande vitesse. C'est l'une des méthodes d'optimisation MySQL efficaces pour améliorer les performances de la base de données. Lorsque la même requête est exécutée plusieurs fois, il est beaucoup plus rapide d’extraire les données du cache et de les renvoyer directement depuis la base de données.

47. EXPLAIN SELECT La requête est utilisée pour suivre l'effet d'affichage :

L'utilisation du mot-clé EXPLAIN peut vous permettre de savoir comment MySQL traite votre instruction SQL. Cela peut vous aider à analyser les goulots d'étranglement des performances de vos instructions de requête ou de vos structures de table. Les résultats de la requête EXPLAIN vous indiqueront également comment vos clés primaires d'index sont utilisées et comment vos tables de données sont recherchées et triées.

48. Utilisez LIMIT 1 lorsqu'il n'y a qu'une seule ligne de données :

Lorsque vous interrogez la table, vous savez déjà qu'il n'y aura qu'un seul résultat, mais parce que vous devrez peut-être récupérer le curseur ou vérifier le nombre d'enregistrements renvoyés. Dans ce cas, l'ajout de LIMIT 1 peut augmenter les performances. De cette façon, le moteur de base de données MySQL arrêtera la recherche après avoir trouvé une donnée, au lieu de continuer à rechercher la donnée suivante correspondant à l'enregistrement.

49. Sélectionnez un moteur de stockage approprié pour la table :

myisam : Principalement des opérations de lecture et d'insertion, avec seulement une petite quantité de mises à jour et de suppressions, et les exigences en matière d'intégrité des transactions et de concurrence ne sont pas très élevées.

InnoDB : Traitement des transactions et cohérence des données requis dans des conditions concurrentes. En plus des insertions et des requêtes, il inclut également de nombreuses mises à jour et suppressions. (InnoDB réduit efficacement le verrouillage causé par les suppressions et les mises à jour).

Pour les tables de type InnoDB qui prennent en charge les transactions, la principale raison qui affecte la vitesse est que AUTOCOMMIT le paramètre par défaut est activé et que le programme n'appelle pas explicitement BEGIN pour démarrer la transaction, ce qui entraîne chaque insertion automatiquement soumis. Affecte sérieusement la vitesse. Vous pouvez appeler start avant d'exécuter SQL. Plusieurs SQL forment une seule chose (même si la validation automatique est activée), ce qui améliorera considérablement les performances.

50. Optimisez le type de données du tableau et choisissez le type de données approprié :

Principe : Plus petit est généralement mieux, simple est mieux, tous les champs are Il doit y avoir une valeur par défaut et essayez d'éviter NULL. MySQL peut très bien prendre en charge l'accès à de grandes quantités de données, mais d'une manière générale, plus la table de la base de données est petite, plus les requêtes exécutées dessus seront rapides. Par conséquent, lors de la création d’un tableau, afin d’obtenir de meilleures performances, nous pouvons définir la largeur des champs du tableau aussi petite que possible.

De même, si possible, nous devrions utiliser MEDIUMINT au lieu de BIGIN pour définir des champs entiers. Nous devrions essayer de définir le champ sur NOT NULL afin que la base de données n'ait pas besoin de l'utiliser lors de l'exécution. requêtes dans le futur. Pour comparer les valeurs NULL.

Pour certains champs de texte, tels que « province » ou « sexe », nous pouvons les définir comme ENUM type. Parce que dans MySQL, le type ENUM est traité comme des données numériques et les données numériques sont traitées beaucoup plus rapidement que les types texte. De cette façon, les performances de la base de données peuvent être améliorées.

51. Types de données de chaîne : char, varchar, texte.

52. Toute opération sur une colonne entraînera une analyse de table, qui inclut les fonctions de base de données, les expressions de calcul, etc. Lors de l'interrogation, déplacez autant que possible l'opération vers la droite du signe égal. .

Tutoriel recommandé : "Tutoriel MySQL"