Inclusion/exclusion de messages MySQL

Question

Ce post prend beaucoup de temps à taper parce que j'essaie d'être le plus clair possible, alors s'il vous plaît, soyez patient si c'est encore flou.

En gros, j'ai un tableau de posts dans la base de données auxquels les utilisateurs peuvent ajouter des paramètres de confidentialité.

ID | owner_id | post | other_info | niveau_confidentialité (valeur int)

À partir de là, les utilisateurs peuvent ajouter leurs détails de confidentialité, permettant ainsi de les rendre visibles à tous [niveau_confidentialité = 0), aux amis (niveau_confidentialité = 1), à personne (niveau_confidentialité = 3), ou à des personnes spécifiques ou des filtres (niveau_confidentialité = 4). Pour les niveaux de confidentialité spécifiant des personnes spécifiques (4), la requête fera référence au tableau "post_privacy_includes_for" dans une sous-requête pour voir si l'utilisateur (ou un filtre auquel l'utilisateur appartient) existe dans une ligne du tableau.

ID | post_id | user_id | list_id

De plus, l'utilisateur a la possibilité d'empêcher certaines personnes de voir leur post au sein d'un groupe plus large en les excluant (par exemple, le configurer pour que tout le monde puisse voir sauf un utilisateur harceleur). Pour cela, une autre table de référence est ajoutée, "post_privacy_exclude_from" - elle ressemble en tout point à la configuration de "post_privacy_includes_for".

Mon problème est que cela ne met pas à l'échelle. Pas du tout. À l'heure actuelle, il y a environ 1 à 2 millions de posts, la majorité d'entre eux étant configurés pour être visibles par tout le monde. Pour chaque post sur la page, il doit vérifier s'il existe une ligne excluant le post d'être affiché à l'utilisateur - cela se déplace vraiment lentement sur une page qui peut être remplie de 100 à 200 posts. Cela peut prendre de 2 à 4 secondes, en particulier lorsque des contraintes supplémentaires sont ajoutées à la requête.

Cela crée également des requêtes extrêmement grandes et complexes qui sont simplement... maladroites.

