MySQL Index (2) 기본 기능

 

 

인덱스는 단순히 Select 조회 뿐만아니라 Update/Insert/Delete와 같은 다양한 쿼리에 모두 영향을 줍니다. 따라서 인덱스 생성은 매우 중요한 작업이라고 할 수 있습니다. 인덱스를 생성시의 가장 중요한 원칙은 cardinality가 높은 컬럼을 인덱스로 지정해야 한다는 것입니다. cardinality 를 간단히 정의하면, 원소의 개수를 의미합니다. 데이터를 더 빠르게 찾기위해서 색인(index)를 만든다고하면, 최대한 중복되는 개수가 적은 성질의 데이터를 이용해서 인덱스를 만드는 것이 인덱스에서는 중요합니다.

 

 

만약에 인덱스로 찾아낸 데이터를 다시 걸러내야 한다면, 디스크 접근에 걸린 시간만큼 많은 시간이 소요될 것입니다.

그리고 복합 인덱스의 경우에는 불필요한 row에 접근을 줄이기 위해서, 복합 인덱스 순서를 cardinality가 높은 순에서 낮은 순으로 잡아야 성능이 좋습니다.   

 

 

 

실제로 테스트 데이터를 가지고 테스트를 진행해보겠습니다. 

 

먼저 테이블을 생성합니다. 사원을 모델링한 샘플 테이블입니다.

CREATE TABLE employees (
	id INTEGER AUTO_INCREMENT PRIMARY KEY,
	email	    VARCHAR(50)     NOT NULL,
	first_name  VARCHAR(14)     NOT NULL,
	last_name   VARCHAR(16)     NOT NULL,
	birth_date  DATE            NOT NULL,
	mobile      VARCHAR(30)     NOT NULL,
	zipcode     VARCHAR(15)     NOT NULL,
	address     VARCHAR(200)	NOT NULL,
	hire_date   DATE            NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 

 

테스트 데이터의 입력은 NodeJS의 faker 패키지를 이용해서 입력하였습니다. 실제 테스트에 사용한 코드는 아래 github에 있습니다.

https://github.com/bitedd/util/tree/main/nodejs_mysql_faker_test_data

GitHub - bitedd/util

 

 

 

 

NodeJS 프로그램을 실행해서 입력후,

실제 테이터의 cardinality를 확인해보겠습니다. row 건수는 4000만건입니다.

SELECT COUNT(DISTINCT(email)) email,
COUNT(DISTINCT(first_name)) first_name,
COUNT(DISTINCT(last_name)) last_name,
COUNT(DISTINCT(birth_date)) birth_date,
COUNT(DISTINCT(mobile)) mobile,
COUNT(DISTINCT(gender)) gender,
COUNT(DISTINCT(zipcode)) zipcode,
COUNT(DISTINCT(address)) address,
COUNT(DISTINCT(hire_date)) hire_date
FROM employees;
+----------+------------+-----------+------------+----------+--------+----------+---------+-----------+
| email    | first_name | last_name | birth_date | mobile   | gender | zipcode  | address | hire_date |
+----------+------------+-----------+------------+----------+--------+----------+---------+-----------+
| 20877278 |       3007 |       473 |      21555 | 39995119 |      2 | 19903256 |  462509 |      4023 |
+----------+------------+-----------+------------+----------+--------+----------+---------+-----------+
1 row in set (8 min 7.41 sec)

 

위 테스트데이터에서 cardinality가 높은 순으로 mobile > email > zipcode 인 것을 알 수 있습니다. 현실세계에서는 cardinality가 email == mobile > zipcode 의 순이겠지만, 테스트 데이터에서는 이것을 반영하기 어려워서 이렇게 되었습니다.

 

 

 

인덱스를 생성합니다.

CREATE INDEX IDX_CARDINALITY_ORDER ON employees (mobile , email, gender);

 

 

 

이제, WHERE 조건에 컬럼에 대한 조건을 나눠서 인덱스를 이용하는지 확인해보았습니다.

 

• WHERE 조건에 인덱스 컬럼모두를 포함한 경우

EXPLAIN SELECT *
FROM employees 
use index (IDX_CARDINALITY_ORDER)
where mobile in ('710.892.6785','784.865.2536', '814.685.8468', '662.483.8634', '539.654.4324 x470750', '818.536.2104 x265') 
and email in ( 'Jaden_Streich85@gmail.com' , 'Ambrose_Brekke@hotmail.com', 'Ambrose_Brick@hotmail.com', 'Ambrose_Brekke@gmail.com', 'Ambrose_Brekke@daum.com')
and gender = 'F';



+----+-------------+-----------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | IDX_CARDINALITY_ORDER_2 | IDX_CARDINALITY_ORDER_2 | 276     | NULL |   30 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.20 sec)

 

• WHERE 조건에 인덱스 컬럼중에서 중간것을 포함하지 않는 경우

EXPLAIN SELECT *
FROM employees 
use index (IDX_CARDINALITY_ORDER)
where mobile in ('710.892.6785','784.865.2536', '814.685.8468', '662.483.8634', '539.654.4324 x470750', '818.536.2104 x265') 
and gender = 'F';

