MySQLの数値型自動増分における落とし穴

テーブル構造を設計する場合、数値型は最も一般的な型の 1 つですが、数値型をうまく使用するのは想像するほど簡単ではありません。次に例を示します。

• 大規模な同時インターネットビジネス向けに自動増分主キーを設計するにはどうすればよいでしょうか? INTを使用するだけで十分ですか?
• 口座残高をどのように設計すればよいですか? DECIMAL 型の使用は安全ですか?

上記はすべて間違っています!

数値型は単純に見えるかもしれませんが、テーブル構造アーキテクチャの設計では、前述の「不完全な設計思考」の問題が発生しやすくなります（特に、大規模な同時インターネット シナリオの場合）。

数値型

整数型

MySQL データベースは、SQL 標準でサポートされている整数型 (INT および SMALLINT) をサポートしています。さらに、MySQL データベースは、TINYINT、MEDIUMINT、BIGINT などの整数型もサポートしています (表 1 に、さまざまな整数型のストレージ領域と値の範囲を示します)。

整数型には、整数の値の範囲を表す signed 属性と unsigned 属性があり、デフォルトは signed です。設計時に、意図的に符号なし属性を使用することはお勧めしません。これは、データ分析を行うときに、SQL によって返される結果が希望する結果ではない可能性があるためです。

テーブル構造とデータが次のようになっている「売上テーブル」の例を見てみましょう。列 sale_count が unsigned 属性を使用していることに注意することが重要です (つまり、列は 0 以上の値を格納するように設計されています)。

mysql> SHOW CREATE TABLE sale_G

************************** 1. 行 ****************************

       テーブル: セール

テーブルの作成: CREATE TABLE `sale` (

  `sale_date` 日付が NULL ではない、

  `sale_count` int unsigned デフォルト NULL,

  主キー (`sale_date`)

) エンジン=InnoDB デフォルト文字セット=utf8mb4 照合=utf8mb4_general_ci

セット内の 1 行 (0.00 秒)


mysql> SELECT * FROM セール;

+------------+-------------+

| セール日 | セール回数 |

+------------+-------------+

| 2020-01-01 | 10000 |

| 2020-02-01 | 8000 |

| 2020-03-01 | 12000 |

| 2020-04-01 | 9000 |

| 2020-05-01 | 10000 |

| 2020-06-01 | 18000 |

+------------+-------------+

セット内の 6 行 (0.00 秒)

このうち、sale_date は販売日を表し、sale_count は月ごとの販売数量を表します。現在、上司はビジネス上の意思決定を行うために、毎月の売上高の変化を計算したいという要件があります。この SQL ステートメントでは非等価結合が必要ですが、記述するのはそれほど難しくありません。

選択 

    s1.sale_date、s2.sale_count - s1.sale_count AS 差分

から

    セール s1

        左結合

    セール s2 ON DATE_ADD(s2.sale_date, INTERVAL 1 MONTH) = s1.sale_date

sale_date で並べ替えます。

ただし、実行中に、列 sale_count が unsigned 属性を使用するため、次の結果がスローされます。

エラー 1690 (22003): BIGINT UNSIGNED 値が '(`test`.`s2`.`sale_count` - `test`.`s1`.`sale_count`)' の範囲外です

ご覧のとおり、MySQL は計算結果が範囲外であることをユーザーに通知します。実際、MySQL では、減算後も符号なしの値は符号なしのままである必要があります。そうでない場合はエラーが報告されます。

このエラーを回避するには、データベース パラメータ sql_mode を NO_UNSIGNED_SUBTRACTION に設定して減算結果に符号を付けられるようにし、最終的な目的の結果が得られるようにする必要があります。

mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';

クエリは正常、影響を受けた行は 0 行 (0.00 秒)

選択


    s1.販売日、

    IFNULL(s2.sale_count - s1.sale_count,'') 差分

から

    セール s1

    左 JOIN セール s2

    ON DATE_ADD(s2.sale_date, INTERVAL 1 ヶ月) = s1.sale_date

sale_date で並べ替えます。


+------------+--------+

| 販売日 | 差分 |

+------------+--------+

| 2020-01-01 | |

| 2020-02-01 | 2000 |

| 2020-03-01 | -4000 |

| 2020-04-01 | 3000 |

| 2020-05-01 | -1000 |

| 2020-06-01 | -8000 |

+------------+--------+

