[2020 정보처리기사 필기] SQL 활용 (2)

111. DBMS 접속 기술 ★★

1. DBMS 접속의 개요

- DBMS 접속은 사용자가 데이터를 사용하기 위해 응용 시스템을 이용하여 DBMS에 접근하는 것을 의미한다.

- 응용 시스템은 사용자로부터 매개 변수를 전달 받아 SQL을 실행하고 DBMS로부터 젇날받은 결과를 사용자에게 전달하는 매개체 역할을 수행한다.

- 인터넷을 통해 구동되는 웹 응용 프로그램은 웹 응용 시스템을 통해 DBMS에 접근한다.

- 웹 응용 시스템은 웹 서버와 웹 애플리케이션 서버로 구성되며 통합하여 하나의 서버만으로도 운용할 수 있다.

 

2. DBMS 접속 기술

- DBMS 접속 기술은 DBMS에 접근하기 위해 사용하는 API 또는 API의 사용을 편리하게 도와주는 프레임워크 등을 의미한다.

- JDBC(Java Database Connectivity)

     - JDBC는 Java 언어로 다양한 종류의 데이터베이스에 접속하고 SQL문을 수행할 때 사용되는 표준 API이다.

     - JDBC는 Java SE(Standart Edition)에 포함되어 있으며, JDBC 클래스는 java.sql, javax.sql에 포함되어 있다.

     - 접속하려는 DBMS에 대한 드라이버가 필요하다.

- ODBC(Open Database Connectivity)

     - 데이터베이스에 접근하기 위한 표준 개방형 API로, 개발 언어에 관계없이 사용할 수 있다.

     - 프로그램 내 ODBC문장을 사용하여, MS-Access, DBase 등 다양한 데이터베이스에 접근 가능

     - 드라이버가 필요하지만, 접속하려는 DBMS의 종류를 몰라도 된다.

- MyBatis

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

     - JDBC로 데이터베이스에 접속하려면 다양한 메소드를 호출하고 해제해야 하는데, MyBatis는 이를 간소화 했고 접속 기능을 더욱 강화시켰다.

     - SQL문장을 분리하여 XML파일을 만들고, Mapping을 통해 SQL을 실행한다.

     - SQL을 거의 그대로 사용할 수 있어 SQL 친화적인 국내 환경에 적합하여 많이 사용된다.

 

3. 동적 SQL(Dynamic SQL)

- 동적 SQL은 개발 언어에 삽입되는 SQL코드를 문자열 변수에 넣어 처리하는 것으로, 조건에 따라 SQL 구문을 동적으로 변경하여 처리할 수 있다.

- 동적 SQL은 사용자로부터 SQL문의 일부 또는 전부를 입력받아 실행할 수 있다.

- 값이 입력되지 않을 경우 사용하는 NVL함수를 사용할 필요가 없다.

- 프리컴파일할 때 구문 분석, 접근 권한 확인 등을 할 수 없다.

- 정적 SQL에 비해 속도가 느리지만, 상황에 따라 다양한 조건을 첨가하는 등 유연한 개발이 가능

 

핵심 

- 웹 응용 시스템 = 웹서버 + WAS

- JDBC : 자바 언어, 드라이버가 필요

- 동적 SQL은 SQL문을 자유롭게 변경할 수 있어 NULL을 처리하는 함수를 사용할 필요가 없음.

---

112. SQL 테스트 ★★

1. SQL 테스트의 개요

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

- 단문 SQL은 SQL코드를 직접 실행한 후 결과를 확인하는 것으로 간단히 테스트 가능

- 절차형 SQL은 테스트 전에 생성을 통해 구문 오류(Syntax Error)나 참조 오류의 존재 여부를 확인한다.

- 정상적으로 생성된 절차형 SQL은 디버깅을 통해 로직을 검증하고, 결과를 통해 최종적으로 확인

 

2. 단문 SQL 테스트

- 단문 SQL 테스트는 DDL, DML, DCL이 포함되어 있는 SQL과 TCL(Commit, Rollback, Savepoint)을 테스트하는 것으로, 직접 실행하여 결과물을 확인한다.

- Describe 명령어를 이용하면 DDL로 작성된 테이블이나 뷰의 속성, 자료형, 옵션들을 바로 확인할 수 있다. (DESC [개체명])

- DML로 변경한 데이터는 SELECT문으로 데이터의 정상적인 변경 여부를 확인 가능

- DCL로 설정된 사용자 권한은 SELECT로 조회하거나, SHOW 명령어로 확인 가능

 

3. 절차형 SQL 테스트

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

- SHOW 명령어를 통해 오류 내용을 확인하고 문제를 수정한다. (SHOW ERRORS)

- 데이터베이스에 변화를 줄 수 있는 SQL문을 주석처리하고 출력문을 이용하여 화면에 출력하여 확인한다.

 

핵심

- DESC : 뷰, 자료형, 옵션들을 바로 확인 가능

- DML로 변경한 것은 SELECT문으로, DCL은 SELECT 또는 SHOW

- 오류 확인 SQL : SHOW ERRORS

