[혼공 SQL]인덱스의 실제 사용

인덱스를 생성하기 위해서는 CREATE INDEX 문을 사용하고, 제거하기 위해서는 DROP INDEX 문을 사용합니다.

 

인덱스 생성

CREATE [UNIQUE]  INDEX 인덱스_이름
          ON 테이블_이름 (열_이름) [ASC | DESC]

기본은 ASC로 만들어지며, DESC로 만드는 경우는 거의 없습니다.

인덱스 제거

DROP INDEX 인덱스_이름 ON 테이블_이름

 

인덱스 생성과 제거 문법

 

인덱스 생성 문법

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_mem_name
     [index_type]
     ON tbl_mem_name (key_part, ...)
     [index_option]
     [algorithm_option | lock_option] ...

key_part: {col_mem_name [(length)] | (expr)} [ASC | DESC]

index_option:
   KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_mem_name
| COMMENT 'string'
| {VISIBLE | INVISIBLE}

index_type:
  USING {BTREE | HASH}

algorithm_option:
   ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
  LOCK [=] {DEFAULT  | NONE | SHARED | EXCLUSIVE}

 

인덱스 생성과 제거 실습

인덱스를 생성한 후에 ANALYZE TABLE 문을 실행해줘야 실제로 적용됩니다. 

 

고유 보조 인덱스가 중복된 값으로 인해 생성할 수 없다.

회원 이름(member_name)에 고유 보조 인덱스가 생성된다.

조금 전에 생성한 고유 보조 인덱스로 인해서 중복된 값을 입력할 수 없기 때문이다.

 

이렇게 이름이 중복된다고 회원가입이 안 된다면, 실제 '인터넷 마켓'에서는 심각한 문제가 발생할 수 있습니다. 그러므로 고유 보조 인덱스를 지정할 때 현재 중복된 값이 없다고 무조건 설정하면 안 되며, 업무상 절대로 중복되지 않는 열(주민등록번호, 학번, 이메일 주소 등)에만 UNIQUE 옵션을 사용해서 인덱스를 생성해야 합니다.

 

인덱스의 활용 실습

현재 회원 아이디(mem_id), 회원 이름(mem_name), 주소(addr) 열에 인덱스가 생성되어 있습니다.

 

WHERE 절에 열 이름이 들어 있어야 인덱스를 사용합니다. 

[Execution Plan] 창에 Full table Scan을 제외하고, 나머지는 모두 인덱스를 사용했다는 의미입니다. 인덱스를 사용하는 방법이 여러 개라서 다양한 용어로 표현됩니다. 

 

숫자의 범위를 조회하는 것도 인덱스를 사용합니다. 

 

인덱스를 사용하지 않을 때

인덱스가 있고WHERE 절에 열 이름이 나와도 인덱스를 사용하지 않는 경우가 있습니다.

인덱스가 있어도 MySQL이 판단해서 사용하지 않을 수 있습니다. 

 

또, WHERE절의 열에 연산을 하면 인덱스를 사용하지 않습니다.

 

인덱스 제거 실습

클러스터형 인덱스와 보조 인덱스가 섞여 있을 때는 보조 인덱스를 먼저 제거하는 것이 좋습니다. 보조 인덱스는 어떤 것을 먼저 제거해도 상관없습니다.

Primary Key에 설정된 인덱스는 DROP INDEX 문으로 제거되지 않고 ALTER TABLE 문으로만 제거할 수 있습니다. 

기본 키를 제거하기 전에 외래 키 관계를 제거해야 합니다. 외래 키 이름을 찾고 외래 키를 제거, 기본 키를 제거할 수 있습니다.

 

<<<인덱스를 효과적으로 사용하는 방법>>>

 

인덱스는 열 단위에 생성됩니다. 

WHERE 절에서 사용되는 열에 인덱스를 만들어야 합니다. 

WHERE 절에 사용되더라도 자주 사용해야 가치가 있습니다.

데이터의 중복이 높은 열은 인덱스를 만들어도 별 효과가 없습니다. 

클러스터형 인덱스는 테이블당 하나만 생성할 수 있습니다.

사용하지 않는 인덱스는 제거합니다.