揭露PHP应用程序中出现的五个常见数据库问题——包括数据库模式设计、数据库访问和使用数据库的业务逻辑代码——以及它们的解决方案。

如果只有一种方式使用数据库是正确的……

您可以用很多的方式创建数据库设计、数据库访问和基于数据库的PHP业务逻辑代码，但最终一般以错误告终。本文说明了数据库设计和访问数据库的PHP代码中出现的五个常见问题，以及在遇到这些问题时如何修复它们。

问题1：直接使用MySQL

一个常见问题是较老的PHP代码直接使用mysql_函数来访问数据库。清单1展示了如何直接访问数据库。

清单1.Access/get.php

  ＜?php
functionget_user_id($name)
{
　$db=mysql_connect('localhost','root','password');
　mysql_select_db('users');

　$res=mysql_query("SELECTidFROMusersWHERElogin='".$name."'");
　while($row=mysql_fetch_array($res)){$id=$row[0];}

　return$id;
}

var_dump(get_user_id('jack'));
?＞
注意使用了mysql_connect函数来访问数据库。还要注意查询，其中使用字符串连接来向查询添加$name参数。

该技术有两个很好的替代方案：PEARDB模块和PHPDataObjects(PDO)类。两者都从特定数据库选择提供抽象。因此，您的代码无需太多调整就可以在IBM?DB2?、MySQL、PostgreSQL或者您想要连接到的任何其他数据库上运行。

使用PEARDB模块和PDO抽象层的另一个价值在于您可以在SQL语句中使用?操作符。这样做可使SQL更加易于维护，且可使您的应用程序免受SQL注入攻击。

使用PEARDB的替代代码如下所示。

清单2.Access/get_good.php

  ＜?php
require_once("DB.php");

functionget_user_id($name)
{
　$dsn='mysql://root:password@localhost/users';
　$db=&DB::Connect($dsn,array());
　if(PEAR::isError($db)){die($db-＞getMessage());}

　$res=$db-＞query('SELECTidFROMusersWHERElogin=?',array($name));
　$id=null;
　while($res-＞fetchInto($row)){$id=$row[0];}

　return$id;
}

var_dump(get_user_id('jack'));
?＞
注意，所有直接用到MySQL的地方都消除了，只有$dsn中的数据库连接字符串除外。此外，我们通过?操作符在SQL中使用$name变量。然后，查询的数据通过query()方法末尾的array被发送进来。

问题2：不使用自动增量功能

与大多数现代数据库一样，MySQL能够在每记录的基础上创建自动增量惟一标识符。除此之外，我们仍然会看到这样的代码，即首先运行一个SELECT语句来找到最大的id，然后将该id增1，并找到一个新记录。清单3展示了一个示例坏模式。

清单3.Badid.sql

  DROPTABLEIFEXISTSusers;
CREATETABLEusers(
idMEDIUMINT,
loginTEXT,
passwordTEXT
);

INSERTINTOusersVALUES(1,'jack','pass');
INSERTINTOusersVALUES(2,'joan','pass');
INSERTINTOusersVALUES(1,'jane','pass');
这里的id字段被简单地指定为整数。所以，尽管它应该是惟一的，我们还是可以添加任何值，如CREATE语句后面的几个INSERT语句中所示。清单4展示了将用户添加到这种类型的模式的PHP代码。

清单4.Add_user.php

  ＜?php
require_once("DB.php");

functionadd_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("SELECTmax(id)FROMusers");
　$id=null;
　while($res-＞fetchInto($row)){$id=$row[0];}

　$id+=1;

　$sth=$db-＞prepare("INSERTINTOusersVALUES(?,?,?)");
　$db-＞execute($sth,array($id,$name,$pass));

　return$id;
}

$id=add_user('jerry','pass');

var_dump($id);
?＞
add_user.php中的代码首先执行一个查询以找到id的最大值。然后文件以id值加1运行一个INSERT语句。该代码在负载很重的服务器上会在竞态条件中失败。另外，它也效率低下。

那么替代方案是什么呢？使用MySQL中的自动增量特性来自动地为每个插入创建惟一的ID。更新后的模式如下所示。

清单5.Goodid.php

  DROPTABLEIFEXISTSusers;
