MariaDB/Admin

MariaDB 옵티마이저 힌트 및 실행 계획 분석 시 주의사항

알 수 없는 사용자 2015. 10. 12. 23:23
  1. 옵티마이저 힌트
    1. 힌트사용법
      1. 힌트는 SQL 일부로 해석되기 때문에 잘못 사용할 경우 오류를 발생시킨다.
      2. SELECT * FROM employees USE INDEX (PRIMARY) where emp_no=10001;
      3. SELECT * FROM employees /*! USE INDEX ( PRIMARY) */ WHERE emp_no=10001;
      4. CREATE /*! 32302 TEMPORARY */ TABLE temp_emp_stat …
    2. STRAIGHT_JOIN
      1. 조인의 순서를 FROM 절에 명시된 테이블 순서대로 고정시키는 힌트 (오라클 ORDERED)
      2. 다음 기준일 경우 힌트 사용을 권고함
        1. 임시 테이블과 일반 테이블 -> 일반적으로 임시 테이블을 드라이빙 테이블로 선정하는 것이 좋으나, 일반 테이블에 조인 컬럼이 존재하지 않는 경우에는 레코드 건수가 적은 테이블을 드라이빙 테이블로 선정
        2. 임시 테이블과 임시 테이블 -> 레코드 건수가 적은 테이블을 드라이빙 테이블로 선정
        3. 일반 테이블과 일반테이블 -> 양쪽에 인덱스가 존재하거나, 존재하지 않을 때는 레코드 건수가 적은 테이블을 드라이빙으로 선정, 그 이외에는 조인 컬럼에 인덱스가 없는 테이블을 드라이빙 테이블로 선정
    3. USE INDEX / FORCE INDEX / IGNORE INDEX
      1. 인덱스 사용여부를 설정하는 힌트로 인덱스를 가지는 테이블 뒤에 명시
      2. 인덱스 힌트 종류
        1. USE INDEX -> 특정 테이블의 인덱스를 사용하도록 권장, 대부분 힌트의 인덱스를 사용하지만 항상 사용하는 것은 아님
        2. FORCE INDEX -> USE INDEX보다 옵티마이저에게 미치는 영향이 더 강하나 USE INDEX 힌트만으로 충분
        3. IGNORE INDEX -> 특정 인덱스를 사용하지 못하도록 막는 힌트, 풀 테이블 스캔 유도 시 사용
      3. 인덱스 용도 명시
        1. USE INDEX FOR JOIN -> 데이터 검색 및 조인 시 인덱스 사용
        2. USE INDEX FOR ORDER BY -> 인덱스를 ORDER BY 용으로만 사용
        3. USE INDEX FOR GORUP BY -> 인덱스를 GROUP BY 용으로만 사용
    4. SQL_CACHE / SQL_NO_CACHE
      1. 쿼리의 결과를 일시적으로 캐시에 저장할 지의 여부를 설정하는 힌트
      2. 보통 시스템 변수값이 쿼리 캐시로 사용되기 때문에 SQL_CACHE 힌트는 자주 사용되지 않음
      3. SQL_NO_CACHE 힌트는 쿼리가 캐시되지 않았을 때 소요되는 시간을 측정함으로써 쿼리 성능 분석용으로 많이 사용함
    5. SQL_CALC_FOUND_ROWS
      1. LIMIT을 사용하는 경우 조건에 만족하는 레코드 전체 건수에 관계없이 LIMIT에 명시된 수만 출력되는데, 이때 해당 힌트를 사용하면 LIMIT과 상관없이 조건에 만족하는 전체 레코드 검색을 수행
      2. 테이블 전체를 읽어야 하기 때문에 사용하지 않는 것을 권고
  2. 실행 계획 분석 시 주의사항
    1. SELECT_TYPE컬럼의 주의 대상
      1. DERIVED -> 인라인 뷰에서 발생한 임시테이블로, 메모리 또는 디스크에 저장되지만 디스크에 저장할 경우 성능이 떨어짐
      2. UNCACHEABLE SUBQUERY -> 사용자 변수나 일부 함수가 서브쿼리로 사용될 경우, 재사용이 불가능 함
      3. DEPENDENT SUBQUERY -> 외부 쿼리에서 값을 전달받아 실행되는 경우, 외부 쿼리 결과에 의존적이기 때문에 전체 쿼리의 성능을 저하시킴
    2. TYPE 컬럼의 주의 대상
      1. ALL, INDEX -> ALL은 테이블 풀 스캔을, INDEX는 인덱스 풀 스캔을 의미하며 전체 레코드를 대상으로 하는 작업 방식이라 속도가 빠르지 않다. 따라서 인덱스를 추가해서 이러한 접근 방법을 제거하는 것이 좋음
    3. KEY 컬럼의 주의 대상
      1. 쿼리가 인덱스를 사용하지 못할 때 실행계획의 KEY 컬럼에 아무 값도 표시되지 않으며, 인덱스를 추가하거나 WHERE 조건을 변경하는 것이 좋음
    4. ROWS 컬럼의 주의 대상
      1. 쿼리의 실제 출력 레코드 수보다 ROWS 컬럼에 표시되는 레코드 수가 많을 경우, 인덱스를 정상적으로 사용하는지 확인이 필요
      2. ROWS의 수치를 판단할 때, LIMIT과 상관없이 ROWS가 출력될 수 있으며, 성능상 최적화된 쿼리일 수도 있음
    5. EXTRA 컬럼의 주의 대상
      1. 쿼리가 요건을 제대로 반영하고 있는지 확인해야 하는 경우 -> 성능과 관계가 깊지 않으나 조건에 맞는 레코드가 존재하지 않다는 의미이기 때문에 쿼리의 요건이 맞는지 확인이 필요
        1. Full scan on NULL key
        2. Impossible HAVING
        3. Impossible WHERE
        4. Impossible WHERE noticed after reading const tables
        5. No matching min/max row
        6. No matching row in const table
        7. Unique row not found
      2. 쿼리의 실행 계획의 좋지 않은 경우 -> 쿼리의 최적화 여부를 확인해야 함
        1. Range checked for each record
        2. Using filesort
        3. Using join buffer
        4. Using temporary
        5. Using where
      3. 쿼리의 실행 계획이 좋은 경우 -> 최적화되어서 처리됨을 의미
        1. Distinct
        2. Using index
        3. Using index for group-by

'MariaDB > Admin' 카테고리의 다른 글

스토리지 엔진 2  (0) 2015.10.20
MariaDB 최적화 1  (0) 2015.10.14
MariaDB 란?  (0) 2015.10.07
MariaDB 실행 계획 분석 1  (0) 2015.10.06
MariaDB 설치  (0) 2015.10.05