Un moyen plus rapide de supprimer les lignes correspondantes?

Question

Je suis un parent novice quand il s'agit de bases de données. Nous utilisons MySQL et je suis en train d'essayer d'accélérer le rythme d'une instruction SQL qui semble prendre un certain temps à s'exécuter. J'ai regardé autour de sur DONC pour une question similaire, mais n'en trouvait pas.

L'objectif est de supprimer toutes les lignes dans Une table qui ont un id correspondant dans le tableau B.

Je suis actuellement en train de faire le suivant:

DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE b.id = a.id);

Il y a environ 100K lignes dans une table et environ 22K lignes dans la table b. La colonne " id " est la PK pour les deux tableaux.

Cette déclaration prend environ 3 minutes pour s'exécuter sur mon test de box - Pentium D, XP SP3, 2 go de ram, MySQL 5.0.67. Cela semble lent à moi. Peut-être qu'il ne l'est pas, mais j'espérais pour accélérer les choses. Est-il mieux/plus rapide façon d'accomplir cette?

---

EDIT:

Quelques informations supplémentaires qui pourraient être utiles. Les tableaux A et B ont la même structure que j'ai effectué les opérations suivantes pour créer la table B:

CREATE TABLE b LIKE a;

Le tableau a (et donc de la table b) a quelques indices pour aider à accélérer les requêtes qui sont portées contre elle. Encore une fois, je suis un novice par rapport à la DB de travail et toujours en apprentissage. Je ne sais pas quel effet, le cas échéant, cela a sur les choses. Je suppose qu'il n'ont un effet que l'index doivent être nettoyés trop, non? Je me demandais aussi si il y avait d'autres DB paramètres qui peuvent affecter la vitesse.

Aussi, je suis en utilisant INNO DB.

---

Voici quelques informations supplémentaires qui pourraient vous être utiles.

La Table a une structure similaire à ceci (j'ai désinfecté cela un peu):

DROP TABLE IF EXISTS `frobozz`.`a`;
CREATE TABLE  `frobozz`.`a` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `fk_g` varchar(30) NOT NULL,
  `h` int(10) unsigned default NULL,
  `i` longtext,
  `j` bigint(20) NOT NULL,
  `k` bigint(20) default NULL,
  `l` varchar(45) NOT NULL,
  `m` int(10) unsigned default NULL,
  `n` varchar(20) default NULL,
  `o` bigint(20) NOT NULL,
  `p` tinyint(1) NOT NULL,
  PRIMARY KEY  USING BTREE (`id`),
  KEY `idx_l` (`l`),
  KEY `idx_h` USING BTREE (`h`),
  KEY `idx_m` USING BTREE (`m`),
  KEY `idx_fk_g` USING BTREE (`fk_g`),
  KEY `fk_g_frobozz` (`id`,`fk_g`),
  CONSTRAINT `fk_g_frobozz` FOREIGN KEY (`fk_g`) REFERENCES `frotz` (`g`)
) ENGINE=InnoDB AUTO_INCREMENT=179369 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

Je soupçonne qu'une partie de la question est qu'il ya un certain nombre d'indices de cette table. Tableau B ressemble à la table B, mais il ne contient les colonnes id et h.

Aussi, le profilage résultats sont comme suit:

starting 0.000018
checking query cache for query 0.000044
checking permissions 0.000005
Opening tables 0.000009
init 0.000019
optimizing 0.000004
executing 0.000043
end 0.000005
end 0.000002
query end 0.000003
freeing items 0.000007
logging slow query 0.000002
cleaning up 0.000002

---

RÉSOLU

Merci pour toutes les réponses et commentaires. Ils ont certainement m'a fait réfléchir sur le problème. Bravo à dotjoe pour m'éloigner du problème en posant la simple question "d'autres tables de référence une.id?"

Le problème est qu'il y a un DÉCLENCHEUR de SUPPRESSION sur la table Un qui appelle une procédure stockée de mettre à jour deux autres tables, C et D. le Tableau C avait une FK retour à un.id et après avoir fait quelques trucs liés à ce code dans la procédure stockée, il avait de l'instruction,

DELETE FROM c WHERE c.id = theId;

J'ai regardé dans l'EXPLIQUER déclaration et a réécrit ce que,

EXPLAIN SELECT * FROM c WHERE c.other_id = 12345;

Donc, je ne pouvais voir ce qu'il faisait et il m'a donné les informations suivantes:

id            1
select_type   SIMPLE
table         c
type          ALL
possible_keys NULL
key           NULL
key_len       NULL
ref           NULL
rows          2633
Extra         using where