CREATETABLEusers(
　idMEDIUMINTNOTNULLAUTO_INCREMENT,
　loginTEXTNOTNULL,
　passwordTEXTNOTNULL,
　PRIMARYKEY(id)
);

INSERTINTOusersVALUES(null,'jack','pass');
INSERTINTOusersVALUES(null,'joan','pass');
INSERTINTOusersVALUES(null,'jane','pass');
我们添加了NOTNULL标志来指示字段必须不能为空。我们还添加了AUTO_INCREMENT标志来指示字段是自动增量的，添加PRIMARYKEY标志来指示那个字段是一个id。这些更改加快了速度。清单6展示了更新后的PHP代码，即将用户插入表中。

清单6.Add_user_good.php

  ＜?php
require_once("DB.php");

functionadd_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("INSERTINTOusersVALUES(null,?,?)");
　$db-＞execute($sth,array($name,$pass));

　$res=$db-＞query("SELECTlast_insert_id()");
　$id=null;
　while($res-＞fetchInto($row)){$id=$row[0];}

　return$id;
}

$id=add_user('jerry','pass');

var_dump($id);
?＞

现在我不是获得最大的id值，而是直接使用INSERT语句来插入数据，然后使用SELECT语句来检索最后插入的记录的id。该代码比最初的版本及其相关模式要简单得多，且效率更高。

 

 

问题3：使用多个数据库

偶尔，我们会看到一个应用程序中，每个表都在一个单独的数据库中。在非常大的数据库中这样做是合理的，但是对于一般的应用程序，则不需要这种级别的分割。此外，不能跨数据库执行关系查询，这会影响使用关系数据库的整体思想，更不用说跨多个数据库管理表会更困难了。那么，多个数据库应该是什么样的呢？首先，您需要一些数据。清单7展示了分成4个文件的这样的数据。

清单7.数据库文件

  Files.sql:
CREATETABLEfiles(
　idMEDIUMINT,
　user_idMEDIUMINT,
　nameTEXT,
　pathTEXT
);

Load_files.sql:
INSERTINTOfilesVALUES(1,1,'test1.jpg','files/test1.jpg');
INSERTINTOfilesVALUES(2,1,'test2.jpg','files/test2.jpg');

Users.sql:
DROPTABLEIFEXISTSusers;
CREATETABLEusers(
　idMEDIUMINT,
　loginTEXT,
　passwordTEXT
);

Load_users.sql:
INSERTINTOusersVALUES(1,'jack','pass');
INSERTINTOusersVALUES(2,'jon','pass');
在这些文件的多数据库版本中，您应该将SQL语句加载到一个数据库中，然后将usersSQL语句加载到另一个数据库中。用于在数据库中查询与某个特定用户相关联的文件的PHP代码如下所示。

清单8.Getfiles.php

  ＜?php
require_once("DB.php");

functionget_user($name)
{
　$dsn='mysql://root:password@localhost/bad_multi1';
　$db=&DB::Connect($dsn,array());
　if(PEAR::isError($db)){die($db-＞getMessage());}

　$res=$db-＞query("SELECTidFROMusersWHERElogin=?",array($name));
　$uid=null;
　while($res-＞fetchInto($row)){$uid=$row[0];}

　return$uid;
}

functionget_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*FROMfilesWHEREuser_id=?",array($uid));
　while($res-＞fetchInto($row)){$rows[]=$row;}
　return$rows;
}

$files=get_files('jack');

var_dump($files);
?＞
get_user函数连接到包含用户表的数据库并检索给定用户的ID。get_files函数连接到文件表并检索与给定用户相关联的文件行。

做所有这些事情的一个更好办法是将数据加载到一个数据库中，然后执行查询，比如下面的查询。

清单9.Getfiles_good.php

  ＜?php
require_once("DB.php");

functionget_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("SELECTfiles.*FROMusers,filesWHERE
users.login=?ANDusers.id=files.user_id",
array($name));
　while($res-＞fetchInto($row)){$rows[]=$row;}

　return$rows;
}

$files=get_files('jack');

var_dump($files);
?＞

该代码不仅更短，而且也更容易理解和高效。我们不是执行两个查询，而是执行一个查询。

尽管该问题听起来有些牵强，但是在实践中我们通常总结出所有的表应该在同一个数据库中，除非有非常迫不得已的理由。

 

 