+----+-------------+-----------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | IDX_CARDINALITY_ORDER_2 | IDX_CARDINALITY_ORDER_2 | 92      | NULL |    6 |    10.00 | Using index condition |
+----+-------------+-----------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.20 sec)

 

• WHERE 조건에 인덱스의 첫번째 컬럼을 포함하지 않는 경우

EXPLAIN SELECT *
FROM employees 
use index (IDX_CARDINALITY_ORDER)
where email in ( 'Jaden_Streich85@gmail.com' , 'Ambrose_Brekke@hotmail.com', 'Ambrose_Brick@hotmail.com', 'Ambrose_Brekke@gmail.com', 'Ambrose_Brekke@daum.com')
and gender = 'F';

+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 38014446 |     5.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.20 sec)

 

 

위의 explain 실행결과의 filtered 컬럼의 내용이 인덱스로 제외된 비율을 의미합니다. 이 숫자가 높을 수록 인덱스를 잘 이용했다는 의미가 됩니다.  type 컬럼의 ALL은 full scan이고, range의 경우는 range 스캔을 의미한다.

 

WHERE 조건에 포함된 인덱스 컬럼 (인덱스를 A, B, C 로 작성한 경우)	Extra	type	filtered 값
A B C	Using index condition	range	100.0
A C	Using index condition	range	10.0
B C	Using where	ALL	5.0

 

마지막에 인덱스 컬럼중에서 B C 즉, 첫번째 컬럼을 제외한 경우에는 Extra가 Using where 로 표시되고 있습니다. 이것은 인덱스를 이용하지 못했다는 의미입니다. 복합 인덱스의 경우에는 첫번째 인덱스가 작성되있어야만 인덱스를 사용합니다. 왜냐면, 복합 인덱스의 정보는 계층적으로 저장이 되기 때문입니다.

 

 

 

 

 

 

 

---

 

 

 

---

다음으로 인덱스 이용시의 예외 사항에 대해서 알아보겠습니다.

 

• 범위조건

그외에 인덱스 조회시에 몇가지 특성이 있습니다.

BETWEEN LIKE < > 과 같은 범위 조건을 사용하는 경우에는 그 칼럼은 인덱스를 사용하지만, 그 뒤 인덱스는 인덱스를 사용하지 못합니다. 예를 들어서, index로 컬럼을 name, age, gender 로 잡았고, age > 10과 같이 범위조건을 주는 경우에는 name, age는 인덱스를 이용하지만, 뒤의 컬럼인 gender는 인덱스를 이용하지 못하게 됩니다.

 

 

 

• 컬럼에 대한 조작

인덱스로 사용한 컬럼을 조작하는 경우에는 인덱스를 이용하지 못합니다.

예를 들어서 인덱스를 이용하는 경우와 이용하지 못하는 예를 정리해보았습니다.

 

인덱스를 이용하지 못하는 WHERE 조건	인덱스를 이용하는 WHERE 조건	비고
WHERE SUBSTR(mobile, 1, 3) = '010'	WHERE mobile LIKE '010%'	컬럼을 가공
WHERE age - 19 > 0;	WHERE age > 19;	컬럼을 가공
WHERE hire_date = '20210310';	WHERE hire_date = TO_DATE('20210310', 'YYYMMDD');	인덱스 컬럼의 묵시적 형변환 => 같은 타입으로 비교해야함.
WHERE gender != 'M'	WHERE gender = 'M'	인덱스 컬럼의 부정형 비교

 

 

 

• WHERE 조건의 순서

WHERE 조건의 인덱스 조건을 작성하는 순서는 옵티마이저가 처리해주기 때문에, 크게 의미가 없습니다. 하지만, 옵티마이저의 부하도 생각한다면, 이 순서도 맞춰주어야만 합니다. 

 

 

 

 

 

 

 

 

---

ORDER BY 

 

복합 인덱스에서 컬럼중 특정 컬럼에 대해서만 ASC/DESC 를 설정할 수 없고, 전체 컬럼에 대해서만 ASC/DESC를 지정할 수 있습니다. 

 

ORDER BY 에서 인덱스가 적용되지 않는 경우가 있습니다.

 

예를 들어서 index를 컬럼 A, B, C 으로 설정하였을 경우, 항상 이 순서(ORDER BY A, B, C)로 지정해야 합니다.

 

만약에, ORDER BY에 index로 지정하지 않은 컬럼이 포함되는 경우에도 index를 이용할 수 없습니다.

 

 

 

 

<참조 문서>

http://www.mysqlkorea.com/sub.html?mcode=manual&scode=01&m_no=21436&cat1=7&cat2=0&cat3=0&lang=k 

:::MySQL Korea:::

 

 

 

이 포스트에서는 인덱스를 사용할 때, 자주 겪는 여러가지 이용 팁들을 알아 보았습니다. 다음에는 커버링 인덱스에 대해서 알아보겠습니다.