MySQL が主キーとして uuid を使用できない理由について話しましょう

この記事では、MySQL が主キーとして uuid を使用できないという関連情報を提供します。MySQL は公式に、uuid や非連続および非繰り返しのスノーフレーク ID を使用しないことを推奨していますが、連続的に自己増加する主キー ID を推奨しています。公式の推奨事項auto_increment であるのに、なぜ uuid の使用が推奨されないのでしょうか? 皆さんのお役に立てれば幸いです。

まえがき

mysql でテーブルを設計する場合、mysql は公式に uuid またはContinuous を使用しないことを推奨しています。非反復スノーフレーク ID (長い形状で一意の、単一マシンの増分) が推奨されますが、継続的に自己増加する主キー ID が推奨されます。公式の推奨事項は auto_increment ですが、uuid の使用が推奨されないのはなぜですか? uuidを使用するデメリットは?

#1. MySQL とプログラムの例

##1.1. この問題を説明するために、まず 3 つのテーブルを作成しましょう

これらはそれぞれ user_auto_key、user_uuid、user_random_key で、自動的に増加する主キーを表します。uuid は主キーとして使用され、

ランダム キーは主キーとして使用され、残りは完全に保持されます。変更なし。

によると、制御変数メソッドでは、異なる戦略を使用して各テーブルの主キーのみを生成し、他のフィールドはまったく同じで、テーブルの挿入速度とクエリ速度をテストします。 :

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

#1.2. 理論だけでは十分ではなく、プログラムに移動し、Spring の jdbcTemplate を使用して実装します。追加の検査テスト:

技術フレームワーク: springboot jdbcTemplate junit hutool、プログラム 原理は、独自のテスト データベースに接続し、同じ環境で同じ量のデータを書き込み、挿入時間を分析することです。最も現実的な効果を実現するために、すべてのデータはランダムに生成されます (名前、メールアドレス、住所など)。

