[고급DB] Employees 데이터베이스를 활용한 인덱스 개념 이해

db index

현재 수강중인 고급데이터베이스의 3번째 과제는 MySQL 사이트(https://dev.mysql.com/doc/employee/en/) 에서 제공하는 employees database를 이용하여 인덱스에 대한 개념을 이해하는 것이다.
* employees database에는 약 4,000,000 개의 레코드(튜플)이 존재한다.


1. 테이블 생성 SQL

employees ( indexed )

CREATE TABLE employees (
    emp_no      INT             NOT NULL,
    birth_date  DATE            NOT NULL,
    first_name  VARCHAR(14)     NOT NULL,
    last_name   VARCHAR(16)     NOT NULL,
    gender      ENUM ('M','F')  NOT NULL,    
    hire_date   DATE            NOT NULL,
    PRIMARY KEY (emp_no)
);

CREATE TABLE departments (
    dept_no     CHAR(4)         NOT NULL,
    dept_name   VARCHAR(40)     NOT NULL,
    PRIMARY KEY (dept_no),
    UNIQUE  KEY (dept_name)
);

CREATE TABLE dept_manager (
   dept_no      CHAR(4)         NOT NULL,
   emp_no       INT             NOT NULL,
   from_date    DATE            NOT NULL,
   to_date      DATE            NOT NULL,
   KEY         (emp_no),
   KEY         (dept_no),
   FOREIGN KEY (emp_no)  REFERENCES employees (emp_no)    ON DELETE CASCADE,
   FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
   PRIMARY KEY (emp_no,dept_no)
); 

CREATE TABLE dept_emp (
    emp_no      INT             NOT NULL,
    dept_no     CHAR(4)         NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE            NOT NULL,
    KEY         (emp_no),
    KEY         (dept_no),
    FOREIGN KEY (emp_no)  REFERENCES employees   (emp_no)  ON DELETE CASCADE,
    FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
    PRIMARY KEY (emp_no,dept_no)
);

CREATE TABLE titles (
    emp_no      INT             NOT NULL,
    title       VARCHAR(50)     NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE,
    KEY         (emp_no),
    FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
    PRIMARY KEY (emp_no,title, from_date)
); 

CREATE TABLE salaries (
    emp_no      INT             NOT NULL,
    salary      INT             NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE            NOT NULL,
    KEY         (emp_no),
    FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
    PRIMARY KEY (emp_no, from_date)
); 

employees2 (no-indexed)

CREATE TABLE employees (
    emp_no      INT             NOT NULL,
    birth_date  DATE            NOT NULL,
    first_name  VARCHAR(14)     NOT NULL,
    last_name   VARCHAR(16)     NOT NULL,
    gender      ENUM ('M','F')  NOT NULL,    
    hire_date   DATE            NOT NULL
);

CREATE TABLE departments (
    dept_no     CHAR(4)         NOT NULL,
    dept_name   VARCHAR(40)     NOT NULL
);

CREATE TABLE dept_manager (
   dept_no      CHAR(4)         NOT NULL,
   emp_no       INT             NOT NULL,
   from_date    DATE            NOT NULL,
   to_date      DATE            NOT NULL
); 

CREATE TABLE dept_emp (
    emp_no      INT             NOT NULL,
    dept_no     CHAR(4)         NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE            NOT NULL
);

CREATE TABLE titles (
    emp_no      INT             NOT NULL,
    title       VARCHAR(50)     NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE
); 

CREATE TABLE salaries (
    emp_no      INT             NOT NULL,
    salary      INT             NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE            NOT NULL
); 

2. indexing과 non-indexing 비교 분석

(1) indexing query use

1) Equality

# emp_no에 indexing이 되어 있음
SELECT  *
FROM    dept_emp
WHERE   emp_no = 100000;
indexedProfiling
profile1

Explain
explain1
Visual Explain
visual1
non-indexedProfiling
profile2

