MySQL ツリー構造テーブルの設計と最適化に関する簡単な説明

序文

多くの管理・オフィスシステムでは、ツリー構造がいたるところで見られます。たとえば、「部門」や「機関」を使用したことがある場合、ページ上の最終的な表示効果は階層構造であることがわかるはずです。次の図は、部門のツリー構造表示をランダムにリストしたものです。

設計上の考慮事項

1. テーブル構造の設計

開発とテーブル構造設計の経験が少しある学生にとって、このようなテーブルを設計するのは簡単なはずです。要件を満たすには、depart テーブルに pid/フィールドを追加するだけです。次の表を参照してください。

テーブル「depart」を作成します（
  `depart_id` varchar(32) NOT NULL COMMENT '部門ID',
  `pid` varchar(32) NOT NULL DEFAULT '0' COMMENT '組織親ID',
  `name` varchar(64) NOT NULL COMMENT '部門名',
  `description` varchar(512) DEFAULT NULL COMMENT '部門の説明',
  `code` varchar(64) DEFAULT NULL COMMENT '部門コード',
  主キー (`depart_id`)
)ENGINE=InnoDB デフォルト文字セット=utf8;

2. ビジネスデザイン

上図は一般的なツリー構造図であり、ほとんどのビジネス シナリオに適しています。たとえば、「部門」が独立して存在しない場合、部門に関連する業務には主に次の点が含まれます。

• 部門が独立して存在し、部門に直接関連する業務が存在する可能性があるかどうか。たとえば、ユーザーは部門に関連付けられる可能性がある。
• 部門エフェクトを表示し、部門ツリーリストを読み込む場合、動的に読み込まれますか?それとも一回限りの返品ですか?
• 部門の追加、削除、変更、照会に関する業務
• 部門のビジネスを彼に関連付けます。つまり、部門は関連付けられたビジネスオブジェクトです。

3. パフォーマンスに関する考慮事項

• フルロードはお勧めできません。これは最後の手段です。階層が深くなりすぎてデータの量が多すぎると、クエリのパフォーマンスが悪夢になります。
• 動的ロードの方が効果は優れています。サーバーのオーバーヘッドと負荷は、フルロードよりはるかに小さくなります。ただし、動的ロードの問題も明らかです。レベルが深くなると、ユーザーの操作エクスペリエンスは良くありません。
• 階層構造機能は、データインポートのコーディングの複雑さを増大させる。つまり、Excelインポート機能である。

最初の点に関して、2 番目の点にいくつか追加したいと思います。フル ローディングとダイナミック ローディングの両方を実装できます。私が経験したプロジェクトや製品では、それらを見てきました。フル ローディングとダイナミック ローディングの異なる設計も、対応する結果をもたらすため、実際には製品の設計と顧客の要件によって異なります。

たとえば、フルロードの利点は、データが一度にページに返され、レンダリング後にページがキャッシュされるため、後で再度ロードするときに非常に高速であることです。同時に、以下のような検索は、インターフェイスとのやり取りを必要としないため、非常に効率的です。

しかし、問題も発生します。部門データは静的ではなく、追加、削除、変更操作が頻繁に行われます。データ全体をロードするように設計すると、初めてクエリを実行するときに、データ量が非常に大きく、階層が非常に深い場合、ページで部門に関連付けられたユーザーデータもレンダリングする必要がある場合、サーバーに大きな負荷がかかります。少し経験を積んだ学生であれば、このサーバーの戻りデータ構造を大まかに考えることができるはずです。

以下は予備的な実装案です

関数 (currentDepart_id) {
  
  1. 現在の部門 DB を検索します...

  2. 現在の部門のサブ部門 DB を検索します...
  
  3. 現在の部門のサブ部門リストに基づいて、トラバース、再帰クエリを実行し、返されたデータを DB にパッケージ化します...

}

上記のコード実装から、データ量が増えると、クエリがパフォーマンスのボトルネックになると予測されることがわかります。エディターのプロジェクトの開発では、同様のテストが行​​われています。レベルは3つあり、各レベルには1,000のデータがあります（部門に関連付けられたユーザーのデータロードは計算されません）。4コア16Gサーバー（通常のCPUパフォーマンス）では、完全なデータロードが完了するまでに平均約3秒かかります。Bサイド製品の場合、この設計にこの遅延を加えても、ユーザーは許容できます（1,000部門、このデータ量は比較的大きいです）

上で分析したように、フルロードのパフォーマンスボトルネックはデータベース IO にあります。クエリを実行するときに、最上位ノードまたは特定のノードから開始すると、データ量が多くなるほど、レベルが深くなり、クエリの数が増え、IO オーバーヘッドが大きくなることを想像してください。

解決策は何ですか?実際には、参考として使用できる経験が 2 つあります。

• 適切なキャッシュストレージ構造を設計する
• テーブル構造を改善する

1 点目については、誰でも簡単に思いつくことですが、より合理的に設計するにはどうすればよいでしょうか。次の図を例にとると、非リーフノードをキーとして、リーフノードの下のコレクションを値として使用し、すべての値を Redis コレクションに格納することが考えられます。この検討は、実際のビジネスとユーザーの需要の検証から生まれたもので、つまり、本当に意味のある部門または機関のデータはリーフノードに分散されます。

このようにして、コーディング実装は次のように変換できます。

1. 部門に新しい関数を追加します。add(params){
    
    1. DBへ出発......
    2. 現在の分岐のレベル、それがリーフノードであるかどうか（リーフノードになるかどうか）を判断する

      if(リーフノード){
        3. 親ノードIDを見つけて、Redisでキーをクエリする

        4. 親キーに対応するキャッシュコレクションを取り出し、新しく追加されたpart_idを追加します。

      } それ以外 {
        5. 新しいキー、つまり新しいキャッシュの空のコレクションを作成し、後続のデータが追加される（または作成されない）のを待ちます。
      }

}


