Résumé de l'optimisation de la base de données MySQL

Utiliser la base de données mysql dans le développement PHP est devenu une habitude pour tous les programmeurs PHP. Si nous voulons que PHP fasse fonctionner mysql plus rapidement et plus facilement, nous devons optimiser la base de données mysql, l'optimisation de MySQL est également fréquemment demandée dans les entretiens. jetons un coup d'oeil ensemble !

1. Optimisez votre cache de requêtes MySQL

Lors d'une requête sur le serveur MySQL, vous pouvez activer le cache de requêtes à grande vitesse. Laisser le moteur de base de données gérer les choses tranquillement en arrière-plan est l’un des moyens les plus efficaces d’améliorer les performances. Lorsque la même requête est exécutée plusieurs fois, c'est assez rapide si les résultats sont extraits du cache.
Mais le principal problème est qu'il est si facilement caché que la plupart d'entre nous, programmeurs, l'ignorons. Dans certaines tâches de traitement, nous pouvons effectivement empêcher le cache de requêtes de fonctionner.

// query cache does NOT work
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
 // query cache works!
 $today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");
 // query cache does NOT work
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
 // query cache works!
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

2. Utilisez EXPLAIN pour rendre votre requête SELECT plus claire

L'utilisation du mot-clé EXPLAIN est une autre astuce d'optimisation MySQL qui vous permet de comprendre quel type d'opérations de requête MySQL effectue. Cela peut vous aider à trouver les goulots d'étranglement et à montrer où la structure de la requête ou de la table ne va pas.
Les résultats de la requête EXPLAIN peuvent vous indiquer quels index sont référencés, comment la table est analysée et triée, etc.
Implémentez une requête SELECT (de préférence une requête plus complexe avec des jointures) et ajoutez-y l'explication de votre mot-clé. Ici, nous pouvons utiliser phpMyAdmin, qui vous indiquera les résultats du tableau. Par exemple, si j'oublie d'ajouter une colonne à un index lors de l'exécution de jointures, EXPLAIN peut m'aider à trouver le problème.

Après avoir ajouté l'index au champ group_id

3. Utilisez LIMIT 1 pour obtenir la ligne unique <.>

Parfois, lorsque vous souhaitez interroger une table, vous savez que vous n'avez besoin de regarder qu'une seule ligne. Vous recherchez peut-être un enregistrement très unique ou vous vérifiez simplement le nombre d'enregistrements existants qui satisfont à votre clause WHERE.
Dans ce cas, ajouter un LIMIT 1 rendra votre requête plus efficace. De cette façon, le moteur de base de données arrêtera l’analyse après en avoir trouvé un seul, au lieu d’analyser l’intégralité de la table ou de l’index.

 // do I have any users from Alabama?   
 // what NOT to do:   
 $r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'");   
 if (mysql_num_rows($r) > 0) {   
     // ...   
 }     
 // much better:   
 $r = mysql_query("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1");   
 if (mysql_num_rows($r) > 0) {   
     // ...   
 }

4. Champs de recherche dans l'index

Un index n'est pas seulement une clé primaire ou une clé unique. Si vous souhaitez rechercher n'importe quelle colonne du tableau, vous devez toujours pointer vers l'index.

5. Assurez-vous que les index connectés sont du même type

