SQL 테스트와 쿼리 최적화

SQL 테스트

SQL 테스트는 SQL이 작성 의도에 맞게 원하는 기능을 수행하는지 검증하는 과정이다.

• 단문 SQL은 SQL 코드를 직접 실행한 후 결과를 확인하는 것으로 간단히 테스트가 가능하다.
• 절차형 SQL은 테스트 전에 생성을 통해 구문 오류(Syntax Error)나 참조 오류 여부를 확인한다.
• 정상적으로 생성된 절차형 SQL은 디버깅을 통해 로직을 검증하고 결과를 통해 최종적으로 확인한다.

단문 SQL 테스트

단문 SQL 테스트는 DDL, DML, DCL이 포함되어 있는 SQL과 TCL(트랜잭션을 제어하는 COMMIT, ROLLBACK, SAVEPOINT 명령)을 테스트하는 것으로 직접 실행하여 결과물을 확인한다.

• DESCRIBE 또는 DESC [개체명] 명령어를 이용하면 DDL로 작성된 테이블이나 뷰의 속성, 자료형, 옵션들을 바로 확인할 수 있다.
• DML로 변경한 데이터는 SELECT문으로 데이터의 정상적인 변경 여부를 확인할 수 있다.
• DCL로 설정된 사용자 권한은 사용자 권한 정보가 저장된 테이블을 SELECT로 조회하거나 SHOW 명령어로 확인할 수 있다.
  • Oracle : SELECT * FROM DBA_ROLE_PRIVES WHERE GRANTEE = 사용자;
  • MySQL : SHOQ GRANTS FOR 사용자@호스트;

절차형 SQL 테스트

프로시저, 사용자 정의 함수, 트리거 등의 절차형 SQL은 디버깅을 통해 기능의 적합성 여부를 검증하고 실행을 통해 결과를 확인하는 테스트를 수행한다.

• 많은 코드로 구성된 절차형 SQL의 특성상 오류 및 경고 메시지가 상세히 출력되지 않으므로 SHOW 명령어를 통해 오류 내용을 확인하고 문제를 수행한다.
  • 형식 : SHOW Errors;
• 데이터베이스에 변화를 줄 수 있는 SQL문은 주석으로 처리하고 출력문을 이용하여 화면에 출력하여 확인한다. 
  • Oracle 출력 형식
    • DBMS_OUTPUT.ENABLE; : 화면에 출력하기 위해 DBMS_OUTPUT 패키지를 불러온다.
    • DBMS_OUTPUT_LINE(데이터); : '데이터'에 넣은 변수나 값을 화면에 출력한다.
  • MySQL 출력 형식
    • SELECT 데이터; : '데이터'에 넣은 변수나 값을 화면에 출력한다.

쿼리 성능 최적화

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

• 쿼리 성능을 최적화학 전에 성능 측정 도구인 APM을 사용하여 최적화 할 쿼리를 선정해야 한다.
  • APM은 애플리케이션의 성능 관리를 위해 접속자, 자원 현황, 트랜잭션 수행 내역, 장애 진단 등 다양한 모니터링 기능을 제공하는 도구로 리소스 방식과 엔드투엔드(End-to-END) 방식이 있다.
• 최적화 할 쿼리에 대해 옵티마이저가 수립한 실행 계획을 검토하고 SQL 코드와 인덱스를 재구성한다.
  • 옵티마이저는 작성된 SQL이 가장 효율적으로 수행되도록 최적의 경로를 찾아 주는 모듈로 RBO와 CBO 두 종류가 있으며 실무에서는 주로 CBO가 사용된다.
  • CBO 옵티마이저는 입출력 속도, CPU 사용량, 쿼리의 블록 개수, 쿼리에 사용되는 개체의 속성, 튜플의 개수 등을 종합하여 각 DBMS마다 고유의 알고리즘에 따라 산출되는 비용을 계산하므로 개체나 DBMS의 버전이 변경되어 알고리즘에 변화가 생기면 실행 계획을 다시 확인해야 한다.

	RBO	CBO
최적화 기준	규칙에 정의된 우선순위	액세스 비용
성능 기준	개발자의 SQL 숙련도	옵티마이저의 예측 성능
특징	실행 계획 예측이 쉬움	성능 통계치 정보 활용, 예측이 복잡함
고려사항	개발자의 규칙 이해도, 규칙의 효율성	비용 산출 공식의 정확성