Comment MySQL est-il optimisé ? Parlons de l’optimisation des performances en 5 dimensions
- 青灯夜游avant
- 2022-06-14 11:10:353666parcourir
Si l'intervieweur vous demande : à partir de quelles dimensions allez-vous optimiser les performances de MySQL ? Comment répondriez-vous ?
L'optimisation dite des performances vise généralement l'optimisation des requêtes MySQL. Puisque nous optimisons la requête, nous devons naturellement d'abord savoir par quels liens passe l'opération de requête, puis réfléchir aux liens qui peuvent être optimisés.
J'utilise une image pour montrer les étapes de base que l'opération de requête doit suivre.
Ce qui suit présente quelques stratégies d'optimisation MySQL sous 5 perspectives.
1. Optimisation de la configuration des connexions
Le traitement des connexions est la première étape de la relation entre le client MySQL et le serveur MySQL. Si la première étape n'est pas bien franchie, ne parlons pas de l'histoire suivante.
La connexion étant l'affaire des deux parties, nous l'optimisons naturellement aussi bien côté serveur que côté client.
1.1 Configuration du serveur
Ce que le serveur doit faire, c'est accepter autant de connexions client que possible. Peut-être avez-vous rencontré l'erreur 1040 : Trop de connexions ? C'est parce que l'esprit du serveur n'est pas assez large et que la mise en page est trop petite ! error 1040: Too many connections的错误？就是服务端的胸怀不够宽广导致的，格局太小！
我们可以从两个方面解决连接数不够的问题：
1、增加可用连接数，修改环境变量max_connections，默认情况下服务端的最大连接数为151个
mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+ 1 row in set (0.01 sec)
2、及时释放不活动的连接，系统默认的客户端超时时间是28800秒（8小时），我们可以把这个值调小一点
mysql> show variables like 'wait_timeout'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wait_timeout | 28800 | +---------------+-------+ 1 row in set (0.01 sec)
MySQL有非常多的配置参数，并且大部分参数都提供了默认值，默认值是MySQL作者经过精心设计的，完全可以满足大部分情况的需求，不建议在不清楚参数含义的情况下贸然修改。
1.2 客户端优化
客户端能做的就是尽量减少和服务端建立连接的次数，已经建立的连接能凑合用就凑合用，别每次执行个SQL语句都创建个新连接，服务端和客户端的资源都吃不消啊。
解决的方案就是使用连接池来复用连接。
常见的数据库连接池有DBCP、C3P0、阿里的Druid、Hikari，前两者用得很少了，后两者目前如日中天。
但是需要注意的是连接池并不是越大越好，比如Druid的默认最大连接池大小是8，Hikari默认最大连接池大小是10，盲目地加大连接池的大小，系统执行效率反而有可能降低。为什么？
对于每一个连接，服务端会创建一个单独的线程去处理，连接数越多，服务端创建的线程自然也就越多。而线程数超过CPU个数的情况下，CPU势必要通过分配时间片的方式进行线程的上下文切换，频繁的上下文切换会造成很大的性能开销。
Hikari官方给出了一个PostgreSQL数据库连接池大小的建议值公式，CPU核心数*2+1
nous Le problème des connexions insuffisantes peut être résolu sous deux aspects :
1. Augmentez le nombre de connexions disponibles et modifiez la variable d'environnementmax_connections Par défaut, le nombre maximum de connexions sur le serveur est C3P0, Druid d'Alibaba et Hikari. Utilisez les deux premiers. sont très peu nombreux, et ces deux derniers sont actuellement à leur apogée. Mais il convient de noter que plus le pool de connexions est grand, mieux c'est. Par exemple, la taille maximale par défaut du pool de connexions de Druid est de 8, et la taille maximale par défaut du pool de connexions de Hikari. vaut 10. Aveuglément Si vous augmentez excessivement la taille du pool de connexions, l'efficacité d'exécution du système peut diminuer. Pourquoi? 🎜🎜Pour chaque connexion, le serveur créera un thread distinct pour la traiter. Plus il y a de connexions, plus le serveur créera de threads. Lorsque le nombre de threads dépasse le nombre de processeurs, le processeur doit allouer des tranches de temps pour effectuer un changement de contexte des threads. Des changements de contexte fréquents entraîneront une surcharge de performances importante. 🎜🎜Hikari official donne un PostgreSQLLa formule de valeur recommandée pour la taille du pool de connexions à la base de données est Nombre de cœurs de processeur*2+1. En supposant que le nombre de cœurs de processeur du serveur soit de 4, définissez simplement le pool de connexions sur 9. Cette formule s’applique également dans une certaine mesure à d’autres bases de données, et vous pourrez vous en vanter lors des entretiens. 🎜🎜🎜2. Optimisation de l'architecture 🎜🎜🎜🎜2.1 Utilisation du cache 🎜🎜🎜 Il est inévitable que des requêtes lentes apparaissent dans le système. Ces requêtes contiennent soit une grande quantité de données, soit des requêtes complexes (de nombreuses tables associées ou calculs complexes). ), ce qui fait que la requête occupe la connexion pendant une longue période. 🎜🎜Si la validité de ce type de données n'est pas particulièrement forte (elle ne change pas à chaque instant, comme les rapports quotidiens), nous pouvons mettre ce type de données dans le système de cache, et pendant la période de validité du cache des données, directement à partir du système de cache Obtenez des données de la base de données, ce qui peut réduire la pression sur la base de données et améliorer l'efficacité des requêtes. 🎜🎜🎜🎜🎜🎜2.2 Séparation en lecture et en écriture (cluster, réplication maître-esclave) 🎜🎜🎜Au début du projet, la base de données s'exécute généralement sur un seul serveur, et toutes les demandes de lecture et d'écriture des utilisateurs affecteront directement ce serveur de base de données, après tout, la quantité de concurrence qu'un seul serveur peut supporter est limitée. 🎜Pour résoudre ce problème, nous pouvons utiliser plusieurs serveurs de base de données en même temps, définir l'un d'entre eux comme chef d'équipe, appelé nœud maître, et les nœuds restants comme membres de l'équipe, appelés . esclave. Les utilisateurs écrivent des données uniquement sur le nœud maître et les requêtes de lecture sont distribuées vers différents nœuds esclaves. Cette solution s'appelle master节点，其余节点作为组员，叫做slave。用户写数据只往master节点写，而读的请求分摊到各个slave节点上。这个方案叫做读写分离。给组长加上组员组成的小团体起个名字，叫集群。
注：很多开发者不满
master-slave这种具有侵犯性的词汇（因为他们认为会联想到种族歧视、黑人奴隶等），所以发起了一项更名运动。受此影响MySQL也会逐渐停用
master、slave等术语，转而用source和replica替代，大家碰到的时候明白即可。
使用集群必然面临一个问题，就是多个节点之间怎么保持数据的一致性。毕竟写请求只往master节点上发送了，只有master节点的数据是最新数据，怎么把对master节点的写操作也同步到各个slave节点上呢？
主从复制技术来了！我在之前的文章中粗浅地介绍了一下binlog日志，我直接搬过来了。
binlog是实现MySQL主从复制功能的核心组件。master节点会将所有的写操作记录到binlog中，slave节点会有专门的I/O线程读取master节点的binlog，将写操作同步到当前所在的slaveséparation lecture-écriture
cluster
Remarque : De nombreux développeurs n'étaient pas satisfaits du terme offensant
maître-esclave(parce qu'ils pensaient qu'il serait associé à la discrimination raciale, aux esclaves noirs, etc.), ils ont donc lancé un mouvement de changement de nom. Affecté par cela, MySQL cessera également progressivement d'utiliser des termes tels quemasteretslave, et utilisera à la placesourceetreplicaRemplacement, comprenez-le simplement lorsque tout le monde le rencontre.
L'utilisation d'un cluster sera inévitablement confrontée à un problème, celui de savoir comment maintenir la cohérence des données entre plusieurs nœuds. Après tout, la demande d'écriture n'est envoyée qu'au nœud master, et seules les données du nœud master sont les dernières données. Comment l'opération d'écriture peut-elle être effectuée sur le maître doit-il également être synchronisé ? Synchroniser avec chaque nœud <code>esclave ? La technologie
Réplication maître-esclaveest là ! J'ai brièvement présenté le journal binlog dans mon article précédent, je l'ai donc déplacé directement.
binlog est le composant principal qui implémente la fonction de réplication maître-esclave MySQL. Le nœud master enregistrera toutes les opérations d'écriture dans le binlog. Le nœud slave aura un thread d'E/S dédié pour lire le binlog du master
esclave actuel. Cette architecture de cluster a un très bon effet sur la réduction de la pression sur le serveur de base de données principal. Cependant, avec de plus en plus de données d'entreprise, si le volume de données d'une certaine table augmente fortement, les performances des requêtes d'une seule table. Cela diminuera. Une baisse significative, et ce problème ne peut pas être résolu même avec une séparation en lecture-écriture. Après tout, tous les nœuds stockent exactement les mêmes données. Les performances des requêtes d'une seule table sont naturellement médiocres. .
.
2.3 Sous-base de données et sous-table
Tout le monde sait que le sharding est divisé en sharding vertical, sharding vertical, sharding horizontal et sharding horizontal, mais chaque fois que je ne me souviens plus de ces concepts, je les expliquerai en détail pour vous aider à comprendre.
2.3.1 Sous-base de données verticale
.
2.3.2 Division verticale d'un tableau
2.3.4 水平分库
水平分库就是对单个数据库水平切一刀，往往伴随着水平分表。
2.3.5 总结
水平分，主要是为了解决存储的瓶颈；垂直分，主要是为了减轻并发压力。
2.4 消息队列削峰
通常情况下，用户的请求会直接访问数据库，如果同一时刻在线用户数量非常庞大，极有可能压垮数据库（参考明星出轨或公布恋情时微博的状态）。
这种情况下可以通过使用消息队列降低数据库的压力，不管同时有多少个用户请求，先存入消息队列，然后系统有条不紊地从消息队列中消费请求。
3. 优化器——SQL分析与优化
处理完连接、优化完缓存等架构的事情，SQL查询语句来到了解析器和优化器的地盘了。在这一步如果出了任何问题，那就只能是SQL语句的问题了。
只要你的语法不出问题，解析器就不会有问题。此外，为了防止你写的SQL运行效率低，优化器会自动做一些优化，但如果实在是太烂，优化器也救不了你了，只能眼睁睁地看着你的SQL查询沦为慢查询。
3.1 慢查询
慢查询就是执行地很慢的查询（这句话说得跟废话似的。。。），只有知道MySQL中有哪些慢查询我们才能针对性地进行优化。
因为开启慢查询日志是有性能代价的，因此MySQL默认是关闭慢查询日志功能，使用以下命令查看当前慢查询状态
mysql> show variables like 'slow_query%'; +---------------------+--------------------------------------+ | Variable_name | Value | +---------------------+--------------------------------------+ | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/9e74f9251f6c-slow.log | +---------------------+--------------------------------------+ 2 rows in set (0.00 sec)
slow_query_log表示当前慢查询日志是否开启，slow_query_log_file表示慢查询日志的保存位置。
除了上面两个变量，我们还需要确定“慢”的指标是什么，即执行超过多长时间才算是慢查询，默认是10S，如果改成0的话就是记录所有的SQL。
mysql> show variables like '%long_query%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.00 sec)
3.1.1 打开慢日志
有两种打开慢日志的方式
1、修改配置文件my.cnf
此种修改方式系统重启后依然有效
# 是否开启慢查询日志 slow_query_log=ON # long_query_time=2 slow_query_log_file=/var/lib/mysql/slow.log
2、动态修改参数（重启后失效）
mysql> set @@global.slow_query_log=1; Query OK, 0 rows affected (0.06 sec) mysql> set @@global.long_query_time=2; Query OK, 0 rows affected (0.00 sec)
3.1.2 慢日志分析
MySQL不仅为我们保存了慢日志文件，还为我们提供了慢日志查询的工具mysqldumpslow，为了演示这个工具，我们先构造一条慢查询：
mysql> SELECT sleep(5);
然后我们查询用时最多的1条慢查询：
[root@iZ2zejfuakcnnq2pgqyzowZ ~]# mysqldumpslow -s t -t 1 -g 'select' /var/lib/mysql/9e74f9251f6c-slow.log Reading mysql slow query log from /var/lib/mysql/9e74f9251f6c-slow.log Count: 1 Time=10.00s (10s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost SELECT sleep(N)
其中，
- Count：表示这个SQL执行的次数
- Time：表示执行的时间，括号中的是累积时间
- Locks：表示锁定的时间，括号中的是累积时间
- Rows：表示返回的记录数，括号中的是累积数
更多关于mysqldumpslow的使用方式，可以查阅官方文档，或者执行mysqldumpslow --help寻求帮助。
3.2 查看运行中的线程
我们可以运行show full processlist查看MySQL中运行的所有线程，查看其状态和运行时间，找到不顺眼的，直接kill。
其中，
- Id：线程的唯一标志，可以使用Id杀死指定线程
- User：启动这个线程的用户，普通账户只能查看自己的线程
- Host：哪个ip和端口发起的连接
- db：线程操作的数据库
- Command：线程的命令
- Time：操作持续时间，单位秒
- State：线程的状态
- Info：SQL语句的前100个字符
3.3 查看服务器运行状态
使用SHOW STATUS查看MySQL服务器的运行状态，有session和global两种作用域，一般使用like+通配符进行过滤。
-- 查看select的次数 mysql> SHOW GLOBAL STATUS LIKE 'com_select'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | Com_select | 168241 | +---------------+--------+ 1 row in set (0.05 sec)
3.4 查看存储引擎运行信息
SHOW ENGINE用来展示存储引擎的当前运行信息，包括事务持有的表锁、行锁信息；事务的锁等待情况；线程信号量等待；文件IO请求；Buffer pool统计信息等等数据。
例如：
SHOW ENGINE INNODB STATUS;
上面这条语句可以展示innodb存储引擎的当前运行的各种信息，大家可以据此找到MySQL当前的问题，限于篇幅不在此意义说明其中信息的含义，大家只要知道MySQL提供了这样一个监控工具就行了，等到需要的时候再来用就好。
3.5 EXPLAIN执行计划
通过慢查询日志我们可以知道哪些SQL语句执行慢了，可是为什么慢？慢在哪里呢？
MySQL提供了一个执行计划的查询命令EXPLAIN，通过此命令我们可以查看SQL执行的计划，所谓执行计划就是：优化器会不会优化我们自己书写的SQL语句（比如外连接改内连接查询，子查询优化为连接查询...）、优化器针对此条SQL的执行对哪些索引进行了成本估算，并最终决定采用哪个索引（或者最终选择不用索引，而是全表扫描）、优化器对单表执行的策略是什么，等等等等。
EXPLAIN在MySQL5.6.3之后也可以针对UPDATE、DELETE和INSERT语句进行分析，但是通常情况下我们还是用在SELECT查询上。
这篇文章主要是从宏观上多个角度介绍MySQL的优化策略，因此这里不详细说明EXPLAIN的细节，之后单独成篇。
3.6 SQL与索引优化
3.6.1 SQL优化
SQL优化指的是SQL本身语法没有问题，但是有实现相同目的的更好的写法。比如：
- 使用小表驱动大表；用join改写子查询；or改成union
- 连接查询中，尽量减少驱动表的扇出（记录数），访问被驱动表的成本要尽量低，尽量在被驱动表的连接列上建立索引，降低访问成本；被驱动表的连接列最好是该表的主键或者是唯一二级索引列，这样被驱动表的成本会降到更低
- 大偏移量的limit，先过滤再排序
针对最后一条举个简单的例子，下面两条语句能实现同样的目的，但是第二条的执行效率比第一条执行效率要高得多（存储引擎使用的是InnoDB），大家感受一下：
-- 1. 大偏移量的查询 mysql> SELECT * FROM user_innodb LIMIT 9000000,10; Empty set (8.18 sec) -- 2.先过滤ID（因为ID使用的是索引），再limit mysql> SELECT * FROM user_innodb WHERE id > 9000000 LIMIT 10; Empty set (0.02 sec)
3.6.2 索引优化
为慢查询创建适当的索引是个非常常见并且非常有效的方法，但是索引是否会被高效使用又是另一门学问了。
推荐阅读：《如何用好MySQL索引？你必须了解这些事！》，感兴趣的读者可以看一下。
https://www.php.cn/mysql-tutorials-493147.html
4. 存储引擎与表结构
4.1 选择存储引擎
一般情况下，我们会选择MySQL默认的存储引擎存储引擎InnoDB，但是当对数据库性能要求精益求精的时候，存储引擎的选择也成为一个关键的影响因素。
建议根据不同的业务选择不同的存储引擎，例如：
- 查询操作、插入操作多的业务表，推荐使用
MyISAM； - 临时表使用
Memory； - 并发数量大、更新多的业务选择使用
InnoDB； - 不知道选啥直接默认。
4.2 优化字段
字段优化的最终原则是：使用可以正确存储数据的最小的数据类型。
4.2.1 整数类型
MySQL提供了6种整数类型，分别是
- tinyint
- smallint
- mediumint
- int
- integer
- bigint
不同的存储类型的最大存储范围不同，占用的存储的空间自然也不同。
例如，是否被删除的标识，建议选用tinyint，而不是bigint。
4.2.2 字符类型
你是不是直接把所有字符串的字段都设置为varchar格式了？甚至怕不够，还会直接设置成varchar(1024)的长度？
如果不确定字段的长度，肯定是要选择varchar，但是varchar需要额外的空间来记录该字段目前占用的长度；因此如果字段的长度是固定的，尽量选用char，这会给你节约不少的内存空间。
4.2.3 Non nul
Les champs non nuls doivent être définis sur NON NULL autant que possible et fournir une valeur par défaut, ou utiliser des valeurs spéciales au lieu de NULL. NOT NULL，并提供默认值，或者使用特殊值代替NULL。
因为NULL类型的存储和优化都会存在性能不佳的问题，具体原因在这里就不展开了。
4.2.4 不要用外键、触发器和视图功能
这也是「阿里巴巴开发手册」中提到的原则。原因有三个：
降低了可读性，检查代码的同时还得查看数据库的代码；
把计算的工作交给程序，数据库只做好存储的工作，并把这件事情做好；
数据的完整性校验的工作应该由开发者完成，而不是依赖于外键，一旦用了外键，你会发现测试的时候随便删点垃圾数据都变得异常艰难。
4.2.5 图片、音频、视频存储
不要直接存储大文件，而是要存储大文件的访问地址。
4.2.6 大字段拆分和数据冗余
大字段拆分其实就是前面说过的垂直分表，把不常用的字段或者数据量较大的字段拆分出去，避免列数过多和数据量过大，尤其是习惯编写SELECT *
NULL auront de mauvais problèmes de performances, les raisons spécifiques ne seront pas abordées ici.
4.2.4 Ne pas utiliser de clés étrangères, de déclencheurs et de fonctions d'affichageC'est également un principe mentionné dans le "Manuel de développement Alibaba". Il y a trois raisons :
réduit la lisibilité, et vous devez vérifier le code de la base de données en même temps
Laissez le travail de calcul au programme, et la base de données ne fait que le travail de stockage, et fait Faites-le bien ;
Le travail de vérification de l'intégrité des données doit être effectué par les développeurs au lieu de s'appuyer sur des clés étrangères. Une fois les clés étrangères utilisées, vous constaterez qu'il devient extrêmement difficile de supprimer les données indésirables pendant les tests. . 4.2.5 Stockage d'images, audio et vidéo
- Ne stockez pas directement les fichiers volumineux, mais stockez l'adresse d'accès des fichiers volumineux.
4.2.6 Répartition des grands champs et redondance des données
Répartition des grands champs
En fait, c'est le partitionnement vertical des tables mentionné précédemment, qui sépare les champs rarement utilisés ou Divisez les champs avec un grand volume de données pour éviter trop de colonnes et un volume de données trop important. Surtout si vous avez l'habitude d'écrireSELECT * , les problèmes causés par un grand nombre de colonnes et un grand volume de données seront éliminés. Sérieusement amplifié ! En principe, elle n'est pas conforme au paradigme de conception de base de données, mais elle est très propice à une récupération rapide. Par exemple, lorsque l'ID client est stocké dans la table des contrats, le nom du client peut être stocké de manière redondante, de sorte qu'il n'est pas nécessaire d'obtenir le nom d'utilisateur basé sur l'ID client lors de l'interrogation. Par conséquent, il s’agit également d’une meilleure technique d’optimisation pour assurer un certain degré de redondance pour la logique métier. 🎜🎜🎜5. Optimisation commerciale🎜🎜🎜À proprement parler, l'optimisation commerciale n'est plus un moyen de réglage de MySQL, mais l'optimisation commerciale peut réduire très efficacement la pression d'accès aux bases de données. Un exemple typique à cet égard est Taobao. pour vous donner quelques idées : 🎜🎜🎜🎜 Autrefois, les achats commençaient la nuit du Double 11. Ces dernières années, la période de prévente du Double 11 est devenue de plus en plus longue, démarrant plus d'un demi-mois à l'avance. , et divers modèles d'enveloppes rouges de dépôt font leur apparition. Cette méthode est appelée 🎜détournement avant-vente🎜. Cela peut détourner les demandes de service des clients, et vous n'avez pas besoin d'attendre tôt le matin de Double Eleven pour passer des commandes collectivement 🎜🎜🎜🎜Au petit matin de Double Eleven, vous souhaiterez peut-être vérifier les commandes en dehors de ce jour-là ; , mais la requête échoue ; même dans Alipay, les rations du poulet ont été retardées. Il s'agit d'une 🎜stratégie de rétrogradation🎜, qui rassemble des ressources informatiques pour des services sans importance afin d'assurer le cœur de métier actuel 🎜🎜🎜🎜Alipay recommande fortement de l'utiliser pendant Double ; Onze paiements Huabei, plutôt que paiement par carte bancaire, bien qu'une partie de l'objectif soit d'améliorer la rigidité du logiciel, d'autre part, l'utilisation du serveur interne d'Alibaba réellement utilisé par Yu'ebao a une vitesse d'accès rapide, tandis que l'utilisation de cartes bancaires nécessite. appeler l'interface bancaire. En comparaison, l'opération est beaucoup plus lente. 🎜🎜🎜🎜🎜C'est la fin du résumé de l'optimisation MySQL. Il y a de nombreux détails non mentionnés, ce qui me fait penser que cet article n'est pas parfait. Cependant, il y a trop de points de connaissances pour les aborder en détail. Il est impossible de tous les écrire en même temps. 🎜🎜【Recommandation associée : 🎜tutoriel vidéo mysql🎜】🎜Articles Liés
Voir plus- Discussion approfondie sur les solutions et solutions pour l'accès « à haute concurrence et à trafic important »
- Nouveaux développements dans la plateforme web que les développeurs ne peuvent pas manquer en 2022
- laravel VS thinkphp, comment décider ?
- 30 plug-ins IDEA pratiques qui valent la peine d'être collectés [recommandé]
- Qu'est-ce qu'un framework PHP ? La différence entre le framework PHP et le CMS