Demander des conseils sur un problème d'index de regroupement de tables MySQL

Question

Je travaille sur un programme de site Web et les exigences générales sont les suivantes.
Les utilisateurs sont divisés en cinq niveaux, de 1 à 5. Plus le nombre est grand, plus l'autorité est élevée.

J'ai beaucoup de contenu. Plus le niveau est élevé, plus le contenu est visible pour les utilisateurs.
Par exemple, il y a du contenu : A, B, C, D, E,
Visible pour le groupe d'utilisateurs 1 : A
Visible pour le groupe d'utilisateurs 2 : A, B
…………
Visible pour le groupe d'utilisateurs 5 : A, B, C , D, E
Si vous souhaitez implémenter cette fonction, comment devez-vous créer un index de base de données ?

Un ami m'a dit d'ajouter une colonne "groupe" au tableau de contenu (sujet),
écrire les niveaux d'utilisateurs visibles 1 à 5, puis de créer un group_tid的联合索引。
然后查询tid<100周围文章（例如当前用户组为3）时的语句就是：
SELECT * FROM topic WHERE group>=3 AND tid<100 LIMIT 10;
可实际发现这种索引是先将group>3的所有数据读出来，再进行选择查询。
假如有100万条数据，有50万个groupindex commun.

Ensuite, interrogez les articles autour de tid<100 (par exemple, le groupe d'utilisateurs actuel est 3) :

SELECT * FROM topic WHERE group>=3 AND tid<100 LIMIT 10;
On peut en fait constater que ce type d'index lit d'abord toutes les données de
>3, puis effectue une requête de sélection.

Supposons qu'il y ait 1 million de données et 500 000

>3 Lors de l'exécution de cette instruction, il est nécessaire de filtrer parmi 500 000 types, ce qui est extrêmement inefficace.

Il semble que les index à colonne unique ne s'appliquent qu'aux restrictions telles que group_tid=*, pas <
J'aimerais donc demander aux experts ici : avez-vous déjà eu des besoins similaires ? Comment créer correctement un index ou une table ? Merci beaucoup ! Supplément 1 :

En fait, changeons la question, c'est-à-dire comment utiliser les limites < ou >

C'est une question logique. L'index group进行了范围限制，后面的tid还是在group的基础上按顺序排列的。
如果我想知道group>1且tid<6的这种情况，不得不先把group actuel est similaire à l'image ci-dessous une fois établi :

Même si je limite la portée des
, les tid suivants sont toujours classés dans l'ordre en fonction de group=1,tid=A
group=2,tid=A
group=3,tid=A
这样在内容读取时直接请求WHERE group.
Si je veux connaître la situation où

>1 et tid<6, je dois d'abord lire tous les 🎜2/3, puis filtrer. 🎜Il semble que la seule solution soit de replanifier la structure de la table. Avez-vous une expérience similaire ? 🎜 🎜Supplément 2 : 🎜Je viens de recevoir une réponse utile d'un ami enthousiaste, disant qu'il avait déjà rencontré cette situation. 🎜La solution est de modifier le mécanisme de publication et de publier des posts qualifiés à chaque niveau. 🎜Par exemple, si le niveau de contenu A est de 3, alors trois lignes de données doivent être créées en même temps lors de la publication : 🎜🎜=1,tid=A🎜🎜=2, tid=A 🎜🎜=3,tid=A🎜De cette façon, le contenu qualifié peut être lu en demandant directement WHERE 🎜=* lors de la lecture du contenu. 🎜Mais cette méthode nécessite l'ajout d'une grande quantité de données associées et peut même provoquer une duplication. Existe-t-il une autre solution ? 🎜

Answer 1

En fait, votre idée est déjà bonne.

Créez un index sur tid et divisez les tableaux par groupe.

Si groupe >= 3 groupes, combinez dynamiquement SQL dans le programme comme suit :

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

L'index ci-dessus est efficace et la logique est disponible.

Answer 2

Tout d'abord, laissez-moi vous expliquer que dans Innodb, que l'index prenne effet ou non n'a rien à voir avec votre utilisation de < Cela ne signifie pas que l'utilisation de = vous permettra définitivement d'utiliser des index. Lorsque les performances d'une requête de table complète sont supérieures à celles d'une requête de récupération d'index, MySQL abandonnera intelligemment l'index et choisira une requête de table complète.

Comme le montre l'image :

De retour à votre question, si la plage récupérée par un index, tel que tid<100, est relativement petite, l'index peut être utilisé.

Si les ensembles de résultats de ces deux index sont volumineux, devriez-vous envisager d'ajouter d'autres conditions de filtrage, telles que la recherche uniquement de contenu au cours du mois dernier en fonction de l'heure de création.

Les problèmes de pagination peuvent également être à nouveau filtrés par ID de clé primaire.

Answer 3

Tout d'abord, vous devez comprendre les points suivants :

1. Pour une requête sur une table, un seul index est utilisé au maximum à chaque fois

2. Pour l'index conjoint, les données sont filtrées de gauche à droite, donc si la première condition de filtre cible supérieur ou inférieur à, la deuxième condition de filtre n'aura pas une plage d'index exacte dans toute la zone facultative. données filtrées par le premier filtre

3. La structure de l'index B-Tree est similaire à une structure arborescente. Voir la figure ci-dessous. L'index conjoint est récupéré de gauche à droite. Le début est le processus de recherche des branches de haut en bas dans cette structure.

Le mécanisme d'indexation consiste simplement à créer une table correspondante à partir de valeurs vers des éléments de données, afin que vous puissiez localiser rapidement une certaine valeur dans un certain champ jusqu'à une certaine ligne, éliminant ainsi le besoin d'exécuter la table entière pour trouver la correspondance. rangée, alors comparez Quick
Structure de l'index B-Tree :

Ensuite, revenons à votre question, si vous souhaitez améliorer considérablement l'efficacité, alors la première étape de l'indexation conjointe doit réduire considérablement la quantité de données pouvant être utilisées pour un filtrage ultérieur, donc si vous souhaitez vérifier

.

Answer 4

Les performances de filtrage des conditions de groupe sont très mauvaises et cela n'a pas de sens de créer un index seul.

Selon le scénario que vous décrivez, tant que la valeur de tid n'est pas trop grande (de l'ordre des milliers), il suffit de créer un index pour tid.
Si vous êtes toujours préoccupé par la grande quantité de données filtrées par les conditions de marée, vous pouvez créer un index combiné de marée et de groupe.