问题4：不使用关系

关系数据库不同于编程语言，它们不具有数组类型。相反，它们使用表之间的关系来创建对象之间的一到多结构，这与数组具有相同的效果。我在应用程序中看到的一个问题是，工程师试图将数据库当作编程语言来使用，即通过使用具有逗号分隔的标识符的文本字符串来创建数组。请看下面的模式。

清单10.Bad.sql

  DROPTABLEIFEXISTSfiles;
CREATETABLEfiles(
　idMEDIUMINT,
　nameTEXT,
　pathTEXT
);

DROPTABLEIFEXISTSusers;
CREATETABLEusers(
　idMEDIUMINT,
　loginTEXT,
　passwordTEXT,
　filesTEXT
);

INSERTINTOfilesVALUES(1,'test1.jpg','media/test1.jpg');
INSERTINTOfilesVALUES(2,'test1.jpg','media/test1.jpg');
INSERTINTOusersVALUES(1,'jack','pass','1,2');
系统中的一个用户可以具有多个文件。在编程语言中，应该使用数组来表示与一个用户相关联的文件。在本例中，程序员选择创建一个files字段，其中包含一个由逗号分隔的文件id列表。要得到一个特定用户的所有文件的列表，程序员必须首先从用户表中读取行，然后解析文件的文本，并为每个文件运行一个单独的SELECT语句。该代码如下所示。

清单11.Get.php

  ＜?php
require_once("DB.php");

functionget_files($name)
{
　$dsn='mysql://root:password@localhost/bad_norel';
　$db=&DB::Connect($dsn,array());
　if(PEAR::isError($db)){die($db-＞getMessage());}

　$res=$db-＞query("SELECTfilesFROMusersWHERElogin=?",array($name));
　$files=null;
　while($res-＞fetchInto($row)){$files=$row[0];}

　$rows=array();

　foreach(split(',',$files)as$file)
　{
$res=$db-＞query("SELECT*FROMfilesWHEREid=?",
array($file));
while($res-＞fetchInto($row)){$rows[]=$row;}
　}

　return$rows;
}

$files=get_files('jack');

var_dump($files);
?＞
该技术很慢，难以维护，且没有很好地利用数据库。惟一的解决方案是重新架构模式，以将其转换回到传统的关系形式，如下所示。

清单12.Good.sql

  DROPTABLEIFEXISTSfiles;
CREATETABLEfiles(
　idMEDIUMINT,
　user_idMEDIUMINT,
　nameTEXT,
　pathTEXT
);

DROPTABLEIFEXISTSusers;
CREATETABLEusers(
　idMEDIUMINT,
　loginTEXT,
　passwordTEXT
);

INSERTINTOusersVALUES(1,'jack','pass');
INSERTINTOfilesVALUES(1,1,'test1.jpg','media/test1.jpg');
INSERTINTOfilesVALUES(2,1,'test1.jpg','media/test1.jpg');
这里，每个文件都通过user_id函数与文件表中的用户相关。这可能与任何将多个文件看成数组的人的思想相反。当然，数组不引用其包含的对象——事实上，反之亦然。但是在关系数据库中，工作原理就是这样的，并且查询也因此要快速且简单得多。清单13展示了相应的PHP代码。

清单13.Get_good.php

  ＜?php
require_once("DB.php");

functionget_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("SELECTfiles.*FROMusers,filesWHEREusers.login=?
ANDusers.id=files.user_id",array($name));
　while($res-＞fetchInto($row)){$rows[]=$row;}
　return$rows;
}

$files=get_files('jack');

var_dump($files);
?＞

这里，我们对数据库进行一次查询，以获得所有的行。代码不复杂，并且它将数据库作为其原有的用途使用。

 

 

问题5：n+1模式

我真不知有多少次看到过这样的大型应用程序，其中的代码首先检索一些实体（比如说客户），然后来回地一个一个地检索它们，以得到每个实体的详细信息。我们将其称为n+1模式，因为查询要执行这么多次——一次查询检索所有实体的列表，然后对于n个实体中的每一个执行一次查询。当n=10时这还不成其为问题，但是当n=100或n=1000时呢？然后肯定会出现低效率问题。清单14展示了这种模式的一个例子。

