Index 란

DB의 검색을 빠르게 하기 위해 미리 데이터의 순서를 정리해두는 과정

Index는 MongoDB에서 데이터 쿼리를 더욱 효율적으로 할 수 있게 해준다.

인덱스가 없이는, MongoDB는 collection scan – 컬렉션의 데이터를 하나하나 조회 – 방식으로 스캔을 하게 된다.

모든 도큐먼트를 스캔해야되는 비효율성을 줄임

한 쿼리당 하나의 index 만 유효하다.

두개의 index가 필요하다면 복합 index를 사용하면 된다.

* B트리

: 내부적으로 B-Tree 알고리즘을 이용하여 인덱스를 구성한다.

사용시 주의할 점

: 모든 인덱스를 갱신해야 하기 떄문에 모든 쓰기 작업은 인덱스 때문에 더 오래 걸림

: Collection 당 최대 64개까지 인덱스를 지닐수있지만, 2~3개만 지니는게 좋다.

: 몽고디비의 인덱스는 RDBMS와 유사하게 작동함

: 인덱스 구축시 background 옵션을 사용하면, 비동기로 작업이 가능하긴하지만 느리다.

Index 종류 

기본 인덱스 _id

모든 MongoDB의 컬렉션은 기본적으로 _id 필드에 인덱스가 존재합니다. 만약에 컬렉션을 만들 때  _id 필드를 따로 지정하지 않으면 mongod드라이버가 자동으로 _id 필드 값을 ObjectId로 설정해준다.

Single(단일) 필드 인덱스

사용자가 지정 할 수 있는 단일 필드 인덱스가 있다.

Compound (복합)  필드 인덱스,

두개 이상의 필드를 사용하는 인덱스를 복합 인덱스라고 부른다. 다음 이미지와 같이 첫번째 필드 (userid)는 오름차순으로, 두번째 필드 (score)는 내림차순으로 정렬 해야 하는 상황이 있을때 사용한다.

Multikey 인덱스

필드 타입이 배열인 필드에 인덱스를 적용 할 때는 Multikey 인덱스가 사용됩니다. 이 인덱스를 통하여 배열에 특정 값이 포함되어 있는 document를 효율적으로 스캔

Geospatial(공간적) Index

지도의 좌표와 같은 데이터를 효율적으로 쿼리하기 위해서 (예: 특정 좌표 반경 x 에 해당되는 데이터를 찾을 때) 사용되는 인덱스

Text 인덱스

텍스트 관련 데이터를 효율적으로 쿼리하기 위한 인덱스

해쉬 (hashed) 인덱스

이 인덱스를 사용하면 B Tree가아닌 Hash 자료구조를 사용합니다. Hash는 검색 효율이 B Tree보다 좋지만, 정렬을 하지 않습니다.

인덱스 사용

인덱스 확인

> db.[컬렉션명].getIndexes()    //    Collection의 인덱스 확인

인덱스 생성

> db.[컬렉션명].ensureIndex({name:1})    //    1이면 오름차순, -1 이면 내림차순

인덱스 생성(백그라운드에서 생성, 비동기방식)

> db.[컬렉션명].ensureIndex({name:1},{background:true})
인덱스 생성시 오래걸리면 백그라운드에서 생산해야함.

인덱스 생성( 고유인덱스 ) 

> db.[컬렉션명].ensureIndex({name:1},{unique:true})    //    unique 속성을 지정해서 중복데이터가 저장되지 못하게 하여, 데이터 저장과 검색속도를 늘린다.

* 인덱스 생성( 중복데이터 삭제 )

> db.[컬렉션명].ensureIndex({name:1},{unique:true, dropDups:true})    //    Unique 하게 했을 때 이미 중복된 데이터가 있을 경우 중복되는 데이터는 삭제하고 인덱스를 저장한다

* Partial (부분적) 속성

> partial 속성은 document의 조건을 정하여 일부 document에만 인덱스를 적용 할 때 사용된다,.

partial 속성을 사용하면, 필요한 부분에만 인덱싱을 사용하여 저장공간도 아끼고 속도를 더 높일수 있다.

visitors 값이 1000 보다 높은 document에만 name 필드에 인덱스 적용

