인덱스

인덱스 장점)

- 검색 속도가 무척 빨라질수 있다.(단, 항상 그런 것은 아니다)

- 그 결과 해당 쿼리의 부하가 줄어들어서, 결국 시스템 전체의 성능이 향상된다.

인덴스 단점)

- 인덱스가 데이터베이스 공간을 차지해서 추가적인 공간이 필요해지느데, 대략 데이터베이스 크기의 10% 정도의 추가 공간이 필요하다.

- 처음 인덱스를 생성하는데 시간이 많이 소요될 수 있다.

- 데이터의 변경 작업(insert, update, delete)이 자주 일어날 경우에는 오히려 성능이 많이 나빠질 수도 있다.

MySQL에서 사용되는 인덱스의 종류

1) 클러스터형 인덱스(Clustered Index)

2) 보조 인덱스(Secondary Index)

클러스터형 인덱스는 테이블당 한 개만 생성할 수 있고, 보조 인덱스는 테이블당 여러 개를 생성할 수 있다. 또 클러스터형 인덱스는 행 데이터를 인덱스로 지정한 열에 맞춰서 자동 정렬한다.

---

B-Tree(Balanced Tree, 균형트리)

- 노드라는 용어는 개념적인 설명에서 주로 나오며 MySQl이 B-Tree를 사용할 떄는 이 노드에 해당되는 것이 페이지(Page)이다.

- 페이지란 16Kbyte 크기의 최소한의 저장 단위이다. 아무리 작은 데이터를 한개만 저장하더라도 한 개 페이지를 차지하게 된다.

- DBMS마다 페이지 크기를 다르며 필요하다면 4k, 8k, 16k, 32k, 64k로 변경할수있다.

페이지 삽입시

- 데이터입력은 2개이지만 C1~C4칸 페이지에는 빈칸이 1개이므로 새로운 페이지를 입력하고 루트노드에 1개 C5를 끼워놓는다.

중간노드 생길경우

- 루트노드가 너무 큰경우 중간노드를 만들어 분할시킨다.

클러스트형 인덱스

특징)

- 클러스트형 인덱스의 생성 시에는 데이터 페이지 전체가 다시 정렬된다. 그러므로 이미 대용량의 데이터가 입력된 상태라면 업무시간에 클러스터형 인덱스를 생성하는 것은 심각한 시스템 부하를 줄 수 있으므로 신중하게 생각해야 한다.

- 클러스터형 인덱스 자체의 리프 페이지가 곧 데이터이다. 그러므로 인덱스 자체에 데이터가 포함되어 있다고 볼 수 있다.

- 클러스터형 인덱스는 보조 인덱스보다 검색 속도는 더 빠르다. 하지만, 데이터의 입력/수정/삭제는 더 느리다

- 클러스터형 인덱스는 성능이 좋지만 테이블에 한 개만 생성할 수 있다. 그러므로 어느 열에 클러스터형 인덱스를 생성하는지에 따라서 시스템의 성능이 달라질 수 있다.

보조 인덱스

- 보조 인덱스의 생성 시에는 데이터 페이지는 그냥 둔 상태에서 별도의 페이지에 인덱스를 구성한다.

- 보조 인덱스의 인덱스 자체의 리프 페이지는 데이터가 아니라 데이터가 위치하는 주소 값(RID)이다. 클러스터형보다 검색 속도는 더 느리지만 데이터의 입력/수정/삭제는 덜 느리다.

- 보조 인덱스는 여러 개 생성할 수 있다. 하지만, 함부로 남용할 경우에는 오히려 시스템 성능을 떨어뜨리는 결과를 초래할 수 있으므로 꼭 필요한 열에만 생성하는 것이 좋다.

혼합인덱스(클러스터형 + 보조)

표만들기위해 쓰인 SQL

drop table IF EXISTS aa;
create temporary table aa (select * from emp where emp_no < 11000 limit 13);
select emp_no, first_name, last_name from aa;
select emp_no, first_name, last_name from aa order by emp_no;
select emp_no, first_name, last_name, gender, hire_date, birth_date from aa order by first_name;

---

<주의>

인덱스 제거

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

- 기본키로 설정된 클러스터형 인덱스의 이름은 항상 'PRIMARY'로 되어 있으므로 인덱스이름 부분에 PRIMARY로 써주면된다. 또한 ALTER TABLE문으로 기본키를 제거해도 클러스터형 인덱스가 제거된다.

- 인덱스를 모두 제거할 때는 되도록 보조 인덱스부터 삭제하도록 한다. 혼합 인덱스를 예시를 든다면 만약 클러스터형 인덱스를 먼저 삭제한다면 클러스터형 인덱스의 루트 페이지가 없어진다. 그러면 보조 인덱스의 리프 페이지는 모두 루트 페이지로 지정하고 있으므로, 어쩔수 없이 '페이지번호+#오프셋'으로 재구성된다. 그런데 재구성이후 보조 인덱스를 삭제해야 하기때문에 결국 시간만 손해보는것이다.

