MySQL 스터디-2

Real MySQL 정리 (확장 기능, 사용자 정의 변수, 실행 계획)

08 확장 기능

FULL TEXT SCAN

• 풀 텍스트 서치는 문장이나 문서의 내용에서 키워드를 검색하는 기능
• LIKE같은 패턴 검색은 인덱스를 사용하지 못할 수 있지만, 이런 전문 검색은 일부만 검색하는 경우에도 전용 인덱스를 사용하기 때문에 빠른 검색이 가능
• CHAR, VARCHAR, TEXT 에서만 사용 가능
• InnoDB FULLTEXT 인덱스는 반전된 인덱스(Inverted Index) 설계를 가지고 있음. 반전 색인은 단어 목록과 각 단어에 대해 단어가 나타나는 문서 목록을 저장함. 근접 검색을 지원하기 위해 각 단어의 위치 정보도 바이트 오프셋으로 저장됨.

공간 검색

• 좌표 정보를 검색하기 위해 r-tree 인덱스를 사용함
• 거리를 기반으로 하는 위도 경도 계산을 할 때 ( 예를 들어 특정 위치 반경 5km 이런 식으로)
• R-Tree 인덱스를 사용해야 하는 이유
  - 좌표 데이터를 X좌표, Y좌표 2개 컬럼으로 만들어서 B-Tree composite 인덱스를 걸면 Left-most 특성땜에 특정 영역 범위 검색 시 한 쪽밖에 인덱스를 못 탐.
  → 데이터의 분포에 따라서 거의 풀스캔 하다시피 하는 경우가 생길 수 있음
• 성능: 위치 정보 검색 시 R-Tree 인덱스가 B-Tree와 비교해서 전반적으로 검색이 빠름. 단, 검색하는 영역이 너무 넓으면 느림

09 사용자 정의 변수

사용자 정의 변수

• 사용자가 정의하는 변수는 @로 시작하고, integer, decimal, float, binary, 문자열만 저장가능, NULL도 됨
• 예제는 @rownum을 생성해서 초기값으로 0을 할당했고, 이를 매번 1만큼 증가시켜서 저장해서 rownum으로 조회
• 사용자 변수는 하나의 커넥션에서 공유되기 때문에 커넥션 풀을 사용하는 경우에 초기화를 안한다면 문제가 생길 수 있음.

누적 합계 구하기

• from 절은 acc_salary를 0으로 초기화하기 위한 서브 쿼리
• 이 쿼리는 급여 테이블에서 10개의 레코드를 읽고 acc_salary라는 칼럼에는 누적된 salary 합계 값을 가져오는 쿼리

06 실행 계획

쿼리 실행 절차

1.SQL 파싱: SQL 구문 쪼개기
2. 파스 트리를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택(불필요한 조건 제거, 조인 순서 결정, 인덱스 통계정보를 이용해 인덱스 결정 등)
3. 2에서 결정된 테이블의 읽기 순서나 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져옴

통계정보

• MySQL은 비용 기반의 옵티마이저를 사용함
• 비용 기반 최적화는 미리 예측된 통계 정보를 이용해 각 실행 계획별 비용을 산출함. 산출된 각 실행 방법별로 최소 비용이 소요되는 처리 방식을 선택해 최종 쿼리를 실행
• 그렇기 때문에 통계 정보를 잘 수집하는게 중요함. 예를 들어 레코드 크기 등이 정확하지 않다면 엉뚱하게 쿼리가 돌아갈 수 있기 때문
  - MySQL은 오라클보다 통계 정보가 그리 다양하지 않음.
  - 대략의 레코드 건수, 인덱스의 유니크한 값 등
  - Oracle은 통계정보가 정적이고 수집에 많은 시간이 소요되기 떄문에 따로 백업하기도 함. (실제로 벅스 오라클 버전업그레이드시 통계정보를 미리 백업했다가 새로운 디비에 옮겼음)
  - MySQL에서는 좀더 동적이다, 개발용 DB에서는 가끔 ANALYZE 커맨드로 직접 갱신해야 할 때도 있음