清单14.Schema.sql

  DROPTABLEIFEXISTSauthors;
CREATETABLEauthors(
　idMEDIUMINTNOTNULLAUTO_INCREMENT,
　nameTEXTNOTNULL,
　PRIMARYKEY(id)
);

DROPTABLEIFEXISTSbooks;
CREATETABLEbooks(
　idMEDIUMINTNOTNULLAUTO_INCREMENT,
　author_idMEDIUMINTNOTNULL,
　nameTEXTNOTNULL,
　PRIMARYKEY(id)
);

INSERTINTOauthorsVALUES(null,'JackHerrington');
INSERTINTOauthorsVALUES(null,'DaveThomas');

INSERTINTObooksVALUES(null,1,'CodeGenerationinAction');
INSERTINTObooksVALUES(null,1,'PodcastingHacks');
INSERTINTObooksVALUES(null,1,'PHPHacks');
INSERTINTObooksVALUES(null,2,'PragmaticProgrammer');
INSERTINTObooksVALUES(null,2,'Rubyalign=centerbgColor=#e7e9e9border=1>   ＜?php
require_once('DB.php');

$dsn='mysql://root:password@localhost/good_books';
$db=&DB::Connect($dsn,array());
if(PEAR::isError($db)){die($db-＞getMessage());}

functionget_author_id($name)
{
　global$db;

　$res=$db-＞query("SELECTidFROMauthorsWHEREname=?",array($name));
　$id=null;
　while($res-＞fetchInto($row)){$id=$row[0];}
　return$id;
}

functionget_books($id)
{
　global$db;

　$res=$db-＞query("SELECTidFROMbooksWHEREauthor_id=?",array($id));
　$ids=array();
　while($res-＞fetchInto($row)){$ids[]=$row[0];}
　return$ids;
}

functionget_book($id)
{
　global$db;

　$res=$db-＞query("SELECT*FROMbooksWHEREid=?",array($id));
　while($res-＞fetchInto($row)){return$row;}
　returnnull;
}

$author_id=get_author_id('JackHerrington');
$books=get_books($author_id);
foreach($booksas$book_id){
　$book=get_book($book_id);
　var_dump($book);
}
?＞
如果您看看下面的代码，您可能会想，“嘿，这才是真正的清楚明了。”首先，得到作者id，然后得到书籍列表，然后得到有关每本书的信息。的确，它很清楚明了，但是其高效吗？回答是否定的。看看只是检索JackHerrington的书籍时要执行多少次查询。一次获得id，另一次获得书籍列表，然后每本书执行一次查询。三本书要执行五次查询！

解决方案是用一个函数来执行大量的查询，如下所示。

清单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());}

functionget_books($name)
{
　global$db;

　$res=$db-＞query("SELECTbooks.*FROMauthors,booksWHEREbooks.author_id=authors.idANDauthors.name=?",
　array($name));
　$rows=array();
　while($res-＞fetchInto($row)){$rows[]=$row;}
return$rows;
　}

　$books=get_books('JackHerrington');
　var_dump($books);
?＞
现在检索列表需要一个快速、单个的查询。这意味着我将很可能必须具有几个这些类型的具有不同参数的方法，但是实在是没有选择。如果您想要具有一个扩展的PHP应用程序，那么必须有效地使用数据库，这意味着更智能的查询。

本例的问题是它有点太清晰了。通常来说，这些类型的n+1或n*n问题要微妙得多。并且它们只有在数据库管理员在系统具有性能问题时在系统上运行查询剖析器时才会出现。

结束语

数据库是强大的工具，就跟所有强大的工具一样，如果您不知道如何正确地使用就会滥用它们。识别和解决这些问题的诀窍是更好地理解底层技术。长期以来，我老听到业务逻辑编写人员抱怨，他们不想要必须理解数据库或SQL代码。他们把数据库当成对象使用，并疑惑性能为什么如此之差。

他们没有认识到，理解SQL对于将数据库从一个困难的必需品转换成强大的联盟是多么重要。如果您每天使用数据库，但是不熟悉SQL，那么请阅读TheArtofSQL，这本书写得很好，实践性也很强，可以指导您基本了解数据库。

评论 (0) All

登陆 还没注册？