ABOUT ME

    -

    Today
    -
    Yesterday
    -
    Total
    -
    • MySQL index의 기록 (22.11~22.12 HospitAl Talk 프로젝트)
      MySQL 2022. 12. 19. 20:41

      인덱스

       

      • 프로젝트의 현재 검색시스템은 covering index를 활용해 쿼리 실행계획의 extra 항목에 Using index(디스크를 읽지 않고 메모리에서 모두 처리될 때. 가장 효율적인 표시) 를 띄우긴 했습니다.

       

      • 거리 정렬 과정에서 나온 Using filesort 라는 신호는 따로 함수를 빼고 mysql에 시키지 않으면서 제거.

       

      • 그럼에도 type이 index라는 것은, 인덱스 스캔으로 범위를 줄이지 못하고(당연합니다. x에서 인덱스 스캔을 하고 나면 y는 인덱스를 타지 못하거든요...) 전체 인덱스를 모조리 읽고 있다는 뜻입니다. 그런데 mysql이 filesort 할 때…가 더 퍼포먼스가 좋았던 거 같은데 ㅠ 하도 Using filesort를 무슨 absolute evil 처럼들 말해서…

       

      • 커버링인덱스(using index를 띄운 현재 상태): 간단합니다. Select 해오는 모든 칼럼을 모조리 한 인덱스그룹에 넣어버리면 됩니다

       

      • 추후 대안이 될 수도 있는 prefix 인덱스 : 현재 죄표를 분기에 따라 1~5 km단위로 (혹은 정 안되면 외국어검색의 경우 서울시 전체도 있긴 합니다) 찾는데, 그게 지금 가진 좌표 데이터의 앞자리 3개만 비교하면 됩니다. 이걸로 일치조건을 만들어 실험해볼 수는 있겠으나 얼마나 개선이 될지, 여기에 리소스를 투입할 가치가 있을지 의문입니다.

       

      인덱스 적용점의 모색

      인덱스의 이점

      • 서버가 검사해야 하는 데이터의 양을 줄인다.
      • 서버가 정렬 및 임시 테이블을 생성하지 않게 돕는다.
      • 인덱스는 랜덤 IO 를 순차 IO로 바꾼다.

      B-트리 인덱스

      • 루트 노드에서 시작, 루트 노드에는 하위노드에 대한 포인터가 있음
      • 리프 노드(리프 페이지) : 다른 노드, 페이지에 대한 포인터 대신 인덱싱된 데이터에 대한 포인터를 가지고 있다.

      적응형 해시 인덱스

      • Inno DB 스토리지 엔진은 일부 인덱스 값이 매우 자주 엑세스 되고 있음을 감지하면 B-트리 인덱스 위에서 메모리에 해시 인덱스를 구축.
      • 이 프로세스는 완전히 자동. 모두 비활성화는 할 수 있지만 제어하거나 구성할 수 없다.

      Prefix 인덱스 및 인덱스 선택성

      • 선택성 : 테이블 총 행수(aka T)에 대한 고유 인덱스 카디널리티의 비율.
        • 선택성 = cardinality / T
        • 최소 1/T ~ 최대 1 : 선택성이 1이면, 이보다 더 좋을 순 없습니다.
      • 프리픽스 인덱스 : 적절한 접두사(몹시 뭣같은 번역어이지만 책에 적힌 그대로를 따릅니다.) 를 선택해야.
        • 적절한 접두사 길이 계산1 : 전체 열의 선택성 계산, 접두사의 선택성을 해당 값에 가깝도록.
        #병원 좌표 가장 빈번한 것 찾기. 
#0이 361개... 좌표 받아와서 다 넣을 것.
#446869.xxx... 가 36개, 이외에도 같은 X좌표가 있는 것들이 35개, 30개, 28개 등등.. 
#의외로 겹치는게 많다. 찾아보니, 같은 빌딩에 있는 병원들.
select count(*) as count, X from Hospitals h Group by X Order by count DESC LIMIT 10;

#X좌표 칼럼의 선택성 = 0.4818
select count(distinct X)/count(*) from Hospitals h
#Y좌표 칼럼의 선택성 = 0.4822
select count(distinct Y)/count(*) from Hospitals h