- 인덱스를 많이 생성해 놓은 테이블은 인덱스의 용도를 잘 확인한 후에, 인덱스의 활용도가 떨어진다면 과감히 삭제해 줄 필요가 있다.

---

인덱스의 성능 비교

create table emp select * from employees.employees order by rand();
create table emp_c select * from employees.employees order by rand();
create table emp_se select * from employees.employees order by rand();
 
select * from emp limit 5;
select * from emp_c limit 5;
select * from emp_se limit 5;

show table status;

Data_length : 데이터 크기

Index_length : 인덱스 크기

alter table emp_c add primary key(emp_no);
alter table emp_se add index idx_emp_no(emp_no);

컴퓨터 사양에 따라 처리시간이 다르다. 그러므로 기다리자.

세개의 테이블중 Primary key를 추가한 테이블만 자동정렬이 되고 index 추가한 테이블은 자동정렬해주지 않는다.

analyze table emp, emp_c, emp_se;
show index from emp;
show index from emp_c;
show index from emp_se;
show table status;

show index 명령어를 통해 key_name을 볼수있으며 Index_length 값이 변경됨을 볼 수 있다.

show global status like 'Innodb_pages_read'; -- 쿼리 실행 전에 읽은 페이지 수
select * from emp where emp_no = 100000;
show global status like 'Innodb_pages_read'; -- 쿼리 실행 후에 읽은 페이지 수
 
show global status like 'Innodb_pages_read'; -- 쿼리 실행 전에 읽은 페이지 수
select * from emp_c where emp_no = 100000;
show global status like 'Innodb_pages_read'; -- 쿼리 실행 후에 읽은 페이지 수
 
show global status like 'Innodb_pages_read'; -- 쿼리 실행 전에 읽은 페이지 수
select * from emp_se where emp_no = 100000;
show global status like 'Innodb_pages_read'; -- 쿼리 실행 후에 읽은 페이지 수

Full Table Scan으로 코스트 비용이 높다.

---

코스트 비용이 낮다

---

코스트 비용이 낮은편이나 Primary Key 보다 손해본다.

하지만 손해본 만큼 다른것에 장점을 메꾸기 때문에 괜찮다.

show global status like 'Innodb_pages_read'; -- 쿼리 실행 전에 읽은 페이지 수
select * from emp_c where emp_no < 11000;
show global status like 'Innodb_pages_read'; -- 쿼리 실행 후에 읽은 페이지 수

Index Range Scan을 실시한다.

show global status like 'Innodb_pages_read'; -- 쿼리 실행 전에 읽은 페이지 수
select * from emp_c ignore index(primary) where emp_no < 11000; -- ingonre 명령어를 써서 인덱스를 사용하지 못하도록 강제로 지정
show global status like 'Innodb_pages_read'; -- 쿼리 실행 후에 읽은 페이지 수

---

결론)

1. 인덱스는 열 단위에 생성된다.

2. WHERE절에서 사용되는 열에 인덱스를 만들어야 한다.

3. WHERE절에 사용되더라도 자주 사용해야 가치가 있다.

만약 insert작업만 일어나고 클러스터형 인덱스라면 성능이 무척 나빠지는 유형이 된다.

4. 데이터의 중복도가 높은 열은 인덱스를 만들어도 별 효과가 없다.

오히려 인덱스의 관리에 대한 비용 때문에 인덱스가 없는 편이 나을 수도 있다.

5. 외래 키를 지정한 열에는 자동으로 외래 키 인덱스가 생성된다.

외래키 제약 조건의 열에는 자동으로 인덱스가 생성된다.

6. JOIN 에 자주 사용되는 열에는 인덱스를 생성해주는 것이 좋다.

7. INSERT / UPDATE / DELETE가 얼마나 자주 일어나는지를 고려해야 한다.

8. 클러스터형 인덱스는 테이블당 하나만 생성할 수 있다.

클러스터형 인덱스를 생헝할 열은 범위(BETWEEN, >, < 등의 조건)로 사용하거나 집계 함수를 사용하는 경우에는 아주 적절하다.

9. 클러스터형 인덱스가 테이블에 아예 없는 것이 좋은 경우도 있다.

-> 주의할점 NOT NULL UNIQUE를 함께 지정하면 클러스터형 인덱스가 생성된다.

10. 사용하지 않는 인덱스는 제거하자

인덱스는 한번 생성했다고 내버려 두는 것이 아니라 잘 활용되는지를 살펴서 활용이 되지 않는 인덱스라면 과감히 제거하고, 주기적인 OPTIMEIZE TABLE 구문이나 ANALYZE TABLE 구문으로 인덱스의 재구성을 통해서 조각화를 최소화해야만 시스템의 성능을 최상으로 유지시킬 수 있을 것이다.