五个常见PHP数据库问题

最新推荐文章于 2023-07-12 18:50:21 发布

cuxiong8996

最新推荐文章于 2023-07-12 18:50:21 发布

阅读量315

点赞数

文章标签：数据库 python mysql java linux

原文链接：https://www.ibm.com/developerworks/opensource/library/os-php-dbmistake/index.html

版权

如果只有一种正确使用数据库的方法...

您可以通过多种方式创建数据库设计，数据库访问以及位于其之上PHP业务逻辑代码，但最终往往会出错。本文说明了数据库设计中的五个常见问题，访问数据库PHP代码以及如何在看到这些问题时解决这些问题。

问题1：直接使用MySQL

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

清单1. Access / get.php

<?php
function 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' ) );
?>

注意使用mysql_connect函数访问数据库。还要注意查询，在该查询中，我们使用字符串串联将$name参数添加到查询中。

该技术有两个不错的选择：PEAR DB模块和PHP数据对象（PDO）类。两者都提供从特定数据库选择中的抽象。因此，您的代码可以在IBM®DB2®，MySQL，PostgreSQL或您要连接的任何其他数据库上运行而无需过多调整。

使用PEAR DB模块和PDO的抽象层的另一个价值是可以使用? SQL语句中的运算符。这样做使SQL易于维护，并使您的应用程序免受SQL注入攻击。

使用PEAR DB的替代代码如下所示。

清单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' ) );
?>

请注意，除了$dsn的数据库连接字符串之外，所有对MySQL的直接提及均已消失。另外，我们通过?在SQL中使用$name变量? 操作员。然后，用于查询的数据在query()方法的末尾通过array发送。

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

像大多数现代数据库一样，MySQL能够在每个记录的基础上创建自动递增的唯一标识符。尽管如此，我们仍然看到代码，该代码首先运行SELECT语句以查找最大id ，然后向该id添加一个，以及一条新记录。清单3显示了一个示例不良模式。

清单3. Badid.sql

DROP TABLE IF EXISTS users;
CREATE TABLE users (
  id MEDIUMINT,
  login TEXT,
  password TEXT
);

INSERT INTO users VALUES ( 1, 'jack', 'pass' );
INSERT INTO users VALUES ( 2, 'joan', 'pass' );
INSERT INTO users VALUES ( 1, 'jane', 'pass' );

这里的id字段仅指定为整数。因此，尽管它应该是唯一的，但是我们可以添加所需的任何值，如CREATE语句之后的INSERT语句所示。清单4显示了将用户添加到这种模式中PHP代码。

清单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;

  $sth = $db->prepare( "INSERT INTO users VALUES(?,?,?)" );
  $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

DROP TABLE IF EXISTS users;
CREATE TABLE users (
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  login TEXT NOT NULL,
  password TEXT NOT NULL,
  PRIMARY KEY( id )
);

INSERT INTO users VALUES ( null, 'jack', 'pass' );
INSERT INTO users VALUES ( null, 'joan', 'pass' );
INSERT INTO users VALUES ( null, 'jane', 'pass' );

我们添加了NOT NULL标志，以指示字段不得为null。我们还添加了AUTO_INCREMENT标志来指示该字段正在自动递增，还添加了PRIMARY KEY标志来指示哪个字段是id 。这些变化加快了速度。清单6显示了将用户插入表中的更新PHP代码。

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

现在，我没有使用最大的id值，而是使用INSERT语句插入数据，然后使用SELECT语句检索最后插入的记录的id 。与原始版本及其相关架构相比，此代码更简单，更高效。

使用MySQL自动递增功能的nextId()一种方法是在PEAR DB系统中使用nextId()方法。对于MySQL，这将创建一个新的序列表，并使用精心设计的锁定机制对其进行管理。使用此方法的优点是它将在不同的数据库系统上工作。

无论哪种方式，您都应该使用一个系统来管理为您递增的唯一ID，而不要依赖您首先查询的系统，然后自己递增值并添加记录。后一种方法容易受到高容量站点上竞争条件的影响。

问题3：使用多个数据库

有时，我们会看到一个应用程序，其中每个表都位于单独的数据库中。在非常大的数据库中这样做是有原因的，但是对于一般的应用程序，您不需要这种级别的分段。另外，即使可以跨多个数据库执行关系查询，我还是强烈建议不要这样做。语法更复杂。备份和还原不容易管理。该语法可能在不同的数据库引擎之间起作用，也可能不起作用。当表拆分到多个数据库时，很难遵循关系结构。

那么，多个数据库是什么样的呢？首先，您需要一些数据。清单7将该数据分为四个文件。

清单7.数据库文件

Files.sql:
CREATE TABLE files (
  id MEDIUMINT,
  user_id MEDIUMINT,
  name TEXT,
  path TEXT
);

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

Users.sql:
DROP TABLE IF EXISTS users;
CREATE TABLE users (
  id MEDIUMINT,
  login TEXT,
  password TEXT
);

Load_users.sql:
INSERT INTO users VALUES ( 1, 'jack', 'pass' );
INSERT INTO users VALUES ( 2, 'jon', 'pass' );