Explain
explain2
Visual Explain
visual2

2) Range

# emp_no에 indexing이 되어 있음
SELECT    *
FROM      salaries
WHERE     emp_no BETWEEN 110000 AND 120000;
indexedProfiling
profile3

Explain
explain3
Visual Explain
visual3
non-indexedProfiling
profile4

Explain
explain4
Visual Explain
visual4

3) composite search keys

# emp_no와 from_date에 indexing이 되어 있음
SELECT  *
FROM    salaries
WHERE   emp_no = 100000
AND     from_date BETWEEN '1995-01-01' AND '2000-12-31';
indexedProfiling
profile5

Explain
explain5
Visual Explain
visual5
non-indexedProfiling
profile6

Explain
explain6
Visual Explain
visual6


(2) indexing query not use

1) Equality

# first_name은 indexing 되어 있지 않음
SELECT * FROM employees WHERE first_name = 'George';
indexedProfiling
profile7

Explain
explain7
Visual Explain
visual7
non-indexedProfiling
profile8

Explain
explain8
Visual Explain
visual8

2) Range

# from_date는 indexing 되어 있지 않음
SELECT   *
FROM     titles
WHERE    from_date BETWEEN '2000-01-01' AND '2001-12-31';
indexedProfiling
profile7

Explain
explain7
Visual Explain
visual7
non-indexedProfiling
profile8

Explain
explain8
Visual Explain
visual8

3) composite search keys

SELECT  *
FROM    employees
WHERE   first_name LIKE 'Al%' # first_name은 non-index
AND     last_name  LIKE '%el' # last_name은 non-index
AND     gender = 'M';         # gender는 non-index
indexedProfiling
profile9

Explain
explain9
Visual Explain
visual9
non-indexedProfiling
profile10

Explain
explain10
Visual Explain
visual10

(3) 위의 결과를 토대로 생각해보는 index가 주는 영향

  1. index를 지정한 field(column)에 대한 조건 검색을 수행할 경우, single row scan, range scan 등을 수행하기 때문에 수행 시간이 빠르다.
  2. index를 지정하지 않을 경우 조건에 상관 없이 항상 full table scan을 실행한다.
  3. index를 지정하지 않은 field(column)에 대해 조건 검색을 수행할 경우, 수행 시간에는 큰 차이가 없다.

4. Join을 사용하는 query

1) 비교

SELECT  e.first_name, e.last_name, s.salary, t.title,
        de.from_date as de_from, de.to_date as de_to,
        dm.from_date as dm_from, dm.to_date as dm_to
FROM    employees e
JOIN    salaries s      ON s.emp_no = e.emp_no
JOIN    titles t        ON s.emp_no = t.emp_no
JOIN    dept_emp de     ON s.emp_no = de.emp_no
JOIN    dept_manager dm ON s.emp_no = de.emp_no;
indexed
Profiling
profile11

Explain
explain11

Visual Explain
visual11
non-indexed
Profiling
- 데이터가 심각하게 많아서 측정불가
- 10분 이상 돌려도 결과가 도출되지 않음

Explain
explain12

Visual Explain
visual12

2) 성능차이가 발생하는 이유

  1. index가 없는 필드를 기준으로 JOIN을 하게 되면, Full Table Scan을 하면서 각 row마다 또 Full Table Scan을 하게 된다.
    즉, 10000개의 튜플이 있는 테이블과 1000개의 튜플이 있는 테이블을 JOIN하면 10000 * 1000 번의 scan을 수행한다.
  2. 반대로 index가 있는 필드를 기준으로 JOIN을 하게 되면 최소 1 row scan이 가능하다
  3. 5중첩 JOIN에 대한 질의문을 수행했을 때
    index가 있는 경우에는 108.87 * 1000000 번의 scan을 수행하고,
    index가 없는 경우에는 24 * 299389 * 331008 * 442123 * 2707962 번의 scan을 수행한다.
    (수행하다가 컴퓨터 망가질 뻔했다)
  4. 즉, index를 하지 않으면 무조건 full table scan * full table scan 형태로 테이블을 조회한다.
  5. 이것은 데이터가 많아질수록 매우 치명적이다.

