Stellt fünf häufige Datenbankprobleme vor, die in PHP-Anwendungen auftreten – einschließlich Datenbankschemadesign, Datenbankzugriff und Geschäftslogikcode, der die Datenbank verwendet – und deren Lösungen.
Wenn nur eine Art, eine Datenbank zu verwenden, richtig wäre ...
Es gibt viele Möglichkeiten, Datenbankdesign, Datenbankzugriff und datenbankbasierten PHP-Geschäftslogikcode zu erstellen, aber sie sind meist falsch. In diesem Artikel werden fünf häufige Probleme erläutert, die beim Datenbankdesign und beim PHP-Code, der auf die Datenbank zugreift, auftreten, und wie Sie diese beheben können, wenn Sie auf sie stoßen.
Problem 1: MySQL direkt verwenden
Ein häufiges Problem besteht darin, dass älterer PHP-Code die Funktion mysql_ direkt verwendet, um auf die Datenbank zuzugreifen. Listing 1 zeigt, wie man direkt auf die Datenbank zugreift.
Listing 1. Access/get.php
<?php
Funktion get_user_id( $name )
{
$db = mysql_connect( 'localhost', 'root', 'password' );
mysql_select_db( 'users' );
$res = mysql_query( "SELECT id FROM users WHERE login='".$name."'" );
while( $row = mysql_fetch_array( $res ) ) { $id = $row[0] }
return $id;
}
var_dump( get_user_id( 'jack' ) );
?＞
Beachten Sie, dass die Funktion mysql_connect für den Zugriff auf die Datenbank verwendet wird. Beachten Sie auch die Abfrage, die eine Zeichenfolgenverkettung verwendet, um den Parameter „$name“ zur Abfrage hinzuzufügen.
Es gibt zwei gute Alternativen zu dieser Technologie: das PEAR DB-Modul und die PHP Data Objects (PDO)-Klassen. Beide bieten Abstraktionen von bestimmten Datenbankauswahlen. Dadurch kann Ihr Code ohne große Anpassungen auf IBM® DB2®, MySQL, PostgreSQL oder jeder anderen Datenbank ausgeführt werden, mit der Sie eine Verbindung herstellen möchten.
Ein weiterer Vorteil der Verwendung des PEAR DB-Moduls und der PDO-Abstraktionsschicht besteht darin, dass Sie den ?-Operator in SQL-Anweisungen verwenden können. Dies erleichtert die Wartung von SQL und schützt Ihre Anwendung vor SQL-Injection-Angriffen.
Alternativer Code mit PEAR DB ist unten dargestellt.
Listing 2. Access/get_good.php
<?php
require_once("DB.php");
function get_user_id( $name )
{
$dsn = 'mysql://root:password@localhost/users';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage()); }
$res = $db->query( 'SELECT id FROM users WHERE login=?',array( $name ) ) ;
$id = null;
while( $res->fetchInto( $row ) ) { $id = $row[0] }
return $id;
}
var_dump( get_user_id( 'jack' ) );
?＞
Beachten Sie, dass alle direkten Verwendungen von MySQL entfernt wurden, mit Ausnahme der Datenbankverbindungszeichenfolge in $dsn. Darüber hinaus verwenden wir die Variable $name in SQL über den ?-Operator. Anschließend werden die Abfragedaten über das Array am Ende der query()-Methode gesendet.
Problem 2: Die Funktion zur automatischen Inkrementierung wird nicht verwendet
. Wie die meisten modernen Datenbanken verfügt MySQL über die Möglichkeit, eindeutige Identifikatoren für die automatische Inkrementierung pro Datensatz zu erstellen. Darüber hinaus sehen wir immer noch Code, der zuerst eine SELECT-Anweisung ausführt, um die größte ID zu finden, diese ID dann um 1 erhöht und einen neuen Datensatz findet. Listing 3 zeigt ein Beispiel für ein schlechtes Muster.
Listing 3. Badid.sql
DROP TABLE IF EXISTS-Benutzer;
CREATE TABLE-Benutzer (
id MEDIUMINT,
Anmeldetext,
Passwort TEXT
);
INSERT INTO user VALUES ( 1, 'jack', 'pass' );
INSERT INTO Benutzer VALUES ( 2, 'joan', 'pass' );
INSERT INTO user VALUES (1, 'jane', 'pass' );
Hier wird das ID-Feld einfach als Ganzzahl angegeben. Obwohl es eindeutig sein sollte, können wir jeden Wert hinzufügen, wie in den verschiedenen INSERT-Anweisungen gezeigt, die auf die CREATE-Anweisung folgen. Listing 4 zeigt den PHP-Code zum Hinzufügen von Benutzern zu diesem Schematyp.
Listing 4. Add_user.php
<?php
require_once("DB.php");
function add_user( $name, $pass )
{
$rows = array();
$dsn = 'mysql://root:password@localhost/bad_badid';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage()); }
$res = $db->query( "SELECT max(id) FROM users" );
$id = null;
while( $res->fetchInto( $row ) ) { $id = $row[0]; }
$
id += 1;
" );
name
, $pass ) );
}
$id = add_user( 'jerry', 'pass' );
var_dump( $id );
?＞
Der Code in add_user.php führt zunächst eine Abfrage durch, um den Maximalwert der ID zu ermitteln. Die Datei führt dann eine INSERT-Anweisung aus, wobei der ID-Wert um 1 erhöht wird. Dieser Code schlägt in einer Race-Bedingung auf einem stark ausgelasteten Server fehl. Außerdem ist es auch ineffizient.
Was ist also die Alternative? Verwenden Sie die Funktion zur automatischen Inkrementierung in MySQL, um automatisch eine eindeutige ID für jede Einfügung zu erstellen. Das aktualisierte Schema sieht folgendermaßen aus.
Listing 5. Goodid.php
DROP TABLE IF EXISTS-Benutzer;
CREATE TABLE-Benutzer (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
Anmeldetext nicht NULL,
Passwort TEXT NICHT NULL,
PRIMÄRSCHLÜSSEL(id)
);
INSERT INTO user VALUES ( null, 'jack', 'pass' );
INSERT INTO user VALUES (null, 'joan', 'pass' );
INSERT INTO user VALUES ( null, 'jane', 'pass' );
Wir haben das NOT NULL-Flag hinzugefügt, um anzugeben, dass das Feld nicht null sein darf. Wir haben außerdem das Flag AUTO_INCREMENT hinzugefügt, um anzuzeigen, dass das Feld automatisch inkrementiert wird, und das Flag PRIMARY KEY, um anzuzeigen, dass das Feld eine ID ist. Diese Änderungen beschleunigen die Dinge. Listing 6 zeigt den aktualisierten PHP-Code zum Einfügen des Benutzers in die Tabelle.
Listing 6. Add_user_good.php
<?php
require_once("DB.php");
function add_user( $name, $pass )
{
$dsn = 'mysql://root:password@localhost/good_genid';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage()); }
$sth = $db->prepare( "INSERT INTO users VALUES(null,?,?)" );
$db->execute( $sth, array( $name, $pass ) );
$res = $db->query( "SELECT last_insert_id()" );
$id = null;
while( $res->fetchInto( $row ) ) { $id = $row[0] }
return $id;
}
$id = add_user( 'jerry', 'pass' );
var_dump( $id );
?>
Anstatt jetzt den maximalen ID-Wert abzurufen, verwende ich direkt die INSERT-Anweisung, um die Daten einzufügen, und verwende dann die SELECT-Anweisung, um die ID des zuletzt eingefügten Datensatzes abzurufen. Der Code ist viel einfacher und effizienter als die Originalversion und die zugehörigen Muster.
Frage 3: Verwendung mehrerer Datenbanken
Gelegentlich sehen wir eine Anwendung, bei der sich jede Tabelle in einer separaten Datenbank befindet. Dies ist bei sehr großen Datenbanken sinnvoll, für allgemeine Anwendungen ist diese Partitionierungsebene jedoch nicht erforderlich. Darüber hinaus können relationale Abfragen nicht datenbankübergreifend durchgeführt werden, was der Idee der Verwendung einer relationalen Datenbank zuwiderläuft, ganz zu schweigen davon, dass es schwieriger wäre, Tabellen über mehrere Datenbanken hinweg zu verwalten. Wie sollten also mehrere Datenbanken aussehen? Zunächst benötigen Sie einige Daten. Listing 7 zeigt solche Daten aufgeteilt in vier Dateien.
Listing 7. Datenbankdatei
Files.sql:
TABLE-Dateien ERSTELLEN (
id MEDIUMINT,
user_id MEDIUMINT,
Namenstext,
Pfad TEXT
);
Load_files.sql:
IN Dateien einfügen VALUES ( 1, 1, 'test1.jpg', 'files/test1.jpg' );
INSERT INTO files VALUES (2, 1, 'test2.jpg', 'files/test2.jpg' )
;
DROP TABLE IF EXISTS Benutzer;
CREATE TABLE-Benutzer (
id MEDIUMINT,
Anmeldetext,
Passwort TEXT
);
Load_users.sql:
INSERT INTO user VALUES ( 1, 'jack', 'pass' );
INSERT INTO user VALUES (2, 'jon', 'pass' );
In der Multi-Datenbank-Version dieser Dateien sollten Sie die SQL-Anweisung in eine Datenbank laden und dann die SQL-Anweisung des Benutzers in eine andere Datenbank laden. Der PHP-Code, der zum Abfragen der Datenbank nach Dateien verwendet wird, die einem bestimmten Benutzer zugeordnet sind, wird unten angezeigt.
Listing 8. Getfiles.php
<?php
require_once("DB.php");
function get_user( $name )
{
$dsn = 'mysql://root:password@localhost/bad_multi1';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage()); }
$res = $db->query( "SELECT id FROM users WHERE login=?",array( $name ) ) ;
$uid = null;
while( $res->fetchInto( $row ) ) { $uid = $row[0] }
return $uid;
}
Funktion get_files( $name )
{
$uid = get_user( $name );
$rows = array();
$dsn = 'mysql://root:password@localhost/bad_multi2';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage()); }
$res = $db->query( "SELECT * FROM files WHERE user_id=?",array( $uid ) ) ;
while( $res->fetchInto( $row ) ) { $rows[] = $row }
$rows zurückgeben;
}
$files = get_files( 'jack' );
var_dump( $files );
?>
Die Funktion get_user stellt eine Verbindung zur Datenbank her, die die Benutzertabelle enthält, und ruft die ID eines bestimmten Benutzers ab. Die Funktion get_files stellt eine Verbindung zur Dateitabelle her und ruft die Dateizeilen ab, die einem bestimmten Benutzer zugeordnet sind.
Eine bessere Möglichkeit, all diese Dinge zu tun, besteht darin, die Daten in eine Datenbank zu laden und dann eine Abfrage wie die folgende auszuführen.
Listing 9. Getfiles_good.php
<?php
require_once("DB.php");
function get_files( $name )
{
$rows = array();
$dsn = 'mysql://root:password@localhost/good_multi';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage()); }
$res = $db->query("SELECT files.* FROM users, files WHERE
users.login=? ANDuser.id=files.user_id",
array( $name ) );
while( $res->fetchInto( $row ) ) { $rows[] = $row }
return $rows;
}
$files = get_files( 'jack' );
var_dump( $files );
?>
Der Code ist nicht nur kürzer, sondern auch verständlicher und effizienter. Anstatt zwei Abfragen auszuführen, führen wir eine Abfrage aus.
Obwohl diese Frage weit hergeholt klingen mag, kommen wir in der Praxis normalerweise zu dem Schluss, dass sich alle Tabellen in derselben Datenbank befinden sollten, es sei denn, es gibt einen sehr zwingenden Grund.
Frage 4: Keine Beziehungen verwenden.
Relationale Datenbanken unterscheiden sich von Programmiersprachen dadurch, dass sie keine Array-Typen haben. Stattdessen nutzen sie Beziehungen zwischen Tabellen, um eine Eins-zu-viele-Struktur zwischen Objekten zu erstellen, die den gleichen Effekt wie ein Array hat. Ein Problem, das ich bei Anwendungen gesehen habe, besteht darin, dass Ingenieure versuchen, die Datenbank wie eine Programmiersprache zu verwenden, indem sie Arrays mithilfe von Textzeichenfolgen mit durch Kommas getrennten Bezeichnern erstellen. Siehe das Muster unten.
Listing 10. Bad.sql
DROP TABLE IF EXISTS-Dateien;
TABLE-Dateien ERSTELLEN (
id MEDIUMINT,
Namenstext,
Pfad TEXT
);
DROP TABLE IF EXISTS Benutzer;
CREATE TABLE-Benutzer (
id MEDIUMINT,
Anmeldetext,
Passwort TEXT,
Dateien TEXT
);
INSERT INTO files VALUES ( 1, 'test1.jpg', 'media/test1.jpg' );
INSERT INTO files VALUES (2, 'test1.jpg', 'media/test1.jpg' );
INSERT INTO user VALUES (1, 'jack', 'pass', '1,2' );
Ein Benutzer im System kann mehrere Dateien haben. In Programmiersprachen sollte ein Array verwendet werden, um die einem Benutzer zugeordneten Dateien darzustellen. In diesem Beispiel entscheidet sich der Programmierer für die Erstellung eines Dateifelds, das eine durch Kommas getrennte Liste von Datei-IDs enthält. Um eine Liste aller Dateien für einen bestimmten Benutzer zu erhalten, muss der Programmierer zunächst die Zeilen aus der Benutzertabelle lesen, dann den Text der Dateien analysieren und für jede Datei eine separate SELECT-Anweisung ausführen. Der Code wird unten angezeigt.
Listing 11. Get.php
<?php
require_once("DB.php");
function get_files( $name )
{
$dsn = 'mysql://root:password@localhost/bad_norel';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage()); }
$res = $db->query( "SELECT files FROM users WHERE login=?",array( $name ) ) ;
$files = null;
while( $res->fetchInto( $row ) ) { $files = $row[0] }
$rows = array(
foreach( split( ',',$files ) as $file )
{
$res = $db->query( "SELECT * FROM files WHERE id=?",
array( $file ) );
while( $res->fetchInto( $row ) ) { $rows[] = $row }
}
return $rows;
}
$files = get_files( 'jack' );
var_dump( $files );
?>
Die Technologie ist langsam, schwer zu warten und nutzt die Datenbank nicht optimal aus. Die einzige Lösung besteht darin, das Schema neu zu gestalten, um es wieder in die traditionelle relationale Form umzuwandeln, wie unten gezeigt.
Listing 12. Good.sql
DROP TABLE IF EXISTS-Dateien;
TABLE-Dateien ERSTELLEN (
id MEDIUMINT,
user_id MEDIUMINT,
Namenstext,
Pfad TEXT
);
DROP TABLE IF EXISTS Benutzer;
CREATE TABLE-Benutzer (
id MEDIUMINT,
Anmeldetext,
Passwort TEXT
);
INSERT INTO user VALUES ( 1, 'jack', 'pass' );
IN Dateien einfügen VALUES ( 1, 1, 'test1.jpg', 'media/test1.jpg' );
INSERT INTO files VALUES (2, 1, 'test1.jpg', 'media/test1.jpg');
Hier wird jede Datei über die Funktion user_id mit dem Benutzer in der Dateitabelle verknüpft. Dies könnte jedem widersprechen, der sich mehrere Dateien als Arrays vorstellt. Natürlich verweisen Arrays nicht auf die darin enthaltenen Objekte – im Gegenteil. Aber in einer relationalen Datenbank funktioniert das so, und Abfragen sind dadurch viel schneller und einfacher. Listing 13 zeigt den entsprechenden PHP-Code.
Listing 13. Get_good.php
<?php
require_once("DB.php");
function get_files( $name )
{
$dsn = 'mysql://root:password@localhost/good_rel';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage() }
$rows = array();
$res = $db->query("Dateien auswählen.* FROM Benutzer,Dateien WHERE Benutzer.login=?
UND users.id=files.user_id",array( $name ) );
while( $res->fetchInto( $row ) ) { $rows[] = $row }
$rows zurückgeben;
}
$files = get_files( 'jack' );
var_dump( $files );
?＞
Hier stellen wir eine Abfrage an die Datenbank, um alle Zeilen abzurufen. Der Code ist nicht komplex und nutzt die Datenbank wie beabsichtigt.
Frage 5: n+1-Muster
Ich kann Ihnen nicht sagen, wie oft ich große Anwendungen gesehen habe, bei denen der Code zunächst einige Entitäten (z. B. Kunden) abruft und diese dann einzeln abruft, um sie abzurufen Details der Entität. Wir nennen diesen n+1-Modus, weil die Abfrage so oft ausgeführt wird – eine Abfrage ruft die Liste aller Entitäten ab und dann wird eine Abfrage für jede der n Entitäten ausgeführt. Bei n=10 ist das kein Problem, aber was ist mit n=100 oder n=1000? Dann kommt es zwangsläufig zu Ineffizienzen. Listing 14 zeigt ein Beispiel für dieses Muster.
Listing 14. Schema.sql
DROP TABLE IF EXISTS Authors;
CREATE TABLE-Autoren (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
Name TEXT NICHT NULL,
PRIMÄRSCHLÜSSEL(id)
);
DROP TABLE IF EXISTS Bücher;
TABELLEBÜCHER ERSTELLEN (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
author_id MEDIUMINT NOT NULL,
Name TEXT NICHT NULL,
PRIMÄRSCHLÜSSEL(id)
INSERT INTO Authors VALUES ( null, 'Jack Herrington' )
;
INSERT INTO Authors VALUES ( null, 'Dave Thomas' );
INSERT INTO Books VALUES ( null, 1, 'Code Generation in Action' );
INSERT INTO Books VALUES ( null, 1, 'Podcasting Hacks' );
INSERT INTO Books VALUES ( null, 1, 'PHP Hacks' );
INSERT INTO Books VALUES ( null, 2, 'Pragmatischer Programmierer' );
INSERT INTO Books VALUES ( null, 2, 'Ruby on Rails' );
INSERT INTO Books VALUES ( null, 2, 'Programming Ruby' );
Das Muster ist zuverlässig und enthält keine Fehler. Das Problem liegt im Code, der auf die Datenbank zugreift, um alle Bücher eines bestimmten Autors zu finden, wie unten gezeigt.
Listing 15. Get.php
<?php
require_once('DB.php');
$dsn = 'mysql://root:password@localhost/good_books';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage() }
function get_author_id( $name )
{
global $db;
$res = $db->query( "SELECT id FROM Authors WHERE name=?",array( $name ) );
$id = null;
while( $res->fetchInto( $row ) ) { $id = $row[0] }
return $id;
}
Funktion get_books( $id )
{
global $db;
$res = $db->query( "SELECT id FROM Books WHERE author_id=?",array( $id ) );
$ids = array();
while( $res->fetchInto( $row ) ) { $ids []= $row[0] }
$ids zurückgeben;
}
Funktion get_book( $id )
{
global $db;
$res = $db->query( "SELECT * FROM Books WHERE id=?", array( $id ) );
while( $res->fetchInto( $row ) ) { return $row }
null zurückgeben;
}
$author_id = get_author_id( 'Jack Herrington' );
$books = get_books( $author_id );
foreach( $books as $book_id ) {
$book = get_book( $book_id );
var_dump( $book );
}
?>
Wenn Sie sich den Code unten ansehen, denken Sie vielleicht: „Hey, das ist wirklich klar und einfach.“ Holen Sie sich zuerst die Autoren-ID, dann die Liste der Bücher und dann die Informationen zu jedem Buch. Ja, es ist klar und einfach, aber ist es effizient? Die Antwort ist nein. Sehen Sie, wie viele Abfragen ausgeführt werden, nur um Jack Herringtons Bücher abzurufen. Einmal, um die ID abzurufen, ein anderes Mal, um die Liste der Bücher abzurufen, und dann eine Abfrage pro Buch durchzuführen. Drei Bücher erfordern fünf Abfragen!
Die Lösung besteht darin, eine Funktion zu verwenden, um eine große Anzahl von Abfragen durchzuführen, wie unten gezeigt.
Listing 16. Get_good.php
<?php
require_once('DB.php');
$dsn = 'mysql://root:password@localhost/good_books';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage() }
function get_books( $name )
{
global $db;
$res = $db->query("SELECT Books.* FROM Authors,Books WHERE Books.author_id=Authors.id AND Authors.Name=?",
array( $name ) );
$rows = array();
while( $res->fetchInto( $row ) ) { $rows []= $row }
$rows zurückgeben;
}
$books = get_books( 'Jack Herrington' );
var_dump( $books );
?>
Das Abrufen der Liste erfordert jetzt eine schnelle, einzelne Abfrage. Das bedeutet, dass ich höchstwahrscheinlich mehrere Methoden dieser Art mit unterschiedlichen Parametern benötigen werde, aber es gibt wirklich keine Wahl. Wenn Sie eine skalierbare PHP-Anwendung wünschen, müssen Sie die Datenbank effizient nutzen, was intelligentere Abfragen bedeutet.
Das Problem an diesem Beispiel ist, dass es etwas zu klar ist. Im Allgemeinen sind diese Arten von n+1- oder n*n-Problemen viel subtiler. Und sie erscheinen nur, wenn der Datenbankadministrator Query Profiler auf dem System ausführt, wenn das System Leistungsprobleme hat.
Fazit
Datenbanken sind leistungsstarke Tools, und wie alle leistungsstarken Tools können Sie sie missbrauchen, wenn Sie nicht wissen, wie man sie richtig verwendet. Der Trick zur Identifizierung und Lösung dieser Probleme besteht darin, die zugrunde liegende Technologie besser zu verstehen. Ich habe schon lange gehört, dass Autoren von Geschäftslogiken sich darüber beschweren, dass sie weder Datenbanken noch SQL-Code verstehen wollen. Sie nutzen die Datenbank als Objekt und fragen sich, warum die Leistung so schlecht ist.
Sie erkennen nicht, wie wichtig es ist, SQL zu verstehen, um eine Datenbank von einer schwierigen Notwendigkeit in eine leistungsstarke Allianz zu verwandeln. Wenn Sie täglich Datenbanken verwenden, aber mit SQL nicht vertraut sind, lesen Sie bitte „The Art of SQL“. Dies ist ein gut geschriebenes und praktisches Buch, das Sie zu einem grundlegenden Verständnis von Datenbanken führen kann.