[MySQL] INDEX

 

✔️ INDEX

• 테이블에서 원하는 많은 양의 데이터를 쉽고 빠르게 찾기 위해 사용합니다.
• 지정한 컬럼들을 기준으로 메모리 영역에 일종의 목차를 생성하는 것입니다.
• INSERT, UPDATE, DELETE의 성능을 희생하고 SELECT의 성능을 향상시킵니다.
• (UPDATE, DELETE를 하기 위해 데이터를 조회할 땐 인덱스를 통해 빠르게 처리됩니다.)

 

📌 INDEX를 사용하는 이유

• SQL문을 변경하지 않고 성능을 개선할 수 있습니다.
• 테이블의 데이터에 영향을 주지 않습니다.

 

📌 INDEX를 만드는 기준

• 조회 시 자주 사용하는 크기가 큰 테이블만 만듭니다.
  • 크기가 작은 테이블은 큰 차이가 없습니다.
• PRIMARY KEY나 UNIQUE 제약조건이 부여된 컬럼에는 불필요합니다.
  • PK나 UNIQUE가 설정된 컬럼은 자동으로 인덱스가 생성됩니다.
• Cardinality가 높은 컬럼에 만듭니다.
  • 값의 분산도(종류)가 높을 수록 인덱스의 효과가 상승합니다.
• 컬럼 값에 NULL이 적은 컬럼에 만듭니다.
  • NULL이 지나치게 많으면 효율이 떨어집니다.

 

📌 INDEX 생성

-- 단일 컬럼 인덱스
CREATE (UNIQUE) INDEX [인덱스이름]
ON [테이블이름(컬럼이름 ASC or DESC)];

-- 다중 컬럼 인덱스
CREATE (UNIQUE) INDEX [인덱스이름]
ON [테이블이름(컬럼이름, 컬럼이름, ...)];

• UNIQUE 를 통해 UNIQUE INDEX를 설정할 수 있습니다.
• 인덱스 생성 시점에 컬럼의 정렬방식을 설정할 수 있습니다.
• 다중 컬럼은 최대 15개까지 구성 가능합니다.
• 다중 컬럼 인덱스는 단일 컬럼 인덱스보다 비효율적으로 INSERT, UPDATE, DELETE를 수행합니다.

 

📌 INDEX 조회

SHOW INDEX
FROM [테이블이름];

• Table : 테이블의 이름
• Non_unique : 인덱스가 중복된 값을 저장할 수 있으면 1, 없으면 0
• Key_name : 인덱스의 이름을 표시하며, 인덱스가 해당 테이블의 기본 키라면 PRIMARY 표시
• Seq_in_index : 인덱스에서의 해당 컬럼의 순서를 표시
• Column_name : 해당 컬럼의 이름을 표시
• Collation : 인덱스에서 해당 필드가 정렬되는 방법을 표시
• Cardinality : 인덱스에 저장된 유일한 값들의 수
• Sub_part : 인덱스 접두어를 표시
• Packed : 키가 압축되는 방법을 표시
• Null : 해당 컬럼이 NULLABLE하면 YES, 아니면 공백
• Index_type : 인덱스에 사용되는 메서드
• Comment : 해당 컬럼을 설명하는 것이 아닌 인덱스에 관한 기타 정보
• Index_comment : 인덱스에 관한 모든 기타 정보

 

📌 INDEX 추가

ALTER TABLE [테이블이름]
ADD (UNIQUE or FULLTEXT) INDEX [인덱스이름(컬럼이름, 컬럼이름, ...)];

• UNIQUE 를 통해 UNIQUE INDEX를 추가할 수 있습니다.
• FULLTEXT INDEX는 테이블의 모든 텍스트 필드를 검색합니다.
  • 자연어를 이용하여 데이터를 검색할 수 있도록 모든 데이터의 문자열 단어를 저장합니다.

 

📌 INDEX 삭제

-- ALTER문으로 인덱스 삭제
ALTER TABLE [테이블이름]
DROP INDEX [인덱스이름];

-- DROP문으로 인덱스 삭제
DROP INDEX [인덱스이름]
ON [테이블이름];

 

📌 INDEX 타는지 확인하기

EXPLAIN
SELECT ~

• 상단에 EXPLAIN을 입력하면 어떤 방식으로 조회하는지 보여줍니다.
• type이 ALL인 경우엔 INDEX를 타지 않았다는 것입니다.
• type이 ref인 경우엔 possible_keys를 통해 어느 인덱스를 타는지 확인할 수 있습니다.