MYSQL에서 INDEX를 추가, 삭제, 확인하기(+성능개선)

작성일 | In ,

MYSQL에서 INDEX를 추가, 삭제, 확인하기

INDEX는 책의 목차라고들 표현한다. 내가 찾는 내용이 어디에 있는지 확인하기 위해서 책 전체를 뒤질 필요 없이, 목차에서 이쯤에 있겠다는 걸 확인한 후 찾기 시작하면 속도는 훨씬 빨라진다. 그래서 쿼리 속도 개선을 위해 테이블마다 자주 사용되는 컬럼 몇 개 정도는 인덱스를 걸어두는 것이 좋다. 이번에 INDEX 사용법을 찾게 된 것도 고객사에서 리스트 조회가 너무 오래 걸린다는 연락이 와 쿼리 튜닝을 위해 점검하던 중 인덱스가 걸려있지 않다는 걸 알게 되어서다.

1. 테이블 인덱스 확인하기

SHOW INDEX FROM 테이블명;

2. 테이블 인덱스 추가하기
인덱스, 유니크 인덱스 모두 컬럼을 여러개 추가 할 수 있다. 하나도 가능함.

ALTER TABLE 테이블명 ADD INDEX 인덱스이름 (컬럼1, 컬럼2...);

ALTER TABLE 테이블명 ADD UNIQUE INDEX 인덱스이름 (컬럼1, 컬럼2...);

3. 테이블 인덱스 삭제하기

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

인덱스 성능 향상을 위한 팁

1. WHERE절 좌변을 가공하지 말 것

1
SELECT * FROM 테이블 WHERE A = 1

회사에서 개발중인 소프트웨어 중 조건절이 DATE_FORMAT(regdt, '%Y%m%d') > '20220426' 이런 식으로 되어 있는 쿼리가 있었다. 팀장님이 그걸 보시고 WHERE절 왼쪽에 함수를 쓰면 성능이 떨어져서 권장하지 않는다고 하셨는데, 마침 보고 있던 INDEX 사용 팁에도 같은 내용이 있었다. WHERE절의 좌변을 가공할 경우 인덱스를 이용한 빠른 검색이 불가능해진다. (우변에 함수를 쓰거나 가공을 하는 건 괜찮지만, 관습적으로 쿼리를 쓸 때 컬럼은 좌측에 쓴다.) 중요한 건 위치가 아니라 컬럼을 가공하지 않아야 한다는 것.
쉽게 비유하여 설명하자면 책에서 목차를 보고 내용을 찾아가야 하는데, 목차를 다른 파일에 따로 보관해둔 상태인 거다. 목차를 바로 확인할 수 없으니 결국 책의 내용을 다 살펴보고 찾아야 하는 것. 좌변을 가공하는 것과 가공하지 않는 것은 row 수가 많아졌을 때 아주 큰 성능 차이를 보인다.

2. 인덱스의 순서를 지킬 것
※ mysql에 해당 되는 내용이다(복합 인덱스가 사용 가능한 환경이어야 하기 때문).
STUDENT 테이블에 NAME, AGE, REGDT… 등의 컬럼이 있다고 가정하고 AGE, NAME 순서로 인덱스를 걸었다고 하자. 이 상태에서 NAME을 이용해 데이터를 조회하면 어떻게 될까?

1
SELECT * FROM STUDENT WHERE NAME = '김철수';

좌변을 가공하지 않았는데도 인덱스를 활용하지 못하고 전체 스캔을 하게 된다. 다시 책으로 예를 들어보자. AGE 목차를 따라 내용을 확인해도, 다른 부분에서 NAME이 김철수인 부분이 존재할 수 있기 때문에 결국 책 전체를 살펴보아야 한다. 이런 문제가 발생할 경우 NAME 기준의 인덱스를 다시 만드는 게 가장 좋지만, 당장 그러기 어려울 경우 강제로 AGE 컬럼을 조건절에 넣으면 된다.(물론 언제까지 이렇게 사용할 수는 없음).

1
SELECT * FROM STUDENT WHERE AGE IN(17,18,19) AND NAME = '김철수';

AGE의 타입이 적을 때나 가능한 방법 같다. 그리고 INDEX가 REGDT, NAME 순으로 걸려있었다면 사용하기 어려운 방법이었을 것이다. 처음부터 INDEX를 만들 때 잘 생각해서 만들자.