よくある MySQL 設計エラーをご存知ですか?

インターネットの発達により、バスを待ったり地下鉄に乗ったりする時間など、断片的な時間を活用して、いつでもどこでも勉強したり情報を得たりできるようになりました。同時に、発達したインターネットにより、人々は同じ興味やニーズを持つ友人と知識を素早く共有したり、議論したりすることも便利になりました。

しかし、共有が便利になりすぎることで知識が多様化し、間違った情報を受け取りやすくなりました。これらのエラーのほとんどは、テクノロジーの急速な発展と、すでにリリースされたコンテンツを更新するための余裕がないことが原因です。後で学ぶ人たちの誤解を避けるために、今日は MySQL の設計仕様におけるよくあるエラーをいくつか見てみましょう。

主キーの設計

間違った設計仕様: 自動増分 ID 値を主キーとして使用し、UUID、MD5、HASH、または文字列を主キーとして使用しないことが推奨されます。

この設計仕様は多くの記事で見ることができます。自動増分主キーの利点には、占有スペースが小さいこと、順序、使いやすさなどがあります。

まず、自動インクリメント主キーの欠点を見てみましょう。

自動インクリメント値はサーバー側で生成されるため、自動インクリメント AI ロックによって保護する必要があります。このとき、挿入要求が大量にあると、自動インクリメント値によってパフォーマンスのボトルネックが発生する可能性があり、同時実行パフォーマンスの問題が発生します。
主キーとして使用される自己増分値は、現在のインスタンス内でのみ一意であり、グローバルに一意ではないため、分散アーキテクチャで使用することはできません。
公開データ値は、簡単にセキュリティ問題を引き起こす可能性があります。当社の製品 ID が自動増分主キーである場合、ユーザーは ID 値を変更することで製品を取得できます。深刻なケースでは、データベースに保存されている製品の数を知ることができます。
MGR (MySQL グループレプリケーション) によって発生する可能性のあるパフォーマンスの問題。

自動インクリメント値はMySQLサーバー上で生成されるため、自動インクリメントAIロックで保護する必要があります。このとき、挿入リクエストが大量に発生すると、自動インクリメントによってパフォーマンスのボトルネックが発生する可能性があります。たとえば、MySQL データベースでは、パラメータ innodb_autoinc_lock_mode を使用して、自動インクリメントロックが保持される時間を制御します。自動インクリメントの最大パフォーマンスを得るために innodb_autoinc_lock_mode パラメータを調整することはできますが、まだ他の問題が残っています。したがって、同時実行シナリオでは、UUID を主キーとして使用するか、ビジネスによって生成された主キーをカスタマイズすることをお勧めします。

UUID の値を取得するには、MySQL で直接 UUID() 関数を使用できます。

MySQL> UUID() を選択します。
+--------------------------------------+
| UUID() |
+--------------------------------------+
| 23ebaa88-ce89-11eb-b431-0242ac110002 |
+--------------------------------------+
セット内の 1 行 (0.00 秒)

時間を保存する場合、UUID は時間ビットの逆順で保存されます。つまり、低い時間ビットが先頭に保存され、高い時間ビットが最後尾に保存されます。つまり、UUID の最初の 4 バイトは時間とともに「ランダムに」変化し続け、単調に増加しません。非ランダムな値は挿入時に個別の IO を生成し、パフォーマンスのボトルネックを引き起こします。これは、自動インクリメントと比較した UUID の最大の欠点でもあります。

この問題を解決するために、MySQL 8.0 では UUID 文字列を変換できる関数 UUID_TO_BIN が導入されました。

パラメータを使用して最も高い値を持つ時間ビットを先頭に配置することで、UUID を挿入する際の乱れの問題が解決されます。
ストレージスペースを節約するために、不要な文字列「-」を削除しました。
文字列は保存のためにバイナリ値に変換され、最終的にスペースは 36 バイトから 16 バイトに短縮されます。