• InnoDB에서 ANALYZE 수행동안 읽기와 쓰기 모두 불가능

실행 계획 분석

• EXPLAIN [ EXTENDED | PARTITIONS] 로 분석 가능
• select 에 대해서만 분석 가능하며, 필요에 따라 쿼리의 일부분을 직접 실행할 때도 있어서 조회가 느려질 가능성도 있음

실행계획분석 — id

쿼리문에 있는 select를 한개의 단위로 나눠서 생각하고, 이 단위를 표시한게 id 컬럼. 위 쿼리는 select 가 세번 나와서 id도 3까지 있음. 하나의 select 문장 안에서 여러 테이블을 조인할때는 id는 하나

실행계획분석 — select_type

각 단위 select 쿼리가 어떤 타입인지 표시되는 칼럼

• simple: 단순한 쿼리, 반드시 하나만 존재
• primary: 서브쿼리가 있는 쿼리의 가장 바깥쪽 쿼리
• union: union으로 결합하는 쿼리 중 첫번째를 제외한 쿼리
• dependent union: union 으로 결합된 쿼리가 외부의 영향을 받는다는 의미 (예를 들어 쿼리의 where절이 외부에 있는 쿼리랑 값을 비교한다던가
  — dependent 키워드를 포함하는 서브 쿼리는 외부 쿼리에 의존적이기 때문에 외부 쿼리보다 먼저 실행될 수 없음, 그래서 이 키워드가 포함된 서버 쿼리는 비효율적인 경우가 많음)
• union result: union all이나 union 모두 임시테이블을 생성하게 되고, 이 테이블이 생김, 실제 쿼리가 아니어서 id값은 부여되지 않음
• subquery: from 절에 사용된 서브쿼리는 derived, 나머지는 다 subquery
• dependent subquery: 서브쿼리가 바깥에서 정의된 칼럼을 사용하는 경우
• derived: 서브쿼리가 from절에 사용된 경우. derived는 이 select 결과를 임시 테이블을 생성해서 담아둔다는 것을 의미함. 파생테이블에는 인덱스가 없어서 다른 테이블과 조인할때 성능상 불리한 경우가 많음. from절에 서브쿼리를 쓰지 말고 조인을 쓰자
• uncacheable subquery: 한개의 쿼리 문장에서 서브 쿼리가 하나 있더라도 그게 한번만 실행되는게 아니기 때문에. 이전의 실행 결과를 그대로 사용할 수 있게 결과를 내부적인 캐시 공간에 담아둠. 서브쿼리는 바깥쪽의 영향을 받지 않으므로, 처음 한번 실행해서 그 결과를 캐시하고 필요할 때 캐시된 결과를 이용. 그냥 서브쿼리와 이건 이 캐시를 사용할 수 있느냐 없느냐 이런 차이가 있다. 서브쿼리에 포함된 요소에 따라 쿼리를 사용 못 할수도 있음. uncacheable이라고 나오는 건 캐시를 사용 못한다는 뜻.
• uncacheable union: 같은 의미.

table

실행계획은 테이블 기준으로 표시됨.

• <drived> 이런건 임시테이블을 의미, 숫자는 id를 의미.
• 첫번째 라인이 테이블 컬럼이 derived2인걸로 봐서 쿼리 id가 2인 라인이 먼저 실행되고, 그 결과가 파생테이블로 준비된다는 것을 의미
• 세번째 라인을 보면 select_type 칼럼이 derived로 되어있음. 따라서 dept_emp 테이블을 읽어 파생 테이블을 생성하는것을 알 수 있음.
• 첫번째, 두번째의 라인이 같은 id값을 가지고 있기 때문에 두개 테이블이 조인되는 쿼리라는 것. derived2 테이블이 e보다 위에 있기 때문에 e 테이블을 먼저 읽고, 그다음 e 테이블을 읽었음을 알 수 있음

