Раскрывает пять распространенных проблем с базами данных, возникающих в приложениях PHP, включая проектирование схемы базы данных, доступ к базе данных и код бизнес-логики, использующий базу данных, а также их решения.
Если бы только один способ использования базы данных был правильным...
Существует множество способов создания дизайна базы данных, доступа к базе данных и кода бизнес-логики PHP на основе базы данных, но обычно они оказываются неверными. В этой статье объясняются пять распространенных проблем, возникающих при проектировании базы данных и PHP-коде, осуществляющем доступ к базе данных, а также способы их устранения при возникновении.
Проблема 1. Непосредственное использование MySQL.
Распространенной проблемой является то, что старый PHP-код использует функцию mysql_ напрямую для доступа к базе данных. В листинге 1 показано, как получить прямой доступ к базе данных.
Листинг 1. Access/get.php
<?php
функция get_user_id($name)
{
$db = mysql_connect('localhost', 'root', 'пароль');
mysql_select_db( 'users');
$res = mysql_query( "ВЫБРАТЬ идентификатор ИЗ пользователей WHERE login='".$name."'" );
while ($row = mysql_fetch_array($res)) {$id = $row[0]}
return $id;
}
var_dump(get_user_id('джек'));
?＞
Обратите внимание, что функция mysql_connect используется для доступа к базе данных. Также обратите внимание на запрос, который использует конкатенацию строк для добавления в запрос параметра $name.
Есть две хорошие альтернативы этой технологии: модуль PEAR DB и классы PHP Data Objects (PDO). Оба предоставляют абстракции от конкретных вариантов выбора базы данных. В результате ваш код может работать в IBM® DB2®, MySQL, PostgreSQL или любой другой базе данных, к которой вы хотите подключиться, без особых настроек.
Еще одна ценность использования модуля PEAR DB и уровня абстракции PDO заключается в том, что вы можете использовать оператор ? Это упрощает поддержку SQL и защищает ваше приложение от атак с использованием SQL-инъекций.
Альтернативный код с использованием PEAR DB показан ниже.
Листинг 2. Access/get_good.php
<?php
require_once("DB.php");
функция get_user_id($name)
{
$dsn = 'mysql://root:пароль@localhost/users';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage() }
$res = $db->query( 'ВЫБРАТЬ id FROM пользователей WHERE login=?',array($name) ) ;
$id = ноль;
while ($res->fetchInto($row)) {$id = $row[0]}
return $id;
}
var_dump(get_user_id('джек'));
?＞
Обратите внимание, что все прямое использование MySQL было исключено, за исключением строки подключения к базе данных в $dsn. Кроме того, мы используем переменную $name в SQL через оператор ? Затем данные запроса передаются через массив в конце метода query().
Проблема 2: Неиспользование функции автоматического приращения.
Как и большинство современных баз данных, MySQL имеет возможность создавать уникальные идентификаторы с автоматическим приращением для каждой записи. Помимо этого, мы по-прежнему увидим код, который сначала запускает инструкцию SELECT, чтобы найти наибольший идентификатор, затем увеличивает этот идентификатор на 1 и находит новую запись. В листинге 3 показан пример неправильного шаблона.
Листинг 3. Badid.sql
УДАЛЕНИЕ ТАБЛИЦЫ ЕСЛИ СУЩЕСТВУЕТ пользователей;
СОЗДАТЬ ТАБЛИЦУ пользователей (
идентификатор СРЕДНИЙ,
вход ТЕКСТ,
пароль ТЕКСТ
);
INSERT INTO user VALUES ( 1, 'jack', 'pass');
ВСТАВИТЬ В ЗНАЧЕНИЯ пользователей (2, 'Джоан', 'пройти');
INSERT INTO user VALUES (1, 'jane', 'pass' );
Здесь поле идентификатора просто указывается как целое число. Таким образом, хотя оно должно быть уникальным, мы можем добавить любое значение, как показано в нескольких операторах INSERT, следующих за оператором CREATE. В листинге 4 показан код PHP для добавления пользователей в схему этого типа.
Листинг 4. Add_user.php
<?php
require_once("DB.php");
функция 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) FROMusers");
$id = ноль;
while( $res->fetchInto( $row ) ) { $id = $row[0] } $
id += 1;
$sth = $db->prepare( "INSERT INTO user VALUES(?,?,?) ");
$db->execute($sth, array($id, $name, $pass));
return $id;
}
$id = add_user('Джерри', 'пропустить');
var_dump($id);
?＞
Код в add_user.php сначала выполняет запрос, чтобы найти максимальное значение id. Затем файл запускает инструкцию INSERT со значением id, увеличенным на 1. Этот код завершится сбоем в состоянии гонки на сильно загруженном сервере. Кроме того, это еще и неэффективно.
Так какова альтернатива? Используйте функцию автоматического приращения в MySQL, чтобы автоматически создавать уникальный идентификатор для каждой вставки. Обновленная схема выглядит следующим образом.
Листинг 5. Goodid.php
УДАЛЕНИЕ ТАБЛИЦЫ, ЕСЛИ СУЩЕСТВУЮТ пользователи;
СОЗДАТЬ ТАБЛИЦУ пользователей (
идентификатор MEDIUMINT NOT NULL AUTO_INCREMENT,
вход ТЕКСТ НЕ NULL,
пароль ТЕКСТ НЕ NULL,
ПЕРВИЧНЫЙ КЛЮЧ(id)
);
INSERT INTO user VALUES ( null, 'jack', 'pass');
INSERT INTO user VALUES (null, 'joan', 'pass' );
INSERT INTO user VALUES ( null, 'jane', 'pass' );
Мы добавили флаг NOT NULL, чтобы указать, что поле не должно быть пустым. Мы также добавили флаг AUTO_INCREMENT, указывающий, что поле автоматически увеличивается, и флаг PRIMARY KEY, указывающий, что поле является идентификатором. Эти изменения ускоряют процесс. В листинге 6 показан обновленный PHP-код для вставки пользователя в таблицу.
Листинг 6. Add_user_good.php
<?php
require_once("DB.php");
функция add_user($name, $pass)
{
$dsn = 'mysql://root:пароль@localhost/good_genid';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage() }
$sth = $db->prepare( "INSERT INTO user VALUES(null,?,?)" );
$db->execute($sth, array($name, $pass) );
$res = $db->query("SELECT last_insert_id()");
$id = ноль;
while ($res->fetchInto($row)) {$id = $row[0]}
return $id;
}
$id = add_user('Джерри', 'пропустить');
var_dump($id);
?>
Теперь вместо получения максимального значения идентификатора я напрямую использую оператор INSERT для вставки данных, а затем использую оператор SELECT для получения идентификатора последней вставленной записи. Код намного проще и эффективнее, чем исходная версия и связанные с ней шаблоны.
Вопрос 3: Использование нескольких баз данных
Иногда мы видим приложение, в котором каждая таблица находится в отдельной базе данных. Это разумно для очень больших баз данных, но для общих приложений такой уровень секционирования не требуется. Кроме того, реляционные запросы не могут выполняться между базами данных, что лишает всей идеи использования реляционной базы данных, не говоря уже о том, что было бы сложнее управлять таблицами в нескольких базах данных. Итак, как должны выглядеть несколько баз данных? Во-первых, вам нужны некоторые данные. В листинге 7 показаны такие данные, разделенные на четыре файла.
Листинг 7. Файл базы данных
Files.sql:
СОЗДАТЬ ТАБЛИЦЫ файлов (
идентификатор СРЕДНИЙ,
user_id СРЕДНИЙ,
имя ТЕКСТ,
путь ТЕКСТ
);
Загрузить_файлы.sql:
ВСТАВИТЬ В файлы ЗНАЧЕНИЯ ( 1, 1, 'test1.jpg', 'files/test1.jpg');
ВСТАВИТЬ В файлы ЗНАЧЕНИЯ (2, 1, 'test2.jpg', 'files/test2.jpg' )
;
УДАЛИТЬ ТАБЛИЦУ, ЕСЛИ СУЩЕСТВУЮТ пользователи;
СОЗДАТЬ ТАБЛИЦУ пользователей (
идентификатор СРЕДНИЙ,
вход ТЕКСТ,
пароль ТЕКСТ
);
Загрузить_пользователей.sql:
ВСТАВИТЬ В ЗНАЧЕНИЯ пользователей (1, «валет», «пасс»);
INSERT INTO user VALUES (2, 'jon', 'pass' );
В версии этих файлов для нескольких баз данных вам следует загрузить оператор SQL в одну базу данных, а затем загрузить оператор SQL пользователя в другую базу данных. Код PHP, используемый для запроса к базе данных файлов, связанных с конкретным пользователем, показан ниже.
Листинг 8. Getfiles.php
<?php
require_once("DB.php");
функция 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( "ВЫБРАТЬ id FROM пользователей WHERE login=?",array($name) ) ;
$uid = ноль;
while ($res->fetchInto($row)) {$uid = $row[0]}
return $uid;
}
Функция 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;
вернуть $строки;
}
$files = get_files('джек');
var_dump($files);
?>
Функция get_user подключается к базе данных, содержащей таблицу пользователей, и извлекает идентификатор данного пользователя. Функция get_files подключается к таблице файлов и извлекает строки файла, связанные с данным пользователем.
Лучший способ сделать все это — загрузить данные в базу данных, а затем выполнить запрос, например, как показано ниже.
Листинг 9. Getfiles_good.php
<?php
require_once("DB.php");
функция 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("ВЫБРАТЬ файлы.* ОТ пользователей, файлы ГДЕ
пользователи.логин=? И пользователи.ид=files.user_id",
массив($имя));
while ($res->fetchInto($row)) {$rows[] = $row }
return $rows;
}
$files = get_files('джек');
var_dump($files);
?>
Код не только короче, но и проще для понимания и эффективен. Вместо выполнения двух запросов мы выполняем один запрос.
Хотя этот вопрос может показаться надуманным, на практике мы обычно приходим к выводу, что все таблицы должны находиться в одной базе данных, если только для этого нет веских причин.
Вопрос 4: Не использовать отношения
Реляционные базы данных отличаются от языков программирования тем, что в них нет типов массивов. Вместо этого они используют связи между таблицами для создания структуры «один ко многим» между объектами, которая имеет тот же эффект, что и массив. Одна проблема, которую я видел в приложениях, заключается в том, что инженеры пытаются использовать базу данных как язык программирования, создавая массивы с использованием текстовых строк с идентификаторами, разделенными запятыми. См. образец ниже.
Листинг 10.
Файлы Bad.sql DROP TABLE IF EXISTS;
СОЗДАТЬ ТАБЛИЦЫ файлов (
идентификатор СРЕДНИЙ,
имя ТЕКСТ,
путь ТЕКСТ
);
УДАЛЕНИЕ ТАБЛИЦЫ, ЕСЛИ СУЩЕСТВУЮТ пользователи;
СОЗДАТЬ ТАБЛИЦУ пользователей (
идентификатор СРЕДНИЙ,
вход ТЕКСТ,
пароль ТЕКСТ,
файлы ТЕКСТ
);
INSERT INTO файлы ЗНАЧЕНИЯ ( 1, 'test1.jpg', 'media/test1.jpg' );
INSERT INTO файлы ЗНАЧЕНИЯ (2, 'test1.jpg', 'media/test1.jpg' );
INSERT INTO user VALUES (1, 'jack', 'pass', '1,2' );
Пользователь в системе может иметь несколько файлов. В языках программирования для представления файлов, связанных с пользователем, следует использовать массив. В этом примере программист решает создать поле файлов, содержащее список идентификаторов файлов, разделенных запятыми. Чтобы получить список всех файлов для конкретного пользователя, программист должен сначала прочитать строки из таблицы пользователей, затем проанализировать текст файлов и выполнить отдельный оператор SELECT для каждого файла. Код показан ниже.
Листинг 11. Get.php
<?php
require_once("DB.php");
функция 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( "ВЫБРАТЬ файлы ОТ пользователей ГДЕ login=?",array($name) ) ;
$files = ноль;
while($res->fetchInto($row)) {$files = $row[0] }
$rows = array();
foreach(split(',',$files) as $file)
{
$res = $db->query( "SELECT * FROM files WHERE id=?",
массив($файл));
while($res->fetchInto($row)) {$rows[] = $row }
}
Вернуть $строки;
}
$files = get_files('джек');
var_dump($files);
?>
Эта технология медленная, ее сложно поддерживать, и она не позволяет эффективно использовать базу данных. Единственное решение — перепроектировать схему, чтобы преобразовать ее обратно в традиционную реляционную форму, как показано ниже.
Листинг 12.
Файлы Good.sql DROP TABLE IF EXISTS;
СОЗДАТЬ ТАБЛИЦЫ файлов (
идентификатор СРЕДНИЙ,
user_id СРЕДНИЙ,
имя ТЕКСТ,
путь ТЕКСТ
);
УДАЛЕНИЕ ТАБЛИЦЫ, ЕСЛИ СУЩЕСТВУЮТ пользователи;
СОЗДАТЬ ТАБЛИЦУ пользователей (
идентификатор СРЕДНИЙ,
вход ТЕКСТ,
пароль ТЕКСТ
);
INSERT INTO user VALUES ( 1, 'jack', 'pass');
ВСТАВИТЬ В файлы ЗНАЧЕНИЯ ( 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");
функция 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("ВЫБРАТЬ файлы.* ИЗ пользователей,файлы ГДЕ user.login=?
Иusers.id=files.user_id",array($name) );
while($res->fetchInto($row)) {$rows[] = $row;
вернуть $строки;
}
$files = get_files('джек');
var_dump($files);
?＞
Здесь мы делаем запрос к базе данных, чтобы получить все строки. Код несложный и использует базу данных так, как было задумано.
Вопрос 5: Шаблон n+1
Я не могу сказать вам, сколько раз я видел большие приложения, где код сначала извлекает несколько объектов (скажем, клиентов), а затем перемещается туда и обратно, чтобы получить их один за другим, чтобы получить каждый Подробности сущности. Мы называем этот режим n+1, потому что запрос выполняется очень много раз: один запрос извлекает список всех сущностей, а затем один запрос выполняется для каждого из n сущностей. Это не проблема, когда n=10, но как насчет n=100 или n=1000? Тогда обязательно будет неэффективность. В листинге 14 показан пример этого шаблона.
Листинг 14. Schema.sql
DROP TABLE ЕСЛИ СУЩЕСТВУЕТ авторов;
СОЗДАТЬ ТАБЛИЦУ авторов (
идентификатор MEDIUMINT NOT NULL AUTO_INCREMENT,
имя ТЕКСТ НЕ NULL,
ПЕРВИЧНЫЙ КЛЮЧ(id)
);
УДАЛИТЕ ТАБЛИЦУ, ЕСЛИ СУЩЕСТВУЮТ книги;
СОЗДАТЬ ТАБЛИЦУ книг (
идентификатор MEDIUMINT NOT NULL AUTO_INCREMENT,
author_id MEDIUMINT NOT NULL,
имя ТЕКСТ НЕ NULL,
ПЕРВИЧНЫЙ КЛЮЧ(id)
);
INSERT INTO авторов VALUES (null, 'Джек Херрингтон');
INSERT INTOauthors VALUES (null, 'Dave Thomas')
INSERT INTO book VALUES (null, 1, 'Генерация кода в действии');
ВСТАВИТЬ В книги ЗНАЧЕНИЯ (null, 1, «Советы по подкастингу»);
INSERT INTO book VALUES ( null, 1, 'PHP Hacks' );
INSERT INTO book VALUES ( null, 2, 'Прагматичный программист' );
INSERT INTO book VALUES ( null, 2, 'Ruby on Rails' );
INSERT INTO book VALUES (null, 2, 'Программирование 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( "ВЫБРАТЬ идентификатор ОТ авторов WHERE name=?",array($name) );
$id = ноль;
while ($res->fetchInto($row)) {$id = $row[0]};
вернуть $id;
}
Функция get_books($id)
{
global $db;
$res = $db->query( "ВЫБРАТЬ идентификатор ИЗ книг ГДЕ автор_id=?",array($id) );
$ids = массив();
while($res->fetchInto($row)) {$ids []= $row[0] };
вернуть идентификаторы $;
}
Функция get_book($id)
{
global $db;
$res = $db->query( "ВЫБРАТЬ * ИЗ книг ГДЕ id=?", array($id) );
while ($res->fetchInto($row)) { return $row }
вернуть ноль;
}
$author_id = get_author_id('Джек Херрингтон');
$books = get_books($author_id);
foreach($books as $book_id) {
$book = get_book($book_id);
var_dump($книга);
}
?>
Если вы посмотрите на приведенный ниже код, вы можете подумать: «Эй, это действительно ясно и просто». Сначала нужно получить идентификатор автора, затем получить список книг, а затем получить информацию о каждой книге. Да, это понятно и просто, но эффективно ли это? Ответ — нет. Посмотрите, сколько запросов выполняется только для того, чтобы получить книги Джека Херрингтона. Один раз, чтобы получить идентификатор, другой раз, чтобы получить список книг, а затем выполнить запрос для каждой книги. Три книги требуют пяти запросов!
Решение состоит в том, чтобы использовать функцию для выполнения большого количества запросов, как показано ниже.
Листинг 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("ВЫБРАТЬ книги.* ИЗ авторов,книги ГДЕ Books.author_id=authors.id ANDauthors.name=?",
массив($имя));
$строки = массив();
while($res->fetchInto($row)) {$rows []= $row }
вернуть $строки;
}
$books = get_books('Джек Херрингтон');
var_dump($книги);
?>
Для получения списка теперь требуется один быстрый запрос. Это означает, что мне, скорее всего, придется иметь несколько методов этих типов с разными параметрами, но выбора на самом деле нет. Если вы хотите иметь масштабируемое PHP-приложение, вы должны эффективно использовать базу данных, что означает более разумные запросы.
Проблема с этим примером в том, что он слишком ясен. Вообще говоря, эти типы задач n+1 или n*n гораздо более тонкие. И они появляются только тогда, когда администратор базы данных запускает Query Profiler в системе, когда в системе возникают проблемы с производительностью.
Заключение
Базы данных — это мощные инструменты, и, как и все мощные инструменты, ими можно злоупотреблять, если не знать, как правильно их использовать. Хитрость в выявлении и решении этих проблем заключается в том, чтобы лучше понять лежащую в их основе технологию. Я давно слышал, как авторы бизнес-логики жаловались, что им не нужно разбираться в базах данных или коде SQL. Они используют базу данных как объект и задаются вопросом, почему производительность такая низкая.
Они не понимают, насколько важно понимание SQL для превращения базы данных из сложной необходимости в мощный альянс. Если вы используете базы данных каждый день, но не знакомы с SQL, прочтите «Искусство SQL». Это хорошо написанная и практичная книга, которая поможет вам получить базовое представление о базах данных.