Si le application Avec plusieurs requêtes de jointure, vous devez vous assurer que les colonnes que vous joignez sont indexées sur les deux tables. Cela affecte la façon dont MySQL optimise les opérations de jointure interne. De plus, les colonnes ajoutées doivent être du même type. Par exemple, si vous rejoignez une colonne DECIMAL et en même temps une colonne int dans une autre table, MySQL ne pourra pas utiliser au moins un des indicateurs. Même le codage des caractères doit être le même que le type de chaîne.

 // looking for companies in my state   
 $r = mysql_query("SELECT company_name FROM users  
     LEFT JOIN companies ON (users.state = companies.state)  
     WHERE users.id = $user_id");   

 // both state columns should be indexed   
 // and they both should be the same type and character encoding   
 // or MySQL might do full table scans

6. N'utilisez pas la commande BY RAND()

这是一个令很多新手程序员会掉进去的陷阱。你可能不知不觉中制造了一个可怕的平静。这个陷阱在你是用BY RAND（）命令时就开始创建了。
如果您真的需要随机显示你的结果，有很多更好的途径去实现。诚然这需要写更多的代码，但是能避免性能瓶颈的出现。问题在于，MySQL可能会为表中每一个独立的行执行BY RAND()命令（这会消耗处理器的处理能力），然后给你仅仅返回一行。

 // what NOT to do:   
 $r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");   
 // much better:   
 $r = mysql_query("SELECT count(*) FROM user");   
 $d = mysql_fetch_row($r);   
 $rand = mt_rand(0,$d[0] - 1);   
 $r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

7. 尽量避免SELECT *命令

从表中读取越多的数据，查询会变得更慢。他增加了磁盘需要操作的时间，还是在数据库服务器与WEB服务器是独立分开的情况下。你将会经历非常漫长的网络延迟，仅仅是因为数据不必要的在服务器之间传输。
始终指定你需要的列，这是一个非常良好的习惯。

 // not preferred   
 $r = mysql_query("SELECT * FROM user WHERE user_id = 1");   
 $d = mysql_fetch_assoc($r);   
 echo "Welcome {$d['username']}";   
 // better:   
 $r = mysql_query("SELECT username FROM user WHERE user_id = 1");   
 $d = mysql_fetch_assoc($r);   
 echo "Welcome {$d['username']}";    
 // the differences are more significant with bigger result sets

8. 从PROCEDURE ANALYSE()中获得建议

PROCEDURE ANALYSE()可让MySQL的柱结构分析和表中的实际数据来给你一些建议。如果你的表中已经存在实际数据了，能为你的重大决策服务。

9. 准备好的语句

准备好的语句，可以从性能优化和安全两方面对大家有所帮助。
准备好的语句在过滤已经绑定的变量默认情况下，能给应用程序以有效的保护，防止SQL注入攻击。当然你也可以手动过滤，不过由于大多数程序员健忘的性格，很难达到效果。

 // create a prepared statement   
 if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) {    
     // bind parameters   
     $stmt->bind_param("s", $state);    
     // execute   
     $stmt->execute();    
     // bind result variables   
     $stmt->bind_result($username);     
     // fetch value   
     $stmt->fetch();    
     printf("%s is from %s\n", $username, $state);     
     $stmt->close();   
 }

10. 将IP地址存储为无符号整型

许多程序员在创建一个VARCHAR（15）时并没有意识到他们可以将IP地址以整数形式来存储。当你有一个INT类型时，你只占用4个字节的空间，这是一个固定大小的领域。
你必须确定你所操作的列是一个UNSIGNED INT类型的,因为IP地址将使用32位unsigned integer。

$r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";

11.永远为每张表设置一个ID

我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的(推荐使用UNSIGNED)，并设置上自动增加的AUTO_INCREMENT标志。
就算是你users表有一个主键叫“email”的字段，你也别让它成为主键。使用VARCHAR类型来当主键会使用得性能下降。另外，在你的程序中，你应该使用表的ID来构造你的数据结构。
而且，在MySQL数据引擎下，还有一些操作需要使用主键，在这些情况下，主键的性能和设置变得非常重要，比如，集群，分区……
在这里，只有一个情况是例外，那就是“关联表”的“外键”，也就是说，这个表的主键，通过若干个别的表的主键构成。我们把这个情况叫做“外键”。比如：有一个“学生表”有学生的ID，有一个“课程表”有课程ID，那么，“成绩表”就是“关联表”了，其关联了学生表和课程表，在成绩表中，学生ID和课程ID叫“外键”其共同组成主键。

12. Utilisez ENUM au lieu de VARCHAR

Le type ENUM est très rapide et compact. En fait, il enregistre TINYINT, mais il apparaît sous forme de chaîne. De cette façon, il devient tout à fait parfait d'utiliser ce champ pour faire des listes de choix.
Si vous avez un champ, tel que « sexe », « pays », « origine ethnique », « statut » ou « département », et que vous savez que les valeurs de ces champs sont limitées et fixes, alors vous devriez utilisez plutôt ENUM et non VARCHAR.
MySQL a également une "suggestion" (voir point 10) pour vous indiquer comment réorganiser la structure de vos tables. Lorsque vous disposez d'un champ VARCHAR, cette suggestion vous demandera de le changer en type ENUM. En utilisant PROCEDURE ANALYSE(), vous pouvez obtenir des suggestions pertinentes.

