2021-06-02 MySQL 데이터베이스 인덱스

인덱스란?

찾아보기( 목차)가 있는 책은 찾아보기에 주요 용어가 가나다순, 혹 알파벳순으로 정렬이 되어 있고 용어 옆에 쪽수가 나와 해당 페이지를 펼치면 원하는 내용을 바로 찾아을 수 있다. MySQL에서 인덱스는 바로 이와같은 찾아보기 기능이라고 생각하면된다. 

'데이터를 좀더 빠르게 찾기위한 도구'

 

@인덱스의 문제점 

책에서 모든 단어를 찾아보도록 목차에 모두 표시하면 책의 분량보다 목차의 분량이 많아 지는데 인덱스도 마찮가지로 인덱스가 많아지면 많아 질수록 배보다 배꼽이 더커진다. 

필요없는 인덱스를 만들면 데이터베이스가 차지하는 공간이 커지므로, 인덱스를 이용해 찾는것이 전체를 검색해서 찾는 거보다 오래 걸릴수 있다. 

 

@인덱스의 장단점

장점 검색속도가 매우 빨라진다. 

단점  인덱스 저장할 공간이 필요 ( 대략 데이터베이스 크기에 10% 정도 추가로 필요) 

        처음 인덱스 생성하는데 많은 시간 소요됨 

        데이터의 변경(삽입 , 수정,  삭제 ) 작업이 자주 일어날경우 오히려 성능이 나빠질 수 있음 

 

 

 

인덱스의 종류

@클러스터형 인덱스

영어 사전처럼 책의 내용 자체가 순서대로 정렬된다 .( 원본이 정렬됨 ) 

테이블당 하나만 생성할 수 있음

행 데이터를 인덱스로 지정한 열에 맞춰서 자동으로 정렬한다.

 

#PRIMARY KEY 를 주거나 UNIQUE NOT NULL 을 주어서  설정을 하면 자동으로 생성이 됨 

(PRIMARY KEY 와 UNIQUE NOT NULL 두개가 있을 때엔 PRIMARY KEY가 우선순위 높음) 

 

@보조 인덱스 ( NON_클러스터 인덱스) 

찾아보기에서 먼저 원한는 단어를 찾은후에 그 옆에 표시된 페이지로 이동하여 원하는 내용을 찾는 것과 같음 

테이블당 여러개 생성가능 

원본이 정렬 되는것이 아니라 새로운 페이지를 만들어서 지정된 단어가 정렬되며 인덱스 번호가 생김

보조 인덱스에는 2가지가 있다. 고유 인덱스와 비고유 인덱스 쉽게 이야기하면

 

고유인덱스 출력시 번호느 0 , UNIQUE 속성을 주게 되면 생겨난다고 생각하면되고 

비교유 인덱스 출력시 번호는 1,   아무 제약조건을 설정하지 않으면 생기는 것이다.

 

이거만 생각하면 됨 0은 고유 1은 비고유!!!

 

실습으로 확인 

@테이블 만들고 자동으로 생성된 인덱스 조회하기 

프라이머리키를 달아줘서 a칼럼에 자동으로 생성된 클러스터형 인덱스 

 

@테이블2 만들고 기본키와 unique 제약 조건 설정

기본적으로 키본키가 들어가게되면 클러스터형으로 하나만 생성된다 .

  

@테이블3 만들고 기본키없이 unique 제약 조건만 설정

@테이블 4 만들고 UNIQUE 제약 조건 설정한 열중 하나에 클러스터형 인덱스 생성

@테이블 5 만들고 a열에 UNIQUE 제약 조건에 not null 설정하고 d에 기본키 설정 

 

@클러스터형 인덱스의 정렬 확인하기 

기본키 없이 넣었을때 화면이다 먼저 넣은게 순서대로 들어간걸 볼수 있는데 우리는 인덱스로 자동 정렬이 되는지 확인을 해야 하기 때문에 테이블 삭제후 기본키를 준뒤에 다시 실행 해보겠다 

 

이렇게 내가 따로 정렬을 하지 않았지만 클러스터 인덱스 이기 때문에 자동 정렬이 된걸 볼 수 있다. 

 

@아이디열에 기본키 제거하고 이름열에 기본키로 설정 

이름으로 변경하자마자 이름으로 정렬이 된것을 볼수 있다 ,

 

#이 실습들고 알수 있는 결론 

기본키로 지정한 열에 클러스터형 인덱스가 생성 

UNIQUE NOT NULL로 지정한 열에 클러스터형 인덱스가 생성 

UNIQUE 또는 UNIQUE NULL 로 지정한 열에 보조 인덱스가 생성

기본키와 UNIQUE NOT NULL 이 같이 있으면 기본키로 지정한 열에 우선 클러스터형인덱스 생성

기본키로 지정한 열에 데이터가 오름차순 정렬 

 

인덱스 생성 

#CREATE INDEX 문으로 인덱스를 만들면 보조 인덱시가 생성

#CREATE INDEX 문으로 클러스터형 만들수 없다 만들려면 ALTER TABLE 해야함

#CREATE INDEX 문의 UNIQUE 옵션은 고유한 인덱스를 만들 때 사용

 

인덱스 삭제

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

클러스터형 인덱스를 삭제할때는 인덱스 이름 부분에 PRIMARY를 넣음 

 

실습 

@테이블에 어떤 인덱스인지 확인 

 

@주소열에 단순 보조 인덱스 생성

@출생 연도 열에 고유 보조 인덱스 생성 

이렇게 생성을 해주면 되지만 단!!! 생년월일에 이미 중복된 값이 있으면 오류가 난다. 

 

 

@이름열과 출생연도열을 조합하여 인덱스 생성 

이렇게 인덱스를 2개를 줄수도 있다.