セット内の 6 行 (0.00 秒)

浮動小数点型と高精度型

一般的に使用されるデジタル型には、整数型に加えて、浮動小数点型や高精度型などがあります。
以前のバージョンの MySQL には浮動小数点型 Float と Double がありましたが、これらの型は高精度ではなく、SQL 標準型でもないため、実際の運用環境での使用は推奨されません。そうしないと、計算中に精度型の問題により最終的な計算結果が間違ってしまいます。
さらに重要なのは、MySQL 8.0.17以降、Float型またはDouble型を使用してテーブルを作成すると、次の警告が表示されることです。MySQLは、上記の浮動小数点型を使用しないようにユーザーに通知し、浮動小数点型は以降のバージョンでは非推奨になることも通知します。

浮動小数点データ型の桁数の指定は非推奨であり、将来のリリースでは削除される予定です。

数値型の中で高精度の DECIMAL 型を使用できます。この型の列を宣言するときには、精度とスケールを指定できます (通常は指定する必要があります)。次に例を示します。

給料 DECIMAL(8,2)

ここで、8 は精度 (精度は値に格納される先頭の桁数を意味します) であり、2 はスケール (スケールは小数点以下に格納される桁数を意味します) です。通常、テーブル構造の設計では、DECIMAL 型を使用して、ユーザーの給与、口座残高、およびその他のサービスを小数点第 2 位までの精度で表すことができます。

ただし、大量の同時処理が行われるインターネットビジネスで使用する場合、金額フィールドには DECIMAL 型は推奨されません。代わりに、INT 整数型が推奨されます (理由は後述します)。

ビジネステーブル構造の実用的な設計

整数型と自動増分設計

実際のビジネス シナリオでは、整数型はビジネスにおけるアイテムの数量を表すために最もよく使用されます。例えば、上記表の販売数量や、電子商取引における在庫数量と購入頻度などです。ビジネスにおいて、整数型のもう 1 つの一般的かつ重要な用途は、テーブルの主キーとして使用すること、つまり、データの行を一意に識別することです。
整数を属性 auto_increment と組み合わせると、自己増分機能を実現できますが、テーブル構造設計で自動増分を主キーとして使用する場合は、次の 2 つの点に特に注意する必要があります。注意しないと、ビジネスに壊滅的な打撃を与える可能性があります。

• INT の代わりに BIGINT を主キーとして使用します。
• 自動インクリメント値は永続的ではなく、バックトラックされる可能性があります (MySQL 8.0 より前)。

表1から、INTの最大範囲は42億レベルであることがわかります。実際のインターネットビジネスシナリオの適用では、最大値に到達するのは簡単です。例えば、フローテーブルやログテーブルの中には、1日あたり1000万のデータ量を持つものがあり、420日経過するとINT型の上限に達してしまいます。
したがって、自動インクリメント整数を主キーとして使用する場合は、常に INT ではなく BIGINT を使用してください。 4バイトを節約するためにINTを使用しないでください。上限に達すると、テーブル構造の変更に大きな負担と苦痛が生じます。
これによって興味深い疑問が生じます。INT 型の上限に達した場合、データベースのパフォーマンスはどうなるのでしょうか?また1になるのでしょうか？次の SQL ステートメントでこれを確認できます。

mysql> テーブル t を作成します (

    -> INT AUTO_INCREMENT 主キー

    -> );


mysql> t 値 (2147483647) に挿入します。

クエリは正常、1 行が影響を受けました (0.01 秒)


mysql> t 値に INSERT INTO (NULL);

エラー 1062 (23000): キー 't.PRIMARY' のエントリ '2147483647' が重複しています

INT 上限に達すると、自動インクリメント挿入が再度実行されるときに重複エラーが報告され、MySQL データベースによって自動的に 1 にリセットされないことがわかります。
特別な注意が必要な 2 番目の問題は、MySQL 8.0 より前では自動インクリメント値が永続的ではなく、自動インクリメント値にバックトラッキングの問題が発生する可能性があることです。

mysql> SELECT * FROM t;

+---+

| ア |

+---+

| 1 |

| 2 |

| 3 |

+---+

セット内の 3 行 (0.01 秒)


mysql> t から削除 WHERE a = 3;

クエリは正常、1 行が影響を受けました (0.02 秒)


mysql> SHOW CREATE TABLE t\G

************************** 1. 行 ****************************

       表: t

