[SQL] 06-3 인덱스의 실제 사용

기본 개념

1. 인덱스를 생성하기 위해서는 CREATE INDEX 문을 사용함.
   1) 형식

   CREATE [UNIQUE] INDEX 인덱스 이름
   ON 테이블 이름 (열 이름) [ASC | DESC]

2. 인덱스를 제거하기 위해서는 DROP INDEX 문을 사용함.
   1) 형식

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

인덱스 생성과 제거 문법

인덱스 생성 문법

1. 직접 인덱스를 생성하려면 CREATE INDEX 문을 사용해야 함.

2. UNIQUE는 중복이 안 되는 고유 인덱스를 만드는 것이며, 생략 시 중복이 허용됨.
   1) CREATE UNIQUE로 인덱스를 생성하려면, 기존에 입력된 값들에 중복이 있으면 안됨.

인덱스 제거 문법

1. CREATE INDEX로 생성한 인덱스는 DROP INDEX로 제거함.

2. 기본 키, 고유 키로 자동 생성된 인덱스는 DROP INDEX로 제거하지 못함.

3. ALTER TABLE 문으로 기본 키나 고유 키를 제거하면, 자동으로 생성된 인덱스도 제거할 수 있음.

인덱스 생성과 제거 실습

인덱스 생성 실습

1. 인덱스 실습을 위해 인터넷 마켓 데이터베이스를 생성하기
   

2. SHOW INDEX 문으로 member에 어떤 인덱스가 설정되어 있는지 확인하기
   
   1) 현재 member에는 mem_id 열에 클러스터형 인덱스 1개만 설정되어 있음.

3. SHOW TABLE STATUS 문을 사용하여 인덱스의 크기를 확인하기
   1) 결과 중에 Data_length는 클러스터형 인덱스의 크기를 Byte 단위로 표기한 것임.
   
   2) MySQL의 1페이지 크기는 기본적으로 16KB이므로 클러스터형 인덱스는 16384/(16*1024)=1페이지가 할당되어 있는 것임.
   3) Index_length는 보조 인덱스의 크기인데 member는 보조 인덱스가 없기 때문에 표기되지 않음.

4. 주소(addr)에 중복을 허용하는 단순 보조 인덱스를 생성하기
   1) 인덱스 이름을 idx_member_addr로 지정함.
   
   2) 단순 보조 인덱스는 고유 보조 인덱스와 다르게, 중복이 허용됨.

5. 새로 생성된 인덱스 확인하기
   
   1) Key_name에서 지금 생성한 단순 보조 인덱스의 이름이 확인됨.
   2) Column_name에서는 어느 열에 지정되었는지 확인됨.
   3) Non_unique가 1로 설정되어 있으므로 고유 보조 인덱스가 아니라는 것을 알 수 있으며, 이는 곧 중복된 데이터를 허용한다는 말임.

6. 전체 인덱스의 크기를 다시 확인하기
   
   1) Index_length 부분이 보조 인덱스의 크기인데, 크기가 0으로 나옴.
   2) 바로 앞에서 보조 인덱스 idx_member_addr이 생성된 것을 확인했는데, 생성된 인덱스를 실제로 적용시키려면 ANALYZE TABLE 문으로 먼저 테이블을 분석/처리 해주어야 함.

7. 보조 인덱스가 생성된 것을 다시 확인하기
   

8. 인원수(mem_number)에 중복을 허용하지 않는 고유 보조 인덱스를 생성하기
   
   1) 블랙핑크, 마마무, 레드벨벳의 인원 수가 4이기에 이미 중복된 값이 있음. 따라서, 인원수 열에는 고유 보조 인덱스를 생성할 수 없음.

9. 회원 이름(mem_name)에 고유 보조 인덱스 생성하기
   

10. 고유 보조 인덱스가 잘 만들어졌는지 확인하기
    
    1) Non-unique가 0이라는 것은 중복을 허용하지 않는다는 의미이므로, 고유 보조 인덱스가 잘 생성된 것임.