次に、関数 UUID_TO_BIN を使用して、前の UUID 文字列 23ebaa88-ce89-11eb-b431-0242ac110002 を変換します。バイナリ値は次のようになります。

MySQL> UUID_TO_BIN('23ebaa88-ce89-11eb-b431-0242ac110002',TRUE) を UUID_BIN として選択します。
+------------------------------------+
文字列
+------------------------------------+
| 0x11EBCE8923EBAA88B4310242AC110002 |
+------------------------------------+
セット内の1行（0.01秒）

さらに、MySQL 8.0 では、バイナリ値を UUID 文字列に変換する機能をサポートする BIN_TO_UUID 関数も提供されています。

MySQL 8.0 より前には UUID_TO_BIN/BIN_TO_UUID 関数はありませんが、関数をカスタマイズすることで解決できます。アプリケーション層では、独自のプログラミング言語に応じて対応する関数を記述できます。

もちろん、多くの学生は UUID が占めるパフォーマンスとストレージスペースについても心配しています。ここで、関連する挿入パフォーマンステストもいくつか実行しました。結果を次の表に示します。

ご覧のとおり、MySQL 8.0 が提供するソートされた UUID は、自動インクリメント ID よりも優れたパフォーマンスを発揮します。さらに、UUID_TO_BIN 変換の結果は 16 バイトであり、自動インクリメント ID より 8 バイトだけ多いため、最終的なストレージ容量は自動インクリメント ID より 3G だけ多くなります。

また、UUID はグローバルな一意性を保証できるため、UUID を使用する利点は自己増分 ID よりもはるかに大きくなります。自動インクリメントを主キーとして使用することに慣れているかもしれませんが、同時実行シナリオでは、UUID などのグローバルに一意の値を主キーとして使用することをお勧めします。

もちろん、UUID は優れていますが、分散シナリオでは、後続のセカンダリインデックスのクエリ効率を確保するために、プライマリキーに追加情報を追加する必要があります。ビジネスのカスタマイズに応じてプライマリキーを生成することをお勧めします。ただし、同時実行性とデータ量がそれほど大きくない場合は、自己増分 UUID を使用することをお勧めします。 UUID は主キーとして使用できないと考えないでください。

金融分野のデザイン

設計仕様が間違っています: float と double はどちらも不正確な浮動小数点型ですが、decimal は正確な浮動小数点型であるため、財務関連の金額データでは、decimal 型を使用する必要があります。したがって、ユーザー残高や製品価格などの財務フィールドを設計する場合、通常はセント単位の精度を実現できる小数型が使用されます。

ただし、大規模なインターネットサービスの設計標準では、DECIMAL 型は推奨されていません。代わりに、DECIMAL を整数型に変換することが推奨されています。つまり、財務上の目的では、データを元ではなくセントで保存することが推奨されます。たとえば、1 元はデータベースでは整数型 100 として保存されます。

bigint 型の利点は次のとおりです。

10 進数はバイナリエンコード方式であり、計算効率は bigint ほど良くありません。
bigint を使用すると、フィールドは固定長フィールドとなり、効率的に保存できます。Decimal は定義された幅によって決まります。データ設計では、固定長ストレージの方がパフォーマンスが優れています。
bigint を使用して単位に分割された金額を保存します。また、ギガバイト範囲の金額を保存することもできます。これで十分です。

列挙フィールドの使用

悪い設計方法: ENUM型の使用を避ける

これまでの開発プロジェクトでは、ユーザーの性別、商品が棚にあるかどうか、コメントが非表示かどうかなどのフィールドに遭遇したとき、フィールドは単純に tinyint として設計され、その後、そのフィールドは状態として 0 と 1 のメモに記録されていました。

この設計の問題点も明らかです。

不明瞭な表現: このテーブルは他の同僚によって設計された可能性があります。深い印象を持っていない場合は、フィールドのコメントを毎回読む必要があり、コーディング時にデータベースにアクセスしてフィールドの意味を確認する必要がある場合もあります。
ダーティデータ: 挿入された値はアプリケーションレイヤーのコードによって制限できますが、SQL および視覚化ツールを通じて値を変更することは可能です。