type(⭐️)

MySQL 서버가 각 테이블 레코드를 어떤 방식으로 읽었는지를 의미, 인덱스를 사용했는지 테이블을 풀 스캔으로 읽었는지 등. 완전 중요한 정보. 여기에서 ALL을 제외하고는 다 인덱스를 사용하는 접근 방식을 의미, 순서대로 가장 빠른 순으로 나열한 것.

• system: 레코드가 한건, 혹은 0건일때, Innodb에서는 나타나지 않음, innodb에서는 index나 ALL로 표시됨
• const: 쿼리가 PK나 unique 키를 이용하는 where 조건절을 가지고 있음, 반드시 한건만 반환 (오라클의 unique index scan)
• eq_ref: 조인되는 테이블에서 두번째 쿼리가 한건 존재할때. 예제는 첨에 dept_emp 를 읽고 그 다음에 e 테이블을 읽는데, emp_no는 pk라서 한건만 읽히기 때문에 eq_ref
• ref는 인덱스의 종류에 상관없이 동등 조건으로 검색할 때 사용됨. 레코드가 1건이라는 보장은 없지만 매우 빠름.
• fulltext: 풀텍스트 인덱스를 사용해 레코드를 읽을 때 사용. 통계정보가 관리되지 않고, 이거 쓰려면 다른 SQL 문법을 써야 해서, 이걸 써야할땐 비용에 상관 없이 거의 fulltext 를 사용함.
• ref_or_null: ref와 같은데, null 비교가 추가됨. 잘 안씀
• unique_subquery: where 절에서 in subquery를 쓸 때 사용, 서브쿼리가 유니크값만 반환할때 이걸 사용
  — IN 조건에서 중복이 없기 때문에 중복 제거 작업이 필요하지 않음
• index_subquery: In절에 중복값이 있을 수 있지만, 인덱스로 제거할 수 있을 때 사용
• range: index range scan. 인덱스를 범위로 검색.
  -> 인덱스를 효율적으로 사용한다: const, ref, range
• index_merge: 두개 이상의 인덱스를 병합. 여러 인덱스를 읽어야 해서 효율성이 떨어짐.
• index: index full scan, 효율적이진 않지만 풀테이블스캔보다는 빠름
• all: 풀 테이블 스캔. read ahead라 하며, 한번에 여러 페이지를 읽어서 처리할 수 있음.

key

실제 선택된, 실행계획에서 사용하는 인덱스를 의미. possible_keys 라는 컬럼도 있는데, 그건 후보로 선정했던 키. index_merge 를 제외하고는 한개씩 나옴.

key_len

다중 칼럼으로 만들어진 인덱스에서 몇개의 칼럼까지 사용했는지, 정확하게는 각 레코드에서 몇 바이트까지 사용했는지

ref

type이 ref이라면 어떤 값을 비교했는지가 나옴. 밑에는 func인데 이는 조인 칼럼의 이름이 아니라, 값이 연산을 거쳐서 참조되었다는 것을 의미함.

rows

반환하는 레코드의 예측치가 아니라, 쿼리를 처리하기 위해 얼마나 많은 레코드를 디스크로부터 읽고 체크해야하는지를 의미

extra(⭐️)

성능에 관련된 중요한 내용이 extra에 자주 표시됨

