3. 데이터베이스 구축 - SQL 활용(DBMS 접속 기술 ~ 쿼리 성능 최적화)

116. DBMS 접속

: 사용자가 데이터를 사용하기 위해 응용 시스템을 이용해 DBMS에 접근하는 것

• 응용 시스템 : 사용자로부터 매개 변수를 전달받아 SQL을 실행, DBMS에게 받은 결과를 사용자에게 전달
  • ex) 웹 응용 프로그램 : 웹 응용 시스템을 통해 DBMS에 접근

※ 웹 응용 시스템

: 웹 서버와 웹 애플리케이션 서비스 (WAS)로 구성된 시스템

• 제공하는 서비스 규모가 작은 경우 웹 서버와 WAS를 통합하여 하나의 서버로 운용하기도 함

웹 응용 시스템을 이용한 DBMS 접근의 구조

1. 사용자는 웹 서버에 접속해 상호 작용
2. 웹 서버는 많은 수의 서비스 요청 처리를 위해 대용량의 데이터 요청을 WAS에 전달
3. WAS는 수신한 요청을 트랜잭션 언어로 변환하여 DBMS에 전달
4. DBMS는 WAS에 요청 받은 데이터를 전달
5. WAS에 보내진 데이터는 웹 서버를 통해 사용자에게 전달

 

1) DBMS 접속 기술

: DBMS에 접근하기 위해 사용하는 API 또는 API의 편리한 사용을 지원하는 프레임워크

 

- JDBC (Java Database Connectivity)

: Java 언어를 이용해 다양한 종류의 데이터베이스 접속, SQL문 수행 시 사용 

• 1997년 2월, 썬 마이크로 시스템에서 출시
• Java SE에 포함되어 있으며, JDBC 클래스는 java.sql, javaxsql에 포함
• 접속하려는 DBMS에 대한 드라이버 필요

 

- ODBC (Open Database Connectivity)

: 데이터베이스 접근을 위한 표준 개방형 API, 개발언어에 관계 없이 사용 가능

• 1992년 9월, 마이크로소프트에서 출시
• 프로그램 내에서 ODBC 문장을 사용하여 MS-Access, DBase, DB2, Excel, Text 등 다양한 데이터베이스에 접근
• 접속하려는 DBMS에 맞는 드라이버 필요
  • ODBC 내 드라이버 관리자가 해당 DBMS에 맞게 연결해주므로 DBMS의 종류를 알 필요가 없음

 

- MyBatis

: JDBC 코드를 단순화하여 사용할 수 있는 SQL Mapping 기반 오픈 소스 접속 프레임워크

• 다양한 메소드를 호출하고 해제하는 JDBC의 코드를 간소화하고 접속 기능은 강화
• SQL 문장을 분리해 XML 파일을 만들고, Mapping을 통해 SQL 문장 실행
• SQL을 거의 그대로 사용하는 것이 가능 -> SQL 친화적인 국내 환경에 적합

 

2) 동적 SQL (Dynamic SQL)

: 개발 언어에 삽입되는 SQL 코드를 문자열 변수에 넣어 처리하는 것, 조건에 따라 SQL 구문을 동적으로 변경 가능

• 사용자로부터 SQL문의 일부 또는 전체를 입력받아 실행
• 값이 입력되지 않아도 NVL 함수 필요 X
• 프리 컴파일 시에 구문 분석, 접근 권환 확인 등이 불가능
• 정적 SQL에 비해 속도는 느리지만 유연한 개발이 가능

※  NVL 함수 

: NVL(A,B)의 형식으로 사용하여 A가 NULL 값인 경우 B를, 그렇지 않으면 A를 반환하는 함수

 

※ 정적 SQL과 동적 SQL의 비교

---

117. SQL 테스트

: 작성한 SQL이 의도에 맞게 원하는 기능을 수행하는지 검증하는 과정

• 단문 SQL : 코드를 직접 실행하여 결과를 확인하는 것으로 테스트
• 절차형 SQL
  1. 테스트 전에 생성을 통해 구문 오류(syntax errro)나 참조 오류 확인
  2. 정상적으로 생성된 SQL 문장을 디버깅하여 로직 검증, 결과를 통해 최종 확인

 

1) 단문 SQL 테스트

: DDL, DML, DCL이 포함되어 있는 SQL와 TCL을 직접 실행하여 테스트하는 것

• 실행 시 오류나 경고가 발생하는 경우 오류 메시지/경고 메시지를 참조해 문제 해결
• DDL : DESCRIBE 명령어를 통해 만들어진 테이블이나 뷰의 속성, 데이터 타입, 옵션 등을 확인
  • DESCRIBE(또는 DESC) 개체_이름;
• DML : 문장 실행 후 SELECT 문장을 통해 데이터의 정상 변경 여부를 확인
• DCL : 문장 실행 후 사용자 권한 정보가 저장된 테이블을 SELECT로 확인하거나 SHOW 명령어 이용
  • Oracle : SELECT * FROM DBA_ROLE_PRIVES WHERE GRANTEE = 사용자;
  • MySQL : SHOW GRANTS FOR 사용자@호스트;

 

2) 절차형 SQL 테스트

: 디버깅을 통해 기능의 적합성을 검증하고 실행을 통해 최종 결과를 확인

• SHOW ERRORS : 실행 시 발견한 오류 내용의 상세를 확인하고 문제를 수정
• 데이터베이스에 변화를 줄 수 있는 문장은 주석으로 처리하고 대신 출력문을 이용해 결과를 확인
  • Oracle
    • DBMS_OUTPUT.ENABLE : 출력을 위해 DBMS_OUTPUT 패키지를 불러옴
    • DBMS_OUTPUT.PUT_LINE(데이터) : '데이터'에 넣은 변수나 값을 화면에 출력
  • MySQL : SELECT 문장을 통해 데이터 확인
