풀 스캔(Full Scan)
테이블에 포함된 레코드를 처음부터 끝까지 읽어들임
explain select * from item;
레인지 스캔(Range Scan)
테이블의 일부(특정) 레코드에만 엑세스해서 읽어들임
explain select * from item where id between 1 and 10;
인덱스(index)
테이블에서 원하는 데이터를 쉽고 빠르게 찾기 위해 사용한다.
인덱스를 사용하면 풀 스캔을 하지않고 레인지 스캔을 함으로 검색과 질의에 대한 처리가 빠르게 이루어진다.
이러한 인덱스는 사용자가 직접 접근할 수는 없으며, 검색과 질의에 대한 처리에서만 사용된다.
인덱스가 설정된 리드 값을 포함한 데이터의 삽입, 수정, 삭제 작업이 원본 테이블에서 이루어질 경우, 인덱스도 수정되어야 한다.
따라서 인덱스가 설정된 테이블의 처리 속도가 느려질 수 있기때문에 수정보다는 검색이 자주 사용되는 테이블에서 사용해야 한다.
- 크기가 큰 테이블에만 인덱스를 적용한다. (작은 테이블에는 큰 차이가 없기때문이다.)
- 기본키 제약, 유일성 제약이 부여된 열에는 불필요하다. (자동으로 인덱스가 설정되어 있다.)
- 인덱스 갱신의 오버헤드로 갱신 처리의 성능이 떨어진다.
- 한개의 테이블에 복수의 인덱스를 작성한 경우 오히려 성능이 떨어질 수 있다.
- Cardinality(값의 분산도)가 높은 열에 만든다. 인덱스 트리를 따라가는데 조작이 증가해서 오버헤드 또한 증가하기 때문이다. (ex. 주민등록번호 : 중복되지않는 고유의 번호로 카디널리티가 높음 / 성별 : 종류가 낮고 중복이 많아 카디널리티가 낮음)
1) 인덱스 생성
CREATE INDEX 인덱스이름
ON 테이블이름 (필드이름1, 필드이름2, ...);
CREATE INDEX item_index
ON item(id);
2) 인덱스 정보 보기
SHOW INDEX FROM 테이블이름;
SHOW INDEX FROM item;
Key_name이 PRIMARY라고 적혀있는 것은 테이블 생성 당시 PK를 설정해주면 자동으로 인덱스가 설정된다.
- Table : 테이블의 이름을 표시함.
- Non_unique : 인덱스가 중복된 값을 저장할 수 있으면 1, 저장할 수 없으면 0을 표시함.
- Key_name : 인덱스의 이름을 표시하며, 인덱스가 해당 테이블의 기본 키라면 PRIMARY로 표시함.
- Seq_in_index : 인덱스에서의 해당 필드의 순서를 표시함.
- Column_name : 해당 필드의 이름을 표시함.
- Collation : 인덱스에서 해당 필드가 정렬되는 방법을 표시함.
- Cardinality : 인덱스에 저장된 유일한 값들의 수를 표시함.
- Sub_part : 인덱스 접두어를 표시함.
- Packed : 키가 압축되는(packed) 방법을 표시함.
- Null : 해당 필드가 NULL을 저장할 수 있으면 YES를 표시하고, 저장할 수 없으면 ''를 표시함.
- Index_type : 인덱스에 사용되는 메소드(method)를 표시함.
- Comment : 해당 필드를 설명하는 것이 아닌 인덱스에 관한 기타 정보를 표시함.
- Index_comment : 인덱스에 관한 모든 기타 정보를 표시함.
3) 인덱스가 설정된 컬럼 vs 아닌 컬럼간의 테스트
인덱스가 걸린 PK를 조건으로 걸어보고 검색
explain select * from item where id = 50;
인덱스가 걸리지 않은 이름으로 조건을 걸어서 검색
explain select * from item where name = '반팔티0101';
PK인 idx로 검색했을 때는 0.5 ms(밀리세컨드) / 인덱스가 잡히지 않은 name 컬럼으로 검색했을 때는 31.ms
데이터가 커질수록 인덱스를 걸어두고 안 걸어두고의 차이가 극명해질 것이다.
4) UNIQUE INDEX 설정
중복 값을 허용하지 않는 인덱스
CREATE UNIQUE INDEX 인덱스이름
ON 테이블이름 (필드이름1, 필드이름2, ...)Non_unique : 인덱스가 중복된 값을 저장할 수 있으면 1, 저장할 수 없으면 0을 표시함
5) 인덱스 생성시 정렬
-- 내림차순
CREATE INDEX 인덱스이름
ON 테이블이름 (필드이름 DESC)
-- 오름차순
CREATE INDEX 인덱스이름
ON 테이블이름 (필드이름 ASC)
6) 인덱스 추가 - ALTER문 이용
- 기본 인덱스
- UNIQUE INDEX
- FULLTEXT INDEX : 일반 인덱스와 다르게 모든 텍스트 필드를 매우 빠르게 검사
-- 기본 인덱스 추가
ALTER TABLE 테이블이름
ADD INDEX 인덱스이름 (필드이름)
-- 유니크 인덱스 추가
ALTER TABLE 테이블이름
ADD UNIQUE 인덱스이름 (필드이름)
-- 풀텍스트 인덱스 추가
ALTER TABLE 테이블이름
ADD FULLTEXT INDEX이름 (필드이름)
7) 인덱스 삭제 - ALTER문, DROP문 이용
ALTER TABLE 테이블이름
DROP INDEX 인덱스이름
DROP INDEX 인덱스이름
ON 테이블이름
참고 및 출처 : zorba91.tistory.com/292
www.tcpschool.com/mysql/mysql_index_add