인덱스 바이너리

마지막 업데이트: 2022년 2월 23일 | 0개 댓글
  • 네이버 블로그 공유하기
  • 네이버 밴드에 공유하기
  • 페이스북 공유하기
  • 트위터 공유하기
  • 카카오스토리 공유하기
정확한 성능비교를 위해서는 MySQL 캐시 이외에 OS 캐시도 비워야만 했습니다.
그래서 쿼리의 조건 ( group_no in () ) 에 포함되는 값들을 하나씩 추가하면서 쿼리가 캐시 안되게 하여 비교하였습니다.

[mysql] 인덱스 정리 및 팁

MySQL 인덱스에 관해 정리를 하였습니다.
MySQL을 잘 알아서 정리를 한것이 아니라, 잘 알고 싶어서 정리한 것이라 오류가 있을수도 있습니다.

1. 인덱스란?

인덱스는 결국 지정한 컬럼들을 기준으로 메모리 영역에 일종의 목차를 생성하는 것입니다.
insert, update, delete (Command)의 성능을 희생하고 대신 select (Query)의 성능을 향상시킵니다.
여기서 주의하실 것은 update, delete 행위가 느린것이지, update, delete를 하기 위해 해당 데이터를 조회하는것은 인덱스가 있으면 빠르게 조회가 됩니다.
인덱스가 없는 컬럼을 조건으로 update, delete를 하게 되면 굉장히 느려 많은 양의 데이터를 삭제 해야하는 상황에선 인덱스로 지정된 컬럼을 기준으로 진행하는것을 추천드립니다.

  • 인덱스 탐색은 Root -> Branch -> Leaf -> 디스크 저장소 순으로 진행됩니다.
    • 예를 들어 Branch (페이지번호 2) 는 dept_no가 d001이면서 emp_no가 10017 ~ 10024까지인 Leaf의 부모로 있습니다.
    • 즉, dept_no=d001 and emp_no=10018 로 조회하면 페이지 번호 4인 Leaf를 찾아 데이터파일의 주소를 불러와 반환하는 과정을 하게 됩니다.
    • 즉, 두번째 컬럼의 정렬은 첫번째 컬럼이 똑같은 열에서만 의미가 있습니다.
    • 만약 3번째, 4번째 인덱스 컬럼도 있다면 두번째 컬럼과 마찬가지로 3번째 컬럼은 2번째 컬럼에 의존하고, 4번째 컬럼은 3번째 컬럼에 의존하는 관계가 됩니다.
    • 결국 인덱스 성능을 향상시킨다는 것은 디스크 저장소에 얼마나 덜 접근하게 만드느냐, 인덱스 Root에서 Leaf까지 오고가는 횟수를 얼마나 줄이느냐에 달려있습니다.
    • 너무 많은 인덱스는 새로운 Row를 등록할때마다 인덱스를 추가해야하고, 수정/삭제시마다 인덱스 수정이 필요하여 성능상 이슈가 있습니다.
    • 인덱스 역시 공간을 차지합니다. 많은 인덱스들은 그만큼 많은 공간을 차지합니다.
    • 특히 많은 인덱스들로 인해 옵티마이저가 잘못된 인덱스를 선택할 확률이 높습니다.

    2. 인덱스 키 값의 크기

    InnoDB (MySQL)은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지라고 하며, 인덱스 역시 페이지 단위로 관리 됩니다.
    (B-Tree 인덱스 구조에서 Root, Branch, Leaf 참고)

    페이지는 16KB 로 크기가 고정되어 있습니다.

    만약 본인이 설정한 인덱스 키의 크기가 16 Byte 라고 하고, 자식노드(Branch, Leaf)의 주소(위 인덱스 구조 그림 참고)가 담긴 크기가 12 Byte 정도로 잡으면, 16*1024 / (16+12) = 585 로 인해 하나의 페이지에는 585개가 저장될 수 있습니다.
    여기서 인덱스 키가 32 Byte로 커지면 어떻게 될까요?
    16*1024 / (32+12) = 372 로 되어 372개만 한 페이지에 저장할 수 있게 됩니다.

    조회 결과로 500개의 row를 읽을때 16byte일때는 1개의 페이지에서 다 조회가 되지만, 32byte일때는 2개의 페이지를 읽어야 하므로 이는 성능 저하가 발행하게 됩니다.

    인덱스의 키는 길면 길수록 성능상 이슈가 있습니다.

    3. 인덱스 컬럼 기준

    먼저 말씀드릴 것은 1개의 컬럼만 인덱스를 걸어야 한다면, 해당 컬럼은 카디널리티(Cardinality)가 가장 높은 것을 잡아야 한다는 점입니다.

    카디널리티(Cardinality)란 해당 컬럼의 중복된 수치를 나타냅니다.
    예를 들어 성별, 학년 등은 카디널리티가 낮다고 얘기합니다.
    반대로 주민등록번호, 계좌번호 등은 카디널리티가 높다고 얘기합니다.

    인덱스로 최대한 효율을 뽑아내려면, 해당 인덱스로 많은 부분을 걸러내야 하기 때문입니다.
    만약 성별을 인덱스로 잡는다면, 남/녀 중 하나를 선택하기 때문에 인덱스를 통해 50%밖에 걸러내지 못합니다.
    하지만 주민등록번호나 계좌번호 같은 경우엔 인덱스를 통해 데이터의 대부분을 걸러내기 때문에 빠르게 검색이 가능합니다.

    3-1. 여러 컬럼으로 인덱스 구성시 기준

    자 그럼 여기서 궁금한 것이 있습니다.
    여러 컬럼으로 인덱스를 잡는다면 어떤 순서로 인덱스를 구성해야 할까요?
    카디널리티가 낮은->높은순으로 구성하는게 좋을까요?
    카디널리티가 높은->낮은순으로 구성하는게 좋을까요?
    실제 실험을 통해 확인해보겠습니다.

    테스트 환경은 AWS EC2 Ubuntu 16.04를 사용했습니다.
    최대한 극적인 비교를 위해 메모리는 1G, 디스크는 인덱스 바이너리 마그네틱(SSD X)을 사용했습니다.

    테이블 형태는 아래와 같습니다.

    전체 Row는 약 1700만건으로 생성했습니다.
    각 컬럼의 카디널리티는 다음과 같습니다.

    자 그럼 인덱스를 2가지 형태로 생성해보겠습니다.

    첫번째 인덱스는 is_bonus, from_date, group_no 순으로 카디널리티가 낮은순에서 높은순 (중복도가 높은 순에서 낮은순으로) 으로,
    두번째 인덱스는 group_no, from_date, is_bonus 순으로 카디널리티가 높은순에서 낮은순 (중복도가 낮은 순에서 높은순으로) 으로 생성했습니다.

    사용한 쿼리는 다음과 같습니다.

    옵티마이저가 인덱스를 자동 선택해버리니 Index Hint ( use index (IDX_SALARIES_INCREASE) ) 로 강제로 인덱스를 사용하도록 하였습니다.

    이 인덱스 2개를 총 10회로 테스트하였습니다.
    결과가 어떻게 될까요?

    IDX_SALARIES_INCREASE IDX_SALARIES_DECREASE
    1 110ms 46.9ms
    2 89.5ms 24.6ms
    3 95.4ms 38.1ms
    4 85.6ms 29.3ms
    5 83.6ms 29.3ms
    6 85.2ms 38.2ms
    7 59.4ms 26.1ms
    8 64.2ms 29.4ms
    9 93.7ms 25.7ms
    10 102ms 35.4ms
    평균 86.86ms 32.3ms

    월등한 차이가 나진 않지만 10회만으로 비교는 가능한것 같습니다.
    즉, 여러 컬럼으로 인덱스를 잡는다면 카디널리티가 높은순에서 낮은순으로 ( group_no, from_date, is_bonus ) 구성하는게 더 성능이 뛰어납니다.

    정확한 성능비교를 위해서는 MySQL 캐시 이외에 OS 캐시도 비워야만 했습니다.
    그래서 쿼리의 조건 ( group_no in () ) 에 포함되는 값들을 하나씩 추가하면서 쿼리가 캐시 안되게 하여 비교하였습니다.

    3-2. 여러 컬럼으로 인덱스시 조건 누락

    꼭 인덱스의 컬럼을 모두 사용해야만 인덱스가 사용되는 것은 아닙니다.
    그렇다면 인덱스 컬럼중 어떤 것들은 누락되어도 되고, 누락되면 안되는 것은 어떤 것일까요?

    예를 들어 아래와 같이 인덱스가 잡혀있습니다.

    여기서 중간에 있는 from_date 를 제외한 조회 쿼리와 가장 앞에 있는 group_no 를 제외한 조회 쿼리를 사용해보겠습니다.

    첫번째 조회쿼리의 인덱스 바이너리 실행계획을 보면

    이렇게 정상적으로 인덱스를 사용했음을 확인할 수 있습니다.
    filtered가 10% 인만큼 효율적으로 사용하지는 못했지만, 인덱스를 태울 수 있는 쿼리입니다.

    그럼 두번째 조회쿼리의 실행계획은 어떻게 될까요?

    전혀 인덱스를 사용하지 못했음을 확인할수 있습니다.

    조회 쿼리 사용시 인덱스를 태우려면 최소한 첫번째 인덱스 조건은 조회조건에 포함되어야만 합니다.
    첫번째 인덱스 컬럼이 조회 쿼리에 없으면 인덱스를 타지 않는다는 점을 기억하시면 됩니다.

    4. 인덱스 조회시 주의 사항

    • between , like , < , >등 범위 조건은 해당 컬럼은 인덱스를 타지만, 그 뒤 인덱스 컬럼들은 인덱스가 사용되지 않습니다.
      • 즉, group_no, from_date, is_bonus 으로 인덱스가 잡혀있는데 조회 쿼리를 인덱스 바이너리 where group_no=XX and is_bonus=YY and from_date > ZZ 등으로 잡으면 is_bonus는 인덱스가 사용되지 않습니다.
      • 범위조건으로 사용하면 안된다고 기억하시면 좀 더 쉽습니다.
      • in 은 결국 = 를 여러번 실행시킨 것이기 때문입니다.
      • 단, in 은 인자값으로 상수가 포함되면 문제 없지만, 서브쿼리를 넣게되면 인덱스 바이너리 성능상 이슈가 발생합니다.
      • in 의 인자로 서브쿼리가 들어가면 서브쿼리의 외부가 먼저 실행되고, in 은 체크조건으로 실행되기 때문입니다.
      • WHERE 에서 OR 을 사용할때는 주의가 필요합니다.
      • 인덱스는 가공된 데이터를 저장하고 있지 않습니다.
      • where salary * 10 > 150000; 는 인덱스를 못타지만, where salary > 150000 / 10; 은 인덱스를 사용합니다.
      • 컬럼이 문자열인데 숫자로 조회하면 타입이 달라 인덱스가 사용되지 않습니다. 정확한 타입을 사용해야만 합니다.

      5. 인덱스 컬럼 순서와 조회 컬럼 순서

      최근엔 이전과 같이 꼭 인덱스 순서와 조회 순서를 지킬 필요는 없습니다.
      인덱스 컬럼들이 조회조건에 포함되어 있는지가 중요합니다.

      (3-1 실험과 동일한 인덱스에 조회 순서만 변경해서 실행한 결과)

      보시는것처럼 조회 컬럼의 순서는 인덱스에 큰 영향을 끼치지 못합니다.
      단, 옵티마이저가 조회 조건의 컬럼을 인덱스 컬럼 순서에 맞춰 재배열하는 과정이 추가되지만 거의 차이가 없긴 합니다.
      (그래도 이왕이면 맞추는게 인덱스 바이너리 조금이나마 낫겠죠?)

      6. 페이징 성능 개선 팁

      위 인덱스 지식을 통해 페이징 성능 개선을 원하시는 분들은 기존의 포스팅을 참고하시면 좋습니다.

      전 회사에서 근무할때는 아무래도 정적 컨텐츠를 보여주는 서비스가 위주다보니 서비스의 성능에 관한것은 모두다 캐시로 처리할 수 있었습니다.
      DB 조회와 관련해서 전혀 신경쓸일이 없었고, 캐시를 어떻게할것이며 페이지 로딩속도를 어떻게 개선할지만 항상 신경썼습니다.
      그러다 이직 후 실제 모든 Request를 캐시할수 없는 일을 하다보니 DB에 관해 진짜 공부할 필요성을 느꼈습니다.

      지금 당장 가장 부족했던 MySQL의 인덱스 부분부터 공부를 시작하였고 실제 업무에 적용해보았습니다.
      앞으로 계속 관련해서 포스팅을 추가하겠습니다. (실행계획, 프로파일링 등등)인덱스 바이너리

      승돌님의 추천글로 읽게된 프로그래머 열정을 말하다 의 한 구절을 소개드리며 이번 포스팅을 마치겠습니다.

      너무나 많은 사람이 어떤 분야에 전문가라는 의미가 다른 분야에 대해서는 잘 몰라도 되는 것으로 오해하고 있는것 같다.
      극단적인 예지만, 그렇다면 우리 어머니는 윈도우 전문가다.
      어머니는 리눅스나 맥 OS를 쓰지 않기 때문이다.
      .
      깊이가 얕은 전문가들은 전문가라는 말을 "한가지만 안다는 것에 대한 변명"으로 사용한다.

      [DB] 데이터베이스 인덱스(Index) 란 무엇인가?

      인덱스는 데이터베이스 테이블에 대한 검색 성능의 속도를 높여주는 자료 구조입니다. 특정 컬럼에 인덱스를 생성하면, 해당 컬럼의 데이터들을 정렬하여 별도의 메모리 공간에 데이터의 물리적 주소와 함께 저장됩니다. 이렇게 인덱스가 생성하였다면 앞으로 쿼리문에 "인덱스 생성 컬럼을 Where 조건으로 거는 등"의 작업을 하면 옵티마이저에서 판단하여 생성된 인덱스를 탈 수가 있습니다. 만약 인덱스를 타게 되면 아래의 그림과 같이 인덱스를 타게 되고 먼저 인덱스에 저장되어 있는 데이터의 물리적 주소로 가서 데이터를 가져오는 식으로 동작을 하여 검색 속도의 향상을 가져올 수 있습니다.

      즉 인덱스는 책에 있는 목차라고 생각하시면 편합니다. 우리가 책에서 정보를 찾을때도 먼저 원하는 카테고리를 목차에서 찾고 목차에 있는 페이지 번호를 보고 찾아가듯 인덱스도 인덱스에서 내가 원하는 데이터를 먼저 찾고 저장되어 있는 물리적 주소로 찾아갑니다. 이 Index에 관한 내용은 자세히 알아두는 것이 좋습니다. 실제 DB 관련 작업을 할 때 대부분의 속도 저하는 바로 Select문 특히 조건 검색 Where절에서 발생하는데 가장 먼저 생각해 볼 수 있는 대안으로 Index를 생각할 수 있기도 하고, SQL 튜닝에서도 Index와 관련된 문제사항과 해결책이 많기 때문입니다.

      인덱스(Index)를 사용하는 이유

      테이블에 데이터들이 인덱스의 가장 큰 특징은 데이터들이 정렬이 되어있다는 점입니다. 이 특징으로 인해 조건 검색이라는 영역에서 굉장한 장점이 됩니다.

      조건 검색 Where 절의 효율성

      테이블을 만들고 안에 데이터가 쌓이게 되면 테이블의 레코드는 내부적으로 순서가 없이 뒤죽박죽으로 저장됩니다. 이렇게 되면 Where절에 특정 조건에 맞는 데이터들을 찾아낼때도 레코드의 처음부터 끝까지 다 읽어서 검색 조건과 맞는지 비교해야 합니다. 이것을 풀 테이블 스캔 (Full Table Scan)이라고 합니다. 하지만 인덱스 테이블은 데이터들이 정렬되어 저장되어 있기 때문에 해당 조건 (Where)에 맞는 데이터들을 빠르게 찾아낼 수 있겠죠. 이것이 인덱스(Index)를 사용하는 가장 큰 이유입니다.

      정렬 Order by 절의 효율성

      인덱스(Index)를 사용하면 Order by에 의한 Sort과정을 피할수가 있습니다. Order by는 굉장히 부하가 많이 걸리는 작업입니다. 정렬과 동시에 1차적으로 메모리에서 정렬이 이루어지고 메모리보다 큰 작업이 필요하다면 디스크 I/O도 추가적으로 발생됩니다. 하지만 인덱스를 사용하면 이러한 전반적인 자원의 소모를 하지 않아도 됩니다. 이미 정렬이 되어 있기 때문에 가져오기만 하면 되니까요.

      MIN, MAX의 효율적인 처리가 가능하다.

      이것 또한 데이터가 정렬되어 있기에 얻을 수 있는 장점입니다. MIN값과 MAX값을 레코드의 시작값과 끝 값 한건씩만 가져오면 되기에 FULL TABE SCAN으로 테이블을 다 뒤져서 작업하는 것보다 훨씬 효율적으로 찾을 수 있습니다.

      인덱스(Index)의 단점

      인덱스가 주는 혜택이 있으면 그에 따른 부작용도 있습니다. 인덱스의 가장 큰 문제점은 정렬된 상태를 계속 유지 시켜줘야 한다는 점입니다. 그렇기에 레코드 내에 데이터값이 바뀌는 부분이라면 악영향을 미칩니다. INSERT, UPDATE, DELETE를 통해 데이터가 추가되거나 값이 바뀐다면 INDEX 테이블 내에 있는 값들을 다시 정렬을 해야겠죠. 그리고 INDEX 테이블, 원본 테이블 이렇게 두 군데에 데이터 수정 작업해줘야 한다는 단점도 있습니다.

      그리고 검색시에도 인덱스가 무조건 좋은 것이 아닙니다. 인덱스는 테이블의 전체 데이터 중에서 10~15% 이하의 데이터를 처리하는 경우에만 효율적이고 그 이상의 데이터를 처리할 땐 인덱스를 사용하지 않는 것이 더 낫습니다. 그리고 인덱스를 관리하기 위해서는 데이터베이스의 약 10%에 해당하는 저장공간이 추가로 필요합니다. 무턱대고 INDEX를 만들어서는 결코 안 될 말입니다.

      인덱스(Index)의 인덱스 바이너리 관리

      앞서 설명했듯이 인덱스는 항상 최신의 데이터를 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있습니다. 그렇기 때문에 인덱스가 적용된 컬럼에 INSERT, UPDATE, DELETE가 수행된다면 계속 정렬을 해주어야 하고 그에 따른 부하가 발생합니다. 이런 부하를 최소화하기 위해 인덱스는 데이터 삭제라는 개념에서 인덱스를 사용하지 않는다 라는 작업으로 이를 대신합니다.

      • INSERT: 새로운 데이터에 대한 인덱스를 추가합니다.
      • DELETE: 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행합니다.
      • UPDATE: 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대해 인덱스를 추가합니다.

      인덱스 생성 전략

      생성된 인덱스를 가장 효율적으로 사용하려면 데이터의 분포도는 최대한으로 그리고 조건절에 호출 빈도는 자주 사용되는 컬럼을 인덱스로 생성하는 것이 좋습니다. 인덱스는 특정 컬럼을 기준으로 생성하고 기준이 된 컬럼으로 정렬된 Index 테이블이 생성됩니다. 이 기준 컬럼은 최대한 중복이 되지 않는 값이 좋습니다. 가장 최선은 PK로 인덱스를 거는것이겠죠. 중복된 값이 없는 인덱스 테이블이 최적의 효율을 발생시키겠고. 반대로 모든 값이 같은 컬럼이 인덱스 컬럼이 된다면 인덱스로써의 가치가 없다고 봐야 할 것입니다.

      1. 조건절에 자주 등장하는 컬럼

      2. 항상 = 으로 비교되는 컬럼

      3. 중복되는 데이터가 최소한인 컬럼 (분포도가 좋은) 컬럼

      4. ORDER BY 절에서 자주 사용되는 컬럼

      5. 조인 조건으로 자주 사용되는 컬럼

      인덱스를 만드는 구체적인 방법은 아래 링크를 통해 확인하여주세요.

      B * Tree 인덱스

      인덱스에는 여러가지 유형이 있지만 그 중에서도 가장 많이 사용하는 인덱스의 구조는 밸런스드 트리 인덱스 구조입니다. 그리고 B TREE 인덱스 중에서도 가장 많이 사용하는것은 B*TREE 와 B+TREE 구조를 가장 많이 사용되는 인덱스의 구조입니다.

      B * Tree 인덱스는 대부분의 DBMS 그리고 오라클에서 특히 중점적으로 사용하고 있는 가장 보편적인 인덱스입니다. 구조는 위와 같이 Root(기준) / Branch(중간) / Leaf(말단) Node로 구성됩니다. 특정 컬럼에 인덱스를 생성하는 순간 컬럼의 값들을 정렬하는데, 정렬한 순서가 중간 쯤 되는 데이터를 뿌리에 해당하는 ROOT 블록으로 지정하고 ROOT 블록을 기준으로 가지가 되는 BRANCH블록을 정의하며 마지막으로 잎에 해당하는 LEAF 블록에 인덱스의 키가 되는 데이터와 데이터의 물리적 주소 정보인 ROWID를 저장합니다.

      [RedShift] 사용자 및 권한 관리 (feat. 계정 삭제시 오류)

      개요 Redshift는 사용자와 권한을 관리하는 방법이 조금 복잡하여 권한 부여 및 삭제에서 삽질한(?) 내용을 공유해보겠습니다. 사용자와 그룹 Redshift에서는 Postgres와 조금 차이가 있는데 접근제어를 위해 role의 개념을 사용하지 않고 사용자와 그룹을 사용합니다. 그리고 아래의 규칙을 가집니다. 사용자만 그룹의 하위에 속할 수 있습니다. 즉, 그룹은 다른 그룹에 속할 수 없습니다. 그룹이 아닌 사용자가 관계를 소유합니다. 사용자와 그룹에 별도로 권한이 부여될 수 있습니다. 사용자는 자신이 속한 그룹의 모든 권한을 자동으로 상속합니다. 계정 계정생성 CREATE USER name [ [ WITH ] option [ . ] ] where option is CREATEDB | NOCREAT..

      [MySQL] 복제 구성을 위한 바이너리 로깅 형식

      개요 MySQL의 복제 데이터 포맷에는 어떤 방식이 있는지 알아보고 간단한 테스트를 통해 유의점을 확인해보겠습니다. 복제 포맷 MySQL의 복제 포맷에는 Statement, Row, Mixed 3가지 형태가 있습니다. Statement 방식은 MySQL 바이너리 로그 도입 당시부터 존재한 복제 포맷이며 5.7.7 이후 버전 부터는 Row 형태가 기본 포맷으로 변경되었습니다. Statement : 명령문 기반의 로깅 방식 Row : 행 기반의 데이터 로깅 방식 Mixed : Statement와 Row의 장점을 혼합한 로깅 방식 Statement 기반 바이너리 로그 포맷 Statement 방식의 경우 실행된 SQL 문을 그대로 바이너리 로그에 저장하는 방식입니다. 다수의 데이터가 수정된 경우에도 단순히 쿼리..

      [MySQL] Docker-compose를 이용한 초간단 서버 구성

      개요 Docker-Compose로 간단하게 MySQL 테스트 환경을 구성해보도록 하겠습니다. 사전에 Docker 및 Docker-Compose에 대한 이해가 필요합니다. 테스트 환경 Centos7 Docker 20.10.14 Docker-compose 1.29.2 구성 docker 구성을 위한 디렉토리를 생성합니다. $ mkdir mysql_test $ cd mysql_test 컨테이너 내의 파일을 동기화할 디렉토리를 mysql_test 하위에만들어 줍니다. $ mkdir db -- my.cnf 파일 관리를 위한 디렉토리 $ mkdir db/conf.d -- mysql Data를 저장할 디렉토리 $ mkdir db/data docker-compose.yml mysql_test 디렉토리 하위에 docker..

      [MySQL] 클러스터 인덱스 미생성 테이블

      개요 MS-SQL에서는 클러스터 인덱스가 없는 힙 테이블이란 개념이 존재했었는데 MySQL은 테이블 생성 시 내부적으로 클러스터 인덱스가 생성되어 어떤 경우에 해당 인덱스가 생성되는지 알아보겠습니다. 클러스터 인덱스가 없는 테이블 생성 MySQL의 InnoDB의 경우 Primary Key. 즉 클러스터 인덱스가 없는 테이블을 생성할 경우 칼럼이 NOT NULL로 구성된 UNIQUE KEY가 있을 경우 클러스터로 사용합니다. 별도의 키가 없을 경우에는 GEN_CLUST_INDEX라는 이름의 클러스터 인덱스가 생성됩니다. 해당 인덱스는 6바이트 크기로 물리적으로 저장된 순서로 인덱스 값이 부여됩니다. case1. 인덱스 미생성 시 CREATE TABLE non_index( col1 int(11) NULL, ..

      트랜잭션과 락(Transaction, Lock)

      트랜잭션 : 논리적인 작업 단위로 전부 처리되거나 처리되지 않는 (commit/rollback) 원자성을 보장하기 위한 기능이다.

      : 서로 다른 작업에서 같은 자원을 동시에 필요로 할 때 자원 경쟁이 일어나는데, 이때 순서대로 사용되는 동시성을 보장하기 위한 기능이다.

      MySQL에서 사용되는 락(LocK)은 크게 MySQL 엔진 레벨의 락과 스토리지 엔진 레벨의 락으로 나눠볼 수 있다.

      스토리지 엔진 레벨의 락

      스토리지 엔진(InnoDB)에서 제공하는 락(Lock, 잠금)이 있다.

      기본적으로 비관적 락(Pessimistic locking)을 사용한다.

      • 비관적 락 : 트랜잭션에서 변경하려는 레코드에 대해 락을 획득하고 쿼리를 수행하는 방식 (같은 레코드를 변경하려는 경우가 많을 것이라고 비관적인 생각을 하기 때문에 비관적 락)
      • 낙관적 락 : 트랜잭션에서 락 없이 일단 쿼리 수행을 하고 마칠 때 서로 다른 트랜잭션에서 충돌이 있었는지 확인하고 문제가 있으면 충돌이 난 트랜잭션을 롤백하는 방식 (같은 레코드를 변경하려는 경우가 거의 없을 것이라고 낙관적인 생각을 하기 때문에 낙관적 락)

      보통 대규모 트래픽을 처리하는 애플리케이션에서는 성능 이슈 때문에 락을 최소화 해야하기 때문에 낙관적 락으로 변경하여 사용하기도 한다.

      스토리지 엔진(InnoDB)이 제공하는 락

      ✔️ 참고로 아래에서 "인덱스 레코드" 라는 표현을 사용할 것인데, 그냥 "인덱스"로 이해하면 된다. 필자는 인덱스 바이너리 잠깐 헷갈렸기에 비슷하게 느끼는 사람이 있을까 봐 참고로 적어놓았다. (인덱스도 레코드 형식으로 저장되니까. )

      • 레코드 락 (Record lock)
        • SELECT c1 FROM t WHERE c1=10 FOR UPDATE; 일 때, c1=10인 인덱스 레코드에 락을 걸어서 수정, 삽입, 삭제등의 다른 트랜잭션을 막는다. (이때 c1은 유니크하다는 인덱스 바이너리 가정 한다.)
        • 실제 테이블의 레코드에 대해 락을 걸지 않고 인덱스 레코드에 락을 건다.
        • 따로 생성한 인덱스가 없는 테이블은 InnoDB가 자체적으로 생성한 클러스터 인덱스를 이용해 락을 건다.
        • 기본키나 유니크 키에 의한 변경 작업은 갭 락 없이 딱 인덱스 레코드에만 락을 인덱스 바이너리 건다.
        • 인덱스 레코드와 인접한 앞/뒤 사이 공간에 락을 거는 것인데, 개념적인 용어로 단독으로는 사용되지 않고 넥스트 키 락에서 사용된다. (자세한 건 넥스트 키 락에서 예제를 보자)
        • 갭 락은 READ_COMMITED 이하에서는 거의 발생하지 않고 REPEATABLE_READ 이상 격리 수준일 때에 주로 발생한다. ('거의'라고 한 이유는 외래 키 검사나 중복 키 검사할 때는 READ_COMMITED에서도 발생하기 때문이다.)
          • INSERT INTO . ON DUPLICATE KEY UPDATE 는 INSERT를 하려는데 "유니크 키"나 "기본 키"에 중복이 일어나면 UPDATE로 동작하도록 한다. 당연히 중복된 키가 없으면 INSERT로 동작한다.
          • 레코드 락과 갭 락을 합쳐놓은 형태다. 인덱스 레코드도 잠그고 그 인덱스 레코드 앞, 뒤 갭도 잠근다.
          • SELECT c1 FROM t WHERE c1 BETWEEN 10 AND 20 FOR UPDATE; 일 때, c1=15 인 레코드를 insert 하는 트랜잭션이 있다면 막는다. 반드시 인덱스 레코드 사이에 있는 갭만 락을 거는 게 아니라 제일 앞 또는 뒤에 있는 인덱스 레코드의 갭도 락을 건다. 예를 들어 c1=10인 레코드 인덱스 바로 앞에 c1=8인 레코드 인덱스가 있는 상태라면, c1=9인 레코드를 insert 하려고 하면 막힌다. (그 갭에도 락이 걸려있기 때문에!)
          • MySQL에서 자동 증가하는 숫자 값을 채번하기 위해 AUTO_INCREMENT라는 컬럼 속성을 정의할 때가 있다. 이때 같이 INSERT 하려는 요청이 올 때 거는 락이다.

          인덱스의 락을 거는 MySQL InnoDB의 동작

          테이블 락이 아닌 레코드 단위의 락을 기본으로 하는 InnoDB의 동작에 대해서 알아본다.

          앞서 언급했듯 레코드 락은 변경하려고 하는 레코드에 락을 거는 게 아니라 인덱스 레코드에 락을 건다.

          아래 예시로 동작을 살펴보겠다.

          만약 first_name 컬럼에만 인덱스(보조 인덱스)가 적용되어 있다고 가정하면 마지막 UPDATE 쿼리에서 넥스트 키 락에 의해 락이 걸리는 레코드는 몇 개 일까?

          first_name이 'DK'고 last_name이 'J'인 사람은 한 명(1건)이지만 인덱스에 락을 거는 InnoDB의 기본 정책상 인덱스가 있는 first_name에 락을 걸게 되므로 250건 전체에 락을 걸고 실제 수정은 last_name이 'J'인 1건에 대해서만 수정이 일어난다.

          이 동작을 이해하고 나면 예상되는 위험을 알 수 있다.🕳️

          만약 위 경우에서 first_name이 같은 레코드가 엄청나게 많으면 분명 이름은 레코드 단위 락(넥스트 키 락)인데 불필요하게 많은 레코드에 락이 걸리게 된다. (= 동시성 강화로 인한 성능 저하)

          넥스트 키 락 사용 줄이기

          넥스트 키 락이 필요하게 된 주된 이유는 복제를 위한 바이너리 로그 때문이다.

          MySQL 5.0 이전 버전에서는 바이너리 로그를 비활성화하지 않아도 READ_COMMITED 격리 레벨을 쓸 수 있었다. (innodb_locks_unsafe_for_binlog=1 설정만으로 넥스트 키 락이 줄어듦)

          MySQL 5.1 이후 버전에서는 바이너리 로그가 활성화되면 최소 REPEATABLE_READ 이상의 격리 레벨을 쓰도록 강제하기 때문에 넥스트 키 락 사용을 줄이기 위해서는 바이너리 로그를 비활성화하거나 바이너리 로그를 활성화하더라도 레코드 기반의 바이너리 로그를 사용하도록 설정한 후에 innodb_locks_unsafe_for_binlog=1 로 설정하여 넥스트 키 락을 줄일 수 있다.

          이렇게 설정하면 앞서 언급한 인덱스 레코드에 락을 걸 때 불필요한 레코드에 락이 걸리는 문제를 어느 정도 해결할 수 있다.

          ⇒ 앞서 예제를 기준으로 250개의 인덱스 레코드에 락이 걸리는 것은 똑같지만, 다음 where 조건에 의해 1개의 레코드로 줄어드는 경우에 불필요한 249개의 인덱스 레코드에 락을 풀어주는 방식으로 해결한다.

          사례로 살펴보면 락

          InnoDB의 기본 격리 레벨(isolation level)이 REPEATABLE READ 이기 때문에 이 레벨에서 발생할 수 있는 사례를 살펴본다.

          🔹 사례 1. 넥스트 키 락은 갭에도 락을 건다.

          위 쿼리에서 두 세션의 쿼리는 조건이 겹치는 레코드가 없지만, 넥스트 키락에 의해서 인덱스 레코드에 있는 갭(session 1의 조건에 만족한 제일 앞에 있는 레코드 이전의 갭)에 락이 걸리면서 session 2의 경우 대기하게 된다.

          당연히 앞선 session 1이 금방 끝나 준다면, 대기하던 session 2의 쿼리가 수행될 것이지만, session 수가 엄청나게 많다고 가정하고 저런 쿼리가 많이 발생하면 점점 대기 시간이 길어져 timeout이 발생하거나, 굳이 비슷한 쿼리가 많지 않더라도 어떤 쿼리에 의해 데드락이 걸려있는 경우, 풀릴 때까지 기다리다가 예외가 발생하게 될 것이다.

          REPEATABLE READ 수준에서 넥스트 키 락을 피할 수 없기 때문에 위와 같은 쿼리로 지우지 말고 최대한 인덱스 레코드 하나하나 집어서 지울 수 있도록 하는 식으로 우회하는 방법을 쓸 수 있다.

          🔹 사례 2. 서브 쿼리의 SELECT 문은 락을 건다.

          위 쿼리에서는 데드락이 걸린다.

          session 1은 user_details 테이블에 account_id로 인덱스가 걸려있어서 해당 레코드를 지우는 쿼리를 처리하기 위해 인덱스 레코드에 락을 걸었다.

          마침 session 2는 accounts 테이블에 잔액이 1억 원 이상 가지고 있는 유저를 VIP로 수정하는 쿼리를 쐈는데 서브 쿼리에 SELECT 절에 의해서 accounts의 많은 레코드와 갭에 인덱스 바이너리 락이 걸었다.

          (SELECT 쿼리라 Shared 락이다. 기본적으로 MySQL에서 SELECT 쿼리에 락을 걸지 않지만 서브 쿼리에는 Shared 락을 건다.)

          session 2는 앞선 session 1에서 account_id가 99인 user_details 테이블의 인덱스 레코드에 잠금이 걸려있어 업데이트를 하지 못하고 기다리게 되었다.

          또 바로 session 1에서 accounts 테이블에 account_id가 99인 레코드를 지우는 쿼리를 보냈다고 해보자.

          session 1은 앞선 session 2에서 accounts 인덱스 레코드에 Shared 락이 걸린 상태이기 때문에 account_id 가 99인 레코드를 지우지 못하고 대기한다.

          ZeddiOS

          부모노드의 키값이 자식노드의 키값보다 항상 작은 힙을 '최소 힙' 이라고 부릅니다.

          하지만 total Order를 만족하지 못하는 이유는 위 조건이 "직계관계"에서만 만족한다는 것이죠.

          힙 구조에서 어떤 키 값에 대해 레벨은 위지만(depth가 작지만) 현재 이 노드의 키값보다 키값이 작을 수 있다는 것입니다.

          그래서 Partial Order에요.

          아니 힙정렬 알려달라니까;; 왜 힙을 설명;;;;;

          힙을 알아야 힙정렬을 이해할 수 있는 것은 당연하겠죠?

          이제..힙도 알았으니 진짜 힙정렬을 하러 가봅시다.

          자.. 정말 중요한 이야기 가 하나 있는데요.

          보통 입력이 배열로 주어진다는 것은 다들 아실겁니다.

          완전이진트리를 배열로 구현했을 때 정말 좋은 장점이 하나가 있죠.

          인덱스로 접근이 "바로" 가능하다는 것이죠.

          자. 완전이진트리를 한번 볼까요?

          주어진 배열로 완전이진트리를 만들면 위 트리모양이 되겠죠?

          이때 엄청나게 유용한 장점이 생기게 되는데,

          한 노드를 알면 그노드의 부모 또는 자식들을 인덱스로 바로 접근이 가능합니다.

          (흰색은 데이터고, 빨간색은 인덱스입니다.)

          노드 i의 부모 노드 인덱스 : i/2, (단, i > 1)

          노드 i의 왼쪽 자식 노드 인덱스 : 2 x i

          노드 i의 오른쪽 자식 노드 인덱스 : (2 x i) + 1

          3을 가지고 한번 해보자구요.

          3의 인덱스는 현재 "3"이네요.

          그럼 3의 인덱스인 3을 2로 나누어주면 1.5죠?

          소수는 버려주기 때문에 즉, "1의 인덱스가 3의 부모다" 라는것을 알게됩니다.

          인덱스1의 데이터는 6이죠? 이렇게 3의 부모는 6이라는 것을 알게됩니다.

          현재 3의 인덱스인 3에 x2를 하면 6이 나옵니다. 즉 인덱스 6(=5)이 3의 왼쪽자식임을 알 수 있습니다.

          현재 3의 인덱스인 3에 x2+1을 하면 7이 나오죠? 즉 인덱스 7(=7)이 3의 오른쪽자식임을 알 수 있습니다.

          이때문에 인덱스 1부터 시작한다는 것도 알 수 있겠네요.

          만약 입력이 크기 n인 배열로 주어진다.

          크기가 n인 완전이진트리를 하나 준다.

          그러므로 만약 입력이 배열이라면, 힙의 구조조건을 따로 만족해줄 필요없이

          순서조건 만 만족시켜주면 된답니다.

          완전이진트리는 이제 됐는데..순서조건은 어떻게 맞추냐!!

          그건 최대힙(max-heap), 최소힙(min-heap)에 따라 다릅니다.

          일단 저는 최대힙으로 배웠기 때문에 최대힙으로 설명드릴게요!

          최대힙을 할 줄 아신다면 자연스럽게 최소힙방법도 아실거에요.

          자, 순서조건은 어떻게 만족시킨다구요?

          네 바로 Partial Order를 만족하면 된답니다.

          최대힙에서의 "직계관계"에서는 무조건 내 부모는 나보다 키값이 커야합니다.

          자연스럽게 자식들은 부모모다 키값이 작겠죠?

          아..계속 힙정렬 알려달라니까 ㅡㅡ 힙이야기만 하고있네

          일단 "힙정렬"을 하려면 다른 정렬 알고리즘처럼 그냥 하면 안됩니다.

          1단계 : 주어진 배열을 최대힙/최소힙으로 만든다. (이번 예제에서는 최대힙으로 만들겁니다.)

          2단계: delete를 통해 정렬을 한다.

          ( 1단계 : 주어진 배열을 최대힙/최소힙으로 만든다. (이번 예제에서는 최대힙으로 만들겁니다.)

          일단 정렬이 필요한 데이터들이 아무렇게나 섞여있겠죠? 아무런 순서도 없이 말이죠.

          이러한 데이터가 있다고 생각해볼게요.

          일단 힙으로 만들어볼까요? 힙은 "완전이진트리"라고 했죠?

          하지만 배열로 n크기의 인풋이 주어졌으니,

          이를 만족하는 완전이진트리는 단 하나입니다.

          즉, 힙의 2가지 조건 중, 구조조건을 만족하게 되는 것이죠.

          일단 완전이진트리로 그려볼까요?

          빨간색 - 인덱스 / 검정색 - 데이터

          자.. 위의 완전이진트리는 (의도치 않게 포화(full)이진트리가 됐네요.) 최대힙일까요?

          Partial Order이므로 아무 부분트리나 잡아도 부모의 키값은 두 자식의 키값보다 커야합니다.

          즉, 최대힙의 순서조건을 만족해야합니다.

          위 완전이진트리는 순서조건을 만족하고있나요?

          루트노트부터 그 조건을 만족시키지 못합니다.

          정렬을 하기 위해서 우리는 위 완전이진트리를 최대힙 /최소힙으로 만들어주어야 합니다.

          자, 우리는 최대힙으로 만들어줘야한다는 것을 잊지마세요.

          최대힙 - 부모가 자식보다 키값이 커야한다!!(부모의 키값 > 자식들의 키값)

          하지만 딱 봐도 부모보다 자식의 키값이 더 크네요. (부모의 키값 < 자식들의 키값)

          그럼 어떻게 만들어주면 될까요?

          네. 7,15,14중 최댓값인 15가 올라가면 (부모가 되면) 7,14보다 커지게 되므로

          밑의 시간복잡도 계산에서 이해하셔야 할 게 하나 있어서

          이 7은, 2번의 비교과정 을 거치게 됩니다.

          7은 그 중 큰값과 자리를 바꾸게되죠.

          만약 7이 두 자식보다 크다면, 7은 그 자리를 계속 유지하게 됩니다.

          자, 15가 위로 올라감으로써 부모의 키값이 자식들의 키값보다 커지게 되었죠?

          그러므로 이 부분 트리는 순서조건을 만족하게 됩니다.

          하지만 이 부분트리만 순서조건을 만족하면 안됩니다. 모든 부분트리가 최대힙의 순서조건을 만족해야 해요.

          다음 부분트리로 넘어가 볼까요?

          일단 순서조건을 만족하지 않습니다.

          그럼 뭐가 부모로 올라가야할까요~~

          이 최대힙으로 만드는 과정은 재귀로 이루어지기 때문에,

          다음 과정은 11,8,6으로 이루어진 부분트리가 아니라

          이때까지 처리했던 두 부분트리의 부모인 3을 포함한 부분트리가 이번에 처리할 트리가 됩니다.

          자 일단 3은 두 자식들보다 작으므로

          15와 12중 큰 15가 올라가게 됩니다.

          자, 3이 내려간 부분트리를 봐주세요.

          3이 내려옴으로써 이 부분트리의 순서조건이 만족하지 인덱스 바이너리 않게 됩니다.

          네. 그냥 한번 더 해주면 됩니다.

          7과 14중 큰 값인 14가 3보다 크므로

          자. 이제 저 빨간 세모안에 있는 모든 (부분)트리는 순서조건을 만족하게 됩니다.

          즉, 모든 부분 트리의 "직계관계"에서 부모의 키값이 자식의 키값보다 크죠.

          네. 오른쪽 부분트리로 갈 차례입니다.

          네. 웬일로 순서조건을 만족하네요. 11이 8,6,보다 크니까요!

          역시 이 트리도 순서조건을 만족합니다.

          자. 딱 봐도 순서조건을 만족하지 않습니다.

          부모인 5가 자식인 11과 13보다 작기때문에 자식중 큰 값인, 즉 13과 자리를 바꾸어줘야 합니다.

          자, 이제 순서조건이 다 만족됐나요? 네. 빨간색 세모 안에 있는 모든 부분트리가 순서조건을 만족합니다.

          이제 뭘 하면 될까요? 네. 이제 전체 트리를 검사할 시간입니다.

          자. 이제 너무 쉬울지경이네요.

          1이 15,13보다 작으니 13과 15의 최댓값인 15가 1의 자리로 가야합니다.

          하지만 1이 내려간 부분트리에서도 순서조건이 만족하지 않게 됩니다.

          역시 1이 내려감으로써 순서조건이 만족하지 않게 인덱스 바이너리 됩니다.

          그럼 7이 위로 올라가야겠죠.

          자. 1이 leaf까지 내려오게 되었네요.

          이제 모든 부분트리들이 순서조건을 만족한다는 것을 볼 수 있죠?

          이제 최대힙을 만드는 과정. 즉 힙정렬의 1단계가 끝나게 되었습니다.

          이렇게 만들어진 트리를 배열로 나타내면,

          여기서 한가지 알고가면 좋은점은, 가장 처음 트리에서 1은 루트노드에 있었지만, 최대힙을 만들고나니 leaf까지 내려왔죠?

          즉, 어떤 노드는 최대힙을 만드는 과정에서 leaf까지 내려올 수 있으므로 최악의 경우 O(트리의 높이)만큼 내려올 수 있습니다.

          이때의 최악의 경우는 루트노드에 있는 노드가 leaf까지 내려왔을 경우겠죠?

          완전이진트리에서 높이는 logn에 바운드되게 되므로

          즉 O(logn)만큼 내려올 인덱스 바이너리 수 있습니다.

          자. 이렇게 최대힙 만드는 과정이 끝나게 되었습니다.

          이제 2단계로 넘어가야할 시간이네요.

          2단계: delete를 통해 정렬을 한다.

          자. delete?이게뭘까요? 뭘 지우는 걸까요?

          우리가 최대힙을 왜. 만들었을까요?

          네. 최대값을 쉽게 얻을 수 있기 때문이죠.

          부분트리에서 계속 최대값을 부모로 올리면서 완전한 트리까지 해나갔죠?

          그럼 최종적으로 트리의 루트노드에는 뭐가 올까요?

          네. 배열안에서 가장 큰 데이터가 루트노드로 오게됩니다.

          (최소힙은 배열안에서 가장 작은 데이터가 루트노드로 오게 되겠죠?)

          그럼 우리는 이 루트노드를 지우는(delete) 방법을 통해 정렬을 진행해보려고 합니다.

          진짜로 2단계를 진행해봅시다.

          이 2단계를 쉽게 이해하기 위해서 다시 작은 단계들로 쪼개어 볼게요.

          2.1단계 - 맨 마지막 노드와 루트노드를 교환한다.

          2.2단계 - 현재 루트노드에 대해 다운힙(순서조건을 만족하게 하도록 만드는과정)을 진행한다.

          사실 2.1단계는 이 힙정렬이 in-place냐 아니냐에 따라 다릅니다.

          만약 정렬이 완성될 배열을 따로 두게되면,

          루트노드는 지워준뒤, 이 완성될 배열의 맨 뒤에 넣어주고

          트리의 가장 마지막 노드를 루트노드에 넣어줍니다.

          하지만 힙정렬은 in-place정렬이 가능한 정렬알고리즘이에요.

          가장 마지막이 루트노드로 가게되면 가장마지막에 있는 자리(인덱스)는 비게되겠죠?

          그 자리에 가장 큰 데이터인 루트노트가 들어가도 아무 문제 없다는 말입니다.

          2.1단계 : 루트노드와 가장 마지막노드의 자리를 바꾸어준다.

          자. 그러면 배열안에서 가장 큰 데이터인 15가 맨 뒤에 가게 되겠죠.

          이 15는 정렬이 끝났다고 생각해도 무방합니다.

          그냥 노드가 "없다"라고 생각하세요. 마지막노드에 루트노드가 간 순간, 저 노드는 이제 우리가 비교할 비교대상에서 사라진다고 보면 됩니다.


0 개 댓글

답장을 남겨주세요