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 を使用してディスク操作を減らすことができます。

既存の 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] < alter_table.sql

ログイン後にコピー

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

方法 1

innodb_file_per_table=1

ログイン後にコピー

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

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)

ログイン後にコピー

innodb_additional_mem_pool_size = 1/200 of buffer_pool
innodb_max_dirty_pages_pct 80%

ログイン後にコピー

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
;

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