テーブルの作成: CREATE TABLE `t` (

  `a` int NOT NULL AUTO_INCREMENT、

  主キー (`a`)

) ENGINE=InnoDB AUTO_INCREMENT=4 デフォルト CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

セット内の1行（0.00秒）

自動インクリメント値が 3 のレコードを削除した後、次の自動インクリメント値がまだ 4 (AUTO_INCREMENT=4) であることがわかります。ここではエラーはなく、自動インクリメントはバックトラックされません。ただし、この時点でデータベースを再起動すると、テーブル t の自動インクリメントの開始値はデータベースの起動後に再び 3 になり、つまり自動インクリメント値がバックトラックされます。詳細は以下の通りです。

mysql> SHOW CREATE TABLE t\G

************************** 1. 行 ****************************

       表: t

テーブルの作成: CREATE TABLE `t` (

  `a` int NOT NULL AUTO_INCREMENT、

  主キー (`a`)

) エンジン=InnoDB AUTO_INCREMENT=3 デフォルト文字セット=utf8mb4 COLLATE=utf8mb4_general_ci

セット内の1行（0.00秒

この問題を完全に解決するには、次の 2 つの方法があります。

• MySQL バージョンをバージョン 8.0 にアップグレードすると、各テーブルの自動インクリメント値が永続的になります。
• データベースのバージョンをアップグレードできない場合は、コアビジネステーブルの主キーとして自動インクリメントデータ型を使用しないことを強くお勧めします。

実際、大規模なインターネット アーキテクチャを設計するプロセスでは、将来の分散アーキテクチャのスケーラビリティを向上させるために、整数型を主キーとして使用することは推奨されておらず、文字列型の方が推奨されています。

ファンドフィールド設計

ユーザー残高、ファンド口座残高、デジタルウォレット、お釣りなどのビジネス設計では、フィールドはすべて資本フィールドであるため、プログラマーは通常、フィールド選択として DECIMAL 型を使用します。これは、分単位の精度を実現できるためです (例: DECIMAL(8,2))。

テーブルユーザーの作成 (

  ユーザーID BIGINT AUTO_INCREMENT、

  お金 DECIMAL(8,2) NOT NULL,

  ......

）

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

金額フィールドの値の範囲が DECIMAL を使用して表現される場合、長さをどのように定義しますか? DECIMAL 型は可変長フィールドであるため、金額フィールドを定義する場合、DECIMAL(8,2) として定義するだけでは不十分です。これは、最大保管値が 999999.99 であり、数百万の資金を保管できることを意味します。

統計局のGDP金額フィールドは数十兆に達する可能性があるのに対し、ユーザーの金額は少なくとも数百億のフィールドに格納する必要があります。 DECIMAL 型を使用して定義を統一することは困難です。
もう 1 つ重要な点は、DECIMAL 型はバイナリ エンコード方式であり、その計算効率は整数型に比べてはるかに低いということです。したがって、金額関連のフィールドを保存するには BIG INT を使用することをお勧めします。

フィールドは別のストレージに保存されますが、それでも BIG INT はギガバイト単位の量を保存できます。ここで、1兆 = 1兆です。

これの利点は、金額に関連するすべてのフィールドが 8 バイトを占める固定長フィールドであり、ストレージ効率が非常に高いことです。もう 1 つのポイントは、直接整数計算を行う方が効率的であるということです。
データベース設計では、固定長ストレージの方がパフォーマンスが優れているため、固定長ストレージを重視していることに注意してください。

レコードがデータベースにどのように保存されるかを見てみましょう。おおよそ次のようになります。

更新が発生した場合、レコード 1 の元のスペースは、更新後のレコード 1 のストレージ スペースを収容できません。そのため、データベースはレコード 1 を削除済みとしてマークし、次のようにレコード 1 用の新しいスペースを探します。

上図の*レコード 1 は、元々レコード 1 が占めていたスペースを示しており、このスペースは断片化されたスペースとなり、テーブル スペースを手動でデフラグしない限り使用できなくなります。

では、BIG INT を使用して金額フィールドを保存する場合、小数点でデータをどのように表現するのでしょうか?実際、この部分はフロントエンドで完全に処理および表示できます。データベース自体はポイント単位で保存するだけで済みます。

これで、MySQL 数値型自動インクリメントの落とし穴に関するこの記事は終了です。MySQL 数値型自動インクリメントに関するより関連性の高いコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。