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

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 をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • MYSQL の 3 つのツリー構造テーブル設計の長所と短所の簡単な分析と共有

<<:  fileReader 使用時の落とし穴と解決策

>>:  モバイルウェブサイトの開発に関するいくつかの結論

推薦する

JavaScript を使用して動的に生成されるテーブルの詳細な説明

*ページを作成する: 2つの入力ボックスとボタン*コードと手順/* 1. 入力行と列の値を取得する2...

MySQL パーティション関数の詳細な説明と例の分析

まず、データベース パーティショニングとは何でしょうか?以前、MySQL のテーブル パーティショニ...

MySQLの挿入文字化け問題を解決する方法

問題の説明: MySQL に中国語の文字を挿入する場合、または MySQL では中国語の文字が正常に...

リクエスト IP の最後のセグメントに基づいてトラフィックを分割するように Nginx を構成する方法

これは主に、場所パラメータのif判断の設定ジャンプです。迂回により、サーバーの負荷と圧力を軽減できま...

mysql 10進データ型変換の実装

最近、次のデータ型のデータベースに遭遇しました:decimal(14,4)発生した問題は次のとおりで...

MySQL での実行計画の explain コマンド例の詳細な説明

序文explain コマンドは、クエリ オプティマイザーがクエリの実行を決定した方法を確認する主な方...

MySQL パーティションテーブルの正しい使用方法

MySQL パーティションテーブルの概要数億、あるいは数十億ものレコードを格納するテーブルに遭遇する...

訪問者を惹きつけるウェブサイトコンテンツを作成する14の方法

ネットサーフィンをしていると、私の注意を引こうとする美しいグラフィックでいっぱいの Web サイトを...

docker と docker-compose による eureka の高可用性の実現の詳細な説明

最近、新しいプロジェクトでは springcloud と docker が使用されています。この 2...

Linux で開いているファイルが多すぎる問題を解決する方法

原因は、プロセスが特定の時点でシステム制限を超える数のファイルと通信リンクを開くことです。 システム...

MySQLのよくある間違い

NULL 値によると、MySQL の NULL 値は単にデータがないことを意味します。NULL 値は...

初心者がdockerにmysqlをインストールするときに遭遇するさまざまな問題

序文最近、パソコンのシャットダウンに時間がかかることが多く、強制的にシャットダウンするには電源ボタン...

js の Array.forEach でループを終了する方法の例

目次forEach() メソッドjs の Array.forEach のループから抜け出す方法解決:...

一般的な XHTML タグの使用方法の紹介

XHTML には多くのタグがありますが、頻繁に使用されるのはごくわずかであり、習得する必要があるのは...

画像をMySQLデータベースに保存し、フロントエンドページに表示するための実装コード

目次1. まず、pycharmを使用してDjangoプロジェクトを作成し、関連する環境を設定します。...