13. Obtenez des conseils de PROCEDURE ANALYSE() Programmer Station

PROCEDURE ANALYSE() permettra à MySQL de vous aider à analyser vos champs. et leurs données réelles et vous donne quelques suggestions utiles. Ces suggestions ne deviendront utiles que si le tableau contient des données réelles, car certaines décisions importantes nécessitent des données comme base.
Par exemple, si vous créez un champ INT comme clé primaire, mais qu'il n'y a pas beaucoup de données, alors PROCEDURE ANALYSE() vous recommandera de changer le type de ce champ en MEDIUMINT. Ou si vous utilisez un champ VARCHAR, parce qu'il n'y a pas beaucoup de données, vous pourriez recevoir une suggestion pour le remplacer par ENUM. Ces suggestions sont toutes possibles car il n’y a pas suffisamment de données, donc la prise de décision n’est pas assez précise.
Dans phpmyadmin, vous pouvez cliquer sur "Proposer la structure du tableau" lorsque vous consultez le tableau pour afficher ces suggestions

Assurez-vous de noter que ce ne sont que des suggestions, uniquement lorsqu'il y a de plus en plus de données dans votre tableau, ces recommandations deviendront exactes. N'oubliez pas que c'est vous qui prenez la décision finale

14 Utilisez autant que possible la station de programmation php NOT NULL

Sauf si vous avez une raison très précise d'utiliser des valeurs NULL, vous devez toujours garder vos champs NON NULL. Cela peut sembler un peu controversé, veuillez continuer à lire.
Tout d'abord, demandez-vous quelle est la différence entre "Vide" et "NULL" (si c'est INT, c'est 0 et NULL) Si vous sentez qu'il n'y a pas de différence entre eux, alors vous ne devriez pas utiliser NULL ? . (Le saviez-vous ? Dans Oracle, les chaînes NULL et vides sont identiques !)
Ne pensez pas que NULL ne nécessite pas d'espace, il nécessite de l'espace supplémentaire, et lorsque vous effectuez des comparaisons, votre programme sera plus complexe. Bien sûr, cela ne signifie pas que vous ne pouvez pas utiliser NULL. La réalité est très compliquée et il y aura toujours des situations dans lesquelles vous devrez utiliser des valeurs NULL.
Ce qui suit est extrait de la propre documentation de MySQL :

15. Les déclarations préparées

Les déclarations préparées sont très utiles. Semblable aux procédures stockées, il s'agit d'un ensemble d'instructions SQL exécutées en arrière-plan. Nous pouvons tirer de nombreux avantages de l'utilisation d'instructions préparées, qu'il s'agisse de problèmes de performances ou de problèmes de sécurité.
Les instructions préparées peuvent vérifier certaines variables que vous avez liées, ce qui peut protéger votre programme contre les attaques par « injection SQL ». Bien entendu, vous pouvez également vérifier vos variables manuellement. Cependant, les vérifications manuelles sont sujettes à des problèmes et sont souvent oubliées par les programmeurs. Lorsque nous utilisons un framework ou un ORM, ce problème sera meilleur.
En termes de performances, cela vous apportera des avantages considérables en termes de performances lorsque la même requête est utilisée plusieurs fois. Vous pouvez définir certains paramètres pour ces instructions préparées, et MySQL ne les analysera qu'une seule fois.
Bien que la dernière version de MySQL utilise le format binaire lors de la transmission des instructions préparées, cela rendra la transmission réseau très efficace.
Bien sûr, il existe certains cas où nous devons éviter d'utiliser les instructions préparées car elles ne prennent pas en charge la mise en cache des requêtes. Mais on dit qu'il est supporté après la version 5.1. Pour utiliser des instructions préparées en PHP, vous pouvez consulter son manuel : extension mysqli ou utiliser une couche d'abstraction de base de données, telle que : PDO.

Requête sans tampon<.>

