SQL (3) - 인덱스와 스토어드 프로그램

 

안녕하세요.

 

오늘은 데이터베이스 성능을 위해 중요한 역할을 하는 인덱스와 프로그래밍 언어와 같은 기능을 하는 스토어드 프로그램에 대해서 알아보겠습니다.

 

인덱스

 

기존에 만들어본 테이블의 경우 무엇인가를 찾을때 효율적이진 않습니다.

 

그런 찾아보기를 효율적으로 하기 위해 인덱스를 만드는 것입니다.

 

장점

- 검색 속도가 빨라질 수 있다.

- 쿼리의 부하가 줄어들어 성능향상

 

단점

- 인덱스가 데이터베이스 공간을 차지. (데이터베이스의 10%만큼 더 필요)

- 인덱스 생성에 시간 소요

 

인덱스의 종류

 

클러스터형 인덱스 : 사전과 같이 내용 자체가 순서대로 정렬되어 있는 것

보조 인덱스(비클러스터형 인덱스) : 찾아보기 기능과 같이 기능이 따로 설정되어 있는 것

 

자동으로 설정되는 인덱스

 

- PRIMARY KEY를 설정한 경우 기본기 열에 자동으로 클러스터형 인덱스가 설정됩니다.

- 열 생성 시, UNIQUE를 설정해주면 보조 인덱스가 설정됩니다.

- 열 생성 시, UNIQUE와 NOT NULL을 설정해주면 클러스터형 인덱스가 설정됩니다.

- 여러 열 생성 시, PRIMARY KEY를 가진 열과 UNIQUE,NOT NULL을 가진 열이 각각 생성되면 기본키를 가진 열이 클러스터형 인덱스로 설정됩니다.

 

인덱스의 내부 작동

 

1. B-Tree 구조( 균형 트리 구조 )

 : 부모 노드와 자식 노드를 나누어 설정한 데이터 구조로 데이터를 검색할 때 전체 페이지를 살펴보지 않음으로 검색에 효용성이 있다.

 

2. 페이지 분할

 : 인덱스를 구성하면 데이터 변경(INSERT, UPDATE, DELETE) 작업이 느려지는데 그 이유는 페이지 분할이 일어나기 때문이다.

 

3. 클러스터형 인덱스와 보조 인덱스의 구조

 - 검색 속도는 클러스터형 인덱스가 더 빠르다. 그리고 테이블에 한 개만 생성할 수 있다.

 - 데이터 변경 작업에서는 보조 인덱스를 사용할 때에 성능에 주는 부하가 적다. 그러나 남용하면 성능이 떨어진다.

 

인덱스 설정 정리

 

- WHERE절에서 사용되는 열에 인덱스 생성해야한다.

- 데이터의 중복도가 높은 열은 인덱스를 만들어도 효과가 없다.

- 외래 키를 지정한 열에는 자동으로 외래 키 인덱스가 생성된다.

- 데이터 변경 작업(INSERT/UPDATE/DELETE)가 얼마나 자주 일어나는가에 따른 인덱스 설정

- 클러스터형 인덱스는 테이블당 하나만 생성

- 사용하지 않는 인덱스는 삭제

 

 

스토어드 시스템

 

스토어드 프로시저는 MySQL에서 제공되는 프로그래밍 기능입니다.

 

쿼리문의 집합으로 어떠한 동작을 일괄 처리하기 위한 용도로 사용됩니다.

#형식
DELIMITER $$
CREATE PROCEDURE 스토어드 프로시저 이름(IN 매겨변수 설정)
BEGIN
	SQL 프로그램 코딩

END $$
DELIMETER ;
CALL 스토어드 프로시저 이름

 

스토어드 프로시저 내의 오류 처리

DECLARE 액션 HANDLER FOR 오류조건 처리할_문장

 

스토어드 프로시저의 특징

 

- SQL의 성능 향상

- 유지관리 간편

- 모듈식 프로그래밍 가능

- 보안 강화

 

스토어드 함수

 

사용자 지정 함수와 같은 뜻이다.

 

DELIMETER $$
CREATE FUNCTION 스토어드 함수이름( 파라미터 )
	RETURNS 반환형식
BEGIN
	프로그래밍 코딩
	RETURN 반환값;
END $$
DELIMETER ;
SELECT 스토어드_함수이름();

 

커서

 

테이블에서 여러 개의 행을 쿼리한 후에 쿼리의 결과인 행 집합을 한 행씩 처리하기 위한 방식

 

처리 순서

- 커서의 선언(DECLARE CURSOR)

- 반복 조건 선언(DECLARE CONTINUE HANDLER)

- 커서 열기(OPEN)

- 커서에서 데이터 가져오기(FETCH)

- 데이터 처리

- 커서 닫기(CLOSE)

 

#형식 예시
DECLARE userCursor CURSOR FOR -- 커서선언
	SELECT --- FROM 테이블이름
DECLARE CONTINUE HANDLER -- 행의 끝이면 endOfRow 변수에 TRUE 대입
	FOR NOT FOUND SET endOfRow=TRUE;
OPEN userCursor; --커서열기

cursor_loop : LOOP
	FETCH userCursor INTO 키_이름
    
    IF endOfRow THEN -- 더이상 읽을 행이 없으면 LOOP 종료
    	LEAVE cursor_loop;
    END IF;
END LOOP cursor_loop;
CLOSE userCursor; -- 커서닫기

 

트리거

 

트리거란 테이블에 삽입,수정,삭제 등의 작업이 발생 시에 자동으로 작동하는 개체입니다.

스토어드 프로시저와 작동이 비슷하지만 직접 실행이 불가하고 해당 테이블에 이벤트가 발생할 때만 실행됩니다.

 

#형식
CREATE TRIGGER 트리거이름
	AFTER DELETE -- 삭제 후에 작동
   	ON 테이블이름
    FOR EACH ROW -- 각행마다 적용

 

트리거 종류

 

- AFTER 트리거

- BEFORE 트리거 : 입력될 데이터 값을 미리 확인하여 문제가 있을 경우 다른 값으로 변경 가능

- 다중 트리거 : 두개 이상의 트리거가 작동되며 마지막 트리거가 실행되지 못하면 이전 상황까지 ROLL BACK 된다.