# INDEX 개요
1. Index는 MYI (MySQL Index)파일에 저장 됩니다.
2. Index가 설정되지 않았다면 Table Full Scan이 일어 나 성능이 저하 되거나 치명적인 장애가 발생 합니다.
3. Index는 검색 속도를 향상 시키는 장점이 있지만, update, insert, delete 속도는 저하 됩니다. 이는 Table의 Index 색인 정보를 갱신하는 비용이 추가 되기 때문입니다. Index 설정시 이런 부분들을 때문에 효율적인 컬럼 선정이 이루어 져야 합니다.
# INDEX 목적
1. 조건과 일치하는 열을 빨리 찾기 위해 사용.
2. JOIN시 다른 Table의 열을 추출 하기 위해 사용.
3. Max, Min값을 찾기 위해 사용.
# INDEX 고려사항
1. Index의 키의 크기는 되도록 작게 설계해야 성능에 유리 합니다.
2. 분포도가 좋은 컬럼(좁은 범위), 기본 키, 조인의 연결 고리가 되는 컬럼을 Index로 구성합니다.
// 분포도에 따라 검색 범위가 좁아진다.
3. 단일 인덱스 여러 개 보다 다중 컬럼 Index의 생성을 고려 합니다.
4. Update가 빈번하지 않은 컬럼으로 인덱스를 구성하는게 좋습니다.
5. Join시 자주 사용하는 컬럼은 Index로 등록하는게 유리 합니다.
6. 되도록 동등 비교(=)를 사용합니다.
7. 조건절에 자주 사용하는 컬럼은 Index 추가를 고려 합니다.
8. Index를 많이 생성하면 Insert/Update/Delete의 성능 저하의 원인이 될 수 있습니다.
9. Index scan이 Table 순차 sacn보다 항상 빠르지 않습니다. 보통 선택도(selectivity)가 5~10% 이내인 경우 Index scan이 우수합니다.
S (selectivity) = d/n
d = 서로 다른 값의 수 (# of distinct values)
n = 테이블의 전체 레코드 수
10. Index를 생성할때 가장 효율적인 자료 형은 정수형 자료 입니다. (TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT등)
11. 가변적인 크기와 정규화 할수 없는 데이터는 인덱스를 생성할때 비효율적으로 동작 합니다 (Text 데이터 등) 이를 위해 FULLTEXT INdex를 사용할 수 있습니다. FullText 는 TEXT, BLOB, VARCHAR등 가변적이고 일반적인 Index의 효율성이 떨어지는 부분에서 많은 효과를 가져올 수 있는 형태의 Index 입니다.
FULLTEXT Index는 텍스트 필드에 '%검색문자열%'와 비슷한 형태의 검색 결과를 얻을 수 있고 Text에 최적화된 Index 방식입니다. MySQL 5.6 버전부터는 InnoDB 에서도 지원 합니다.
12. Index를 사용한 필드의 조건 검색이 not, <, > 일 경우 Index를 사용하지 않습니다. ( => , <= , =, Between 은 사용이 가능합니다.)
13. Index를 사용한 필드의 조건 검색이 like '%값' 혹은 '%값%'인 경우 Index를 사용하지 않습니다. (like '값%'은 사용 가능 합니다.)
14. Index를 사용한 필드의 조건 검색이 다른 필드와 비교일 경우 Index를 사용하지 않습니다. (where 컬럼1=컬럼2)
# order by 와 group by에 대한 Index
Index는 order by와 group by에도 영향을 끼칩니다. 다음과 같은 경우에는 Index를 타지 않습니다.
1. order by 인덱스 컬럼1, 컬럼2 : 복수의 키에 대해서 order by를 사용한 경우
2. where 컬럼1='값' order by 인덱스 컬럼 : 연속하지 않은 컬럼에 대해 order by를 실행한 경우
3. order by 인덱스컬럼1 desc, 인덱스컬럼2 asc : desc와 asc를 혼합하여 사용한 경우
4. group by 컬럼1 order by 컬럼2 : group by와 order by의 컬럼이 다른 경우
5. order by abs(컬럼) : order by절에 다른 표현을 사용할 경우
# order by 동작
1. where절에 일치하지 않는 열은 제외하고 키 순서에 따라 모든 열을 읽어 들입니다.
2. 각각의 열에 대해 버퍼에 정렬을 위한 키와 열에 대한 포인터의 쌍을 저장합니다. (이때 사용되는 버퍼의 크기는 sort_buffer_size에 의해 결정 됩니다.)
3. 버퍼가 가득 차면, 퀵소트를 수행하고 그 결과를 임시 파일에 보관한 뒤 다시 반복 합니다. (sort_buffer_size의 크기에 다 담길 양이면 임시 파일을 만들지 않습니다.)
4. 다른 임시 파일에 대해, 최대 MERGEBUFF(기본값: 7)의 영역을 하나의 블록이 될 때까지 다중 병합 작업을 수행합니다. 첫번째 파일의 모든 블럭이 두번째 파일과 같아질 때까지 반복합니다.
5. MERGEBUFF2(기본값:15)보다 작은 수의 블록이 남을 때 까지 반복합니다.
6. order by에 대한 index를 사용하지 못할 경우는 sort_buffer_size와 read_rnd_buffer_size의 크기를 증가 시킵니다.
# INDEXT 추가/생성/삭제.
인덱스 보기
show index from 테이블명
인덱스 추가
alter table 테이블명 add index 인덱스명 (칼럼명);
인덱스 삭제
alter table 테이블명 drop index 인덱스명;
# 다중 컬럼 인덱스 (Multiple-column Index)
다중 컬럼 인덱스는 두개 이상의 필드를 조합하여 생성한 Index 입니다. 첫번째 조건과 이를 만족하는 두번째 조건을 함께 Index 하여 검색 성능 향상을 위해 사용 됩니다. MySQL은 하나의 Index에 최대 15개의 컬럼으로 구성될 수 있습니다.
다중 컬럼 인덱스는 단일 컬럼 인덱스 보다 더 비효율적으로 Index/Update/Delete를 수행하기 때문에 사용에 신중해야 합니다. 가급적 업데이트가 안되는 값을 선정하야 사용하는게 좋습니다.
테이블1
Create Table tb_name( |
테이블2
Create Table tb_name( |
단일 index와 다중 컬럼 인덱스의 경우를 차이점에 대해 알아 봅니다.
select * from tb_name where name='홍길동' and address='경기도'
테이블1의 경우 각각의 필드에 Index가 설정되어 있기 때문에 MySQL은 name컬럼과 address컬럼을 보고 둘중에 어떤 컬럼의 수가 더 빠르게 검색되는지 판단후 빠른쪽을 먼저 검색하고 그 다음 다른 컬럼을 검색하게 됩니다. // Index를 둘다 타는 것이 아니다.
테이블2의 경우 바로 원하는 값을 찾습니다. 그 이유는 Index를 저장할때 name가 address를 같이 저장하기 때문입니다. 즉 name 과 address의 값을 함께 색인을 한 뒤 검색에서도 홍길동경기도 로 검색을 시도하게 됩니다. 이렇게 사용할 경우 테이블1의 경우 보다 테이블2의 경우가 더 빠른 검색을 할 수 있습니다.
하지만 다중 컬럼 인덱스를 다음과 같이 사용 한다면 Index를 타지 못합니다.
(항상 A,B 로만 조회해야 한다면 다중, 개별로 조회해야하는 경우도 있으면 각각)
select * from tb_name where address='경기도'
이 경우 name이 함께 검색이 되지 않으므로 Index의 효과를 볼 수 없습니다.
다중 컬럼 인덱스를 사용할때는 index의 제일 왼쪽 컬럼이 where 절에 사용 되어야 합니다.
# 인덱스를 지정하면 좋은 컬럼!
그럼 어떤 컬럼에 인덱스를 설정하는게 좋을까?
인덱스는 한 테이블당 보통 3~5개 정도가 적당합니다.
물론 테이블의 목적 등에 따라 개수는 달라질 수 있습니다.
인덱스는 컬럼을 정해서 설정하는 것이므로 후보 컬럼의 특징을 잘 파악해야 합니다.
아래 4가지 기준을 사용하면 효율적으로 인덱스를 설정할 수 있습니다.
- 카디널리티 (Cardinality)
- 선택도 (Selectivity)
- 활용도 : 활용도가 높다면, 업데이트가 잦더라도, 인덱스로 설정하고, 가끔식 인덱스 리빌딩을 수행한다.
- 중복도
카디널리티 (Cardinality)
✔️ 카디널리티가 높을 수록 인덱스 설정에 좋은 컬럼입니다.
= 한 컬럼이 갖고 있는 값의 중복 정도가 낮을 수록 좋습니다.
컬럼에 사용되는 값의 다양성 정도, 즉 중복 수치를 나타내는 지표입니다.
후보 컬럼에 따라 상대적으로 중복 정도가 낮다, 혹은 높다로 표현됩니다.
예를 들어, 10개 rows를 가지는 ‘학생’ 테이블에 ‘학번’과 ‘이름’ 컬럼이 있다고 해봅시다.
- ‘학번’은 학생마다 부여 받으므로 10개 값 모두 고유합니다.
- 중복 정도가 낮으므로 카디널리티가 낮습니다.
- ‘이름’은 동명이인이 있을 수 있으니 1~10개 사이의 값을 가집니다.
- 중복 정도가 ‘학번’에 비해 높으므로 카디널리티가 높다고 표현할 수 있습니다.
선택도 (Selectivity)
✔️ 선택도가 낮을 수록 인덱스 설정에 좋은 컬럼입니다.
5~10% 정도가 적당합니다.
데이터에서 특정 값을 얼마나 잘 선택할 수 있는지에 대한 지표입니다.
선택도는 아래와 같이 계산합니다.
= 컬럼의 특정 값의 row 수 / 테이블의 총 row 수 * 100
= 컬럼의 값들의 평균 row 수 / 테이블의 총 row 수 * 100
예를 들어, 10개 rows를 가지는 ‘학생’ 테이블에 ‘학번’, ‘이름’, ‘성별’ 컬럼이 있다고 해봅시다.
학번은 고유하고, 이름은 2명씩 같고, 성별은 남녀 5:5 비율입니다.
- ‘학번’의 선택도 = 1/10*100 = 10%
SELECT COUNT(1) FROM '학생' WHERE '학번' = 1;(모두 고유하므로 특정 값: 1)
- ‘이름’의 선택도 = 2/10*100 = 20%
SELECT COUNT(1) FROM '학생' WHERE '이름' = "김철수";(2명씩 같으므로 특정 값: 2)
- ‘성별’의 선택도 = 5/10*100 = 50%
SELECT COUNT(1) FROM '학생' WHERE '성별' = F;(5명씩 같으므로 특정 값: 5)
즉, 선택도는 특정 필드값을 지정했을 때 선택되는 레코드 수를 테이블 전체 레코드 수로 나눈 것입니다.
활용도
✔️ 활용도가 높을 수록 인덱스 설정에 좋은 컬럼입니다.
해당 컬럼이 실제 작업에서 얼마나 활용되는지에 대한 값입니다.
수동 쿼리 조회, 로직과 서비스에서 쿼리를 날릴 때 WHERE 절에 자주 활용되는지를 판단하면 됩니다.
중복도
✔️ 중복도가 없을 수록 인덱스 설정에 좋은 컬럼입니다.
중복 인덱스 여부에 대한 값입니다.
인덱스 성능에 대한 고려 없이 마구잡이로 설정하거나,
다른 부서 다른 작업자의 분리된 요청으로
같은 컬럼에 대해 인덱스가 중복으로 생성된 경우를 볼 수 있습니다.
인덱스도 속성을 가집니다.
인덱스는 테이블 형태로 생성되므로, 속성을 컬럼으로 관리합니다.
(참고) 주요 인덱스 컬럼
- Table: The name of the table.
- Non_unique: 0 if the index cannot contain duplicates, 1 if it can.
- Key_name: The name of the index. If the index is the primary key, the name is always PRIMARY.
- Seq_in_index: The column sequence number in the index, starting with 1.
- Column_name: The column name.
- Collation: How the column is sorted in the index. This can have values A (ascending) or NULL (not sorted).
- Cardinality: An estimate of the number of unique values in the index.
- Index_type: The index method used (BTREE, FULLTEXT, HASH, RTREE)
이 속성이 다를 때 같은 컬럼에 대해 중복으로 인덱스 설정이 가능합니다.
같은 컬럼에 대해 중복 인덱스가 설정되어 있다고 하더라도 SQL 자체 연산이 빠른 쪽으로 데이터를 조회 합니다만,
인덱스도 결국 메모리의 일부이므로 필요 없는 항목은 삭제하는게 좋겠지요?
# 인덱스 설정 기준
| 기준 | 정도 |
|---|---|
| 카디널리티 (Cardinality) | 높을 수록 적합 |
| 선택도 (Selectivity) | 낮을 수록 적합 (5~10% 적정) |
| 활용도 | 높을 수록 적합 |
| 중복도 | 없을 수록 적합 |
○ 쿼리 튜닝 시 고려사항
- Where절에서 자주 사용되는 컬럼에는 인덱스 추가 고려
- 인덱스를 많이 생성하는 것은 INSERT/UPDATE/DELETE 성능 저하의 원인이 될 수 있음.
- 동일한 값을 가지는 레코드가 적은 컬럼에 인덱스를 설정
- 인덱스 scan이 Full Table scan보다 항상 빠르지는 않다
# 인덱스의 사이즈
인덱스를 저장하는 데 필요한 디스크 공간은 보통 테이블을 저장하는 데 필요한 디스크 공간보다 작다.
(왜냐하면 보통 인덱스는 키-필드만 갖고 있고, 테이블의 다른 세부 항목들은 갖고 있지 않기 때문이다.)
# 출처
https://yurimkoo.github.io/db/2020/03/14/db-index.html
https://code-factory.tistory.com/24[mysql Index 와 다중 컬럼 인덱스]
'[DBA] Database > SQL ∕ Mssql' 카테고리의 다른 글
| On Delete Cascade에 대한 고찰 (0) | 2020.10.27 |
|---|---|
| Mssql 기초 사이트 (0) | 2020.09.09 |
최근댓글