mysql 에서의 기본적인 것들을 알았으니 이제 실무에 적용 할 추가적인 정보들을 알아보자

• 실행 계획

  • mysql이 쿼리를 통해 어떻게 명령을 수행할 지를 만들어 내는 과정.
  • 실행 계획은 옵티마이저를 통해 만들어지게 된다
  • 옵티마이저의 종류
    • 규칙 기반 최적화: 내장된 우선 순위에 따라 실행 계획을 수립하는 방식 → 거의 사용되지 않음
    • 비용 기반 최적화: 예측된 통계 정보를 이용하여 여러가지 방법 중 가장 최선의 방법을 선택하는 방식
  • 통계 정보
    • 비용 기반 최적화에서 사용될 정보. 데이터베이스에서 DML이 사용될 때 만들어지게 된다
    • Mysql에서는 레코드 건수와 인덱스의 유니크 값의 개수를 주로 이용한다
  • 실행 계획의 해석

  

  • id: 단위 쿼리에 해당하는 id. 서브 쿼리에서는 여러 개의 id를 부여 받으나, 조인에서는 한 개로 통일된다
    • select_type
      • 어떤 타입의 쿼리인지 표시
      • SIMPLE: 기본적인 단순 select 쿼리
      • PRIMARY: union이나 서브 쿼리가 포함된 쿼리에서 가장 바깥쪽의 쿼리
      • union: 각 union 쿼리 . 내부의 쿼리가 먼저 실행된다
      • dependent union, subquery
        • 쿼리가 바깥쪽의 쿼리에 의해 영향을 받는 경우이다
        • 바깥쪽 쿼리가 먼저 실행되므로 비 효율 적인 경우가 많다
      • Derived
        • select의 결과를 임시 테이블을 생성하여 처리한다
        • 인덱스가 없기 때문에 다른 테이블과 조인 시 매우 불리하다
    • table
      • 사용되는 테이블 명을 명시한다
      • <> 둘러싸인 이름의 경우 임시 테이블을 의미한다
    • type
      • 각 테이블의 레코드를 어떤 방식으로 읽었는지 의미
      • 각 종류에 대해 표시하면서 빠른 순서대로 알아보자. 이 중 1,2,3,4의 쿼리는 튜닝의 여지가 별로 없다
      • 종류
        • 1. system: 레코드가 1건만 존재하는 방식. innodb에서는 없다
        • 2. const: PK나 유니크 키 칼럼을 사용하면서 반환 레코드가 1개인 경우
        • 3. eq_ref
          • 조인 쿼리에서만 등장한다
          • 처음 읽은 테이블의 값이 다음 테이블(드리븐 테이블)의 PK, 유니크 인덱스 조건으로 사용되는 경우
        • 4. ref: 동등(equal) 조건으로 검색할 때 사용된다
        • 5. ref_or_null: ref 검색과 null 검색이 동시에 적용되는 경우
        • 6. unique_subquery: 서브 쿼리에서 중복되지 않는 유니크 값만 반환하는 경우
        • 7. index_subquery: 중복된 값을 반환하는 서브 쿼리이나 인덱스를 이용해 제거 가능할 때
        • 8. range
          • 가장 많이 사용되는 방식
          • 범위 검색(<, > , is null, between, in, like)에서 사용된다
        • 9. index_merge: 2개 이상의 인덱스를 이용하여 각각 결과를 만든 후 병합 처리한다
        • 10. index: 인덱스 풀 스캔
        • 11. all: 테이블 풀 스캔
    • possible_key: 사용 가능한 인덱스의 목록. 사용했다는 뜻이 아니다!!!
    • key, key_len: 최종 사용된 인덱스 및 그 키의 길이
    • ref: 의도하지 않았다면 func만 아니면 된다
    • rows: 예측했던 레코드 건수
    • filtered: mysql 엔진에 의해 필터링 되어 제거되지 않고 남은 레코드의 비율. 높을 수록 좋다
    • Extra (중요하지 않아 보이는 항목은 제외했다 좋음, 나쁨)
      • const row not found: 레코드가 없는 경우
      • distinct: 조인하지 않아도 되는 항목은 무시했음을 표시
      • impossible ( having, where ) : 조건을 만족하는 레코드가 없을 때
      • Not Exist: Not In 또는 Not Exist를 최적화한 경우
      • using filtersort: orderBy 쿼리가 인덱스를 사용하지 못할 때
      • using index: 데이터를 전혀 읽지 않고 인덱스로만 쿼리를 처리 가능할 때!!!
      • using index for group-by: groupBy 처리에 인덱스를 사용 할 수 있는 경우. 루스(스킵) 인덱스 스캔이 주로 사용된다
      • using join buffer: 조인 시 드리븐(후행) 테이블에 인덱스가 없는 경우 드라이빙(선행) 테이블의 레코드를 임시 저장하는 메모리를 사용하는 경우
      • using temporary: 임시 테이블을 사용하는 쿼리. union은 이 메시지가 없더라도 사용한다
      • using where: mysql 엔진 레이어에서 별도의 필터링 작업이 필요한 경우. 더럽게 느리다
      • using where with pushed condition
        • 컨디션 푸시 다운이 적용되어 스토리지 엔진에서 별도의 필터링 작업을 하는 경우
  • 주요 처리 방식
    • Mysql에서 정렬은 매우 느리며 특히 select 조건에 text,blob가 포함되면 two-pass를 사용하므로 더욱 느려진다 ( 기본 방식은 one-pass 정렬 방식)
    • 순서 정렬이 필요하다면 기본으로 orderBy를 넣어두자 → Join 시에 임시 테이블을 쓰게 되면 인덱스를 쓰더라도 정렬 순서가 깨질 수 있다
    • 임시 테이블이 필요한 쿼리 조건
      • OrderBy, GroupBy의 명시된 칼럼이 다른 쿼리
      • OrderBy, GroupBy의 명시된 컬럼이 조인 순서 상 첫 번째 테이블이 아닌 경우
      • 쿼리 실행 계획에 select_type이 derived인 쿼리
      • Union이 사용된 쿼리
    • Outer Join을 하는 경우 조건을 On절에 모두 명시하는 습관을 들이자 → 옵티마이저가 자체적으로 innerJoin으로 처리해 버리는 경우가 생긴다
    • Not in 쿼리는 PK가 아닌 경우 인덱스 풀 스캔을 하므로 좋지 않다
    • where에서 조건을 변형해야 한다면 컬럼이 아닌 상수 쪽을 항상 변형하자 → 인덱스를 사용 할 수 있다
    • Null 비교를 해야 한다면 is Null 연산자를 이용하자
    • Union은 기본적으로 distinct 이다 ( distinct는 인덱스가 없으면 임시 테이블을 사용한다 )

