PHP 데이터베이스의 다섯 가지 문제 (한글)

데이터베이스 스키마 디자인, 데이터베이스 액세스, 데이터베이스를 사용하는 비즈니스 로직 코드 등, PHP 애플리케이션에서 발생하는 다섯 가지 일반적인 데이터베이스 문제와 이것의 해결 방법에 대해 알아봅니다.

글쓴이 : 유광희 날짜 : 2006-11-10 (금) 17:38 조회 : 8540

http://www-128.ibm.com/developerworks/kr/library/os-php-dbmistake/ (939)

http://www-128.ibm.com/developerworks/kr/library/os-php-dbmistake/ (651)

출처 : 한국 IBM developerWorks

난이도 : 중급

Jack Herrington, Senior Software Engineer, Studio B

2006 년 10 월 31 일

데이터베이스 스키마 디자인, 데이터베이스 액세스, 데이터베이스를 사용하는 비즈니스 로직 코드 등, PHP 애플리케이션에서 발생하는 다섯 가지 일반적인 데이터베이스 문제와 이것의 해결 방법에 대해 알아봅니다.

데이터베이스를 올바르게 사용하는 단 한 가지 방법만 있다면...

여러 가지 방법으로 데이터베이스 디자인, 데이터베이스 액세스, PHP 비즈니스 로직 코드를 만들 수 있고, 이 모든 것을 종종 망치기도 한다. 이 글에서는 데이터베이스 디자인과, 데이터베이스에 액세스 하는 PHP 코드의 다섯 가지 대표적인 문제들을 설명하고, 문제를 해결하는 방법을 설명한다.

문제점 1: MySQL을 직접 사용하기

첫 번째 문제는 mysql_ 함수를 사용하는 오래된 PHP 코드가 데이터베이스에 직접 액세스 하는 것이다. Listing 1은 데이터베이스에 직접 액세스 하는 방법을 보여주고 있다.

Listing 1. /get.php에 액세스 하기

mysql_connect 함수를 사용하여 데이터베이스에 액세스 하고 있다. $name 매개변수를 쿼리에 추가하기 위해 스트링 연결(concatenation)을 사용하는 쿼리도 주목하라.

이 기술은 두 개의 훌륭한 대안을 갖고 있다. 바로 PEAR DB 모듈과 PHP Data Objects (PDO) 클래스이다. 두 개 모두 선택된 데이터베이스에서 추상화를 제공한다. 따라서, 코드는 IBM? DB2?, MySQL, PostgreSQL 또는 다른 데이터베이스에 맞춰 조정하지 않고도 실행될 수 있다.

PEAR DB 모듈과 PDO의 추상화 레이어를 사용할 때의 또 다른 이점은 SQL 문에 ? 연산자를 사용할 수 있다는 점이다. 이렇게 하면 SQL을 관리하기가 더 쉬워지고, SQL 인젝션 공격에서 애플리케이션을 보호할 수 있다.

PEAR DB를 사용하는 대안 코드는 다음과 같다.

Listing 2. /get_good.php에 액세스 하기

                                
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' ) );
?>

MySQL의 모든 직접적인 언급은 $dsn의 데이터베이스 연결 스트링을 제외하고는 없어진다. 게다가, 우리는 ? 연산자를 통해서 SQL에서 $name operator. Then, the data for the query is sent in through array 변수를 사용한다. 이 쿼리에 대한 데이터는 query() 메소드의 끝에서 array를 통해서 보내진다.

위로

문제점 2: 자동 증가 기능을 사용하지 않는 것

대부분의 데이터베이스와 마찬가지로, MySQL도 레코드 기반으로 자동 증가하는 고유 식별자를 만드는 기능이 있다. 그럼에도 불구하고, 우리는 여전히, 최대 id를 찾기 위해 SELECT 문을 처음 실행하고, 하나를 그 id, then adds one to that id와 새로운 레코드에 추가하는 코드를 본다. Listing 3은 잘못된 스키마 샘플이다.

Listing 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 문을 보라. Listing 4는 사용자들을 이러한 유형의 스키마에 추가하는 PHP 코드 모습이다.

Listing 4. Add_user.php

                                
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 값에 하나를 더하여 INSERT 문을 실행한다. 이 코드는 부하가 큰 서버에서 경쟁 조건에서 실패할 수 있다. 게다가, 이는 효율성도 떨어진다.

대안은 없을까? MySQL의 자동 증가 기능을 사용하여 각 삽입에 대한 고유 ID를 자동으로 만들 수 있다. 업데이트 된 스키마가 아래 보인다.

