[DB 14편] MySQL Index Hints 정리

목차는 DB 목차 에 있습니다.

[DB 14편] MySQL Index Hints 정리

예시에 사용된 DDL, DML

	# 예시에 사용된 테이블 DDL, DML
	- TEAM : TEAM_MEMBER = 1 : n
	- TEAM_MEMBER : MEMBER_ADDR = 1 : 1
	# DDL
	CREATE TABLE `team` (
	`team_id` bigint NOT NULL AUTO_INCREMENT,
	`team_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
	PRIMARY KEY (`team_id`)
	) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
	CREATE TABLE `team_member` (
	`team_member_id` bigint NOT NULL AUTO_INCREMENT,
	`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
	`team_id` bigint NOT NULL,
	PRIMARY KEY (`team_member_id`),
	KEY `FK9ubp79ei4tv4crd0r9n7u5i6e` (`team_id`),
	KEY `index3` (`name`) USING BTREE
	) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
	CREATE TABLE `member_addr` (
	`addr_id` bigint NOT NULL,
	`zip_code` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
	`addr_detail` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
	`member_id` bigint NOT NULL,
	PRIMARY KEY (`addr_id`)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
	# DML
	INSERT INTO team (`team_id`,`team_name`) VALUES (1,'test-team');
	INSERT INTO team (`team_id`,`team_name`) VALUES (2,'test-team-2');
	INSERT INTO team_member (`team_member_id`,`name`,`team_id`) VALUES (1,'member-1',1);
	INSERT INTO team_member (`team_member_id`,`name`,`team_id`) VALUES (2,'member-2',2);
	INSERT INTO team_member (`team_member_id`,`name`,`team_id`) VALUES (3,'member-3',1);
	INSERT INTO team_member (`team_member_id`,`name`,`team_id`) VALUES (4,'member-4',2);
	INSERT INTO team_member (`team_member_id`,`name`,`team_id`) VALUES (5,'member-5',2);
	INSERT INTO `member_addr` (`addr_id`, `zip_code`, `addr_detail`, `member_id`) VALUES ('1', '123', 'detail-1', '1');
	INSERT INTO `member_addr` (`addr_id`, `zip_code`, `addr_detail`, `member_id`) VALUES ('2', '234', 'detail-2', '2');
	INSERT INTO `member_addr` (`addr_id`, `zip_code`, `addr_detail`, `member_id`) VALUES ('3', '456', 'detail-3', '3');
	INSERT INTO `member_addr` (`addr_id`, `zip_code`, `addr_detail`, `member_id`) VALUES ('4', '567', 'detail-4', '4');
	INSERT INTO `member_addr` (`addr_id`, `zip_code`, `addr_detail`, `member_id`) VALUES ('5', '678', 'detail-5', '5');

view raw MySQL-Hint-example.sql hosted with ❤ by GitHub

MySQL Index Hints

• 인덱스 힌트는 옵티마이저에게 인덱스를 어떻게 선택해야하는지에 대한 정보를 주는 옵션입니다.

IGNORE

• 해당 인덱스를 사용하지 않도록 옵티마이저에게 정보를 전달합니다.

IGNORE INDEX 예시 쿼리

# 힌트 사용 전
explain format = tree
select tm.*
from team t, team_member tm
where tm.team_id = t.team_id
and tm.name = 'member-1'
and t.team_id = '1'
;

# 힌트 사용
explain format = tree
select tm.*
from team t IGNORE INDEX(PRIMARY), team_member tm
where tm.team_id = t.team_id
and tm.name = 'member-1'
and t.team_id = '1'
;

IGNORE INDEX 예시 실행 계획

• 힌트 사용 전에는 TEAM 테이블의 기본키를 통해 조회를 했지만, 힌트 적용 후에는 테이블을 full scan 했습니다.

[힌트 사용 전]
2. -> Filter: (tm.team_id = 1)  (cost=0.29 rows=0)
1.    -> Index lookup on tm using index3 (name='member-1')  (cost=0.29 rows=1)


[힌트 사용]
5. -> Inner hash join (no condition)  (cost=0.68 rows=0)
    4. -> Filter: (t.team_id = 1)  (cost=1.13 rows=1)
       3. -> Table scan on t  (cost=1.13 rows=2)
    -> Hash
       2.  -> Filter: (tm.team_id = 1)  (cost=0.29 rows=0)
          1.  -> Index lookup on tm using index3 (name='member-1')  (cost=0.29 rows=1)

FORCE

• USE INDEX Hints 와 유사하게 동작하지만 옵티마이저에게 추가 정보를 전달합니다. (테이블 스캔은 매우 비용이 크다는 것을)

사용 예시

explain format = tree
select tm.*
from team t, team_member tm FORCE INDEX(index3)
where tm.team_id = t.team_id
and tm.name = 'member-1'
and t.team_name = 'team-name'
;

USE INDEX

• 인덱스를 사용하도록 MySQL 에게 정보를 전달합니다.

사용 예시

explain format = tree
select tm.*
from team t, team_member tm USE INDEX(index3)
where tm.team_id = t.team_id
and tm.name = 'member-1'
and t.team_name = 'team-name'
;

Reference

• https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html