Ceci m'a dit que c'était une douloureuse opération à faire et comme il allait être appelé 22500 fois (pour l'ensemble de données sont supprimées), qui était le problème. Une fois que j'ai créé un INDEX sur cette other_id colonne et rediffusé l'EXPLIQUER, j'ai eu:

id            1
select_type   SIMPLE
table         c
type          ref
possible_keys Index_1
key           Index_1
key_len       8
ref           const
rows          1
Extra         

Beaucoup mieux, en fait, vraiment génial.

J'ai ajouté que Index_1 et mon supprimer les heures sont en ligne avec les temps déclarés par mattkemp. Ce fut un très subtile erreur de ma part en raison de la chaussure horning certaines fonctionnalités supplémentaires à la dernière minute. Il s'est avéré que la plupart de l'alternative proposée SUPPRIMER/SELECT, comme Daniel l'a déclaré, a fini par prendre essentiellement la même quantité de temps et que soulmerge mentionné, la déclaration a été à peu près le meilleur que j'allais être capable de construire fondé sur ce que je devais faire. Une fois que j'ai fourni un indice pour cet autre tableau C, mon Suppressions ont été rapides.

Post-mortem:
Deux leçons apprises au cours de cet exercice. Tout d'abord, il est clair que je n'ai pas de tirer parti de la puissance de l'EXPLIQUER pour obtenir une meilleure idée de l'impact de mes requêtes SQL. C'est une erreur de débutant, donc je ne vais pas me battre jusqu'à ce propos. Je vais apprendre de cette erreur. Deuxièmement, le code malveillant a été le résultat d'un "get it done quick" mentalité et l'insuffisance de conception, de test a conduit à ce problème ne se présente plus tôt. J'avait généré plusieurs grandes ensembles de données de test à utiliser comme entrée de test pour cette nouvelle fonctionnalité, je n'aurais pas perdu mon temps ni la vôtre. Mes tests sur la DB côté manquait de la profondeur de mon côté a mis en place. Maintenant, j'ai eu l'occasion de l'améliorer.

Référence: EXPLIQUER Déclaration

Answer 1

Suppression de données de InnoDB est le plus cher de l'opération, vous pouvez demander. Comme vous l'avez déjà découvert la requête elle-même n'est pas le problème - la plupart d'entre eux sera optimisé pour le même plan d'exécution de toute façon.

Tandis qu'il peut être difficile de comprendre pourquoi les Suppressions de tous les cas sont les plus lentes, il y a une explication simple. InnoDB est un moteur de stockage transactionnel. Cela signifie que si votre requête a été interrompu à mi-chemin à travers, tous les enregistrements seraient encore en place, comme si rien ne s'était passé. Une fois qu'il est terminé, tout aura disparu dans l'instant même. Lors de la SUPPRESSION d'autres clients se connectant au serveur de voir les enregistrements jusqu'à ce que votre SUPPRESSION complète.

Pour atteindre cet objectif, InnoDB utilise une technique appelée MVCC (Version Multi de Contrôle de la Simultanéité). Ce qu'il fait est de donner à chaque connexion une vue instantanée de l'ensemble de la base de données tel qu'il était lors de la première déclaration de la transaction. Pour atteindre cet objectif, chaque enregistrement dans InnoDB en interne peut avoir plusieurs valeurs, une pour chaque instantané. C'est aussi pourquoi compter sur InnoDB prend un peu de temps, ça dépend de l'instantané de l'état que vous voyez à l'époque.

Pour votre transaction de SUPPRESSION, chaque et chaque enregistrement est identifié en fonction de vos conditions de la requête, est marqué pour suppression. Comme d'autres clients peuvent accéder aux données en même temps, il ne peut pas les supprimer immédiatement à partir de la table, parce qu'ils ont de voir leurs respectifs instantané pour garantir l'atomicité de la suppression.

Une fois que tous les enregistrements ont été marqués pour la suppression, l'opération est correctement engagé. Et même alors, ils ne peuvent pas être immédiatement retiré de l'effectif des pages de données, avant toutes les autres opérations qui ont travaillé avec un instantané de la valeur avant de votre transaction de SUPPRESSION, ont pris fin.

Donc, en fait, votre 3 minutes ne sont pas vraiment lente, compte tenu du fait que tous les dossiers doivent être modifiées afin de les préparer pour l'enlèvement dans une transaction de façon sécuritaire. Probablement vous "entendre" votre disque dur de travail alors que l'instruction est exécutée. Ceci est causé par l'accès à toutes les lignes. Pour améliorer les performances, vous pouvez essayer d'augmenter InnoDB pool de mémoire tampon de taille pour votre serveur et essayez de limiter l'accès à la base de données tandis que vous SUPPRIMEZ, réduisant ainsi le nombre de l'historique des versions InnoDB doit maintenir par enregistrement. Avec la mémoire supplémentaire InnoDB peut-être en mesure de lire votre table (pour la plupart) en mémoire et éviter certains disque temps de recherche.

Answer 2

Essaye ça:

 DELETE a
FROM a
INNER JOIN b
 on a.id = b.id
 

Les sous-requêtes ont tendance à être plus lentes que les jointures car elles sont exécutées pour chaque enregistrement de la requête externe.

Answer 3

Votre temps de trois minutes semble vraiment lent. Ma conjecture est que la colonne id est de ne pas être indexé correctement. Si vous pouvez fournir l'exacte définition de la table que vous êtes à l'aide qui pourrait être utile.

J'ai créé un simple script python pour produire des données de test et a couru plusieurs versions différentes de la suppression de la requête sur le même ensemble de données. Voici ma table de définitions:

drop table if exists a;
create table a
 (id bigint unsigned  not null primary key,
  data varchar(255) not null) engine=InnoDB;

drop table if exists b;
create table b like a;

J'ai ensuite inséré 100k lignes dans une et 25k lignes dans b (22.5 k, étaient également dans une). Voici les résultats des différentes commandes de suppression. J'ai laissé tomber et ont repeuplé la table entre les courses.

mysql> DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE a.id=b.id);
Query OK, 22500 rows affected (1.14 sec)

