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

一般的な 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_pa​​cketは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. ハードウェアのアップグレード

以下もご興味があるかもしれません:
  • MysqlチューニングExplainツールの詳細な説明と実践的な演習(推奨)
  • MySQL パフォーマンス チューニングについて知っておくべき 15 個の重要な変数 (要約)
  • SQL Server パフォーマンス チューニングのための I/O オーバーヘッドの詳細な分析
  • SQL Server のパフォーマンス チューニング: クエリ時間を 20 秒から 2 秒に短縮する方法
  • SQL Server パフォーマンス チューニング キャッシュ
  • 数千万ユーザー規模のシステムにおけるSQLチューニングの実践的な共有

<<:  フロントエンドとバックエンドを分離した nginx 構成を展開するための完全な手順

>>:  Javascript実践におけるコマンドモードの詳しい説明

推薦する

XHTML 入門チュートリアル: XHTML ハイパーリンク

ハイパーリンクはインターネット全体を接続していると言っても過言ではありません。ハイパーリンクは、別の...

Windows での PyTorch 開発環境のインストール チュートリアル

アナコンダのインストールAnaconda は、Python の使用を容易にするために作成されたソフト...

Dockerコンテナを更新、パッケージ化、Alibaba Cloudにアップロードする方法

今回は、実行中のコンテナをイメージにパッケージ化して Alibaba Cloud にアップロードし、...

CSS スタイルの読み込みの優先順位に関する経験の共有

昨日のプロジェクト開発中に、スタイルの読み込み優先順位に関する問題が発生しました。クラスは定義され、...

Vueデータ監視の原理の詳細な説明

<本文> <div id="ルート"> <h1&...

Nginx プロキシを使用してインターネットを閲覧する方法

私は通常、Tomcatや他のアプリケーションのリバースプロキシとしてnginxを使用しています。実際...

MySQL ストアド関数(カスタム関数)の定義と使用方法の詳細な説明

ストアド関数ストアド関数とは: SQL コードの一部をカプセル化し、特定の関数を完了して、結果を返し...

CSS でベジェ曲線の実装を反転する方法

まずは、以前書いた CSS カルーセルアニメーション効果を見てみましょう。アニメーションの遷移をスム...

一意の注文番号を生成するためのMySQLの高同時実行方法

序文このブログ記事が公開された後、何人かの友人からSQL Serverバージョンがあるかどうか尋ねら...

Docker マルチステージビルドを使用してイメージサイズを縮小する方法

この記事では、Docker のマルチステージ ビルド機能を使用してイメージ サイズを大幅に削減する方...

より人気がありクリエイティブなダーク背景のウェブデザインの例

暗い背景スタイルのページ デザインは非常に人気があり、シックでエレガント、そして非常にクリエイティブ...

MySQLデータベースのマスタースレーブレプリケーションと読み取り書き込み分離に関する詳細なチュートリアル

目次序文1. MySQL マスタースレーブレプリケーション1. サポートされているレプリケーションの...

VMware での Ubuntu と Windows 間のファイル共有

この記事では、VMware 環境下で Ubuntu と Windows 間でファイルを共有する方法を...

MySQL 8.0.22 圧縮パッケージの完全なインストールと構成のチュートリアル図 (テスト済みで効果的)

1. zipインストールパッケージをダウンロードするMySQL サーバー 8.0.22 の圧縮パッ...

Sysbench の MySQL ベンチマーク プロセスの分析

序文1.ベンチマークは、テスト オブジェクトのクラスの特定のパフォーマンス指標の定量的、再現可能、比...