在这些文件的多数据库版本中，您将SQL语句加载到一个数据库中，然后将users SQL语句加载到另一个数据库中。下面显示了用于查询数据库以查找与特定用户关联的文件PHP代码。

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

function 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; }

  return $rows;
}

$files = get_files( 'jack' );

var_dump( $files );
?>

get_user函数连接到包含users表的数据库，并检索给定用户的ID。 get_files函数连接到文件表，并检索与给定用户关联的文件行。

一种更好的方法是将数据加载到一个数据库中，然后执行查询，如下所示。

清单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=? AND users.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

DROP TABLE IF EXISTS files;
CREATE TABLE files (
  id MEDIUMINT,
  name TEXT,
  path TEXT
);

DROP TABLE IF EXISTS users;
CREATE TABLE users (
  id MEDIUMINT,
  login TEXT,
  password TEXT,
  files TEXT
);

INSERT INTO files VALUES ( 1, 'test1.jpg', 'media/test1.jpg' );
INSERT INTO files VALUES ( 2, 'test1.jpg', 'media/test1.jpg' );
INSERT INTO users VALUES ( 1, 'jack', 'pass', '1,2' );

系统中的一个用户可以拥有多个文件。在编程语言中，您将使用数组来表示与用户关联的文件。在此示例中，程序员选择创建一个文件字段，其中包含用逗号分隔的文件id的列表。为了获得特定用户的所有文件的列表，程序员必须首先从users表中读取行，然后解析文件的文本并为每个文件运行单独的SELECT语句。该代码如下所示。

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

该技术速度慢，难以维护，并且没有充分利用数据库。唯一的解决方案是重新架构该架构，以将其转换回传统的关系形式，如下所示。

清单12. Good.sql

DROP TABLE IF EXISTS files;
CREATE TABLE files (
  id MEDIUMINT,
  user_id MEDIUMINT,
  name TEXT,
  path TEXT
);

DROP TABLE IF EXISTS users;
CREATE TABLE users (
  id MEDIUMINT,
  login TEXT,
  password TEXT
);

INSERT INTO users VALUES ( 1, 'jack', 'pass' );
INSERT INTO files VALUES ( 1, 1, 'test1.jpg', 'media/test1.jpg' );
INSERT INTO files VALUES ( 2, 1, 'test1.jpg', 'media/test1.jpg' );

在这里，每个文件都通过文件表中的user_id函数与用户相关。对于任何将其视为数组的人来说，这似乎都是向后的。当然，数组不会引用其包含的对象-实际上，情况恰恰相反。但是在关系数据库中，这就是事情的工作方式以及为什么查询如此之快和容易得多的原因。清单13显示了相应PHP代码。

清单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(
    "SELECT files.* FROM users,files WHERE users.login=?
      AND users.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

DROP TABLE IF EXISTS authors;
CREATE TABLE authors (
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  name TEXT NOT NULL,
  PRIMARY KEY ( id )
);

DROP TABLE IF EXISTS books;
CREATE TABLE books (
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  author_id MEDIUMINT NOT NULL,
  name TEXT NOT NULL,
  PRIMARY KEY ( 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, 'Pragmatic Programmer' );
INSERT INTO books VALUES ( null, 2, 'Ruby on Rails' );
INSERT INTO books VALUES ( null, 2, 'Programming Ruby' );

此架构是可靠的。这里没有错。问题出在访问数据库以查找给定作者的所有书籍的代码中，如下所示。

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

function 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]; }
  return $ids;
}

function get_book( $id )
{
  global $db;

  $res = $db->query( "SELECT * FROM books WHERE id=?", array( $id ) );
  while( $res->fetchInto( $row ) ) { return $row; }
  return null;
}

$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 );
}
?>

如果看一下底部的代码，您可能会自己想：“嘿，这很干净。” 首先，获取作者id ，然后获取书籍列表，然后获取有关每本书的信息。当然，这很干净-但是效率高吗？不。看看我们只检索杰克·赫林顿的书而必须执行的查询数量。一个获取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()); }

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; }
  return $rows;
}

$books = get_books( 'Jack Herrington' );
var_dump( $books );
?>

现在，检索列表需要快速，单一的查询。这意味着我可能不得不使用不同参数的几种这类方法，但是实际上别无选择。如果要具有可扩展PHP应用程序，则必须有效利用数据库，这意味着更智能的查询。

这个示例的问题在于它过于清晰。通常，这些类型的n + 1或n * n问题要微妙得多。并且仅当数据库管理员在系统上出现性能问题时在系统上运行查询分析器时，它们才会出现。

结论

数据库是功能强大的工具，并且-与所有功能强大的工具一样-如果您不知道如何正确使用它们，它们可能会被滥用。识别和解决这些问题的诀窍是更好地了解基础技术。很长时间以来，我听过业务逻辑编码人员感到遗憾的是，他们不想了解数据库或SQL代码。他们将数据库包装在对象中，想知道为什么性能如此差。

他们没有意识到理解SQL是将数据库从困难的需要变成强大的盟友的基础。如果您每天使用数据库，但是SQL不是您的强项，请阅读SQL的艺术。这是一本写得很好的实用指南，可让您充分利用数据库。

翻译自: https://www.ibm.com/developerworks/opensource/library/os-php-dbmistake/index.html