b

6장 실행계획 (6.2 MySQL의 주요 처리 방식 - order by 등등) 본문

RealMysql

6장 실행계획 (6.2 MySQL의 주요 처리 방식 - order by 등등)

dev.bistro 2012. 11. 8. 20:46


6.3 MySQL의 주요 처리 방식

  스토리지 엔진에서 읽은 레코드를 MySQL엔진이 아무 작업도 하지 않고 사용자에게 반환하면 최고의 베스트 하지만 대부분의 쿼리는 그렇지 않다. MySQL엔진이 처리하는데 오래 걸리는 작업의 원리를 알아두면 도움이 될 것


6.3.1 풀 테이블 스캔

 - 레코드 건수가 매우 작아, 인덱스보다 유리할 경우(보통 1개 page)

 - WHERE, ON절에 적절한 인덱스를 이용할 수 없을 경우

 - 인덱스 레이지 스캔이지만, 실행계획에서 일치 레코드 개수가 너무 많을 경우 등등


 MySQL의 MyISAM은 Read ahead 작업의 갯수를 선언할 수 없다. 그래서, 한번에 한개씩 읽어오지만, InnoDB는 다르다. 

 처음에는 Foreground thread가 페이지를 1개씩 읽지만 이후 4개 , 8개, 최대64개 페이지를 동시에 읽어온다. (시스템 변수 : innodb_read_ahead_threshold)


6.3.2 ORDER BY 처리

 - 인덱스 기반 : 인덱스를 이용해 빠르지만,  INSERT, UPDATE, DELETE 시 부가적으로 인덱스 관련 작업이 필요하고, 디스크 공간도 필요.

 -  FILE SORT : 인덱스의 단점과, 레코드수가 적으면 메모리에서 SORT가 가능해 충분히 빠르지만, 레코드 수가 많으면 느리다.(Using filesort)

 모든 정렬을 인덱스를 이용하기는 불가능

 - 정렬기준이 많아, 모든 인덱스 생성 불가

 - GROUP BY, DISTINCT를 쓰거나 UNION결과 처럼 임시 테이블을 SORT해야하는 경우


 Sort Buffer : MySQL이 정렬을 수행하기 위한 별도 메모리 공간. 정렬에만 이용, 가변적으로 증가하지만 최대 사이즈는 soft_buffer_size 시스템 변수, 그리고소트 완료후 즉시 시스템으로 반납되는 메모리공간이다. (이 사이즈를 넘길 경우 이제 문제가 된다)

 읽고->SortBuffer에서정렬->Disk에 임시저장 -> 다음 읽고 -> 정렬 -> 임시저장 (Multi- Merge방식) 512K가 적당(P334)

 SOrtBuffer를 크게한다고 해서 빨리 지지는 않지만, DISK I/O의 횟수를 줄일수 있다.


 Single Pass 알고리즘 : SELECT되는 모든 컬럼을 버퍼에 담아서 정렬 -> 결과를 바로 보내줌 (최근 5.0이후 일반적 방식)

 Two Pass 알고리즘 : 정렬대상, KEY값으로 정렬 -> 정렬순으로 Key를 읽어서 결과를 보내줌 

 - Single Pass는 한번에 되지만, 대신 더 많은 Sort Buffer 공간이 필요

 - 5.0 이상이더라도, BLOB나 TEXT가 사용되면 Two Pass알고리즘을 사용한다.


 *** SELECT * 가 안좋은 이유 : Sort Buffer를 쓸데없이 다 사용하기 때문이다. 임시테이블결과도 마찬가지 (필요한것만 select하는게 좋다)

 


 * 인덱스를 이용한 정렬

   - ORDER BY 명시된 칼럼이 제일 먼저 읽는 테이블에 속하고, ORDER BY순대로 인덱스가 생성되어 있어야한다!

   - 실제로 인덱스값이 정렬되어 있어 '읽기만'하면 된다.

   - 인덱스에 인해 자동 정렬된다고 ORDER BY를 제거하지 마라. 명시적 쿼리이며 성능손해따윈 없다(P339)

 * 드라이빙 테이블만 정렬

 * 임시 테이블을 이용한 정렬


 * 스트리밍 방식 : 데이터가 얼마인지 상관없이 조건에 일치하는 레코드가 검색될때마다 클라이언트측으로 전송 (첫 응답은 매우 빠르다) 하지만 Group By, Order는 이 작업이 불가능하다. 이를 '버퍼링 방식'이라 한다. 그래서 Order By 에 LIMIT를 걸더라도 성능에 크게 도움이 되지는 않는다. 네트워크 전송양이 줄지, MySQL 서버 작업량 자체는 그다지 줄지 않는다.. JDBC는 기본 버퍼링 방식임을 참고하자. (P343)

 * SHOW SESSION STATUS LIKE 'sort%' 으로 특정 실행 횟수를 확인해 볼 수 있다.



6.3.3 GROUP BY 처리

 * 스트리밍 처리가 불가능한 요소, HAVING는 일반적으로 임시 영역에 있는 GROUP BY결과를 필터링하므로 HAVING절을 튜닝하거나 인덱스를 이용하려는 수고를 하지 않아도 된다.,

 * GROUP BY칼럼이 인덱스 있다면, Using index fro grup-by나 Using temporary, Using filesort와 같은 코멘트가 표시 되지 않을것이다.


6.3.4 DISTINCT 처리

 * SELECT DISTINCT 는 GROUP BY와 거의 유사하게 처리된다.

 * 주의!) DISTINCT는 컬럼을 유니크하게 조회하는게 아닌, 튜플을 유니크하게 가져오는것을 까먹지 말것 (P351)

   SELECT DISTINCT (first_name), last_name FROM === SELECT DISTINCT first_name, last_name FROM employees


 집합함수와 함께 사용된 DISTINCT

  COUNT, MAX, MIN과 사용되면 조금 다른 형태로 해석된다. - 그 함수의 인자로 전달된 칼럼만 유니크한걸로 가져온다.

 SELECT COUNT(DISTINCT first_name), COUNT(DISTINCT last_name) != SELECT COUNT(DISTINCT first_name, last-name)


6.3.5 임시 테이블

 * MySQL의 임시테이블은 메모리에 생성되었다가 커지만 디스크로 옮겨진다. (특이 케이스도 있다0

 * 임시 테이블은 다른 세션이나 쿼리에서는 볼수, 사용할 수 없다. 그리고 자동삭제된다.


임시 테이블이 필요한 쿼리는 실행 계획에서 Using Temporary 라는 키워드로 확인하면 된다. (표시가 안되도 임시테이블이 사용될 수 있는데 인덱스를 가지는 내부 임시테이블이 생성될수도있다)

임시 테이블이 디스크에 만들어지는 경우

- 임시로 저장해야 하는 내용중 BLOB, TEXT 같은 대용량 칼럼이 있을경우

- 길이가 512바이트 이상인 칼럼이 있을 경우

- GROUP BY, DISTINCT 512바이트 이상 칼럼이 있을 경우

- 임시 테이블 전체 크기가 tmp_table_size 또는 max_heap_table_size보다 큰 경우

임시테이블에 관한것을 파악하려면 SHOW SESSION STATUS LIKE 'Create_tmp%'; 로 확인할 수 있다.

** VARCHAR(512) * 3(utf8) = 1500정도이다. 가능한한 필요한 만큼의 SIZE를 지정하는게 좋다(P358) **

Comments