package com.wyq.mysqldemo;
import cn.hutool.core.collection.CollectionUtil;
import com.wyq.mysqldemo.databaseobject.UserKeyAuto;
import com.wyq.mysqldemo.databaseobject.UserKeyRandom;
import com.wyq.mysqldemo.databaseobject.UserKeyUUID;
import com.wyq.mysqldemo.diffkeytest.AutoKeyTableService;
import com.wyq.mysqldemo.diffkeytest.RandomKeyTableService;
import com.wyq.mysqldemo.diffkeytest.UUIDKeyTableService;
import com.wyq.mysqldemo.util.JdbcTemplateService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.util.StopWatch;
import java.util.List;
@SpringBootTest
class MysqlDemoApplicationTests {
    @Autowired
    private JdbcTemplateService jdbcTemplateService;
    @Autowired
    private AutoKeyTableService autoKeyTableService;
    @Autowired
    private UUIDKeyTableService uuidKeyTableService;
    @Autowired
    private RandomKeyTableService randomKeyTableService;
    @Test
    void testDBTime() {
        StopWatch stopwatch = new StopWatch("执行sql时间消耗");
        /**
         * auto_increment key任务
         */
        final String insertSql = "INSERT INTO user_key_auto(user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?)";
        List<UserKeyAuto> insertData = autoKeyTableService.getInsertData();
        stopwatch.start("自动生成key表任务开始");
        long start1 = System.currentTimeMillis();
        if (CollectionUtil.isNotEmpty(insertData)) {
            boolean insertResult = jdbcTemplateService.insert(insertSql, insertData, false);
            System.out.println(insertResult);
        }
        long end1 = System.currentTimeMillis();
        System.out.println("auto key消耗的时间:" + (end1 - start1));
        stopwatch.stop();
        /**
         * uudID的key
         */
        final String insertSql2 = "INSERT INTO user_uuid(id,user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?,?)";
        List<UserKeyUUID> insertData2 = uuidKeyTableService.getInsertData();
        stopwatch.start("UUID的key表任务开始");
        long begin = System.currentTimeMillis();
        if (CollectionUtil.isNotEmpty(insertData)) {
            boolean insertResult = jdbcTemplateService.insert(insertSql2, insertData2, true);
            System.out.println(insertResult);
        }
        long over = System.currentTimeMillis();
        System.out.println("UUID key消耗的时间:" + (over - begin));
        stopwatch.stop();
        /**
         * 随机的long值key
         */
        final String insertSql3 = "INSERT INTO user_random_key(id,user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?,?)";
        List<UserKeyRandom> insertData3 = randomKeyTableService.getInsertData();
        stopwatch.start("随机的long值key表任务开始");
        Long start = System.currentTimeMillis();
        if (CollectionUtil.isNotEmpty(insertData)) {
            boolean insertResult = jdbcTemplateService.insert(insertSql3, insertData3, true);
            System.out.println(insertResult);
        }
        Long end = System.currentTimeMillis();
        System.out.println("随机key任务消耗时间:" + (end - start));
        stopwatch.stop();
        String result = stopwatch.prettyPrint();
        System.out.println(result);
    }

1.3. プログラムの作成結果

次のことがわかります。 in データ量が 100W 程度になると uuid の挿入効率が底をつき、その後のシーケンスで 130W のデータが追加されると、uudi の時間は再び急落します。

時間使用量の全体的な効率ランキングは、auto_key>random_key>uuid であり、uuid の効率が最も低く、データ量が多い場合、効率は急激に低下します。では、なぜこのようなことが起こるのでしょうか?疑問を持ちながら、この問題について議論してみましょう。

2. uuid と自動インクリメント id

を使用したインデックス構造の比較 2.1.自動インクリメント ID の内部構造を使用します

#自動インクリメント主キーの値は連続しているため、Innodb は各レコードを記録 。ページの最大 FILL FACTOR に達すると (InnoDB のデフォルトの最大 FILL FACTOR はページ サイズの 15/16 で、スペースの 1/16 は将来の変更のために残されます):

①次のレコード新しいページに書き込むとき、この順序でデータがロードされると、主キー ページはほぼ連続したレコードで埋められ、ページの最大フィル レートが増加し、ページの無駄はなくなります

② 新しく挿入された行は、元の最大データ行の次の行に必ず存在します。MySQL の位置指定とアドレス指定は非常に高速であり、新しい行の位置を計算するための余分な消費はありません。

#③ページ分割と断片化を減らす 生成

#2.2. uuid のインデックス内部構造を使用する

uuid は関係ないため順次自動インクリメント ID 原則として、新しい行の値は必ずしも前の主キーの値より大きいとは限りません。そのため、innodb は常に新しい行をインデックスの末尾に挿入できるわけではありませんが、新しい行を見つける必要があります。新しい行に適切な位置を設定して、新しいスペースを割り当てます。

このプロセスには多くの追加操作が必要ですが、データが乱雑であるとデータが分散し、次のような問題が発生します。ディスク上で更新されてキャッシュから削除されたか、キャッシュにロードされていない場合、InnoDB はターゲット ページを挿入する前にディスクからターゲット ページを見つけてメモリに読み取る必要があり、これにより大量のランダム IO

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

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

インデックス作成後 (デフォルトのインデックス)、ランダムな値 (uuid とスノーフレーク ID) をクラスターにロードします。 innodb のタイプ)、場合によっては、OPTIMEIZE TABLE を実行してテーブルを再構築し、ページ埋め込みを最適化する必要がありますが、これにはある程度の時間がかかります。

#結論: innodb を使用する場合は、主キーの増加順にできるだけ多くを挿入し、単調増加するクラスタリング キー値を使用して新しい行を挿入するようにしてください

2.3. 自己増加 ID を使用するデメリット

では、自己増加 ID を使用することにまったくデメリットはないのでしょうか。いいえ、自己増加 ID には次の問題もあります。

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

② 同時負荷が高い場合、innodb は主キーに従って挿入するときに明らかなロック競合を引き起こします。主キーの上限は競合のホット スポットになります。ここでは、同時挿入によりギャップ ロックの競合が発生します。

③Auto_Increment ロック メカニズムにより、自動インクリメント ロックの取得が発生し、パフォーマンスがある程度低下します。

添付ファイル: Auto_increment ロックの競合の問題 (次の場合)改善したい innodb_autoinc_lock_mode の設定のチューニング

3. 概要

このブログは、最初に質問をすることから始まり、テーブルを作成し、また、jdbcTemplate を使用してさまざまなテストを行います。ID 生成戦略は、大量のデータのデータ挿入でうまく機能し、インデックス構造における ID のさまざまなメカニズムと mysql の長所と短所を分析し、uuid とランダムでない理由を詳しく説明します。 ID を繰り返すとデータ挿入時にパフォーマンスが低下するという問題について詳しく説明しました。

実際の開発では、mysql の公式推奨に従い、自己増加 ID を使用するのがベストですが、mysql は奥が深く、内部的には最適化すべき点が多く、学ぶべき点がたくさんあります。

推奨学習:

mysql ビデオ チュートリアル

以上がMySQL が主キーとして uuid を使用できない理由について話しましょうの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。