데이터베이스, PHP를 만나면「알짜 사이트로 부활!」
- PHP는 거의 모든 데이터베이스를 지원한다. 많은 프로젝트에서 PHPer들은 다양한 데이터베이스 연동에 대한 요구를 받는다. 하지만 기초를 다지거나, 기술력을 높이기 위한 전략적인 프로그래밍을 하다 보면 하나의 데이터베이스에 의존하는 경우가 많다. 개발자들에게 요구되는 기술력이라는 것은 결국 다양한 경험에 기반을 둔다고 볼 수 있다. 진정한 개발자의 모습은 어떤 것일까? 언제나 다양성 앞에 놓여 있고, 복잡성에 도전하는 게 개발자의 모습이 아닐까라는 생각을 하면서 이 글을 시작한다.
웹의 무수한 확장력은 가공할 만큼 커지고 있다. 여기에 부각되는 다양한 언어들이 수많은 현장에서 SI라는 명분으로, 웹이라는 명분으로, 인터넷과 인트라넷이라는 명분으로 쓰여지고 대중화되고 있다. PHP 또한 이러한 확장력에 힘을 싣고 있다.
PHP의 핵심적인 확장력은 무엇일까? 여러 가지가 있을 수 있겠지만 단연히 데이터베이스에 대한 강력한 핸들링이라는 측면을 들 수 있겠다. PHP는 거의 모든 데이터베이스를 지원한다. 이번 호에서는 데이터베이스에 대한 핸들링과 나눌 수 있는 팁이 있다면 함께 고민해 보는 자리로 만들고자 한다.
상세한 데이터베이스의 설치나 DBA(DataBase Administrator)가 다루는 기법은 다루지 않겠다. 하지만 기존의 책자에 다양하게 소개되지 않은 부분이 있다면 그것을 다루어 봤으면 한다. 다루고자 하는 데이터베이스는 가장 많이 사용되는 MySQL, 윈도우 진영의 MSSQL, 대형 프로젝트에 자주 사용되는 오라클, 사이베이스로 분류해 진행하고자 한다.
가장 많이 다뤄진 MySQL과 궁합 맞추기
PHP와 데이터베이스라면 역시 MySQL을 들 수 있다. 중소 사이트에선 거의 MySQL이 사용되었다고 해도 과언이 아닐 정도로 많은 분야에서 MySQL이 사용되고 있다. 또한 MySQL은 리눅스 버전이 아니고 윈도우 버전도 계속 업데이트되면서 그 확장력을 확보하고 있다. 자료를 찾지 못해 이번 호에 게재를 못했는데 얼마 전 데이터베이스 벤치마킹에서 오라클 9i와 MySQL이 성능 및 속도에서 1위를 했다는 기사를 보았었다. 이렇듯 MySQL은 계속 확장하는 큰 물결이라고 볼 수 있다. 관련 프로그램도 많이 지원되는데 일례로 MySQL Front 같은 유틸리티는 MSSQL 엔터프라이즈 관리자와 같은 UI를 제공해 개발자에게 편리한 개발환경을 제공하기도 한다.
MySQL은 기존에도 많이 다루었던 부분임으로 간단한 사용 루틴을 설명하고 나머지 부분은 MySQL 팁에 대해 다뤄보도록 하겠다. 데이터베이스 프로그래밍은 SELECT, UPDATE, DELETE문을 이용해 화면에 어떻게 뿌려주는가가 가장 기본적인 컨셉이다. 간단한 웹 게시판 리스팅 소스 코드를 보자.
<?
mysql_connect("host","user","pass");
mysql_select_db("user_db");
$query = mysql_query("select * from web_board");
$all = mssql_num_rows($query);
for($i=0;$i<$all;$i++)
{
$seek = mysql_data_seek($query,$i);
$row = mysql_fetch_array($query);
$number = $row[number];
$nam = $row[name];
$date = chop($row[write_date]);
$title = $row[title];
$conten = $row[content];
$hit = $row[hit];
}//ending of for loof
?>
단순히 테이블을 SELETE한 후 리스트를 가져오는 구문이다. 경험상 이 구문은 모든 웹 프로그램의 기본이다. 데이터베이스 프로그래밍에서 HTML. 스크립트를 포함한 기법을 제외하고는 모두 이 틀에 기초하고 있다고 해도 과언이 아니다. 앞 코드도 DB 연결 -> SQL 실행 -> 루프 -> 순차적 데이터 추출의 과정이다. 물론 좀더 정교한 프로그래밍을 한다면, WHERE 조건문, GROUP BY 등 SQL이 정밀하게 수정돼야 하고 리스팅을 위해 페이징 기법도 추가될 것이다.
여기서는 단순한 구조만 이해하고 넘어가도록 하겠다. 너무 많이 다룬 부분일 것 같아서 MySQL에 대한 특별한 설명은 하지 않겠다. 여기서는 mysql_data_seek() 함수와 mssql_fetch_array() 함수만 이해하면 될 듯 하다. mysql_data_seek는 MySQL 쿼리 결과의 커서의 위치를 가리키는 함수이다. 예를 들어 쿼리의 결과가 다섯 개가 나왔다면 이것을 순차적으로 추출하기 위해 mysql_data_seek() 함수를 이용해 첫 번째 인자에는 유효한 SQL 쿼리문을 넣고, 두 번째 인자에는 커서의 위치를 넣는 것이다.
0을 넣게 되면 첫 번째 row를 가져올 것이다. mssql_fetch_array() 함수는 0번째 row를 배열에 담는 과정이다. 이렇게 해서 $row에서 추출해 데이터 정보를 정렬하면 된다. 이 부분만 짚고 넘어가도 충분히 이해할 수 있으리라 생각된다. 또한 UPDATE, DELETE문은 따로 함수나 구문을 이용하는 것이 아니고 mysql_query() 함수만 사용하면 쿼리를 실행할 수 있다.
데이터베이스 프로그래밍할 때 가장 시간이 오래 걸리는 것은 관련 SQL문을 만드는 것과 실제로 데이터 값이 POST나 GET으로 잘 넘어가는지를 체크하고 디버깅하는 경우다. 이 시간을 줄이기 위해 어떤 개발자는 POST나 GET으로 넘어가는 모든 값을 출력해 주는 디버깅 툴을 간단히 만들어서 사용하기도 한다. MySQL은 이런 개발을 지원하는 툴이나 프로그램이 많이 제공된다. 개발자가 조금만 신경쓰면 개발에 많은 도움이 될 것이라고 생각된다. 최신 정보를 잘 활용하자.
좀더 빠르게! MySQL 데이터베이스 활용법
● *를 사용하지 마라
무심코 개발자들은 SELETE문을 던질 때 * 기호를 많이 쓴다. 하지만 이것은 관련되어 있는 필드를 모두 가져오는 행위이기 때문에 비효율적이라고 할 수 있겠다. 다음과 같이 필요한 필드만 불러와서 효율적인 쿼리를 만들도록 하자.
“SELECT 필드 1, 필드 2 FROM 테이블 명”
● mysql_result() 함수보다는 mysql_data_seek()를 사용하자
$date = mysql_result($query_result, $i, 0); 소스를 보면 $i개의 0번째 컬럼을 호출한다. 이런 방법은 컬럼 개수가 맞지 않거나, 정확한 컬럼의 특징(제목)을 알아내기가 힘들고 컬럼의 업데이트가 발생했을 때 프로그램 전체를 수정하는 문제가 발생할 수도 있다. 따라서 앞의 리스트와 같이 mysql_data_seek를 선택해 row에 배열로 담아서 꺼내면, 잘못 호출했을 때 null로 인식하기 때문에 오류를 줄일 수 있고, 데이터베이스의 수정이 발생하더라도 해당 필드의 정보만 추가하면 된다.
● limit를 잘 활용하자
MySQL에서는 LIMIT 속성을 제공한다. LIMIT는 정확히 그 개수만큼 가져온 뒤에 테이블의 나머지 레코드에 대해서는 WHERE 조건문에 만족하는 레코드가 있는지 검사하지 않아도 되므로 속도가 빠르다. 다시 말해 속도 차이는 LIMIT를 만족하면, 바로 쿼리 실행을 중지하므로 더 이상 WHERE 검사를 하지 않는 데서 발생한다.
● 인덱스를 사용하자
인덱스를 사용하고 하지 않고는 속도에서 엄청난 차이가 난다. MySQL의 모든 인덱스(PRIMARY, UNIQUE AND INDEX())는 B-트리에 저장된다. 문자열은 자동으로 앞뒤의 공간이 압축된다. 인덱스는 다음과 같은 곳에 사용하면 아주 유용하다.
◆ WHERE문에서 해당하는 레코드 빨리 찾기
◆ 조인을 수행할 때 다른 테이블에서 레코드 가져오기
◆ 특정 키에서 MAX()나 MIN() 값 찾기
◆ 정렬이나 그룹화할 때 인덱스 키를 사용하면 테이블을 정렬하거나 그룹화한다.
키에 DESC가 붙으면 역순으로 인덱스를 읽는다.
◆ 어떤 경우에는 데이터 파일에 묻지 않고 값을 가져온다. 어떤 테이블에서 사용
하는 모든 컬럼이 숫자이고 특정 키로 형성되어 있으면 빠른 속도로
인덱스 트리에서 값을 가져올 수 있다.
● varchar와 char의 차이를 이해하자
varchar는 가변형 컬럼이고, char는 고정형 컬럼이다. 속도는 테이블 컬럼이 고정된 즉, varchar 같은 타입이나 bolb, text 타입이 들어가지 않는 고정 테이블이 빠르다. char형은 고정 길이이기 때문에 속도 면에서 빠르며, 이유는 메모리 사용이나 스토리지(HDD) 사용 면에서 일정한 길이이기 때문이다. 스토리지에 부담이 없는 프로젝트라면 char형 같은 고정 길이를 추천한다.
MSSQL로 PHP를 윈도우 서버에서 개발하자
윈도우 서버는 광범위하게 쓰여지고 있다. 많은 개발자들이 PHP를 윈도우 서버에 포팅시켜 솔루션을 개발하고 있다. 윈도우 서버의 대표적인 데이터베이스가 MSSQL이다. MSSQL은 상용 데이터베이스로 편리한 UI를 제공해 관리자 및 개발자가 사용하기에 편리한 장점이 있다.
혹자는 MSSQL과 PHP를 연동하면 윈도우 계열이기 때문에 많이 느리다고 말하는데, 필자의 경우 윈도우에서 PHP를 많이 개발해 본 결과 실제 ASP보다 느리다고 생각한 적은 한 번도 없었다. 그 정도로 PHP는 넓은 확장성을 가지는 장점이 있다고 말하고 싶다. MSSQL 연동의 기본적인 골격을 살펴보자.
<?
mssql_connect("host","user","pass");
mssql_select_db("user_db");
$query = mssql_query("select * from web_board");
$all = mssql_num_rows($query);
for($i=0;$i<$all;$i++)
{
$seek = mssql_data_seek($query,$i);
$row = mssql_fetch_array($query);
$number = $row[number];
$name = $row[name];
$date = $row[write_date];
$title = $row[title];
$content = $row[content];
$hit = $row[hit];
}//ending of for loof
?>
독자들은 한눈에 MySQL과 MSSQL이 거의 유사하다는 것을 알 수 있다. 아마도 PHP 라이브러리 개발자들의 배려이지 않을까 싶다. 개발자들은 mysql이라는 글자를 mssql로만 바꾸면 바로 mssql에 포팅시킬 수 있다. msslq_connect() 함수로 서버에 접속하고 mssql_select_db()로 데이터베이스를 선택한다. mssql_query()문은 SQL문을 실행시키는 함수이다. 이 쿼리를 mssql_num_rows() 함수를 이용해 전체 행의 개수를 구한다. for문에서 sql_data_seek() 함수로 $i번째 row를 선택해 mssql_fetch_array() 함수를 이용해 $row 배열에 담아 출력하는 루틴이다.
MSSQL은 윈도우 환경이기 때문에 개발을 더욱 직관적으로 진행할 수 있는 장점이 있다. 이러한 장점과 함께 제공하는 다양한 기능을 활용함으로써 최적의 사이트를 구축할 수 있을 것이다.
MS SQL 활용하기
● 스토어 프로시저를 활용하자
데이터베이스 프로그래밍을 하다 보면 통계나 축적치에 대한 결과를 보아야 할 경우가 많다. 예를 들면 일별 통계, 월별 통계 같은 경우가 그런 것이다. 우리가 일반적으로 count나 sum, avg로 통계를 내고자 할 경우 많은 데이터 양으로 인해 제대로 된 결과를 시간내에 볼 수 없는 경우가 많다. 필자는 프로젝트시에 통계 관련 모든 데이터는 시간, 일, 월의 통계를 낼 수 있는 스토어 프로시저를 만들어 작업 일정에 추가해 주기적으로 자동 작동하도록 만든다. 이렇게 하면 아주 빠른 시간내에 만들고 싶은 통계 데이터를 쉽게 만들 수 있다. 데이터베이스에서 제시하는 스토어 프로시저의 장점은 다음과 같다.
◆ 여러 클라이언트간의 업무 규칙 공유
◆ 데이터베이스 내부 구조 감추기
◆ 서버 보호, 데이터 통합 구현
◆ 쿼리 처리 속도 향상
◆ 네트워크 트래픽 감소
● 뷰를 잘 활용하자
뷰도 마찬가지로 공통 업무나 반복적인 쿼리 또는 아주 복잡한 쿼리를 위해 꼭 필요하다. 뷰는 SELECT문을 바탕으로 한 일종의 가상 테이블이다. 하지만 뷰는 테이블이나 인덱스처럼 자체 데이터를 저장하기 위한 저장 영역을 사용하지 않는다. 뷰는 다만 사용자 정의 데이터 타입, 디폴트, 룰처럼 자신의 정의만이 해당 데이터베이스 내의 sysobjects, syscomments등 시스템 테이블에 레코드로서 저장되어 있다. 뷰의 장점은 다음과 같다.
◆ 뷰는 일반 사용자 또는 개발자들에게 필요한 정보를 아주 쉽게 제공해 줄 수 있다.
◆ 뷰가 참조하는 테이블 구조가 바뀌더라도 쿼리문을 수정할 필요가 없다.
◆ 뷰는 보안과 관련해 테이블에서 사용자들에게 필요한 레코드, 필요한 컬럼 데이터만을 선별적으로 보여줄 수 있다.
간단히 정리하면 뷰는 복잡한 SELECT 쿼리문을 아주 단순하게 만들어 서버에 저장해 둔 것이다. 이것은 클라이언트가 그렇게 복잡한 SELET문을 반복적으로 사용하기 싫을 때 사용하면 매우 편리하다. PHP에서 데이터베이스를 액세스할 때는 스크립트 내에 복잡한 SELECT문을 사용하지 않는 것이 좋다. 이것은 스크립트 파일의 크기를 늘리고 스크립트의 가독성과 보안을 떨어뜨리며 스크립트의 속도 저하의 원인이 되기도 한다.
● 트리거를 활용하자
트리거는 자기가 종속된 특정 테이블에서 데이터의 변화가 발생했을 때 자동으로 실행되는 아주 특별한 종류의 스토어 프로시저이다. 어떤 테이블의 데이터가 INSERT, UPDATE 또는 DELETE문을 만나 변화가 생길 때 자동으로 실행되게끔 정의해 놓은 Transact-SQL문의 집합이라고 볼 수 있다. 트리거는 다음과 같은 장점을 제공한다.
◆ 복잡한 데이터 통합을 구현해 준다.
◆ 역정규화된(denormalized) 데이터 관리를 해준다.
◆ 복잡한 업무 규칙을 단순화시킬 수 있다.
● 메모리 버퍼를 잘 잡아서 속도를 보장하자
MSSQL서버는 메모리를 동적으로 잡는다. 물론 시스템 자원이 요청하면 메모리를 클리어시키지만 그렇지 않으면 계속 메모리가 늘어난다. 필자는 1GB 메모리의 시스템에서 980MB의 메모리를 잡고 있는 SQL 서버를 처음 봤을 때 무척이나 놀랐던 기억이 있다. 물론 DB 서버가 독립적으로 운영된다면 동적으로 메모리를 잡는 것이 낫지만, 여러 가지 서버가 한 서버에 연동돼 작동되는 서버라면 고정 메모리를 시스템 성능에 맞게 적절하게 분석해 설정할 경우 서버 속도가 훨씬 안정적일 것이다.
● 초기 설정이 시스템 속도에 많은 영향을 미친다
우리는 데이터베이스를 설정할 때 초기 값을 디폴트로 하는 경우가 많다. 필자도 프로젝트에서 데이터베이스를 무심코 디폴트로 생성하곤 했는데, 이렇게 했을 경우 이후 시스템 튜닝이 어려워질 수 있다. 초기에 데이터베이스를 생성할 때 필수적으로 로그와 데이터 크기를 감안해 데이터베이스의 용량을 적절하게 잡아주는 것도 중요하다.
실제 적은 양으로 설정되어 있어도 문제는 없으나 시스템이 자동으로 증가하기 때문에 이러한 시간도 늘어나게 된다. 연결 설정도 마찬가지이다. 제어판에 있는 SQL 서버 정보에서 동시 접속자의 기본 사용자 수인 5명을 적절하게 조절해 SQL의 접속 수를 늘려서 속도를 높여줄 필요가 있다.
● 글자가 많은 부분에 컬럼 속성을 char로 하지 마라
필자는 이 버그(?)를 찾는 데만 며칠이 걸렸다. 실제로 MSSQL에서 char(3000)이라고 설정해 고객의 응답 내용을 담았는데 PHP로 불러오면 256 글자밖에 보이지 않는다. char를 보이게 하려고 노력했으나 php.net에서도 찾지를 못했다.
어떻게든 이 문제를 해결하기 위해 text 필드를 사용했는데 이것 또한 256 밖에 보이지 않았다. 이것은 PHP가 버전업하면서 문제해결을 지원했다. 이를 해결하는 방법은 윈도우의 경우 php.ini에서 다음과 같은 부분을 찾아 필요한 크기만큼 늘려주면 된다.
필드를 사용하고자 한다면, 이런 사항을 유념해 설계하기 바란다. 소스에서 ;(주석)을 제거하는 것을 잊지 말자.
; Valid range 0 - 2147483647. Default = 4096.
;mssql.textlimit = 4096
; Valid range 0 - 2147483647. Default = 4096.
;mssql.textsize = 4096
대형 프로젝트에선 PHP와 오라클이 만난다
대형 프로젝트에서는 어김없이 오라클과 만난다. DBA가 아닌 개발자라면 크게 부담 갖지 말고 프로그래밍에 임하면 될 것이다. 오라클은 대형 데이터베이스이기 때문에 설계부터 구현까지 아주 복잡하고, 거대하게 만들어질 때가 많다.
이런 프로젝트는 PHP의 기법을 중심으로 개발에 임하기보다는 크고 복잡하게 구성되어 있는 데이터베이스의 관계와 업무 프로세스를 익히는 것이 더 중요하다. 실제 프로젝트에서는 관련 업무 프로세스를 먼저 알고 가는 것이 이후 수정 및 재개발 업무를 훨씬 줄일 수 있다.
오라클에서도 MySQL이나 MS QL처럼 쉽게 게시판 리스트를 만들 수 있다. 앞의 게시판 리스트와 똑같은 코드를 오라클 연동 버전으로 변환해 보자.
<?
$conn = OCILogon("scott","tiger");
$stmt = OCIParse($conn,"select * from web_board");
OCIExecute($stmt);
$nrows = OCIFetchStatement($stmt,$results);
if ( $nrows > 0 ) {
for ( $i = 0; $i < $nrows; $i++ ) {
reset($results);
while ( $column = each($results) ) {
$data = $column['value'];
$number = $column['number'];
$name = $column['name'];
$date = $column['write_date'];
$title = $column['title'];
$content = $column['content'];
$hit = $column['hit'];
}
}
} else {
echo "No data found";
}
OCIFreeStatement($stmt);
OCILogoff($conn);
?>
현재 쓰여 있는 소스 코드는 OCI 함수로 프로그래밍되어 있다. OCILogon() 함수는 첫 번째와 두 번째 인수로 DB 계정의 사용자 이름과 패스워드를 받고, 세 번째 인수로는 로컬 오라클 인스턴스(Local Oracle instane) 이름이나 접속 가능한 tnsnames.ora의 엔트리 이름을 명시한다. OCIParse() 함수로 SQL문의 구문의 유효 여부를 리턴한다. OCIExecute() 함수로 SQL문을 실행한다.
첫 번째 인수는 OCIParse의 리턴 값을 취하고, 두 번째 인수는 커밋 모드에 관한 것이다. 생략가능한 인자이다. 생략했을 때 두 번째 인수의 디폴트 값은 OCI_COMMIT_ON_SUCCESS로 SQL을 실행한 후 바로 커밋한다.
테이블에 변경 작업을 하는 SQL문 이후에 롤백하기 위해서는 두 번째 인수로 OCI_DEFAULT을 지정해야 한다.
물론 표준 오라클 함수로도 프로그래밍할 수 있다. PHP는 오라클 함수를 OCI 함수와 표준 ORA 함수를 지원한다. 다음은 앞의 코드를 ORA 함수로 프로그래밍한 코드이다.
<?
$conn = ora_Logon("TNS", "password");
$curs = ora_open($conn);
$query = sprintf("select * from web_board");
ora_parse($curs, $query);
ora_exec($curs);
ora_fetch($curs);
$ncols = ora_numcols($curs);
$nrows = ora_numrows($curs);
for ($j=0; $j<$nrows; $j++)
{
for ($i=0; $i<$ncols; $i++)
{
$col = ora_getcolumn($curs, $i);
}
ora_fetch($curs);
}
?>
ORA 함수로 코딩된 소스를 보면 ora_Logon()으로 로그온하고 데이터베이스를 ora_open()으로 열고, ora_parse()로 구문을 검사한 후 ora_exec(), ora_fetch()를 통해 쿼리를 실행한다. 그리고 나서 ora_numcols()으로 컬럼 개수를 가져온다, 또한 ora_numrows() 함수는 전체 행을 가져오는 함수이다. 이 함수의 결과 값을 이용해 loop를 돌려 해당 컬럼 값을 순차적으로 가져오는 것이다.
개발자는 OCI함수와 ORA 함수 중 자신이 선호하는 함수를 선택해 개발하면 된다. 다른 데이터베이스보다 선택 폭이 넓다는 장점이 있다. 또한 많은 레퍼런스가 제공되기 때문에 개발에 크게 어려움이 없을 것이다. 오라클 또한 국내보다는 국외 레퍼런스가 많기 때문에 외국 사이트를 잘 찾아내는 것도 중요할 것이다.
커밋과 롤백에 대한 이야기
데이터를 조회하거나 변경하는 DML(Data Manipulation Language)에는 SELET, INSERT, UPDATE, DELETE가 있는데, 이러한 구문은 롤백(rollback)이 가능하다. 이중 SELET를 제외한 구문은 모두 데이터베이스에 변경을 가하는 구문인데, 이러한 구문을 사용해 데이터베이스에 변경한 사항을 COMMIT 명령으로 완료하거나 ROLLBACK 명령으로 취소할 수 있다는 얘기이다.
반면, 데이터 구조를 정의하는 DDL(Data Definition Language)에는 CREATE, DROP문이 있다. 이러한 구문이 데이터베이스에 가한 변경 작업은 ROLLBACK 명령으로 취소할 수 없다. 즉 DLL 구문을 실행할 때에는 즉시 데이터베이스에 반영된다. ROLLBACK문은 CREATE TABLE을 통해 작성된 테이블을 제거하지 못하므로 생성된 테이블을 제거하기 위해서는 DROP TABLE문을 사용해야 한다.
오라클 활용하기
● 클라이언트 설정 오류를 조심하자
오라클 시스템을 운영할 때 기본적으로 웹 서버 + DB 서버로 분리를 한다. 그렇다면 웹 서버에서 오라클 데이터베이스에 접근해야 한다. 그렇게 하기 위해서는 오라클 클라이언트를 반드시 웹 서버에 설치해야 한다.
필자는 테스트를 위해 오라클 클라이언트를 윈도우 2000에 설치하고 PHP를 설치하고자 하였으나 오라클 연동되지 않아 낭패를 본 경우가 있었다. 결국 PHP 사이트의 버그 리포트를 뒤져보니 오라클 클라이언트 8.16과 PHP의 호환 버그가 문제였다.
시스템 연동을 생각하는 독자들은 반드시 오라클 클라이언트 8.17을 설치하기 바란다. 또한 NetEasyconfig를 한 후에는 리부팅해 호환에 만전을 기하기 바란다.
● rownum을 활용하자
MySQL의 limit와 같이 오라클에서도 rownum을 제공한다. 특정 부분만 쿼리를 실행하기 때문에 속도 보정을 할 수 있다.
rownum에 대한 자세한 설명은 생략하도록 하겠다. 오라클 관련 문서에 보면 자세하게 설명되어 있을 것이다.
● 서브 쿼리를 잘 활용하자
오라클은 서브 쿼리를 지원한다. 그리하여 SQL을 전문으로 하는 개발자나 DBA를 보면 엄청난 길이의 서브 쿼리를 사용하기도 한다. 복잡한 계산 과정은 서브 쿼리를 사용한 속도를 저하시키는 장문의 SQL문보다 단순한 SQL 여러 개를 실행시키는 것이 PHP에서 빠르지만 속도가 문제되지 않는 부분에서는 서브 쿼리를 잘 이용하는 방법도 개발에 효율적일 것이다.
● order by와 인덱스를 적절하게 사용하자
일반적으로 대용량의 데이터를 가진 테이블의 쿼리에서 order by를 권장하지 않는다. 정렬에 의해 속도가 아주 늦어지기 때문이다. 이러한 경우에 가장 기본적으로 생각해 볼 수 있는 방법이 인덱스를 설정하는 방법이다. 인덱스를 설정했다면, order by문을 사용하더라도 기본적인 속도를 보장해 줄 것이다. 인덱스는 데이터 용량이 커질 수 있는 테이블을 설계 단계에서 미리 설정해 놓는 것이 좋으며 재수정 가능하게 설계하도록 하자.
● sqlloader를 활용하자
대용량의 데이터를 삽입하거나 처리할 때 인덱스가 있는 테이블이라면 엄청난 로드가 걸린다. 이것은 테이블이 하나를 삽입할 때마다 인덱스를 재설정하기 때문이다. 이럴 때는 아예 인덱스를 드롭하고 삽입이 끝난 후 다시 인덱싱하는 방법이 훨씬 빠르다. 보통 이런 대용량 처리는 매 경우 삽입하는 것보다 파일 데이터를 sqlloader를 이용해 사용하는 방법이 훨씬 도움이 될 것이다.
사이베이스와 PHP 연동하기
사이베이스는 유명한 데이터베이스임에도 불구하고, PHP에서 잘 알려진 정보가 없는 것으로 알고 있다. 필요한 독자가 있을 것이라는 판단에 간략하나마 사이베이스와의 연동을 다루도록 하겠다. 사이베이스 또한 PHP에서 지원하는 함수를 사용하면 다른 데이터베이스와 마찬가지로 쉽게 데이터를 핸들링하고 리스트를 출력할 수 있다.
다음은 앞의 데이터베이스 코드와 동일한 기능을 하는 코드이다. sysbase_connect() 함수로 데이터베이스에 접속하고, sybase_query() 함수로 SQL문을 실행하고, sysbase_fetch_array() 함수로 순차적으로 게시물 데이터를 가져온다.
<?
$cnn = sybase_connect("db_server","sa","password");
$result = sybase_query("select * from web_board");
$i = 0;
while ($r = sybase_fetch_array($result)) {
$i++;
$data = $column['value'];
$number = $column['number'];
$name = $column['name'];
$date = $column['write_date'];
$title = $column['title'];
$content = $column['content'];
$hit = $column['hit'];
}
?>
조금 눈여겨 본 독자라면 거의 모든 데이터베이스를 핸들링하는 구조가 비슷하다는 것을 느꼈을 것이다. 개발자는 자신이 사용하는 데이터베이스 관련 함수만 잘 습득해도 다른 데이터베이스에 쉽게 적응할 수 있다. 왜냐하면 대부분의 함수 이름과 기능이 비슷하기 때문이다.
사이베이스에서 한 가지 유념할 것은 속도 보장을 위해 sybase_result() 함수를 가급적 사용하지 말기 바란다. 이것은 전체 행을 가져오는 함수이기 때문에 속도가 많이 느릴 수 있다. 이런 이유로 sybase_result() 함수보다는 순차적으로 가져오는 sybase_fetch_object(), sybase_fetch_array(), sybase_fetch_row() 함수들을 권고하고 있다. 실제 커뮤니티를 방문하면 사이베이스에 대한 정보가 많이 적다. 관련 개발자들은 www.php.net의 메일링이나 아카이브를 검색하는 것이 많은 도움이 될 것이다.
속도 개선을 설계로 해결할 수 있을까?
데이터베이스 프로그래밍을 하면서 많은 개발자들의 관심사는 빠른 데이터 로딩이다. 현재 운영되는 사이트를 보면 초기개발 단계에서 설계 오류로 데이터가 10만건을 넘어가기 시작하면 데이터 핸들링에서 엄청난 속도 부하가 나타난다.
대부분 사이트는 초기 개발 비용보다 많은 재개발 및 튜닝 작업 비용을 들여 사이트 개편에 나서는 경우가 허다하다. 가장 일반적인 웹 게시판의 경우도 마찬가지다(필자가 말했듯이 웹 게시판의 로직은 거의 모든 데이터베이스 연동 프로그래밍의 기본이다. 전부라고 말해도 과언이 아님을 유념하길 바란다).
웹 게시판을 어떻게 설계하는 것이 좋을까? 이 부분으로 웹 게시판 설계를 고쳐보도록 하자. 앞 부분에서 다뤘듯이 데이터베이스에서 주 키나 인덱스로 속도와 조회 문제를 다소 해결할 수 있다. 하지만 이것으로는 방대하게 늘어나는 데이터를 빠르게 로딩하기는 힘들다. 현재 게시판의 간단한 프로그래밍 구조를 살펴보자. 게시판은 최근의 10개의 데이터를 먼저 보여주고 페이징을 시도해 페이지를 나눈다. 프로그래밍 구조는 다음과 같다.
① 전체의 게시물 개수를 최근 데이터로 정렬해 구한다.
② 최근 데이터를 기준으로 10개씩 페이징을 시도한다.
③ 최근 10개를 for 루프로 돌려 값을 출력한다.
만일 데이터가 몇 만개를 넘어섰을 때 전체 개수를 가져오는 곳부터 데이터의 로딩이 걸린다. 페이징을 시도하더라도 전체 개수를 가져오는 루틴은 늘 걸리기 때문에 데이터의 로딩 시간은 해결되지 않는다.
가장 쉽게 이 문제를 해결하는 방법은 무엇일까? 제일 간단하게 접근한다면 전체 행을 가지고 있는 테이블을 새로 하나 생성해 사용하는 방법이다(물론 여기서 계층형 게시판이나 아주 정밀한 게시판의 구조에 대해 설명하는 것이 아님을 밝힌다). 전체 카운트를 가지고 있는 테이블이 있는 프로그램의 구조를 살펴보자.
① 전체의 게시물 개수를 전체 카운트 테이블에서 조회한다.
② 최근 데이터를 기준으로 10개씩 페이징을 시도한다.
③ 해당 10개를 for 루프로 돌려서 값을 출력한다.
얼핏 보면 비슷할 것 같지만, 페이징을 시도하기 위해 전체 개수를 가져오는 부분에서 속도가 개선될 것이다. 다음 페이지를 넘겨도 전체 카운트 테이블에서 개수를 조회하기 때문에 빠르게 가져올 수 있다. 물론 이 구조는 완벽하지 않다.
답변 글에 대한 계층 설계도 없고, DELETE시 카운트가 중간에 빠졌을 때 전체 카운트는 어떻게 처리할 것인지, 페이징은 어떻게 할 것인지 등을 고려해야 한다. 여기서 필자가 말하고 싶은 것은 확장성을 고려해 기초 설계를 하자는 것이다. 작은 아이디어가 속도 개선에 엄청난 도움을 줄 것이다.
실제로 대량 데이터에 대한 게시판 처리 기법이나 페이징 기법은 많은 개발자의 관심사였으며, 커뮤니티의 기술 토론을 이끌어 가는 주축이기도 했다. 이런 정보는 커뮤니티에 많이 제시되어 있으며 그것을 참고하면 될 것이다. 다만 이 글에서는 이런 방법을 시도하는 개발자가 속도 개선과 개발 업무 개선을 이끌어 나갈 수 있다는 것을 말해주고 싶어서다.
자신감과 경험이 곧 지식
이번 호에서는 각종 데이터베이스에 대한 간단한 프로그래밍 소개와 이용시 도움이 되는 방법들을 다루었다. 데이터베이스는 앞에서도 밝혔듯이 웹 프로그래밍의 가장 근간이라고 할 수 있다. 웹 프로그래머에게 요구되는 다양한 데이터베이스 처리에 대한 방법, 이러한 데이터베이스를 빠르고 안전하게 구현하는 방법을 다양한 경험으로 해결하기 바란다.
불과 몇 년 전만 해도 데이터베이스 없이 운영되는 사이트도 많았는데, 현재 그런 사이트는 하나도 찾을 수 없을 정도로 우리 곁에 데이터베이스가 있다. 이러한 데이터베이스에 대한 스킬은 다양한 경험과 학습에서 나오는 것임을 다시 한번 당부하고 싶다.
또한 개발자로서 자신 있는 데이터베이스를 반드시 하나쯤 갖추라고 말하고 싶다. 하나의 데이터베이스에 정통하게 되면, 다른 데이터베이스를 접할 때는 비교할 줄 알고, 테스트할 줄 알고, 응용할 줄 아는 새로운 스킬이 개발자에게 부여 될 것이다.