mysql> DELETE FROM a USING a LEFT JOIN b ON a.id=b.id WHERE b.id IS NOT NULL;
Query OK, 22500 rows affected (0.81 sec)

mysql> DELETE a FROM a INNER JOIN b on a.id=b.id;
Query OK, 22500 rows affected (0.97 sec)

mysql> DELETE QUICK a.* FROM a,b WHERE a.id=b.id;
Query OK, 22500 rows affected (0.81 sec)

Tous les tests ont été exécutés sur un processeur Intel Core2 quad-core 2,5 GHz, 2 go de RAM avec Ubuntu 8.10 et MySQL 5.0. Notez que l'exécution d'une instruction sql est toujours en mono-thread.

---

Mise à jour:

J'ai mis à jour mes tests à utiliser itsmatt du schéma. J'ai légèrement modifié par suppression de l'auto incrément (je suis de la génération de données synthétiques) et de codage du jeu de caractères (n'a pas de travail - ne pas creuser en elle).

Voici ma nouvelle table de définitions:

drop table if exists a;
drop table if exists b;
drop table if exists c;

create table c (id varchar(30) not null primary key) engine=InnoDB;

create table a (
  id bigint(20) unsigned not null primary key,
  c_id varchar(30) not null,
  h int(10) unsigned default null,
  i longtext,
  j bigint(20) not null,
  k bigint(20) default null,
  l varchar(45) not null,
  m int(10) unsigned default null,
  n varchar(20) default null,
  o bigint(20) not null,
  p tinyint(1) not null,
  key l_idx (l),
  key h_idx (h),
  key m_idx (m),
  key c_id_idx (id, c_id),
  key c_id_fk (c_id),
  constraint c_id_fk foreign key (c_id) references c(id)
) engine=InnoDB row_format=dynamic;

create table b like a;

J'ai ensuite rediffusé les mêmes tests avec 100k lignes dans un et de 25 lignes de b (et le remplissage entre les pistes).

mysql> DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE a.id=b.id);
Query OK, 22500 rows affected (11.90 sec)

mysql> DELETE FROM a USING a LEFT JOIN b ON a.id=b.id WHERE b.id IS NOT NULL;
Query OK, 22500 rows affected (11.48 sec)

mysql> DELETE a FROM a INNER JOIN b on a.id=b.id;
Query OK, 22500 rows affected (12.21 sec)

mysql> DELETE QUICK a.* FROM a,b WHERE a.id=b.id;
Query OK, 22500 rows affected (12.33 sec)

Comme vous pouvez le voir c'est un peu plus lent qu'avant, probablement en raison de plusieurs indices. Cependant, il est nulle part près de trois minutes.

Une autre chose que vous voudrez peut-être regarder à l'est de déplacer la longtext champ à la fin du schéma. Je crois me souvenir que mySQL fonctionne mieux si tout de la taille des domaines restreints sont le premier et le texte, blob, etc sont à la fin.

Answer 4

Vous faites votre sous-requête sur "b" pour chaque ligne de "a".

Essayer:

 DELETE FROM a USING a LEFT JOIN b ON a.id = b.id WHERE b.id IS NOT NULL;
 

Answer 5

Essayez ceci:

 DELETE QUICK A.* FROM A,B WHERE A.ID=B.ID
 

C'est beaucoup plus rapide que les requêtes normales.

Référez-vous à la syntaxe: http://dev.mysql.com/doc/refman/5.0/en/delete.html