---

113. ORM(Object-Relational Mapping) ★★

1. ORM(Object-Relational Mappin)의 개요

- ORM은 객체지향 프로그래밍의 객체(Object)와 관계형 데이터베이스(RDB)의 데이터를 연결하는 기술을 의미한다.

- ORM은 객체지향 프로그래밍에서 사용할 수 있는 가상의 객체지향 데이터베이스를 만들어 프로그래밍 코드와 데이터를 연결한다.

- ORM으로 생성된 가상의 객체지향 데이터베이스는 프로그래밍 코드 또는 데이터베이스와 독립적이므로 재사용 및 유지보수가 용이하다.

- SQL 코드를 직접 입력하지 않고 선언문이나 할당 같은 부수적인 코드가 생략되기 때문에 간단하게 데이터를 조작할 수 있다.

 

2. ORM 프레임워크

- JAVA : JPA, Hibernate

- C++ : ODB, QxOrm

- Python : Django, SQLAlchemy

- iOS : DatabaseObjects, Core Data

- .NET : NHibernate, DatabaseObjects

- PHP : Doctrine, Propel, RedBean

 

3. ORM의 한계

- ORM은 프레임워크가 자동으로 SQL을 작성하기 때문에 의도대로 SQL이 작성되었는지 확인할 필요가 있다.

- 객체지향적인 사용을 고려하고 설계된 데이터베이스가 아닌 경우 프로젝트가 크고 복잡해질수록 ORM 기술을 적용하기 어려워진다.

- 기존의 기업들은 ORM을 고려하지 않은 데이터베이스를 사용하고 있기 때문에 ORM에 적합하게 변환하려면 많은 시간과 노력이 필요하다.

 

핵심

- ORM : 객체와 관계형 데이터베이스의 데이터를 연결하는 기술, 재사용 유지보수 용이

- ORM 프레임워크 : JPA, ODB, Django, NHibernate 등

---

114. 쿼리 성능 최적화 ★

1. 쿼리 성능 최적화의 개요

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

- 쿼리 성능을 최적화하기 전에 성능 측정 도구인 APM을 사용하여 최적화 할 쿼리를 선정해야 한다.

- 최적화 할 쿼리에 대해 옵티마이저가 수립한 실행 계획을 검토하고 SQL 코드와 인덱스를 재구성한다.

- RBO(규칙 기반 옵티마이저) : 규칙에 정의된 우선순의 , 개발자의 SQL 숙련도에 따른 성능, 실행 계획 예측이 쉬움, 개발자의 규칙 이해도

- CBO(비용 기반 옵티마이저) : 액세스 비용, 옵티마이저의 예측 성능, 성능 통계치 정보 활용, 예측이 복잡, 비용 산출 공식의 정확성을 고려

 

2. 실행 계획(Execution Plan)

- 실행 계획은 DBMS의 옵티마이저가 수립한 SQL 코드의 실행 절차와 방법을 의미한다.

- 실행 계획은 EXPLAIN 명령어를 통해 확인할 수 있으며, 그래픽이나 텍스트로 표현된다.

- 실행 계획에는 요구사항들을 처리하기 위한 연산 순서가 적혀있으며, 연산에는 조인, 테이블 검색, 필터, 정렬 등이 있다.

 

3. 쿼리 성능 최적화

- 쿼리 성능 최적화는 실행 계획에 표시된 연산 순서, 조인 방식, 테이블 조회 방법 등을 참고하여 SQL문이 더 빠르고 효율적으로 작동하도록 SQL 코드와 인덱스를 재구성하는 것을 의미한다.

- SQL 코드 재구성

     - WHERE 절을 추가하여 일부 레코드만 조회하게 함으로써 비용을 줄인다.

     - WHERE 절에 연산자가 포함되면 INDEX를 활용하지 못하므로 가능한 한 연산자 사용을 자제한다.

     - 서브 쿼리에 특정 데이터가 존재하는지 확인할 때는 IN보다 EXISTS를 활용한다.

     - 옵티마이저의 실행 계획이 잘못되었다고 판단되는 경우 힌트를 활용하여 실행 계획의 액세스 경로 및 조인 순서를 변경한다.

- 인덱스 재구성

     - SQL 코드에서 조회되는 속성과 조건들을 고려하여 인덱스를 구성한다.

     - 실행 계획에 따라 인덱스를 추가하거나 열 순서를 변경한다.

     - 인덱스의 추가 및 변경은 해당 테이블을 참조하는 다른 SQL문에도 영향을 줄 수 있으므로 신중하게 결정한다.

     - 단일 인덱스로 쓰기나 수정 없이 읽기로만 사용되는 테이블의 경우 IOT(Index Organized Table)로 구성하는 것을 고려한다.

     - 불필요한 인덱스를 제거한다.

 

핵심

- APM을 통해 최적화할 쿼리를 특정

- 선정된 쿼리에서 옵티마이저가 수립한 실행 계획을 검토

- RBO, CBO

- 서브 쿼리에 특정 데이터 존재하는지는 IN보다 EXISTS를 활용