Explication détaillée du code d'optimisation InnoDB pour l'optimisation MySQL

InnoDB est conçu pour des performances maximales lorsque Mysql gère d'énormes quantités de données. Son efficacité CPU est probablement inégalée par aucunautremoteur de base de données relationnelle sur disque. Innodb est très populaire parmi les sites Web ou les applications contenant de grandes quantités de données. Il n’a donc pas besoin d’être optimisé ? La réponse est évidente : bien sûr que non ! ! !

Les plans d'études sont facilement interrompus et la persévérance n'est pas facile. Récemment, il y a eu une réunion dans l'entreprise pour ajuster l'orientation commerciale. Il est recommandé d'apprendre NodeJS. Je connaissais un peu NodeJS auparavant, mais je ne l’ai pas étudié en profondeur. La syntaxe de Node est fondamentalement la même que celle des J côté client. Au cours des six derniers mois, peu de choses côté client ont été développées. A l’origine, j’avais de bonnes bases en JS, mais je ne connaissais pas ces connaissances. Il semble que les connaissances soient utilisées ou perdues. Si elles ne sont pas utilisées fréquemment, elles seront vite oubliées. J'ai donc revu les connaissances pertinentes en JS. J'ai appris la connaissance du serveur et des sockets de Node. Le plan MySQL a été suspendu le dimanche, j'ai mangé, bu et dormi, j'étais terriblement paresseux le matin et je suis resté éveillé jusqu'à l'après-midi. Sans plus tarder, poursuivons la série d'optimisation MySQL, en examinant cette fois les éléments d'optimisation d'InnoDB.

L'index principal d'InnoDB est un index clusterisé, et l'index et les données partagent un espace table commun. Pour InnoDB, les données sont l'index et l'index est les données. La plus grande différence entre le mécanisme de mise en cache d'InnoDB et MyISAM est qu'InnoDB met non seulement en cache les index, mais met également en cache les données. 1. Le pool de cache InnoDB

Le pool de tampons InnoDB est la clé pour améliorer les performances d'InnoDB. Il peut mettre en cache à la fois les données et les index. , verrous au niveau des lignes), etc. Vous pouvez utiliser des variables show telles que « innodb%pool% » pour afficher les options de paramètres associées.

innodb_buffer_pool_size

mysql> show variables like 'innodb%pool%';
+-------------------------------------+----------------+
| Variable_name            | Value     |
+-------------------------------------+----------------+
| innodb_additional_mem_pool_size   | 8388608    |
| innodb_buffer_pool_dump_at_shutdown | OFF      |
| innodb_buffer_pool_dump_now     | OFF      |
| innodb_buffer_pool_filename     | ib_buffer_pool |
| innodb_buffer_pool_instances    | 8       |
| innodb_buffer_pool_load_abort    | OFF      |
| innodb_buffer_pool_load_at_startup | OFF      |
| innodb_buffer_pool_load_now     | OFF      |
| innodb_buffer_pool_size       | 134217728   |
+-------------------------------------+----------------+

innodb_buffer_pool_size est utilisé pour définir la taille du pool de cache InnoDB (InnoDBBufferPool la valeur par défaut). est de 128 Mo. La taille du pool de cache InnoDB a un plus grand impact sur les performances globales d'InnoDB. Si le serveur MySQL actuel est exclusivement utilisé comme service MySQL, vous pouvez augmenter la taille de ce paramètre autant que possible.

innodb_buffer_pool_instance

La valeur par défaut de innodb_buffer_pool_instance est 1, ce qui signifie que le pool de cache InnoDB est divisé en une région. Augmenter de manière appropriée la valeur du paramètre peut améliorer les performances de concurrence. d'InnoDB.

innodb_additional_mem_pool_size

Spécifie la taille du cache utilisée par InnoDB pour stocker le dictionnaire de données et d'autres données internes. La valeur par défaut est de 2 Mo. Plus InnoDB a de tables, plus il y en a. Augmentez la taille de ce paramètre de manière appropriée.

2. Structure interne du pool de cache InnoDB

InnoDB maintient un pool de cache en mémoire pour la mise en cache des données et des index. Le pool de cache peut être considéré comme une longue liste chaînée (list). La liste chaînée est divisée en deux listes sous-liées, une liste sous-liée stocke les anciennes données de page et les anciennes données de page sont une page de données qui n'a pas été consultée depuis longtemps. Une liste sous-liée stocke la

nouvelle

page, et la nouvelle page est la page de données la plus récemment consultée. L'ancienne page représente par défaut 37 % de la taille totale de la liste chaînée, qui peut être consultée via le paramètre innodb_old_blocks_pct

L'intersection de l'ancienne page et de la nouvelle. la page est appelée point médian.

