Expone cinco problemas comunes de bases de datos que surgen en las aplicaciones PHP (incluido el diseño del esquema de la base de datos, el acceso a la base de datos y el código de lógica empresarial que utiliza la base de datos) y sus soluciones.
Si solo una forma de usar una base de datos fuera correcta...
Hay muchas maneras de crear diseño de bases de datos, acceso a bases de datos y código de lógica de negocios PHP basado en bases de datos, pero generalmente terminan mal. Este artículo explica cinco problemas comunes que surgen en el diseño de bases de datos y el código PHP que accede a la base de datos, y cómo solucionarlos cuando los encuentra.
Problema 1: Usar MySQL directamente
Un problema común es que el código PHP antiguo usa la función mysql_ directamente para acceder a la base de datos. El Listado 1 muestra cómo acceder a la base de datos directamente.
Listado 1. Acceso/get.php
<?php
función get_user_id($nombre)
{
$db = mysql_connect('localhost', 'raíz', 'contraseña');
mysql_select_db( 'usuarios' );
$res = mysql_query( "SELECCIONE la identificación DE los usuarios DONDE iniciar sesión ='".$nombre."'" );
while( $fila = mysql_fetch_array( $res ) ) { $id = $fila[0] }
return $id;
}
var_dump( get_user_id( 'jack' ) );
?＞
Tenga en cuenta que la función mysql_connect se utiliza para acceder a la base de datos. Tenga en cuenta también la consulta, que utiliza concatenación de cadenas para agregar el parámetro $name a la consulta.
Hay dos buenas alternativas a esta tecnología: el módulo PEAR DB y las clases PHP Data Objects (PDO). Ambos proporcionan abstracciones de selecciones de bases de datos específicas. Como resultado, su código puede ejecutarse en IBM® DB2®, MySQL, PostgreSQL o cualquier otra base de datos a la que desee conectarse sin muchos ajustes.
Otro valor de usar el módulo PEAR DB y la capa de abstracción PDO es que puede usar el operador ? Hacerlo hace que SQL sea más fácil de mantener y protege su aplicación de ataques de inyección SQL.
A continuación se muestra un código alternativo que utiliza PEAR DB.
Listado 2. Acceso/get_good.php
<?php
require_once("DB.php");
función get_user_id($nombre)
{
$dsn = 'mysql://root:contraseña@localhost/usuarios';
$db =& DB::Conectar( $dsn, matriz() );
if (PEAR::isError($db)) { die($db->getMessage() }
$res = $db->query( 'SELECCIONE la identificación DE los usuarios DONDE iniciar sesión=?',array( $nombre ) ) ;
$identificación = nulo;
while( $res->fetchInto( $fila ) ) { $id = $fila[0] }
return $id;
}
var_dump( get_user_id( 'jack' ) );
?＞
Tenga en cuenta que se han eliminado todos los usos directos de MySQL, excepto la cadena de conexión de la base de datos en $dsn. Además, utilizamos la variable $name en SQL a través del operador ? Luego, los datos de la consulta se envían a través de la matriz al final del método query().
Problema 2: No utilizar la función de incremento automático
Como la mayoría de las bases de datos modernas, MySQL tiene la capacidad de crear identificadores únicos de incremento automático por registro. Más allá de eso, seguiremos viendo código que primero ejecuta una instrucción SELECT para encontrar la identificación más grande, luego incrementa esa identificación en 1 y encuentra un nuevo registro. El Listado 3 muestra un ejemplo de patrón incorrecto.
Listado 3. Badid.sql
DROP TABLE SI EXISTE usuarios;
CREAR TABLA usuarios (
id MEDIUMINO,
iniciar sesión TEXTO,
contraseña TEXTO
);
INSERTAR EN LOS VALORES de los usuarios (1, 'jack', 'pass');
INSERTAR EN LOS VALORES de los usuarios (2, 'joan', 'pass');
INSERTAR EN LOS VALORES de los usuarios (1, 'jane', 'pass');
Aquí el campo de identificación simplemente se especifica como un número entero. Entonces, aunque debería ser único, podemos agregar cualquier valor, como se muestra en las diversas declaraciones INSERT que siguen a la declaración CREATE. El Listado 4 muestra el código PHP para agregar usuarios a este tipo de esquema.
Listado 4. Add_user.php
<?php
require_once("DB.php");
función add_user($nombre, $contraseña)
{
$filas = matriz();
$dsn = 'mysql://root:contraseña@localhost/bad_badid';
$db =& DB::Conectar( $dsn, matriz() );
if (PEAR::isError($db)) { die($db->getMessage() }
$res = $db->query( "SELECCIONAR max(id) DE usuarios");
$identificación = nulo;
while( $res->fetchInto( $fila ) ) { $id = $fila[0] }
$id += 1;
$sth = $db->prepare( "INSERTAR EN LOS VALORES de los usuarios(?,?,?) " );
matriz
( $id, $nombre, $contraseña ) );
}
$id = add_user( 'jerry', 'pasar' );
var_dump( $id );
?＞
El código en add_user.php primero realiza una consulta para encontrar el valor máximo de id. Luego, el archivo ejecuta una instrucción INSERT con el valor de identificación aumentado en 1. Este código fallará en una condición de carrera en un servidor muy cargado. Además, también es ineficiente.
Entonces ¿cuál es la alternativa? Utilice la función de incremento automático en MySQL para crear automáticamente una ID única para cada inserción. El esquema actualizado se ve así.
Listado 5. Goodid.php
DROP TABLE SI EXISTE usuarios;
CREAR TABLA usuarios (
id MEDIUMINT NO NULO AUTO_INCREMENT,
iniciar sesión TEXTO NO NULO,
contraseña TEXTO NO NULO,
CLAVE PRIMARIA(id)
);
INSERTAR EN LOS VALORES de los usuarios (nulo, 'jack', 'pass');
INSERTAR EN LOS VALORES de los usuarios (nulo, 'joan', 'pass');
INSERTAR EN LOS VALORES de los usuarios (null, 'jane', 'pass');
agregamos el indicador NOT NULL para indicar que el campo no debe ser nulo. También agregamos el indicador AUTO_INCREMENT para indicar que el campo se incrementa automáticamente y el indicador PRIMARY KEY para indicar que el campo es una identificación. Estos cambios aceleran las cosas. El Listado 6 muestra el código PHP actualizado para insertar al usuario en la tabla.
Listado 6. Add_user_good.php
<?php
require_once("DB.php");
función add_user($nombre, $contraseña)
{
$dsn = 'mysql://root:contraseña@localhost/good_genid';
$db =& DB::Conectar( $dsn, matriz() );
if (PEAR::isError($db)) { die($db->getMessage() }
$sth = $db->prepare( "INSERTAR EN LOS VALORES de los usuarios (nulo,?,?)" );
$db->ejecutar( $algo, matriz( $nombre, $contraseña ) );
$res = $db->query( "SELECCIONAR last_insert_id()" );
$identificación = nulo;
while( $res->fetchInto( $fila ) ) { $id = $fila[0] }
return $id;
}
$id = add_user( 'jerry', 'pasar' );
var_dump( $id );
?>
Ahora, en lugar de obtener el valor de identificación máximo, uso directamente la instrucción INSERT para insertar los datos y luego uso la instrucción SELECT para recuperar la identificación del último registro insertado. El código es mucho más simple y eficiente que la versión original y sus patrones asociados.
Pregunta 3: Uso de múltiples bases de datos
Ocasionalmente, veremos una aplicación donde cada tabla está en una base de datos separada. Esto es razonable en bases de datos muy grandes, pero para aplicaciones generales no se requiere este nivel de partición. Además, las consultas relacionales no se pueden realizar entre bases de datos, lo que elimina la idea de utilizar una base de datos relacional, sin mencionar que sería más difícil administrar tablas en varias bases de datos. Entonces, ¿cómo deberían ser varias bases de datos? Primero, necesitas algunos datos. El Listado 7 muestra dichos datos divididos en cuatro archivos.
Listado 7. Archivo de base de datos
Files.sql:
CREAR archivos de TABLA (
id MEDIUMINO,
user_id MEDIUMINT,
nombre TEXTO,
camino TEXTO
);
cargar_archivos.sql:
INSERTAR EN archivos VALORES (1, 1, 'test1.jpg', 'archivos/test1.jpg');
INSERTAR EN archivos VALORES (2, 1, 'test2.jpg', 'files/test2.jpg' )
;
DROP TABLE SI EXISTE usuarios;
CREAR TABLA usuarios (
id MEDIUMINO,
iniciar sesión TEXTO,
contraseña TEXTO
);
cargar_usuarios.sql:
INSERTAR EN LOS VALORES de los usuarios (1, 'jack', 'pass');
INSERT INTO users VALUES (2, 'jon', 'pass');
En la versión de múltiples bases de datos de estos archivos, debe cargar la declaración SQL en una base de datos y luego cargar la declaración SQL de los usuarios en otra base de datos. El código PHP utilizado para consultar la base de datos en busca de archivos asociados con un usuario específico se muestra a continuación.
Listado 8. Getfiles.php
<?php
require_once("DB.php");
función get_user($nombre)
{
$dsn = 'mysql://root:contraseña@localhost/bad_multi1';
$db =& DB::Conectar( $dsn, matriz() );
if (PEAR::isError($db)) { die($db->getMessage() }
$res = $db->query( "SELECCIONE la identificación DE los usuarios DONDE iniciar sesión =?", matriz ($ nombre)) ;
$uid = nulo;
while( $res->fetchInto( $fila ) ) { $uid = $fila[0];
return $uid;
}
función get_files($nombre)
{
$uid = get_user( $nombre );
$filas = matriz();
$dsn = 'mysql://root:contraseña@localhost/bad_multi2';
$db =& DB::Conectar( $dsn, matriz() );
if (PEAR::isError($db)) { die($db->getMessage() }
$res = $db->query( "SELECCIONAR * DE archivos DONDE user_id=?",array( $uid ) ) ;
while( $res->fetchInto( $fila ) ) { $filas[] = $fila }
devolver $filas;
}
$archivos = get_files( 'jack' );
var_dump( $archivos );
?>
La función get_user se conecta a la base de datos que contiene la tabla de usuarios y recupera la ID de un usuario determinado. La función get_files se conecta a la tabla de archivos y recupera las filas de archivos asociadas con un usuario determinado.
Una mejor manera de hacer todas estas cosas es cargar los datos en una base de datos y luego ejecutar una consulta, como la siguiente.
Listado 9. Getfiles_good.php
<?php
require_once("DB.php");
función get_files($nombre)
{
$filas = matriz();
$dsn = 'mysql://root:contraseña@localhost/good_multi';
$db =& DB::Conectar( $dsn, matriz() );
if (PEAR::isError($db)) { die($db->getMessage() }
$res = $db->query("SELECCIONAR archivos.* DE usuarios, archivos DONDE
usuarios.login=? Y usuarios.id=archivos.user_id",
matriz( $nombre ) );
while( $res->fetchInto( $fila ) ) { $filas[] = $fila }
return $filas;
}
$archivos = get_files( 'jack' );
var_dump( $archivos );
?>
El código no sólo es más corto, sino también más fácil de entender y eficiente. En lugar de ejecutar dos consultas, ejecutamos una consulta.
Aunque esta pregunta puede parecer descabellada, en la práctica solemos concluir que todas las tablas deberían estar en la misma base de datos, a menos que exista una razón muy convincente.
Pregunta 4: No utilizar relaciones
Las bases de datos relacionales se diferencian de los lenguajes de programación en que no tienen tipos de matrices. En cambio, utilizan relaciones entre tablas para crear una estructura de uno a muchos entre objetos, que tiene el mismo efecto que una matriz. Un problema que he visto en las aplicaciones es cuando los ingenieros intentan utilizar la base de datos como un lenguaje de programación, creando matrices utilizando cadenas de texto con identificadores separados por comas. Vea el patrón a continuación.
Listado 10.
Archivos Bad.sql DROP TABLE IF EXISTS;
CREAR archivos de TABLA (
id MEDIUMINO,
nombre TEXTO,
camino TEXTO
);
SOLTAR TABLA SI EXISTE usuarios;
CREAR TABLA usuarios (
id MEDIUMINO,
iniciar sesión TEXTO,
TEXTO de contraseña,
archivos TEXTO
);
INSERTAR EN archivos VALORES (1, 'test1.jpg', 'media/test1.jpg');
INSERTAR EN archivos VALORES (2, 'test1.jpg', 'media/test1.jpg');
INSERTAR EN LOS VALORES de los usuarios (1, 'jack', 'pass', '1,2');
Un usuario en el sistema puede tener varios archivos. En los lenguajes de programación, se debe utilizar una matriz para representar los archivos asociados con un usuario. En este ejemplo, el programador elige crear un campo de archivos que contiene una lista de identificadores de archivos separados por comas. Para obtener una lista de todos los archivos para un usuario en particular, el programador primero debe leer las filas de la tabla de usuarios, luego analizar el texto de los archivos y ejecutar una instrucción SELECT separada para cada archivo. El código se muestra a continuación.
Listado 11. Get.php
<?php
require_once("DB.php");
función get_files($nombre)
{
$dsn = 'mysql://root:contraseña@localhost/bad_norel';
$db =& DB::Conectar( $dsn, matriz() );
if (PEAR::isError($db)) { die($db->getMessage() }
$res = $db->query( "SELECCIONE archivos DE usuarios DONDE iniciar sesión =?",array( $nombre ) ) ;
$archivos = nulo;
while( $res->fetchInto( $fila ) ) { $archivos = $fila[0] } $
filas = matriz();
split( ',',$archivos ) como $archivo )
{
$res = $db->query( "SELECCIONAR * DE archivos DONDE id=?",
matriz( $archivo ) );
while( $res->fetchInto( $fila ) ) { $filas[] = $fila }
}
devolver $filas;
}
$archivos = get_files( 'jack' );
var_dump( $archivos );
?>
La tecnología es lenta, difícil de mantener y no hace un buen uso de la base de datos. La única solución es rediseñar el esquema para convertirlo nuevamente a la forma relacional tradicional como se muestra a continuación.
Listado 12.
Archivos Good.sql DROP TABLE IF EXISTS;
CREAR archivos de TABLA (
id MEDIUMINO,
user_id MEDIUMINT,
nombre TEXTO,
camino TEXTO
);
SOLTAR TABLA SI EXISTE usuarios;
CREAR TABLA usuarios (
id MEDIUMINO,
iniciar sesión TEXTO,
contraseña TEXTO
);
INSERTAR EN LOS VALORES de los usuarios (1, 'jack', 'pass');
INSERTAR EN archivos VALORES (1, 1, 'test1.jpg', 'media/test1.jpg');
INSERT INTO files VALUES (2, 1, 'test1.jpg', 'media/test1.jpg'
Aquí, cada archivo está relacionado con el usuario en la tabla de archivos a través de la función user_id. Esto puede ir en contra de cualquiera que piense en varios archivos como matrices. Por supuesto, las matrices no hacen referencia a los objetos que contienen; de hecho, viceversa. Pero en una base de datos relacional, así es como funciona, y las consultas son mucho más rápidas y sencillas gracias a ello. El Listado 13 muestra el código PHP correspondiente.
Listado 13. Get_good.php
<?php
require_once("DB.php");
función get_files($nombre)
{
$dsn = 'mysql://root:contraseña@localhost/good_rel';
$db =& DB::Conectar( $dsn, matriz() );
if (PEAR::isError($db)) { morir($db->getMessage() }
$filas = matriz());
$res = $db->query("SELECCIONAR archivos.* DE usuarios, archivos DONDE usuarios.iniciar sesión=?
Y usuarios.id=files.user_id",array( $nombre ) );
while( $res->fetchInto( $fila ) ) { $filas[] = $fila }
devolver $filas;
}
$archivos = get_files( 'jack' );
var_dump( $archivos );
?＞
Aquí, realizamos una consulta a la base de datos para obtener todas las filas. El código no es complejo y utiliza la base de datos según lo previsto.
Pregunta 5: Patrón n+1
No puedo decirle cuántas veces he visto aplicaciones grandes donde el código primero recupera algunas entidades (digamos clientes) y luego va y viene para recuperarlas una por una para obtener cada una. Detalles de la entidad. A este modo lo llamamos n+1 porque la consulta se ejecuta muchas veces: una consulta recupera la lista de todas las entidades y luego se ejecuta una consulta para cada una de las n entidades. Esto no es un problema cuando n=10, pero ¿qué pasa con n=100 o n=1000? Entonces seguramente habrá ineficiencias. El Listado 14 muestra un ejemplo de este patrón.
Listado 14. Schema.sql
DROP TABLE SI EXISTE autores;
CREAR TABLA autores (
id MEDIUMINT NO NULO AUTO_INCREMENT,
nombre TEXTO NO NULO,
CLAVE PRIMARIA(id)
);
DROP TABLE SI EXISTE libros;
CREAR TABLA libros (
id MEDIUMINT NO NULO AUTO_INCREMENT,
Author_id MEDIUMINT NO NULO,
nombre TEXTO NO NULO,
CLAVE PRIMARIA(id)
;
INSERTAR EN LOS VALORES de los autores (nulo, 'Jack Herrington');
INSERTAR EN VALORES de autores (nulo, 'Dave Thomas');
INSERTAR EN VALORES de libros (nulo, 1, 'Generación de código en acción');
INSERTAR EN VALORES de libros (nulo, 1, 'Trucos de podcasting');
INSERTAR EN VALORES de libros (nulo, 1, 'Hacks de PHP');
INSERTAR EN VALORES de libros (nulo, 2, 'Programador pragmático');
INSERTAR EN los libros VALORES (nulo, 2, 'Ruby on Rails');
INSERT INTO books VALUES ( null, 2, 'Programming Ruby' );
El patrón es confiable y no contiene errores. El problema radica en el código que accede a la base de datos para encontrar todos los libros de un autor determinado, como se muestra a continuación.
Listado 15. Get.php
<?php
require_once('DB.php');
$dsn = 'mysql://root:contraseña@localhost/good_books';
$db =& DB::Conectar( $dsn, matriz() );
if (PEAR::isError($db)) { morir($db->getMessage() }
función get_author_id( $nombre )
{
global $db;
$res = $db->query( "SELECCIONE id DE autores DONDE nombre =?",array( $nombre ) );
$identificación = nulo;
while( $res->fetchInto( $fila ) ) { $id = $fila[0];
devolver $identificación;
}
función get_books( $id )
{
global $db;
$res = $db->query( "SELECCIONE id DE libros DONDE autor_id=?",array( $id ) );
$identificadores = matriz();
while( $res->fetchInto( $fila ) ) { $ids []= $fila[0];
devolver $identificadores;
}
función get_book ($id)
{
global $db;
$res = $db->query( "SELECCIONAR * DE libros DONDE id=?", matriz( $id ) );
while( $res->fetchInto( $fila ) ) { return $fila }
devolver nulo;
}
$id_autor = get_id_autor( 'Jack Herrington' );
$libros = get_books( $autor_id );
foreach( $libros como $book_id ) {
$libro = get_libro( $libro_id );
var_dump ($libro);
}
?>
Si observa el código a continuación, podría estar pensando: "Oye, esto es realmente claro y simple". Primero, obtenga la identificación del autor, luego obtenga la lista de libros y luego obtenga la información sobre cada libro. Sí, es claro y sencillo, pero ¿es eficiente? La respuesta es no. Vea cuántas consultas se ejecutan solo para recuperar los libros de Jack Herrington. Una vez para obtener la identificación, otra vez para obtener la lista de libros y luego realizar una consulta por libro. ¡Tres libros requieren cinco consultas!
La solución es utilizar una función para realizar una gran cantidad de consultas como se muestra a continuación.
Listado 16. Get_good.php
<?php
require_once('DB.php');
$dsn = 'mysql://root:contraseña@localhost/good_books';
$db =& DB::Conectar( $dsn, matriz() );
if (PEAR::isError($db)) { morir($db->getMessage() }
función get_books( $nombre )
{
global $db;
$res = $db->query("SELECCIONAR libros.* DE autores,libros DONDE libros.author_id=autores.id Y autores.nombre=?",
matriz( $nombre ) );
$filas = matriz();
while( $res->fetchInto( $fila ) ) { $filas []= $fila }
devolver $filas;
}
$libros = get_books( 'Jack Herrington' );
var_dump ($libros);
?>
Recuperar la lista ahora requiere una consulta única y rápida. Esto significa que lo más probable es que tenga que tener varios métodos de este tipo con diferentes parámetros, pero realmente no hay otra opción. Si desea tener una aplicación PHP escalable, debe utilizar la base de datos de manera eficiente, lo que significa consultas más inteligentes.
El problema con este ejemplo es que es demasiado claro. En términos generales, este tipo de problemas n+1 o n*n son mucho más sutiles. Y solo aparecen cuando el administrador de la base de datos ejecuta Query Profiler en el sistema cuando el sistema tiene problemas de rendimiento.
Conclusión
Las bases de datos son herramientas poderosas y, como todas las herramientas poderosas, puedes abusar de ellas si no sabes cómo usarlas correctamente. El truco para identificar y resolver estos problemas es comprender mejor la tecnología subyacente. Hace mucho que escucho a los escritores de lógica de negocios quejarse de que no quieren tener que entender las bases de datos o el código SQL. Usan la base de datos como un objeto y se preguntan por qué el rendimiento es tan pobre.
No se dan cuenta de lo importante que es comprender SQL para transformar una base de datos de una necesidad difícil a una alianza poderosa. Si utiliza bases de datos todos los días pero no está familiarizado con SQL, lea El arte de SQL. Este es un libro práctico y bien escrito que puede guiarlo hacia una comprensión básica de las bases de datos.