MySQL が uuid または snowflake id を主キーとして使用することを推奨しない理由の詳細な分析

前書き: MySQL でテーブルを設計する場合、MySQL では UUID や非連続かつ非繰り返しのスノーフレーク ID (長くて一意) の使用は推奨されておらず、連続した自己増分主キー ID の使用が推奨されています。公式の推奨事項は auto_increment です。では、なぜ UUID は推奨されないのでしょうか。UUID を使用することのデメリットは何でしょうか。このブログでは、この問題を分析し、内部的な原因を探ります。

1: MySQLとプログラム例

1.1: この問題を説明するために、まず、自動的に増加する主キー、主キーとしての uuid、主キーとしてのランダム キーを表す user_auto_key、user_uuid、および user_random_key という 3 つのテーブルを作成します。他のテーブルはまったく変更しません。制御変数方式に従って、異なる戦略を使用して各テーブルの主キーのみを生成し、他のフィールドはまったく同じにして、テーブルの挿入速度とクエリ速度をテストします。

注: ここでのランダムキーは、実際にはスノーフレークアルゴリズムによって計算されたIDを指し、連続的でも繰り返しでも不規則でもありません。18ビット長の値の文字列です。

ID は自動的にテーブルを生成します:

ユーザー UUID テーブル

ランダム主キーテーブル:

1.2: 理論だけでは不十分です。プログラムに直接進み、Spring の jdbcTemplate を使用して増分テストを実装してみましょう。

技術的フレームワーク：springboot+jdbcTemplate+junit+hutool。プログラムの原理は、独自のテストデータベースに接続し、同じ環境で同じ量のデータを書き込んで挿入時間を分析してその効率を合成することです。最も現実的な効果を実現するために、名前、メールアドレス、住所など、すべてのデータはランダムに生成されます。プログラムはgiteeからアップロードされており、アドレスは記事の下部にあります。