11. 마마무와 이름이 같은 태국의 가수 그룹이 회원가입을 하려고 함.
    1) 기본 키만 다르면 되므로 MOO로 지정함.
    2) 그러나, 오류가 발생하는데 그 이유는 조금 전 생성한 고유 보조 인덱스로 인해서 중복된 값을 입력할 수 없기 때문임.
    

인덱스의 활용 실습

1. 지금까지 만든 인덱스가 어느 열에 있는지 확인하기
   
   1) 현재 회원 아이디(mem_id), 회원 이름(mem_name), 주소(addr) 열에 인덱스가 생성되어 있음.

2. 전체 조회하기
   

3. 인덱스를 사용했는지 여부는 결과 중 Execution Plan 창을 확인하면 됨.
   
   1) 전체 테이블 검색(Fulll Table Scan)을 한 것이 확인됨.

4. 이번에는 인덱스가 있는 열을 조회하기
   
   
   1) 열 이름이 SELECT 다음에 나와도 인덱스를 사용하지 않음.

5. 인덱스가 생성된 mem_name 값이 '에이핑크'인 행을 조회하기
   
   
   1) single row(constant)라고 되어 있으며 이는 인덱스를 사용해서 결과를 얻었다는 의미임.
   2) WHERE 절에 열 이름이 들어있어야 인덱스를 사용함.

6. 숫자의 범위로 조회하기
   1) 숫자로 구성된 인원수(mem_number)로 단순 보조 인덱스 만들기
   

7. 인원수가 7명 이상인 그룹의 이름과 인우너수 조회하기
   
   
   1) 인덱스를 사용한 것 확인 가능

인덱스를 사용하지 않을 때

1. 인덱스가 있고 WHERE 절에 열 이름이 나와도 인덱스를 사용하지 않는 경우가 있음. 인원수가 1명 이상인 회원 조회하기
   
   
   1) 앞에서 7명 이상일 때는 인덱스를 사용했는데, 1명 이상으로 설정하니 전체 테이블 검색을 하였음을 알 수 있음.
   2) 이는 대부분의 행을 가져와야 하므로 인덱스를 왔다갔다 하는 것 보다는 테이블을 차례대로 읽는 것이 효율적이기에, MySQL이 인덱스 검색보다 전체 테이블 검색이 낫겠다고 판단했기 때문임.

2. 인원수의 2배를 하면 14명 이상이 되는 회원의 이름과 인원수 검색하기
   
   
   1) 이 경우에도 전체 테이블 검색을 진행함.
   2) WHERE 문에서 열에 연산이 가해지면 인덱스를 사용하지 않음.

3. 아래와 같이 수정하면, 인덱스를 사용하면서 동일한 결과를 도출해낼 수 있음.
   
   

인덱스 제거 실습

1. 지금까지 사용한 인덱스 제거하기
   1) 먼저 인덱스 이름 확인하기
   
   2) 클러스터형 인덱스와 보조 인덱스가 섞여있을 때는 보조 인덱스를 먼저 제거하는 것이 좋고, 보조 인덱스끼리는 어떤 것을 먼저 제거해도 좋음.
   
   3) 클러스터형 인덱스(Primary Key에 설정된 인덱스)는 DROP INDEX 문으로 제거되지 않고, ALTER TABLE 문으로만 제거 가능함.
   4) 그러나, 기본 키를 제거하기 전에 외래 키 관계를 제거해야 함. 우선, 외래 키의 이름을 알아내기
   
   5) 외래 키를 먼저 제거하고, 기본 키 제거하기
   

2. 인덱스를 제거한다고 데이터의 내용이 바뀌는 것은 아님.
   1) 지금 인덱스를 제거한 것은 찾아보기를 제거하고 영어사전을 순서가 섞인 단어장으로 변경한 것이며, 내용은 그대로임.

인덱스를 효과적으로 사용하는 방법

1. 인덱스는 열 단위에 생성됨.
2. WHERE 절에서 사용되는 열에 인덱스를 만들어야 함.
3. WHERE 절에 사용되더라도 자주 사용해야 가치가 있음.
4. 데이터의 중복이 높은 열은 인덱스를 만들어도 별 효과가 없음.
5. 클러스터형 인덱스는 테이블당 하나만 생성할 수 있음.
6. 사용하지 않는 인덱스는 제거함.