Optimisation PHP-Mysql

Le contenu présenté dans cet article concerne l'optimisation PHP mysql, qui a une certaine valeur de référence. Maintenant, je le partage avec tout le monde. Les amis dans le besoin peuvent se référer à

a. quelques articles Conseils MySQL

• 1. Il est préférable d'écrire les mots clés dans les instructions SQL en majuscules , premièrement, il est facile de distinguer les mots-clés et les objets d'opération. Deuxièmement, lorsque l'instruction SQL est exécutée, MySQL la convertira en majuscules. L'écriture manuelle des majuscules peut augmenter l'efficacité des requêtes (même si elle est très faible).

2. Si nous ajoutons ou supprimons des lignes de données dans la base de données, l'ID de données sera trop grand. Utilisez

• ALTER TABLE tablename AUTO_INCREMENT=N , . fait que l'ID à incrémentation automatique commence à compter à partir de N.

3. Ajoutez l'attribut

• ZEROFILL au type int pour ajouter automatiquement 0 aux données

4. Lors de l'importation d'une grande quantité de données, il est préférable de supprimer d'abord l'index, d'insérer les données, puis d'ajouter l'index, sinon MySQL passera beaucoup de temps à mettre à jour l'index.
5. Lors de la création d'une base de données et de l'écriture d'instructions SQL, nous pouvons créer un fichier avec le suffixe .sql dans l'EDI reconnaîtra la syntaxe SQL, ce qui facilitera l'écriture. . Plus important encore, si votre base de données est perdue, vous pouvez toujours retrouver ce fichier en utilisant

• /path/mysql -uusername -ppassword databasename < filename.sql dans le répertoire actuel. instruction sql de l'intégralité du fichier (notez que -u et -p sont suivis du nom d'utilisateur et du mot de passe sans espaces).

---

Optimisation de la conception de la base de données1 , La conception de la base de données est conforme au troisième paradigme et il peut y avoir une certaine redondance des données pour faciliter l'interrogation. 2. Sélectionnez la priorité du type de données int > date, time > enum, char > blob Lors de la sélection du type de données, vous pouvez envisager de le remplacer. unsign int tapez en utilisant la fonction ip2long() pour stocker.
3. Pour le type char(n), essayez de garder la valeur n aussi petite que possible si les données sont complètes.
4. Lors de la création d'une table, utilisez la commande partition pour partitionner une seule

table pour améliorer considérablement l'efficacité des requêtes. MySQL prend en charge les types de partition RANGE, LIST, HASH et KEY, dont RANGE est la plus populaire. Les méthodes de partitionnement couramment utilisées sont : CREATE TABLE tablename{}ENGINE innodb/myisam CHARSET utf8 //Sélectionnez le moteur de base de données et l'encodage
PARTITION BY RANGE/LIST(column) ,//Par plage et liste prédéfinie Partition
　PARTITION partname VALUES LESS THAN /IN(n),//Nommez la partition et limitez la portée de la partition en détail
5. 🎜>
La différence entre innodb et myisam
. Structure de stockage : MyISAM est stocké dans trois fichiers sur le disque. Toutes les tables d'InnoDB sont stockées dans le même fichier de données, généralement 2 Go Prise en charge des transactions : MyISAM ne fournit pas de prise en charge des transactions. InnoDB fournit un support pour les transactions. Différences de verrouillage de table : MyISAM ne prend en charge que les verrous au niveau de la table. InnoDB prend en charge les transactions et les verrous au niveau des lignes.
Index de texte intégral : MyISAM prend en charge l'index de texte intégral de type FULLTEXT (non applicable au chinois, le moteur d'indexation de texte intégral sphinx doit donc être utilisé). InnoDB ne le prend pas en charge.
Le nombre spécifique de lignes dans le tableau : MyISAM enregistre le nombre total de lignes dans le tableau et l'interrogation de count(*) est très rapide. InnoDB n'enregistre pas le nombre total de lignes dans le tableau et doit être recalculé.
Clés étrangères : MyISAM ne le prend pas en charge. InnoDB prend en charge

---

optimisation d'index

