Mysql 인덱스 사용법 및 실행 계획 정리

데이터베이스/mysql|2020. 6. 10. 19:18

mysql 인덱스에 대한 정확한 이해도 없이 사용을 하다보니 조금 개념적으로 헷갈리는게 많이 있었다. 이 부분에 대해 한번 정리하고 넘어가고자 기록해본다.

 

인덱스


인덱스는 빠르게 특별한 컬럼과 함께 값을 찾는데 사용된다. 인덱스가 없으면 Mysql은 처음 행부터 전체 테이블을 읽어 들여서 데이터를 찾는다. 거대한 테이블에서 이런 행동은 비용이 상당히 많이 들어가게 된다. 만약에 테이블이 인덱스를 가지고 있으면 빠르게 접근할 수 있게 된다.

대부분의 Mysql 인덱스 (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT)는 B-tree안에 저장된다. 예외적으로 spatial 데이터 타입은 R-tree를 사용, 메모리 테이블은 또한 hash index를 지원, InnoDB는 FULLTEXT 인덱스를 위해 inverted list를 사용한다.

 

 

인덱스 동작 방식


- 행을 찾기 위해서 매칭되는 WHERE 구문을 빠르게 찾는다.

 

- 조건으로 부터 불필요 행을 제거한다. 만약에 여러 인덱스가 있는 경우 Mysql은 가장 적은 수의 행을 사용하는 인덱스를 선택한다. (Mysql은 한번에 하나의 인덱스만 사용할 수 있다.)

 

- 만약 테이블이 multiple column 인덱스를 가지고 있으면 인덱스의 가장 왼쪽에 컬럼을 사용하여 옵티마이저를 통해 행을 찾는다. 예를 들어 만약에 (col1, col2, col3)을 사용하는 인덱스가 있는 경우 인덱스는 이 순서로 검색을 진행한다. (col1), (col1, col2), (col1, col2, col3)

 

- 조인이 있는 경우 다른 테이블에서 행을 찾는다. Mysql은 동일한 유형과 사이즈로 되어 있는 열을 index로 사용할 때 더욱 효과적으로 행을 찾는다. VARCHAR, CHAR는 두개를 같은 사이즈로 명시 하였을 경우에 같은 타입으로 고려되어 사용된다. 예를 들어 VARCHAR(10) = CHAR(10)이지만 VARCHAR(10) ≠ CHAR(15)는 같지 않다.

 

- binary가 아닌 문자열 사이를 비교하기 위해서는 동일한 문자열 집합을 사용해야한다. utf8과 latin1열을 비교할 경우 인덱스를 사용할 수 없다.

 

- 타입이 다른 문자열과 숫자 등을 비교하려고 할때도 마찬가지로 인덱스를 탈 수 없다. (묵시적 형변환)

 

- index로 사용되는 key_col에서 min(), max()의 값을 찾기 위해 인덱스에서 key_col 이전에 발생하는 모든 키 파트에서 WHERE key_part_n = const를 사용하는지 여부를 확인하기 위해서 전처리기를 통해서 최적화가 진행된다. 이 경우에서 Mysql은 각 min() 또는 max() 표현식에 대해 단일키 조회를 수행하여 상수를 대체한다. 모든 표현식이 상수로 바뀌고 나면 쿼리가 한번에 반환된다.

SELECT MIN(key_part2),MAX(key_part2)
  FROM tbl_name WHERE key_part1=10;

 

 

실행계획


- 실행계획 필드 정리

  • Id
    • Select 구문 구분 ID
  • select_type
    • SIMPLE : 단순 SELECT
    • DERIVED : 서브 쿼리 중 가장 안쪽에 있는 쿼리 
    • PRIMARY : 서브 쿼리 바깥쪽에 있는 쿼리
    • DEPENDENT SUBQUERY  : 조건절 내부에서 외부 쿼리와 연결된 SELECT 
// PRIMARY
SELECT * FROM ( 
    // DERIVED : 서브 쿼리 중 가장 안쪽에 있는 쿼리
    SELECT * FROM timeline_item ti
) tt;


