코딩하는 eroke

SQL 성능개선 팁을 정리해보려 한다.

1. 인덱스 사용 시 주의사항 

- 조회(SELECT, WHERE)시에는 속도에 도움을 주지만 삽입(INSERT), 갱신(UPDATE)시에는 오히려 느려진다.

- 데이터가 적은 경우에는 사용하지 않는 게 좋다.

- 조회쿼리 성능개선을 위해 WHERE 절에 자주 사용되는 컬럼은 인덱스로 지정하기도 하는데 주의할 점이 있다.

- 구간 별 선택이 많은 컬럼은 클러스터 인덱스 추천 

특정 컬럼을 인덱스로 지정해도 사용할 수 없는 경우

• LIKE '%'을 변수 앞에서 사용 > 무조건 FULL SCAN
• IS NULL, IS NOT NULL 사용
• 여러컬럼에 OR 사용
• 부정비교에 사용 (예시 : NOT, <>, !=. NOT EXISTS )
• 컬럼을 변형한 경우 (예시 : substr() )

2. 데이터 추출 시 주의사항

• select에 필요한 컬럼만 지정 (select * 지양, 사용하지 않는 데이터 호출에도 부하가 발생할 수 있음)
• where 절 조건문 작성 시, 가장 많은 데이터를 걸를 수 있는 컬럼을 우선적으로 작성
• where 절 조건문 작성 시, 왼쪽은 되도록 변형되지 않은 순수 컬럼을 사용
• 정렬 (order by)하지 않고 limit, top을 사용중인지 확인
• 백만건이 넘어갈 시에는 파티셔닝을 고려
• IN < EXISTS < INNER JOIN 순으로 가독성은 떨어지지만 성능이 좋음
• 서브쿼리보다 JOIN을 지향 (상황에 따라 서브쿼리로 추출 후 조인 시 성능이 개선될 수도 있음)
• COUNT()보다 EXISTS() 지향
  • COUNT()는 모든 레코드 중 관련 데이터를 필터링 한 후 함수를 수행
  • EXISTS()는 필터링 시 하나라도 레코드가 있을 때 반환  

추후 개선 알게되는 팁은 계속해서 추가할 예정입니닷,,

Reference

INDEX 인덱스 (색인)

• 책으로 비유하자면 목차
• DBMS에서 저장 성능을 희생하여 데이터 읽기 속도를 높이는 기능
• 데이터가 정렬되어 들어감
• 양이 많은 테이블에서 일부 데이터만 불러 왔을 때, 이를 풀 스캔 시 처리 성능 떨어짐

• 종류
  • B+-Tree 인덱스 : 원래의 값을 이용하여 인덱싱
  • Hash 인덱스 : 칼럼 값으로 해시 값 게산하여 인덱싱, 메모리 기반 DB에서 많이 사용
  • B>Hash
• 생성시 고려해야 할 점
  • 테이블 전체 로우 수 15%이하 데이터 조회시 생성
  • 테이블 건수가 적으면 인덱스 생성 하지 않음, 풀 스캔이 빠름
  • 자주 쓰는 컬럼을 앞으로 지정
  • DML시 인덱스에도 수정 작업이 동시에 발생하므로 DML이 많은 테이블은 인덱스 생성 하지 않음

DBMS : 데이터베이스 관리 시스템

다수의 사용자가 데이터베이스 내의 데이터를 접근할 수 있도록 설계된 시스템

• 정의 기능(DDL: Data Definition Language)
  • 데이터베이스가 어떤 용도이며 어떤 식으로 이용될것이라는 것에 대한 정의가 필요함
  • CREATE, ALTER, DROP, RENAME
• 조작 기능(DML: Data Manipulation Language)
  • 데이터베이스를 만들었을 때 그 정보를 수정하거나 삭제 추가 검색 할 수 있어야함
  • SELECT, INSERT, UPDATE, DELETE
• 제어 기능(DCL: Data Control Language)
  • 데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령
  • GRANT REVOKE

