[MySQL] 인덱스(INDEX)에 대해 알아보자! 🧐

1. 인덱스(INDEX)란❓

• 테이블의 동작속도(조회)를 높여주는 자료구조
• 데이터의 위치를 빠르게 찾아주는 역할

 

1-1. 인덱스의 특징

• MYI(MySQL Index)파일에 저장
• 인덱스를 설정하지 않으면 Table Full Scan이 일어나 성능이 저하되거나 장애가 발생할 수 있음
• 조회(select)속도는 빨라지지만 update, insert, delete의 속도는 저하될 수 있음
• [MySQL]에서는 Primary key, unique 제약조건을 사용하면 해당 컬럼에 index가 적용됨
• 인덱스는 하나 또는 여러개의 컬럼에 설정할 수 있음
• where절을 사용하지 않고 인덱스가 걸린 컬럼을 조회하면 성능에 아무런 효과가 없음
• 가급적 update가 안되는 값을 설정하는 것이 좋음

 

1-2. 인덱스에 대한 성능 효과가 없는 경우(order by, group by)

• order by 인덱스컬럼, 일반컬럼: 복수의 키에 대해 order by를 사용한 경우
• where 일반컬럼 = '값' order by 인덱스컬럼: 연속하지 않은 컬럼에 대해 order by를 실행한 경우
• order by 인덱스컬럼1 desc, 인덱스컬럼2 asc: desc와 asc를 혼합해서 사용한 경우
• group by 일반컬럼1 order by 인덱스컬럼: group by와 order by의 컬럼이 다른 경우
• order by 함수(인덱스컬럼): order by절에 컬럼이 아닌 다른 표현을 사용한 경우

 

2. 인덱스의  조회, 생성, 삭제, 추가

 

2-1. 인덱스 조회

show index from 테이블명;

 

✅member 테이블에서 index 확인하기

show index from member;

primary key는 index 자동 적용

---

2-2. 인덱스 생성

create index 인덱스명 on 테이블명(필드명)
create index 인덱스명 on 테이블명(필드명1, 필드명2, ...)   --다중컬럼에 인덱스 설정

✅ member 테이블에서 hp컬럼의 인덱스를 생성해보자.

create index idx_hp on member(hp);

show index from member;

idx_hp 생성

 

✔ UNIQUE INDEX 생성

• UNIQUE INDEX는 중복 값을 허용하지 않는 인덱스

CREATE UNIQUE INDEX 인덱스이름
ON 테이블이름 (필드이름1, 필드이름2, ...)

 

---

2-3. 인덱스 삭제

alter table 테이블명 drop index 인덱스명;

✅ idx_hp를 삭제해보자.

alter table member drop index idx_hp;

show index from member;

idx_hp 삭제됨

 

---

2-4. 인덱스 추가

• 기본 인덱스에서 필드의 값은 같은 값이 여러 번 저장될 수 있으며, NULL 값을 가질 수도 있음

ALTER TABLE 테이블이름
ADD INDEX 인덱스이름 (필드이름)

✅ member테이블에 username을 이용한 인덱스를 추가해보자.

alter table member add index idx_name (username);

show index from member;