// DEPENDENT SUBQUERY
SELECT * FROM timeline_item t1 WHERE EXISTS ( SELECT * FROM timeline_site t2 WHERE t1.id = t2.id)
  • table

    • 참조하는 테이블 이름
  • type

    • 조인 혹은 조회 타입 (아래로 갈수록 성능 하락)
      1. System : 테이블에 데이터가 하나만 있는 경우
      2. const : SELECT에서 Primary Key 혹은 Unique Key를 상수로 조회하는 경우
      3. eq_ref : 조인할 때 Primary, Unique Key로 매칭하는 경우
      4. ref : 조인할 때 Primary, Unique Key로 매칭하지 않은 경우
      5. ref_or_null : ref와 같지만 NULL이 추가되어 검색된 경우
      6. index_merge : 두개의 인덱스가 병합되어 검색이 된경우
      7. unique_subquery : In절 내부 서브쿼리에서 Primary Key가 있는 경우
      8. index_subquery : In절 내부 서브쿼리에서 Primary Key가 아닌 인덱스가 있는 경우
      9. range : 특정 범위 내에서 인덱스를 사용하여 데이터 추출 하는 경우
      10. index : 인덱스를 처음부터 끝까지 찾아서 검색하는 경우로 일반적인 인덱스 풀스캔
      11. all : 테이블 풀스캔
  • possible_keys

    • 데이터 조회 시 DB에서 사용할 수 있는 인덱스 리스트
  • key

    • 실제로 사용할 인덱스
  • key_len

    • 실제로 사용할 인덱스 길이
  • ref

    • key 안의 인덱스와 비교하는 컬럼(상수)
  • rows

    • 쿼리 실행 시 조사하는 행수
  • extra

    • 추가 정보 (데이터가 많고 Using filesort, Using temporary 상태가 나온다면 무조건 최적화 필요)

      1. Using Index

        커버링 인덱스라고 하며 인덱스 자료구조를 이용하여 데이터를 추출

      2. Using where

        where 조건으로 데이터를 추출 (Type이 All 또는 Index와 같이 표현될 시 성능이 안좋다는 뜻)

      3. Using filesort

        데이터 정렬이 필요한 경우로, 메모리 혹은 디스크 상에서의 정렬을 모두 포함 (데이터 많을 시 성능 하락)

      4. Using Temporary

        쿼리 처리 시 내부적으로 Temporary 테이블이 사용됨

 

 

WHERE 조건문 주의사항


  • 묵시적 형변환에 조심하라
  • 무턱되고 함수를 사용하면 옵티마이저가 데이터 분포도 체크를 하지 못하기에 사용하지 말 것
// 함수 사용
SELECT * FROM timeline_item ti WHERE DATE_FORMAT(modified_at, '%Y%m%d') <= '20200402'

// 대안방안
SELECT * FROM timeline_item ti WHERE modified_at <= '2020-04-02'
  • Like 검색은 % 위치에 따라 다르게 수행 된다. %123, %125%의 경우에는 데이터 풀 스캔이 발생된다. 하지만 12312%와 같은 경우에는 인덱스를 사용해서 진행됨. 하지만 1%등과 같이 데이터 분포도에 비해 너무 추상적으로 %를 사용하게 될 경우에는 옵티마이저가 인덱스 사용과 데이터 풀스캔의 효율성 판단 했을 때 풀스캔이 효율적이다고 생각하고 데이터 풀스캔이 실행된다. 

댓글()

Elasticsearch에서 Paging시 max_result_window 초과시 조회가 안되는 이슈

엘라스틱 서치에서 데이터를 paging 하여 조회할때 from과 size를 사용한다. 

from은 시작 지점을 이야기하고 size는 그 시작 지점으로 부터 몇 개의 데이터를 보여주어야 하는 건지 설정할 때 사용 되는 값이다. 그래서 계산 방법은 다음과 같다.

from : (page - 1) * size 

size : size