#sel1 0.0114, sel2 0.0953, sel3 0.3588, sel4 0.3588, sel5 0.467
#sel6 0.48 sel7 0.4816
select count(distinct left(X, 4))/count(*)  as sel1, 
count(distinct left(X, 5))/count(*) as sel2,
count(distinct left(X, 6))/count(*) as sel3, 
count(distinct left(X, 7))/count(*) as sel4, 
count(distinct left(X, 8))/count(*) as sel5,
count(distinct left(X, 9))/count(*) as sel6,
count(distinct left(X, 10))/count(*) as sel7
from Hospitals h
        • 당연히 왼쪽부터 맞추는 길이가 길어질 수록, 전체 열의 선택성과 비슷해진다.
        • X좌표의 경우, 8자(sel 5)부터 선택성에 큰 개선이 있다.
        • 평균적인 선택성으로 몇글자가 (위의 경우 8~9자가) 충분하다고 생각할 수 있지만, 데이터가 고르지 않다면 함정일 수 있다.
        • 예컨대, 도시 이름 데이터의 경우. 첫4글자는 San, Sant 등이 가장 빈번할 수 있다.
        • 그러나 정작 가장 빈번한 전체 길이 값(London)과 다르다.
        • san, sant, new 등으로 시작하는 도시데이터에 대한 선택성이 매우 떨어질 것이다.
        • 프리픽스 인덱스의 이점을 얻을 수 있는 흔한 경우로는 긴 16진수 식별자를 사용하는 경우 등.
        • 처음 8자 내외 문자에 인덱스를 추가하면 애플리케이션에 전혀 영향을 미치지 않고 성능이 크게 향상된다.

      다중열 인덱스

      • 일반적인 실수 : 1. 열을 여러개, 2.모든 열을 개별적으로 인덱싱, 3. 열을 잘못된 순서로 인덱싱
      • 실수 2번 : 많은 열에 대한 개별 인덱스 : 쿼리 성능 향상에 도움X.
        • 인덱스 병합 index merge 로 알려진 전략은 이렇게 제대로 인덱싱 되지 않은 테이블에 약간 대처가 가능.
        • 여러 인덱스를 제한적으로 사용, 결과 병합.
        • explain을 하면 extra 항목에 Using union 이라고 나온다.
        • 인덱스 병합 전략은 때때로 매우 잘 작동하지만 대개는 사실 그냥 제대로 인덱싱 되지 않은 테이블이다.
        • 서버가 인덱스를 교차할 때. (보통 AND 조건) 여러 인덱스를 결합할게 아니라 모든 관련 열들을 가진 단일 인덱스가 필요.
        • 서버가 인덱스를 통합할 때. (보통 OR 조건) CPU 및 메모리 자원 많이 사용. 모든 인덱스가 선택성이 좋은 것이 아니므로 스캔에서 많은 행을 병합작업에 반환하는 경우 특히 그렇다.
          • 옵티마이저는 이런 비용을 고려하지 않는다. 옵티마이저는 랜덤 페이지 읽기 수만 최적화한다.
          • 메모리와 CPU사용량이 많은 경우, 쿼리를 독립적으로 실행하고 UNION 절을 실행하는 것이 낫다.
        • explain 에서 인덱스 병합이 확인될 경우, 쿼리와 테이블 구조를 검토할 것.
        • optimizer_switch 옵션 또는 변수와의 인덱스 병합을 비활성화할 수 있다.
        • IGNORE INDEX를 사용할 수도 있다.
      • 실수 3번 좋은 열 순서 선택하지 못함 : 가장 일반적인 혼동
        • 여러 칼럼으로 구성된 B트리 인덱스.
        • 맨 왼쪽 칼럼 기준으로 먼저 정렬됨.
        • 따라서 인덱스를 정방향 또는 역순으로 스캔하여 열순서와 정확히 일치하는 order by, group by, distict 절로 쿼리를 충족할 수 있다.
        • (183쪽) 인덱스 별점 매겨줌. 3개 만점.
          • 인덱스가 관련 행을 서로 인접하게 배치하면 별1개 (검사할 데이터 범위를 많이 줄인다)
          • 행이 쿼리에 필요한 순서대로 정렬되면 별2개
          • 쿼리에 필요한 모든 칼럼이 인덱스에 포함되어 있으면 별3개
        • 위 별점 따는 것은 오려된 경험칙이 있다.
          • 가장 선별적인 칼럼을 인덱스에 먼저 배치할 것.
          • 그러나 이것은 랜덤I/O 및 정렬을 피하는 것보다 훨씬 덜 중요하다.
          • 가장 선별적인 칼럼을 먼저 배치하는 것은 고려할 정렬이나 그룹화가 없을 때 좋은 방안이 될 수 있다.

      커버링 인덱스

      • where절에 인덱스 들어가는 것은 일부에 불과하다.
      • 인덱스는 전체 쿼리에 대해 설계되어야 한다.
      • 쿼리를 충족하는 데 필요한 모든 데이터를 포함하는 인덱스를 covering index라 한다.

      MySQL : 풀텍스트 인덱스

      • 풀텍스트 인덱스 추가
       

      [MySQL] 13. 인덱스의 내부 작동 원리와 구조, 인덱스에서 데이터 검색하기

      클러스터형 인덱스와 보조 인덱스는 모두 내부적으로 균형 트리로 만들어진다. 균형 트리(Balanced Tree)는 '자료 구조'에 나오는 범용적으로 사용되는 데이터의 구조로써 데이터를 검색하는 데 효

      daegwonkim.tistory.com

      ALTER TABLE Ans ADD FULLTEXT (address)

       

      • 테이블의 인덱스 확인
      SHOW INDEX FROM Ans

       

      • SHOW INDEX FROM Ans
      • explain 명령으로 이 쿼리의 작동을 확인
        • 풀텍스트 인덱스는 LIKE 등의 명령어나 %강남구%(강남구 포함하는 것) 같은 리터럴을 사용할 수 없습니다.
        • MATCH(칼럼명) AGAINST(’검색할 문자열’)
        EXPLAIN SELECT * FROM Ans WHERE MATCH(address) AGAINST('강남구')
         

      • index를 타지 않는다면, possible_keys가 NULL.
      • rows의 1행만을 조사했다는 것. 곧 테이블을 딱 한번 뒤졌다는 뜻이 됩니다.
      • 글자수 변경
        • full text index는 default 값이 3글자 이상.
        • 아래 쿼리로 확인 가능
      SHOW VARIABLES LIKE 'innodb_ft_min_token_size';

       

       

      'MySQL' 카테고리의 다른 글

      MySQL partitioning의 기록 (22.11~22.12 HospitAl Talk 프로젝트)  (1) 2022.12.19
      MySQL 튜닝의 기록 (22.11~12 HospitAI Talk 프로젝트)  (0) 2022.12.19
      Sequelize) createdAt의 timezone 설정  (0) 2022.10.29
      SQL : group by, order by  (0) 2022.10.09
      SQL : 범위조건 between  (0) 2022.10.09

      관련글 관련글 더보기

    Designed by Tistory.

    티스토리툴바