[MySQL_MariaDB] 스키마_인덱스 정리

* 스키마 : 데이터베이스와 동일한 용어로 사용된다. 다른 DBMS에서는 스키마와 데이터베이스가 동일한 용어는 아님.

 Full Table Scan > 인덱스를 사용하지 않고 테이블 전체를 검색했고 비용은 103개가 들었다. 500만 건이라면 과부하가 걸렸을 것이다.

 

1. 인덱스 이름 idx_indexTBL_firstname은 indexTBL 테이블의 first_name 열에 인덱스를 생성.

2. Non-Unique Key Lookup : 인덱스를 사용했다는 의미. 인덱스명은 하단에 명시됨.

> 이렇게 인덱스를 생성하면 데이터의 양에 따라 몇 십 배 이상 빠를 수 있다.

 

삭제는 drop index idx_indexTBL_firstname > drop 개체종류 개체이름

 

신문기사 테이블(아래표)을 생성하고, 일반 인덱스(위표)를 생성했다고 가정해보자.

 

1. 신문기사의 내용을 모두 알아서 검색하면 당연히 빠르다.

2. 앞글자 like검색도 인덱스를 통하여 위위사진(신문기사 인덱스)처럼 정렬되어 있기에 검색이 빠르다.

 

22~33. 입력한 순서와 조회한 순서가 다르다. 그 이유는 userID가 PK이므로 클러스터형 인덱스가 생성돼서 입력시 userID열로 정렬되기 때문이다.

* 인덱스의 종류

1) 클러스터형 인덱스

> 영어사전처럼 책의 내용 자체가 순서대로 정렬되어 있음. Primary Key 인덱스와 거의 동일한 용어로 사용된다. 기본키는 하나이므로 클러스터형 인덱스는 1개만 생성되는 것이다.(복합키 또한 클러스터형 인덱스로 복합키자체로 인덱스로 생성됨)

> 테이블 생성시 Primary Key 또는 Unique를 사용하면 인덱스가 자동으로 생성된다.

2) 보조인덱스(넌클러스터형 인덱스) : 찾아보기가 별도로 있는 책

 

6. show index from 테이블명 : 인덱스의 정보를 확인한다.

* Non_unique

> 0 : Unique 인덱스

> 1 : Nonunique 인덱스를 의미한다.

* Key_name

> PRIMARY : 클러스터형 인덱스.(예외도 있음) 보조인덱스는 열의 이름 또는 키 이름으로 표기된다.

* Seq_in_index : 여러 개의 인덱스가 설정되었을 때의 순서를 나타낸다.

* Null : NULL 값의 허용 여부인데 비어있으면 NO를 의미한다.

* Cardinality : 중복되지 않은 데이터 개수가 들어간다.

* Index_type : 어떤 형태로 인덱스가 구성되었는지 나타내는데, MySQL은 기본적으로 B-Tree 구조를 갖는다.

 

Unique 제약조건으로 설정하면 보조 인덱스가 자동으로 생성되는 것을 확인할 수 있다. 보조인덱스는 테이블당 여러 개가 생성될 수 있으므로 여러 개의 보조 인덱스가 생성된 것은 아무 문제가 안된다.

 

* Primary Key 없이 Unique Key로만 지정되어도 클러스터형 인덱스가 비었다고 Unique가 클러스터형 인덱스가 되는 것은 아니다.

 

Non_unique값이 0이고, Not null 이면 a열은 클러스터형 인덱스로 지정된 것이다.

 

d열이 Primary로 되었다. 이런 경우 d열이 클러스터형 인덱스가 생성되고, a열에는 보조 인덱스가 생성된다. 즉, 클러스터형 인덱스는 테이블당 하나밖에 지정되지 않으므로 Primary Key로 설정한 열에 우선 클러스터형 인덱스가 생성된다.

 

입력할 때는 이승기, 김범수 .. 순으로 입력했으나, 입력되어 있는 차례는 영어순으로 조용필, 김범수 순이다. 이는 Primary Key인 userid에 클러스터형 인덱스가 생성되어 정렬하기 때문.

1. primary key로 지정한 열은 클러스터형 인덱스가 생성된다.

2. unique, not null로 지정한 열은 클러스터형 인덱스가 생성된다.

3. unique(또는 unique null)로 지정한 열은 보조 인덱스가 생성된다.

5. primary key와 unique not null이 있으면 primary key로 지정한 열에 우선 클러스터형 인덱스가 생성된다.

6. primary key로 지정한 열로 데이터가 오름차순 정렬된다.

(데이터의 양에 따라서 PrimaryKey를 변경한다면 인덱스를 재색인 작업이 필요해 디비가 엄청 느려진다. 업무시간에 많은 양의 데이터의 key를 바꾸는 것은 자제해야한다.)

 

