[MySQL] Index, View ( 인덱스, 뷰 )
* Index란?
- 책의 '찾아보기', '색인'과 같이 원하는 내용을 바로 찾을 수 있도록 지원해주는 자료구조
- 테이블의 데이터 조회 시 동작 속도를 높여주는 효과를 갖고 있음.
- 즉, 데이터의 위치를 빠르게 찾도록 찾아주는 역할을 하며 컬럼의 값과 레코드가 저장된 주소를 키와 쌍으로 만들어둠.
* 주의 사항
1. 필요없는 Index를 만들면 데이터베이스에 쓸데없는 공간을 차지하며, 오히려 검색 속도가 저하될 수 있음.
2. 데이터베이스의 공간을 차지하므로 추가적인 공간을 차지함
3. 처음 Index를 생성하는 데 시간이 소요됨
4. 데이터의 변경 작업( INSERT, UPDATE, DELETE )이 자주 일어나는 경우, 오히려 성능 저하가 일어날 수 있음.
*인덱스의 종류
1. 클러스터형 인덱스 ( Clustered Index )
- 특정 나열된 데이터들을 일정 기준으로 정렬해주는 인덱스 ( 데이터 자체가 정렬해주는 기준이 된다는 의미 )
- 클러스터형 인덱스는 생성시 데이터 페이지 전체가 다시 정렬됨
>> 이미 대용량의 데이터가 입력된 상태라면 클러스터형 인덱스 생성시 심한 부하가 발생할 수 있음
- 테이블당 하나만 생성이 가능하며 어느 Column에 지정하느냐에 따라 성능이 바뀜
- 보조 인덱스보다 검색 속도는 빠를 수 있지만, 입력/ 수정/ 삭제는 더 느림
- MySQL의 경우 Primary Key가 있다면 Primary Key를, PK가 없다면 Unique하면서 Not Null인 Column을, 그것도 없다면
임의로 하나의 Column을 선정해 클러스터형 인덱스로 선정함.
2. 보조 인덱스 ( Secondary or Non - Clustered Index )
- 개념적으로 후보키에만 부여 가능한 Index
(후보키 : 주민번호와 같이 각 데이터를 인식할 수 있는 최소한의 고유 식별 속성의 집합 )
- 보조 인덱스 생성시 데이터 페이지는 그냥 둔 상태에서 별도의 페이지에 인덱스를 구성 ( 자동 정렬 X )
- 데이터가 위치하는 주소값(RID)를 저장
- 클러스터형 인덱스보다 검색은 느리지만, 입력/수정/삭제는 더 빠름
- 보조 인덱스는 테이블 당 여러개 생성 가능 ( 너무 많으면 오히려 성능 저하 )
| 클러스터형 인덱스 | 보조 인덱스 | |
| 개수 | 테이블 당 1개 | 테이블 하나에 여러개 존재 가능 (필요 없는 인덱스가 여러개 존재하면 오히려 성능 저하 가능성 상승) |
| 선정 기준 | PK -> Unique+Not Null -> 임의의 키 | 개념적으로 후보키에 부여 가능 |
| 테이블 자동 정렬 | 클러스터형 인덱스 기준으로 자동 정렬 | 자동 정렬 X |
| 속도 | 검색속도 향상, 입력/수정/삭제 하향 | 검색속도 하향, 입력/수정/삭제 상향 |
* 제약 조건에 따른 Index 결정
1) 특정 테이블에 Primary Key가 존재하며 Unique Key 또한 존재하는 경우
- Primary Key로 지정된 컬럼은 클러스터형 인덱스로 자동 지정
- Unique Key로 지정된 컬럼들은 보조 인덱스로 지정
- Unique Key가 Not Null이건 Null이건 전부 보조 인덱스가 됨.
2) 특정 테이블에 Primary Key가 존재하지 않으며 Unique + Not Null인 특정 컬럼이 있는 경우
- 해당 컬럼이 클러스터형 인덱스가 됨
- Not Null 제약조건이 없다면 보조 인덱스가 됨
* Index의 생성 전략
1. 인덱스는 열단위로 생성한다.
2. Where 절에서 사용되는 열에 생성한다.
3. Where 절에서 사용되더라도, 자주 사용되는 열에 지정한다.
4. 데이터 중복도가 높은 열에 Index를 설정하면 유의미한 성능 향상을 보기 힘들다.
5. 외래키를 설정한 열에는 자동으로 외래키 인덱스가 생성된다.
6. 조인에 자주 사용되는 열에 지정해주는 것이 좋다.
7. 데이터 변경( 입력, 수정, 삭제 )이 얼마나 자주 일어나는지를 파악 후에 선정해야 한다.
8. 클러스터형 인덱스는 무조건 테이블당 1개다.
9. 사용하지 않는 인덱스는 제거한다.
* Index의 생성
- Create 문으로 index를 생성하면 보조 인덱스가 생성이 됨.
- Unique 옵션을 부여한다면 고유한 index가 만들어지게 됨.
- ASC, DESC 방식으로 정렬 가능
- Create index 문으로는 클러스터형 인덱스를 생성할 수 없고, 클러스터형 인덱스를 만들려면 ALTER를 사용해야함
* View 란?
- 데이터베이스에 존재하는 '가상의 테이블'
- 실제 행과 열을 가지고 있지만 물리적으로 저장되지 않음
- Join이나 SubQuery 등에서 여러 개의 테이블을 조회 할 때의 번거로움을 줄여줄 수 있음
- View와 Table의 차이는 View에는 데이터가 없고 SQL만 저장하며 Table은 실질적인 데이터가 존재함.
- View를 생성한 기존 테이블의 Data가 업데이트 되면 View의 내용도 업데이트가 진행됨
---> View를 조회하면 옵티마이저에서 View를 생성할 때의 SQL문을 실행하기 때문에 View가 업데이트 된 것 처럼 보이는 것임.
| 장점 | 단점 |
| 특정 사용자에게 전체 테이블이 아닌 특정 컬럼만을 제공해 보안성 향상 |
삽입, 삭제, 갱신에 많은 제약 사항을 가짐 |
| 복잡한 쿼리를 단순화해서 사용 가능 | View는 자신만의 인덱스를 가질 수 없음 |
| 쿼리의 재사용이 용이해짐 | |
| 여러 방법의 데이터 조회에 알맞은 구조로 사용 가능 |
| Simple View (단순 뷰) | Complex View(복합 뷰) | |
| 테이블의 개수 | 한개의 테이블로 생성 | 여러개의 테이블로 생성 |
| 그룹함수 사용 여부 | 그룹 함수 사용 불가능 | 그룹 함수 사용 가능 |
| Distinct 사용 여부 | 사용 불가능 | 사용 가능 |
| DML 사용 여부 | 사용 가능 | 사용 불가능 |
- Simple View에서 DML 명령이 사용 불가능한 경우
1) View 생성시 포함되지 않은 컬럼이 Not Null인 경우
2) 산술 표현식을 포함한 컬럼이 포함된 경우
3) Distinct를 포함한 경우
4) 그룹함수나 Group by를 사용한 경우