UML (Unified Modeling Language) : 통합 모델링 언어 - 프로그램 설계를 표현하기 위해 사용하는 그림으로 된 표기법

View = 가상 테이블

하나 이상의 테이블에서 유도된 가상 테이블

• 사용자가 view에 접근했을 때 해당하는 데이터를 원본에서 가져옴
• view에 나타나지 않은 데이터를 간편히 보호할 수 있는 장점 존재 (데이터를 제한적으로 보여줄 수 있음)

정규화

• 중복 최소화/ 데이터 구조화/ 불필요한 데이터 제거/ 이상현상 방지!
• 테이블 구성을 논리적, 직관적으로 함 
• CRUD 많이 일어나는 DB는 정규화 되는게 좋음 - OLTP (OnLine Analytical Processing) DB  : 온라인 거래 시스템

이상현상 (Anomaly)

릴레이션에서 일부 속성들의 종속 > 데이터 중복 > 데이터 불일치가 발생하는 것 (insert, update, delete)

• 갱신 이상 (Modification Anomaly) : 반복된 데이터 중에 일부를 갱신할 시 데이터의 불일치 발생
• 삽입 이상 (Insertion Anomaly) : 불필요한 정보를 함께 저장하지 않고서는 어떤 정보를 저장하는 것이 불가능하다.
• 삭제 이상 (Deletion Anomaly) : 필요한 정보를 함께 삭제하지 않고서는 어떤 정보를 삭제하는 것이 불가능하다.

반정규화

• 정규화 역과정. 정규화된 시스템을 성능 향상 및 개발과 운영의 단순화를 위해 역으로 정규화를 수행.
• 조회에 대한 처리가 중요하다고 판단될 때 부분적으로 반정규화 수행   
• 일반적으로 join을 많이 사용해야 할 경우, 대량의 범위를 자주 처리하는 경우 등 

데이터베이스 무결성

• DB 설계에서 가장 중요!
• 테이블에 있는 모든 행들이 유일한 식별자를 가질 것을 요구함 (같은 값 X)
• 외래키 값은 NULL이거나 참조 테이블의 PK값이어야 함
• 한 컬럼에 대해 NULL 허용 여부와 자료형, 규칙으로 타당한 데이터 값 지정

- 무결성 보장 방법

• 데이터 조작 프로그램 내에서 데이터 생성, 수정, 삭제 시 무결성 조건 검증하기
• 트리거 이벤트 시 저장 SQL을 실행하고 무결성 조건을 실행하기
• DB 제약조건 기능 선언하기

트리거 : 자동으로 실행되도록 정의된 저장 프로시저

• insert, update, delete문에 대한 응답을 자동으로 호출
• 업무 규칙 보장, 업무 처리 자동화, 데이터 무결성 강화

Oracle VS MySQL

오라클 : 대규모 트랜잭션 로드 처리, 성능 최적화를 위해 여러 서버에 대용량 DB 분산

MySQL : 단일 데이터베이스로 제한, 대용량 데이터베이스로는 부적합. 작은 프로젝트에서 적용시키기 용이하며 이전 상태를 복원하는데 commit과 rollback만 존재

Commit과 Rollback

• Commit : 하나의 논리적 단위(트랜잭션)에 대한 작업이 성공적으로 끝났을 때, 이 트랜잭션이 행한 갱신 연산이 완료된 것을 트랜잭션 관리자에게 알려주는 연산
• Rollback : 하나의 트랜잭션 처리가 비정상적으로 종료되어 DB의 일관성을 깨뜨렸을 때, 모든 연산을 취소시키는 연산

JDBC와 ODBC

• JDBC : 자바에서 DB에 접근하여 데이터를 조회, 삽입, 수정, 삭제 가능 DBMS 종류에 따라 맞는 jdbc를 설치해야함
• ODBC : 응용 프로그램에서 DB 접근을 위한 표준 개방형 응용 프로그램 인터페이스. MS사에서 만듦, Excel/Text 등 여러 종류의 데이터에 접근할 수 있음

Reference