그럼  만약 3개씩 보여주는 페이지에서 2번째 페이지를 보여주기 위해서는 from은 3, size는 3으로 설정하면 된다.

1
2
3
4
5
6
7
8
9
GET wedul/_search
{
  "from": 3, 
  "size": 3, 
  "query": {
    "match_all": {}
  }
}
 
cs


그럼 만약 wedul 페이지를 접근하다가 다음과 같이 Document의 숫자가 10000을 넘어가게 되면 어떻게 될까? 쿼리를 사용해서 조회를 해보자.

1
2
3
4
5
6
7
8
9
GET wedul/_search
{
  "from": 9999, 
  "size": 3, 
  "query": {
    "match_all": {}
  }
}
 
cs


정상적인 결과가 나오지 않고 query_phase_execution_exception에러가 발생하고 다음과 같은 에러문구를 출력한다.

1
Result window is too large, from + size must be less than or equal to: [10000] but was [10002]. See the scroll api for a more efficient way to request large data sets. This limit can be set by changing the [index.max_result_window] index level setting.
cs


이유가 몰까? 이유를 잘 몰라서 엘라스틱 서치 공식홈페이지 문서를 뒤져봤다. 

엘라스틱서치에서 인덱스를 생성할 때 기본적으로 하나의 노드에 5 섯개의 shard가 생성되고 그 shard는 데이터를 나누어서 저장한다. 물론 그 데이터를 복제하고 있는 primary shard도 1로 설정되는데 이는 primary shard가 한개라는 뜻이 아니라 각 shard 마다 복제 shard가 한 개씩 존재한다는 의미이다, 

다시 본점으로 돌아가서 노드에 분리되어있는 파티션 shard들에 데이터가 분산되어 들어간다. 그래서 만약 5섯개의 shard가 있는 노드에 위치한 index에서 1 ~ 10 개의 데이터를 찾는다면 각 shard에서 10개의 데이터를 찾고 모아서 정렬작업을 한 후 50개의 데이터에서 1 ~ 10까지의 데이터를  반환한다.

그럼 10000개의 데이터라면? 각 shard에서 10000개의 데이터를 가져와서 모으고 그것을 정렬할 것이다. 그래서 총 50000개 이상의 데이터를 모아야 하고 그것을 정렬해야하기 때문에 성능적인 문제를 야기할 수 있다. 그래서 엘라스틱서치의 기본 검색 제한 document의 값은 10000이고 이 설정값 이름은 max_result_window이다.

이 값은 아래의 쿼리를 사용해서 원하는 대로 50000까지 설정할 수 있다. 하지만 근본적으로 10000을 넘게 조회하게되면 많은 리소스 사용으로 성능문제를 야기할 수 있기 때문에 함부로 설정값을 바꿀것이아니라 검색을 10000개가 한번에 되지 않도록 검색조건을 잘 분할해서 지정해야한다.

1
2
3
4
PUT your_index_name/_settings
  "max_result_window" : 500000 
}
cs


참조 

https://www.elastic.co/guide/en/elasticsearch/guide/current/pagination.html

https://www.elastic.co/guide/en/elasticsearch/guide/current/_fetch_phase.html

댓글()

Docker Container에 Elasticsearch와 데이터 시각화 kibana 설치 및 연동

회사에서 사용하는 Elasticsearch 공부를 위해서 docker에 설치해보고 시각화에 도움주는 Kibana도 같이 설치해보자.

우선 Elasticsearch에 대한 기본 정보는 API 문서에서 확인할 수 있다.
https://www.elastic.co/guide/kr/elasticsearch/reference/current/gs-index-query.html


Elasticsearch 설치

해당 이미지에는 xpack도 포함되어있다. xpack은 보안, 알림, 모니터링, 보고, 그래프 기능을 설치하기 편리한 단일 패키지로 번들 구성한 Elastic Stack 확장 프로그램이다.


우선 이미지를 내려받는다.

1
docker pull docker.elastic.co/elasticsearch/elasticsearch-platinum:6.0.0
cs

