データベースのパフォーマンスを向上させるための主要な ySQL スキーマチェック-mysql チュートリアル-php.cn

データベーススキーマは、テーブル、列、関係、インデックス、データの編成方法やアクセス方法を形成する制約など、データベースの論理構造を定義します。データの保存方法だけでなく、データがクエリ、トランザクション、その他の操作とどのようにやり取りするかについても重要です。

これらのチェックは、雪だるま式に大きな問題に発展する前に、新しい問題や長引く問題を常に把握するのに役立ちます。以下のスキーマチェックをさらに詳しく調べて、データベースが合格しなかった場合に問題を修正する方法を正確に確認できます。 スキーマを変更する前に、変更中に発生する可能性のある潜在的なリスクから保護するために、必ずデータをバックアップしてください。

1. 主キーのチェック (主キーの欠落)

主キーはテーブルの重要な部分であり、各行を一意に識別し、効率的なクエリを可能にします。主キーがないと、テーブルでパフォーマンスの問題が発生する可能性があり、レプリケーションやスキーマ変更ユーティリティなどの特定のツールが適切に機能しない可能性があります。

スキーマの設計時に主キーを定義することで回避できる問題がいくつかあります。

主キーまたは一意キーが指定されていない場合、MySQL はアクセスできない内部キーを作成します。
主キーがないと、特に行ベースまたは混合レプリケーションの場合、レプリケーションのパフォーマンスが低下する可能性があります。
主キーにより、スケーラブルなデータのアーカイブとパージが可能になります。 pt-online-schema-change のようなツールには、主キーまたは一意のキーが必要です。
主キーは行を一意に識別します。これはアプリケーションの観点から非常に重要です。

例

テーブルがすでに作成されている場合に「ID」列に PRIMARY KEY 制約を作成するには、次の SQL を使用します。

ALTER TABLE Persons ADD PRIMARY KEY (ID);

ログイン後にコピー

複数の列に主キーを定義するには:

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

ログイン後にコピー

注: ALTER TABLE コマンドを使用する場合は、テーブルが最初に作成されたときに主キー列に NULL 値が含まれないように宣言されている必要があります。

2. テーブルエンジンのチェック(非推奨のテーブルエンジン)

MyISAM ストレージエンジンは非推奨になっており、これをまだ使用しているテーブルは InnoDB に移行する必要があります。 InnoDB は、優れたパフォーマンス、データ回復機能、トランザクションサポートにより、ほとんどのユースケースでデフォルトの推奨エンジンです。 MyISAM から InnoDB に移行すると、書き込みの多いアプリケーションのパフォーマンスが大幅に向上し、フォールトトレランスが向上し、全文検索や外部キーなどのより高度な MySQL 機能が可能になります。

InnoDB が推奨される理由:

クラッシュ回復機能により、データベースサーバーまたはホストのクラッシュからデータを破損することなく自動的に回復できます。
クエリの影響を受ける行のみをロックするため、同時実行性の高い環境でのパフォーマンスが大幅に向上します。
データとインデックスの両方をメモリにキャッシュします。これは、読み取り負荷の高いワークロードに推奨されます。
完全に ACID に準拠し、データの整合性を確保し、トランザクションをサポートします。
InnoDB エンジンは MySQL 開発コミュニティから大部分の注目を集めており、最新で十分にサポートされているエンジンとなっています。

InnoDB への移行方法

ALTER TABLE Persons ADD PRIMARY KEY (ID);

ログイン後にコピー

3. テーブル照合順序チェック (混合照合順序)

テーブル間またはテーブル内で異なる照合順序を使用すると、特に文字列の比較や結合時にパフォーマンスの問題が発生する可能性があります。 2 つの文字列カラムの照合順序が異なる場合、MySQL は実行時に文字列を変換する必要がある場合があり、これによりインデックスが使用できなくなり、クエリが遅くなる可能性があります。

混合照合テーブルに変更を加えると、いくつかの問題が表面化する可能性があります。

照合順序は列レベルで異なる場合があるため、結合内の関連する列に一致する照合順序がある場合、テーブルレベルでの不一致によって問題が発生することはありません。
テーブルの照合順序の変更、特に文字セットの切り替えは、必ずしも簡単ではありません。データ変換が必要な場合があり、サポートされていない文字は破損したデータになる可能性があります。
テーブルの作成時に照合順序または文字セットを指定しない場合、データベースのデフォルトが継承されます。データベースレベルで何も設定されていない場合は、サーバーのデフォルトが適用されます。これらの問題を回避するには、データセット全体、特に結合操作で頻繁に使用される列の照合順序を標準化することが重要です。

照合順序設定を変更する方法

データベースの照合設定を変更する前に、意図しない結果を避けるために、非運用環境でアプローチをテストしてください。不明な点がある場合は、DBA に相談することをお勧めします。

すべてのデータベースのデフォルトの文字セットと照合順序を取得します:

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

ログイン後にコピー

特定のテーブルの照合順序を確認します:

ALTER TABLE <table_name> ENGINE=InnoDB;

ログイン後にコピー

サーバーのデフォルトの文字セットを検索します:

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, 
DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA;

ログイン後にコピー

サーバーのデフォルトの照合順序を検索します:

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM
information_schema.TABLES WHERE TABLE_COLLATION IS NOT NULL ORDER BY
TABLE_SCHEMA, TABLE_COLLATION;

ログイン後にコピー

特定のデータベースの照合順序を更新します:

SELECT @@GLOBAL.character_set_server;

ログイン後にコピー

特定のテーブルの照合順序を更新します:

SELECT @@GLOBAL.collation_server;

ログイン後にコピー

4. テーブル文字セットチェック（混合文字セット）