• const row not found: const 방식으로 읽었지만 테이블에 레코드가 1건도 없을때
• distinct: select절에 distinct로 값을 읽어오는 경우 조인하지 않아도 되는 항목은 무시하고 필요한 것만 조인함
• full scan on null key: col1 (select col2 from …) 이런 문장에서 col1이 NULL일때에 full scan을 하겠다는 쿼리
• impossible HAVING: having 에 만족하는 레코드가 없을 때 발생, 쿼리를 다시 짜야 함
• impossible WHERE: where 절이 항상 false가 될 수 밖에 없는 경우에 표시됨 (예를 들어 not null 컬럼이 null인 조건)
• impossible where noticed after reading const tables: 실제 데이터를 읽었을때 레코드가 없었음을 의미 (실행계획에서 실제 데이터를 실행해본다는걸 의미)
• no matching min/max row: min, max를 select 했을 때 일치하는 레코드가 없을때
• no matching row in const table: impossible where이랑 마찬가지이지만, 조인하는 테이블에서 나타남
• no table used: select 1 from dual 같은 테이블을 사용 안하는 절에서 나타남
• not exists: not in, not exists 절을 처리할 때 보통은 anti join을 사용하지만, 레코드 건수가 많을 때에는 outer join을 이용하면 더 빠르다. 이 값은 아우터조인을 사용해서 안티조인과 동일한 수행을 할 때 나타남
• range checked for eaqch record: 매 레코드마다 인덱스 레인지 스캔을 체크한다. 무슨 뜻이냐면, 왼쪽 쿼리에서 예를들어 e1.emp_no가 1이고 e2테이블이 1억건이 있다면 풀테이블스캔으로 읽는 것이 유리하지만, e1.emp_no가 클 경우에는 index range scan이 빠르기 때문
• scanned N databases: information schema를 읽을 때 N개의 DB 메타 정보를 읽었다는 것을 보여줌.
• select tables optimized away: min(), max()만 select 절에 사용되었을 때 인덱스를 이용해서 한건만 읽을 수 있을 때 사용됨
• skip_open_table, open_frm_only, open_Trigger_only, open_full_Table: information schema를 조회할때 표시됨
• unique_not_found: 두개의 테이블이 각각 유니크로 아우터 조인을 수행하는 쿼리에서 아우터 테이블에 일치하는 레코드가 존재하지 않을 때.
• using filesort: orderby가 인덱스를 사용하지 못할 때
• using index: 데이터파일을 읽지 않고 인덱스만 읽어서 쿼리를 처리할 수 있을 때 이걸 커버링 인덱스라 하고, 엄청 빠름. innodb부터는 index의 leaf 노드에 pk값이 저장되기 때문에, 쿼리에 인덱스 키값 외에 pk값이 포함된 경우에도 이렇게 사용될 수 있음
• using index for group-by: group by 할 때 정렬하고 정렬된 결과를 그룹핑할는데, 인덱스를 사용하면 정렬작업이 필요없기 때문에 효율적이고 빠르다. 그런데 count(*)나 sum이나 평균 구하는것처럼 모든 값을 다 읽을 때에는 이게 아니라 using_index 가 나오고, 이건 loose index scan을 이용해 그러니까 min, max값이나 groupbyt 하는 값만 읽을 때에만 이 방식으로 처리된다.
• using join buffer: 조인 칼럼에 적절한 인덱스가 없을 때 임시로 조인 버퍼를 만든다는 것을 의미
• using sort_union, using uion, unsing intersect: 앞에서 type이 index_merge라는건 두개 이상의 인덱스를 사용한다는 것이었다. 이때 extra 칼럼에는 이걸 어떻게 병합했는지 상세하게 설명함
  — using intersect: 조건이 and로 연결된 경우
  — using union: 조건이 or로 연결된 경우
  — using sort_union: 조건이 or이고 대량의 range 조건일때
• using temporary: 임시테이블을 생성한다는 것
• using where: 스토리지 엔진에서 데이터를 읽어와서 MySQL 엔진에서 필터링이나 가공을 해서 전달할 때에 표시됨

EXPAIN EXTENDED

• rows: 스토리지 엔진이 전체 100건을 읽음
• filtered: MySQL 엔진에 의해 필터링되고 20만 남음을 의미

Q&A

