Ich bitte um Rat zu einem Problem mit dem Gruppierungsindex einer MySQL-Tabelle

Question

Ich arbeite an einem Website-Programm und die allgemeinen Anforderungen sind wie folgt.
Benutzer sind in fünf Ebenen unterteilt: 1-5. Je größer die Anzahl, desto höher die Autorität.

Ich habe eine Menge Inhalte. Je höher die Ebene, desto mehr Inhalte sind für Benutzer sichtbar.
Zum Beispiel gibt es Inhalte: A, B, C, D, E,
Sichtbar für Benutzergruppe 1: A
Sichtbar für Benutzergruppe 2: A, B
…………
Sichtbar für Benutzergruppe 5: A, B, C, D, E
Wenn Sie diese Funktion implementieren möchten, wie sollten Sie einen Datenbankindex erstellen?

Ein Freund sagte mir, ich solle der Inhaltstabelle (Thementabelle) eine Spalte „Gruppe“ hinzufügen,
die sichtbaren Benutzerebenen 1–5 schreiben und dann einen group_tid的联合索引。
然后查询tid<100周围文章（例如当前用户组为3）时的语句就是：
SELECT * FROM topic WHERE group>=3 AND tid<100 LIMIT 10;
可实际发现这种索引是先将group>3的所有数据读出来，再进行选择查询。
假如有100万条数据，有50万个groupgemeinsamen Index erstellen.

Fragen Sie dann die Artikel rund um tid<100 ab (die aktuelle Benutzergruppe ist beispielsweise 3):

SELECT * FROM topic WHERE group>=3 AND tid<100 LIMIT 10;
Es kann tatsächlich festgestellt werden, dass diese Art von Index zunächst alle Daten von
>3 ausliest und dann eine Auswahlabfrage durchführt.

Wenn es 1 Million Daten und 500.000

>3 gibt, werden bei der Ausführung dieser Anweisung 500.000 Typen herausgefiltert, was äußerst ineffizient ist.

Es scheint, dass einspaltige Indizes nur für Einschränkungen wie group_tid=* gelten, nicht <
Daher möchte ich die Experten hier fragen: Hatten Sie jemals ähnliche Bedürfnisse? Wie erstellt man einen Index oder eine Tabelle richtig? Vielen Dank! Ergänzung 1:

Tatsächlich ändern wir die Frage, wie man < oder > verwendet.

Dies ist eine logische Frage. Der aktuelle group进行了范围限制，后面的tid还是在group的基础上按顺序排列的。
如果我想知道group>1且tid<6的这种情况，不得不先把groupIndex ähnelt dem Bild unten, nachdem er erstellt wurde:

Auch wenn ich den Bereich von
einschränke, werden die folgenden tid immer noch in der Reihenfolge basierend auf group=1,tid=A
group=2,tid=A
group=3,tid=A
这样在内容读取时直接请求WHERE group angeordnet.
Wenn ich die Situation wissen möchte, in der

>1 und tid<6 sind, muss ich zuerst alle 🎜2/3 auslesen und dann filtern. 🎜Es scheint, dass die einzige Lösung darin besteht, die Tischstruktur neu zu planen. Haben Sie ähnliche Erfahrungen? 🎜 🎜Ergänzung 2: 🎜Ich habe gerade eine hilfreiche Antwort von einem begeisterten Freund erhalten, der sagte, dass er diese Situation schon einmal erlebt habe. 🎜Die Lösung besteht darin, den Veröffentlichungsmechanismus zu ändern und qualifizierte Beiträge auf jeder Ebene zu veröffentlichen. 🎜Wenn beispielsweise die Ebene des Inhalts A 3 ist, müssen beim Posten drei Datenzeilen gleichzeitig erstellt werden: 🎜🎜=1,tid=A🎜🎜=2, tid=A 🎜🎜=3,tid=A🎜Auf diese Weise können qualifizierte Inhalte gelesen werden, indem beim Lesen des Inhalts direkt WHERE 🎜=* abgefragt wird. 🎜Aber diese Methode erfordert das Hinzufügen einer großen Menge verwandter Daten und kann sogar zu Duplikaten führen. Gibt es eine andere Lösung? 🎜

Answer 1

其实你的思路已经很对了。

tid上建立索引，根据group分表。

如果group >=3的组，在程序中动态组合sql如下：

select * from group3 where tid < 100
union all 
select * from group4 where tid < 100
union all 
select * from group5 where tid < 100

以上索引生效，逻辑可用。

Answer 2

首先说明一下，在 Innodb 中，索引生不生效跟你使用 < 或 > 没有必然关系。也不是说用 = 就一定能用上索引。当全表查的性能要高于索引检索查询时，MySQL 会智能的放弃索引，选择全表查询。

如图：

回到你的问题，如果某个索引，如 tid<100 检索出的范围相对较小时，索引是能够用上的。

如果这两个索引的结果集都很大的话，是否考虑添加其他过滤条件，比如根据创建时间只查近一个月的内容。

分页问题也可以通过主键ID来再次过滤。

Answer 3

首先，需要明白以下几点：

1. 对于一个表的查询，每次最多只使用一个索引

2. 对于联合索引，从左往右依次进行数据的筛选，所以如果第一个筛选条件针对了大于或者小于的话，第二个筛选条件由于在整个可选区域内没有确切的索引范围，所以会将第一个筛选条件筛除来的数据都跑一遍

3. B-Tree索引的结构类似于树形结构，见下图，联合索引从左往右的检索，起始就是这个结构从上往下查找分支的过程

4. 索引的机制，简单说来就是创建一个值到数据项的对应表，这样可以快速的从某一字段某个值定位到某一行，省却了跑整个表去找对应行的操作，所以比较快

B-Tree索引的结构：

然后回到你的问题上，如果要大幅度提高效率，那么联合索引的第一步就需要大幅度减少可以用于后续筛选的数据量，所以如果你要查tid < 100的话，先用tid筛选才能够大幅度减少后续的B-Tree索引分支，所以如果要用联合索引，则应该是(tid, group)。

Answer 4

group条件的过滤性很差，单独建立索引意义不大。

根据你描述的场景，只要tid的值不是太大（几千的数量级），针对tid建立索引就可以了。
如果还担心tid条件过滤后的数据量大，可以创建tid,group的组合索引。

Answer 5

首先非常感谢各位对我问题的关注和回答！！
问题解决之后针对boxsnake的建议有一个思考，在这里发一下。
group_tid这种索引方式除了解决读取之外还能解决分页问题，
例如我每页文章数量是10，用户级别为3，那么读取时分别从group1、group2、group3中，
按范围tid<100各取10篇，即便某group中没有符合条件的结果，几项加起来也可以覆盖全。

但如果用tid_group这种索引方式来读取，如果需要group<=3的情况，我不知道该取多少篇文章。
比方说取10篇，tid90-tid99，如果他们的group都是4，那么就无法取出符合条件的数值。
而tid_group在限定group之前又必须对tid进行限定，所以就没法使用了。