Recommandations d'apprentissage gratuites : tutoriel vidéo MySQL
Répertoire d'articles
1 Plusieurs aspects qui affectent les performances
1.1 Matériel
Habituellement, les ordinateurs personnels sont lents, et nous disons tous que c'est à cause de problèmes matériels informatiques, généralement des facteurs tels que le processeur, la mémoire, les E/S du disque, etc. donc ce problème se produira également sur le serveur.
1.2 Système serveur
Généralement, le système d'exploitation des ordinateurs personnels est Windows. Les performances des différentes versions des systèmes Windows sont différentes ou certains paramètres sont configurés pour provoquer. performances différentes. C'est la même chose pour les systèmes serveur, et les paramètres définis affecteront également les performances du serveur.
1.3 Sélection du moteur de stockage de base de données
MySQL dispose d'un moteur de stockage plug-in, et différents moteurs de stockage peuvent être sélectionnés en fonction des différents besoins de l'entreprise.
Différents moteurs de stockage ont également des caractéristiques différentes :
1.4 Configuration des paramètres de base de données
Pour différents moteurs de stockage, leurs configurations de paramètres sont différentes. Certains paramètres ont un impact minimal sur le moteur de stockage, mais certains paramètres jouent. un rôle déterminant dans la performance. Par conséquent, il est également important d’optimiser les paramètres en fonction du moteur de stockage sélectionné et des différents besoins de l’entreprise.
1.5 Conception de la structure de la base de données et instructions SQL (points clés)
Lorsque nous concevons la structure de la base de données, nous devons réfléchir au type d'instructions SQL que nous exécuterons sur la base de données à l'avenir, pour interroger et mettre à jour la structure de la table. Ce n'est qu'ainsi qu'une structure de table répondant aux exigences peut être conçue.
Pour les requêtes lentes, c'est le principal responsable des faibles performances, et cela est dû à notre conception déraisonnable de la structure des tables de la base de données. Ce type de SQL est également le plus difficile à optimiser, car une fois le projet mis en ligne, il est difficile de modifier la structure des tables de la base de données.
Nous nous concentrons donc sur l'optimisation des performances de la base de données :
Conception de la structure des tables de base de données
Rédaction et optimisation d'instructions SQL
Ce qui suit est une description détaillée de chaque aspect.
2 Aspects matériels
2.1 Ressources CPU et taille de mémoire disponible
2.1.1 Comment choisir le CPU
Habituellement, lors du choix d'un processeur, nous espérons tous que la fréquence et le nombre de cœurs du processeur sont aussi élevés que possible, mais en raison du coût ou de divers facteurs, nous sommes souvent obligés de choisir un seul d'entre eux. Alors, comment choisir la meilleure solution ? Par conséquent, nous devons prêter attention à plusieurs problèmes lors de l’achat d’un processeur :
2.1.2 Mémoire
La taille de la mémoire affecte directement les performances de la base de données. La mémoire est actuellement bien plus efficace que le disque. Par conséquent, la mise en cache des données en mémoire peut améliorer considérablement les performances du serveur.
2.1.2.1 Moteurs de stockage MySQL couramment utilisés
Il existe deux moteurs de stockage couramment utilisés : MyISAM et InnoDB.
MyISAM :
L'index est stocké en mémoire et les données sont enregistrées sur le disque dur.
InnoDB :
Les index et les données sont stockés en mémoire, améliorant ainsi l'efficacité opérationnelle de la base de données.
2.1.2.3 Comment choisir la mémoire
Essayez d'utiliser la mémoire que la carte mère peut prendre en charge à la fréquence maximale
2.2 Configuration et sélection du disque
Bien que la mémoire joue un rôle important dans les performances de la base de données, nous ne pouvons pas ignorer l'impact du sous-système IO sur les performances . À l'heure actuelle, nous utilisons couramment les 4 types d'options de disque suivants :
2.2.1 Utilisation de disques durs de machines traditionnels
Caractéristiques : grand espace de stockage, prix bas, la plupart utilisé, le plus courant, la lecture et l'écriture sont lentes
2.2.2 Utiliser le RAID pour améliorer les performances des disques durs des machines traditionnelles
2.2.2.1 Qu'est-ce que le RAID
RAID est la redondance des disques L'abréviation de Redundant Arrays of Independent Disks. En termes simples, la fonction du RAID est de combiner plusieurs disques de plus petite capacité en un groupe de disques de plus grande capacité et de fournir une redondance des données pour garantir l'intégrité des données.
2.2.2.2 RAID级别
2.2.2.2.1 RAID 0
RAID 0 est le premier mode RAID, également appelé répartition des données. Il s'agit de la forme la plus simple parmi les baies de disques de composants. Elle ne nécessite que plus de 2 disques durs. Elle est peu coûteuse et peut améliorer les performances et le débit de l'ensemble du disque. RAID 0 ne fournit pas de capacités de redondance ou de récupération d'erreur, mais constitue le coût de mise en œuvre le plus bas. Cependant, si l'on considère les facteurs de récupération des données et de fiabilité, RAID 0 est devenu la configuration la plus coûteuse, car il n'y a pas de redondance dans RAID 0 et la probabilité d'endommagement des données est plus élevée que dans un seul disque. Parce que des dommages aux données sur n'importe quel disque entraîneront une perte de données. Par exemple, un RAID 0 composé de trois disques est trois fois plus susceptible d'être endommagé qu'un seul disque dur.
Par conséquent, RAID 0 convient aux situations dans lesquelles aucune donnée ne sera perdue, telles que : une base de données de secours qui peut être clonée à partir d'autres bases de données à tout moment ou certaines bases de données qui ne doivent être utilisées qu'une seule fois.
En termes simples, RAID 0 consiste à connecter des disques durs en série pour former un disque plus grand, tel que :
Et dans le processus simultané, il peut atteindre l'équivalent de 3 fois les performances d’un seul disque dur.
RAID 1 est aussi appelé mirorisation de disque Le principe est de mettre en miroir les données d'un disque sur un autre disque, c'est-à-dire de mettre en miroir les données d'un disque sur un autre disque. disons, les données Lors de l'écriture sur un disque, un fichier image sera généré sur un autre disque restreint pour maximiser la fiabilité et la réparabilité du système sans affecter les performances.
La différence entre celui-ci et RAID 0 est qu'il y a un signe égal au milieu. Les données sur les deux disques sont les mêmes et ont de bonnes capacités de redondance, mais le coût augmentera en conséquence. Il peut fonctionner normalement même en cas de panne de disque, mais le disque défaillant doit être remplacé, sinon le système plantera.
Après le remplacement d'un nouveau disque, la synchronisation des données prendra beaucoup de temps. Même si cela n'affectera pas l'accès aux données, les performances du système seront réduites.
RAID 1 peut fournir de bonnes performances de lecture dans de nombreux cas, ainsi que des données redondantes entre différents disques, la redondance des données est donc très bonne. RAID 1 est meilleur en lecture que RAID 0, il est donc plus adapté au stockage de journaux ou de tâches similaires.
RAID 5 est également appelé matrice de disques à parité distribuée. Les données sont réparties sur plusieurs disques via des blocs de parité distribués, de sorte que si des données de disque échouent, elles peuvent être reconstruites à partir des blocs de parité. Mais si deux disques tombent en panne, l'intégralité des données du volume ne peut pas être récupérée.
On peut voir que chaque disque a respectivement Dp, Cp, Bp et Ap. S'il y a un problème avec l'un des disques, le disque peut être recalculé en fonction des données et des valeurs de parité. des trois autres disques.
Pour RAID 0 et RAID 1, il s'agit de la configuration redondante la plus économique, car l'ensemble de la configuration de la matrice ne nécessite que la capacité d'un seul disque.
Les écritures sont plus lentes sur RAID 5 car chaque écriture nécessite 2 lectures et 2 écritures entre disques pour calculer la valeur du chiffre de contrôle stocké. Cependant, les lectures aléatoires et séquentielles sont rapides, car il n'est pas nécessaire de calculer les bits de parité lorsque lecture, donc RAID 5 est plus adapté aux services de bases de données orientés lecture.
Le plus gros problème avec RAID 5 est lorsque le disque tombe en panne, car les données doivent être réaffectées à d'autres disques, ce qui affectera sérieusement les performances du disque, il est donc préférable d'utiliser RAID 5 en cas de lecture importante .
RAID 10 est également appelé mise en miroir partagée. Il exécute d'abord le RAID 1 sur les disques, puis exécute le RAID 0 sur les deux ensembles de disques RAID 1, ce qui lui confère de bonnes performances en lecture et en écriture. Par rapport au RAID 5, il est plus facile à reconstruire et plus rapide.
Sur RAID 10, si un disque dur est endommagé, cela aura un impact sérieux sur les performances, car pendant le processus de lecture et d'écriture, deux disques adjacents peuvent être lus en même temps. est endommagé, alors seules les lectures peuvent être effectuées à partir d'un seul disque, donc dans le pire des cas, nos performances seront réduites de 50 %.
等级 | 特点 | 是否冗余 | 盘数 | 读 | 写 |
---|---|---|---|---|---|
RAID 0 | 便宜,快速,危险 | 否 | N | 快 | 快 |
RAID 1 | 高速读,简单,安全 | 有 | 2 | 快 | 慢 |
RAID 5 | 安全,成本折中 | 有 | N+1 | 快 | 取决于最慢的盘 |
RAID 10 | 贵,高速,安全 | 有 | 2N | 快 | 快 |
2.2.3 Utilisation du stockage SSD et des cartes PCIe
Le stockage SSD est également appelé mémoire flash.
Caractéristiques :
Caractéristiques du SSD :
Caractéristiques de la carte PCIe de stockage à semi-conducteurs :
Scénarios d'utilisation du stockage SSD
2.2.4 Utiliser le stockage réseau NAS et SAN
SAN (Strorage Area Network) et NAS (Network-Attached Storage) sont deux méthodes pour monter des périphériques de stockage de fichiers externes sur le serveur.
SAN :
Le périphérique SAN est connecté au serveur via la fibre optique. L'appareil est accessible via l'interface de bloc et le serveur peut l'utiliser comme disque dur.
Fonctionnalités du SAN :
NAS :
Les appareils NAS utilisent des connexions réseau, via des protocoles basés sur des fichiers tels que NFS ou PME pour y accéder.
2.2.4.1 Scénarios d'utilisation du stockage réseau
Convient à la sauvegarde de base de données.
2.2.4.2 Limitations des performances du réseau
Les limitations des performances du réseau sont principalement la latence et la bande passante.
2.2.4.3 L'impact du réseau sur les performances
Résumé 2.3
CPU :
Mémoire :
Sous-système E/S :
Systèmes d'exploitation adaptés à MySQL : Windows, FreeBSD, Solaris, Linux
3.1 Optimisation des paramètres du système CentOSParamètres liés au noyau (/etc/sysctl.conf)
net.core.somaxconn = 65535
net.core.netdev_max_backlog=65535
net.ipv4.tcp_max_syn_backlog=65535
net.ipv4.tcp_fin_timeout = 10
net.ipv4.tcp_tw_reuse = 1
, net.ipv4.tcp_tw_recycle = 1
net.core.wmem_default = 87380
, net.core.wmem_max = 16777216
, net.core.r0mem_default = 87380
, net.core.rmem_max = 16777216
net.ipv4.tcp_keepalive_time = 120
, net.ipv4.tcp_keepalive_intvl = 30
, net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_keepalive_time
. signifie tcp L'intervalle de temps d'envoi des messages de détection tcp_keepalive, en secondes, utilisé pour confirmer si la connexion TCP est valide. net.ipv4.tcp_keepalive_intvl
est utilisé pour renvoyer le message de détection après avoir détecté que la connexion TCP ne répond pas. L'unité est en secondes net.ipv4.tcp_keepalive_probes
indique combien de messages de détection tcp_keepalive doivent être envoyés avant que la connexion TCP ne soit considérée comme invalide. Les valeurs par défaut de ces trois paramètres sont un peu trop grandes pour un système ordinaire, elles sont donc ici modifiées par des valeurs plus petites. kernel.shmmax = 4294967295
vm.swappiness = 0
free -m
pour afficher le système, nous pouvons voir quelque chose de similaire à ce qui suit, où swap est la partition d'échange. Lorsque le système d'exploitation ne dispose pas de suffisamment de mémoire, il écrira de la mémoire virtuelle dans la zone d'échange du disque et un échange de mémoire se produira. Augmenter les limites de ressources (/etc/security /limit.conf )limit.conf
Ce fichier est en fait le fichier de configuration de Linx PAM, qui est le module d'authentification du plug-in.
L'une des configurations de paramètres les plus importantes est la limite du nombre de fichiers ouverts.
Conclusion : augmentez le nombre de fichiers ouverts à 65 535 pour garantir que suffisamment de descripteurs de fichiers peuvent être ouverts.
Remarque : les modifications apportées à ce fichier doivent être redémarrées pour prendre effet.
Politique de planification de disque (/sys/block/devname/queue/scheduler)
Vous pouvez utiliser la commande cat /sys/block/sda/queue/scheduler
pour afficher la politique de planification utilisée par le disque actuel. Le noop anticipatory deadline [cfq]
suivant est la politique de planification cfq par défaut du système.
Sous le service de base de données MySQL, cfq ne convient pas car pendant le processus de travail de MySQL, cfq insérera des requêtes inutiles dans la file d'attente, ce qui entraînera un mauvais temps de réponse.
En plus de la stratégie de planification cfq, il existe également les stratégies suivantes :
noop (stratégie de planification des ascenseurs) :
date limite (stratégie de planification des délais) :
anticipative (politique de planification d'E/S anticipative) :
Nous pouvons saisir la commande suivante pour modifier la politique de planification des disques : echo schedulerName > /sys/block/sda/queue/scheduler
Par exemple : echo deadline > /sys/block/sda/queue/scheduler
4 Impact du système de fichiers sur les performances
Il est recommandé d'utiliser le système de fichiers XFS Les paramètres suivants doivent être configurés sous EXT3. et EXT4 :
Paramètres de montage du système EXT3/4 (/etc/fstab) :
data=writeback | ordered | journal
writeback
signifie que seul l'élément Data est écrit dans le journal et que l'écriture des métadonnées et l'écriture des données ne sont pas synchronisées. Il s'agit de la configuration la plus rapide et constitue généralement le meilleur choix pour InnoDB car InnoDB possède à l'origine son propre journal de transactions. ordered
n'enregistrera que les métadonnées, mais offre certaines garanties de cohérence. Avant d'écrire les métadonnées, les données seront d'abord écrites pour les rendre cohérentes. Cette option est légèrement plus lente que writeback
, mais elle sera plus sûre en cas de crash. . journal
Fournit un comportement de journalisation atomique, où les données seront enregistrées dans le journal avant d'être écrites dans le journal final. Cette option est évidemment inutile pour InnoDB et est la plus lente des trois. noatime
, nodiratime
/dev/sda1/ext4
: noatime,nodiratime,data=writeback 1 1
5 Architecture MySQL
L'architecture est au top Le niveau est appelé client. Cette couche représente le client qui peut se connecter à mysql via le protocole de connexion mysql, tel que PHP, JAVA, API C, .Net, ODBC, JDBC, etc. On peut voir d'ici que cette couche est pas unique à l'architecture MySQL. La plupart des services d'architecture CS adoptent cette architecture. Cette couche remplit principalement certaines fonctions telles que le traitement des connexions, l'authentification des autorisations et la sécurité. Chaque client connecté à MySQL a un thread dans le processus serveur. La requête de cette connexion ne sera exécutée que dans ce thread. Comme nous l'avons mentionné précédemment, chaque requête de connexion n'utilise qu'un seul cœur de processeur.
Ensuite, dans la deuxième couche de ce système, la plupart des services MySQL de base se trouvent dans cette couche, comme le montre la figure ci-dessous.
Nos instructions DDL ou DML couramment utilisées sont définies au niveau de cette couche. Mais nous devons juste nous rappeler une chose : toutes les fonctions du moteur de stockage croisé sont implémentées dans cette couche, car cette couche est également appelée couche de service.
La troisième couche de notre système structurel est la couche du moteur de stockage. MySQL est une très excellente base de données open source, qui définit une série d'interfaces du moteur de stockage, tant qu'elle répond aux exigences du moteur de stockage, nous pouvons développer MySQL. Créez un moteur de stockage qui répond pleinement à vos besoins, comme notre InnoDB couramment utilisé. Actuellement, il existe de nombreux moteurs de stockage pris en charge par MySQL, comme le montre la figure ci-dessous :
Remarque : Moteur de stockage Il est destiné aux tables plutôt qu'aux bibliothèques (différentes tables d'une bibliothèque peuvent utiliser différents moteurs de stockage) Ci-dessous, nous sélectionnons certains des moteurs de stockage les plus couramment utilisés pour une brève explication. Le moteur de stockage utilisé par MySQL sera. Les performances de la base de données ont un impact direct. J'espère également que vous pourrez bien comprendre certaines des caractéristiques du moteur de stockage avant d'utiliser le moteur de stockage.
Plus de recommandations d'apprentissage gratuites connexes : tutoriel MySQL(vidéo)
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!