よくある MySQL テーブル設計エラーの概要

間違い1: データの列が多すぎる

MySQL ストレージエンジン API は、行バッファ方式でサーバーからストレージエンジンにデータをコピーします。サーバーはバッファデータをデータ列にデコードします。ただし、行バッファの形式をデータ行データ構造の列に変換すると、コストが高くなる可能性があります。 MyISAM はサーバーに一致する固定行形式を使用するため、変換は必要ありません。ただし、MyISAM の可変行形式と InnoDB の行形式では、常に変換が必要になります。変換のコストは列の数によって異なります。データテーブル内の列数が数百を超えると、たとえ数列しか使用されていない場合でも、CPU リソースの消費量が増大します。かつて、システムでサポートされている言語を対応する列で単純かつ大まかに表現した多言語ソリューションについて言及した記事を読んだことがあります。たとえば、次のようになります。

テーブル t_multi_language_news を作成します (
  id INT 主キー、
  タイトル_cn VARCHAR(32)、
  タイトル_en VARCHAR(32),
  タイトル_it VARCHAR(32)、
  ...
  content_cn VARVHAR(256)、
  コンテンツ_en VARCHAR(256)、
  コンテンツ_it VARCHAR(256)、
);

このアプローチでは、システムがサポートする言語が増え、データテーブル内の列数が増えるにつれて、最終的には重大なパフォーマンスの低下につながります。 100 を超える列を持つデータテーブルを設計する場合は、設計が妥当かどうかを検討する必要があります。 **対応方法: **まず、ビジネス自体の設計が妥当かどうかを検討します。エンティティを記述するために多くのフィールドが必要な場合は、データテーブルを分割し、情報テーブルを拡張することで実行できます。たとえば、情報データテーブルの場合、コンテンツは一般的に大きなスペースを占めますが、リストで直接表示されることはないため、情報メインテーブルと情報詳細テーブルに分割できます。メインテーブルには、タイトル、時間、概要、サムネイルの添付ファイル ID など、リストに表示される情報が格納されます。情報詳細には、コンテンツ、ソース、元のリンクなどの情報を保存できます。

誤解2: 共同クエリが多すぎる

MySQL は一度に最大 61 個のテーブルのみをクエリできます。一部の設計では、冗長なフィールドを設計しないことを推奨していますが、その結果、複雑なビジネスでのクエリのために複数のテーブルを接続する必要が生じます。結合されたテーブルの数が 61 未満の場合でも、パフォーマンスが低下し、SQL ステートメント全体のメンテナンスが非常に困難になります。設計の第一原則として、速度を追求する場合、特に同時実行性の高いシナリオに直面している場合は、1 つのクエリであまり多くのデータテーブルにわたる結合クエリを実行しないでください。 **対応方法: **まず、変更されないことが確実なフィールドについては、冗長フィールドを使用して共同クエリを減らすことを検討できます。たとえば、会社の都道府県情報では、都道府県コードと都道府県名を冗長化することができ、都道府県コードを通じて都道府県名を照会する必要がなくなります。次に、他のテーブルをクエリする必要がある場合は、ステップバイステップのクエリ方法を使用して、アプリケーションを通じてデータアセンブリを完了することを検討できます。この方法は、データテーブルが多数ある場合により効率的であり、コードの保守が容易になります。誤解 3: ユニバーサル列挙たとえば、次のテーブル設計:

テーブルt_countriesを作成します（
  ...
  国 ENUM('', '1', '2', ..., '45'),
  ...
);

これは、整数キーを持つ辞書検索テーブルを使用して実装することもできます。ビジネス目的で列挙が追加された場合は、ALTER TABLE を使用してテーブル全体を更新する必要があります。アプリケーションコードでルックアップテーブルを使用する場合は、新しいキーと値のペアを追加するだけで済みます。 **対処方法:** 列挙型が不変であることがわかっている場合 (性別など) は問題ありません。列挙を増やす可能性がある場合は、可能であればアプリケーションを通じて行うのが最適です。

誤解3: ENUMの代わりにSETを乱用する