* B-Tree : 자료 구조에 나오는 범용적으로 사용되는 데이터의 구조로 주로 인덱스를 표현할 때와 그 외에서도 많이 사용된다.

* 노드(페이지) : 갈라지는 부분(마디)

> 위 사진은 총 4개의 노드로 루트 노드란 가장 상위 노드. 제일 마지막에 존재하는 노드 리프노드.

* 위 사진은 페이지당 4개의 데이터가 들어간다고 가정하고 4칸으로 그림.

* 만약 B-Tree 구조가 아니라면 MMM 데이터를 찾는 방법은 처음부터 검색하는 방법밖에 없으므로 Full Table Scan. B-Tree 구조라면 MMM은 LLL 다음에 나오므로 세번째 리프 페이지로 직접 이동하면된다. 결국 2개의 페이지를 읽고 찾게 된다. 지금은 2단계 뿐이어서 그 효용성이 별로 크게 못 느껴질 수 있지만, 훨씬 많은 양의 데이터의 경우 그 차이가 기하급수적으로 난다. 이 말은 인덱스를 구성하면 SELECT의 속도가 급격히 향상될 수 있다는 것을 뜻한다.

* 인덱스의 단점

> 데이터의 변경작업(INSERT, UPDATE, DELETE) 시에 성능이 나빠지는 단점이 있다. 특히 INSERT 작업이 일어날 때 성증이 급격히 느려질 수 있다. 그 이유는 ‘페이지 분할’이라는 작업이 발생되기 때문이다.

 

만약 III와 GGG 가 입력되었다면 MySQL은 새로운 페이지를 만들고 전체적인 수정이 일어나게 된다. 이렇게 됨으로 인덱스를 구성하면 왜 데이터변경이 느려지는지 확인할 수 있다.

 

클러스트형 인덱스와 보조인덱스의 구조

 

페이지당 16k의 데이터가 들어간다. 많은 행이 들어간다고 보면됨.

필요하다면 4,8,16,32,64k로 변경할 수 있다.

 

예를들어 페이지당 4개의 행이 입력된다고 가정한다면 인덱스가 없는 데이터는 저런식으로 하나의 페이지에 4개의 데이터가 등록된 순서대로 저장될 것이다.

 

Primary key를 지정하면 클러스터형 인덱스가 생성되어 B-Tree형태의 인덱스가 형성된다.

 

클러스터형 인덱스는 루트페이지, 중간페이지, 리프페이지로 구성되어 있는데, 리프페이지는 데이터 그 자체를 의미하며 루트,중간페이지는 찾아보기(인덱스)로 구성되어 있다. 클러스터 인덱스 생성시 데이터 페이지 전체가 다시 정렬되므로 심각한 시스템 부하가 올 수 있다.

클러스터 인덱스는 보조인덱스보다 일부 예외사항도 있지만, 검색 속도가 더 빠르다.

 

Unique 제약조건은 보조 인덱스를 생성한다. 입력한 것과 순서의 변화는 없다.

 

* 보조인덱스 : 데이터 페이지를 건드리지 않고, 별도의 장소에 인덱스 페이지를 생성한다.

보조인덱스는 루트, 리프페이지에 데이터를 정렬하여 데이터 주소값을 생성한다. 클러스터 인덱스에 비해 속도는 느리지만, 데이터 페이지를 정렬하는 것이 아닌 리프페이지의 추가를 시키면 끝남으로 데이터 입력시 성능에 주는 부하는 더 적다.

* 클러스터형 인덱스와 보조 인덱스가 혼합된 경우

> 클러스터형의 경우에는 그대로 변함이 없다.

> 보조인덱스의 경우 리프페이지에 주소값이 아닌 클러스터형 인덱스의 키값을 가지게 되어, 클러스터형 인덱스에서 검색을 진행한다. 이유는 데이터 등록시 클러스터형, 보조인덱스 모두 페이지를 만들어지는 난잡한 현상을 억제하기 위함.

* 따라서 클러스터형 인덱스로 설정할 열은 적은 자릿수의 열을 선택하는 것이 바람직하다.

 

* FULLTEXT : 전체 텍스트 인덱스를 만드는데 사용된다. 후에..

* SPATIAL : 점,선,면 등의 공간 데이터와 관련된 인덱스를 생성한다.

* UNIQUE : 고유한 인덱스를 만들 것인지 결정한다. 디폴트는 UNIQUE가 생략된(=중복이 허용되는) 인덱스이다.

 

인덱스 제거. 클러스터형 인덱스 이름은 primary 이다. 아래아래 참조

 

간단하게 제거하는 법