그리고 내려받은 이미지를 이용하여 Elasticsearch를 conatiner에 올려서 실행시킨다.

1
docker run --9200:9200 -9300:9300 -"discovery.type=single-node" -"transport.host=127.0.0.1" ---name elastic docker.elastic.co/elasticsearch/elasticsearch-platinum:6.0.0 && sleep 20
cs

그리고 xpack 설치를 진행하기 위해서 우선 해당 컨테이너 bash 쉘을 실행시킨다.

1
2
// bash shell 열기
docker exec -it elastic /bin/bash
cs

그리고 xpack을 설치한다.

1
bin/elasticsearch-plugin install x-pack
cs


마지막으로 Elasticsearch에서 자동 색인 생성을 비활성화 해준경우에 xpack에서 다음 색인을 생성할 수 있도록 elasticsearch.yml에 설정해준다.

1
action.auto_create_index: .security,.monitoring*,.watches,.triggered_watches,.watcher-history*
cs


그러고 http://localhost:9200에 들어가면 정상적으로 설치된것을 확인할 수 있다. (계정입력하는 화면이 나오면 elastic / changeme 정보를 이용해서 사용한다.

Kibana 설치

Docker 이미지 다운

1
docker pull docker.elastic.co/kibana/kibana:6.0.0
cs

container에 이미지 올리기

1
docker run -d --rm --link dazzling_mayer:elastic-url -e "ELASTICSEARCH_URL=http://elastic-url:9200" -p 5601:5601 --name kibana docker.elastic.co/kibana/kibana:6.0.0 && sleep 20
cs


Index 추가


처음 접속하면 elasticsearch에서 사용하는 index의 이름을 입력하라고 한다. 패턴을 입력하면 되는데 만약 날짜마다 인덱스가 생성되는 구조면 밑에 TimeFilter를 설정을 같이해주고 그게 아니라면 customer* 이런식으로만 지정해줘도 된다.


댓글()

인덱스 기본 사용법과 인덱스 스캔이 불가능한 경우 소

우리가 색인을 통해 단어를 찾는 순간을 생각해보자.

ㄱ.

가나

가방 장식

가시 방석

ㄴ.

나방

나방 나무

누에고치

나무 장식

누나


여기서 누에고치라는 단어를 찾을 때, 위에서 순차적으로 진행한다고 가정하였을 때 큰 어려움 없이 발견할 수있다. 이 방식을 Index Range Scan이라고 한다.

반대로 장식이 포함된 단어를 찾아보자. 찾기 어려운 건 아니여도 모든 색인을 전부 확인해봐야한다. 이렇게 모든 색인을 다 확인하고 나서 찾을 수 있는 방식을 Index Full Scan 방식이라고 한다.

그렇기 때문에 인덱스의 기준이되는 데이터 즉 컬럼을 가공하게되면 Range Scan이 불가능해진다. 정리하면 인덱스 기준이 가공되면 인덱스 스캔의 시작점을 찾는 수직적 탐색이 불가능해지기 때문이다.


몇 가지 쿼리를 예로 들어보자.


1
2
3
4
create table student (
name varchar2(255),
birth date);
cs


학생 테이블이 있을 때 생일이 1월로 시작하는 사용자를 찾기위해 다음과 같은 쿼리를 사용한다고 가정해보자.


