MYSQL est probablement la base de données backend WEB la plus populaire. Les langages de développement WEB se sont développés rapidement ces derniers temps. PHP, Ruby, Python et Java ont chacun leurs propres caractéristiques. Bien que NOSQL soit de plus en plus mentionné récemment, je pense que la plupart des architectes choisiront toujours MYSQL pour le stockage de données. Alors, comment MySQL parvient-il à optimiser les performances ? L'article suivant présentera quelques méthodes pratiques d'optimisation des performances de MySQL. J'espère qu'il sera utile à tout le monde. .
Référez-vous au tutoriel mysql sur le site Web php chinois : "Six jours pour vous guider à travers le didacticiel vidéo MySQL"
Méthodes pratiques MySQL pour l'optimisation des performances :
Améliorer la vitesse de lecture et d'écriture du disque
RAID0 surtout lorsque Utilisation d'EC2 Lors de l'utilisation de ce type de disque virtuel (EBS), il est très important d'utiliser le soft RAID0.
Utiliser MYSQL à la manière NOSQL
B-TREE est toujours l'un des index les plus efficaces et tout MYSQL est toujours à l'épreuve du temps.
Utilisez HandlerSocket pour ignorer la couche d'analyse SQL de MYSQL, et MYSQL devient véritablement NOSQL.
Réduire les opérations d'écriture sur disque
1 Utilisez un cache d'écriture suffisamment grand innodb_log_file_size
Mais vous devez faire attention si vous utilisez 1G innodb_log_file_size, si le machine serveur et prend 10 minutes pour récupérer.
Il est recommandé que innodb_log_file_size soit défini sur 0,25 * innodb_buffer_pool_size
2 innodb_flush_log_at_trx_commit
Cette option est étroitement liée aux opérations d'écriture sur disque
innodb_flush_log_at_trx_commit : = 1 , alors chaque modification est écrite en écriture sur le disque
innodb_flush_log_at_trx_commit = 0/2 Écriture sur le disque par seconde
Si votre application n'implique pas de haute sécurité (système financier), ou si l'infrastructure est suffisamment sécurisée, ou si les transactions sont petites, vous pouvez utiliser 0 ou 2 pour ralentir les opérations du disque.
3 Évitez la double mise en mémoire tampon d'écriture
innodb_flush_method=O_DIRECT
À moins que vos données La table est utilisé pour la recherche en lecture seule ou en texte intégral (je pense que personne n'utilisera MYSQL pour la recherche en texte intégral maintenant), vous devez choisir InnoDB par défaut.
Vous constaterez peut-être que MyISAM est plus rapide qu'InnoDB lors de vos propres tests. En effet : MyISAM met uniquement en cache les index, tandis qu'InnoDB met en cache les données et les index. Mais si vous utilisez innodb_flush_log_at_trx_commit = 2 vous pouvez obtenir des performances de lecture proches (une différence cent fois supérieure).
mysql -u [USER_NAME] -p -e "SHOW TABLES IN [DATABASE_NAME];" | tail -n +2 | xargs -I '{}' echo "ALTER TABLE {} ENGINE=InnoDB;" > alter_table.sql
perl -p -i -e 's/(search_[a-z_]+ ENGINE=)InnoDB//1MyISAM/g' alter_table.sql
mysql -u [USER_NAME] -p [DATABASE_NAME] < alter_table.sqlinnodb_file_per_table=1
Cela garantit que le fichier ibdata1 n'est pas Ce sera trop gros et hors de contrôle. Surtout lors de l'exécution de mysqlcheck -o –all-databases.
suffisamment grand est recommandé Les données sont entièrement stocké dans innodb_buffer_pool_size, c'est-à-dire que la capacité de innodb_buffer_pool_size est planifiée en fonction de la quantité de stockage. De cette façon, vous pouvez lire entièrement les données depuis la mémoire, minimisant ainsi les opérations sur le disque.
Méthode 1
mysql> SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_%'; +----------------------------------+--------+ | Variable_name | Value | +----------------------------------+--------+ | Innodb_buffer_pool_pages_data | 129037 | | Innodb_buffer_pool_pages_dirty | 362 | | Innodb_buffer_pool_pages_flushed | 9998 | | Innodb_buffer_pool_pages_free | 0 | !!!!!!!! | Innodb_buffer_pool_pages_misc | 2035 | | Innodb_buffer_pool_pages_total | 131072 | +----------------------------------+--------+ 6 rows in set (0.00 sec)
Si vous constatez qu'Innodb_buffer_pool_pages_free vaut 0, cela signifie que le pool de tampons a été utilisé et vous devez augmenter innodb_buffer_pool_size
Plusieurs autres paramètres d'InnoDB :
innodb_additional_mem_pool_size = 1/200 of buffer_pool innodb_max_dirty_pages_pct 80%
Méthode 2
Ou utilisez la commande iostat -d -x -k 1 pour vérifier le fonctionnement du disque dur.
Exécutez echo 1 > /proc/sys/vm/drop_caches pour vider le cache de fichiers du système d'exploitation et vous pouvez voir l'utilisation réelle de la mémoire .
Par défaut, une donnée sera mise en cache dans innodb_buffer_pool uniquement si elle est lue une fois. Par conséquent, la base de données vient de démarrer et doit réchauffer les données et mettre en cache toutes les données du disque dans la mémoire. L'échauffement des données peut augmenter la vitesse de lecture.
Pour la base de données InnoDB, vous pouvez utiliser la méthode suivante pour réchauffer les données :
1 Enregistrez le script suivant sous MakeSelectQueriesToLoad.sql
SELECT DISTINCT
CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,
' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
FROM
(
SELECT
engine,table_schema db,table_name tb,
index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
FROM
(
SELECT
B.engine,A.table_schema,A.table_name,
A.index_name,A.column_name,A.seq_in_index
FROM
information_schema.statistics A INNER JOIN
(
SELECT engine,table_schema,table_name
FROM information_schema.tables WHERE
engine='InnoDB'
) B USING (table_schema,table_name)
WHERE B.table_schema NOT IN ('information_schema','mysql')
ORDER BY table_schema,table_name,index_name,seq_in_index
) A
GROUP BY table_schema,table_name,index_name
) AA
ORDER BY db,tb
;2.
mysql -uroot -AN < /root/MakeSelectQueriesToLoad.sql > /root/SelectQueriesToLoad.sql
3. Chaque fois que vous redémarrez la base de données, ou lorsque vous devez vous réchauffer avant de sauvegarder l'intégralité de la base de données, exécutez :
mysql -uroot < /root/SelectQueriesToLoad.sql > /dev/null 2>&1
Si c'est le cas Pour un serveur MYSQL dédié, SWAP peut être désactivé S'il s'agit d'un serveur partagé, assurez-vous que innodb_buffer_pool_size est suffisamment grand. Ou utilisez un espace mémoire fixe pour la mise en cache et utilisez l'instruction memlock.
mysqlcheck -o –all-databases feront continuer à croître ibdata1 La seule véritable optimisation. est de reconstruire la structure de la table de données :
CREATE TABLE mydb.mytablenew LIKE mydb.mytable; INSERT INTO mydb.mytablenew SELECT * FROM mydb.mytable; ALTER TABLE mydb.mytable RENAME mydb.mytablezap; ALTER TABLE mydb.mytablenew RENAME mydb.mytable; DROP TABLE mydb.mytablezap;
SHOW CREATE TABLE db1.tb1/G
添加必要的索引
索引是提高查询速度的唯一方法，比如搜索引擎用的倒排索引是一样的原理。
索引的添加需要根据查询来确定，比如通过慢查询日志或者查询日志,或者通过 EXPLAIN 命令分析查询。
ADD UNIQUE INDEX ADD INDEX
添加索引
ALTER TABLE users ADD UNIQUE INDEX username_ndx (username); ALTER TABLE users ADD UNIQUE INDEX username_password_ndx (username,password);
每次重启服务器进行数据预热
echo “select username,password from users;” > /var/lib/mysql/upcache.sql
添加启动脚本到 my.cnf
[mysqld] init-file=/var/lib/mysql/upcache.sql
比如，Rails 这样的框架，会自动添加索引，Drupal 这样的框架会自动拆分表结构。会在你开发的初期指明正确的方向。所以，经验不太丰富的人一开始就追求从 0 开始构建，实际是不好的做法。
记录所有查询，这在用 ORM 系统或者生成查询语句的系统很有用。
log=/var/log/mysql.log
注意不要在生产环境用，否则会占满你的磁盘空间。
记录执行时间超过 1 秒的查询：
long_query_time=1 log-slow-queries=/var/log/mysql/log-slow-queries.log
相关推荐：
1. MySQL最新手册教程
mysql modifier le nom de la table de données
MySQL crée une procédure stockée
La différence entre MongoDB et MySQL
Comment vérifier si le mot de passe MySQL est oublié
mysql créer une base de données
niveau d'isolement des transactions par défaut de MySQL
La différence entre sqlserver et mysql
mysqlmot de passe oublié
![[Web front-end] Démarrage rapide de Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
