Explication détaillée de l'optimisation du cache pour l'optimisation MySQL (1)

La question la plus fréquemment posée concerne la base de données MySQL l'optimisation des performances , j'ai donc récemment prévu d'écrire un sujet sur la base de données MySQL l'optimisation des performances Cette série d'articles espère être utile aux administrateurs de bases de données MySQL juniors et intermédiaires et à d'autres amis intéressés par l'optimisation des performances MySQL.

Je suis heureuse qu'un blogueur ait marqué mon article. Après avoir connu Mark, je reviens rarement et je continue d'y prêter attention. Mais cela montre de côté que lorsque le blogueur clique sur le blog, il sent que ce blog est précieux et peut compenser son manque de connaissances. La chose la plus importante à propos d’un blog est qu’il soit utile à vous-même. S’il est utile aux autres, c’est le meilleur résultat. Le but de mon insistance à écrire un blog est que lorsque j'oublie un point de connaissance, je puisse trouver une solution fiable le plus rapidement possible. Lorsque vous vous souvenez de vos connaissances résumées, vous les oubliez plus lentement. Après un long moment, cette partie des connaissances se transforme enfin en vos propres mots, vous n'avez alors plus peur d'oublier. Ce blog continuera à parler de MySQL. Ce blog parlera de l'optimisation de la mise en cache. Le processus pour en parler est aussi mon processus d'apprentissage.

Jetons d'abord un coup d'œil à notre version mysql. La version installée sur mon mac est la 5.7, et de nombreux contenus ont changé. Nous parlons ici principalement de la version 5.6.

[root@roverliang ~]# mysql --version
mysql Ver 14.14 Distrib 5.6.24, for Linux (x86_64) using EditLine wrapper

1. Classification du cache MySQL

L'optimisation MySQL fait référence à un grand système, lors de l'interview j'en ai parlé. du point de vue de l'optimisation des instructions SQL auparavant, ce type d'optimisation a également un effet, mais il est optimisé du point de vue logique. Mais lorsque tous les niveaux logiques ne peuvent pas être optimisés, que tous les index ont été ajoutés et que la structure de la table est raisonnablement conçue, pourquoi MySQL ne peut-il toujours pas le gérer en cas de concurrence élevée ? Bien entendu, la pression sur MySQL peut être allégée par d’autres aspects, que nous n’aborderons pas ici pour le moment. Pour MySQL, nous devons faire de notre mieux pour réduire les performances de la machine afin que toutes les ressources informatiques puissent nous servir sans les gaspiller. MySQL s'exécute sur un serveur, en particulier un serveur Linux. Ensuite, le disque dur, le processeur, la mémoire et le réseau du serveur affectent tous les performances de MySQL. MySQL consomme beaucoup de mémoire. La mémoire MySQL du serveur en ligne consomme environ 80 %. Si la mémoire est trop petite, il y a en réalité très peu de place pour d'autres optimisations.

De plus, la connexion est également un aspect important qui affecte les performances de MySQL. La connexion entre le client MySQL et le serveur MySQL est le résultat de poignées de main répétées entre le client MySQL et le serveur MySQL. Chaque « poignée de main » passe par une vérification d'identité, une vérification des autorisations, etc. La poignée de main nécessite certaines ressources réseau et ressources mémoire du serveur MySQL.

Je dois mentionner la concurrence de verrouillage. Pour les bases de données avec des exigences de performances de concurrence relativement élevées, s'il y a une concurrence de verrouillage féroce, les performances de la base de données seront un coup dur. Les conflits de verrouillage augmenteront considérablement la surcharge de changement de contexte de thread, qui est indépendante de la demande attendue.

2. Afficher l'état et afficher les variables

Dans les premiers blogs de la série MySQL, vous verrez souvent ces commandes, alors jetons un coup d'œil à ces deux-là. commandes séparément. Quelles informations cette commande affiche-t-elle à l'administrateur système MySQL :

show status

Lorsque le service MySQL est en cours d'exécution, le statut de MySQL instance de service Les informations sont dynamiques. Utilisez cette commande pour afficher l'état de la session variable informations de la connexion actuelle au serveur MySQL. Par défaut, les noms de variables ont la première lettre en majuscule.

show variables

show variables est utilisé pour afficher diverses variables système de l'instance de service MySQL (telles que : variables système globales, système de session variables, variables statiques ), ces variables contiennent les valeurs par défaut des paramètres de compilation MySQL, ou les valeurs des paramètres définies dans my.cnf. Les variables ou paramètres système sont un concept statique. Par défaut, les noms de variables système sont tous des lettres minuscules.

