데이터베이스 스키마 설계, 데이터베이스 액세스, 데이터베이스를 사용하는 비즈니스 로직 코드 등 PHP 애플리케이션에서 발생하는 5가지 일반적인 데이터베이스 문제와 해당 솔루션을 소개합니다.
데이터베이스를 사용하는 방법 중 하나만 맞다면...
데이터베이스 디자인, 데이터베이스 액세스, 데이터베이스 기반 PHP 비즈니스 로직 코드를 생성할 수 있는 방법은 여러 가지가 있지만 일반적으로 잘못된 결과를 낳게 됩니다. 이 문서에서는 데이터베이스 설계 및 데이터베이스에 액세스하는 PHP 코드에서 발생하는 다섯 가지 일반적인 문제와 이러한 문제가 발생할 때 이를 해결하는 방법에 대해 설명합니다.
문제 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] }
return $id;
}
var_dump( get_user_id( 'jack' ) );
?>
데이터베이스에 접근하기 위해 mysql_connect 함수를 사용한다는 점에 유의하세요. 또한 문자열 연결을 사용하여 $name 매개 변수를 쿼리에 추가하는 쿼리에 유의하세요.
이 기술에 대한 두 가지 좋은 대안이 있습니다. PEAR DB 모듈과 PDO(PHP Data Objects) 클래스입니다. 둘 다 특정 데이터베이스 선택의 추상화를 제공합니다. 결과적으로 코드는 별다른 조정 없이 IBM® DB2®, MySQL, PostgreSQL 또는 연결하려는 기타 데이터베이스에서 실행될 수 있습니다.
PEAR DB 모듈과 PDO 추상화 계층을 사용하는 또 다른 가치는 SQL 문에서 ? 연산자를 사용할 수 있다는 것입니다. 이렇게 하면 SQL을 유지 관리하기가 더 쉬워지고 SQL 삽입 공격으로부터 애플리케이션을 보호할 수 있습니다.
PEAR DB를 사용한 대체 코드는 아래와 같습니다.
목록 2. Access/get_good.php
<?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( 'WHERE 로그인=?',array( $name ) ) ;
$id = null;
while( $res->fetchInto( $row ) ) { $id = $row[0] }
return $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 사용자 VALUES ( 1, '잭', '패스' );
INSERT INTO 사용자 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 = 배열();
$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 사용자 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 사용자;
CREATE TABLE 사용자(
ID MEDIUMINT NOT NULL AUTO_INCREMENT,
로그인 TEXT NOT NULL,
비밀번호 TEXT NOT NULL,
기본 키(id)
);
INSERT INTO 사용자 VALUES ( null, 'jack', 'pass' );
INSERT INTO 사용자 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");
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를 가져옵니다. 코드는 원본 버전 및 관련 패턴보다 훨씬 간단하고 효율적입니다.
질문 3: 여러 데이터베이스 사용
때때로 각 테이블이 별도의 데이터베이스에 있는 애플리케이션을 볼 수 있습니다. 이는 매우 큰 데이터베이스에서는 합리적이지만 일반 응용 프로그램에서는 이 수준의 분할이 필요하지 않습니다. 또한, 관계형 쿼리는 여러 데이터베이스에 걸쳐 수행될 수 없으며, 이는 관계형 데이터베이스를 사용한다는 전체적인 아이디어에서 벗어나며, 여러 데이터베이스에 걸쳐 테이블을 관리하는 것이 더 어려울 것이라는 점은 말할 것도 없습니다. 그렇다면 다중 데이터베이스는 어떤 모습이어야 할까요? 먼저, 몇 가지 데이터가 필요합니다. Listing 7에서는 이러한 데이터를 4개의 파일로 나누어 보여줍니다.
목록 7. 데이터베이스 파일
Files.sql:
테이블 파일 생성(
ID MEDIUMINT,
user_id MEDIUMINT,
이름 텍스트,
경로 텍스트
)
;
INSERT INTO 파일 VALUES ( 1, 1, 'test1.jpg', 'files/test1.jpg' );
INSERT INTO 파일 VALUES (2, 1, 'test2.jpg', 'files/test2.jpg' )
;
존재하는 경우 테이블 삭제 사용자;
CREATE TABLE 사용자(
ID MEDIUMINT,
로그인 텍스트,
비밀번호 텍스트
)
;
INSERT INTO 사용자 VALUES ( 1, 'jack', 'pass' );
INSERT INTO users 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( "WHERE 로그인=?",array( $name ) ) ;
$uid = null;
while( $res->fetchInto( $row ) ) { $uid = $row[0] }
return $uid;
}
함수 get_files( $name )
{
$uid = get_user( $name );
$rows = array()
= '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( 'jack' );
var_dump( $files );
?>
get_user 함수는 사용자 테이블이 포함된 데이터베이스에 연결하고 특정 사용자의 ID를 검색합니다. get_files 함수는 파일 테이블에 연결하고 특정 사용자와 연관된 파일 행을 검색합니다.
이러한 모든 작업을 수행하는 더 좋은 방법은 데이터를 데이터베이스에 로드한 다음 아래와 같은 쿼리를 실행하는 것입니다.
목록 9. Getfiles_good.php
<?php
require_once("DB.php");
함수 get_files( $name )
{
$rows = 배열();
$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 사용자, 파일 WHERE
users.login=? AND users.id=files.user_id",
배열( $이름 ) );
while( $res->fetchInto( $row ) ) { $rows[] = $row }
return $rows;
}
$files = get_files( 'jack' );
var_dump( $files );
?>
코드가 더 짧을 뿐만 아니라 이해하기 쉽고 효율적입니다. 두 개의 쿼리를 실행하는 대신 하나의 쿼리를 실행합니다.
이 질문이 터무니없는 것처럼 들릴 수도 있지만 실제로는 매우 설득력 있는 이유가 없는 한 일반적으로 모든 테이블이 동일한 데이터베이스에 있어야 한다고 결론을 내립니다.
질문 4: 관계를 사용하지 않음
관계형 데이터베이스는 배열 유형이 없다는 점에서 프로그래밍 언어와 다릅니다. 대신 테이블 간의 관계를 사용하여 객체 간에 일대다 구조를 생성하는데, 이는 배열과 동일한 효과를 갖습니다. 내가 응용 프로그램에서 본 한 가지 문제는 엔지니어가 쉼표로 구분된 식별자가 있는 텍스트 문자열을 사용하여 배열을 생성하여 데이터베이스를 프로그래밍 언어처럼 사용하려고 하는 것입니다. 아래 패턴을 참조하세요.
목록 10. Bad.sql
DROP TABLE IF EXISTS 파일;
테이블 파일 생성(
ID MEDIUMINT,
이름 텍스트,
경로 텍스트
)
존재하는 경우 테이블 삭제;
CREATE TABLE 사용자(
ID MEDIUMINT,
로그인 텍스트,
비밀번호 텍스트,
파일 TEXT
);
파일에 삽입 VALUES ( 1, 'test1.jpg', 'media/test1.jpg' );
INSERT INTO 파일 VALUES (2, 'test1.jpg', 'media/test1.jpg' );
INSERT INTO users VALUES (1, 'jack', 'pass', '1,2' )
시스템의 사용자는 여러 파일을 가질 수 있습니다. 프로그래밍 언어에서는 사용자와 관련된 파일을 나타내기 위해 배열을 사용해야 합니다. 이 예에서 프로그래머는 쉼표로 구분된 파일 ID 목록을 포함하는 파일 필드를 생성하도록 선택합니다. 특정 사용자에 대한 모든 파일 목록을 얻으려면 프로그래머는 먼저 사용자 테이블에서 행을 읽은 다음 파일의 텍스트를 구문 분석하고 각 파일에 대해 별도의 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( "로그인=?",array( $name ) ) ;
$파일 = null;
while( $res->fetchInto( $row ) ) { $files = $row[0]; }
$rows = array();
foreach( 분할( ',',$files ) $file )
{
$res = $db->query( "SELECT * FROM 파일 WHERE ID=?",
배열( $file ) );
while( $res->fetchInto( $row ) ) { $rows[] = $row }
}
$행을 반환합니다.
}
$files = get_files( 'jack' );
var_dump( $files );
?>
기술이 느리고, 유지 관리가 어렵고, 데이터베이스를 제대로 활용하지 못합니다. 유일한 해결책은 스키마를 다시 설계하여 아래와 같이 전통적인 관계형 형식으로 다시 변환하는 것입니다.
목록 12. Good.sql
DROP TABLE IF EXISTS 파일;
테이블 파일 생성(
ID MEDIUMINT,
user_id MEDIUMINT,
이름 텍스트,
경로 텍스트
)
존재하는 경우 테이블 삭제;
CREATE TABLE 사용자(
ID MEDIUMINT,
로그인 텍스트,
비밀번호 텍스트
);
INSERT INTO 사용자 VALUES ( 1, '잭', '패스' );
INSERT INTO 파일 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("파일을 선택하세요.* 사용자로부터,파일은 어디에 users.login=?
AND users.id=files.user_id",array( $name ) );
while( $res->fetchInto( $row ) ) { $rows[] = $row }
$행을 반환합니다.
}
$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 작성자;
CREATE TABLE 작성자(
ID MEDIUMINT NOT NULL AUTO_INCREMENT,
이름 TEXT NOT NULL,
기본 키(id)
)
책이 있으면 테이블을 삭제하세요.
테이블 책 만들기(
ID MEDIUMINT NOT NULL AUTO_INCREMENT,
작성자_ID MEDIUMINT NOT NULL,
이름 TEXT NOT NULL,
기본 키(id)
);
INSERT INTO 작성자 VALUES( null, 'Jack Herrington' );
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()) }
function get_author_id( $name )
{
global $db;
$res = $db->query( "WHERE 이름=?에서 작성자의 ID 선택",array( $name ) );
$id = null;
while( $res->fetchInto( $row ) ) { $id = $row[0] };
$id를 반환합니다.
}
함수 get_books( $id )
{
global $db;
$res = $db->query( "author_id=?",array( $id ) );
$ids = 배열();
while( $res->fetchInto( $row ) ) { $ids []= $row[0] }
$id를 반환합니다.
}
함수 get_book( $id )
{
global $db;
$res = $db->query( "SELECT * FROM books WHERE id=?", array( $id ) );
while( $res->fetchInto( $row ) ) { $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의 책을 검색하기 위해 얼마나 많은 쿼리가 실행되는지 확인하세요. 한 번은 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("책을 선택하세요.* 도서.author_id=authors.id AND Authors.name=?에서 저자,책을 선택하세요.",
배열( $이름 ) );
$행 = 배열();
while( $res->fetchInto( $row ) ) { $rows []= $row }
$행을 반환합니다.
}
$books = get_books( '잭 헤링턴' );
var_dump($books);
?>
이제 목록을 검색하려면 빠른 단일 쿼리가 필요합니다. 즉, 다양한 매개 변수를 사용하여 이러한 유형의 여러 메서드를 가져야 할 가능성이 높지만 실제로는 선택의 여지가 없습니다. 확장 가능한 PHP 애플리케이션을 원한다면 데이터베이스를 효율적으로 사용해야 하며, 이는 더 스마트한 쿼리를 의미합니다.
이 예의 문제점은 너무 명확하다는 것입니다. 일반적으로 이러한 유형의 n+1 또는 n*n 문제는 훨씬 더 미묘합니다. 그리고 시스템에 성능 문제가 있을 때 데이터베이스 관리자가 시스템에서 쿼리 프로파일러를 실행할 때만 나타납니다.
결론
데이터베이스는 강력한 도구이며 모든 강력한 도구와 마찬가지로 올바르게 사용하는 방법을 모른다면 남용할 수 있습니다. 이러한 문제를 식별하고 해결하는 비결은 기본 기술을 더 잘 이해하는 것입니다. 나는 오랫동안 비즈니스 로직 작성자들이 데이터베이스나 SQL 코드를 이해할 필요가 없다고 불평하는 것을 들어왔습니다. 그들은 데이터베이스를 객체로 사용하고 왜 성능이 그렇게 좋지 않은지 궁금해합니다.
그들은 데이터베이스를 어려운 필요성에서 강력한 동맹으로 전환하는 데 SQL을 이해하는 것이 얼마나 중요한지 깨닫지 못합니다. 데이터베이스를 매일 사용하지만 SQL에 익숙하지 않다면 The Art of SQL을 읽어보세요. 데이터베이스에 대한 기본적인 이해를 도와줄 수 있는 잘 쓰여진 실용적인 책입니다.