02-3 데이터베이스 개체
- 핵심 키워드:
인덱스,뷰,스토어드 프로시저
0. 시작하기 전에
테이블은 데이터베이스의 핵심 개체이다. 하지만 데이터베이스에서는 테이블 외에 인덱스, 뷰, 스토어드 프로시저, 트리거, 함수, 커서등의 개체도 필요하다.
이번 절에서는 테이블을 제외한 개체에 대하여 간단히 알아본다.
1. 인덱스(Index)
1-1 인덱스 개념
책의 제일 뒤에 수록되는 '찾아보기'와 비슷한 개념으로 결과가 출력되는 시간을 대폭 줄여준다. 인덱스를 잘 활용하지 못하면 시스템의 성능이 전체적으로 느려지는 일이 발생한다.
1-2 인덱스 실습
SELECT * FROM member WHERE member_name = '아이유';라는 구문을 통해서 '아이유'를 찾을 때는 테이블의 1행부터 전체를 살펴보게 된다. 그리고 이러한 형식은 Execution Plan에서 Full Table scan을 통해서 '아이유'를 찾았다는 알고리즘의 흐름을 간단하게 파악해볼 수 있다.
이제는 인덱스를 만들어서 SQL을 실행하여 보도록 하겠다.
CREATE INDEX idx_member _name ON member (member_name);위 SQL문의 의미는 member테이블의 member_name열에 인덱스를 지정하라는 의미다. 이 SQL문을 실행하여 주면 눈에 보이지 않지만 인덱스가 생성된다.
이제 이 인덱스를 통해서 '아이유'를 찾아보도록 하겠다. 위와 같은 SELECT문을 실행한 후 Execution Plan을 확인하여 보면 Non-Unique Key Lookup이라고 나오는 것을 알 수 있다. 상세한 의미는 6장에서 다루도록 하고, Key Lookup은 인덱스를 통해 결과를 찾았다고 기억하면 된다. 이런 방법을 인덱스 검색(Index scan)이라고 한다.
인덱스에서 주의할 점은 인덱스 생성 여부에 따라서 결과가 달라지는 것은 아니라는 것이다. 실제로 두 사진을 비교하여 보면 Cost가 0.65에서 0.35로 감소된 것을 확인할 수 있지만, 결과의 출력은 '아이유'에 대한 정보로 동일한 것을 알 수 있다.
2. 뷰(View)
2-1. 뷰 개념
뷰(View)를 한마디로 정의하면 가상의 테이블이라고 할 수 있다. 일반 사용자 입장에서는 테이블과 뷰를 구분할 수 없다. 다만 뷰는 실제 데이터를 가지고 있지 않으며, 진짜 테이블에 링크(link)된 개념이라고 생각하면 된다. 뷰의 실체는 SELECT 문이라고 할 수 있다.
2-2. 뷰 실습
CREATE VIEW member_view
AS
SELECT * FROM member;위의 SQL문을 이용하여 기본적인 뷰를 생성하여 준다. 회원테이블과 연결되는 member_view를 생성하여 준 것이다. 이 SQL문을 실행한 후 Output창을 확인하여 View가 정상적으로 생성되었는지 확인한다.
❗ SQL은 들여쓰기를 하지 않아도 정상적으로 작동한다. 이 SQL역시 한 칸만 띄어쓰기하면 1줄에 써도 무방하다. 하지만 가독성을 위하여 책에서는 들여쓰기를 사용했다.
SELECT * FROM member_view;위의 SQL문을 통하여 member_view에 접근할 수 있다. 이 문을 실행하면 회원 테이블에 접근했을 때 와 동일한 결과가 출력되는 것을 확인할 수 있다. 즉, 바탕화면의 바로 가기 아이콘을 더블클릭한 것이다.
테이블을 사용하지 않고 굳이 뷰를 사용하는 이유는 다음과 같다.
1. 보안에 도움이 된다.
2. 긴 SQL문을 간략하게 만들 수 있다.
3. 스토어드 프로시저
3-1. 스토어드 프로시저 개념
스토어드 프로시저(stored procedure)란 MySQL에서 제공하는 프로그래밍 기능으로 여러 개의 SQL문을 하나로 묶어서 편리하게 사용할 수 있다. SQL을 묶는 것 외에도 프로그래밍 언어에서 사용되는 연산식, 조건문, 반복문 등을 사용할 수도 있다.
스토어드 프로시저를 통해서 MySQL에서도 기본적인 형태의 일반 프로그래밍 로직을 코딩할 수 있다.
3-2. 스토어드 프로시저 실습
SELECT * FROM member WHERE member_name = '나훈아';
SELECT * FROM product WHERE product_name = '삼각김밥';위 두 SQL은 앞으로 자주 사용된다고 가정하여 보자. 매번 두줄의 SQL을 입력해야 한다면 상당히 불편할 것이고, SQL의 문법을 잊어버리거나 오타를 입력할 수도 있다.
이러한 문제를 방지하기 위하여 두 SQL을 하나의 스토어드 프로시저로 만들어보겠다.
첫 행과 마지막 행에 구분문자라는 의미의 DELIMITER // ~ DELIMITER ;문을 사용한다. 이 장에서는 일단 이것이 스토어드 프로시저를 묶어주는 약속이다라고 생각하면 된다. 그리고BEGIN과 END사이에 SQL문을 넣으면 된다.
이후 CALL myProc()라는 SQL을 입력하여 실행하여 주면 위 사진과 같이 두 SQL문을 실행했을 때와 동일한 결과가 나타나는 것을 확인할 수 있다.