SÉLECTIONNEZ t.*
DE posts t
OÙ ( (t.niveau_confidentialité = 3
         ET t.owner_id = ?)
       OU (t.niveau_confidentialité = 4
           ET EXISTS
             ( SELECT i.id
              DE PostPrivacyIncludeFor i
              OÙ i.user_id = ?
                ET i.thought_id = t.id)
           OU t.niveau_confidentialité = 4
           ET t.owner_id = ?)
       OU (t.niveau_confidentialité = 4
           ET EXISTS
             (SELECT i2.id
              DE PostPrivacyIncludeFor i2
              OÙ i2.thought_id = t.id
                ET EXISTS
                  (SELECT r.id
                   DE FriendFilterIds r
                   OÙ r.list_id = i2.list_id
                     ET r.friend_id = ?))
           OU t.niveau_confidentialité = 4
           ET t.owner_id = ?)
       OU (t.niveau_confidentialité = 1
           ET EXISTS
             (SELECT G.id
              DE Following G
              OÙ follower_id = t.owner_id
                ET following_id = ?
                ET friend = 1)
           OU t.niveau_confidentialité = 1
           ET t.owner_id = ?)
       OU (NON EXISTS
             (SELECT e.id
              DE PostPrivacyExcludeFrom e
              OÙ e.thought_id = t.id
                ET e.user_id = ?
                ET NON EXISTS
                  (SELECT e2.id
                   DE PostPrivacyExcludeFrom e2
                   OÙ e2.thought_id = t.id
                     ET EXISTS
                       (SELECT l.id
                        DE FriendFilterIds l
                        OÙ l.list_id = e2.list_id
                          ET l.friend_id = ?)))
           ET t.niveau_confidentialité DANS (0, 1, 4))
  ET t.owner_id = ?
ORDRE PAR t.created_at LIMITE 100

(requête de simulation, similaire à la requête que j'utilise actuellement dans Doctrine ORM. C'est un gâchis, mais vous comprenez ce que je veux dire.)

Je suppose que ma question est la suivante : comment aborderiez-vous cette situation pour l'optimiser ? Y a-t-il une meilleure façon de configurer ma base de données ? Je suis prêt à abandonner complètement la méthode que j'ai actuellement mise en place, mais je ne saurais pas vers quoi me tourner.

Merci les gars.

Mise à jour : Corriger la requête pour refléter les valeurs que j'ai définies pour le niveau de confidentialité ci-dessus (j'ai oublié de la mettre à jour car j'ai simplifié les valeurs)

Answer 1

Votre requête est trop longue pour donner une solution définitive, mais l'approche que je suivrais serait de simplifier les recherches de données en convertissant les sous-requêtes en jointures, puis de construire la logique dans la clause where et la liste des colonnes de l'instruction SELECT :

select t.*, i.*, r.*, G.*, e.* from posts t
left join PostPrivacyIncludeFor i on i.user_id = ? and i.thought_id = t.id
left join FriendFilterIds r on r.list_id = i.list_id and r.friend_id = ?
left join Following G on follower_id = t.owner_id and G.following_id = ? and G.friend=1
left join PostPrivacyExcludeFrom e on e.thought_id = t.id and e.user_id = ? 

(Cela pourrait nécessiter une expansion : je n'ai pas pu suivre la logique de la dernière clause.)

Si vous parvenez à faire fonctionner la sélection simple rapidement ET à inclure toutes les informations nécessaires, alors tout ce que vous avez à faire est de construire la logique dans la liste de sélection et la clause where.

Answer 2

Je me suis essayé à simplifier rapidement cela sans retravailler trop votre design initial.

En utilisant cette solution, votre page web peut désormais simplement appeler la procédure stockée suivante pour obtenir une liste de publications filtrées pour un utilisateur donné dans une période spécifiée.

call list_user_filtered_posts( ,  );

Le script complet peut être trouvé ici : http://pastie.org/1212812

Je n'ai pas entièrement testé tout cela et vous pourriez constater que cette solution n'est pas assez performante pour vos besoins, mais elle peut vous aider à ajuster/modifier votre design existant.

Tables

J'ai supprimé votre table post_privacy_exclude_from et ajouté une table user_stalkers qui fonctionne pratiquement comme l'inverse de user_friends. J'ai conservé la table post_privacy_includes_for initiale selon votre design car cela permet à un utilisateur de restreindre une publication spécifique à un sous-ensemble de personnes.

drop table if exists users;
create table users
(
user_id int unsigned not null auto_increment primary key,
username varbinary(32) unique not null
)
engine=innodb;

drop table if exists user_friends;
create table user_friends
(
user_id int unsigned not null,
friend_user_id int unsigned not null,
primary key (user_id, friend_user_id)
)
engine=innodb;

drop table if exists user_stalkers;
create table user_stalkers
(
user_id int unsigned not null,
stalker_user_id int unsigned not null,
primary key (user_id, stalker_user_id)
)
engine=innodb;

drop table if exists posts;
create table posts
(
post_id int unsigned not null auto_increment primary key,
user_id int unsigned not null,
privacy_level tinyint unsigned not null default 0,
post_date datetime not null,
key user_idx(user_id),
key post_date_user_idx(post_date, user_id)
)
engine=innodb;

drop table if exists post_privacy_includes_for;
create table post_privacy_includes_for
(
post_id int unsigned not null,
user_id int unsigned not null,
primary key (post_id, user_id)
)
engine=innodb;

Procédures stockées

La procédure stockée est relativement simple - elle sélectionne initialement TOUTES les publications dans la période spécifiée, puis filtre les publications selon vos exigences initiales. Je n'ai pas testé la performance de cette procédure stockée avec de gros volumes, mais comme la sélection initiale est relativement petite, elle devrait être suffisamment performante tout en simplifiant votre code d'application/couche intermédiaire.

drop procedure if exists list_user_filtered_posts;

delimiter #

create procedure list_user_filtered_posts
(
in p_user_id int unsigned,
in p_day_interval tinyint unsigned
)
proc_main:begin

 drop temporary table if exists tmp_posts;
 drop temporary table if exists tmp_priv_posts;

 -- sélectionner TOUTES les publications dans la plage de dates requise (ou tout autre critère de sélection que vous exigez)

 create temporary table tmp_posts engine=memory 
 select 
  p.post_id, p.user_id, p.privacy_level, 0 as deleted 
 from 
  posts p
 where
  p.post_date between now() - interval p_day_interval day and now()  
 order by 
  p.user_id;

 -- purger les publications de stalkers (0,1,3,4)

 update tmp_posts 
 inner join user_stalkers us on us.user_id = tmp_posts.user_id and us.stalker_user_id = p_user_id
 set
  tmp_posts.deleted = 1
 where
  tmp_posts.user_id != p_user_id;

 -- purger les publications privées d'autres utilisateurs (3)

 update tmp_posts set deleted = 1 where user_id != p_user_id and privacy_level = 3;

 -- purger les publications réservées aux amis (1) c.-à-d. lorsque p_user_id n'est pas ami de l'auteur

 /*
  nécessite une autre table temporaire en raison d'un problème/bogue avec les tables temporaires MySQL
  http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html
 */

 -- les publications privées (1) que cet utilisateur peut voir

 create temporary table tmp_priv_posts engine=memory 
 select
  tp.post_id
 from
  tmp_posts tp
 inner join user_friends uf on uf.user_id = tp.user_id and uf.friend_user_id = p_user_id
 where
  tp.user_id != p_user_id and tp.privacy_level = 1;

 -- supprimer les publications privées que cet utilisateur ne peut pas voir

 update tmp_posts 
 left outer join tmp_priv_posts tpp on tmp_posts.post_id = tpp.post_id 
 set 
  tmp_posts.deleted = 1
 where 
  tpp.post_id is null and tmp_posts.privacy_level = 1;

 -- purger les publications filtrées (4)

 truncate table tmp_priv_posts; -- réutiliser la table temporaire

 insert into tmp_priv_posts
 select
  tp.post_id
 from
  tmp_posts tp
 inner join post_privacy_includes_for ppif on tp.post_id = ppif.post_id and ppif.user_id = p_user_id
 where
  tp.user_id != p_user_id and tp.privacy_level = 4;

 -- supprimer les publications privées que cet utilisateur ne peut pas voir

 update tmp_posts 
 left outer join tmp_priv_posts tpp on tmp_posts.post_id = tpp.post_id 
 set 
  tmp_posts.deleted = 1
 where 
  tpp.post_id is null and tmp_posts.privacy_level = 4;

 drop temporary table if exists tmp_priv_posts;

 -- afficher les publications filtrées (afficher TOUTES celles-ci sur la page web)

 select 
  p.* 
 from 
  posts p
 inner join tmp_posts tp on p.post_id = tp.post_id
 where
  tp.deleted = 0
 order by
  p.post_id desc;

 -- nettoyer

 drop temporary table if exists tmp_posts;

end proc_main #

delimiter ;

Données de test

Quelques données de test basiques.

insert into users (username) values ('f00'),('bar'),('alpha'),('beta'),('gamma'),('omega');

insert into user_friends values 
(1,2),(1,3),(1,5),
(2,1),(2,3),(2,4),
(3,1),(3,2),
(4,5),
(5,1),(5,4);

insert into user_stalkers values (4,1);

insert into posts (user_id, privacy_level, post_date) values

-- public (0)

(1,0,now() - interval 8 day),
(1,0,now() - interval 8 day),
(2,0,now() - interval 7 day),
(2,0,now() - interval 7 day),
(3,0,now() - interval 6 day),
(4,0,now() - interval 6 day),
(5,0,now() - interval 5 day),

-- réservé aux amis (1)

(1,1,now() - interval 5 day),
(2,1,now() - interval 4 day),
(4,1,now() - interval 4 day),
(5,1,now() - interval 3 day),

-- privé (3)

(1,3,now() - interval 3 day),
(2,3,now() - interval 2 day),
(4,3,now() - interval 2 day),

-- filtré (4)

(1,4,now() - interval 1 day),
(4,4,now() - interval 1 day),
(5,4,now());

insert into post_privacy_includes_for values (15,4), (16,1), (17,6);

Test

Comme je l'ai mentionné précédemment, je n'ai pas entièrement testé cela, mais en surface cela semble fonctionner.

select * from posts;

call list_user_filtered_posts(1,14);
call list_user_filtered_posts(6,14);

call list_user_filtered_posts(1,7);
call list_user_filtered_posts(6,7);

J'espère que vous trouverez une partie de ceci utile.