db.[컬렉션명].createIndex( { name: 1 }, { partialFilterExpression: { visitors: { $gt: 1000 } } } )

* ttl 속성 인덱스 생성

db.[컬렉션명].createIndex( { "notifiedDate": 1 }, { expireAfterSeconds: 3600 } )

예제: notifiedDate 가 현재 시각과 1시간 이상 차이나면 제거

document가 만료되어 제거 될 때, 시간이 아주 정확하지는 않다. 만료되는 document를 제거하는 thread는 매 60초마다 실행된다.

인덱스 삭제 

> db.[컬렉션명].dropIndex({name:1})    //    해당 인덱스 제거

* 인덱스 삭제( 모든 인덱스)

> db.[컬렉션명].dropIndexes()    //    _id를 제외한 모든 인덱스 제거

몽고디비 공식 문서 (인덱스 가이드)

docs.mongodb.com/manual/indexes/

mysql 에는 rownum 이라는 키워드가 없다,

그래서 변수 선언을 통해서 임의로 rownum 을 표시할 수 있다.

rownum 생성 방법

SELECT
  @rownum:=@rownum+1 as no
  table.*
FROM
  table 
where 
  (@rownum:0)=0

where 절에서 변수를 초기화 하고 사용하는 방법이다.

select 절보다 where 절에서 먼저 실행되기 때문에 where 절에서 변수 선운 후 사용 가능하다.

또는 아래와 같은 set 키워드로 변수를 선언 가능하다.

SET @rownum:=0;
SELECT
  @rownum:=@rownum+1 as no
  table.*
FROM
 table;

mysql Table 정보검색 및 column정보 검색

1 테이블 정보검색.

select * 

from INFORMATION_SCHEMA.tables 

where table_schema='데이터베이스명';

=> show tables;

2. 컬럼명 검색.

select * 

 from INFORMATION_SCHEMA.columns 

where table_schema='데이터베이스명' 

    and table_name='테이블명' 

 order by ordinal_position;

=> show full columns from 테이블명 ;

Select SQL 실행순서(오라클 SQL 기준)

1) FROM table(s) [alias]

- FROM 절에 사용된 테이블을 인식하여 데이터 딕셔너리에서 관련된 정보들을 파악

2) [WHERE condition(s)]

- WHERE절에서 조건에 맞는 데이터를 추출

3) [GROUP BY column(s)]

- GROUP BY절이 추가되면 GROUP BY절에 사용된 항목별로 데이터의 정렬이 일어남

4) [HAVING condition(s)]

- HAVING 절은 GROUP BY절로 정렬이 된 데이터를 대상으로 조건을 정의.

5) SELECT {*, column(s) [alias],...}

- 대부분의 RDBMS가 ROW(로우)기준 저장구조입니다. SELECT이전까지 원하지 않는 칼럼까지도 데이터베이스의 메모리에 저장.

6 [ORDER BY column(s) [alias] [DESC],.....];

- ORDER BY절이 가장 나중에 실행.

- SELECT절에서 선택되지 않은 칼럼이나 연산도 데이터베이스의 메모리에 저장되어있으므로 ORDER BY절에서 사용

정리하자면 다음과 같다.

1.FROM 절에서 테이블의 목록을 가져옴
2.WHERE 절에서 검색 조건에 불일치 하는 행 제외
3.GROUP BY 절에서 명시된 행의 값을 그룹화
4.HAVING 절이 GROUP BY 절의 결과 행 중 검색 조건에 불일치 하는 행 제외
5.SELECT 절에서 명시된 열을 정리 
6.ORDER BY 절에서 열을 기준으로 출력할 대상을 정렬 후 출력 

table

어떤 테이블에 대한 접근을 표시하고 있는지는 table 필드에 표시되어있다.

id

id는 SELECT에 붙은 번호를 말한다. MySQL은 조인을 하나의 단위로 실행하기 때문에 id는 그 쿼리에 실행 단위를 식별하는 것이다. 따라서 조인만 수행하는 쿼리에서는 id는 항상 1이 된다.

select_type

select_type은 항상 SIMPLE 이된다. 복잡한 조인을 해도 SIMPLE이 된다. 서브쿼리나 UNION이 있으면 id와 select_type이 변한다.

SIMPLE: 단순 select ( union이나 서브쿼리를 사용하지 않음 )

