一般的な MySQL ストレージエンジンとパラメータ設定およびチューニングの紹介

MyISAM、MySQLでよく使われるストレージエンジン

特性：
1. 同時実行性とロックレベル
2. テーブルの損傷を修復する
テーブル名をチェックする
テーブルを修復するテーブル名
3. MyISAMテーブルでサポートされているインデックスの種類①、フルテキストインデックス②、プレフィックスインデックス
4. MyISAMテーブルはデータ圧縮をサポートします
マイサムパック
制限:
MySQL 5.0未満のバージョンでは、デフォルトのテーブルサイズは4Gです。
ストレージが要件を満たしている場合は、MAX_RowsとAVG_ROW_LENGTHを変更します。
MySQL 5.0以降のバージョンのデフォルトのサポートは256 TBです。
適用可能なシナリオ:
1. 非トランザクションアプリケーション
2. 読み取り専用アプリケーション
3. 宇宙への応用

MySQLでよく使われるストレージエンジン、Innodb

Innodb ストレージエンジンの特徴
1. Innodbはトランザクションストレージエンジンです
2. トランザクションのACID機能を完全にサポート
3. やり直しログと取り消しログ
4. Innodbは行レベルのロックをサポートする
Innodbはデータストレージにテーブルスペースを使用し、テーブルごとに個別のテーブルスペースを作成します。
innodb_file_per_table
ON: 独立表領域: tablename.ibd
OFF: システムテーブルスペース: ibdataX (X は 1 から始まる数字)

システム表領域と独立表領域の選択および比較方法:
システムテーブルスペースは縮小できません。独立テーブルスペースは、optimize table コマンドを使用して縮小できます。システムファイルシステムテーブルスペースは、IO ボトルネックの原因になります。独立テーブルスペースは、複数のファイルに同時にデータを更新できます。

テーブル転送の手順:
1. mysqldumpを使用してすべてのデータベーステーブルデータをダンプする
2. MySQLサービスを停止し、パラメータを変更し、Innodb関連ファイルを削除します。
3. MySQLサービスを再起動し、Innodbシステムテーブルスペースを再構築します。
4. データの再インポート

CSV、MySQLの共通ストレージエンジン

ファイルシステムのストレージ特性
1. データはテキスト形式で保存されます
2. .csvファイルはテーブルの内容を保存します
3. .csmファイルには、テーブルの状態やデータ量などのテーブルメタデータが格納されます。
4. .frmファイルはテーブル構造情報を保存します
5. CSV形式で保存する
6. すべての列はNullableであってはならない
7. インデックスサポートなし
適用可能なシナリオ:
データ交換のための中間テーブルとして適しています (スプレッドシート -> csv ファイル -> MySQL データベースディレクトリ)

MySQLでよく使われるストレージエンジンであるArchive

ファイルシステムのストレージ特性
1. zlibでテーブルデータを圧縮してディスクI/Oを削減する
2. データはARZという拡張子のファイルに保存されます。
アーカイブストレージエンジンの機能
1. 挿入と選択の操作のみがサポートされています
2. 自動増分ID列にのみインデックスを許可する

適用可能なシナリオ:
ログ記録およびデータ収集アプリケーション

MySQL 共通ストレージエンジンメモリ

ファイルシステムのストレージ特性
1. HEAPストレージエンジンとも呼ばれ、データはメモリに保存されます
特徴：
1. HASHインデックスとBtreeインデックスをサポート
2. すべてのフィールドは固定長varchar(10)=char(10)です。
3. BLOGやTEXTなどの大きなフィールドはサポートされていません
4. メモリストレージエンジンはテーブルレベルのロックを使用する
5. 最大サイズはmax_heap_table_sizeパラメータによって決定されます。
適用可能なシナリオ:
1. 郵便番号と地域の対応表などの表を検索したりマッピングしたりするために使用します。
2. データディストラクションで生成された中間テーブルを保存するために使用される
3. 定期集計データをキャッシュするための結果テーブル

MySQL 共通ストレージエンジンフェデレーション

特徴：
1. リモートMySQLサーバー上のテーブルにアクセスする方法を提供する
2. データはローカルに保存されず、すべてのデータがリモートサーバー上に配置される
3. テーブル構造とリモートサーバーの接続情報はローカルに保存する必要がある

デフォルトの静的状態を使用する方法、起動時にフェデレーションパラメータを増やす必要性を有効にする
mysql://ユーザー名[:パスワード]@ホスト名[:ポート]/データベース名/テーブル名

適用可能なシナリオ:
定期的な統計分析と手動クエリ

適切なストレージエンジンを選択する方法

基準条件
1. トランザクションをサポートするかどうか
2. 定期的なバックアップ
3. クラッシュリカバリ
4. ストレージエンジンのユニークな機能

Mysql サーバーパラメータの紹介

MySQLは設定情報パスを取得します

1. コマンドラインパラメータ

mysqld_safe --datadir=/data/sql_data

2. 設定ファイル

