- 저장 성능을 희생해 데이터의 읽기 속도를 높이는 기능
- 인덱스는 저장하는 Column 값을 항상 정렬된 상태로 유지
- 데이터 파일은 정렬되어 있지 않음 !
- InnoDB 데이터 파일은 클러스터 되어 PK 값을 기준으로 정령되어 있음
- 프라이머리 키(Primary Key , PK)
- 레코드를 대표하는 칼럼 값
- 식별자
- NULL, 중복 값을 가질 수 없음
- 보조 키 (Secondary Index, Secondary Key)
- PK 를 제외한 다른 키
- 유니크 인덱스도 여기에 포함시키기도 함
- B-Tree (Balanced Tree)
- 칼럼 값을 변경하지 않고 원래 값을 인덱싱
- R-Tree (Saptial Index)
- 위치 기반 검색 기능 지원
- Hash 인덱스
- 칼럼 값으로 해시 값을 계산해 인덱싱
- 칼럼 값이 변경됨
- 메모리 기반의 테이블에서만 사용 가능
- 값의 일부 검색, 범위 검색 불가능
- 유니크 인덱스
- 유니크하지 않은 인덱스
- 전문 검색용 인덱스 (Full-Text Search Index)
- 공간 검색용 인덱스 (Spatial Index)
- 가장 일반적, 가장 범용적
- Variation
- B+-Tree / B*-Tree
- 구성: 루트(필수) / 브랜치 / 리프(필수)
- 리프노드: 항상 실제 데이터 레코드를 찾아가기 위한 주소값을 가짐
- 스토리지 엔진에 따라 저장되는 주소의 종류가 다름
- MyISAM,MEMORY : ROWID (물리적인 주소를 저장) -> 바로 데이터 조회 가능
- InnoDB : PK 값 (논리적인 주소를 저장) -> PK 값으로 데이터를 다시 조회해야함
- 성능 이슈 ? 클러스터링 인덱스가 주는 이점이 더 크다 !
- 레코드(데이터)가 저장될 위치가 정해지면 키 값과 대상 레코드의 주소 정보를 리프 노드에 저장
- 리프 노드가 꽉 찬 경우 리프 노드가 분리(Split) 되야함
- 이는 상위 브랜치에 영향을 주게 되어 처리 범위가 넓어짐 -> 비용이 많이 듦
- 비용 : 레코드 저장 = 1 , 인덱스 추가 = 1.5
- 메모리, CPU 비용이 아닌 디스크 I/O 비용이 대부분
- MyISAM,MEMORY 스토리지 엔진의 경우 INSERT시 즉시 새로운 키 값을 B-Tree 인덱스에 변경
- InnoDB 스토리지 엔진의 경우 지연 처리 가능
- PK, Unique Key의 경우 중복확인을 위해 즉시 처리함
- 해당 키의 B-Tree 리프 노드를 조회 후 삭제 마크 처리
- 마크 처리를 위해 디스크 I/O 작업이 필요함
- InnoDB의 경우 지연처리 가능
- 키 값을 삭제한 후 새로운 키 값을 추가하는 형태로 처리
- InnoDB의 경우 지연처리 가능
- 삭제/생성의 절차는 위와 같음
- 인덱스의 생성 목적
- B-Tree의 특성을 이용해 빠르게 검색 가능
- InnoDB 에서는 갭락/넥스트 키 락을 이용해 인덱스를 잠그므로 인덱스 설계가 중요함
- Page ?
- InnoDB 스토리지 엔진이 데이터를 저장하는 기본 단위
- 디스크 I/O의 기본 단위
- 4~64KB 사이의 크기를 가짐 (기본값 : 16KB)
- 인덱스의 키 값에 따라 결정됨
- Page에 저장 가능한 레코드 수 = (Page 크기 / (인덱스 크기 + 메모리 주소 크기))
- 메모리 주소는 보통 6~12B
- ex) Page = 16KB, 인덱스 크기 = 32B, 메모리 주소 크기 = 12B
- 한 페이지에 저장 가능한 레코드 수 = 16*1024 / (32+12) = 372
- 이 때 한 번에 500개의 데이터를 읽어야 한다면 기본 단위가 1Page인데 1Page에 372개를 읽을 수 있으므로 2번을 읽어야 함
- 그러므로 인덱스 키 값이 커지면 한 Page에 저장 가능한 수가 줄어듦
- 같은 양의 데이터를 저장하기 위해서서는 인덱스 키 값이 작을 때보다 depth가 더 깊어짐
- 이는 디스크 I/O 비용이 증가함을 의미
- 직접 제어할 방법 X
- 키 값이 32B 인 경우 372개 가 저장이 가능함
- Depth = 3 이면 372372372 = 약 5천만
- Depth =3 ,키 값이 16B인 경우 585585585 = 약 2억
- 둘은 거의 비슷한 의미
- 모든 인덱스 가운데 유니크한 값의 수를 의미
- ex) 총 데이터 1000개, 유니크한 값은 10개 -> 기수성/선택도 = 10
- 중복된 데이터가 많을질수록 선택도/기수성 낮아짐
- 선택도가 높을 수록 검색 대상이 줄어들어 빠르게 처리된다.
- 옵티마이저는 인덱스를 통해 데이터를 읽는 것을 직접 레코드 1건 읽는 것보다 4~5배 비용이 드는 것으로 추정함
- 그러므로 조회하는 데이터가 전체 데이터의 20~25% 이상이라면 인덱스를 사용하지 않는 것이 더 빠를 수 있음
- 전체 테이블 스캔 후 필터링 하는 것이 효율적일 수 있다
- 인덱스 풀 스캔 / 루스 인덱스 스캔 보다 빠름
- 검색해야 할 인덱스의 범위가 결정된 경우 사용
- 검색하는 인덱스의 정순/역순으로 데이터가 정렬 됨
- 데이터 파일에서 데이터를 읽어오는 과정이 필요함 (주소만 저장되어 있기 때문)
- 이 때 한 건 단위로 랜덤I/O 작업이 일어남
- 그렇기 때문에 읽어야할 데이터가 20~25% 넘으면 테이블을 직접 읽는 것이 효율적이라고 판단함
- 탐색 과정
- 인덱스에서 조건을 만족하는 값이 저장된 위치 탐색 (인덱스 탐색)
- 1번에서 탐색된 위치부터 필요한 만큼 인덱스를 읽음 (인덱스 스캔) -> 1,2 과정을 통칭해 인덱스 스캔이라고도 함
- 2번에서 읽은 인덱스 키,레코드 주소를 이용해 실제 데이터를 가지고 온다.
- 커버링 인덱스를 사용하는 켱우 3번 과정에서 디스크 I/O가 없어 랜덤 읽기 수가 줄어들어 성능이 빨라진다.
- 인덱스의 처음부터 끝까지 모두 읽음
- 쿼리 조건절에 사용되는 칼럼이 인덱스의 첫 번째 칼럼이 아닌 경우 사용 됨 ( 다중 컬럼 )
- 쿼리가 인덱스에 명시된 칼럼만을 조건을 처리할 수 있는 경우에 이 방식이 사용된다.
- ex) 인덱스는 (A,B,C/다중 컬럼 인덱스) 인데 B,C로 검색하는 경우
- SELECT B,C FROM T WHERE B = 1 AND C = 2
- SELECT * FROM T WHERE B = 1 AND C = 2 인 경우 인덱스에 명시되지 않은 데이터도 조회하므로 인덱스 풀 스캔을 하지 않을 것이다.
- 일반적으로 인덱스의 크기가 테이블 크기보다는 작기 때문에 테이블 풀 스캔 보다 효율적이다.
- 인덱스 풀 스캔은 테이블 전체를 읽는 것보다 적은 디스크 I/O
- 인덱스에 포함된 칼럼만으로 쿼리를 처리할 수 있는 경우 테이블을 레코드를 읽지 않기 때문
- MySQL 8.0 부터 최적화 됨
- 인덱스 레인지 스캔과 비슷하게 작동
- 중간에 필요치 않은 인덱스 키 값은 무시
- 주로 GROUP BY, MIN, MAX 최적화에 사용
- ex) 인덱스 = (A,B) 다중 컬럼 인덱스
- SELECT A,MIN(B) FROM T WHERE A BETWEEN 1001 AND 1003 GROUP BY A;
- 다중 컬럼 인덱스에서 첫 번째 키 값을 제외하고 두 번째 키 값을 조회할 수 있도록 도와주는 스캔 방식
- 인덱스 = (gender, age)인 경우
- SELECT age,gender FROM human WHERE age >= 20;
- SELECT age,gender FROM human WHERE gender = 'M' AND age >= 20;
- SELECT * FROM human WHERE gender = 'M' AND age >= 20;
- 1번 쿼리는 8.0 이전 버전에서는 인덱스 사용이 안 되었음 ( 인덱스 풀 스캔 / SELECT * 인 경우 테이블 풀 스캔)
- 루스 인덱스 스캔은 주로 GROUP BY 작업을 처리하는 경우에만 사용 되었었기 때문
- 2번 쿼리는 복합 인덱스의 두 키를 모두 명시 했으므로 인덱스가 사용됨
- 8.0 이후 버전에서는 1번 쿼리도 인덱스 사용이 가능해짐
- 단 스킵된 첫 번째 키 값이 많을 수록 성능이 떨어짐 (첫 번째 키의 기수성/선택도가 낮을 수록 좋음)
- 스킵된 첫 번째 키의 Unique 값을 모두 두 번째 키와 조합해 조회함
- 위의 경우 gender = 'M' AND age >= 20 / gender = 'F' AND age >= 20 이 두 개의 쿼리를 실행한 결과를 합친 것과 같음
- 3번의 경우 인덱스 외의 다른 컬럼도 필요하므로 테이블 풀 스캔을 하게 된다.
- 두 개 이상으로 구성된 칼럼
- N+1 번째 칼럼은 N 번째 칼럼에 의해 정렬된다.
- ex) (A,B,C) 인덱스
- A로 정렬 -> A가 같은 경우 B로 정렬 -> B가 같은 경우 C로 정렬
- 인덱스는 항상 정렬된 상태를 유지한다.
- 8.0 부터는 다중 컬럼에서 오름/내림차순을 섞어서 인덱스 생성이 가능해졌다.
- ex) CREATE INDEX ix_teamname_userscore ON emploees (teamname ASC, userscore DESC);
- 스캔 방향은 정순/역순이 존재한다.
- 이는 오름/내림 차순과는 다르다.
- 인덱스 방향과 같으면 정순 / 반대면 역순
- 오름차순 인덱스의 경우 정순:오름차순 / 역순: 내림차순
- 내림차순 인덱스의 경우 정순:내림차순 / 역순: 오름차순
- 정순 스캔이 역순 스캔에 비해 빠르다 ( 약 20~30% )
- 페이지 잠금이 정순 스캔에 적합하기 때문
- 페이지 내에서 인덱스 레코드가 단 방향으로 연결된 구조이기 때문
- 그러므로 조회시 사용되는 쿼리를 조사해 인덱스의 정렬 방향을 결정하는 것이 중요하다. (정순 스캔을 더 많이 사용하도록)
- 작업 범위 결정 조건 : 작업의 범위를 결정하는 조건 / 범위를 좁히는데 사용되는 조건
- 많으면 많을 수록 성능 향상에 영향을 줌
- 인덱스를 효율적으로 사용한 경우임
- 다른 DBMS와는 다르게 NULL 값도 인덱스에 포함되기 때문에 작업 범위 결정 조건으로 사용된다.
- 체크 조건 / 필터링 조건 : 작업 결과를 필터링하는 조건
- 성능 향상에 영향을 주지 않음 / 오히려 악영향을 주는 경우가 많다
- 인덱스를 효율적으로 사용하지 못한 경우임
- 인덱스 = (teamname, userscore) 인 경우
- SELECT * FROM employees WHERE teamname = 'A' AND userscore >= 80;
- SELECT * FROM employees WHERE userscore >= 80 AND teamname = 'A';
- 1번 쿼리는 인덱스를 효율적으로 사용한 경우
- teamname = 'A' 조건으로 인덱스의 범위를 좁히고 userscore >= 80 조건으로 필터링
- 2번 쿼리는 인덱스를 효율적으로 사용하지 못한 경우
- userscore >= 80 조건으로 인덱스의 범위를 좁히지 못하고 teamname = 'A' 조건으로 필터링
- 사용한다 : 작업 범위 결정 조건으로 사용된다.
- NOT EQUAL 조건
- LIKE %문자열 조건
- 인덱스 칼럼이 변형된 후 비교된 경우 (가상 인덱스, 함수 인덱스를 사용해야함)
- NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우
- WHERE column = deterministic_function(column)
- NOT-DETERMINISTIC 함수 : 동일한 입력값에 대해 같은 결과를 보장하지 않는 함수
- 함수가 외부 변수나 데이터베이스 상태에 의존
- 데이터 타입이 서로 다른 비교 ( 타입을 변환해야 비교가 가능한 경우 )
- 문자열 데이터의 collation이 다른 경우
- INDEX ix_test (col_1,col_2, col_3,... col_n)
- 작업 범위 결정 조건으로 인덱스를 사용하지 못하는 경우
- col_1 칼럼에 대한 조건이 없는 경우
- col_1 칼럼의 비교 조건이 인덱스 사용 불가능 조건인 경우
- 작업 범위 결정 조건으로 인덱스를 사용하는 경우
- col_1 ~ col_(i-1) 칼럼까지 동등비교 형태 또는 IN (= , IN)
- col_i 칼럼에 대해 아래 연산자를 사용하는 경우
- 동등 비교 ( = / In )
- 크다 작다 비교
- LIKE 좌측 패턴 일치
- col_i+1 ~ col_n은 체크 조건으로 동작
- 2차원의 공간 개념 값을 저장한다
- 공간(Spatial) 인덱스라고도 함
- 공간 데이터를 조회하는데 사용
- 주로 위도 경도 좌표 저장에 사용, 좌표 시스템에는 모두 적용 가능
- MySQL 3가지 기능을 제공함
- 공간 데이터 저장
- 공간 인덱스 알고리즘
- 공간 데이터 연산 함수
- 공간 데이터
- POINT, LINE, POLYGON, GEOMETRY
- MBR (Minimum Bounding Rectangle)
- 공간 데이터를 감싸는 가장 작은 사각형
- MBR을 이용해 공간 데이터를 저장하고 인덱싱
- 인덱싱 방식
- MBR의 포함 관계를 이용해 만들어진 인덱스
- 최하위 레벨 : 단일의 도형의 MBR
- 차상위 레벨 : 최하위 레벨의 MBR을 감싸는 MBR
- 최상위 레벨 : R-Tree 루트 노드에 저장되는 정보 차상위를 포함
- ST_Contains,ST_Within 등의 포함 관계를 비교하는 함수를 이용해서 인덱스 사용 가능
- 거리비교 함수는 인덱스 사용 불가능 (ST_Distance,ST_Distance_Sphere 등)
- ST : Spatial and Temporal (공간 및 시간의 약자)
- 일반적인 인덱스는 키 값이 큰 경우 일부만 잘라서 인덱스로 사용함
- InnoDB 로우 포맷 변수에 따라 767 ~ 3072 Byte 까지 가능함
- InnoDB Default 값 : 3072 Byte
- 어근 분석 알고리즘
-
과정
- 불용어(Stop Word) 처리
- 어근 분석 (Stemming)
-
불용어 처리
- 가치가 없는 단어 제외하는 작업
- MySQL에서 기본으로 제공하지만 별도로 정의할 수 있는 기능 제공함
-
어근 분석
- 단어의 뿌리인 원형을 찾는 작업
- 한국어/일본어의 경우 어근보다는 형태소로 분리함
- 오픈소스인 MeCab 플러그인 등을 사용해 형태소 분석을 수행함
-
단점 : 완성도를 갖추는 작업에 시간과 노력이 필요하다
- n-gram 알고리즘
-
어근 분석 알고리즘 보다 범용적이고 간편하다
-
단어를 n개의 문자로 나누어 인덱싱하는 방식
-
주로 2-gram (Bi-gram) 방식이 많이 사용됨
-
N글자 단어는 N-1 개의 토큰으로 분해됨
-
ex) Real MySQL
- Real -> Re ea al
- MySQL -> My yS SQ QL
-
각 토큰을 인덱스에 저장
-
과정
- 토큰화
- 불용어 처리
- 불용어 처리 무시할 수 있다.
- MySQL 서버 전체의 불용어를 완전히 삭제
- InnoDB 스토리지 엔진의 불용어 무시
- 사용자 정의 불용어 사용
- 불용어 목록 파일을 저장 후 등록
- InnoDB 스토리지 엔진 테이블 전문 검색 엔진에서만 변경
- 전문 검색을 위한 문법을 사용해야 한다.
- MATCH... AGAINST... 문법을 사용해야 한다.
- 테이블이 전문 검색 대상 칼럼에 대해서 전문 인덱스 보유
- CREATE FULLTEXT INDEX ix_full_movie ON movie(title,content);
- MATCT ... AGAINST ... 사용 방법
- 자연어 처리 : 자연스럽게 포함된 문서 조회
- WHERE MATCH(title, content) AGAINST('database optimization' IN NATURAL LANGUAGE MODE);
- 불리언 모드 : 특정 키워드의 포함 불포함 여부 검색
- WHERE MATCH(title, content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);
-
- : 포함 / - : 불포함
- 쿼리 확장 : 사용자의 초기 검색어를 바탕으로 관련 키워드를 자동으로 찾아 검색을 확장
- WHERE MATCH(title, content) AGAINST('security' WITH QUERY EXPANSION);
- MySQL 8.0 부터 지원
- 종류
- 가상 칼럼 인덱스
- 함수를 이용한 인덱스
- 테이블 구조의 변경이 필요
- 가상 칼럼을 생성하고 인덱스를 생성함
- ALTER TABLE employees ADD COLUMN fullname VARCHAR(100) AS (CONCAT(firstname,' ',lastname)) VIRTUAL;
- CREATE INDEX ix_fullname ON employees(fullname);
- 테이블 구조 변경 X
- 반드시 조건 절에 함수 기반 인덱스에 명시된 표현식이 그대로 사용되야 한다.
- CREATE INDEX ix_fullname ON employees((CONCAT(firstname,' ',lastname)));
- 하나의 데이터 레코드에 여러 개의 키 값을 가지는 형태의 인덱스
- MySQL은 원래 JSON을 사용하던 MongoDB와 비교해도 부족함 없는 JSON 관리 기능을 제공
- JSON 데이터를 사용해 멀티 밸류 인덱스를 생성할 수 있다.
- 반드시 아래 함수들을 이용해서 멀티 밸류 인덱스 사용 가능
- MEMBER OF
- 주어진 값이 JSON 배열의 멤버인직 확인
- JSON_CONTAINS
- 해당 요소를 포함하는지 확인
- WHERE JSON_CONTAINS(tags, '"organic"');
- JSON_OVERLAPS
- 하나 이상의 공통 요소를 포함하는지 확인
- WHERE JSON_OVERLAPS(tags, '["organic", "new"]');
- MEMBER OF
-
CREATE TABLE products (
-
id INT AUTO_INCREMENT PRIMARY KEY,
-
name VARCHAR(100),
-
tags JSON
-
);
-
멀티 밸류 인덱스 생성
-
CREATE INDEX idx_tags ON products( (CAST(tags->'$.tags.name' AS CHAR(255) ARRAY)) );
-
조회
-
'organic' 태그가 있는 모든 제품 검색
-
SELECT * FROM products WHERE 'organic' MEMBER OF (tags-> '$.tags.name')) ;
- InnoDB 스토리지 엔진에서만 사용 가능
- InnoDB는 항상 클러스터링 인덱스로 저장한다.
- PK 값을 기준으로 테이블의 레코드를 비슷한 것들끼리 묶어서 저장
- PK 값에 의해 레코드의 물리적인 저장 위치가 결정된다.
- 인덱스 알고리즘이라기 보다 레코드의 저장 방식이라고 볼 수 있다.
- PK 값이 변경되면 레코드의 물리적인 저장 위치가 바뀐다.
- 데이터가 인덱스와 같은 순서로 저장된다.
- PK 값에 기반해 데이터에 접근하기 때문에 키 값을 사용하는 쿼리는 매우 빠르게 데이터를 찾을 수 있다.
- 다른 인덱스는 주소를 저장하는 것과 다르게 실제 레코드의 모든 칼럼을 저장한다.
- InnoDB 테이블은 하나의 클러스터링 인덱스만 가질 수 있다.
- PK로 정렬된다.
- 리프 노드에 실제 데이터가 저장된다.
- PK
- NOT NULL 옵션의 Unique Key
- 자동 증가 유니크 값을 가지는 키를 생성후 클러스터링 키로 선택
- 결구 AUTO INCREMENT PK와 같으나 조회가 불가능함
- 그러므로 가능하면 PK를 만들어 사용하자
- PK로 조회하는 경우 매우 빠름
- 보조키들이 PK 값을 가지기 때문에 인덱스로만 처리되는 경우가 많음
- 보조키들이 전부 클러스터링 키 값을 가지기 때문에 PK 값이 크다면 전체적으로 인덱스 크기가 커짐
- 보조키로 조회하는 경우 PK 값으로 다시 조회해야 하므로 성능이 느림
- INSERT시 PK에 의해 레코드 저장 위치가 결정되기 때문에 처리 성능이 느림
- PK 변경시 레코드를 삭제 후 삽입하기 떄문에 처리 성능이 느림
- InnoDB의 클러스터링 인덱스는 리프 노드에 키 값과 함께 데이터를 저장한다.
- 다른 스토리지 엔진은 인덱스와 데이터를 별도로 저장한다.
- 그렇기 때문에 클러스터링 인덱스에서 PK를 이용해 데이터를 조회하면 추가적은 디스크I/O 없이 데이터 베이스에 접근하기 때문에 빠르다 !
- 그리고 클러스터링 인덱스는 PK 순서대로 데이터가 물리적으로 정렬되어 저장하기 때문에 물리적으로 정렬되어 있지 않은
- 다른 스토리지 엔진에 비해 범위 탐색이 굉장히 빠르다.
- 유니크 인덱스는 중복을 허용하지 않는 인덱스
- 성능이 좋아지는 것은 아님
- 자동으로 인덱스를 생성하기 때문에 복수로 생성할 필요 없다
- 데이터 생성시 중복 체크를 하기 때문에 느림
- 중복 체크 : 읽기 잠금 / 쓰기 : 쓰기 잠금
- 데드락 빈번히 발생
- 외래키는 다른 테이블의 PK를 참조하는 칼럼
- 연관 테이블의 칼럼에 인덱스를 생성함
- 외래키가 제거되지 않으면 인덱스 삭제 불가능
- 자식/부모 데이터 변경 시에 잠금을 걸기 때문에 쿼리 동시 처리 성능이 떨어진다.
- 외래키 제약 조건을 사용하면 데이터 무결성을 보장할 수 있다.
- 인덱스 키 값은 어떻게 계산하나요 ?
- utf8mb4 : 4byte
- utf8 : 3byte