mysqlのパフォーマンスを最適化するにはどうすればよいですか?パフォーマンスの最適化を実現する実践的な方法（初心者向け）

MYSQL はおそらく最も人気のある WEB バックエンド データベースです。 WEB 開発言語は最近急速に発展していますが、PHP、Ruby、Python、Java にはそれぞれ独自の特徴があります。最近では NOSQL が取り上げられることが多くなりましたが、ほとんどのアーキテクトは依然としてデータ ストレージとして MYSQL を選択すると思います。では、mysql はどのようにしてパフォーマンスの最適化を達成するのでしょうか?次の記事では、MySQL のパフォーマンスを最適化するための実践的な方法をいくつか紹介しますので、皆様のお役に立てれば幸いです。 。

PHP 中国語 Web サイトの mysql チュートリアルを参照してください:「MySQL ビデオ チュートリアルを理解するには 6 日間」

パフォーマンスの最適化を達成するための Mysql の実践的な方法:

改善ディスクの読み取りおよび書き込み速度

RAID0 特に EC2 などの仮想ディスク (EBS) を使用する場合、ソフト RAID0 を使用することが非常に重要です。

NOSQL の方法で MYSQL を使用する

B-TREE は今でも最も効率的なインデックスの 1 つであり、すべての MYSQL は将来も使用可能です。

HandlerSocket を使用して MYSQL の SQL 解析層をスキップすると、MYSQL は真の NOSQL になります。

ディスク書き込み操作を減らす

1 十分な大きさの書き込みキャッシュ innodb_log_file_size を使用します

ただし、1G innodb_log_file_size を使用すると、サーバーがクラッシュした場合、回復するまでに 10 分かかることに注意してください。

innodb_log_file_size を 0.25 * innodb_buffer_pool_size に設定することをお勧めします

2 innodb_flush_log_at_trx_commit

このオプションはディスク書き込み操作と密接に関連しています:

innodb_flush_log_at_trx_commit = 1、各変更はディスクに書き込まれます
innod b_flush_log_at_trx_commit = 0/2 書き込みアプリケーションに高度なセキュリティ (金融システム) が含まれていない場合、インフラストラクチャが十分に安全である場合、またはトランザクションが小さい場合は、0 または 2 を使用してディスク操作を減らすことができます。

3 )、デフォルトで InnoDB を選択する必要があります。

独自のテスト中に、MyISAM が InnoDB よりも高速であることがわかる場合があります。これは、MyISAM はインデックスのみをキャッシュしますが、InnoDB はデータとインデックスをサポートしません。ただし、 innodb_flush_log_at_trx_commit = 2

を使用すると、ほぼ同等の読み取りパフォーマンス (100 倍の差) を得ることができます。

既存の MyISAM データベースを InnoDB に変換する方法:

innodb_flush_method=O_DIRECT

テーブルごとに 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] 

これにより、ibdata1 ファイルが大きすぎて制御不能になることがなくなります。特に mysqlcheck -o –all-databases を実行する場合。

メモリからのデータの読み取りとメモリへのデータの保存が保証されています

十分に大きな innodb_buffer_pool_size

データを innodb_buffer_pool_size に完全に保存すること、つまり、ストレージ量に応じて innodb_buffer_pool_size の容量を計画することをお勧めします。これにより、メモリからデータ全体を読み取ることができ、ディスク操作を最小限に抑えることができます。

innodb_buffer_pool_size が十分な大きさであり、データがハードディスクではなくメモリから読み取られることを確認するにはどうすればよいですか?

方法 1

innodb_file_per_table=1

Innodb_buffer_pool_pages_free が 0 であることがわかった場合は、バッファ プールが使い果たされているため、innodb_buffer_pool_size を増やす必要があることを意味します

InnoDB の他のいくつかのパラメータ:

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)

方法 2

または iostat を使用する - d -x -k 1 コマンドを使用して、ハードディスクの動作を表示します。

計画に必要なメモリがサーバー上にあるかどうか

echo 1 > /proc/sys/vm/drop_caches を実行して、オペレーティング システムのファイル キャッシュをクリアすると、実際のメモリ使用量を確認できます。

データのウォームアップ

デフォルトでは、データは innodb_buffer_pool にキャッシュされる前に 1 回だけ読み取られます。したがって、データベースは起動したばかりで、データをウォームアップし、ディスク上のすべてのデータをメモリにキャッシュする必要があります。データのウォームアップにより、読み取り速度が向上します。

InnoDB データベースの場合は、次の方法を使用してデータをウォームアップできます:

1. 次のスクリプトを MakeSelectQueriesToLoad.sql

innodb_additional_mem_pool_size = 1/200 of buffer_pool
innodb_max_dirty_pages_pct 80%

として保存します。2. 毎回、またはバックアップ前にデータベースを再起動します。データベース全体をウォームアップ中に実行します:

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
;

SWAP へのデータの保存を許可しない

専用の MYSQL サーバーの場合は、SWAP を無効にすることができます。共有サーバーの場合は、innodb_buffer_pool_size が大きいことを確認してください。十分。または、キャッシュに固定メモリ空間を使用し、memlock 命令を使用します。

データベースを定期的に最適化して再構築します

mysqlcheck -o –all-databases では、ibdata1 が増大し続けます。実際の最適化では、データ テーブル構造を再構築することしかできません。

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参考手册