Answer 5

Tout d'abord, merci beaucoup pour votre attention et vos réponses à mes questions ! !
Après avoir résolu le problème, j'ai quelques réflexions sur les suggestions de Boxsnake et je les publierai ici.
group_tid Cette méthode d'indexation peut non seulement résoudre le problème de lecture, mais également résoudre le problème de pagination group_tid这种索引方式除了解决读取之外还能解决分页问题，
例如我每页文章数量是10，用户级别为3，那么读取时分别从group1、group2、group3中，
按范围tidPar exemple, si le nombre d'articles par page est de 10 et le niveau d'utilisateur est de 3, alors quand. en lecture, ce sera du groupe 1, du groupe 2, du groupe 3,

prenez 10 articles chacun selon la plage tid<100 Même s'il n'y a aucun résultat qui remplit les conditions dans un certain groupe, le la somme de plusieurs éléments peut tous les couvrir.

tid_group这种索引方式来读取，如果需要group<=3的情况，我不知道该取多少篇文章。
比方说取10篇，tid90-tid99，如果他们的group都是4，那么就无法取出符合条件的数值。
而tid_group在限定group之前又必须对tidMais si vous utilisez la méthode d'index tid_group pour lire, si groupPar exemple, si vous prenez 10 articles, tid90-tid99, si leurs groupes sont tous au nombre de 4, alors vous ne pouvez pas obtenir les valeurs qui remplissent les conditions. 🎜Et tid_group doit limiter tid avant de limiter group, il ne peut donc pas être utilisé. 🎜