MySQL中級最適化チュートリアル(8)——MySQL構成最適化

前言:
MySQLのデータベース・テーブル構造、使用するSQL文を最適化したら、これで終わりますか?経験のあるプログラマーは、プロファイルが自分のデータベースに対して差別的な最適化を行う必要があることを教えてくれます.異なるプロジェクト規模、異なるビジネスロジック、異なるシステムハードウェア構成は、私たちが自分のデータベースに対して異なる構成を行うことを要求しています.
 
まず、私たちのmysqlプロファイルはどこですか?
MySQLは、起動時に設定パラメータを指定する方法と、プロファイルを使用する方法の2つで構成できます.Linuxでは、ほとんどの場合、プロファイルは/etc/myにあります.cnfまたは/etc/mysql/my.cnf、windowsシステムの構成はC:/windows/myに位置することができる.iniファイル.
Linuxシステムでは、MySQLがプロファイルを検索する順序は、次の方法で取得できます.

$/usr/sbin/mysqld --verbose --help | grep -A 1 'Default options'

 
MySQL共通プロファイルの説明:
 
まず最初のパラメータはinnodb_ですbuffer_pool_size
Innodbのバッファ・プールを構成するために非常に重要なパラメータです.データベースにInnodbテーブルがあり、データベース・サーバとしてのみ使用される場合は、サーバの総メモリの75%を推奨します.
次の文を使用して、ネイティブのすべてのタイプのデータベースに必要な領域(データベースインデックス+データの合計サイズ)を判断できます.

SELECT ENGINE,
ROUND(SUM(data_length + index_length) /1024/1024, 1) AS "Total MB"
FROM INFORMATION_SCHEMA.TABLES WHERE table_schema not in
('information_schema', 'performance_schema')
GROUP BY ENGINE;

私の実行結果は次のとおりです.

+--------+----------+
| ENGINE | Total MB |
+--------+----------+
| InnoDB |     22.2 |
| CSV    |      0.0 |
| NULL   |      0.0 |
| MyISAM |      0.0 |
+--------+----------+

私たちの要求は:Innodb_buffer_pool_size>=Total MBデータベースが大きく、データも多く、TotalMBの値が大きすぎる場合でも構いません.Innodb_buffer_pool_sizeの値はできるだけ大きくすればいいです.
 
2番目のパラメータ:Innodb_buffer_pool_instances
これはMySQL 5です.5で追加を開始するプロパティで、バッファの数を制御できます.デフォルトではバッファは1つしかありません.このプロパティと前のプロパティinnodb_buffer_pool_sizeには一定の関係があり、バッファの個数を増やすことで、ブロック要求の数を減らし、データベース接続の同時性を高めることができます.一般的には、4または8に設定します.各バッファのサイズはInnodb_です.buffer_pool_sizeをInnodb_で割るbuffer_pool_instances.
 
3番目のパラメータ:innodb_log_buffer_size
これはデータベース・ログ・キャッシュのサイズです.パフォーマンスの観点から、データベースはデフォルトでログ・データをバッファに格納し、1秒おきにディスクに書き込みます.したがって、その値はあまり大きくなくても、次の秒以内にデータベースで生成されたログを置くことができます.
 
