테이블은 데이터베이스의 핵심 개체이다. 하지만 데이터베이스에서는 테이블 외에 인덱스, 뷰, 크토어드 프로시저, 트리거, 함수, 커서 등의 개체도 필요하다.
인덱스는 데이터를 조회할 때 결과가 나오는 속도를 획기적으로 빠르게 해주고, 뷰는 테이블의 일부를 제한적으로 표현할 때 주로 사용한다. 스토어드 프로시저는 SQL에서 프로그래밍이 가능하도록 해주고, 트릭는 잘못된 데이터가 들어가는 것을 미연에 방지하는 기능을 한다.
인덱스
데이터를 조회할 때 테이블에 데이터가 적다면 결과가 금방 나오지만 데이터가 많아질수록 결과가 나오는 시간이 많이 소요된다. 인덱스는 이런 경우 결과가 나오는 시간을 대폭 줄여준다.
인덱스 개념 이해하기
인덱스(index)란 책의 제일 뒤에 수록되는 '찾아보기'와 비슷한 개념이다. 책의 내용 중에서 특정 단어를 찾고자 할 때, 책의 처음부터 마지막까지 한 페이지씩 전부 찾아보는 것은 상당히 시간이 오래 걸린다. 그래서 찾아보기를 통해 먼저 해당 단어를 찾고 바로 옆에 적혀 있는 페이지로 이동하는 효율적인 방법을 사용하는 것이다.
지금 우리가 실습하는 데이터들은 양이 많지 않기 때문에 인덱스의 필요서을 느끼지 못할 수도 있지만, 실무에서 많게는 수천만 ~ 수억 건 이상의 데이터를 처리할 때 인덱스 없이 전체 데이터를 찾아본다는 것은 상상조차 할 수 없는 일이다. 실제로 인덱스를 활용하지 못해 시스템의 성능이 전체적으로 느려지는 일이 흔하게 발생한다.
인덱스 실습하기
01
MySQLWorkbench를 실행하여 'root/0000'으로 접속하고 [File] = [Close Tab] 메뉴를 여러 번 실행해서 쿼리 창이 열려 있지 않은 상태로 만든다. 새로운 쿼리 창을 열기 위해 툴바에서 Create a new SQL tab for executing queries 아이콘을 클릭하고 [SCHEMAS] 패널의 'shop_db'를 더블 클릭해서 선택한다.
02
회원 테이블에는 아직 인덱스를 만들지 않았다. 책과 비교하면 책의 찾아보기가 없는 상태이다. 이 상태로 책에서 어떤 단어를 찾는다면 당연히 1페이지부터 전체를 찾아봐야 할 것이다. 마찬가지로 테이블에서 '아이유'를 찾을 때는 회원 테이블의 1행부터 끝까지 전체를 살펴봐야 할 것이다. 다음 SQL 문을 입력하고 실행해보자.
SELECT * FROM member WHERE member_name = '아이유' ;03
결과는 당연히 아이유를 잘 찾았을 것이다. 어떻게 아이유를 찾았는지 확인하기 위해 [Exeution Plan(실행 게획)] 탭을 클릭하면 Full Table Scan이라고 나온다. 이것을 해석하면 전체 테이블 검색 정도가 될 텐데, 처음부터 끝까지 엄청나게 오랜 시간이 걸려서 '아이유'를 찾은 것이다. 현재 인덱스가 없기 때문에 별다른 방법이 없다.
04
이제는 회원 테이블에 인덱스를 만들어 보자. 다음 SQL을 실행하면 인덱스가 생성된다. 인덱스는 열에 지정한다. SQL의 마지막에 ON member(member_name)의 의미는 member 테이블의 memeber_name 열에 인덱스를 지정하라는 의미. 결과는 특별히 눈에 보이지 않는다. 이렇게 인덱스 생성됨.
CREATE INDEX idx_member_name ON member(memeber_name);05
이제는 인덱스가 생긴 회원 테이블에서 아이유를 찾아보자. 03번에서 사용한 SQL을 다시 실행해보자. 역시 결과는 동일. 하지만 이번에는 찾는 방법이 달라졌다. [Execution Plan] 탭을 보면 Non-Unique Key Lookup이라고 나온다. 자세한 의미는 6장에서 알아보는 것으로 하고, Key Lookup은 인덱스를 통해 결과를 찾았다고 기억하면 된다. 이런 방법을 인덱스 검색(Index Scan)이라고 부른다.
SELECT * FROM member WHERE member_name = '아이유';인덱스에서 한 가지 더 기억해야 할 점은 인덱스 생성 여부에 따라 결과가 달라지는 것은 아니라는 것이다. 즉 책의 내용을 찾을 때 찾아보기가 있으면 시간을 단축하는 효과는 있지만, 책의 찾아보기가 없어도 책의 첫 페이지부터 찾아야 하기 때문에 시간이 오래 걸릴 뿐 어차피 동일하게 찾을 수 있다.
뷰
뷰는 테이블과 상당히 동일한 성격의 데이터베이스 개체이다. 뷰를 활용하면 보안도 강화하고, SQL문도 간단하게 사용할 수 있다.
뷰 개념 이해하기
뷰는 위도우즈 운영 체제의 '바로 가기 아이콘'과 비슷한 개념이다. 윈도우즈에서 바탕 화면의 바로가기 아이콘을 더블 클릭해서 실행하지만, 실제로 실행되는 파일은 다른 폴더에 있다. 예를 들어, 바탕 화면에 있는 크롬 브라우저의 바로 가기 아이콘은 C:\Program Files\Google\Chrome\Application 폴더의 chrome.exe와 연결되어 있다.
지금까지 바탕 화면의 크롬 아이콘을 더블 클릭해서 프로그램이 실행된다고 생각해도 아무런 문제없이 크롬을 잘 사용해 왔다. 굳이 복잡한 폴더 구조까지 생각하지 않아도 되었던 것이다.
뷰도 비슷한 개념으로 실체는 없으며 테이블과 연결되어 있는 것뿐이다. 사용자가 뷰를 테이블처럼 생각해서 접근하면 알아서 테이블에 연결해준다.
그렇다면 뷰의 실체는 무엇일까? 이 그림에도 나와 있듯이, 뷰의 실체는 바로 SELECT 문이다.
뷰 실습하기
뷰도 SQL 문을 통해 MySQL 워크벤치에서 생성할 수 있다.
01
MySQL WorkBench 실행하여 'root/0000'으로 접속하고 [File] - [Close Tab] 메뉴를 여러 번 실행해서 쿼리 창이 열려 있지 않은 상태로 만든다. 새로운 쿼리 창을 열고 [SCHEMAS] 패널에서 'shop_db'를 선택한다.
02
기본적인 뷰를 만들어보자. 회원 테이블과 연결되는 회원 뷰(member_view)를 만들기 위해 다음 SQL을 실행한다. [Output] 패널에 초록색 체크 표시가 나타나면 SQL이 제대로 실행되었다는 의미이다.
CREATE VIEW member_view
AS
SELECT * FROM memeber;03
이제는 회원 테이블(member)이 아닌 회원 뷰(member_view)에 접근해보자. 뷰에 접근하는 것은 테이블에 접근하는 것과 동일하다. 다음 SQL을 실행하면 회원 테이블에 접근했을 때와 동일한 결과가 나온다. 즉, 바탕 화면의 크롬 바로 가기 아이콘을 더블 클릭하든, 직접 해당 폴더에서 chrome.exe를 실행하든 크롬이 실행되는 것과 동일한 개념이다.
그렇다면 테이블을 사용하지 않고 굳이 뷰를 사용하는 이유는 뭘까? 다음과 같은 이유이다.
- 보안에 도움이 된다.
- 긴 SQL 문을 간략하게 만들 수 있다.
스토어드 프로시저
스토어드 프로시저를 통해 SQL 안에서도 일반 프로그래밍 언어처럼 코딩할 수 있다. 비록 일반 프로그래밍보다는 좀 불편하지만, 프로그래밍 로직을 작성할 수 있어서 때론 유용하게 사용 가능.
스토어드 프로시저 개념 이해하기
스토어드 포르시저(stored procedure)란 MySQL에서 제공하는 프로그래밍 기능으로, 여러 개의 SQL 문을 하나로 묶어서 편리하게 사용할 수 있다. SQL을 묶는 개념 외에 C, 자바, 파이썬과 같은 프로그래밍 언어에서 사용되는 연산식, 조건문, 반복문 등을 사용할 수도 있다.
스토어드 프로시저를 통해서 MySQL에서도 기본적인 형태의 이반 프로그래밍 로직을 코딩할 수 있다.
스토어드 프로시저 실습하기
스토어드 프로시저도 MySQL 워크벤치에서 SQL 문을 사용해서 생성할 수 있다.
01
MySQL Workbench를 실행하여 'root/0000'으로 접속하고 [File] - [Close Tab] 메뉴를 여러 번 실행해서 쿼리 창이 열려 있지 않은 상태로 만든다. 새로운 쿼리 창을 열고 [SCHEMAS] 패널에서 'shop_db'를 선택한다.
02
다음 두 SQL을 입력하고 한꺼번에 실행. 예상대로 별도의 탭으로 동시에 결과가 나온다. 그런데 이 두 SQL은 앞으로 상당히 자주 사용된다고 가정해보자. 매번 두 줄의 SQL을 입력해야 한다면 상당히 불편할 것이고, SQL의 문법을 잊어버리거나 오타를 입력할 수도 있다.
SELECT * FROM member WHERE member_name = '나훈아';
SELECT * FROM product WHERE product_name = '삼각김밥';03
두 SQL을 하나의 스토어드 프로시저로 만들어보자. 다음 SQL을 입력하고 실행해보자. 첫 행과 마지막 행에 구분 문자라는 의미의 DELIMITER // ~ DELIMITER; 문이 나왔다. 일단 이것은 스토어드 프로시저를 묶어주는 약속으로 생각하자. 그리고 BEGIN과 END 사이에 SQL 문을 넣으면 된다.
DELIMITER //
CREATE PROCEDURE myProc() → 스토어드 프로시저 이름 지정
BEGIN
SELECT * FROM member WHERE member_name = '나훈아';
SELECT * FROM product WHERE member_name = '삼각김밥';
END //
DELIMITER ;04
이제부터는 두 줄의 SQL 문을 실행할 필요 없이 앞에서 만든 스토어드 프로시저를 호출하기 위해서 CALL 문을 실행하면 된다. 다음 SQL 실행하면 동일한 것을 확인할 수 있다.
CALL myProc()+ CREATE 문과 DROP 문
테이블, 인덱스, 뷰, 스토어드 프로시저 등의 데이터베이스 개체를 만들기 위해서는 CREATE 개체종류 개체이름 ~~ 형식을 사용한다. 반대로 데이터베이스 개체를 삭제하기 위해서는 DROP 개체종류 개체이름 형식을 사용한다. 예로, 실습에서 생성한 스토어드 프로시저를 삭제하면 DROP PROCEDURE myProc를 사용한다.
MySQL 워크벤치에서 생성, 삭제하려면 먼저 [SCHEMAS] 패널의 빈 곳에서 마우스 오른쪽 버튼을 클릭하고 [Refresh All]을 선택해서 새로 고침한다. 그리고 생성, 삭제할 데이터 베이스 개체에서 마우스 오른쪽 버튼을 클릭하고 생성하려면 [Create 데이터베이스개체]를, 삭제하려면 [Drop 데이터베이스_개체]를 선택한다._
정리
- 인덱스는 책 뒤의 '찾아보기'와 비슷한 개념. 데이터를 빠르게 찾을 수 있도록 도와준다.
- 뷰는 '바로 가기 아이콘'과 비슷한 개념. 한마디로 정의하면 '가상의 테이블'이라고 할 수 있다.
- 스토어드 프로시저는 여러 개의 SQL을 묶어주거나, 프로그래밍 기능을 제공해준다.
관련 중요 용어
- 데이터베이스 개체 : Database Object / 테이블 뷰, 인덱스, 스토어드 프로시저 등 데이터베이스 안에 저장되는 개체
- 실행 계획 : Execution Plan / SQL을 실행할 때, 인덱스 사용 여부를 확인할 수 있는 워크벤치의 화면
- 전체 테이블 검색 : Full Table Scan / 테이블의 모든 데이터를 훑어서 원하는 데이터를 찾아내는 것을 말함. 책 전체를 찾아보는 것과 비슷함
- 인덱스 검색 : Index Scan / 인덱스를 통해서 데이터를 찾는 것ㅇ르 말함. 책 뒤의 찾아보기를 사용한 것과 비슷함.
- 구분 문자 : DELIMITER / 스토어드 프로시저를 묶어주는 예약어
- 호출 : CALL / 스토어드 프로시저를 호출하는 예약어
- 개체 생성문 : CREATE / 데이터베이스 개체를 생성할 때 사용하는 예약어
- 개체 삭제문 : DROP / 데이터베이스 개체를 삭제할 때 사용하는 예약어