1
select * from student where substr(birth, 52= '01';
cs



어디서 부터 스캔을 멈춰야할지 인덱스 스캔을 할 수가 없다. 또 다른 예를 들어서 확인해보자.



1
2
3
select * from student where nvl(birth, CURRENT_DATE) < '2018-08-12';
 
select * from student where name like '%edu%';
cs



어디서 인덱스 스캔을 멈추어야하는지 알 수없기 때문에 마찬가지로 인덱스 스캔을 진행할 수가 없다.





OR이나 IN절의 경우에는 내부적으로 UNION ALL으로 내부적으로 나뉘어서 각자 자신의 쿼리를 사용하여 인덱스를 사용할 수 있다.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
where 전화번호 in ( tel_no1, tel_no2 )
 
 
 
                |
// 아래와 같이 변경
                ▽
 
 
 
select * from 고객 where 전화번호 = tel_no1
 
union all 
 
select * from 고객 where 전화번호 = tel_no2

cs


댓글()

인덱스 구조 및 탐색

SQL 튜닝에서 사용되는 인덱스

인덱스 구조는 수평적 탐색과 수직적 탐색을 이룬다. 이것을 이해하고 나면 인덱스 구조에 대해 그림이 명확해진다.

인덱스를 우리가 중심적을 공부하는 이유는 SQL 튜닝에 초점이 맞쳐져있다. SQL 튜닝을 통해 원하고자 하는 데이터를 빠르게 얻기 위해서 인덱스에 대해 공부하는데 SQL을 사용하여 데이터를 찾는 방법은 이미 많이 알려진 두 가지 방식이 있다.

  • 테이블 전체 스캔
  • 인덱스를 이용

 

그럼 Table Full Scan 방식이 아닌 인덱스를 사용하기 위해 적절 한 튜닝의 핵심요소가 무엇인지 살펴보자.

만약 몸무게가 기재되어있는 학생 연명부가 있다고 가정해보자. 학생명과 몸무게로 정렬되어 있을때 학생을 찾기 위한 과정에서 학생을 찾고 몸무게 검색하는 과정이 거친다. 이런과정을 인덱스 스캔이라고 하는데, 이런 인덱스 스캔 과정에서 비용을 줄여야 한다.

그리고 만약 몸무게로만 정렬되어 있을 때 몸무게 접근 후 정렬되어 있지 않은 학생명을 찾기위해서 랜덤 I/O가 발생하는데 이런 랜덤 액세스를 최소화하도록 인덱스를 튜닝해야한다.

인덱스 스캔과 랜덤 액세스 두 가지 중 조금더 중요한 부분을 찾으라 한다면 랜덤 액세스 비용을 줄이는게 더 효율적이다.

위의 예에서 봤을때 시력을 찾아도 해당학생을 찾기위한 과정이 더 비용이 많이 들기 때문이다. 이처럼 SQL튜닝은 랜덤 I/O와의 전쟁이다.

이미지 출처 : http://www.dbguide.net/db.db?cmd=view&boardUid=148214&boardConfigUid=9&boardIdx=137&boardStep=1

 

인덱스 구조

인덱스는 대용량 테이블에서 필요한 데이터만 빠르고 효율적으로 액세스하기 위해 사용되는 오브젝트이다.

책에 주제에 대한 페이지 번호가 기재되어 있으면 필요한 내용을 빠르게 찾을 수 있는것처럼 인덱스를 이용하면 Range Scan을 사용하여 필요한 부분을 빠르게 읽을 수있다.

일반적으로 DBMS에서는 BTree 형태의 인덱스를 사용한다. 일반적으로 루트와 브랜치 블록에 있는 데이터는 하위 블록에 대한 주소값을 가지고 있으며, 이 것을 구분하는 키는 하위 블록에 저장된 키값의 범위를 나타낸다. 만약 사용자 ‘정철’ 데이터를 기준이라면 사용자 >= ‘정철’의 데이터는 우측 블록에 위치하는 것을 의미한다.

리프 블록에 저장된 각 레코드는 키값 순으로 정렬되어 있고, ROWID를 가지고 있다. 인덱스 키값이 동일할 경우 ROWID 순으로 정렬된다. ROWID는 데이터 블록주소와 로우 번호가 있기에 인덱스를 스캔하는 이유는 이 ROWID를 찾기 위해서이다. 왜냐하면 ROWID의 데이터 블록주소는 데이터 파일번호와 블록번호(데이터파일 내에서 부여한 상대적 순번)로 구성되어 있고, 로우번호(블록 내 순번)으로 구성되어 있기 때문에 데이터를 찾을 수 있기 때문이다.

 

인덱스 수직적 탐색

정렬된 인덱스 레코드 중 조건을 만족하는 첫 번째 레코드를 찾는 과정으로 인덱스 스캔 시작지점을 찾는 과정이다.

인덱스 수직적 탐색은 루트 블록에서 부터 시작한다. 각 브랜치 블록에 저장된 인덱스 레코드는 하위 블록에 대한 주소값을 가진다. 그 주소값을 이용하여 인덱스를 스캔한다.

인덱스 수직적 탐색은 찾고자 하는 값보다 크거나 같은 값을 만나면, 바로 직전 레코드가 가리키는 하위 블록으로 이동한다. 이 과정을 반복하면서 찾아가는 하위 블록에서 조건을 만족하는 첫 번째 블록을 찾아가는 과정이다.

 

인덱스 수평적 탐색

수직적 탐색을 통해 스캔 지점을 찾고나면 그 후 데이터가 더 안나타날 때까지 인덱스 리프 블록을 수평적으로 스캔한다. 즉 데이터를 찾는과정이다.

인덱스 리프 블록끼리는 서로 앞뒤 블록에 대해 주소값을 가지고 있기 때문에 수평적인 탐색이 가능하다. 이런 과정을 통해서 조건절에 만족하는 데이터를 모두 찾고 ROWID값을 얻는다.

결국 수직적 탐색은 수평적 탐색을 시작할 위치를 찾는것이고, 수평적탐색은 그렇게 찾은 위치에서 본격적으로 데이터를 찾는 작업을 시작한다.

 

결합 인덱스 구조와 탐색

두 개 이상의 컬럼을 결합해서 인덱스를 만들 수도 있다. 이런경우에도 찾고자 하는 값과 동일하거나 같은 값을 만나게 되었을 때, 하위 블록으로 이동하는 수직적 탐색을 먼저 진행한다.

만약 고객명과 성별을 하나의 인덱스로 묶어서 탐색한다고 하였을 때와 성별과 고객명으로 묶었을 때 서로 탐색하는 블록은 달라질 수 있다. 하지만 결국 블록을 탐색하는 개수는 동일하다. 서로 컬럼의 순서가 바뀔경우에 인덱스 탐색의 비용이 다를거라는 주장이 있으나 BTree의 경우 평면구조가 아니기 때문에 수직적, 수평적 탐색에서 발생하는 비용은 동일하다.

댓글()

오라클의 Table Full Scan과 Index Range Scan

테이블에 저장된 데이터를 읽는 방식은 두 가지이다.

Table Full Scan은 해당 테이블에 전체 블록을 읽어서 사용자가 원하는 데이터를 찾는 방식이다. 그리고 Index Range Scan은 인덱스를 이용하여 데이터를 일정부분읽어서 ROWID로 테이블 레코드를 찾아가는 방식이다. ROWID는 테이블 레코드가 디스크 상에 어디 저장됐는지를 가리키는 위치 정보이다.

상당수의 툴(Toad, Orange)에서 데이터를 Full Scan 하는 경우에 실행계획에서 빨간색으로 경고를 보여준다.

그래서 Table Full Scan에 경우 더 느리다는 고정관념이 있으나 모두 그런것은 아니다. Index를 이용한 스캔방식이 더 느린 경우도 있다.

Table Full Scan에 경우 읽고자 하는 데이터의 블록을 Multi Block I/O로 읽기 때문에 프로세스가 데이터를 바로 처리할 수 있으나, Index의 경우 Single Block I/O로 데이터를 읽는다. 그렇기 때문에 데이터를 모두 읽는 I/O Call이 끝날 때까지 정작 프로세스는 대기 상태에 들어가기 때문에 비효율적인 상태가 된다.

그리고 인접한 데이터 500개를 찾을 경우 Single Block I/O를 사용하는 Index의 경우에는 같은 블록을 500번 방문하는 안좋은 경우가 발생할 수 있다.

경우에 따라 Full Scan이 더 좋은 방법일 수 있고, Index Scan이 좋은 방법일 수 있다. 앞으로 더 공부하면서 어떤경우가 더 좋고 나쁜지 알아봐야겠다.

댓글()