Dans des circonstances normales, lorsque vous exécutez une instruction SQL dans votre script, votre programme s'arrêtera là jusqu'à ce qu'aucune instruction SQL ne revienne, puis votre programme continuera l'exécution ci-dessous. Vous pouvez utiliser des requêtes sans tampon pour modifier ce comportement.
À ce sujet, il y a une très bonne explication dans la documentation PHP : fonction mysql_unbuffered_query() :
La traduction de la phrase ci-dessus signifie que mysql_unbuffered_query() envoie une instruction SQL à MySQL. pas automatiquement récupérer et mettre en cache les résultats comme mysql_query(). Cela permettra d'économiser beaucoup de mémoire, en particulier pour les requêtes qui génèrent un grand nombre de résultats, et vous n'aurez pas besoin d'attendre que tous les résultats soient renvoyés. Il vous suffit de renvoyer la première ligne de données et vous pouvez commencer. fonctionne immédiatement. Les résultats de la requête sont disponibles.
Cependant, cela comporte certaines limites. Parce que vous devez soit lire toutes les lignes, soit appeler mysql_free_result() pour effacer les résultats avant la requête suivante. De plus, mysql_num_rows() ou mysql_data_seek() ne fonctionnera pas. Par conséquent, vous devez réfléchir attentivement à l’opportunité d’utiliser des requêtes sans tampon.

17. Enregistrez l'adresse IP sous UNSIGNED INT

De nombreux programmeurs créeront un champ VARCHAR(15) pour stocker la chaîne de caractères. IP au lieu d’IP façonné. Si vous utilisez un entier pour le stocker, cela ne prend que 4 octets et vous pouvez avoir des champs de longueur fixe. De plus, cela vous apportera des avantages dans les requêtes, surtout lorsque vous devez utiliser des conditions WHERE comme celle-ci : IP entre ip1 et ip2.
Nous devons utiliser UNSIGNED INT car l'adresse IP utilise l'intégralité de l'entier non signé de 32 bits.
Pour votre requête, vous pouvez utiliser INET_ATON() pour convertir une chaîne IP en entier, et utiliser INET_NTOA() pour convertir un entier en chaîne IP. En PHP, il existe également des fonctions telles ip2long() et long2ip().

18. Les tableaux de longueur fixe seront plus rapides

Si tous les champs du tableau sont de "longueur fixe"" , la table entière sera considérée comme « statique » ou « de longueur fixe ». Par exemple, la table ne contient aucun champ des types suivants : VARCHAR, TEXT, BLOB. Tant que vous incluez l'un de ces champs, la table n'est plus une "table statique de longueur fixe" et le moteur MySQL la traitera d'une autre manière.
Les tables de longueur fixe amélioreront les performances car MySQL recherchera plus rapidement. Parce que ces longueurs fixes facilitent le calcul du décalage des données suivantes, la lecture sera naturellement plus rapide. Et si le champ n'est pas de longueur fixe, chaque fois que vous souhaitez trouver le suivant, le programme doit trouver la clé primaire.
De plus, les tables de longueur fixe sont plus faciles à mettre en cache et à reconstruire. Cependant, le seul effet secondaire est que les champs de longueur fixe gaspillent de l'espace, car les champs de longueur fixe nécessitent beaucoup d'espace, que vous les utilisiez ou non. Station de programmation PHP
Grâce à la technologie "vertical split" (voir élément suivant), vous pouvez diviser votre table en deux, une avec une longueur fixe et une avec une longueur variable.

19. Division verticale

La « division verticale » est un moyen de transformer les tables de la base de données en plusieurs tables par colonnes. méthode, qui peut réduire la complexité de la table et le nombre de champs, atteignant ainsi l'objectif d'optimisation. (Avant, je travaillais sur un projet dans une banque et j'ai vu une table avec plus de 100 champs, ce qui faisait peur)
Exemple 1 : Il y a un champ dans la table Utilisateurs qui est l'adresse du domicile. Ce champ est facultatif. avec , et lorsque vous exploitez la base de données, à l'exception des informations personnelles, vous n'avez pas besoin de lire ou de réécrire fréquemment ce champ. Alors, pourquoi ne pas le mettre dans une autre table ? Cela améliorera votre table

Recommandations associées :

Résumé de l'optimisation MySQL- nombre total de requêtes

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!