このタイプの固定オプション値フィールドでは、ENUM列挙文字列型とSQL_MODEの厳密モードを使用することをお勧めします。

MySQL 8.0.16 以降のバージョンでは、列挙フィールド型を使用せずにチェック制約メカニズムを直接使用できます。

さらに、列挙値を定義するときは通常、「Y」や「N」などの単一の文字を使用するため、多くのスペースを占有しません。ただし、オプションの値が固定されておらず、ビジネスの発展に伴って増加する可能性がある場合は、列挙フィールドを使用することはお勧めしません。

インデックス番号の制限

設計仕様が間違っています。各テーブルのインデックス数を制限します。テーブルには 5 つ以上のインデックスを含めることはできません。

1 つの MySQL テーブルに対するインデックスの数に制限はありません。ビジネスクエリに特定のニーズがある場合は、それを作成できます。制限について迷信的に考えないでください。

サブクエリの使用

悪い設計方法: サブクエリを避ける

実際、この仕様は MySQL の古いバージョンには当てはまります。MySQL データベースの以前のバージョンではサブクエリの最適化が制限されていたため、多くの OLTP ビジネスシナリオでは、オンラインビジネスでサブクエリをできるだけ避けることが求められます。

ただし、MySQL 8.0 ではサブクエリの最適化が大幅に改善されているため、新しいバージョンの MySQL でもサブクエリを安全に使用できます。

サブクエリは JOIN よりも人間にとって理解しやすいです。たとえば、2020 年に論文を発表していない学生の数を確認したいとします。

選択カウント(*)
ユーザーより
id が ( に含まれない場所
    ユーザーIDを選択
    ブログより
    publish_time >= "2020-01-01" かつ publish_time <= "2020-12-31" の場合
）

ご覧のとおり、サブクエリのロジックは非常に明確です。記事テーブルのユーザーをクエリするために not IN が使用されています。

左結合を使って書くと

SELECT カウント(*)
ユーザー左からブログに参加
ON user.id = blog.user_id かつ blog.publish_time >= "2020-01-01" かつ blog.publish_time <= "2020-12-31"
blog.user_id は NULL です。

LEFT JOIN も上記の要件を満たすことができますが、理解するのは簡単ではないことがわかります。

explain を使用して 2 つの SQL ステートメントの実行プランを表示し、それらが同じであることを確認します。

上の図から、サブクエリと LEFT JOIN の両方が最終的に左ハッシュ結合に変換されるため、上記の 2 つの SQL ステートメントの実行時間は同じであることが明確にわかります。つまり、MySQL 8.0 では、オプティマイザが IN サブクエリを最適な JOIN 実行プランに自動的に最適化するため、パフォーマンスが大幅に向上します。

要約する

これまでのコンテンツを読んだ後、MySQL について新たな理解が得られたことと思います。これらの一般的なエラーは次のようにまとめることができます。

UUID は主キーとしても使用できます。自動増分 UUID のパフォーマンスは自動増分主キーよりも優れており、占有される余分なスペースはごくわずかです。
財務フィールドでは、10 進数に加えて bigint を使用して単位に分割されたデータを保存することもできます。
固定オプション値を持つフィールドについては、MySQL 8より前では列挙フィールドを使用し、MySQL 8以降ではチェック関数制約を使用することをお勧めします。0、1、または2を使用して表さないでください。
テーブル内のインデックス数に制限はなく、5 を超えることはできません。業務状況に応じて追加または削除できます。
MySQL8 ではサブクエリが最適化されており、安心して使用できます。

よくある間違ったMySQL設計仕様に関するこの記事はこれで終わりです。関連するMySQLの間違った設計仕様については、123WORDPRESS.COMの以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも123WORDPRESS.COMを応援していただければ幸いです。

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