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

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) **