[MySQL] index

특정 컬럼이 특정 값인 데이터를 찾기 위해서 아래와 같은 식을 사용하면 모든 데이터들을 하나씩 순차적으로 찾게 된다.
데이터 행이 100만개라면 100만개의 데이터를 훑어봐야 한다는 단점이 있다.

SELECT * FROM db.table WHERE 특정컬럼 = 특정값;

 

이런 문제점을 해결하기 위해서 index 를 사용한다.
인덱스는 데이터베이스에서 검색 성능을 높여주는 자료 구조이다. 
특정 컬럼에 인덱스를 생성하면 해당 컬럼의 데이터들을 정렬하여 별도의 메모리 공간에 데이터의 물리적 주소와 함께 저장한다.
이렇게 되면 검색할 때 전체 데이터를 검색하는 것이 아니라 인덱스를 검색하여 빠르게 원하는 데이터를 찾을 수 있다.

 

데이터베이스에서는 B+ tree를 이용한 인덱스를 많이 사용한다.

 

• B+ tree는 데이터들 사이에도 연결이 되어있어 범위검색을 할 때 효율적이다.

 

Index의 단점

• index를 생성할 때 마다 추가적인 저장 공간이 필요하다.
• index가 있는 테이블은 삽입/수정/삭제 등을 할 때 index에도 반영해야하므로 느려질 수 있다.

 

---

Index 생성

아래와 같이 도서관 데이터베이스가 존재한다.

특정 등록번호를 가지고 책을 찾는 쿼리문을 작성하는 경우, cost가 6,156.63이며 access_type이 ALL 임을 알 수 있다. (모든 행을 다 본다는 뜻)

 

이번엔 등록번호에 대한 index를 생성하고 다시 검색해본 결과 Cost가 줄어들었음을 확인할 수 있다.

CREATE INDEX 인덱스명 ON 테이블명 (컬럼명);

CREATE INDEX 등록번호 ON index_test.library (등록번호);

 

 

다중컬럼 Index 생성

CREATE INDEX 인덱스명 ON 테이블명 (컬럼명1, 컬럼명2);

 

다중컬럼으로 index를 생성한 경우에는 아래와 같은 쿼리문을 빠르게 실행할 수 있다.

...
WHERE 컬럼명1 = 특정값 AND 컬럼명2 = 특정값;

 

그러나 (컬럼명1, 컬럼명2)의 순서로 인덱스를 만들었을 때 아래와 같이 컬럼명2의 조건만을 사용한다면 index를 사용할 수 없다.

WHERE 컬럼명1 = 특정갑 --이건 가능

WHERE 컬럼명2 = 특정값 --🔥이건 불가능‼🔥

 

Index 삭제

ALTER TABLE 테이블명 DROP INDEX 인덱스명;