データテーブル内の列挙 ENUM 型列の値は値セットのうちの 1 つだけになりますが、SET 型列には 1 つ以上の値を設定できます。列に値が 1 つだけ含まれることが確実な場合は、コレクションではなく列挙を使用する必要があります。たとえば、次の例は典型的な虐待です。

テーブル t_payment_way を作成します (
  ...
  is_default SET('Y', 'N') NOT NULL デフォルト 'N',
  ...
);

明らかに、is_default は Y または N のいずれかなので、ここでは ENUM を使用する必要があります。 **解決策: **ビジネスの観点から、列に複数の値があるかどうかを検討します。オプションの値が 1 つしかない場合は、列挙 ENUM を使用します。

誤解4: NULLを避ける

多くの記事では、NULL をできるだけ避けることについて議論されています。これは、ほとんどのシナリオに適した設計です。null 値は、0、空の文字列、合意された値などで表すことができます。ただし、これに惑わされないでください。値自体が無意味な場合は、NULL を使用する方が適切な場合があります。たとえば、-1 が意味のない整数を表す場合、コードが複雑になり、バグが発生する可能性もあります。たとえば、次の例をご覧ください。

テーブルt_personを作成します（
  誕生日 DATETIME NOT NULL デフォルト '0000-00-00 00:00:00',
  ...、
);

DATETIME 型のデフォルト値をすべて 0 に設定するのは奇妙です。人々の平均年齢を計算したい場合、説明できない問題が発生します。このシナリオでは、NULL を使用すると統計に含まれません。意味のない日付の使用を禁止するように MySQL SQL_MODE パラメータを設定することで、この状況を回避できます。 **対処方法: **テーブルを設計するときに、NOT NULL を使用して null 値を回避することもできますが、あまり厳密になりすぎないようにしてください。一部のフィールドでは、デフォルト値がテーブル名に意味をなさない場合や実際の状況と一致しない場合は、NULL 列を使用することもできます。ただし、インデックス列に NULL を使用しないように注意する必要があります。実際には、ほとんどのインデックス列が NULL になる可能性は低いです。

誤解5: タイムスタンプの代わりに整数を使用する

以前に、時間形式の選択方法について説明しました。実際、タイムスタンプの保存に整数を使用する開発者もいます。その理由は、この方が効率的だからです。ある意味、効率が少しは向上するかもしれませんが、MySQL では DATETIME と TIMESTAMP が整数として保存されるため、あまり役に立ちません。整数を使用して時間を保存する場合、アプリケーションで時間の変換を行う必要があるか、SQL ステートメントで指定されたフィールドの時間を変換する必要があることを意味し、メリットがコストを上回らない可能性があります。 **解決策: **できるだけ多くの時間を保存するには、DATETIME を使用してください。秒未満の精度で時間を保存する必要がある場合は、BIGINT を使用して保存することを検討してください。

間違い6: フィールドの最大保存範囲を忘れる

実際には、テーブルを設計するときに、データ型の保存範囲を忘れることがよくあります。たとえば、TINYINT(2) は 2 桁の整数の保存を制限しません。TINYINT(2) の実際の保存範囲は -128 ～ 127 です。 255 より大きい整数を格納します。このエラーは、整数型を使用する場合に発生しがちです。整数を挿入する場合、MySQL は実際の整数の桁数をチェックせず、対応するストレージバイトの範囲に従って格納します。この場合、注意しないと意味のない値が格納されます。たとえば、次の INSERT 操作は成功しますが、TINYINT(2) は 2 ビットの整数しか格納できないと誤って考えてしまう可能性があります。

テーブルt_int_testを作成します（
    id INT 主キー、
    数値TINYINT(2)
);

t_int_test (id, number) に値 (3,123) を挿入します。

解決策: アプリケーションでデータ検証を実行します。

結論：

実際のデータテーブルの設計プロセスでは、各フィールドのデータ型を考慮するだけでなく、ストレージ領域のサイズも考慮する必要があります。時間、タイトル、メモなどのよく使用されるフィールドについては、社内で一定の標準を策定し、全員がその標準に従うのが最適です。検証を追加することで、多くの問題を回避できます。

上記は、一般的な MySQL データテーブル設計エラーの詳細な概要です。MySQL データテーブル設計エラーの詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

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