Comment optimiser les performances de MySQL ? Une méthode pratique pour optimiser les performances (pour référence des débutants)

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

Choisissez le bon moteur de stockage : InnoDB

À 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).

Comment convertir une base de données MyISAM existante en InnoDB :

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] 4e96f32533a37eb2d7995ea8aec4a0c6 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.

S'il y a suffisamment de mémoire sur le serveur pour la planification

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 .

Préchauffement des données

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 d2c06994f867596579cebf752445c7f9 /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 f85c6fa352274fed00a31796e4233668 /dev/null 2>&1

Ne laissez pas les données être enregistrées. dans SWAP

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.

L'optimisation et la reconstruction régulières de la base de données

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最新手册教程

2. MySQL 5.1参考手册