MySQL 인덱스 조회 생성 수정 삭제 및 타는지 확인방법

MySQL에서 인덱스는 테이블을 빨리 조회하기 위해

테이블 데이터에 포인터를 주는 것인데

인덱스는 책의 목차와도 같아서 특정 컬럼에 인덱스를 지정해주면

테이블 조회 시 인덱스를 이용해 빠르게 조회할 수 있게 된다

 

하지만 그렇다고 인덱스를 도배하면

금방 찾을거 인덱스를 사용해 찾느라 더 느려지게 되고

컬럼 값에 NULL이 많이 들어가는 경우에도

인덱스를 사용하면 오히려 역효과가 난다

 

대체적으로 INDEX는 테이블에

데이터 양이 엄청나게 많을 경우 주로 사용하는데

여기서 지나치게 많은 INDEX를 지정하거나

NULL이 많은 컬럼, 삽입 수정이 자주 이루어지는 테이블에는

INDEX 사용을 지양하는 것이 좋다

 

인덱스에 관한 자세한 내용은

아래 링크의 글을 확인해주면 되고

SQL에서 인덱스가 필요한 이유

이제 인덱스 조회부터 추가 수정 삭제방법은 다음과 같은데

 

인덱스 조회

SHOW INDEX FROM 테이블명;

 

인덱스 생성

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

ALTER TABLE 테이블명 ADD INDEX 인덱스명(컬럼명)

ㄴ 둘 중에 하나 택일

 

인덱스 삭제

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

 

인덱스 수정의 경우에는

삭제한 후에 다시 생성해주면 된다

 

이후 인덱스를 생성한 후 실제 쿼리가

인덱스를 타는지 확인하는 방법은 EXPLAIN을 활용해주면 되는데

 

쿼리 입력 후 상단에 EXPLAIN을 입력해준 후 조회해주면

어떤 방식으로 쿼리를 조회하는지 보여주는데

 

type가 ALL일 경우에는 INDEX를 사용하지 않고 그냥 돌지만

type가 ref인 상태에서 possible_keys를 보면

해당 쿼리가 어느 인덱스를 타는지 확인이 가능하다

 

인덱스는 무조건 타는 게 아니라

DB가 봤을 때 특정 인덱스를 타는게 빠르겠다 싶으면

작성된 인덱스를 가져가 사용하게 된다

 

인덱스를 작성한 후 쿼리가 인덱스를 타는지 확인하려면

EXPLAIN을 쿼리 위에 사용해주면 된다

 

마지막으로 예제에 사용한 쿼리는 아래와 같다

 

-- 인덱스 타는지 확인 (Type가 ALL이고 possible keys가 NULL이면 안탐)
EXPLAIN
SELECT * FROM TB1
 WHERE NAME = '철수';

-- 테이블 인덱스 확인
SHOW INDEX FROM TB1;

-- 인덱스 생성
CREATE INDEX 인덱스명 ON 테이블명(컬럼명);
ALTER TABLE 테이블명 ADD INDEX 인덱스명(컬럼명);

-- 인덱스 삭제 (수정 시에는 DROP 후 재생성)
ALTER TABLE 테이블명 DROP INDEX 인덱스명;