Q1. 쿼리 튜닝할때 보이면 바꿔야 할 지표는?
A1. type 컬럼에 ALL이나 INDEX가 있다면 full table scan 이나 index full scan을 하는 것이기 때문에 바꿔야 함. extra 컬럼에 join buffer가 보이면 왜 나오는지 확인해야 하지만, 무조건 고쳐야 할 것은 아님. 옵티마이저에서 알아서 계산해서 오히려 더 좋은 성능을 가지고 오는 경우가 더 많음. file sort가 보이면 인덱스를 못타는 것이니까 확인해야 한다. rows에 나오는 숫자가 크면 확인해야 함
Q2. 쿼리 튜닝이 끝났음을 알 수 있는 지표는?
A1. 실행해서 빠르면 됨

풀 테이블 스캔

innodb에서는 read ahead에 의해 다음 데이터 페이지를 innodb 버퍼 풀에 가져다 둠. 백그라운드 스레드가 한번에 최대 64개의 데이터 페이지까지 읽어서 버퍼 풀에 저장할 수 있음.

order by

- 인덱스가 없을 때, 혹은 사용을 할 수 없을 때 filesort라는 방법을 사용해서 정렬을 함
- 모든 컬럼을 가져와서 소트 버퍼에 넣고 졍렬을 함. 그대로 클라이언트에 전달.
- 예전에는 버퍼 공간이 작아서 정렬할 컬럼만 가져오고 다시 테이블에 가서 읽어오는 방식 (two pass 알고리즘) 을 사용함, 이 방식은 single path인데 공간이 더 많이 듬

쿼리 처리 방식

• 스트리밍 처리: 엄청 큰 테이블을 select 하면 나머지는 쿼리가 돌아가고 있어도 첫번째 레코드는 아주 빨리 가져올 수 있음. 이건 풀 테이블 스캔의 결과가 버퍼링 처리나 필터링 과정 없이 바로 클라이언트로 스트리밍 되기 때문
• 버퍼링 처리: order by, group by는 스트리밍 처리를 불가능하게 함. 모든 데이터를 가져온 후 정렬하거나 그룹핑해서 보내야 함

group by

• index scan: 조인의 드라이빙 테이블에 속한 칼럼만 이용해서 그룹핑할때, group by 칼럼으로 이미 인덱스가 있다면 그 인덱스를 차례대로 읽으면서 그룹핑 할 수 있음
• loose index scan: 인덱스의 레코드를 건너뛰면서 필요한 부분만 가져오는 것을 의미.
• temporary table: 인덱스를 전혀 사용할 수 없을 때 이 방식으로 처리됨

distinct

• select distict 는 group by와 거의 같은 방식으로 처리됨
• count(),min(),max() 와 같은 집합 함수 내에서 distinct가 사용되면 임시테이블을 사용함. 임시테이블은 distinct 테이블 칼럼을 저장하기 위한 임시 테이블을 만들어서 사용함. emp 테이블과 salaries 테이블을 조인한 결과에서 salary 칼럼의 값만 저장하기 위한 임시 테이블을 만들어서 사용함.

join

• MySQL 에서는 nested loop join만 가능 (8부터는 아닌걸로 알고있음)
• inner join, outer join 등이 있고, 오라클과 똑같다
• 드라이빙 테이블은 한번에 쭉 읽지만 드리븐 테이블은 여러번 읽는것을 의미.
• 드리븐을 사용할 때 인덱스를 사용할 수 없는 쿼리는 느려지고, 경우에 따라 풀 테이블 스캔이나 인덱스 풀 스캔을 피할 수 없을 때에는 드라이빙 테이블에서 읽은 레코드를 메모리에 캐시한 후 드리븐 테이블과 이 메모리 캐시를 조인하는 형태로 처리
• 이때 사용되는 메모리 캐시를 조인 버퍼라고 함
• 드라이빙 테이블이 너무 커서 조인 버퍼에 담지 못하면 이 과정을 여러번 반복함

출처

• 이성욱-Real MySQL (위키북스)