• 베스트 프랙티스( 팁 )

  • Join 조건은 항상 On 절에 기재하자!
  • 서브 쿼리는 조인으로 변경하도록 노력하자
  • Union을 쓰느니 쿼리를 두 번 날려서 어플리케이션에서 합치자

• 8.0 업데이트 기술
  • Nosql을 위한 Document Store 엔진 제공
  • SQL 추가: CTE(common table expresion), NoWAIT, Skip LOCKED 제공
  • 인덱스: 내림차순 인덱스 가능, Invisibe 인덱스 기능 추가
  • 옵티마이저는 메모리 캐싱 여부를 포함하여 쿼리 플랜을 작성합니다.
  • 유저 롤 및 캐시 관련 기능 추가
  • 쿼리 캐시 삭제
  • 성능 향상 및 버퍼 풀 관련 최적화

• mysql에서 실행 계획은 explain 명령어로 확인할 수 있다
• 컨디션 푸시 다운: 범위 제한 조건, 체크 조건 관계없이 스토리지 엔진으로 전달해서 필터링하는 방식
• Not In, Exist는 이렇게 최적화 하자
• 현재 커넥션의 마지막 Auto_incresement 값은 LAST_INSERT_ID() 함수를 통해 알 수 있다
• table의 rename은 한 트랜잭션에 여러 건 수행 할 수 있다
• 바인딩 쿼리(프리페어 스테이트먼트): 프로그램에서 변수를 사용하는 쿼리문
• 동적 쿼리(다이나믹 쿼리): 바인딩 변수 없이 사용하는 쿼리 → 쿼리 캐싱이 불가능하다