構成ファイルを表示するコマンド:
[root@localhost ~]# mysqld --help --verbose | egrep -A 1 'デフォルトオプション'
設定ファイルへの有効なパス
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

MySQL 構成パラメータの範囲

1. グローバルパラメータ
グローバルパラメータ名 = パラメータ値を設定します。
@@global.parameter name:=parameter value を設定します。
2. セッションパラメータ
[セッション] パラメータ名 = パラメータ値を設定します。
@@session.パラメータ名:=パラメータ値を設定します。

メモリ構成関連のパラメータ
1. 利用可能なメモリの上限を決定する
2. 各MySQL接続で使用されるメモリを決定する
ソートバッファサイズ
結合バッファサイズ
読み取りバッファサイズ
読み取りバッファサイズ
3. オペレーティングシステム用に確保する必要があるメモリの量を決定する
4. キャッシュプールにメモリを割り当てる方法
Innodb_バッファプールサイズ
注: キャッシュプールのサイズを設定する際の考慮基準は、合計メモリ - (各プログラムに必要なメモリ * 接続数) - システム予約メモリです。
キーバッファサイズ
information_schema.tables から、engines='myisam' の場合に sum(index_length) を選択します。

I/O関連の設定パラメータ
Innodo I/O関連の設定
Innodb_log_file_size 単一のトランザクションログのサイズ
Innodb_log_files_in_group はファイルの日数を制御します。トランザクションログの合計サイズ = Innodb_log_files_in_group * Innodb_log_file_size
Innodb_log_buffer_size = (32M または 128M)
Innodb_flush_log_at_trx_commint
0: 1秒ごとにログをキャッシュに書き込み、ログをディスクにフラッシュします。
1 [デフォルト]: トランザクションのコミットごとにログをキャッシュに書き込み、ディスクにフラッシュする
2[提案]: トランザクションがコミットされるたびに、ログデータがキャッシュに書き込まれ、1秒ごとにログがディスクにフラッシュされます。
Innodb_flush_method=O_DIRECT
テーブルあたりのInnodbファイル数 = 1
Innodb_doublewrite = 1

MyISAM I/O関連の設定
遅延キー書き込み
OFF: 各書き込み操作の後にキーバッファ内のダーティブロックをディスクにフラッシュします。
ON: 遅延更新は、テーブルのキー設定時に delay_key_write オプションが指定されたテーブルに対してのみ使用されます。
ALL: すべてのMyISAMテーブルに遅延書き込みを使用する

セキュリティ関連の設定パラメータ
expire_logs_daysは、binlogを自動的にクリーンアップする日数を指定します。
max_allowed_packetはMySQLが接続できるパケットサイズを制御します。32Mに設定することをお勧めします。マスタースレーブレプリケーションを使用する場合は、パラメータを同じ値に設定する必要があります。
skip_name_resolveはDNSルックアップを無効にします
sysdate_is_now sysdate() が有効な日付を返すことを保証する
read_only は、スーパー権限を持たないユーザーによる書き込み操作を禁止します。注: マスタースレーブレプリケーションでは、スレーブデータベースでこの機能を有効にすることをお勧めします。スレーブデータベースの操作が変更されないようにするために、マスターデータベースからのみ同期できます。
skip_slave_start はスレーブの自動回復を無効にします (ライブラリの設定から使用されます)
sql_mode は、MySQL で使用される SQL モードを設定します (これにより、MySQL の実行が失敗する可能性があるため注意してください)
① strict_trans_tables 指定されたデータがデータベースに挿入できない場合、トランザクションエンジンは操作を終了し、非トランザクションエンジンには影響しません。 ② no_engine_subitiution create tableでエンジンを指定する場合、エンジンが利用できない場合は、デフォルトのエンジンを使用してテーブルを作成しません。 ③ no_zero_date 0年0月0日の日付はテーブルに挿入できません。 ④ no_zero_in_date 部分的な値が0の日付を受け入れません。 ⑤ noly_full_group_by

その他のよく使用される設定パラメータ
sync_binlogはMySQLがbinlogをディスクにフラッシュする方法を制御します
tmp_table_size と max_heap_table_size は一時メモリテーブルのサイズを制御します (メモリオーバーフローを回避するために、あまり大きく設定しないでください)
max_connections は、許可される接続の最大数を制御します (デフォルトは 100 ですが、少し小さいので、ビジネスに応じてサイズを適切に調整してください)

パフォーマンスに影響を与えるもの

データベース設計がパフォーマンスに与える影響
1. 過度な非正規化により、テーブルに作成される列が多すぎる
2. 過度な正規化により、テーブルの関連付けが多すぎる（関連付けるテーブルの数は、できるだけ 10 以内に抑える必要がある）
3. OLTP環境でプレフィックスなしのパーティションテーブルを使用する
4. データの整合性を確保するために外部キーを使用する

要約する

パフォーマンス最適化の順序
1. データベース構造設計とSQL文
2. データベースストレージエンジンの選択パラメータの設定
3. システムの選択と最適化
4. ハードウェアのアップグレード

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