データベースの使用方法が 1 つだけ正しい場合は、さまざまな方法でデータベース設計、データベース アクセス、データベース ベースの PHP ビジネス ロジック コードを作成できますが、それらは通常、最終的には間違っています。この記事では、データベース設計およびデータベースにアクセスする PHP コードで発生する 5 つの一般的な問題と、それらが発生した場合の修正方法について説明します。
質問 1: MySQL を直接使用する
よくある問題は、古い PHP コードが mysql_ 関数を使用してデータベースに直接アクセスすることです。リスト 1 は、データベースに直接アクセスする方法を示しています。
リスト 1. Access/get.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
require_once("DB.php");
関数 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] }
$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 MEDIUMINT、
ログインテキスト、
パスワードテキスト
);
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
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( "INSERT INTO users VALUES(?,?,?)" );
$db->execute( $sth, array( $id, $name, $pass ) );
$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 ユーザー;
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' );
フィールドが空であってはいけないことを示すために、NOT NULL フラグを追加しました。また、フィールドが自動インクリメントであることを示す AUTO_INCREMENT フラグと、フィールドが ID であることを示す PRIMARY KEY フラグも追加しました。これらの変更により、作業がスピードアップします。リスト 6 は、テーブルにユーザーを挿入する更新された PHP コードを示しています。
リスト 6. Add_user_good.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->execute( $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' );
var_dump( $id );
?>
ここでは、最大の ID 値を取得する代わりに、INSERT ステートメントを直接使用してデータを挿入し、次に SELECT ステートメントを使用して最後に挿入されたレコードの ID を取得します。このコードは、元のバージョンおよびそれに関連するパターンよりもはるかにシンプルで効率的です。
問題 3: 複数のデータベースの使用
場合によっては、各テーブルが別個のデータベースに存在するアプリケーションを目にすることがあります。これは非常に大規模なデータベースでは妥当ですが、一般的なアプリケーションではこのレベルのパーティショニングは必要ありません。さらに、データベース間でリレーショナル クエリを実行することはできません。これにより、複数のデータベース間でテーブルを管理することがより困難になることは言うまでもなく、リレーショナル データベースを使用するという概念自体が失われてしまいます。
では、複数のデータベースはどのようなものであるべきなのでしょうか? まず、いくつかのデータが必要です。リスト 7 は、このようなデータが 4 つのファイルに分割されていることを示しています。
リスト 7. データベース ファイル
Files.sql:
CREATE TABLE ファイル (
id MEDIUMINT、
user_id MEDIUMINT、
名前テキスト、
パステキスト
);
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:
ユーザーが存在する場合はテーブルを削除;
CREATE TABLE ユーザー (
id MEDIUMINT、
ログインテキスト、
パスワードテキスト
);
Load_users.sql:
INSERT INTO users VALUES (1, 'jack', 'pass');
INSERT INTO users VALUES (2, 'jon', 'pass' );
これらのファイルのマルチデータベース バージョンでは、SQL ステートメントを 1 つのデータベースにロードしてから、ユーザーの SQL ステートメントを別のデータベースにロードする必要があります。特定のユーザーに関連付けられたファイルをデータベースにクエリするための PHP コードを以下に示します。
リスト 8. Getfiles.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 を返す;
}
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 関数は、ファイル テーブルに接続され、指定されたユーザに関連するファイルを検索します。
これらすべてを解決するためのより良い方法は、データをデータベースに追加してから、次のようなコメントを実行することです。清单 9. Getfiles_good.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=?かつ、users.id=files.user_id",array( $name ) );
while( $res->fetchInto( $row ) ) { $rows[] = $row; }
return $rows;
}
$files = get_files( 'jack' );
var_dump( $files );
?>
このコードは、より短いだけでなく、より理解しやすく、より効果的です。
この問題は多少の影響はありますが、実際には、非常に困難な理由がない限り、すべての表現を同じデータベース内にまとめて出力します。
问题 4：不用关系
逆に、関係データベースは、オブジェクト間の 1 対複数の構造を構築するためにテーブル間の関係を使用します。これは、アプリケーション プログラムで見られるものと同じ効果があります。問題は、プロセス図では、データを処理手順で使用すること、つまり、間隔を持ったテキスト文字列を使用することによって、次のモードを作成することです。 清单 10. Bad.sql
DROP TABLE IF EXISTS files;
CREATE TABLE ファイル (
id MEDIUMINT、
名前TEXT、
パス TEXT
);
DROP TABLE IF EXISTS users;
CREATE TABLE ユーザー (
id MEDIUMINT、
ログインテキスト、
パスワードテキスト、
ファイルのテキスト
);
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
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=?",
array( $file ) );
while( $res->fetchInto( $row ) ) { $rows[] = $row }
}
return $rows;
}
$files = get_files( 'jack' );
var_dump( $files );
?>
このテクノロジーは遅く、保守が難しく、データベースをうまく活用できません。唯一の解決策は、以下に示すように、スキーマを再構築して従来のリレーショナル形式に変換することです。
リスト 12. Good.sql
ファイルが存在する場合はテーブルを削除;
CREATE TABLE ファイル (
id MEDIUMINT、
user_id MEDIUMINT、
名前テキスト、
パステキスト
);
DROP TABLE IF EXISTS ユーザー;
CREATE TABLE ユーザー (
id MEDIUMINT、
ログインテキスト、
パスワードテキスト
);
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
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=?
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 パターン
コードが最初にいくつかのエンティティ (たとえば、顧客) を取得し、次にそれらを 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, 'Jack Herrington' );
INSERT INTO authors 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, 'プログラミング Ruby');
モデルは信頼でき、エラーはありません。問題は、以下に示すように、データベースにアクセスして特定の著者によるすべての書籍を検索するコードにあります。
リスト 15. Get.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 )
{
グローバル $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 )
{
グローバル $db;
$res = $db->query( "SELECT ID FROM Books WHERE author_id=?",
array( $id ) );
$ids = array();
while( $res->fetchInto( $row ) ) { $ids []= $row[0] }
$ids を返す;
}
関数 get_book( $id )
{
グローバル $db;
$res = $db->query( "SELECT * FROM Books WHERE id=?", array( $id ) );
while( $res->fetchInto( $row ) ) { return $row }
null を返します;
}
$author_id = 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
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 )
{
グローバル $db;
$res = $db->query(
「書籍を選択してください。* 著者から、書籍がどこにあるか
books.author_id=authors.id AND authors.name=?",
array( $name ) );
$rows = array();
while( $res->fetchInto( $row ) ) { $rows []= $row }
return $rows;
}
$books = get_books( 'ジャック・ヘリントン' );
var_dump( $books );
?>
リストを取得するには、迅速な単一のクエリが必要になります。これは、異なるパラメーターを持つこれらのタイプのメソッドをいくつか用意する必要がある可能性が高いことを意味しますが、実際には選択の余地はありません。スケーラブルな PHP アプリケーションを作成したい場合は、データベースを効率的に使用することが必須であり、これはよりスマートなクエリを意味します。
この例の問題は、少し明確すぎることです。一般に、この種の n+1 または n*n の問題は、はるかに微妙です。また、これらは、システムにパフォーマンスの問題がある場合に、データベース管理者がシステム上でクエリ プロファイラを実行する場合にのみ表示されます。
結論
データベースは強力なツールですが、他の強力なツールと同様、正しい使用方法を知らなければ悪用する可能性があります。これらの問題を特定して解決するための秘訣は、基礎となるテクノロジーをより深く理解することです。ビジネス ロジック作成者がデータベースや SQL コードを理解する必要がないという不満を長い間聞いてきました。彼らはデータベースをオブジェクトとして使用していますが、なぜパフォーマンスがこれほど悪いのか疑問に思っています。
彼らは、データベースを困難な必要性から強力な連携に変えるために SQL を理解することがいかに重要であるかを理解していません。データベースを毎日使用しているものの、SQL については初めての場合は、データベースの基本をガイドできる、よく書かれた実践的な本である The Art of SQL を読んでください。
![[Web フロントエンド] Node.js クイック スタート](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