Listing 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 플래그를 추가하여 이 필드가 무효가 되어서는 안된다는 것을 나타냈다. 또한, AUTO_INCREMENT 플래그를 추가하여 이 필드가 자동 증가한다는 것을 나타냈고, PRIMARY KEY 플래그를 통해서 어떤 필드가 id인지를 나타냈다. 이러한 변화로 속도가 빨라졌다. Listing 6은 업데이트 된 PHP 코드로서, 사용자를 테이블에 삽입한다.

Listing 6. Add_user_good.php

                                
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를 관리하는 시스템을 사용해야 하고, 처음 쿼리했던 시스템에 의존하지 말아야 하며, 값을 스스로 증가시키고, 레코드를 추가하도록 한다. 후자의 방식은 높은 용량의 사이트에서 경쟁 조건이 생길 수 있다.

위로

문제점 3: 여러 데이터베이스 사용하기

가끔씩, 각 테이블이 독립된 데이터베이스에 있는 애플리케이션을 보게 된다. 과도하게 큰 데이터베이스라면 이렇게 하는 데는 이유가 있지만, 보통의 애플리케이션이라면, 이렇게 분리할 필요가 없다. 게다가, 여러 데이터베이스들에 연관 쿼리를 수행할 수 있지만, 이렇게 하지 말라고 말하고 싶다. 문법은 더 복잡하다. 백업과 복구가 쉽지 않다. 문법은 다른 데이터베이스 엔진들에서 작동할 수도 있고, 아닐 수도 있다. 게다가, 테이블이 여러 데이터베이스에 걸쳐 나뉘어지면 관계 구조를 따르기 어렵다.

따라서, 여러 데이터베이스는 어떤 모습일까? 시작하기 전에, 약간의 데이터가 필요하다. Listing 7은 네 개의 파일로 나뉜 데이터를 보여주고 있다.

Listing 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 문을 하나의 데이터베이스로 로딩한 후, users SQL 문을 또 다른 데이터베이스로 로딩한다. 아래 코드는 특정 사용자와 관련된 파일을 위해 데이터베이스를 쿼리하는 PHP 코드이다.

Listing 8. Getfiles.php

                                
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 함수는 그 파일 테이블로 연결하고, 그 사용자와 연결된 파일 행(row)들을 불러온다.

이 모든 것을 수행하는 더 나은 방법은 데이터를 하나의 데이터베이스로 로딩한 다음, 아래와 같이 쿼리를 수행하는 것이다.

Listing 9. Getfiles_good.php

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

이 코드는 더 짧고, 이해하기도 쉽고 효율적이다. 두 개의 쿼리를 수행하는 대신, 하나만 수행한다.

이 문제는 다소 억지스럽지만, 급박한 문제가 아닌 이상, 모든 테이블들은 같은 데이터베이스에 있어야 한다는 것을 깨닫게 해준다.

위로

문제점 4: 관계(relation)를 사용하지 않는 것

관계형 데이터베이스는 프로그래밍 언어와는 다르다. 이들은 어레이(array) 유형을 갖고 있지 않다. 대신, 이들은 테이블들 간에 관계(relation)를 사용하여 객체들 간 일대다 구조를 만드는데, 이는 어레이와 같은 효과가 있다. 애플리케이션들과 관련하여 내가 경험했던 한 가지 문제는, 엔지니어가 프로그래밍 언어에서 했던 것처럼, 데이터베이스를 사용하려고 할 때, 콤마로 구분된 식별자들을 가진 테스트 스트링을 사용하여 어레이를 만든다는 것이다.

Listing 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' );

시스템의 한 명의 사용자는 여러 파일들을 가질 수 있다. 프로그래밍 언어에서, 어레이를 사용하여 사용자와 관련된 파일들을 나타낸다. 이 예제에서, 프로그래머는 콤마로 분리된 id 파일 리스트를 포함하고 있는 files 필드를 만들었다. 특정 사용자에 대한 모든 파일들의 리스트를 얻으려면, 프로그래머는 사용자 테이블에서 행을 우선 읽고, 파일의 텍스트를 파싱하고, 각 파일에 개별 SELECT 문을 실행해야 한다. 코드는 아래와 같다.

Listing 11. Get.php

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

이 방법은 느리고, 관리하기도 어려우며, 데이터베이스를 잘 활용하지 못하는 것이다. 유일한 솔루션은 스키마를 다시 짜서 전통적인 관계형 폼으로 전향하는 것이다.