PRIMARY: 가장 외곽에 있는 select문

UNION: union에서의 두번째 혹은 나중에 따라오는 select문

DEPENDENT UNION: union에서의 두번째 혹은 나중에 따라오는 select문, 외곽 쿼리에 의존적이다.

UNION RESULT: union의 결과물

SUBQUERY: 서브쿼리의 첫번째 select

DEPENDENT SUBQUERY: 서브쿼리의 첫번째 select, 바깥 쪽 쿼리에 의존적이다.

DERIVED: from절의 서브쿼리

partitions

partitions는 파티셔닝이 되어 있는 경우에 사용되는 필드이다. 이 쿼리에서 사용된 테이블을 모두 파티셔닝이 되어 있지 않기 때문에 이 필드가 모두 NULL로 출력되았다. 파티셔닝 되어 있는 경우는 반드시 이 필드를 확인하자

type

type은 접근 방식을 표시하는 필드다. 접근 방식은 테이블에서 어떻게 행데이터를 가져올것인가를 가리킨다.

ALL, eq_ref는 조인시 기본 키나 고유키를 사용하여 하나의 값으로 접근(최대 1행만을 정확하게 패치), ref는 여러 개의 행을 패치할 가능성이 있는 접근을 의미한다.접근 방식은 대상 테이블로의 접근이 효율적일지 여부를 판단하는 데 아주 중요한 항목이다.

이들 접근 방식 가운데도 주의가 필요한 것은 ALL, index, ref_or_null이다.

ALL, index 두 가지는 테이블 또는 특정 인덱스가 전체 행에 접근하기 때문에 테이블 크기가 크면 효율이 떨어진다. ref_or_null의 경우 NUL이 들어있는 행은 인덱스의 맨 앞에 모아서 저장하지만 그 건수가 많으면 MySQL 서버의 작업량이 방대해진다. 다시 말해서 ALL 이외의 접근 방식은 모두 인덱스를 사용한다.

접근 방식이 ALL 또는 index인 경우는 그 쿼리로 사용할 수 있는 적절한 인덱스가 없다는 의미일 수도 있다. 위 쿼리에서 Country 테이블에 대한 접근은 ALL이지만 이는 WHERE 구의 조건을 지정하지 않았기 때문이다. 그러한 쿼리에서 드라이빙 테이블에 접근한다면 전체 행을 스캔 할수 밖에 없다.

접근 방식설명

possible_keys

possible_keys 필드는 이용 가능성있는 인덱스의 목록이다. [테이블 검색에 사용할 수 있는 인덱스]

key

possible_keys 필드는 이용 가능성있는 인덱스의 목록 중에서 실제로 옵티마이저가 선택한 인덱스가 key가 된다. 위 EXPLAN 에서는 County 테이블(첫 번째 행)의Key는 NULL 인데 이는 행 데이터를 가져오기 위해 인덱스를 사용할 수 없다는 의미이다.

실제 사용한 key 임

key_len

key_len 필드는 선택된 인덱스의 길이를 의미한다.

중요한 필드는 아니지만 인덱스가 너무 긴 것도 비효율적이므로 기억해두자.

rows

rows는 이 접근 방식을 사용해 몇 행을 가져왔는가를 표시한다. 최초에 접근하는 테이블에 대해서 쿼리 전체에 의해 접근하는 행 수, 그 이후에 테이블에 대해서는 1행의 조인으로 평균 몇 행에 접근했는가를 표시한다. 단 어디까지나 통계 값으로 계산한 값이므로 실제 행 수와 반드시 일치하지 않는다.

filtered

filtered는 행 데이터를 가져와 WHERE 구의 검색 조건이 적용되면 몇행이 남는지를 표시한다. 이 값도 통계 값 바탕으로 계산한 값이므로 현실의 값과 반드시 일치하지 않는다.

extra

Extra 필드는 옵티마이저가 동작하는데 대해서 우리에게 알려주는 힌트다. 이 필드는 EXPLAN을 사용해 옵티마이저의 행동을 파악할때 아주 중요하다.

참고문헌

https://cheese10yun.github.io/mysql-explian/ [MySQL 실행계획]

쿼리 실행 절차