* alter table로 기본키를 제거해도 클러스터형 인덱스는 제거된다.

* 인덱스를 모두 제거할 때는 보조인덱스부터 삭제한다. 보조인덱스의 리프페이지는 클러스터형의 루트페이지를 지정하고 있으므로, 보조인덱스의 리프페이지는 페이지번호+#주소값으로 재구성되야하므로.

* 어쩌다 한번 사용하는 인덱스는 과감하게 삭제하자.

 

인덱스 이름 조회

Primary 키의 이름은 클러스터형 인덱스를 의미한다. 현재 usertbl에는 userid 열에 클러스터형 인덱스 하나만 설정되어 있다.

 

인덱스 크기 조회

* data_length : 클러스터형 인덱스의 크기를 byte 단위로 표기한 것. Mysql의 페이지 크기는 기본적으로 16KB이므로 16*1024 = 16384 로 할당되어 있다.

* index_length : 보조 인덱스의 크기인데 현재 보조인덱스가 없으므로 0

* 위 테이블은 이미 클러스터형 인덱스가 있으므로 클러스터형 인덱스를 또 생성불가.

 

 

단순 보조인덱스 생성(단순은 고유(UNIQUE)와 반대) 이게 끝이 아님 밑에 스샷참조

 

고유 인덱스가 아니면 1로 설정된다.

 

보조인덱스 크기인 index_length가 계속 0으로 나온다.

 

생성한 보조인덱스를 적용시키려면 analyze table 문으로 먼저 테이블을 분석/처리해줘야한다.

 

인덱스 조합. Analyze table은 위에서 해주었기에 생략함. 테이블당 하나만 하면됨.

 

*인덱스 삭제

15,16. 삭제시에는 보조인덱스부터 삭제하는게 좋으며(위에 설명), drop alter를 통해 삭제가 가능하다.

18~20. 클러스터형 인덱스를 제거해아한다. 하지만, 외래키 때문에 제거되지 않아 외래키 조회

22,23. 외래키를 먼저 제거하고 기본키를 제거하며, 클러스터형 인덱스는 drop문은 먹히지 않으면 alter문으로만 삭제 가능하다. 당연히 기본키는 제거된다.

 

6~8. Create + select를 통한 복사는 인덱스 제약조건 등을 제외한 데이터만 복사함.

12. 인덱스의 이름 즉 인덱스가 있는지 조회. 없음 아직 안만듬.

14~16. 인덱스 생성. analyze table 문을 통해 테이블 분석처리를 통해 보조인덱스를 적용시킴.

18. 인덱스의 크기를 조회(index_length : 보조인덱스 크기, data_length : 클러스터형 인덱스)

20,22. Show global status : 조회한 페이지 수를 조회. 약 1000페이지에 가까운 899페이지를 읽었다.

21. ignore index : 인덱스를 강제로 사용하지 못하게함 > 반대 use index

- emp_no가 499,999인데, where 조건에 500,000 미만을 지정했으니 모두 읽는다. Index가 있거나 없거나 모든 페이지를 읽어야 하기 때문에 인덱스의 효과를 볼 수 없다.

* select * from emp_c 와 같이 전체검색은 인덱스가 있다하더라고 전체검색이므로 FullScan이므로, 인덱스가 없는 것과 차이가 없다.

* 보조인덱스 또한 전체 데이터의 대략20% 이상을 스캔하는 경우에는 mysql이 인덱스를 사용하지 않고 FullScan하는 경우가 있다. 데이터를 찾기위해 중간페이지에서 멤도는 시간이 더 많기 때문. 이러한 사용하지 않는 인덱스는 제거하여 변경작업이 발생했을 때 성능이 개선될 수 있다.

 

25,26. 두 결과는 동일하지만, 열 이름에 함수(*)가 적용되면 인덱스가 발생하지 않는 현상이 발생되는 DBMS들이 있다. 그러므로 최대한 where 조건의 열 이름에 아무런 가공을 하면 안된다.

 

남,여 밖에 없는 중복도가 높은 컬럼에 index를 걸어도 거의 모든 페이지를 찾기 때문에 Full Scan과 조회 속도에 큰 차이는 없는 반면에, INSERT 등의 구문에서는 오히려 성능저하가 올 수 있다는 것을 알아야한다.

 

 

* 인덱스를 생성해야 하는 경우와 그렇지 않은 경우

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

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

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

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

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

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

7. insert/update/delete가 얼마나 자주 일어나는지를 고려해야 한다.

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

9. 클러스터형 인덱스가 테이블에 아예 없는 것도 좋은 경우도 있다.(select는 거의 없고 대용량의 데이터가 계속 입력되는 시스템)

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