MySQL ストレージエンジン InnoDB の設定と使用方法の説明

MyISAM と InnoDB は、MySQL で最も一般的なストレージエンジンです。前回の記事では、InnoDB と MyISAM の違いについて説明しました。MyISAM はトランザクションをサポートしていないため、堅牢なトランザクションストレージエンジンを使用する必要がある場合は、InnoDB が最適な選択肢になります。

Innodb は、マルチバージョン同時実行制御 (MVCC) を通じて高い同時実行性を実現し、4 つの SQL 標準分離レベルを実装します。デフォルトはREPEATABLEレベルです。同時に、 phantomリーディングを回避するために、 next-key lockingと呼ばれる戦略が使用されます。さらに、InnoDB ストレージエンジンは、 insert buffer 、 double write 、アダプティブadaptive hash indexインデックス、 read aheadなどの高性能かつ高可用性の機能も提供します。

InnoDB をデフォルトのエンジンとして設定する方法:

1. MySQL ストレージエンジンのステータスを確認します: mysql>show engines。 InnoDB | YES は、この MySQL データベースサーバーが InnoDB エンジンをサポートしていることを示します。
2. InnoDBをデフォルトのエンジンとして設定します。my.ini設定ファイルの[mysqld]の下にdefault-storage-engine=INNODBを追加します。
3. MySQLサーバーを再起動する
4. MySQL データベースにログインし、mysql>show engines を実行します。 InnoDB | DEFAULT が表示された場合、InnoDB がデフォルトのエンジンとして正常に設定されていることを意味します。

InnoDB 共通構成パラメータ:

#InnoDB はデータ ディクショナリと内部データ構造のバッファ プールを格納します。16 MB あれば十分な大きさです。
innodb_追加メモリプールサイズ = 16M
#InnoDBは、データ、インデックス、ロック、挿入バッファ、データ辞書などのキャッシュに使用されます。#専用のDBサーバーで、InnoDBエンジンが主なシナリオである場合は、通常、物理メモリの50%に設定できます。
#非専用 DB サーバーの場合は、まずメモリの 1/4 に設定してみて、問題があれば調整してください。#デフォルト値は 8M で、これは非常に悪いです。多くの人が InnoDB は MyISAM ほど良くないと考えるのもこれが理由です。innodb_buffer_pool_size = 4G
#InnoDB 共有テーブルスペースの初期化サイズは、デフォルトは 10MB ですが、これも非常に扱いにくいため、1GB に変更し、innodb_data_file_path = ibdata1:1G:autoextend で自動的に拡張します。
#このオプションがわからない場合は、1に設定することをお勧めします。これにより、データの信頼性がより保護され、パフォーマンスに一定の影響がありますが、制御可能です innodb_flush_log_at_trx_commit = 1
#InnoDB ログ バッファは通常 64MB に設定すれば十分です innodb_log_buffer_size = 64M
#InnoDB redo ログのサイズは通常 256MB で十分です innodb_log_file_size = 256M
#InnoDB redo ログ ファイル グループ。通常は 2 に設定すれば十分です。innodb_log_files_in_group = 2
#管理を容易にするために InnoDB の独立テーブルスペース モードを有効にする innodb_file_per_table = 1
# 管理者が表示や監視などを行うために InnoDB のステータス ファイルを有効にします。innodb_status_file = 1
#トランザクションの効率を向上させるために、トランザクション分離レベルを READ-COMMITTED に設定します。通常、トランザクションの一貫性要件は満たされます。transaction_isolation = READ-COMMITTED 
その他の構成オプションも注目に値します。
#同時接続の最大数を設定します。フロントエンドプログラムがPHPの場合は、適切に増やすことができますが、あまり増やしすぎないようにしてください。#フロントエンドプログラムが接続プールを使用する場合は、接続が多すぎないように適切に調整できます。max_connections = 60
#接続エラーが頻繁に発生してもフロントエンドホストがmysqlによって拒否されるのを防ぐために、接続エラーの最大数を適切に増やすことができます。max_connect_errors = 100000
#遅いクエリのしきい値を設定します。最小値を1秒に設定することをお勧めします long_query_time = 1
# 一時テーブルの最大値を設定します。これは各接続に割り当てられます。あまり大きく設定することはお勧めできません。max_heap_table_size と tmp_table_size は同じサイズに設定する必要があります。max_heap_table_size = 96M
tmp_table_size = 96M
#各接続はソート、接続、その他のバッファを割り当てます。通常は 2MB に設定すれば十分です。sort_buffer_size = 2M
結合バッファサイズ = 2M
読み取りバッファサイズ = 2M
読み取りバッファサイズ = 2M
# パフォーマンスに悪影響を与える可能性があるため、クエリキャッシュをオフにすることをお勧めします。query_cache_size = 0
#メインDBがInnoDBの場合、MyISAMのkey_buffer_sizeは小さく設定できます。8MBで十分です。#メインDBがMyISAMの場合、大きく設定できますが、4Gを超えてはいけません。
#ここでは、MyISAMエンジンを使用しないことを強くお勧めします。デフォルトでは、InnoDBエンジンを使用します。key_buffer_size = 8M
#接続タイムアウトしきい値を設定します。フロントエンド プログラムが短い接続を使用する場合は、これら 2 つの値を短くすることをお勧めします。#フロントエンド プログラムが長い接続を使用する場合は、これら 2 つのオプションを直接コメント アウトして、既定の構成 (8 時間) を使用できます。
インタラクティブタイムアウト = 120
待機タイムアウト = 120

InnoDB エンジンデータテーブル設計に関するいくつかの提案:

1. すべての InnoDB データテーブルは、ビジネスとは無関係な自動増分数値主キーを作成します。これは、パフォーマンスの確保に非常に役立ちます。
2. text/blob の使用は避けてください。どうしても使用する必要がある場合は、可能な限り独立したテーブルに分割してください。
3. タイムスタンプを保存するには TIMESTAMP 型を使用することをお勧めします。
4. IPV4 アドレスは INT UNSIGNED 型を使用して保存することをお勧めします。
5. 性別などのバイナリロジックの場合は、CHAR(1)ではなくTINYINTストレージを使用することをお勧めします。
6. 長いテキストコンテンツを保存する場合は、JSON/BSON 形式を使用することをお勧めします。

InnoDB ロックの種類

InnoDBストレージエンジンは、次の2つの標準的な行レベルロックを実装しています。

共有ロック（Sロック）により、トランザクションはデータ行を読み取ることができる
排他ロック（Xロック）。トランザクションがデータ行を削除または更新できるようにします。

共有ロックと排他ロックの互換性

知らせ：

（１）SロックとXロックはともに行ロックです。互換性とは、同じレコード（行）上のロックの互換性を指します。

（２）トランザクションT1はすでに行Rの共有ロックを取得している。別のトランザクションT2はすぐに行Rの共有ロックを取得できる。この状況をロック互換性と呼ぶ。トランザクション T3 が行 R の排他ロックを取得したい場合、トランザクション T1 と T2 が行 R の共有ロックを解放するまで待機する必要があります。この状況はロックの非互換性になります。

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。これについてもっと知りたい場合は、次のリンクをご覧ください。

以下もご興味があるかもしれません: