PHP データベースの 5 つの一般的な問題

PHP アプリケーションで発生する 5 つの一般的なデータベースの問題 (データベース スキーマ設計、データベース アクセス、データベースを使用するビジネス ロジック コードなど) とその解決策を紹介します。

データベースの使用方法が 1 つだけ正しければ...

データベースの設計、データベース アクセス、データベース ベースの PHP ビジネス ロジック コードを作成する方法はたくさんありますが、最終的には間違っていることがほとんどです。この記事では、データベース設計およびデータベースにアクセスする PHP コードで発生する 5 つの一般的な問題と、それらが発生した場合の修正方法について説明します。

問題 1: MySQL を直接使用する

よくある問題は、古い PHP コードが mysql_ 関数を使用してデータベースに直接アクセスすることです。リスト 1 は、データベースに直接アクセスする方法を示しています。

リスト 1. Access/get.php

<?php
関数 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] }

$id

var_dump( get_user_id( 'jack' ) )

;

?＞

データベースへのアクセスには mysql_connect 関数が使用されることに注意してください。また、文字列連結を使用して $name パラメーターをクエリに追加するクエリにも注目してください。

このテクノロジには、PEAR DB モジュールと PHP データ オブジェクト (PDO) クラスの 2 つの優れた代替手段があります。どちらも、特定のデータベース選択からの抽象化を提供します。その結果、コードは、あまり調整することなく、IBM® DB2®、MySQL、PostgreSQL、または接続したいその他のデータベース上で実行できます。

PEAR DB モジュールと PDO 抽象化レイヤーを使用するもう 1 つの利点は、SQL ステートメントで ? 演算子を使用できることです。これにより、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( 'SELECT id FROM users WHERE login=?',array( $name ) ) ;
$id = null;
while( $res->fetchInto( $row ) ) { $id = $row[0] } $id

;

var_dump( get_user_id( 'jack' ) )

;

?>

$dsn のデータベース接続文字列を除き、MySQL の直接使用がすべて削除されていることに注意してください。さらに、SQL では ? 演算子を使用して $name 変数を使用します。次に、クエリ データは、query() メソッドの最後で配列を通じて送信されます。

問題 2: 自動インクリメント機能を使用していない

最新のデータベースと同様に、MySQL にはレコードごとに自動インクリメントの一意の識別子を作成する機能があります。さらに、最初に SELECT ステートメントを実行して最大の ID を検索し、次にその ID を 1 ずつインクリメントして、新しいレコードを検索するコードが引き続き表示されます。リスト 3 は、悪いパターンの例を示しています。

リスト 3. Badid.sql

DROP TABLE IF EXISTS ユーザー。
CREATE TABLE ユーザー (
id ミディアムミント、
ログインテキスト、
パスワードテキスト
);

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");

関数 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( "ユーザーの値に挿入(?,?,?) " );
$db->e​​xecute( $sth, array( $id, $name, $pass ) );

return $id;
$

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

;
?＞

add_user.php のコードは、最初にクエリを実行して id の最大値を見つけます。次に、ファイルは、ID 値を 1 増やして INSERT ステートメントを実行します。このコードは、負荷の高いサーバーでは競合状態で失敗します。さらに、非効率でもあります。

では、代替手段は何でしょうか? MySQL の自動インクリメント機能を使用して、挿入ごとに一意の ID を自動的に作成します。更新されたスキーマは次のようになります。

リスト 5. Goodid.php

DROP TABLE IF EXISTS ユーザー。
CREATE TABLE ユーザー (
id MEDIUMINT NOT NULL AUTO_INCREMENT、
ログインテキストが NULL ではありません。
パスワードのテキストが NULL ではありません、
主キー(id)
);

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

フィールドが null であってはいけないことを示す NOT NULL フラグを追加しました。また、フィールドが自動インクリメントであることを示す AUTO_INCREMENT フラグと、フィールドが ID であることを示す PRIMARY KEY フラグも追加しました。これらの変更により、作業がスピードアップします。リスト 6 は、テーブルにユーザーを挿入するための更新された PHP コードを示しています。

リスト 6. Add_user_good.php

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

関数 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->e​​xecute( $sth, array( $name, $pass ) );

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

;
$

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

;
?>

ここでは、最大 ID 値を取得する代わりに、INSERT ステートメントを直接使用してデータを挿入し、次に SELECT ステートメントを使用して最後に挿入されたレコードの ID を取得します。このコードは、元のバージョンおよびそれに関連するパターンよりもはるかにシンプルで効率的です。

質問 3: 複数のデータベースの使用

場合によっては、各テーブルが別個のデータベースに存在するアプリケーションを目にすることがあります。これは非常に大規模なデータベースでは妥当ですが、一般的なアプリケーションではこのレベルのパーティショニングは必要ありません。さらに、データベース間でリレーショナル クエリを実行することはできません。これにより、複数のデータベース間でテーブルを管理することがより困難になることは言うまでもなく、リレーショナル データベースを使用するという概念自体が失われてしまいます。 では、複数のデータベースはどのようにあるべきでしょうか?まず、いくつかのデータが必要です。リスト 7 は、このようなデータが 4 つのファイルに分割されていることを示しています。

リスト 7. データベース ファイル

Files.sql:
CREATE TABLE ファイル (
id ミディアムミント、
user_id MEDIUMINT、
名前テキスト、
パステキスト
);

ロードファイル.sql:
INSERT INTO files VALUES ( 1, 1, 'test1.jpg', 'files/test1.jpg' );
INSERT INTO files VALUES (2, 1, 'test2.jpg', 'files/test2.jpg' )

;
ユーザーが存在する場合はテーブルを削除します。
CREATE TABLE ユーザー (
id ミディアムミント、
ログインテキスト、
パスワードテキスト
);

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

これらのファイルのマルチデータベース バージョンでは、SQL ステートメントを 1 つのデータベースにロードしてから、users 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( "SELECT ID FROM users WHERE login=?",array( $name ) ) ;
$uid = null;
while( $res->fetchInto( $row ) ) { $uid = $row[0] }

$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; }
$rows を返します。

$files = get_files( 'jack'

)

;

?>

get_user 関数は、users テーブルを含むデータベースに接続し、特定のユーザーの ID を取得します。 get_files 関数は、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("SELECT files.* FROM users, files WHERE
users.login=? AND users.id=files.user_id",
array( $name ) );
while( $res->fetchInto( $row ) ) { $rows[] = $row }

$rows;

$files = get_files( 'jack'

)

;

?>

コードが短くなっただけでなく、理解しやすく効率的になりました。 2 つのクエリを実行する代わりに、1 つのクエリを実行します。

この質問は突飛に聞こえるかもしれませんが、実際には、よほどの理由がない限り、すべてのテーブルは同じデータベース内にあるべきであると通常結論付けられます。

質問 4: リレーションシップを使用していない

リレーショナル データベースは、配列型を持たないという点でプログラミング言語とは異なります。代わりに、テーブル間のリレーションシップを使用して、オブジェクト間に 1 対多の構造を作成します。これは、配列と同じ効果があります。私がアプリケーションで見た問題の 1 つは、エンジニアがカンマ区切りの識別子を含むテキスト文字列を使用して配列を作成することにより、データベースをプログラミング言語のように使用しようとする場合です。以下のパターンを参照してください。

リスト 10. Bad.sql

DROP TABLE IF EXISTS ファイル。
CREATE TABLE ファイル (
id ミディアムミント、
名前テキスト、
パステキスト
);

ユーザーが存在する場合はテーブルを削除します。
CREATE TABLE ユーザー (
id ミディアムミント、
ログインテキスト、
パスワードテキスト、
ファイルテキスト
);

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")

関数 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=?",
配列( $file ) );
while( $res->fetchInto( $row ) ) { $rows[] = $row; }

$rows を返します

。

$files = get_files( 'jack'

)

;

?>

このテクノロジは遅く、保守が難しく、データベースを十分に活用できません。唯一の解決策は、以下に示すように、スキーマを再構築して従来のリレーショナル形式に変換することです。

リスト 12. Good.sql

DROP TABLE IF EXISTS ファイル。
CREATE TABLE ファイル (
id ミディアムミント、
user_id MEDIUMINT、
名前テキスト、
パステキスト
);

ユーザーが存在する場合はテーブルを削除します。
CREATE TABLE ユーザー (
id ミディアムミント、
ログインテキスト、
パスワードテキスト
);

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")

関数 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=?
かつ、 users.id=files.user_id",array( $name ) );
while( $res->fetchInto( $row ) ) { $rows[] = $row; }
$rows を返します。

$files = get_files( 'jack'

)

;

?＞

ここでは、データベースにクエリを実行してすべての行を取得します。コードは複雑ではなく、意図したとおりにデータベースを使用します。

質問 5: n+1 パターン コード

が最初にいくつかのエンティティ (顧客としましょう) を取得し、次にそれらを 1 つずつ取得するために行ったり来たりする大規模なアプリケーションを何度見たかわかりません。エンティティの詳細。クエリが何度も実行されるため、これを n+1 モードと呼びます。1 つのクエリですべてのエンティティのリストが取得され、その後、n 個のエンティティごとに 1 つのクエリが実行されます。 n=10 の場合は問題ありませんが、n=100 や n=1000 の場合はどうでしょうか。そうなると、必ず非効率が生じます。リスト 14 は、このパターンの例を示しています。

リスト 14. Schema.sql

DROP TABLE IF EXISTS authors;
CREATE TABLE の作成者 (
id MEDIUMINT NOT NULL AUTO_INCREMENT、
名前のテキストが NULL ではありません、
主キー(id)
);

書籍が存在する場合はテーブルを削除します。
CREATE TABLE ブック (
id MEDIUMINT NOT NULL AUTO_INCREMENT、
author_id MEDIUMINT NOT NULL、
名前のテキストが NULL ではありません、
主キー(id)
);

INSERT INTO authors VALUES ( null, 'ジャック・ヘリントン' );
INSERT INTO 著者 VALUES ( null, 'Dave Thomas' );

INSERT INTO Books VALUES ( null, 1, '動作中のコード生成' );
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()) }

関数 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] }
$id を返します。
関数

get_books( $id )
{
global $db;

$res = $db->query( "SELECT ID FROM Books WHERE author_id=?",array( $id ) );
$ids = 配列();
while( $res->fetchInto( $row ) ) { $ids []= $row[0] }
$id を返します。
関数

get_book( $id )
{
グローバル $db;

$res = $db->query( "SELECT * FROM Books WHERE id=?", array( $id ) );
while( $res->fetchInto( $row ) ) { return $row }
null を返します。

=

get_author_id( 'ジャック・ヘリントン' );
$books = get_books( $author_id );
foreach( $books as $book_id ) {
$book = get_book( $book_id );
var_dump( $book );
}
?>

以下のコードを見ると、「これは非常に明快で簡単だ」と思われるかもしれません。まず、著者 ID を取得し、次に書籍のリストを取得し、次に各書籍に関する情報を取得します。はい、それは明確でシンプルですが、効果的ですか?答えはノーです。 Jack Herrington の書籍を取得するためだけに実行されるクエリの数を確認します。 1 回目は ID を取得し、もう 1 回目は書籍のリストを取得し、書籍ごとにクエリを実行します。 3 冊の本には 5 つのクエリが必要です。

解決策は、以下に示すように、関数を使用して多数のクエリを実行することです。

リスト 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()) }

関数 get_books( $name );
{
global $db;

$res = $db->query("書籍を選択します。* FROM 著者、書籍 WHERE Books.author_id=authors.id AND authors.name=?",
array( $name ) );
$rows = 配列();
while( $res->fetchInto( $row ) ) { $rows []= $row; }
$rows を返します。
$

books = get_books( 'ジャック・ヘリントン' );
var_dump( $books );
?>

リストを取得するには、迅速な単一のクエリが必要になります。これは、異なるパラメーターを持つこれらのタイプのメソッドをいくつか用意する必要がある可能性が高いことを意味しますが、実際には選択の余地はありません。スケーラブルな PHP アプリケーションを作成したい場合は、データベースを効率的に使用する必要があります。これは、よりスマートなクエリを意味します。

この例の問題は、少し明確すぎることです。一般に、この種の n+1 または n*n の問題は、はるかに微妙です。また、これらは、システムにパフォーマンスの問題がある場合に、データベース管理者がシステム上でクエリ プロファイラを実行する場合にのみ表示されます。

結論

データベースは強力なツールですが、他の強力なツールと同様、正しい使用方法を知らなければ悪用する可能性があります。これらの問題を特定して解決するための秘訣は、基礎となるテクノロジーをより深く理解することです。ビジネス ロジック作成者がデータベースや SQL コードを理解する必要がないという不満を長い間聞いてきました。彼らはデータベースをオブジェクトとして使用していますが、なぜパフォーマンスがこれほど悪いのか疑問に思っています。

彼らは、データベースを困難な必要性から強力な連携に変えるために SQL を理解することがいかに重要であるかを理解していません。毎日データベースを使用しているが SQL に詳しくない場合は、『The Art of SQL』を読んでください。これはデータベースの基本的な理解を導くことができる、よく書かれた実用的な本です。