2020 정보처리기사 필기 - 3.4 SQL 활용(2)

DBMS 접속 기술

DBMS 접속 기술의 개요

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

- 응용 시스템은 사용자로부터 매개 변수를 전달받아 SQL을 실행하고 DBMS로부터 전달받은 결과를 사용자에게 전달

- 웹 응용프로그램은 웹 응용 시스템을 통해 DBMS에 접근

- 웹 응용 시스템은 웹 서버와 웹 애플리케이션 서버(WAS)로 구성

    -> 사용자 ↔ 웹 서버 ↔ WAS ↔ DBMS

 

 

DBMS 접속 기술

- DBMS에 접근하기 위해 사용하는 API 또는 프레임워크를 의미

    -> API(Application Programming Interface) : 응용 프로그램 개발 시 운영 체제나 DBMS 등을 이용할 수 있도록 규칙 등에 대해 정의해 놓은 인터페이스

    -> 프레임워크 : 소프트웨어에서는 특정 기능을 수행하기 위해 필요한 클래스나 인터페이스 등을 모아둔 집합체

- JDBC(Java DataBase Connectivity)

    -> Java 언어

    -> 썬 마이크로시스템에서 출시

    -> Java SE에 포함되어 있고 JDBC 클래스는 java.sql, javax.sql에 포함

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

- ODBC(Open DataBase Connectivity)

    -> 개발 언어와 상관 없음

    -> 마이크로소프트에서 출시

    -> MS-Access, DBase, DB2, Excel, Text 등 다양한 데이터베이스에 접근 가능

- MyBatis

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

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

    -> SQL을 거의 그대로 사용할 수 있어 국내 환경에 적합

 

동적 SQL

- 개발 언어에 삽입되는 SQL 코드를 문자열 변수에 넣어 처리하는 것

- 조건에 따라 SQL 구문을 동적으로 변경하여 처리 가능

- NVL 함수를 사용할 필요가 없음

- 응용 프로그램 수행 시 SQL이 변형될 수 있어 프리컴파일할 때 구문 분석, 접근 권한 확인 등을 할 수 없음

 

SQL 테스트

SQL 테스트의 개요

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

- 단문 SQL은 코드를 직접 실행한 후 결과를 확인

- 절차형 SQL은 테스트 전에 생성을 통해 구문 오류나 참조 오류의 존재 여부 확인

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

 

단문 SQL 테스트

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

- DESCRIBE 명령어를 이용하면 DDL로 작성된 테이블이나 뷰의 속성, 자료형, 옵션들을 확인할 수 있음

- DCL로 설정된 사용자 권한은 사용자 권한 정보가 저장된 테이블을 SELECT문으로 조회하거나 SHOW 명령어로 확인할 수 있음

    -> Oracle : SELECT * FROM DBA_ROLE_PRIVES WHERE GRANTEE = 사용자;

    -> MySQL : SHOW GRANTS FOR 사용자@호스트;

 

절차형 SQL 테스트

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

- SHOW 명령어를 통해 오류 내용을 확인

    -> SHOW ERRORS;

- 데이터베이스에 변화를 줄 수 있는 SQL문은 주석 처리 후 출력문을 이용하여 결과 확인

    -> Oracle : DBMS_OUTPUT.ENABLE; / DBMS_OUTPUT.PUT_LINE(데이터);

    -> MySQL : SELECT 데이터;

 

ORM

ORM(Object-Relational Mapping)의 개요

- 객체지향 프로그래밍의 객체와 관계형 데이터베이스의 데이터를 연결하는 기술

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

- 프로그래밍 코드 또는 데이터베이스와 독립적이므로 재사용 및 유지보수가 용이

- SQL 코드를 직접 사용하지 않기 때문에 직관적이고 간단하게 데이터 조작 가능

 

ORM 프레임워크

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

- JAVA : JPA, Hibernate, EclipseLink, DataNucleus, Ebean 등

- C++ : ODB, QxOrm 등

- Python : Django, SQLAlchemy, Storm 등

- iOS : DatabaseObjects, Core Data 등

- .NET : NHibernate, DatabaseObjects, Dapper 등

- PHP : Doctrine, Propel, RedBean 등

 

ORM의 한계

- 프레임워크가 자동으로 작성하기 때문에 의도대로 작성되었는지 확인할 필요가 있음

- 객체지향적 사용을 고려, 설계한 데이터베이스가 아닌 경우 프로젝트가 크고 복잡할수록 ORM 기술을 적용하기 어려움

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

 

쿼리 성능 최적화

쿼리 성능 치적화의 개요

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

- 최적화 전 APM을 사용하여 최적화할 쿼리 선정

    -> APM(Application Performance Management/Monitoring) : 애플리케이션의 성능 관리를 위해 다양한 모니터링 기능을 제공하는 도구

- RBO(Rule Based Optimizer) : 규칙 기반 옵티마이저

- CBO(Cost Based Optimizer) : 비용 기반 옵티마이저

RBO, CBO 비교

실행 계획

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

- EXPLAIN 명령어를 통해 확인

- 그래픽이나 텍스트로 표현

- 요구사항을 처리하기 위한 연산 순서가 적혀있고 연산에는 조인, 테이블 검색, 필터, 정렬 등이 있음

 

쿼리 성능 최적화

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

- SQL 코드 재구성

    -> WHERE 절을 추가하여 일부 레코드만 조회

    -> WHERE 절에 연산자 사용 자제

    -> 특정 데이터 확인 시 IN보다 EXISTS 사용

    -> 힌트를 활용하여 실행 계획의 액세스 경로 및 조인 순서 변경

- 인덱스 재구성

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

    -> 인덱스를 추가하거나 기존 인덱스의 열 순서 변경

    -> 단일 인덱스로 쓰기나 수정 없이 읽기로만 사용되는 경우 IOT(Index-Organized Table)로 구성

필기 정리

2020 정보처리기사 필기 정리