![閉じる [x]](http://dw1.s81c.com/i/c.gif){kind=small}
データベースを正しく使う方法が 1 つしかないとしたらどうでしょう。
データベースの設計、データベースのアクセス、そしてそれを制御する PHP ビジネス・ロジック・コードはありとあらゆる方法で作成できますが、間違った結果になることも少なくありません。この記事では、データベースの設計や、データベースにアクセスするPHP コードで起こりがちな 5 つの問題、そして問題が見つかった場合の解決方法について説明します。
共通の問題の 1 つとして、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 パラメーターをクエリーに追加していることにも注意してください。
この手法には、ちょうどいい 2 つの代替策があります。1 つは PEAR DB モジュール、そしてもう1 つは PDO (PHP データ・オブジェクト) クラスです。どちらも、選択された特定のデータベースから抽象化を行います。そのため、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 から送信されます。
現代のほとんどのデータベース同様、MySQL にはレコードごとに自動でインクリメントする固有識別子を作成する機能が備わっています。それにもかかわらず、まずSELECT 文を実行して最大の ID を検索し、それからその id に 1 を足すといったコードを依然として見かけます。リスト 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 フラグ、id のフィールドを示す PRIMARY KEY フラグも追加しています。これらの変更は、多少のスピードアップにつながります。リスト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 のオートインクリメント機能を使う代わりに、PEAR DB システムの nextId() メソッドを使用する方法もあります。MySQL の場合、これによって新しいシーケンス・テーブルが作成され、精巧なロック・メカニズムでテーブルが管理されます。この方法の利点は、さまざまな種類のデータベース・システムで機能するということです。
いずれにしても、固有 ID の増分を管理するシステムを使うべきで、クエリーを実行してから値を増分し、その上でレコードを追加しなければならないようなシステムには頼らないことです。後者の場合、大容量のサイトでの競合状態に影響されがちです。
それぞれのテーブルが個別のデータベース内にあるというアプリケーションを見かけることがたまにあります。非常に大型のデータベースでは、そのような方法をとる理由がありますが、平均的なアプリケーションにはこのレベルのセグメンテーションは必要ありません。また、複数のデータベースでリレーションクエリーを実行することができたとしても、このようなセグメンテーションには強く反対します。その理由は、構文が一層複雑になること、バックアップとリストアが管理しにくいこと、そしてデータベース・エンジンが違うと構文が機能しない場合があることです。さらに、テーブルが複数のデータベースに分かれている場合、リレーショナル構造を把握するのが困難です。
それでは、複数のデータベースとはどのようなものなのかを見てみましょう。それにはまず、データが必要です。リスト7 に、4 つのファイルに分かれたデータを示します。
リスト 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 文を 1 つのデータベースにロードし、次に、usersSQL 文を別のデータベースにロードすることになります。以下に、特定のユーザーに関連付けられたファイルをデータベースにクエリーを実行する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 関数でユーザー・テーブルが含まれるデータベースに接続し、指定ユーザーのID を検索しています。get_files 関数ではファイル・テーブルに接続し、指定ユーザーに関連付けられたファイルの行を検索しています。
これをすべて行うのにより適した方法は、以下に示すように、データを 1 つのデータベースにロードしてからクエリーを実行することです。
リスト 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 );
?>
|
このコードは短いだけでなく、わかりやすく一層効率的で、2 つのクエリーを実行する代わりに、まとめて1 つのクエリーを実行しています。
この問題はあまり一般的ではなさそうに聞こえますが、差し迫った理由がない限り、すべてのテーブルは同じデータベース内になければならないことがわかるようになるまで、実際に何度も見てきました。
リレーショナル・データベースはプログラム言語とは違って、配列型を持ちません。その代わり、テーブル間のリレーションを使って、オブジェクト間に配列と同じ効果を持つ1 対多の構造を作成します。アプリケーションに見られる問題の 1 つは、エンジニアがプログラミング言語と同じようにデータベースを扱い、テキスト・ストリングをコンマ区切りの識別子で区切って、配列を作成しようとすることです。以下のスキーマを見てください。
リスト 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' );
|
システム内の 1 人のユーザーは複数のファイルを持つことができます。プログラミング言語では、ユーザーに関連付けられたファイルを表すには配列を使います。この例では、プログラマーはコンマで区切ったid のリストを含む files フィールドを作成する方法を選びました。特定ユーザーのすべてのファイルのリストを取得するため、プログラマーはまず、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 );
?>
|
上記のコードでは、データベースに対する単一のクエリーで、すべての行を取得しています。このコードは複雑なものではなく、データベースをその本来の意図に従って使っています。
コードがまずエンティティー (例えば、カスタマー) のリストを取得し、それからあらためて各エンティティーを検索して、それぞれの詳細を取得するという大規模アプリケーションを何度目にしたことか分かりません。我々はこれをn+1 パターンと呼んでいます。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 を取得し、本のリストを取得し、それからそれぞれの本についての情報を取得しています。おっしゃるとおり、実にわかりやすいコードです。でも効率的かと言えば、そうではありません。JackHerrington の著書を検索するだけのことに、どれだけ多くのクエリーを実行しなければならなかったかを見てください。idを取得するためのクエリー、本のリストを取得するためのクエリー、そして各本に対するクエリーの合わせて5 つのクエリーが、たった 3 冊の本のためだけに行われています。
この解決策は、以下に示すように、1 つの関数で 1 つのまとまったクエリーを実行することです。
リスト 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 );
?>
|
これで、リストの取得に必要なのは、高速な 1 つのクエリーだけになりました。パラメーターを変えて、このようなタイプのメソッドをいくつか使用する必要は出てきますが、他に選択肢はありません。スケーラブルなPHP アプリケーションを目指す場合は、データベースを効率的に利用しなければなりません。つまり、より賢いクエリーです。
この例の問題点は、問題がややはっきりしすぎているという点です。通常、このようなn+1 や n*n の問題は、はるかに捕えにくいものです。システムにパフォーマンス上の問題がある場合に、データベース管理者がクエリー・プロファイラーを実行してみて、やっと見つかるものです。
データベースは強力なツールで、すべての強力なツール同様、正しい使い方を知らないと誤って使用されることになります。これらの問題を特定して解決する上での秘訣は、基本となる技術をよく理解することです。私は長い間、ビジネス・ロジックのコード作成者がデータベースやSQL コードを理解しなくても済むようにしたいと嘆く声を耳にしてきました。彼らはデータベースをオブジェクトにラップして、パフォーマンスが何故そんなに悪いのか、首を傾げています。
そのようなコード作成者は、データベースを難しいけれども必要なものから強力な味方に変えるには、SQLを理解することが基本であることに気づいていません。データベースを日常的に使用しているのに、SQLは得意分野ではないという方は、『Art of SQL』を読んでください。これは、データベースを最大限に利用するための優れた実用的ガイドです。
学ぶために
- Stephane Faroult、Peter Robson共著の『Art of SQL』は、アプリケーションでデータベースを使用するプログラマーの必読本です。
-
PHP.net は、PHP のあらゆる点での開始点となります。
-
PEAR DB 資料は、優れたリソースとなります。
-
PDO Functions の資料は、PHP データ・オブジェクト (PDO) の最新情報を提供します。
-
MySQL.org には、より有効なデータベース活用方法の例を使用した優れた資料が記載されています。
- IBM developerWorks の PHP プロジェクト・リソースにアクセスして、PHPの詳細を学んでください。
-
developerWorks technical events and Webcasts で最新情報を入手してください。
- 世界中で近日中に予定されている IBM オープン・ソース開発者を対象とした会議、見本市、ウェブ放送やその他のイベントをチェックしてください。
- オープン・ソース技術を使用した開発、および IBM 製品でこれらの技術を使用する際に役立つ詳細な手順、ツール、およびプロジェクト更新については、developerWorksOpen source ゾーンにアクセスしてください。
- ソフトウェア開発者を対象とした興味深いインタービューや討論については、developerWorks ポッドキャストをチェックしてください。
製品や技術を入手するために
- 皆さんの次期オープンソース開発プロジェクトを、IBM trial softwareを使って革新してください。ダウンロード、あるいはDVDで入手することができます。
議論するために
-
developerWorks blogsからdeveloperWorksのコミュニティーに加わってください。
目次
タグ
スライダーバーを使用することで、より多く（少なく）タグを表示します。
人気のタグは、この特定のコンテンツ・ゾーン（例えば、Java テクノロジー、Linux や WebSphere など）に対するトップのタグを表示します。
マイ・タグは、この特定のコンテンツ・ゾーン（例えば、Java テクノロジー、Linux や WebSphere など）に対するお客様ご自身のタグを表示します。
記事の人気タグ |
記事のマイ・タグタグ・リストへスキップ
記事の人気タグ |
記事のマイ・タグ