[mysql] 인덱스

MySQL에서의 인덱스: insert, update, delete의 성능을 희생하고 select의 성능을 향상(update, delete, insert의 행위가 느린것이며 해당 쿼리를 실행하기 위해 데이터를 조회하는 것은 인덱스가 존재하면 빠름)

• B-Tree Index(=Balanced Tree)
  • 설계기준
    • 개수는 3~4개가 적당
    • 인덱스의 키는 길면 길수록 성능이 저하(인덱스 키란 데이터타입(ex: varchar, int ..)의 크기로 이해하면 되고, InnoDB Storage Engine에서 전체 인덱스 키 크기는 767byte이다.)
    • 1개의 컬럼만 Index를 적용시 카디널리티(=Cardinality)가 가장 높은 것으로 잡는게 좋음(인덱스로 최대한 효율을 내기 위해서는 많은 부분을 걸러내야 한다. 예를 들어 성별을 인덱스로 잡으면 남/녀 50% 밖에 못거르나 로그인아이디나 주민등록번호는 대부분을 걸러내기 때문에 빠르게 조회가 가능)
    • 여러 컬럼으로 인덱스 구성시 기준
      • 카디널리티가 높은순에서 낮은순으로 구성하는것이 성능에 좋다
      • 최소한 첫번째 인덱스 조건은 조회조건에 포함해야 인덱스를 탄다

          -- index: group_no(1번 index), reg_date(2번 index), state(3번 index)
        
          -- 첫번째 인덱스 조건인 group_no가 조회조건에 포함된 경우
          -- 인덱스를 탄다
          EXPLAIN SELECT * FROM test WHERE group_no = 1 AND state = true;
        
          -- 첫번째 인덱스 조건인 group_no가 조회조건에 포함되지 않는 경우
          -- 인덱스를 타지 않음
          EXPLAIN SELECT * FROM test WHERE reg_date = '2020-06-07' AND state = true;
        

  • 인덱스 조회시 주의사항
    • 범위조건을 사용하지 않는다
      • BETWEEN, LIKE, <, > 등.. (LIKE는 뒷% 즉, LIKE 'test%' 같이 조회시만 인덱스를 탄다)
    • "=", "IN"은 인덱스를 타지만 IN절에 인자로 서브쿼리가 들어갈 경우 인덱스를 타지 않음(서브쿼리의 외부가 먼저 실행되고 IN은 체크조건으로 실행되기 때문)
    • "AND", "OR" 사용시 주의(OR 연산자는 비교할 Row가 더 증가 하는 것이므로 풀스캔이 발생할 확률이 높아짐)
    • 인덱스로 사용된 컬럼값 그대로 사용해야 인덱스를 탄다

        -- 인덱스를 타지 않는 경우 (price * 2 컬럼값으로 비교하기 때문)
        WHERE price * 2 > 11000;
      
        -- 인덱스를 타는 경우
        WHERE price > 11000 / 2;
      

    • 컬럼과 조회할 조건값의 타입이 다르면 인덱스 적용이 불가
    • null값인 경우 is null 조건으로 index range scan 가능
    • 인덱스 컬럼하고 조회조건의 컬럼의 순서를 일치시켜야 한다(성능상 크게 차이는 없으나 되도록 맞춰주는 것이 좋음)
  • 커버링 인덱스(=Covering Index)

    • 쿼리를 충족시키는데 필요한 모든 데이터를 갖고 있는 인덱스

    • EXPLAIN시 extra 컬럼에 "Using Index" 표기시 커버링 인덱스가 적용된것이다

    • EXPLAIN시 type 컬럼에 "index"가 표기되는 경우 인덱스 풀스캔이 발생한것이다.(range 스캔 X)

      • type 컬럼이 range, const, ref가 아닌 경우
        • 위 조건과 더불어 아래 조건중 하나가 동시 만족된 경우
        • 인덱스에 포함된 컬럼만으로 처리할 수 있는 쿼리인 경우(즉, 데이터 파일을 읽지 않아도 되는 경우)
        • 인덱스를 이용해 정렬이나 그룹핑 작업이 가능한 경우(즉, 별도의 정렬작업을 피할 수 있는 경우)

    • Non Clustered Key와 Clustered Key

      • Clustered Key
        • PK
        • PK가 없으면 Unique Key
        • PK, Unique Key 둘다 없으면 6byte의 hidden key를 생성(rowid)
        • 테이블당 1개만 생성가능
      • Non Clustered Key
        • 일반적인 인덱스
        • 여러개 생성가능

      

      
      
      • Non Clustered Key에는 인덱스 컬럼의 값들과 Clustered Key(PK)의 값이 포함
      • Clustered Key만이 실제 테이블의 row 위치를 알고 있다.
      • MySQL은 Non Clustered Key에 Clustered Key가 항상 포함되어 있다. 이유는 Non Clustered Key에는 데이터블록의 위치가 없기 때문
      • 인덱스 조건에 부합한 WHERE 조건이 있더라도 SELECT에 인덱스에 포함된 컬럼외에 다른 컬럼값이 필요할때는 Non Clustered Key에 있는 Clustered Key값으로 데이터 블록을 찾는 과정이 필요 다만 PK를 사용하는경우 인덱스 탐색시간이 없으므로 향상된 데이터 파일 접근이 가능

    • 커버링 인덱스는 실제 데이터 접근(=Clustered Key)의 행위없이 인덱스에 있는 컬럼값들로만 쿼리를 완성하는 것

      • SELECT 예시 쿼리(index는 id, name, age의 순서대로 설정)

          -- 인덱스는 사용되었으나 SELECT 절의 필드를 완성하기 위해 데이터 블록 접근이 있음(EXPLAIN -> key: 사용한 인덱스, extra: 빈 값)
          SELECT * FROM test WHERE id = 1;
        
          -- 커버링 인덱스 사용(EXPLAIN -> key: 사용한 인덱스, extra: using index)
          SELECT id FROM test WHERE id = 1;
        

      • WHERE + GROUP BY 예시 쿼리(index는 id, name, age의 순서대로 설정)
        • GROUP BY 절에 명시된 컬럼이 인덱스 컬럼의 순서와 같아야 한다.

            GROUP BY name; [X]
            GROUP BY name, id; [X]
            GROUP BY id, age, name; [X]
            GROUP BY id, name, age; [O]
          

        • 인덱스 컬럼 중 뒤에 있는 컬럼이 GROUP BY 절에 명시되지 않아도 인덱스 사용이 가능

            GROUP BY id; [O]
            GROUP BY id, name; [O]
            GROUP BY id, name, age; [O]
          
            -- 반대로 앞에 있는 컬럼이 GROUP BY 절에 명시되지 않으면 인덱스 사용이 불가
            GROUP BY name, age [X]
          

        • WHERE + GROUP BY일 경우 WHERE 조건이 동등 비교일 경우 GROUP BY 절에 해당 컬럼이 없어도 인덱스가 적용됨

            ... WHERE id = 1 GROUP BY name, age; [O]
            ... WHERE id = 1 AND name = 'test' GROUP BY age; [O]
          

        • GROUP BY가 인덱스 순서대로 잘 타지 않는 경우 "Using Temporary", "Using filesort"가 Extra에 표시된다.
      • WHERE + ORDER BY 예시 쿼리(index는 id, name, age의 순서대로 설정)
        • GROUP BY와 유사하지만 정렬기준에 대한 차이가 있다.
        • ORDER BY에서 index가 적용 안되는 경우

            -- 인덱스 첫번째 컬럼인 id가 누락
            ... ORDER BY name, age;
          
            -- 인덱스에 포함된 name 컬럼이 id, age 사이에 미포함되므로 인덱스 적용 X
            ... ORDER BY id, age;
          
            -- name 컬럼 DESC로 인하여 인덱스 적용 X
            ... ORDER BY id, name DESC, age;
          
            -- 인덱스에 존재하지 않는 컬럼 type으로 인하여 인덱스 적용 X
            ... ORDER BY id, name, age, type;
          
            -- 인덱스가 적용되는 경우
            -- 해당 쿼리가 가능한 이유는 실제로 WHERE id = 1 ORDER BY id, name, age와 동일하기 때문, 옵티마이저가 적절하게 실행계획을 결정
            -- 또한 GROUP BY + WHERE와 동일하게 WHERE에 사용된 인덱스 컬럼이 동등비교가 아니면 ORDER BY절은 인덱스 수행 X -> WHERE절만 인덱스를 타게됨(WHERE가 동등비교가 아닐경우에는 ORDER BY에 인덱스 컬럼을 전부 선언하는게 낫다 -> 조회결과가 동일할때만 해당)
            ... WHERE id = 1 ORDER BY name, age;
            ... WHERE id = 1 AND name = 'test' ORDER BY age;
          

      • WHERE + GROUP BY + ORDER BY
        • WHERE + ORDER BY의 경우엔 WHERE가 동등일 경우 ORDER BY가 나머지 인덱스 컬럼만 있어도 인덱스를 탄다.
        • GROUP BY + ORDER BY의 경우엔 둘 다 인덱스 컬럼을 탈 수 있는 조건이여야한다.
      • Descending Index
        • MySQL 8.0 이후부터 지원 -> 많은 레코드를 빈번하게 실행할 경우 사용하면 좋음

     

    [참고]
  • https://jojoldu.tistory.com/243