Expõe cinco problemas comuns de banco de dados que surgem em aplicativos PHP — incluindo design de esquema de banco de dados, acesso a banco de dados e código de lógica de negócios que usa o banco de dados — e suas soluções.
Se apenas uma maneira de usar um banco de dados fosse correta...
Há muitas maneiras de criar design de banco de dados, acesso a banco de dados e código de lógica de negócios PHP baseado em banco de dados, mas geralmente elas acabam erradas. Este artigo explica cinco problemas comuns que surgem no design do banco de dados e no código PHP que acessa o banco de dados e como corrigi-los quando você os encontrar.
Problema 1: Usando o MySQL diretamente
Um problema comum é que o código PHP mais antigo usa a função mysql_ diretamente para acessar o banco de dados. A Listagem 1 mostra como acessar o banco de dados diretamente.
Listagem 1. Access/get.php
<?php
função get_user_id($nome)
{
$db = mysql_connect( 'localhost', 'root', 'senha' );
mysql_select_db('usuários');
$res = mysql_query( "SELECIONE id FROM usuários WHERE login='".$nome."'" );
while($row = mysql_fetch_array( $res ) ) { $id = $row[0] }
return $id;
}
var_dump(get_user_id('jack'));
?＞
Observe que a função mysql_connect é usada para acessar o banco de dados. Observe também a consulta, que usa concatenação de strings para adicionar o parâmetro $name à consulta.
Existem duas boas alternativas para esta tecnologia: o módulo PEAR DB e as classes PHP Data Objects (PDO). Ambos fornecem abstrações de seleções específicas de bancos de dados. Como resultado, seu código pode ser executado em IBM® DB2®, MySQL, PostgreSQL ou qualquer outro banco de dados ao qual você queira se conectar sem muitos ajustes.
Outro valor de usar o módulo PEAR DB e a camada de abstração PDO é que você pode usar o operador ? Isso facilita a manutenção do SQL e protege seu aplicativo contra ataques de injeção de SQL.
O código alternativo usando PEAR DB é mostrado abaixo.
Listagem 2. Access/get_good.php
<?php
require_once("DB.php");
função get_user_id($nome)
{
$dsn = 'mysql://root:senha@localhost/usuários';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage() }
$res = $db->query( 'SELECT id FROM users WHERE login=?',array( $name ) ) ;
$id = nulo;
while( $res->fetchInto( $row ) ) { $id = $row[0] }
return $id;
}
var_dump(get_user_id('jack'));
?＞
Observe que todos os usos diretos do MySQL foram eliminados, exceto a string de conexão do banco de dados em $dsn. Além disso, usamos a variável $name em SQL por meio do operador ? Em seguida, os dados da consulta são enviados por meio do array no final do método query().
Problema 2: Não usar o recurso de incremento automático
Como a maioria dos bancos de dados modernos, o MySQL tem a capacidade de criar identificadores exclusivos de incremento automático por registro. Além disso, ainda veremos o código que primeiro executa uma instrução SELECT para encontrar o maior id, depois incrementa esse id em 1 e encontra um novo registro. A Listagem 3 mostra um exemplo de padrão incorreto.
Listagem 3. Badid.sql
DROP TABLE IF EXISTS usuários;
CREATE TABLE usuários (
id MEDIUMINT,
entrar TEXTO,
senha TEXTO
);
INSERT INTO usuários VALUES ( 1, 'jack', 'pass' );
INSERT INTO usuários VALUES (2, 'joan', 'pass' );
INSERT INTO users VALUES (1, 'jane', 'pass' );
Aqui o campo id é simplesmente especificado como um número inteiro. Portanto, embora deva ser único, podemos adicionar qualquer valor, conforme mostrado nas diversas instruções INSERT após a instrução CREATE. A Listagem 4 mostra o código PHP para adicionar usuários a esse tipo de esquema.
Listagem 4. Add_user.php
<?php
require_once("DB.php");
função add_user( $nome, $pass )
{
$linhas = array();
$dsn = 'mysql://root:senha@localhost/bad_badid';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage() }
$res = $db->query( "SELECT max(id) FROM users" );
$id = nulo;
while( $res->fetchInto( $row ) ) { $id = $row[0] } $
id += 1
; ");
nome
, $pass ) );
}
$id = add_user('jerry', 'passar')
;
?＞
O código em add_user.php primeiro executa uma consulta para encontrar o valor máximo de id. O arquivo então executa uma instrução INSERT com o valor do id aumentado em 1. Este código falhará em uma condição de corrida em um servidor muito carregado. Além disso, também é ineficiente.
Então, qual é a alternativa? Use o recurso de incremento automático no MySQL para criar automaticamente um ID exclusivo para cada inserção. O esquema atualizado fica assim.
Listagem 5. Goodid.php
DROP TABLE IF EXISTS usuários;
CREATE TABLE usuários (
id MEDIUMINT NÃO NULO AUTO_INCREMENT,
login TEXTO NÃO NULO,
senha TEXTO NÃO NULO,
CHAVE PRIMÁRIA(id)
);
INSERT INTO usuários VALUES ( null, 'jack', 'pass' );
INSERT INTO usuários VALUES (null, 'joan', 'pass' );
INSERT INTO users VALUES ( null, 'jane', 'pass' );
Adicionamos o sinalizador NOT NULL para indicar que o campo não deve ser nulo. Também adicionamos o sinalizador AUTO_INCREMENT para indicar que o campo é incrementado automaticamente e o sinalizador PRIMARY KEY para indicar que o campo é um id. Essas mudanças aceleram as coisas. A Listagem 6 mostra o código PHP atualizado para inserir o usuário na tabela.
Listagem 6. Add_user_good.php
<?php
require_once("DB.php");
função add_user( $nome, $pass )
{
$dsn = 'mysql://root:senha@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( $nome, $pass ) );
$res = $db->query( "SELECT last_insert_id()" );
$id = nulo;
while( $res->fetchInto( $row ) ) { $id = $row[0] }
return $id;
}
$id = add_user('jerry', 'passar')
;
?>
Agora, em vez de obter o valor máximo do id, uso diretamente a instrução INSERT para inserir os dados e, em seguida, uso a instrução SELECT para recuperar o id do último registro inserido. O código é muito mais simples e eficiente que a versão original e seus padrões associados.
Pergunta 3: Usando vários bancos de dados
Ocasionalmente, veremos uma aplicação onde cada tabela está em um banco de dados separado. Isto é razoável em bancos de dados muito grandes, mas para aplicações gerais este nível de particionamento não é necessário. Além disso, consultas relacionais não podem ser realizadas em bancos de dados, o que tira toda a ideia de usar um banco de dados relacional, sem falar que seria mais difícil gerenciar tabelas em vários bancos de dados. Então, como deveriam ser os vários bancos de dados? Primeiro, você precisa de alguns dados. A Listagem 7 mostra esses dados divididos em quatro arquivos.
Listagem 7. Arquivo de banco de dados
Files.sql:
CRIAR arquivos TABLE (
id MEDIUMINT,
user_id MEDIUMINT,
nome TEXTO,
caminho TEXTO
);
Carregar_arquivos.sql:
INSERT INTO arquivos VALUES ( 1, 1, 'test1.jpg', 'files/test1.jpg' );
INSERT INTO arquivos VALUES (2, 1, 'test2.jpg', 'files/test2.jpg' )
;
DROP TABLE SE EXISTE usuários;
CREATE TABLE usuários (
id MEDIUMINT,
entrar TEXTO,
senha TEXTO
);
Load_users.sql:
INSERT INTO usuários VALUES (1, 'jack', 'pass' );
INSERT INTO users VALUES (2, 'jon', 'pass' );
Na versão multi-banco de dados desses arquivos, você deve carregar a instrução SQL em um banco de dados e depois carregar a instrução SQL dos usuários em outro banco de dados. O código PHP usado para consultar o banco de dados em busca de arquivos associados a um usuário específico é mostrado abaixo.
Listagem 8. Getfiles.php
<?php
require_once("DB.php");
função get_user( $nome )
{
$dsn = 'mysql://root:senha@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 = nulo;
while( $res->fetchInto( $row ) ) { $uid = $row[0] }
return $uid;
}
função get_files($nome)
{
$uid = get_user( $nome );
$
linhas = array();
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage() }
$res = $db->query( "SELECT * FROM arquivos WHERE user_id=?",array( $uid ) ) ;
while( $res->fetchInto( $row ) ) { $rows[] = $row };
retornar $ linhas;
}
$arquivos = get_files('jack');
var_dump($arquivos);
?>
A função get_user se conecta ao banco de dados que contém a tabela de usuários e recupera o ID de um determinado usuário. A função get_files se conecta à tabela de arquivos e recupera as linhas do arquivo associadas a um determinado usuário.
A melhor maneira de fazer tudo isso é carregar os dados em um banco de dados e então executar uma consulta, como a mostrada abaixo.
Listagem 9. Getfiles_good.php
<?php
require_once("DB.php");
função get_files($nome)
{
$linhas = array();
$dsn = 'mysql://root:senha@localhost/good_multi';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage() }
$res = $db->query("SELECT arquivos.* FROM usuários, arquivos WHERE
usuários.login=?E usuários.id=arquivos.user_id",
array($nome));
while( $res->fetchInto( $row ) ) { $rows[] = $row }
return $rows;
}
$arquivos = get_files('jack');
var_dump($arquivos);
?>
O código não é apenas mais curto, mas também mais fácil de entender e eficiente. Em vez de executar duas consultas, executamos uma consulta.
Embora esta questão possa parecer absurda, na prática geralmente concluímos que todas as tabelas deveriam estar no mesmo banco de dados, a menos que haja uma razão muito convincente.
Questão 4: Não utilizar relacionamentos
Os bancos de dados relacionais são diferentes das linguagens de programação por não possuírem tipos array. Em vez disso, eles usam relacionamentos entre tabelas para criar uma estrutura um-para-muitos entre objetos, que tem o mesmo efeito que um array. Um problema que tenho visto em aplicações é onde os engenheiros tentam usar o banco de dados como uma linguagem de programação, criando arrays usando strings de texto com identificadores separados por vírgula. Veja o padrão abaixo.
Listagem 10.
Arquivos Bad.sql DROP TABLE IF EXISTS;
CRIAR arquivos TABLE (
id MEDIUMINT,
nome TEXTO,
caminho TEXTO
);
DROP TABLE SE EXISTE usuários;
CREATE TABLE usuários (
id MEDIUMINT,
entrar TEXTO,
senha TEXTO,
arquivos TEXTO
);
INSERIR EM arquivos VALUES ( 1, 'test1.jpg', 'media/test1.jpg' );
INSERT INTO arquivos VALUES (2, 'test1.jpg', 'media/test1.jpg' );
,
'pass', '1,2' );
Em linguagens de programação, um array deve ser usado para representar os arquivos associados a um usuário. Neste exemplo, o programador escolhe criar um campo de arquivos que contém uma lista de IDs de arquivos separados por vírgula. Para obter uma lista de todos os arquivos de um usuário específico, o programador deve primeiro ler as linhas da tabela de usuários, depois analisar o texto dos arquivos e executar uma instrução SELECT separada para cada arquivo. O código é mostrado abaixo.
Listagem 11. Get.php
<?php
require_once("DB.php");
função get_files($nome)
{
$dsn = 'mysql://root:senha@localhost/bad_norel';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage() }
$res = $db->query( "SELECIONE arquivos FROM usuários WHERE login=?",array( $name ) ) ;
$arquivos = null;
while( $res->fetchInto( $row ) ) { $files = $row[0] }
$rows = array()
;
{
$res = $db->query( "SELECT * FROM arquivos WHERE id=?",
array($arquivo));
while( $res->fetchInto( $row ) ) { $rows[] = $row };
}
retornar $ linhas;
}
$arquivos = get_files('jack');
var_dump($arquivos);
?>
A tecnologia é lenta, difícil de manter e não faz bom uso do banco de dados. A única solução é reprojetar o esquema para convertê-lo de volta à forma relacional tradicional, conforme mostrado abaixo.
Listagem 12.
Arquivos Good.sql DROP TABLE IF EXISTS;
CRIAR arquivos TABLE (
id MEDIUMINT,
user_id MEDIUMINT,
nome TEXTO,
caminho TEXTO
);
DROP TABLE SE EXISTE usuários;
CREATE TABLE usuários (
id MEDIUMINT,
entrar TEXTO,
senha TEXTO
);
INSERT INTO usuários VALUES ( 1, 'jack', 'pass' );
INSERIR EM arquivos VALUES ( 1, 1, 'test1.jpg', 'media/test1.jpg' );
INSERT INTO files VALUES (2, 1, 'test1.jpg', 'media/test1.jpg' );
Aqui, cada arquivo é relacionado ao usuário na tabela de arquivos através da função user_id. Isso pode ir contra a tendência de quem pensa em vários arquivos como matrizes. É claro que os arrays não fazem referência aos objetos que contêm — na verdade, vice-versa. Mas em um banco de dados relacional é assim que funciona, e as consultas são muito mais rápidas e simples por causa disso. A Listagem 13 mostra o código PHP correspondente.
Listagem 13. Get_good.php
<?php
require_once("DB.php");
função get_files($nome)
{
$dsn = 'mysql://root:senha@localhost/good_rel';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage() }
$linhas = array());
$res = $db->query("SELECIONE arquivos.* FROM usuários,arquivos WHERE usuários.login=?
AND users.id=files.user_id",array( $nome ) );
while( $res->fetchInto( $row ) ) { $rows[] = $row };
retornar $ linhas;
}
$arquivos = get_files('jack');
var_dump($arquivos);
?＞
Aqui, fazemos uma consulta ao banco de dados para obter todas as linhas. O código não é complexo e usa o banco de dados conforme pretendido.
Pergunta 5: Padrão n+1
Não sei dizer quantas vezes vi aplicativos grandes em que o código primeiro recupera algumas entidades (digamos, clientes) e depois vai e volta para recuperá-las uma por uma para obter cada Detalhes da entidade. Chamamos esse modo n+1 porque a consulta é executada muitas vezes - uma consulta recupera a lista de todas as entidades e, em seguida, uma consulta é executada para cada uma das n entidades. Isso não é um problema quando n=10, mas e n=100 ou n=1000? Então, certamente haverá ineficiências. A Listagem 14 mostra um exemplo desse padrão.
Listagem 14. Schema.sql
DROP TABLE IF EXISTS autores;
CREATE TABLE autores (
id MEDIUMINT NÃO NULO AUTO_INCREMENT,
nome TEXTO NÃO NULO,
CHAVE PRIMÁRIA(id)
);
DROP TABLE SE EXISTE livros;
CRIAR TABELA livros (
id MEDIUMINT NÃO NULO AUTO_INCREMENT,
autor_id MEDIUMINT NÃO NULO,
nome TEXTO NÃO NULO,
CHAVE PRIMÁRIA(id)
);
INSERT INTO autores VALUES ( null, 'Jack Herrington' );
INSERT INTO autores VALUES ( null, 'Dave Thomas' );
INSERT INTO livros VALUES ( null, 1, 'Code Generation in Action' );
INSERT INTO livros VALUES ( null, 1, 'Podcasting Hacks' );
INSERT INTO livros VALUES ( null, 1, 'PHP Hacks' );
INSERT INTO livros VALUES ( null, 2, 'Pragmatic Programmer' );
INSERT INTO livros VALUES ( null, 2, 'Ruby on Rails' );
INSERT INTO books VALUES ( null, 2, 'Programming Ruby' );
O padrão é confiável e não há erros nele. O problema está no código que acessa o banco de dados para localizar todos os livros de um determinado autor, conforme mostrado abaixo.
Listagem 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( "SELECIONE id FROM autores WHERE nome=?",array( $nome ) );
$id = nulo;
while( $res->fetchInto( $row ) ) { $id = $row[0] };
retornar $id;
}
função 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] };
retornar $ids;
}
função get_book($id)
{
global $db;
$res = $db->query( "SELECT * FROM livros WHERE id=?", array( $id ) );
while($res->fetchInto($row)) { return $row};
retornar nulo;
}
$author_id = get_author_id( 'Jack Herrington' );
$livros = get_books( $author_id );
foreach($livros as $book_id) {
$livro = get_livro( $livro_id );
var_dump($livro);
}
?>
Se você olhar o código abaixo, poderá estar pensando: "Ei, isso é muito claro e simples." Primeiro, obtenha o ID do autor, depois obtenha a lista de livros e, em seguida, obtenha as informações sobre cada livro. Sim, é claro e simples, mas é eficiente? A resposta é não. Veja quantas consultas são executadas apenas para recuperar os livros de Jack Herrington. Uma vez para obter o id, outra vez para obter a lista de livros e depois realizar uma consulta por livro. Três livros requerem cinco consultas!
A solução é usar uma função para realizar um grande número de consultas conforme mostrado abaixo.
Listagem 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() }
função get_books( $nome ))
;
{
global $db;
$res = $db->query("SELECIONE livros.* FROM autores,livros WHERE livros.autor_id=autores.id AND autores.nome=?",
array($nome));
$linhas = array();
while( $res->fetchInto( $row ) ) { $rows []= $row };
retornar $ linhas;
}
$livros = get_books( 'Jack Herrington' );
var_dump($livros);
?>
Recuperar a lista agora requer uma consulta rápida e única. Isso significa que provavelmente terei que ter vários métodos desses tipos com parâmetros diferentes, mas realmente não há escolha. Se você deseja ter uma aplicação PHP escalável, deve usar o banco de dados de forma eficiente, o que significa consultas mais inteligentes.
O problema com este exemplo é que ele é um pouco claro demais. De modo geral, esses tipos de problemas n+1 ou n*n são muito mais sutis. E eles só aparecem quando o administrador do banco de dados executa o Query Profiler no sistema quando o sistema apresenta problemas de desempenho.
Conclusão
Os bancos de dados são ferramentas poderosas e, como todas as ferramentas poderosas, você pode abusar deles se não souber como usá-los corretamente. O truque para identificar e resolver esses problemas é compreender melhor a tecnologia subjacente. Há muito tempo ouço escritores de lógica de negócios reclamarem que não querem entender bancos de dados ou código SQL. Eles usam o banco de dados como um objeto e se perguntam por que o desempenho é tão ruim.
Eles não conseguem perceber o quão importante é a compreensão do SQL para transformar um banco de dados de uma necessidade difícil em uma aliança poderosa. Se você usa bancos de dados todos os dias, mas não está familiarizado com SQL, leia The Art of SQL. Este é um livro prático e bem escrito que pode guiá-lo para uma compreensão básica de bancos de dados.