4番目のパラメータ:Innodb_flush_log_at_trx_commitこれも極めて重要なパラメータであり、MySQLディスクの書き込みポリシーを制御しています.つまり、キャッシュ内のデータベースのデータをディスクに同期する方法です.オプションのパラメータ0、1、2が3つあります.この3つのパラメータの異なるメカニズムを説明する前に、データベースの操作がディスクに同期する方法を理解しておきます.プロセスは次のとおりです:データベースに操作を挿入しました--』操作はlog_に格納されます.buffer--》log_bufferはデータをlog fileに同期します--』log fileはデータを物理ディスクに同期します.
3つのパラメータとメカニズムは次のとおりです.
0:log bufferは毎秒1回log fileに書き込まれ、log fileのflush(ディスクにブラシ)操作が同時に行われます.このモードでは、トランザクションのコミット時にディスクへの書き込みはアクティブにトリガーされません.1:MySQLは、トランザクションがコミットされるたびにlog bufferのデータをlog fileに書き込み、flush(ディスクにブラシをかける)がシステムのデフォルトです.2:MySQLはトランザクションのコミット時にlog bufferのデータをlog fileに書き込みますが、flush(ディスクにブラシをかける)操作は同時に行われません.このモードでは、MySQLは1秒に1回flush(ディスクにブラシ)操作を実行します.
データのセキュリティ要件が高い場合は、デフォルト値1を使用します.
 
5番目のパラメータ:
実は2つ:Innodb_read_io_threadsとInnodb_write_io_threads
以上の2つのパラメータはInnodb読み書きのIOプロセス数を決定し、デフォルトは4、MySQL 5である.5以降、手動で値を指定し、cpuのスレッド数、コア数に基づいて値を調整し、同時性を向上させることができます.
 
6番目のパラメータ:Innodb_file_per_table
これも重要なパラメータで、Innodbが各テーブルに独立したテーブルスペースを使用するかどうかを制御します.MySQL 5.6以前はデフォルトでOFF、つまりすべてのテーブルが共有テーブル空間ibdata 1に確立されていた.MySQL5.6以降はデフォルトでONに設定されます.つまり、各テーブルに独立したテーブルスペースがあります.両者にはそれぞれメリットとデメリットがあり、全体的にONに設定され、メリットはデメリットより大きい.
興味のあるブーツは筆者が収集した説明を見ることができる:Innodbストレージエンジンはibdata*の共有表空間にすべてのデータを保存することができ、各表を独立したものに保存することができる.ibdファイルの独立した表領域.共有表領域:あるデータベースのすべての表データ、インデックスファイルはすべて1つのファイルに配置され、デフォルトではこの共有表領域のファイルパスはdataディレクトリの下にあります.デフォルトのファイル名はibdata 1を10 Mに初期化します.≪独立表領域|Independent Tablespace|emdw≫:各表は独立したファイル方式で格納、各表は1つずつ生成されます.frmテーブル記述ファイル、もう一つ.ibdファイル.このファイルには、個別のテーブルのデータコンテンツとインデックスコンテンツが含まれています.デフォルトでは、その格納場所もテーブルの場所にあります.共有テーブルスペースの最大の問題は、このibdataファイルのサイズが増減しないだけで、不思議に大きくなることがあります.個別のテーブルスペースを使用すると、このデータテーブルがtruncateまたはdropされると、このデータの個別のテーブルスペースが回収され、メモリが節約されます.詳細については、このブログを参照してください.https://blog.csdn.net/jesseyoung/article/details/42236615
なお、一部はMySQL 5を用いる.6以前のバージョンの子供靴は、現在ONに設定されている場合は、以前のテーブルが共有空間に配置されているため、新しく作成されたテーブルが個別のテーブル空間を持つことに注意してください.
 
7番目のパラメータ:innodb_stats_on_metadata
MySQLがinnodbテーブルの統計をいつリフレッシュするかを決定します.このパラメータはMySQL 5にあります.6の後にデフォルトからonに変更されてoffに変更されましたが、このパラメータは何をしていますか?私たちのデータベース・オプティマイザは、どのインデックスを使用するかを分析するときに、私たちの統計を分析して参考にします.onに設定すると、このテーブルに対して構造クエリー文を実行するとき(show table status、show create tableなど)、データベース・エンジンはついでに統計をクリーンアップしてリフレッシュします.
我々の統計情報のリフレッシュ頻度が速すぎると,我々の検索操作に不利であることが明らかになった.したがって、off状態であるMySQL 5を維持することが望ましい.6以降のデフォルト設定.