• 디버깅이 완료되면 출력문을 삭제하고, 주석처리한 문장을 주석 해제하여 실행 후 결과 검토

 

※ SQL 문장의 주석 처리

• # : MySQL, 한 줄 주석 처리에 이용
• -- : Oracle, 한 줄 주석 처리에 이용
• /* */ : '/*'와 '*/' 사이의 문장을 모두 주석으로 처리

---

118. ORM (Object-Relational Mapping)

: 객체지향 프로그래밍의 객체(Object)와 관계형 데이터베이스(Relational Database)의 데이터를 연결하는 기술

• 객체지향 프로그래밍이 사용할 수 있는 가상의 객체지향 데이터베이스를 만들어 프로그래밍 코드와 데이터를 연결
• 재사용 및 유지보수 용이 : 생성된 객체지향 데이터베이스는 프로그래밍 코드와 데이터베이스로부터 독립성을 유지함
• 직관적이고 간단하게 데이터 조작이 가능 : SQL 코드를 직접 입력하지 않고, 선언문이나 할당 등의 부수적인 코드도 생략함

 

- ORM 프레임 워크

: ORM을 구현하기 위한 구조와 구현에 필요한 여러 기능을 제공하는 소프트웨어

• 개발 언어별 프레임 워크
  • Java : JPA, Hibernate, EclipseLink, DataNucleus, Ebean, etc.
  • C++ : ODB, QxOrm, etc.
  • Python : Django, SQLAlchemy, Storm, etc.
  • iOS : DatabaseObjects, Core Data, etc.
  • .NET : NHibernate, DatabaseObjects, Dapper, etc.
  • PHP : Doctrine, Propel, RedBean, etc.

 

- ORM의 한계

• 프레임워크가 자동으로 SQL을 작성하므로 의도대로 SQL이 작성되었는지 확인해야 함
• 객체지향을 고려하여 설계된 데이터베이스가 아닌 경우 ORM 기술 적용이 어려움
• ORM을 고려하지 않는 기존 시스템의 변환이 어려움

---

119. 쿼리 성능 최적화

: 데이터 입출력 애플리케이션의 성능 향상을 위해 SQL 코드를 최적화하는 것

 

1) 옵티마이저

: 작성된 SQL이 가장 효율적으로 수행되도록 최적의 경로를 찾아주는 모듈

• 규칙 기반 옵티마이저 (RBO; Rule Based Optimizer) : 규칙에 정의된 우선순위를 기준으로 하여 최적화를 수행
  • 성능 기준 : 개발자의 SQL 숙련도
  • 실행 계획 예측이 쉬움
  • 고려 사항 : 개발자의 규칙 이해도, 규칙의 호율성
• 비용 기반 옵티마이저 (CBO; Cost Based Optimizer) : 접근 비용을 기준으로 하여 최적화를 수행
  • 성능 기준 : 옵티마이저의 예측 성능
  • 성능 통계치 정보를 활용해 예측
  • 개체나 DBMS의 버전이 변경되어 알고리즘에 변화가 생기면 실행 계획을 다시 확인해야 함
  • 고려 사항 : 비용 산출 공식의 정확성

 

2) 쿼리 성능 최적화 과정

1. 성능 측정 도구인 APM을 사용해 최적화할 쿼리를 선정
2. 최적화 대상 쿼리에 대해 옵티마이저가 수립한 실행 계획을 검토하고 SQL 코드와 인덱스를 재구성

 

1. 실행 계획

: DBMS 옵티마이저가 수립한 SQL 코드의 실행 절차와 방법

Oracle의 EXPLAIN을 통해 실행 계획 예시

• EXPLAIN : 옵티마이저의 실행 계획을 그래픽이나 텍스트로 표시하는 명령어
• 실행 계획에는 요구 사항들을 처리하기 위한 연산 순서와 처리 비용이 명시됨
  • 설명에 포함되는 연산에는 조인, 테이블 검색, 필터, 정렬 등이 존재

 

2. 쿼리 성능 최적화

: 실행 계획에 표시된 연산 순서, 조인 방식, 테이블 조회 방법 등을 참고해 SQL 코드와 인덱스를 재구성하는 것

 

- SQL 코드 재구성

• WHERE 절을 추가하여 조회에 들어가는 비용을 줄임
• WHERE 절에서 인덱스 사용이 가능하도록 최대한 연산자 사용을 자제
• 특정 데이터의 존재 유무 확인에는 IN보다 EXISTS를 활용
  • EXISTS : IN과 달리 데이터의 존재여부가 확인되면 검색이 종료됨
• 옵티마이저의 실행 계획이 잘못되었다고 판단되는 경우, 힌트를 활용해 실행 계획의 접근 경로, 조인 순서를 변경
  • 힌트 : SQL문에 추가되어 테이블의 접근 순서를 변경하거나, 인덱스 사용을 강제하는 등의 문장

 

- 인덱스 재구성

• SQL 코드에서 조회되는 속성과 조건들을 고려하여 인덱스를 구성
• 불필요한 인덱스 제거
• 실행 계획을 참고해 인덱스를 추가하거나 기존 인덱스의 열 순서 변경
• 인덱스의 추가 및 변경은 해당 테이블을 참조하는 다른 SQL 문장에 영향을 줄 수 있음을 고려
• 단일 인덱스를 이용해 읽기로만 사용되는 테이블을 IOT (Index-Organized Table)로 구성하는 것을 고려
  • Index-Organized Table : 인덱스 안에 해당 레코드의 주소가 아닌 테이블 데이터를 직접 저장햐여 빠른 조회가 가능