mysqlテーブルのグループ化インデックスの問題についてアドバイスを求める

Question

私は Web サイトのプログラムを作成しています。一般的な要件は次のとおりです。
ユーザーは1～5の5段階に分かれており、数字が大きいほど権限が高くなります。

大量のコンテンツがあります。レベルが高くなるほど、より多くのコンテンツがユーザーに表示されます。
たとえば、次のコンテンツがあります: A、B、C、D、E、
ユーザー グループ 1 に表示: A
ユーザー グループ 2 に表示: A、B
…………
ユーザー グループ 5 表示可能: A、B、C、D、E
この機能を実現したい場合、データベース インデックスをより適切に構築するにはどうすればよいでしょうか?

以前、友人から、コンテンツ (トピック) テーブルに列「グループ」を追加し、表示されるユーザー レベル 1 ～ 5 を書き込み、
group_tid の結合インデックスを作成するように言われました。 。 次に、周囲の記事
tid<100 件をクエリします (たとえば、現在のユーザー グループは 3 です)。ステートメントは次のとおりです: SELECT * FROM
topic WHERE group>=3 AND tid<100 LIMIT 10;実際には、この種のインデックスは
group> のすべてのデータを読み出すことがわかります。 3を選択してからお問い合わせください。 データが 100 万個あり、
group>3 が 500,000 個ある場合、このステートメントを実行すると 500,000 種類が除外され、非常に非効率です。

単一列インデックスは、

group=* などの制限にのみ適用され、< や > には適用されないようです。 そこで専門家の方にお聞きしたいのですが、同じようなニーズを感じたことはありますか？インデックスやテーブルを正しく作成するにはどうすればよいですか? ＃＃＃どうもありがとうございます！

補足 1:

実際、質問の変更は、2 つの mysql インデックスで < または > 制限を使用する方法です。

これは論理的な問題です。現在の
group_tid
インデックスは、確立されると次の図のようになります:

group

の範囲を制限しても、次の tid は group に基づいて順番に配置されます。 group
>1 と tid<6 の状況を知りたければ、最初に group2/3 をすべて読み上げる必要があります。再度フィルターをかけます。 テーブル構造を再計画することが唯一の解決策のようですが、同様の経験はありますか?
補足 2:

熱心な友人から、以前にもこの状況に遭遇したことがあるという有益な回答を受け取りました。

解決策は、公開メカニズムを変更し、適格な投稿を各レベルに公開することです。
たとえば、コンテンツ A のレベルが 3 の場合、投稿時に 3 つのデータ行を同時に作成する必要があります:

group
=1,tid= Agroup
=2,#​​##tid=Agroup=3,
tid=Aこのようにすると、内容を読み出す場合は、WHERE Group=* を直接リクエストすることで条件を満たす内容を読み出すことができます。
しかし、この方法では大量の関連データを追加する必要があり、重複が発生する可能性もあります。他に解決策はありますか?

Answer 1

実際、あなたの考えはすでに正しいです。

tid にインデックスを作成し、グループごとにテーブルを分割します。

グループ >= 3 グループの場合、次のようにプログラム内で SQL を動的に結合します:

リーリー

上記のインデックスは有効であり、ロジックが利用可能です。

Answer 2

まず最初に、Innodb ではインデックスが有効かどうかは < または > の使用とは関係がないことを説明させてください。 = を使用すると必ずインデックスが使用できるようになるというわけではありません。フル テーブル クエリのパフォーマンスがインデックス取得クエリのパフォーマンスよりも高い場合、MySQL はインテリジェントにインデックスを放棄し、フル テーブル クエリを選択します。

写真に示すように:

質問に戻りますが、tid<100 などのインデックスによって取得される範囲が比較的小さい場合、インデックスを使用できます。

これら 2 つのインデックスの結果セットが大きい場合は、作成時間に基づいて過去 1 か月のコンテンツのみを検索するなど、他のフィルター条件の追加を検討する必要があります。

ページネーションの問題は、主キー ID によって再度フィルタリングすることもできます。

Answer 3

まず、次の点を理解する必要があります:

1. テーブルに対するクエリの場合、毎回最大で 1 つのインデックスのみが使用されます

2. ジョイント インデックスの場合、データは左から右にフィルタリングされるため、最初のフィルタ条件がより大きいまたはより小さいことをターゲットにしている場合、2 番目のフィルタ条件はオプション領域全体で正確なインデックス範囲を持ちません。最初のフィルターで除外されたデータ

3. B-Tree インデックスの構造は、以下の図に示すように、ツリー構造に似ています。結合インデックスは、この構造の上から下に向かって検索されるプロセスです。

4. インデックスメカニズムは、値からデータ項目に対応するテーブルを作成するだけなので、特定のフィールドの特定の値を特定の行に素早く見つけることができ、対応するテーブル全体を実行して対応するテーブルを見つける必要がなくなります。行なので、クイック比較してください

B ツリー インデックス構造:

それでは質問に戻りますが、効率を大幅に向上させたい場合は、共同インデックス作成の最初のステップで、その後のスクリーニングに使用できるデータの量を大幅に削減する必要があるため、

を確認したい場合は。 tid < 100的话，先用tid筛选才能够大幅度减少后续的B-Tree索引分支，所以如果要用联合索引，则应该是(tid, group)

Answer 4

グループ条件のフィルタリングのパフォーマンスは非常に低く、インデックスを単独で作成することはほとんど意味がありません。

あなたが説明したシナリオによれば、tid の値が大きすぎない限り (数千のオーダー)、tid のインデックスを作成するだけで十分です。

tid 条件によってフィルタリングされた大量のデータがまだ心配な場合は、tid と group を組み合わせたインデックスを作成できます。

Answer 5

まず最初に、私の質問に集中して答えていただき、誠にありがとうございました！ ！
問題を解決した後、boxsnake の提案についていくつか考えがあるので、ここに投稿します。
group_tid このインデックス作成方法は、読み取りの問題を解決できるだけでなく、ページングの問題も解決できます。group_tid这种索引方式除了解决读取之外还能解决分页问题，
例如我每页文章数量是10，用户级别为3，那么读取时分别从group1、group2、group3中，
按范围tidたとえば、ページあたりの記事数が 10 で、ユーザー レベルが 3 の場合、読むと、グループ1、グループ2から、グループ3では、tid<100の範囲でそれぞれ10記事ずつ取得します。いくつかの項目を合計すると、すべてをカバーできます。

しかし、tid_group インデックス メソッドを使用して読み取る場合、group<=3 が必要な場合、いくつの記事を取得する必要があるかわかりません。 tid_group这种索引方式来读取，如果需要group<=3的情况，我不知道该取多少篇文章。
比方说取10篇，tid90-tid99，如果他们的group都是4，那么就无法取出符合条件的数值。
而tid_group在限定group之前又必须对tid例えば、tid90〜tid99までの10個の記事がある場合、そのグループがすべて4つだと条件を満たす値を取得できません。

そして、tid_group は group を制限する前に tid を制限する必要があるため、使用できません。 🎜