2. 部門を削除する functiondelete(params){
    
    1.Depart独自の削除DB...

    2. 現在の部門の下にサブセット部門がある場合、サブ部門も一緒に（自社製品事業と組み合わせて）削除する必要がありますか？
        DB......

        3. 2 番目のステップが確立されていると仮定すると、現在の部門ノードによって作成されたキーも使用し、キーに設定されたリストを取り出し、Redis 操作をまとめて削除して、2 番目のステップですべての非リーフ ノード セットを取得し、キーに組み立て、ループしてキーを削除します (メモリ操作、パフォーマンスは問題ではなく、非同期操作も実行できます)

}

Redis と組み合わせたフルロードは、パフォーマンスのボトルネックを打破するための重要なステップです。ただし、上記の実装では、コーディングの複雑さが確かに増加し、開発者に対するコーディング要件が比較的高くなっています。ただし、この実装後、クエリのパフォーマンスが大幅に向上すると言えます。

クエリパフォーマンスを最適化するための2番目の考慮事項は、テーブル構造の変換です。

多くの学生が、テーブル構造の変換がパフォーマンスにどの程度の影響を与えるのかという疑問を抱いています。信じられないかもしれませんが、データ ストレス テストをシミュレートする場合、変更された実装を使用せずに 5 レベルの部門を使用し、各部門に 1000 のデータがあり (各レベルの各部門のデータ量は 1000 なので、合計データ量を計算できます)、各部門は 500 人のユーザーに関連付けられており、このようなデータ量の最終的なパフォーマンスは約 5 分です。

データ量が増えた後、クエリの圧力が非常に大きいようです。修正された設計とテスト結果により、最終的に同じデータが平均 15 ～ 20 秒で表示されるようになり、10 倍以上の改善が見られました。おそらく、私が答えを出す前に、多くの学生がそれを使用したことがあるものの、その魔法を実際に体験したことはありません。

この記事の冒頭のテーブル構造に基づいてパス フィールドを追加すると、変換されたテーブルは次のようになります。

テーブル「depart」を作成します（
  `depart_id` varchar(32) NOT NULL COMMENT '部門ID',
  `pid` varchar(32) NOT NULL DEFAULT '0' COMMENT '組織親ID',
  `name` varchar(64) NOT NULL COMMENT '部門名',
  `description` varchar(512) DEFAULT NULL COMMENT '部門の説明',
  `code` varchar(64) DEFAULT NULL COMMENT '部門コード',
  主キー (`depart_id`)、
  `path` varchar(128) NOT NULL COMMENT '部門パス',
)ENGINE=InnoDB デフォルト文字セット=utf8;

パス フィールドは非常に重要です。通常、第 1 レベルから始まり、各レベルには最大 10,000 の部門を収容できると想定されています。第 1 レベルのデータは、00001、00002、00003... などのようになります。第 2 レベルでは、部門 00002 の下に第 2 レベルの部門を追加すると、データは、00002/00001、00002/00002、00002/00003... などのようになります。

もっと深いレベルについては、私が例を挙げなくても、誰もが自分で以下の構造を挙げることができると思います。

これを実行する利点は何ですか?

MySQL が正規表現関数などをサポートしていることはわかっています。特定のレベルから始まるすべての階層データを一度にクエリしたいとします。パス フィールドがない場合はどうすればよいでしょうか。明らかに、それは前述のように再帰を通じて行われます。

ただし、パスフィールドを使用すると、MySQL の正規表現機能を直接使用できます。上記のデータを例にとると、次の 2 つの SQL ステートメントを通じて、第 1 レベル部門 (テスト) のデータのすべてのサブセットを一度に見つけることができます。このようにして、データベースとのやり取りの数を大幅に削減できます。

この実装は落とし穴に陥りやすく、実際の操作で問題が発生しやすいのはパスルールの生成です。通常、ユーザー向けのパスを生成するには、事前にカスタム関数をカスタマイズする必要があります。生成されたパスフィールドデータが正確である限り、この実装はクエリパフォーマンスの最適化という点で大きな進歩です。この方法は、エディターが作業している開発プロジェクトで使用されています。

関数generatePath(pid){
    
  1. pidはトップレベルですか? 2. 親部門の部門を取得します

  3. 親部門の下に追加する部門と同じレベルのすべてのパスフィールドをリストします 4. 手順 3 のパスの最大値を取得します 5. 手順 4 のパスの最大値に基づいて新しいパスを生成します

}

もう一つの難しい点は、パス フィールドの設計後、部門データを Excel にインポートするときに、このパスの処理がまだ比較的複雑な実装ポイントであり、誰もが考えなければならない点です。

以上の議論では、フルロード時の業務実装とコード設計の最適化、およびテーブル構造設計の最適化について説明しました。動的ロードの実装は、上記の 2 つのソリューションに基づいて少し参照するだけで比較的簡単に実装できます。

要約すると、階層構造を持つビジネス設計におけるベスト プラクティスの推奨事項は次のとおりです。

テーブル構造に関しては、パスフィールドデータロードを使用し、動的ロードを使用するようにしてください。部門（階層型業務）の変化があまりない場合は、キャッシュの導入を検討できます。具体的な実践方法については、上記の記事を参照してください。

これで、MySQL ツリー構造テーブルの設計と最適化に関するこの記事は終了です。MySQL ツリー構造テーブルの最適化に関するより関連性の高いコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。