1. 사용자로부터 요청된 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리한다.
2. SQL의 파싱 정보(파스 트리)를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다.
3. 두번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.

첫 번째 단계를 "SQL 파싱(Parsing)"이라고 하며, MySQL 서버의 "SQL 파서"라는 모듈로 처리합니다. 만약 SQL 문장이 문법적으로 잘못됐다면 이 단계에서 걸러집니다. 또한 이 단계에서 "SQL 파스 트리"가 만들어집니다. MySQL 서버는 SQL 문장 그 자체가 아니라 SQL 파스 트리를 이용해 쿼리를 실행합니다.

두 번째 단계는 첫 번째 단계에서 만들어진 SQL 파스 트리를 참조하면서, 다음과 같은 내용을 처리합니다.

불필요한 조건의 제거 및 복잡한 연산의 단순화  
여러 테이블의 조인이 있는 경우 어떤 순서로 테이블을 읽을지 결정  
각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스 결정  
가져온 레코드들을 임시 테이블에 넣고 다시 한번 가공해야 하는지 결정

물론 이 밖에도 수많은 처리를 하지만, 대표적으로 이런 작업을 들 수 있습니다. 두 번째 단계는 "최적화 및 실행 계획 수립" 단계이며, MySQL 서버의 "옵티마이저"에서 처리합니다. 또한 두 번째 단계가 오나료되면 쿼리의 "실행 계획"이 만들어집니다.

세 번째 단계는 수립된 실행 계획대로 스토리지 엔진에 레코드를 읽어오도록 요청하고, MySQL 엔진에서는 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 수행합니다.

옵티마이저의종류

1. 비용 기반 최적화(Cost-based optimizer, CBO)
2. 규칙 기반 최적화 방법(Rule-based optimizer, RBO)

비용 기반 최적화는 쿼리를 처리하기 위한 여러 가지 가능한 방법을 만들고, 각 단위 작업의 비용(부하) 정보와 대상 테이블의 예측된 통계 정보를 이용해 각 실행 계획별 비용을 산출한다. 이렇게 산출된 각 실행 방법별로 최소 비용이 소요되는 처리 방식을 선택해 최종 쿼리를 실행하게 된다.

규칙 기반 최적화는 각 테이블이나 인덱스의 통계 정보가 거의 없고, 상대적으로 느린 CPU 연산 탓에 비용 계산 과정이 부담스러웠기 때문에 사용되던 최적화 방법이다. 현재는 거의 대부분의 RDBMS가 비용 기반의 옵티마이저를 채택하고 있으며, MySQL 역시 마찬가지이다.

통계정보

MySQL에서 관리되는 통계 정보는 대략의 레코드 건수와 인덱스의 유니크한 값의 개수 정도가 전부이다. 오라클과 같은 DBMS에서는 통계 정보가 상당히 정적이고 수집에 많은 시간이 소요되기 때문에 통계 정보만 따로 백업하기도 한다. 하지만 MySQL에서 통계 정보는 사용자가 알아채지 못하는 순간순간 자동으로 변경되기 때문에 상당히 동적인 편이다. 하지만 레코드 건수가 많지 않으면 통계 정보가 상당히 부정확한 경우가 많으므로 "ANALYZE" 명령을 이용해 강제적으로 통계 정보를 갱신해야 할 때도 있다. 특히 이런 현상은 레코드 건수가 얼마 되지 않는 개발용 MySQL 서버에서 자주 발생한다.

MEMORY 테이블은 별도로 통계 정보가 없으며, MyISAM과 InnoDB의 테이블과 인덱스 통계 정보는 다음과 같이 확인할 수 있다. ANALYZE 명령은 인덱스 키값의 분포도(선택도)만 업데이트하며, 전체 테이블의 건수는 테이블의 전체 페이지 수를 이용해 예측한다.

MyISAM 테이블의 ANALYZE는 정확한 키값 분포도를 위해 인덱스 전체를 스캔하므로 많은 시간이 소요된다. 이와는 달리 InnoDB 테이블은 인덱스 페이지 중에서 8개 정도만 랜덤하게 선택해서 분석하고 그 결과를 인덱스의 통계 정보로 갱신한다.

https://12bme.tistory.com/160 [mysql 옵티마지이져원리 부터 실행계획까지]