1. InnoDB est un index clusterisé, et il doit y avoir une clé primaire lorsque stocker l'index. Si cela n'est pas spécifié, le moteur générera automatiquement une clé primaire cachée et générera un index primaire
L'adresse physique de la clé primaire est stockée dans l'index. les données sont stockées par la clé primaire. Chaque fois que l'index est utilisé, vous devez d'abord trouver l'index principal, puis trouver les données sous l'index principal. L'avantage est que la recherche via la clé primaire est très rapide. L'inconvénient est que l'index secondaire sera plus lent car vous devez d'abord trouver l'index primaire via l'index secondaire (l'index secondaire est l'emplacement de l'index principal.), puis recherchez les données de l'index principal. Et si la clé primaire est irrégulière, davantage de blocs de données doivent être déplacés lors de l'insertion de nouvelles valeurs, ce qui affectera l'efficacité, alors essayez d'utiliser un type int qui augmente régulièrement comme clé primaire. De plus, comme les données sont placées immédiatement après la clé primaire, s'il y a des colonnes (texte/blob) avec une quantité de données particulièrement importante dans les données, InnoDB ignorera de nombreux blocs de données pendant la requête, ce qui entraînera également une lenteur.

2. Chaque index de myisam pointe vers la même adresse de chaque ligne du disque. Ce sont toutes des données de pointeur légères. L'inconvénient est que chaque index n'est pas établi via la clé primaire et que la requête n'est pas aussi rapide que la recherche de la clé primaire dans l'index clusterisé. Mais comme il stocke l'adresse, l'aspect de la comparaison se déplace et change lors de l'insertion d'une nouvelle valeur.
3. Lors de l'exécution d'une requête multi-conditions, lors de la création d'index pour plusieurs conditions séparément, lors de l'exécution d'une requête SQL, MySQL sélectionnera uniquement l'index le plus proche à utiliser. Par conséquent, si une requête multi-conditions est requise, une requête conjointe. L'index doit être établi, même s'il provoque une redondance des données.
La méthode d'établissement BTREE de l'index conjoint : indexer la première condition, indexer la deuxième condition dans la zone BTREE du premier index, et ainsi de suite, donc lors de l'utilisation de l'index, Utiliser la seconde une condition sans utiliser la première condition n'utilisera pas l'index conjoint . Lors de l'utilisation d'index, les conditions doivent être dans l'ordre et utilisées dans l'ordre.
4. La longueur de l'index a également un grand impact sur la requête. Nous devrions essayer de construire une longueur d'index courte. Nous pouvons utiliser la colonne de requête
SELECT COUNT(DISTINCT LEFT(column)) / COUNT(*) FROM tablename pour tester la requête. Différentes longueurs sont sélectionnées lors de la création d'un index pour la colonne colonne. Quelle est la taille de la couverture de l'index ? Choisissons n longueurs proches de la saturation pour créer un index
ALTER TABLE tablename ADD INDEX. (column(n)); pour indexer la première partie d'une certaine colonne de n caractères pour créer un index. Si les n premiers caractères sont identiques, nous pouvons même inverser la chaîne et la stocker, puis créer un index.
5. Comment maintenir la fragmentation de l'index causée par des modifications fréquentes : ALTER TABLE tablename ENGINE oldengine ; c'est-à-dire, appliquez à nouveau le moteur de stockage de table pour activer la maintenance automatique, vous pouvez également utiliser la commande OPTIMIZE tablename pour la maintenance ;

---

Optimisation des requêtes de données
Essayez de conserver le moins de requêtes possible dans les opérations de base de données, et essayez de ne pas le faire au niveau de la base de données lorsqu'il y a des requêtes. Au lieu d'effectuer des opérations sur les données sur la base de données, revenez au script PHP pour exploiter les données afin de réduire la pression sur la base de données.
Une fois qu'un problème de performances de base de données est découvert, il doit être résolu à temps. Généralement, utilisez Requête lenteSi la requête de journal est "lente", utilisez EXPLAINAnalysez l'utilisation des requêtes et des index, utilisez PROFILE pour analyser la consommation de ressources spécifique lors de l'exécution de l'instruction.
Journal des requêtes lentes : 1. Ajoutez
slow_query_log_file=/ sous [mysqld] dans le chemin my.ini ou my.cnf. //Définissez le chemin de stockage du journal
long_query_time=n //Définissez si le temps d'exécution de l'instruction atteint n secondes, il sera enregistré
2. Définissez ensuite SET slow_query_log='ON' dans MySQL pour activer les requêtes lentes.
3. Après avoir enregistré le journal, nous utilisons le nom de fichier mysqldumpslow dans le répertoire /bin/ pour afficher le journal. Ses paramètres communs sont les suivants :
　-g pattern Utiliser des expressions régulières
　-t nRenvoyer le premier n. éléments de données
　-s c/t/l/r Trier par nombre d'enregistrements/heure/heure de requête/nombre d'enregistrements renvoyés

Instruction EXPLAIN Méthode d'utilisation, ajoutez EXPLAIN
EXPLAIN SELECT * FROM user;
devant l'instruction de requête à exécuter pour obtenir le résultat comme indiqué ci-dessous :

Le Voici une explication de chaque élément :
id est l'identifiant de l'instruction de requête. Une simple requête n'a aucun sens dans le cas de plusieurs requêtes, l'ordre d'exécution de la requête est le
select-type. type d'instruction de requête à exécuter. Elle correspond à plusieurs requêtes, notamment simple/primaire/union, etc.
tabel La table de données interrogée par l'instruction de requête

type Le type de données obtenu de haut en bas est null>const>eq_ref>ref>. ;range>index> ;allpossible-keys : Index éventuellement utilisés

key Index utiliséskey_len index length
ref Quelle colonne utiliser avec le index Sélectionner dans le tableau.

lignes Trouver le nombre approximatif de lignes à scanner pour trouver les données, qui montrent la qualité de l'index
extra Courant Il y a utiliser le tri de fichiers pour trier les fichiers après avoir interrogé les données, ce qui est lent et nécessite d'optimiser l'index
en utilisant où lire la ligne entière de données, puis de juger et de filtrer si elle répond aux où condition
en utilisant la couverture de l'index, c'est-à-dire dans Les données cibles sont déjà stockées dans la traction, et l'index est lu directement, ce qui est très rapide.

PROFILE
Utilisez SELECT @@frofiling pour vérifier l'état d'ouverture de PROFILE.
S'il n'est pas activé, utilisez SET profiling=1 pour l'activer.
Après l'avoir activé, MySQL enregistrera automatiquement les informations de profil lorsque vous exécuterez l'instruction de requête.
Appliquez les profils d'affichage pour afficher toutes les informations SQL. Le résultat est des résultats à trois colonnes de la requête de durée Query_ID, qui sont l'ID de requête, l'heure et l'instruction SQL utilisée.
Nous pouvons utiliser
SHOW PFROFILE [type[,type]][FOR QUREYQuery_ID][Limit rwo_count [OFFSET offset]]
Les types courants incluent ALL (tous) BLOCK IO (affiche la surcharge liée aux IO) CPU (surcharge du CPU) MEMORY (surcharge de mémoire), etc.

---

Optimisation du grand stockage
Réplication maître-esclave de la base de données et séparation lecture-écriture
1. Le maître enregistrera les modifications dans le binaire. log, et l'esclave enregistrera les modifications du maître. Le binaire est copié dans son journal de relais et les données sont renvoyées dans ses propres données pour atteindre l'objectif de répliquer les données du serveur principal.

La réplication maître-esclave peut être utilisée pour : l'équilibrage de charge de la base de données, la sauvegarde de la base de données, la séparation en lecture-écriture et d'autres fonctions.
2. Configurer le serveur principal maître
Modifier my.ini/my.conf
[mysqld]
log-bin=mysql-bin //Activer les journaux binaires
server-id=102 / /ID unique du serveur
3. Configurer le serveur esclave
log-bin=mysql-bin //Activer le journal binaire
server-id=226 //ID unique du serveur
4. server Autoriser le serveur esclave
GRANT REPLICATION SLAVE ON *.* à 'slavename'@'IP' identifié par 'root'
5. Utiliser sur le serveur esclave
changer master en
master_host=" masterip" ,
　master_user="masteruser",
　master_password="masterpasswd";
6. Utilisez ensuite la commande start slave pour démarrer la réplication maître-esclave.
N'oubliez pas de redémarrer le serveur après chaque modification de configuration. Vous pouvez ensuite utiliser show master/slave status sur les serveurs maître et esclave pour visualiser l'état maître/esclave.
La séparation de la lecture et de l'écriture dans la base de données repose sur des middleware MySQL, tels que mysql_proxy, atlas, etc. En configurant ces middlewares pour séparer la lecture et l'écriture entre les serveurs maître et esclave, le serveur esclave assume la responsabilité d'être lu, réduisant ainsi la charge sur le serveur maître.

Partage de base de données
Lorsque la quantité de données dans la table de données de la base de données est très importante, l'indexation et la mise en cache sont soumises à une forte pression . Si la base de données est volumineuse, partitionnez-la afin qu'elle soit stockée sur plusieurs serveurs de base de données ou plusieurs tables afin de réduire la pression des requêtes.
Les méthodes incluent la segmentation verticale, la segmentation horizontale et la segmentation combinée.
Segmentation verticale  : Lorsqu'il y a beaucoup de tables de données, divisez les tables qui sont étroitement liées dans la base de données (comme le même module, souvent connecté et interrogé) et placez-les à différents endroits sur le serveur maître-esclave.
Segmentation horizontale : Lorsqu'il n'y a pas beaucoup de tables et que la quantité de données dans la table est très importante, afin d'accélérer la requête, vous pouvez utiliser le hachage et d'autres algorithmes pour diviser une table de données en Divisez-la en plusieurs et placez-les sur différents serveurs pour accélérer les requêtes. La différence entre le partitionnement horizontal et le partitionnement de tables de données réside dans la différence de support de stockage.
Segmentation conjointe  : Le plus souvent, le tableau de données et la quantité de données dans le tableau sont très volumineux, une segmentation conjointe est donc nécessaire, c'est-à-dire verticale et horizontale La segmentation est effectuée en même temps que le tableau, la base de données est divisée en une matrice distribuée pour le stockage.
Chacune de ces méthodes d'optimisation de base de données peut être utilisée pour rédiger un article. On peut dire qu'elle est profonde et profonde. Si vous comprenez et mémorisez ces méthodes, vous pouvez effectuer une sélection et une optimisation ciblées lorsque cela est nécessaire pour atteindre une efficacité élevée de la base de données. .

Recommandations associées :

Utilisation de la session d'optimisation PHP

Parcours d'apprentissage PHP et 10 conseils d'optimisation PHP

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!