混合文字セットは、パフォーマンスと互換性の問題を引き起こす可能性があるという点で混合照合順序と似ています。混合文字セットは、異なる列またはテーブルがデータの保存に異なるエンコード形式を使用する場合に発生します。

文字セットが混在していると、インデックスの使用が妨げられたり、値の変換が必要になったりするため、文字列列の結合パフォーマンスが低下する可能性があります。
文字セットは列レベルで定義でき、結合に関係する列の文字セットが一致している限り、テーブルレベルでの不一致によってパフォーマンスが影響を受けることはありません。
テーブルの文字セットの変更にはデータ変換が含まれる場合があり、サポートされていない文字が発生するとデータが破損する可能性があります。
文字セットまたは照合順序が指定されていない場合、テーブルはデータベースのデフォルトを継承し、データベースはサーバーのデフォルトの文字セットと照合順序を継承します。

キャラクター設定の変更方法

データベースの文字設定を調整する前に、予期しない問題が発生しないように、必ずステージング環境で変更をテストしてください。手順について不明な点がある場合は、DBA に相談して指示を受けてください。

すべてのデータベースのデフォルトの文字セットと照合順序を取得します:

ALTER TABLE Persons ADD PRIMARY KEY (ID);

ログイン後にコピー

列の文字セットを取得します:

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

ログイン後にコピー

サーバーのデフォルトの文字セットを検索します:

ALTER TABLE <table_name> ENGINE=InnoDB;

ログイン後にコピー

サーバーのデフォルトの照合順序を検索します:

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, 
DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA;

ログイン後にコピー

テーブルの構造を表示するには:

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM
information_schema.TABLES WHERE TABLE_COLLATION IS NOT NULL ORDER BY
TABLE_SCHEMA, TABLE_COLLATION;

ログイン後にコピー

出力例:

SELECT @@GLOBAL.character_set_server;

ログイン後にコピー

列の文字セットを変更するには:

SELECT @@GLOBAL.collation_server;

ログイン後にコピー

5. カラム自動インクリメントチェック(自動インクリメントカラムの種類)

無限に大きくなることが予想され、主キーに自動インクリメントを使用するテーブルの場合は、UNSIGNED BIGINT データ型に切り替えることをお勧めします。これにより、列でより広い範囲の値を処理できるようになり、最大値に達した後にコストのかかるテーブルを変更する必要がなくなります。 UNSIGNED を指定すると、正の値のみが格納され、データ型の範囲が効果的に 2 倍になります。

キャラクター設定の変更方法

列の型を UNSIGNED BIGINT に変更するには:

ALTER DATABASE <db-name> COLLATE=<collation-name>;

ログイン後にコピー

6. テーブル外部キーチェック(外部キーの有無)

外部キーは、親テーブルと子テーブル間の関係を維持することでデータの一貫性を提供しますが、データベースのパフォーマンスにも影響します。書き込み操作が発生するたびに、関連データの整合性を検証するために追加のルックアップが必要になります。これにより、特に交通量の多い環境では速度が低下する可能性があります。

パフォーマンスが懸念される場合は、特にデータの整合性をアプリケーションレベルで処理できるシナリオでは、外部キーの削除を検討することをお勧めします。

外部キーを削除する方法

テーブルから外部キー制約を削除するには:

ALTER TABLE Persons ADD PRIMARY KEY (ID);

ログイン後にコピー

7. 重複インデックスチェック

MySQL の重複インデックスは不要なディスク領域を消費し、すべてのインデックスを更新する必要があるため、書き込み操作中に追加のオーバーヘッドが発生します。これにより、クエリの最適化が複雑になり、実際のメリットが得られずに非効率的な実行計画につながる可能性があります。

重複したインデックスを特定して削除し、クエリの最適化を合理化し、オーバーヘッドを削減します。ただし、インデックスを削除する前に、そのインデックスが重要なクエリに使用されていないことを確認してください。

8. 未使用インデックスのチェック

MySQL の未使用のインデックスは、ディスク領域を消費し、挿入、更新、削除時の処理オーバーヘッドを増加させ、全体的な操作を遅くすることにより、データベースのパフォーマンスに悪影響を与える可能性があります。インデックスはクエリを高速化するのに役立ちますが、インデックスが使用されないとシステムに不必要な負担がかかる可能性があります。
未使用または重複したインデックスを削除すると、さらに次のような利点があります。

インデックスが少なくなると、MySQL のオプティマイザーが評価する選択肢が減り、クエリの実行が簡素化され、CPU/メモリの使用量が削減されます。
未使用のインデックスを削除すると、より重要なデータに使用できる貴重なディスク領域が解放され、I/O 効率も向上します。
インデックスの数を最小限に抑えると、再構築や再編成などのインデックスのメンテナンスタスクが高速化され、リソースの消費が少なくなります。これにより、特に 24 時間年中無休の稼働時間が必要な環境で、よりスムーズな運用が可能になります。

MySQL または MariabDB で未使用のインデックスを識別するには、次の SQL ステートメントを使用してください:

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

ログイン後にコピー

未使用または重複したインデックスを削除する方法

MySQL 8.0 以降では、インデックスを非表示にして、完全に削除せずにインデックスが必要かどうかをテストできます。

ALTER TABLE <table_name> ENGINE=InnoDB;

ログイン後にコピー

パフォーマンスに影響がない場合は、インデックスを安全に削除できます。

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, 
DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA;

ログイン後にコピー

必要に応じて、インデックスを表示に戻すことができます:

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM
information_schema.TABLES WHERE TABLE_COLLATION IS NOT NULL ORDER BY
TABLE_SCHEMA, TABLE_COLLATION;

ログイン後にコピー