Lorsqu'un utilisateur accède aux données, InnoDB recherchera d'abord les données dans le cache InnoDB. S'il n'y a pas de données dans le pool de cache, InnoDB insérera les données du disque dur dans le pool de cache InnoDB. le pool de cache est plein, puis utilisez l'algorithme LRU pour effacer les anciennes données expirées

mysql> show variables like 'innodb_old_blocks%';
+------------------------+-------+
| Variable_name     | Value |
+------------------------+-------+
| innodb_old_blocks_pct | 37  |
| innodb_old_blocks_time | 1000 |
+------------------------+-------+

3. Échauffement du pool de cache InnoDB.

Après le démarrage du serveur MySQL pendant un certain temps, InnoDB placera les données fréquemment consultées (données commerciales, données de gestion) dans le cache InnoDB, c'est-à-dire que le pool de cache InnoDB stocke les données fréquemment consultées. (appelé données chaudes). Lorsque la taille du pool de cache InnoDB est de dizaines ou de centaines de gigaoctets, si vous redémarrez MySQL, que se passe-t-il si vous chargez les données chaudes du pool de cache InnoDB précédent dans le pool de cache InnoDB ? Si vous comptez uniquement sur InnoDB lui-même pour réchauffer le pool de cache InnoDB, cela prendra beaucoup de temps pour les systèmes dont l'activité est occupée, un blocage à long terme est un grave accident de production et ne peut être toléré. Heureusement, MySQL version 5.6 prend en charge la sauvegarde des données chaudes sur le disque dur lors de l'arrêt du service. MySQL redémarre en chargeant d'abord les données chaudes du disque dur dans le cache InnoDB, ce qui peut raccourcir le temps de préchauffage et améliorer la simultanéité des tâches occupées. entreprise.

innodb_buffer_pool_dump_at_shutdown

mysql> show variables like '%innodb%pool%';
+-------------------------------------+----------------+
| Variable_name            | Value     |
+-------------------------------------+----------------+
| innodb_additional_mem_pool_size   | 8388608    |
| innodb_buffer_pool_dump_at_shutdown | OFF      |
| innodb_buffer_pool_dump_now     | OFF      |
| innodb_buffer_pool_filename     | ib_buffer_pool |
| innodb_buffer_pool_instances    | 8       |
| innodb_buffer_pool_load_abort    | OFF      |
| innodb_buffer_pool_load_at_startup | OFF      |
| innodb_buffer_pool_load_now     | OFF      |
| innodb_buffer_pool_size       | 134217728   |
+-------------------------------------+----------------+

La valeur par défaut est désactivée. Si le paramètre est activé et que le service MySQL est arrêté, le la chaleur dans le cache InnoDB sera Les données seront enregistrées sur le disque dur.

innodb_buffer_pool_load_at_starup

默认是关闭的，如果开启该参数，启动MySQL服务时，MySQL将本地硬盘的热数据加载到InnoDB缓存池中。

innodb_buffer_pool_dump_now

默认关闭，如果开启该参数，停止MySQL服务时，以手动方式将InnoDB缓存池中的热数据保存到本地硬盘。

innodb_buffer_pool_load_now

默认关闭，如果开启该参数，启动MySQL服务时，以手动方式将本地硬盘的数据加载到InnoDB缓存池中，

innodb_buffer_pool_filename

如果开启InnoDB预热功能，停止MySQL服务是，MySQL将InnoDB缓存池中的热数据保存到数据库根目录下，默认文件名是这个参数的值。

开启InnoDB缓存后，可以使用如下命令查看当前InnoDB缓存池预热的状态信息：

show status like 'innodb_buffer%';
+---------------------------------------+-------------+
| Variable_name             | Value    |
+---------------------------------------+-------------+
| Innodb_buffer_pool_dump_status    | not started |
| Innodb_buffer_pool_load_status    | not started |
| Innodb_buffer_pool_pages_data     | 218     |
| Innodb_buffer_pool_bytes_data     | 3571712   |
| Innodb_buffer_pool_pages_dirty    | 0      |
| Innodb_buffer_pool_bytes_dirty    | 0      |
| Innodb_buffer_pool_pages_flushed   | 1      |
| Innodb_buffer_pool_pages_free     | 7973    |
| Innodb_buffer_pool_pages_misc     | 0      |
| Innodb_buffer_pool_pages_total    | 8191    |
| Innodb_buffer_pool_read_ahead_rnd   | 0      |
| Innodb_buffer_pool_read_ahead     | 0      |
| Innodb_buffer_pool_read_ahead_evicted | 0      |
| Innodb_buffer_pool_read_requests   | 1497    |
| Innodb_buffer_pool_reads       | 219     |
| Innodb_buffer_pool_wait_free     | 0      |
| Innodb_buffer_pool_write_requests   | 1      |
+---------------------------------------+-------------+

这里面的英语都比较简单，就不解释了。

四、InnoDB实时监控

mysql> show engine innodb status\G

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!