5. employees2 개선

1) Equlity

SELECT  * FROM dept_emp WHERE emp_no = 100000;

단일 Equlity만 사용하는 경우, 해당 필드만 Index로 만드는 게 좋다.

ALTER TABLE `dept_emp` ADD INDEX `index1` USING BTREE (`emp_no`) VISIBLE;
employees1employees2
visual13visual14

2) Range

SELECT    *
FROM      salaries
WHERE     emp_no BETWEEN 110000 AND 120000;

이 경우 emp_no에만 index를 적용하면 오히려 성능이 안 좋게 나온다. emp_no는 unique한 값이 아니기 때문이다.

employees1employees2
visual15visual16

그래서 emp_no와 from_date에 index를 지정하여 그룹으로 묶어야 한다. 그렇게 되면 인덱스의 갯수가 줄어들고, 최소한의 scan을 하게 된다.

ALTER TABLE `employees2`.`salaries` 
ADD PRIMARY KEY (`emp_no`, `from_date`);
employees1employees2
visual15visual15

explain의 결과가 완벽하게 동일하다. 다만 의문인점은, employees1에는 emp_no에만 단독으로 index가 지정되어있으며, 해당 인덱스는 현재 질의문에 아무런 영향도 주지 못하고 있다.

3) Composite Search Key

SELECT  *
FROM    salaries
WHERE   emp_no = 100000
AND     from_date BETWEEN '1995-01-01' AND '2000-12-31';

이 질의문 또한 emp_no와 from_date에 대해 탐색하고 있다. 따라서 2)에서 사용한 index를 변경하지 않고 그대로 사용하면 된다.

6. 결론

1) Clustered index의 효용성에 대한 적합한 예제

clustered index에 대한 효과를 확실하게 확인할 수 있는 부분은 JOIN 질의문이다.

SELECT  e.first_name, e.last_name, s.salary, t.title,
        de.from_date as de_from, de.to_date as de_to,
        dm.from_date as dm_from, dm.to_date as dm_to
FROM    employees e
JOIN    salaries s      ON s.emp_no = e.emp_no
JOIN    titles t        ON s.emp_no = t.emp_no
JOIN    dept_emp de     ON s.emp_no = de.emp_no
JOIN    dept_manager dm ON s.emp_no = de.emp_no;
employees1employees2
visual11visual12

이러한 질의문을 사용할 경우, 인덱스가 없는 것과 있는 것은 하늘과 땅 차이임을 확인할 수 있다.

2) index-only plan의 효용성에 대한 예

index only plan을 사용한 예제중 가장 적합한 예는 Range에서 작성한 질의문이다.

# emp_no에 indexing이 되어 있음
SELECT    *
FROM      salaries
WHERE     emp_no BETWEEN 110000 AND 120000;

이 질의문은 index only plan으로 수행 가능하다.

indexedProfiling
profile3

Explain
explain3
Visual Explain
visual3
non-indexedProfiling
profile4

Explain
explain4
Visual Explain
visual4

Equlity 조건도 마찬가지지만, index를 사용 할 경우, 탐색의 시작 offset으로 갈 때는 1 만큼의 시간이 걸린다. 하지만 index를 사용하지 않은 경우에 첫 rid부터 탐색 위치까지 scan을 수행해야한다. 만약에 탐색의 시작 offset이가 100만이라고 한다면, 1부터 100만까지 scan을 해야 되는 경우가 생긴다.


ETC

  • error1
    • error content : lost connection to mysql server during query
    • error solution : Edit → Preferences → SQL Editor → DBMS connection read time out (in seconds): 30 to 600