パッケージ com.wyq.mysqldemo;
cn.hutool.core.collection.CollectionUtil をインポートします。
com.wyq.mysqldemo.databaseobject.UserKeyAuto をインポートします。
com.wyq.mysqldemo.databaseobject.UserKeyRandom をインポートします。
com.wyq.mysqldemo.databaseobject.UserKeyUUID をインポートします。
com.wyq.mysqldemo.diffkeytest.AutoKeyTableService をインポートします。
com.wyq.mysqldemo.diffkeytest.RandomKeyTableService をインポートします。
com.wyq.mysqldemo.diffkeytest.UUIDKeyTableService をインポートします。
com.wyq.mysqldemo.util.JdbcTemplateService をインポートします。
org.junit.jupiter.api.Test をインポートします。
org.springframework.beans.factory.annotation.Autowired をインポートします。
org.springframework.boot.test.context.SpringBootTest をインポートします。
org.springframework.util.StopWatch をインポートします。
java.util.List をインポートします。
@SpringBootテスト
クラスMysqlDemoApplicationTests {

  オートワイヤード
  プライベート JdbcTemplateService jdbcTemplateService;

  オートワイヤード
  プライベート AutoKeyTableService autoKeyTableService;

  オートワイヤード
  プライベート UUIDKeyTableService uuidKeyTableService;

  オートワイヤード
  プライベート RandomKeyTableService ランダムキーテーブルサービス;


  @テスト
  void testDBTime() {

    StopWatch stopwatch = new StopWatch("SQL実行時間消費量");


    /**
     * auto_increment キータスク */
    最終的な文字列 insertSql = "INSERT INTO user_key_auto(user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?,?)";

    リスト<UserKeyAuto> insertData = autoKeyTableService.getInsertData();
    stopwatch.start("キーテーブルの自動生成タスクが開始されます");
    長いstart1 = System.currentTimeMillis();
    CollectionUtil.isNotEmpty(挿入データ)の場合{
      ブール値の insertResult = jdbcTemplateService.insert(insertSql、insertData、false);
      System.out.println(挿入結果);
    }
    長い end1 = System.currentTimeMillis();
    System.out.println("自動キーによって消費された時間: " + (end1 - start1));

    ストップウォッチ.stop();


    /**
     * uudIDキー
     */
    最終的な文字列 insertSql2 = "INSERT INTO user_uuid(id,user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?,?,?,?)";

    リスト<UserKeyUUID> insertData2 = uuidKeyTableService.getInsertData();
    stopwatch.start("UUID キー テーブル タスクが開始されます");
    長いbegin = System.currentTimeMillis();
    CollectionUtil.isNotEmpty(挿入データ)の場合{
      ブール値の insertResult = jdbcTemplateService.insert(insertSql2、insertData2、true);
      System.out.println(挿入結果);
    }
    長いオーバー = System.currentTimeMillis();
    System.out.println("UUID キーによって消費された時間: " + (over - begin));

    ストップウォッチ.stop();


    /**
     * ランダムな長い値キー
     */
    最終的な文字列 insertSql3 = "INSERT INTO user_random_key(id,user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?,?,?,?)";
    リスト<UserKeyRandom> insertData3 = randomKeyTableService.getInsertData();
    stopwatch.start("ランダムな長い値キーテーブルタスクが開始されます");
    ロングスタート = System.currentTimeMillis();
    CollectionUtil.isNotEmpty(挿入データ)の場合{
      ブール値の insertResult = jdbcTemplateService.insert(insertSql3、insertData3、true);
      System.out.println(挿入結果);
    }
    ロングエンド = System.currentTimeMillis();
    System.out.println("ランダム キー タスクの消費時間: " + (end - start));
    ストップウォッチ.stop();


    文字列結果 = stopwatch.prettyPrint();
    System.out.println(結果);
  }

1.3: プログラム書き込み結果

user_key_auto 書き込み結果:

User_random_key 書き込み結果:

user_uuid テーブルの書き込み結果:

1.4: 効率テスト結果

既存のデータ量が 130 万の場合、10 万個のデータを挿入して結果がどうなるかをテストしてみましょう。

データ量が約 100 万のときに uuid の挿入効率が最も低く、後続のシーケンスで 130 万のデータが追加されると uuid の時間が急激に低下することがわかります。全体的な効率のランキングは、時間の使用に基づいて、auto_key>random_key>uuid です。UUID は最も効率が低く、データ量が多いと効率が急激に低下します。では、なぜこのような現象が起こるのでしょうか?疑問を抱きながら、この問題を検討してみましょう。

2: uuidと自動増分IDを使用したインデックス構造の比較

2.1: 自動増分IDの内部構造の使用

自動インクリメント主キーの値は連続しているため、Innodb は各レコードをレコードの後ろに格納します。ページの最大フィル ファクタに達した場合 (InnoDB のデフォルトの最大フィル ファクタはページ サイズの 15/16 で、1/16 のスペースが将来の変更用に予約されます)。

① 次のレコードは新しいページに書き込まれます。この順序でデータがロードされると、主キーページはほぼ連続したレコードで埋められるため、ページの最大充填率が向上し、ページの無駄が回避されます。

②新しく挿入された行は、元の最大行の下に配置されます。MySQL は行をすばやく見つけてアドレス指定し、新しい行の位置を計算するために余分な時間を費やしません。

③ページの分割と断片化を減らす

2.2: uuidを使用したインデックスの内部構造

UUID は順次自動増分 ID と比べて完全に不規則であるため、新しい行の値は必ずしも前の主キーの値よりも大きい必要はなく、InnoDB は常にインデックスの末尾に新しい行を挿入できるわけではありません。代わりに、新しい行に適した新しい位置を見つけて、新しいスペースを割り当てる必要があります。このプロセスには多くの追加操作が必要です。データの順序が崩れると、データが分散して分布し、次のような問題が発生します。

①: 書き込む対象ページがディスクにフラッシュされてキャッシュから削除されているか、まだキャッシュにロードされていない可能性があります。InnoDBは挿入前にディスクから対象ページを見つけてメモリに読み込む必要があるため、大量のランダムIOが発生します。

②: 書き込みは順序どおりに行われないため、InnoDB は新しい行にスペースを割り当てるために頻繁にページを分割する必要があります。ページ分割により大量のデータが移動され、1 回の挿入で少なくとも 3 ページを変更する必要があります。

③: 頻繁なページ分割により、ページがまばらになり、不規則に埋められ、最終的にデータの断片化につながります。

ランダムな値 (uuid と snowflake id) をクラスター化インデックス (innodb のデフォルトのインデックス タイプ) にロードした後、テーブルを再構築してページの入力を最適化するために OPTIMEIZE TABLE を実行する必要がある場合があります。これには時間がかかります。

結論: InnoDB を使用する場合は、可能な限り主キーの自動増分順に​​行を挿入し、単調に増加するクラスタリング キー値を使用して可能な限り新しい行を挿入する必要があります。

2.3: 自動増分IDを使用するデメリット

では、自動増分 ID を使用しても問題ないのでしょうか?いいえ、自己増分 ID には次のような問題もあります。

①：データベースをクロールすると、データベースの自己増加IDに基づいてビジネスの成長情報を取得でき、ビジネス状況を簡単に分析できます。

②: 同時負荷が高い場合、InnoDB は主キーによる挿入時に明らかなロック競合を引き起こします。すべての挿入がここで行われるため、主キーの上限は競合のホットスポットになります。同時挿入はギャップロック競合を引き起こします。

③: Auto_Incrementロック機構により、自動インクリメントロックが奪われ、パフォーマンスの低下を招く。

付録: Auto_incrementのロック競合問題を改善するには、innodb_autoinc_lock_modeの設定を調整する必要があります。

3: まとめ

このブログは、冒頭で提起された疑問から始まり、テーブルを作成し、jdbcTemplate を使用して、大量のデータを挿入する際のさまざまな ID 生成戦略のパフォーマンスをテストします。次に、MySQL インデックス構造のさまざまな ID メカニズムとその長所と短所を分析し、UUID とランダムな非繰り返し ID がデータ挿入時にパフォーマンスの低下を引き起こす理由を詳しく説明し、この問題について詳しく説明します。実際の開発では、MySQL の公式推奨に従って自動インクリメント ID を使用するのが最適です。MySQL は奥が深く、内部には最適化する価値のあるポイントがまだ多くあり、学ぶ必要があります。

付録: このブログのデモアドレス: https://gitee.com/Yrion/mysqlIdDemo

これで、MySQL が UUID または Snowflake ID を主キーとして使用することを推奨しない理由についての詳細な分析に関するこの記事は終了です。MySQL UUID または Snowflake ID を主キーとして使用することに関するより関連性の高いコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM を応援していただければ幸いです。