Utilisez la commande MySQL show status ou show session status pour afficher les informations sur les variables de session de la connexion actuelle au serveur MySQL. La valeur variable de l'état de la session est liée. au client MySQL actuel Valide, par exemple : variables d'état Opened_tables, Opened_table_definitions.

Mécanisme de mise en cache

缓存之所以有效，主要是因为程序运行时对内存或者外存的访问呈现局部性特征，局部性特征为空间局部性和时间局部性两方面。时间局部性是指刚刚访问过的数据近期可能再次被访问，空间局部性是指，某个位置被访问后，其相邻的位置的数据很可能被访问到。而MySQL的缓存机制就是把刚刚访问的数据（时间局部性）以及未来即将访问到的数据（空间局部性）保存到缓存中，甚至是高速缓存中。从而提高I/O效率。

按照缓存读写功能的不同，MySQL将缓存分为Buffer缓存和Cache缓存。

Buffer缓存。由于硬盘的写入速度过慢，或者频繁的I/O，对于硬盘来说是极大的效率浪费。那么可以等到缓存中储存一定量的数据之后，一次性的写入到硬盘中。Buffer 缓存主要用于写数据，提升I/O性能。

Cache 缓存。 Cache 缓存一般是一些访问频繁但是变更较少的数据，如果Cache缓存已经存储满，则启用LRU算法，进行数据淘汰。淘汰掉最远未使用的数据，从而开辟新的存储空间。不过对于特大型的网站，依靠这种策略很难缓解高频率的读请求，一般会把访问非常频繁的数据静态化，直接由nginx返还给用户。程序和数据库I/O设备交互的越少，则效率越高。

三、MySQL 超时

在使用MySQL的过程中，可能会出现各种超时（timeout）异常，典型的有连接超时、锁等待等。

查看超时时间的类型有哪些：

mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name        | Value  |
+-----------------------------+----------+
| connect_timeout       | 10    |
| delayed_insert_timeout   | 300   |
| innodb_flush_log_at_timeout | 1    |
| innodb_lock_wait_timeout  | 50    |
| innodb_rollback_on_timeout | OFF   |
| interactive_timeout     | 28800  |
| lock_wait_timeout      | 31536000 |
| net_read_timeout      | 30    |
| net_write_timeout      | 60    |
| rpl_stop_slave_timeout   | 31536000 |
| slave_net_timeout      | 3600   |
| wait_timeout        | 28800  |
+-----------------------------+----------+

1、连接超时（connect_timeout）

connect_timeout默认为10s,获取MySQL连接是客户机与服务器之间握手的结果，并且是多次握手的结果，每次握手，除了验证账户名和身份信息外，还需要验证主机、域名解析。如果客户机和服务器之间存在网络故障，可以通过connect_timeout参数来设置，防止它们之间重复握手。

interactive_timeout指的是交互式的终端，在命令行中输入的这种。超过了其设置的默认值就会断开。

wait_timeout指的是非交互式的终端，比如PHP实例化的Mysql连接，一直占用着，超过了这个参数设置的值，就会自动断开。

net_write_timeout MySQL服务器产生一个很大的数据集，MySQL客户机在该值设置的时间内不能接受完毕，则会断开连接。

net_read_timeout MySQL客户机读取了一个很大的数据，在设置值内不能读取完毕，则会自动断开连接。

InnoDB锁等待超时

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name      | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50  |
+--------------------------+-------+

InnoDB 的锁等待时间默认为50s,设置行级锁锁等待的值，当出现锁等待的时候，等待时长超过该值会导致锁等待的SQL回滚（不是整个事务回滚）。如果希望整个事务回滚，需要开启innodb_rollback_on_timeout参数。

mysql> show variables like '%rollback%';
+----------------------------+-------+
| Variable_name       | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | OFF  |
| innodb_rollback_segments  | 128  |
+----------------------------+-------+

innodb_rollback_on_timeout设置为true 后，遇到事务超时，会回滚整个事务的操作。

复制连接超时

当主从配置是，从服务器（slave）从主服务器（master）读取二进制日志失败后，从服务器会等待 slave_net_timeout 后，从新从master机拉去二进制日志。可以设置成10s.

mysql> show variables like 'slave_net_timeout';
+-------------------+-------+
| Variable_name   | Value |
+-------------------+-------+
| slave_net_timeout | 3600 |
+-------------------+-------+

这部分总结，应该是周日晚上就该整理好的，结果拖到了今天。后面的计划又要后延了，拖延症真严重。

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!