Listing 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 함수를 통해 각 파일은 사용자와 연관된다. 이것을 어레이로 간주하는 모든 사람들에게는 구식처럼 보인다. 확실히, 어레이는 저장된 객체들을 참조하지 않는다. 사실, 그 반대다. 하지만, 관계형 데이터베이스에서는 이러한 방식이 사용되고, 때문에 쿼리는 더 빠르고 쉬워진다. Listing 13은 이와 관련한 PHP 코드이다.

Listing 13. Get_good.php

                                
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 패턴

코드가 엔터티 리스트(예를 들어, 고객)를 가져온 다음, 다시 돌아가서, 이들을 하나씩 가져와서 각 엔터티에 대한 상세를 얻는 방식의 대형 애플리케이션을 많이 보았다. 우리는 이것을 n+1 패턴이라고 부른다. 많은 쿼리들이 수행될 것이기 때문이다. 모든 엔터티들의 리스트를 가져오기 위한 쿼리와, n 개의 엔터티들을 위한 쿼리가 필요하다. n=10 일 경우는 문제가 아니지만, n=100 또는 n=1000 같은 경우는 어떻게 할 것인가? 비효율성은 말할 나위가 없다. Listing 14는 이 같은 스키마 예제이다.

Listing 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' );

스키마는 올바르다. 잘못된 것이 없다. 문제는 코드에 있다. 데이터베이스에 액세스 하여 해당 저자에 대한 모든 책들을 찾는다.

Listing 15. Get.php

                                
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를 얻은 다음, 도서 리스트를 얻고, 각 책에 대한 정보를 얻는다. 깔끔하다. 하지만 이것이 과연 효율적일까? 아니다. Jack Herrington이라는 사람이 쓴 책을 검색하기 위해 얼마나 많은 쿼리를 해야 할까? 하나의 쿼리로 id를 얻고, 또 다른 쿼리로 도서 리스트를 얻고, 그 다음 쿼리로 책들을 파악한다. 세 권의 책을 찾는데 다섯 개의 쿼리가 필요하다니!

솔루션은 하나의 벌크(bulk) 쿼리를 수행하는 함수이다.

Listing 16. Get_good.php

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

리스트를 가져오기 위해서는 빠른, 하나의 쿼리가 필요하다. 다른 매개변수들을 가진 이러한 유형의 메소드들이 여러 개 필요하다는 소리 같지만, 상관없다. 하나의 PHP 애플리케이션을 갖고 싶다면 데이터베이스를 효율적으로 사용해야 한다. 다시 말해서 현명한 쿼리를 해야 한다.

이 예제의 문제는 너무 명확하다는 것이다. 일반적으로, n+1 이나 n*n 문제들은 훨씬 더 미묘하다. 게다가, 퍼포먼스 문제가 생기고 데이터베이스 관리자가 시스템에 쿼리 프로파일러를 실행할 때만 이러한 문제가 발생한다.

위로

결론

데이터베이스는 강력한 툴이고, 모든 강력한 툴이 그러하듯, 올바르게 사용하는 방법을 모른다면 남용될 소지가 있다. 이러한 문제들을 규명하고 해결하는 방법은 기반 기술을 이해하는 것이다. 비즈니스 로직 코더들이 데이터베이스나 SQL 코드를 알고 싶지 않다고 징징대는 소리를 많이 들었다. 그들은 데이터베이스를 객체들로 래핑해놓고, 왜 퍼포먼스가 형편없는지를 의아해 한다.

이들은 데이터베이스를 효율적을 활용하기 위해서는 SQL을 이해하는 것이 가장 우선이라는 것을 모르고 있다. 매일 데이터베이스를 사용하지만, SQL을 잘 활용할 수 없다면, The Art of SQL을 읽어보기 바란다. 데이터베이스를 최대로 활용할 수 있는 실질적인 가id이다.

기사의 원문보기

Five common PHP database problems

참고자료

교육

The Art of SQL , by Stephane Faroult and Peter Robson
PHP.net
PEAR DB documentation
PDO Functions documentation
MySQL.org
PHP project resources
developerWorks technical events and webcasts.
Events
오픈 소스 존
developerWorks podcasts.

제품 및 기술 얻기

IBM trial software

토론

developerWorks blogs.

필자소개


		Jack D. Herrington은 20년 경력의 소프트웨어 엔지니어이다: Code Generation in Action, Podcasting Hacks,, PHP Hacks의 저자이며, 30편의 기술자료를 기고했다.