[혼자 공부하는 SQL] Chapter 6-3. 인덱스의 실제 사용

<인덱스 생성>

​

CREATE [UNIQUE] INDEX 인덱스_이름

ON 테이블_이름 (열_이름) [ASC | DESC]

​

<인덱스 제거>

​

DROP INDEX 인덱스_이름 ON 테이블_이름

​

<인덱스 생성 실습>

 

1) 인덱스 실습을 위해 인터넷 마켓 데이터베이스를 초기화

 

2) SHOW INDEX 문으로 현재 생성된 인덱스를 검사하면 mem_id에 클러스터형 인덱스 1개만 생성된 모습 확인

 

3) SHOW TABLE STATUS 문을 이용해 인덱스의 크기 확인이 가능

입력된 데이터는 적지만 최소 단위가 1페이지이므로 그에 해당하는 16KB가 할당된 모습을 볼 수 있음

 

4) 이미 클러스터형 인덱스가 있으므로 addr의 중복을 허용하는 단순 보조 인덱스를 생성

그 결과, 새로 생성된 인덱스가 정상적으로 확인되는 것을 알 수 있음

단 이 인덱스는 Non_unique가 1이기 때문에 고유 인덱스가 아니며 중복값이 허용됨

 

5) 하지만 STATUS 문으로 다시 확인하였을 때 인덱스를 생성했음에도 불구하고 Index_length가 0로 출력됨

 

6) 이는 생성된 인덱스를 적용하는 명령인 ANALYZE TABLE 문을 입력하지 않았기 때문으로

해당 명령을 실행하면 정상적으로 인덱스 크기가 출력되는 모습을 알 수 있음

 

7) 이어서 mem_number을 기준으로 중복을 허용하지 않는 고유 보조 인덱스를 생성

하지만 이미 인원수에 중복값이 있기 때문에 오류가 발생

 

8) 반면 중복값이 없는 mem_name을 대상으로 고유 인덱스 생성 시 정상적으로 생성됨

 

9) 하지만 이런 식으로 고유 인덱스를 생성하자 기본 키가 다름에도 불구하고 중복값 때문에 데이터 입력이 불가능

이처럼 고유 보조 인덱스는 업무상 절대로 중복되지 않는 열(주민등록번호, 이메일, 학번 등)에만 사용해야 함

​

<인덱스 활용 실습>

 

1) 인덱스를 사용하지 않고 전체 데이터를 SELECT 할 경우 Full Table Scan, 전체 테이블 검색이 확인

 

2) 반면 WHERE 문을 사용하여 검색 시 Single Row, 인덱스를 사용하여 검색했다는 것이 확인

 

3) 만약 단순 보조 인덱스를 생성한다면 숫자를 조건으로 검색할 경우에도

Index Range Scan, 인덱스를 통한 검색이 이루어지는 것을 알 수 있음

​

<인덱스를 사용하지 않을 때>

 

1) 하지만 위 코드처럼 인덱스가 있더라도 인덱스를 사용하지 않는 경우가 있는데

이는 MySQL이 인덱스 검색보다 전체 테이블 검색이 낫다고 판단했기 때문

위 코드는 인원수가 1 이상인 데이터를 찾는 것이므로 사실상 모든 데이터를 출력해야함

 

2) 또한 위 코드처럼 WHERE 문에 연산이 가해지면 인덱스가 사용되지 않음

 

3) 이를 방지하기 위해선 WHERE 문이 아닌 부분에 연산을 가해주면 인덱스가 사용됨

​

<인덱스 제거 실습>

 

1) 우선 SHOW INDEX 명령을 통해 현재 존재하는 인덱스를 파악

 

2) 클러스터형 인덱스를 먼저 삭제할 경우 시간이 더 오래 걸리기 때문에 보조 인덱스부터 삭제하는 것이 좋음

따라서 Key_name이 PRIMARY가 아닌 세 인덱스를 우선 삭제

 

3) 기본 키는 DROP INDEX가 아닌 ALTER TABLE 문에서 삭제 가능

하지만 현재 mem_id 열을 외래 키가 참조하고 있기 때문에 기본 키 삭제가 불가능

 

4) 위 코드를 입력하여 외래 키의 이름 buy_ibfk_1 검색

 

5) 외래 키 삭제 후 기본 키 삭제 시 클러스터형 인덱스가 정상적으로 제거됨