一般的な 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実践におけるコマンドモードの詳しい説明

推薦する

HTML 画像 img タグ_Powernode Java アカデミー

まとめプロジェクトの説明形式<img src="..."> H2+ ...

Web デザインのための 5 つのシンプルな XHTML Web フォーム

Web デザイン 5 におけるシンプルな XHTML Web フォーム。 テクニック 1: ラベル ...

MySQL での実行計画の詳細分析

序文効率的なSQL文の書き方は、Explain実行計画の分析と切り離せません。実行計画とは何か、効率...

Alibaba Cloud Centos 7.5 に MySQL をインストールするチュートリアル

CentOS 7 の yum ソースには、MySQL を正常にインストールするための mysql-s...

Linux でソフトウェア パッケージのバージョンをアップグレードする方法の詳細な説明

Linux環境で、特定のソフトウェア(パッケージ)がインストールされているかどうかを確認したい。 r...

CSSスコープ(スタイル分割)の使用の概要

1. CSSスコープの使用(スタイル分割) Vue では、CSS スタイルを現在のコンポーネントでの...

DockerにRedisコンテナをインストールするための実装手順

目次DockerにRedisをインストールする1. Redisイメージを見つける2. Redisイメ...

Linux Centos でスクリプトを使用して Docker をインストールする方法

Dockerの主な機能は何ですか?現在、Docker には少なくとも次のアプリケーション シナリオが...

docker に openjdk をインストールして jar パッケージを実行する方法

画像をダウンロード docker プル openjdkデータボリュームの作成java_appデータボ...

Tomcatアーキテクチャの原則をアーキテクチャ設計に分析する

目次1. 学習目標1.1. Tomcatアーキテクチャの設計と原則をマスターして社内スキルを向上させ...

TypeScriptのインデックスシグネチャの理解に関する簡単な説明

目次1. インデックス署名とは何ですか? 2. インデックス署名構文3. インデックス署名に関する注...

uniapp WeChatミニプログラムのグローバル共有を実装するためのサンプルコード

目次グローバル共有コンテンツファイルを作成するファイルをインポートしてグローバルに登録するページ共有...

WeChatミニプログラムの開発をスピードアップするためのヒント

1. app.jsonを使用してページを作成する私たちの通常の開発習慣によれば、新しいページを作成す...

jsでライトスイッチの効果を実現

この記事の例では、ライトスイッチ効果を実現するためのjsの具体的なコードを参考までに共有しています。...

MySQL は低速クエリを可能にします (EXPLAIN SQL ステートメントの使用の概要)

今日、データベース操作はますますアプリケーション全体のパフォーマンスのボトルネックになりつつあり、こ...