Expose cinq problèmes de base de données courants qui surviennent dans les applications PHP, notamment la conception du schéma de base de données, l'accès à la base de données et le code de logique métier qui utilise la base de données, ainsi que leurs solutions.
Si une seule façon d'utiliser une base de données était correcte...
Il existe de nombreuses façons de créer une conception de base de données, un accès à une base de données et un code de logique métier PHP basé sur une base de données, mais elles finissent généralement par se tromper. Cet article explique cinq problèmes courants qui surviennent dans la conception de bases de données et le code PHP qui accède à la base de données, et comment les résoudre lorsque vous les rencontrez.
Problème 1 : Utiliser MySQL directement
Un problème courant est que l'ancien code PHP utilise directement la fonction mysql_ pour accéder à la base de données. Le listing 1 montre comment accéder directement à la base de données.
Liste 1. Access/get.php
<?php
fonction 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' ) );
?＞
Notez que la fonction mysql_connect est utilisée pour accéder à la base de données. Notez également la requête, qui utilise la concaténation de chaînes pour ajouter le paramètre $name à la requête.
Il existe deux bonnes alternatives à cette technologie : le module PEAR DB et les classes PHP Data Objects (PDO). Les deux fournissent des abstractions à partir de sélections de bases de données spécifiques. Par conséquent, votre code peut s'exécuter sur IBM® DB2®, MySQL, PostgreSQL ou toute autre base de données à laquelle vous souhaitez vous connecter sans trop de modifications.
Un autre avantage de l'utilisation du module PEAR DB et de la couche d'abstraction PDO est que vous pouvez utiliser l'opérateur ? dans les instructions SQL. Cela facilite la maintenance de SQL et protège votre application contre les attaques par injection SQL.
Un code alternatif utilisant PEAR DB est présenté ci-dessous.
Liste 2. Access/get_good.php
<?php
require_once("DB.php");
fonction 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 = nul ;
while( $res->fetchInto( $row ) ) { $id = $row[0] }
return $id;
}
var_dump( get_user_id( 'jack' ) );
?＞
Notez que toutes les utilisations directes de MySQL ont été éliminées, à l'exception de la chaîne de connexion à la base de données dans $dsn. De plus, nous utilisons la variable $name dans SQL via l'opérateur ?. Ensuite, les données de la requête sont envoyées via le tableau à la fin de la méthode query().
Problème 2 : Ne pas utiliser la fonctionnalité d'incrémentation automatique
Comme la plupart des bases de données modernes, MySQL a la capacité de créer des identifiants uniques avec incrémentation automatique pour chaque enregistrement. Au-delà de cela, nous verrons toujours du code qui exécute d'abord une instruction SELECT pour trouver le plus grand identifiant, puis incrémente cet identifiant de 1 et trouve un nouvel enregistrement. Le listing 3 montre un exemple de mauvais modèle.
Listing 3. Badid.sql
DROP TABLE IF EXISTS utilisateurs ;
Utilisateurs CREATE TABLE (
identifiant MEDIUMINT,
connexion TEXTE,
mot de passe TEXTE
);
INSÉRER DANS LES VALEURS des utilisateurs ( 1, 'jack', 'pass' );
INSÉRER DANS LES VALEURS DES Utilisateurs ( 2, 'joan', 'pass' );
INSERT INTO users VALUES (1, 'jane', 'pass' );
Ici, le champ id est simplement spécifié comme un entier. Ainsi, même si elle doit être unique, nous pouvons ajouter n'importe quelle valeur, comme indiqué dans les différentes instructions INSERT qui suivent l'instruction CREATE. Le listing 4 montre le code PHP pour ajouter des utilisateurs à ce type de schéma.
Liste 4. Add_user.php
<?php
require_once("DB.php");
fonction 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 = nul ;
while( $res->fetchInto( $row ) ) { $id = $row[0]; $
id += 1;
$db->prepare( "INSÉRER LES VALEURS DES Utilisateurs(?,?,?) " );
$db->execute( $sth, array( $id, $name, $pass ) );
return $id;
}
$id = add_user( 'jerry', 'pass' );
var_dump( $id );
?＞
Le code dans add_user.php effectue d'abord une requête pour trouver la valeur maximale de id. Le fichier exécute ensuite une instruction INSERT avec la valeur id augmentée de 1. Ce code échouera en cas de concurrence critique sur un serveur très chargé. De plus, c’est également inefficace.
Alors quelle est l’alternative ? Utilisez la fonctionnalité d'incrémentation automatique de MySQL pour créer automatiquement un identifiant unique pour chaque insertion. Le schéma mis à jour ressemble à ceci.
Listing 5. Goodid.php
DROP TABLE IF EXISTS utilisateurs ;
Utilisateurs CREATE TABLE (
identifiant MEDIUMINT NON NULL AUTO_INCREMENT,
connexion TEXTE NON NULL,
mot de passe TEXTE NON NULL,
CLÉ PRIMAIRE (identifiant)
);
INSÉRER DANS LES VALEURS des utilisateurs ( null, 'jack', 'pass' );
INSÉRER DANS LES VALEURS des utilisateurs (null, 'joan', 'pass' );
INSERT INTO users VALUES ( null, 'jane', 'pass' );
Nous avons ajouté l'indicateur NOT NULL pour indiquer que le champ ne doit pas être nul. Nous avons également ajouté l'indicateur AUTO_INCREMENT pour indiquer que le champ est auto-incrémenté, et l'indicateur PRIMARY KEY pour indiquer que le champ est un identifiant. Ces changements accélèrent les choses. Le listing 6 montre le code PHP mis à jour pour insérer l'utilisateur dans la table.
Liste 6. Add_user_good.php
<?php
require_once("DB.php");
fonction 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 = nul ;
while( $res->fetchInto( $row ) ) { $id = $row[0] }
return $id;
}
$id = add_user( 'jerry', 'pass' );
var_dump( $id );
?>
Maintenant, au lieu d'obtenir la valeur d'identifiant maximale, j'utilise directement l'instruction INSERT pour insérer les données, puis j'utilise l'instruction SELECT pour récupérer l'identifiant du dernier enregistrement inséré. Le code est bien plus simple et efficace que la version originale et ses modèles associés.
Question 3 : Utilisation de plusieurs bases de données
Occasionnellement, nous verrons une application où chaque table se trouve dans une base de données distincte. Ceci est raisonnable dans les très grandes bases de données, mais pour les applications générales, ce niveau de partitionnement n'est pas requis. De plus, les requêtes relationnelles ne peuvent pas être effectuées sur des bases de données, ce qui enlève toute idée d'utilisation d'une base de données relationnelle, sans compter qu'il serait plus difficile de gérer des tables sur plusieurs bases de données. Alors, à quoi devraient ressembler plusieurs bases de données ? Tout d’abord, vous avez besoin de quelques données. Le listing 7 montre ces données divisées en quatre fichiers.
Listing 7. Fichier de base de données
Files.sql :
Fichiers CRÉER UNE TABLE (
identifiant MEDIUMINT,
identifiant_utilisateur MOYENINT,
nom TEXTE,
chemin TEXTE
);
Load_files.sql :
INSÉRER DANS LES FICHIERS VALEURS ( 1, 1, 'test1.jpg', 'files/test1.jpg' );
INSÉRER DANS les fichiers VALEURS (2, 1, 'test2.jpg', 'files/test2.jpg' )
;
DROP TABLE SI EXISTE les utilisateurs ;
Utilisateurs CREATE TABLE (
identifiant MEDIUMINT,
connexion TEXTE,
mot de passe TEXTE
);
Load_users.sql :
INSÉRER DANS LES VALEURS des utilisateurs ( 1, 'jack', 'pass' );
INSERT INTO users VALUES (2, 'jon', 'pass' );
Dans la version multi-bases de données de ces fichiers, vous devez charger l'instruction SQL dans une base de données, puis charger l'instruction SQL de l'utilisateur dans une autre base de données. Le code PHP utilisé pour interroger la base de données pour les fichiers associés à un utilisateur spécifique est présenté ci-dessous.
Liste 8. Getfiles.php
<?php
require_once("DB.php");
fonction get_user( $nom )
{
$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 = nul ;
while( $res->fetchInto( $row ) ) { $uid = $row[0] }
return $uid;
}
fonction get_files( $nom )
{
$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 }
renvoie $lignes ;
}
$files = get_files( 'jack' );
var_dump( $files );
?>
La fonction get_user se connecte à la base de données contenant la table users et récupère l'ID d'un utilisateur donné. La fonction get_files se connecte à la table des fichiers et récupère les lignes du fichier associées à un utilisateur donné.
Une meilleure façon de faire toutes ces choses est de charger les données dans une base de données, puis d'exécuter une requête, comme celle ci-dessous.
Liste 9. Getfiles_good.php
<?php
require_once("DB.php");
fonction get_files( $nom )
{
$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 utilisateurs, fichiers OÙ
utilisateurs.login=? ET utilisateurs.id=files.user_id",
tableau( $nom ) );
while( $res->fetchInto( $row ) ) { $rows[] = $row }
return $rows;
}
$files = get_files( 'jack' );
var_dump( $files );
?>
Le code est non seulement plus court, mais aussi plus facile à comprendre et efficace. Au lieu d'exécuter deux requêtes, nous exécutons une seule requête.
Bien que cette question puisse paraître tirée par les cheveux, en pratique, nous concluons généralement que toutes les tables doivent se trouver dans la même base de données, sauf raison très impérieuse.
Question 4 : Ne pas utiliser de relations
Les bases de données relationnelles sont différentes des langages de programmation dans la mesure où elles n'ont pas de types de tableaux. Au lieu de cela, ils utilisent les relations entre les tables pour créer une structure un-à-plusieurs entre les objets, ce qui a le même effet qu'un tableau. Un problème que j'ai rencontré dans les applications est que les ingénieurs tentent d'utiliser la base de données comme un langage de programmation, en créant des tableaux à l'aide de chaînes de texte avec des identifiants séparés par des virgules. Voir le modèle ci-dessous.
Listing 10.Fichiers
Bad.sql
DROP TABLE IF EXISTS ;
Fichiers CRÉER UNE TABLE (
identifiant MEDIUMINT,
nom TEXTE,
chemin TEXTE
);
SUPPRIMER LA TABLE SI EXISTE les utilisateurs ;
Utilisateurs CREATE TABLE (
identifiant MEDIUMINT,
connexion TEXTE,
TEXTE de mot de passe,
fichiers TEXTE
);
INSÉRER DANS les fichiers VALEURS ( 1, 'test1.jpg', 'media/test1.jpg' );
INSÉRER DANS LES FICHIERS VALEURS (2, 'test1.jpg', 'media/test1.jpg' );
INSERT INTO users VALUES (1, 'jack', 'pass', '1,2' );
Un utilisateur dans le système peut avoir plusieurs fichiers. Dans les langages de programmation, un tableau doit être utilisé pour représenter les fichiers associés à un utilisateur. Dans cet exemple, le programmeur choisit de créer un champ de fichiers contenant une liste d'identifiants de fichiers séparés par des virgules. Pour obtenir une liste de tous les fichiers pour un utilisateur particulier, le programmeur doit d'abord lire les lignes de la table des utilisateurs, puis analyser le texte des fichiers et exécuter une instruction SELECT distincte pour chaque fichier. Le code est affiché ci-dessous.
Liste 11. Get.php
<?php
require_once("DB.php");
fonction get_files( $nom )
{
$dsn = 'mysql://root:password@localhost/bad_norel';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage()); }
$res = $db->query( "SÉLECTIONNER les fichiers DES utilisateurs OÙ login=?",array( $name ) ) ;
$fichiers = nul ;
while( $res->fetchInto( $row ) ) { $files = $row[0];
$rows = array();
foreach( split( ',',$files ) as $file )
{
$res = $db->query( "SELECT * FROM files WHERE id=?",
tableau( $fichier ) );
while( $res->fetchInto( $row ) ) { $rows[] = $row }
}
renvoie $lignes ;
}
$files = get_files( 'jack' );
var_dump( $files );
?>
La technologie est lente, difficile à maintenir et ne fait pas bon usage de la base de données. La seule solution consiste à réarchitecturer le schéma pour le reconvertir sous la forme relationnelle traditionnelle, comme indiqué ci-dessous.
Listing 12.
Fichiers Good.sql DROP TABLE IF EXISTS ;
Fichiers CRÉER UNE TABLE (
identifiant MEDIUMINT,
identifiant_utilisateur MOYENINT,
nom TEXTE,
chemin TEXTE
);
SUPPRIMER LA TABLE SI EXISTE les utilisateurs ;
Utilisateurs CREATE TABLE (
identifiant MEDIUMINT,
connexion TEXTE,
mot de passe TEXTE
);
INSÉRER DANS LES VALEURS des utilisateurs ( 1, 'jack', 'pass' );
INSÉRER DANS LES FICHIERS VALEURS ( 1, 1, 'test1.jpg', 'media/test1.jpg' );
INSERT INTO files VALUES (2, 1, 'test1.jpg', 'media/test1.jpg' );
Ici, chaque fichier est lié à l'utilisateur dans la table de fichiers via la fonction user_id. Cela peut aller à l’encontre de quiconque considère plusieurs fichiers comme des tableaux. Bien entendu, les tableaux ne font pas référence aux objets qu’ils contiennent – en fait, vice versa. Mais dans une base de données relationnelle, c'est ainsi que cela fonctionne, et les requêtes sont beaucoup plus rapides et simples grâce à cela. Le listing 13 montre le code PHP correspondant.
Liste 13. Get_good.php
<?php
require_once("DB.php");
fonction get_files( $nom )
{
$dsn = 'mysql://root:password@localhost/good_rel';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage() }
$rows = array();
$res = $db->query("SELECT fichiers.* FROM utilisateurs, fichiers OÙ utilisateurs.login=?
ET users.id=files.user_id",array( $name ) );
while( $res->fetchInto( $row ) ) { $rows[] = $row }
renvoie $lignes ;
}
$files = get_files( 'jack' );
var_dump( $files );
?＞
Ici, nous effectuons une requête à la base de données pour obtenir toutes les lignes. Le code n’est pas complexe et utilise la base de données comme prévu.
Question 5 : Modèle n+1
Je ne peux pas vous dire combien de fois j'ai vu de grandes applications où le code récupère d'abord quelques entités (disons des clients), puis va et vient pour les récupérer une par une pour obtenir chacune les détails de l'entité. Nous appelons ce mode n+1 car la requête est exécutée un grand nombre de fois : une requête récupère la liste de toutes les entités, puis une requête est exécutée pour chacune des n entités. Ce n'est pas un problème lorsque n=10, mais qu'en est-il de n=100 ou n=1000 ? Il y aura alors forcément des inefficacités. Le listing 14 montre un exemple de ce modèle.
Listing 14. Schema.sql
DROP TABLE IF EXISTS auteurs ;
CREATE TABLE auteurs (
identifiant MEDIUMINT NON NULL AUTO_INCREMENT,
nom TEXTE NON NULL,
CLÉ PRIMAIRE (identifiant)
);
SUPPRIMER LA TABLE SI EXISTE des livres ;
CRÉER des livres TABLE (
identifiant MEDIUMINT NON NULL AUTO_INCREMENT,
author_id MEDIUMINT NON NULL,
nom TEXTE NON NULL,
CLÉ PRIMAIRE (identifiant)
);
INSERT INTO auteurs VALUES ( null, 'Jack Herrington' );
INSERT INTO auteurs 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, 'Pragmatic Programmer' );
INSERT INTO books VALUES ( null, 2, 'Ruby on Rails' );
INSERT INTO books VALUES ( null, 2, 'Programming Ruby' );
Le modèle est fiable et ne contient aucune erreur. Le problème réside dans le code qui accède à la base de données pour rechercher tous les livres d'un auteur donné, comme indiqué ci-dessous.
Liste 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 auteurs WHERE name=?",array( $name ) );
$id = nul ;
while( $res->fetchInto( $row ) ) { $id = $row[0];
renvoie $id ;
}
fonction get_books( $id )
{
global $db;
$res = $db->query( "SELECT id FROM books WHERE author_id=?",array( $id ) );
$ids = tableau();
while( $res->fetchInto( $row ) ) { $ids []= $row[0];
renvoie les $ids ;
}
fonction get_book( $id )
{
global $db;
$res = $db->query( "SELECT * FROM livres WHERE id=?", array( $id ) );
while( $res->fetchInto( $row ) ) { return $row;
renvoie null ;
}
$author_id = get_author_id( 'Jack Herrington' );
$books = get_books( $author_id );
foreach( $books comme $book_id ) {
$livre = get_book( $book_id );
var_dump( $livre );
}
?>
Si vous regardez le code ci-dessous, vous pensez peut-être : « Hé, c'est vraiment clair et simple. » Tout d'abord, obtenez l'identifiant de l'auteur, puis obtenez la liste des livres, puis obtenez les informations sur chaque livre. Oui, c'est clair et simple, mais est-ce efficace ? La réponse est non. Découvrez combien de requêtes sont exécutées uniquement pour récupérer les livres de Jack Herrington. Une fois pour obtenir l'identifiant, une autre fois pour obtenir la liste des livres, puis effectuez une requête par livre. Trois livres nécessitent cinq requêtes !
La solution consiste à utiliser une fonction pour effectuer un grand nombre de requêtes comme indiqué ci-dessous.
Liste 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 auteurs,livres O books.author_id=authors.id AND authors.name=?",
tableau( $nom ) );
$lignes = tableau();
while( $res->fetchInto( $row ) ) { $rows []= $row }
renvoie $lignes ;
}
$books = get_books( 'Jack Herrington' );
var_dump( $livres );
?>
La récupération de la liste nécessite désormais une requête unique et rapide. Cela signifie que je devrai probablement utiliser plusieurs méthodes de ce type avec des paramètres différents, mais je n'ai vraiment pas le choix. Si vous souhaitez disposer d'une application PHP évolutive, vous devez utiliser la base de données de manière efficace, ce qui signifie des requêtes plus intelligentes.
Le problème avec cet exemple est qu'il est un peu trop clair. De manière générale, ces types de problèmes n+1 ou n*n sont beaucoup plus subtils. Et ils n'apparaissent que lorsque l'administrateur de base de données exécute Query Profiler sur le système lorsque celui-ci présente des problèmes de performances.
Conclusion
Les bases de données sont des outils puissants et, comme tous les outils puissants, vous pouvez en abuser si vous ne savez pas comment les utiliser correctement. L’astuce pour identifier et résoudre ces problèmes consiste à mieux comprendre la technologie sous-jacente. J'entends depuis longtemps des auteurs de logique métier se plaindre de ne pas vouloir comprendre les bases de données ou le code SQL. Ils utilisent la base de données comme un objet et se demandent pourquoi les performances sont si mauvaises.
Ils ne réalisent pas à quel point la compréhension de SQL est importante pour transformer une base de données d'une nécessité difficile en une alliance puissante. Si vous utilisez des bases de données tous les jours mais n'êtes pas familier avec SQL, veuillez lire The Art of SQL. Il s'agit d'un livre bien écrit et pratique qui peut vous guider vers une compréhension de base des bases de données.