Ist die Abfrage der MySQL information_schema-Datenbank ein guter Weg, um verwandte Tabellen zu finden?

Question

Ich habe eine Tabelle, die durch Fremdschlüssel in vielen anderen Tabellen referenziert wird. Wenn ich in meinem Programm eine dieser Zeilen löschen möchte, muss ich zunächst nach Abhängigkeiten suchen und sie dem Benutzer anzeigen - "Dieses Objekt hängt von x aus Tabelle y, z aus Tabelle q usw. ab". Außerdem erwarte ich, dass die Anzahl der Tabellen, die Fremdschlüssel zu dieser Tabelle haben, im Laufe der Zeit beträchtlich ansteigen wird.

Ist die Datenbank information_schema ein guter Weg, um nach allen Abhängigkeiten zu suchen? Ich habe versucht, eine Liste aller Tabellen abzufragen, die Fremdschlüssel zu meiner Tabelle haben, dann über das Ergebnis zu iterieren und alle Einträge aus jeder Tabelle auszuwählen, bei denen der Fremdschlüsselwert mit dem Wert übereinstimmt, den der Benutzer zu löschen versucht. Die Abfrage lautet wie folgt:

SELECT * FROM `KEY_COLUMN_USAGE` kcu
LEFT JOIN TABLE_CONSTRAINTS tc
ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE='FOREIGN KEY'
AND (kcu.REFERENCED_TABLE_SCHEMA='db')
AND (kcu.REFERENCED_TABLE_NAME = 'testtable')

was für die Bestimmung der zu durchsuchenden Tabellen perfekt funktioniert, aber es ist sehr langsam. Die Ausführung der Abfrage dauert auf meinem Entwicklungscomputer bestenfalls 1 bis 2 Sekunden. Wenn ich sie auf meinem Produktionsserver ausführe, verringert sich die Zeit erheblich, aber sie ist immer noch recht langsam.

Ich muss wissen, ob es eine schlechte Idee ist, information_schema auf diese Weise zu verwenden. Wenn nicht, wie kann ich eine bessere Leistung aus der Abfrage herausholen. Ist die Abfrage, die ich verwende, solide, oder gibt es einen besseren Weg, dies zu tun? Wenn ja, wie sollte ich dieses Problem unter dem Gesichtspunkt der Wartbarkeit am besten angehen.

Answer 1

Dvorak hat recht, INFORMATION_SCHEMA ist dafür gedacht.

Was Ihre Leistungsprobleme betrifft, so gibt es mehrere Möglichkeiten, die Leistung zu verbessern

• Das ist zwar einfach, bringt aber keine großen Verbesserungen mit sich: Speichern Sie die Informationen in einer statischen Variable. Zumindest wird die Abfrage nur einmal pro Seite erfolgen

• Verwenden Sie persistente Zwischenspeicherung: Der alternative PHP-Cache kann Ihnen helfen (siehe http://fr3.php.net/manual/en/book.apc.php ). Die Informationen, die Sie aus dem Informationsschema erhalten, sind ein guter Kandidat für die Speicherung in einem dauerhaften Cache.

• Verwenden Sie eine ORM-Bibliothek, z. B. Doctrine ( http://www.doctrine-project.org/ ) Ein Blick auf die Datei lib/Doctrine/Import/Mysql.php zeigt, dass sie genau das tut, was Sie brauchen, und noch viel mehr.

Answer 2

Ich denke, das ist genau die Art von Dingen, für die INFORMATION_SCHEMA gedacht ist.

Answer 3

Ich habe mir das auch angeschaut. Ich möchte die KEY_COLUMN_USAGE für einige CRUD verwenden. Und ich habe festgestellt, dass für diese Tabellen keine Schlüssel oder Indizes verfügbar sind. Das könnte der Grund für die schlechte Leistung sein.

Answer 4

Die Verwendung von INFORMATION_SCHEMA ist für statische oder administrative Systeme in Ordnung, wird aber für eine transaktionale Anwendungsfunktion nicht empfohlen, da INFORMATION_SCHEMA wahrscheinlich als Ansichten über dem systemeigenen Datenwörterbuch implementiert wird.

Dies wäre ein ziemlich ineffizienter Weg, um eine generische "D"-Operation für eine CRUD-Bibliothek durchzuführen. Außerdem ist auf vielen Systemen (z.B. Oracle) das Systemdatenwörterbuch in Form von Ansichten auf einer niedrigeren Datenstruktur implementiert. Das bedeutet, dass das systemeigene Datenwörterbuch hierfür möglicherweise ebenfalls nicht geeignet ist. Das Systemdatenwörterbuch kann sich auch von Version zu Version ändern.

Es dürfte relativ wenige Fälle geben, in denen ein direktes "Löschen" eines Datensatzes und aller seiner Kinder der richtige Weg ist. Wenn Sie dies als generische Funktion tun, bringt Ihnen das wenig praktischen Nutzen. Wenn die Fremdschlüssel nicht in der Datenbank vorhanden sind, werden verwaiste Kinder herumliegen, da dieser Ansatz davon abhängt, dass die FKs vorhanden sind, um zu wissen, welche Kinder zu löschen sind.

Answer 5

Verlangsamt meine Anwendungen zu einem Kriechen, aber ich brauche die Fremdschlüssel-Beschränkungsdaten, um alles richtig miteinander zu verbinden.

Die Verzögerungen bei der Abfrage von Informationsschemata sind enorm und führen dazu, dass eine Seite, die früher sofort geladen wurde, nun in 3-4 Sekunden geladen wird.

Nun, zumindest sind Fremdschlüssel-Beschränkungen in MySQL 5 verfügbar, was eine robustere Anwendungsentwicklung ermöglicht, aber natürlich zu einem gewissen Preis.

Nach meinen Google-Recherchen beschweren sich die Leute seit 2006 über dieses Problem, und das Problem besteht nach wie vor - es ist wohl nicht einfach zu beheben ;--(