mysql 정렬

mysql 주요 처리 방식
 

풀 테이블 스캔

옵티마이저가 풀 테이블 스캔을 선택하는 경우

1. 테이블 레코드가 너무 작아서 인덱스를 통해 읽는 것보다 풀스캔을 하는게 더 낫다고 생각하는 경우

2. where 절이나 on절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우

3. 인덱스 레인지 스캔을 사용할 수 있는 쿼리여도 일치 레코드 건수가 너무 많은 경우

4. max_seeks_for_key 변수를 특정 값(n)으로 설정하면 옵티마이저는 인덱스 기수성(cardinality)나 선택도(selectivity) 무시 하고 최대 n건만 읽으면 된다고 판단 이 값을 작게 설정할수록 mysql서버가 인덱스를 더 사용하다록 유도

 - 대부분의 dbms는 풀 스캔을 실행할 때 한번에 여러개의 블록이나 페이지를 읽어오는 기능이 있으며 그 수를 조정할 수 있다

 - 하지만 mysql에는 풀 스캔할 때 한꺼번에 몇 개씩 페이지를 읽어올지 설정하는 변수는 없다.

 - innodb 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드 작업이 자동으로 시작됨.

 - 리드 어헤드란 ? 어떤 영영의 데이터가 앞으로 필요해지리라는 것을 예측하여 요청이 오기 전에 미리 디스크에 innodb 버퍼 풀에 가져다 두는것을 의미

 - 즉 풀 스캔이 실행되면 처음 몇개 데이터 페이지는 포그라운드 스레드가 페이지를 읽기를 실행하지만 특정 시점 부터는 읽기 작업을 백그라운드 스레드로 넘긴다.

innodb_read_ahead_threshold : 5.1 innodb 플러그인 버전부터는 리드 어헤드를 시작할지 시스템 변수를 이용해 변경 할 수 있다.

orderby 처리(using filesort)

 -  정렬을 처리하기 위해서는 인덱스를 이용하는 방법과 쿼리가 실행될 때 Filesor라는 별도의 처리를 이용하는 방법으로 나뉨

 

	장점	단점
인덱스 이용	매우 빠름	insert update delete 작업 시 부가적인 인덱스 추가.삭제가 필요하므로 느리다. 디스크 공간이 더많이 필요 인덱스가 늘어날수록 innodb의 버퍼풀 이나 myisam 키 캐시용 메모리가 많이 필요
Filesort 이용	인덱스의 단점이 장점으로 바뀜 데이터가 많지 않으면 filesor가 되므로 충분히 빠름	응답 속도가 느림

소트버퍼 (sort buffer)

 - mysql은 정렬을 수행하기 위해 별도의 메모리 공간을 할당 받아서 사용함 이 메모리 공간을 소트버퍼 라 함.

 - 버퍼의 크기는 정렬해야 할 레코드의 크기의 따라 가변적으로 증가하지만 최대 사용 가능한 소트 버퍼 공간은 sort_buffer_size 시스템 변수로 설정

 - 소트 버퍼 메모리 공간은 쿼리 실행이 완료 되면 시스템으로 반납

 - 정렬해야 할 레코드 건수가 소트 버퍼로 할당된 공간보다 크다면 정혈해야 할 레코드를 여러 조각으로 나눠서 처리하는데 이 과정에서 임시 저장 을 위해 디스크 사용

1 메모리의 소트 버퍼에서 정렬을 수행 하고 그 결과를 임시 디스크에 기록

2. 그 다음 레코드를 가져와서 다시 정렬해서 반복적으로 디스크에 임시 저장함.

 - 각 버퍼크기 만큼 정렬된 레코드를 다시 병합하면서 정렬을 수행해야 한다.

이 병합 작업을 멀티 머지라고 표현 하며 수댕된 멀티 머지 횟수는 Sort_merge_passes라는 상태 변수에 누적

  - 소트 버퍼를 크게 설정해서 빠른 성능을 얻을 수 없지만 디스크의 읽기와 쓰기 사용량은 줄일 수 있다,.

  - mysql 서버의 데이터가 많거나 디스크 i/o의 성능이 낮은 장비라면 소트 버퍼의 크기를 더 크게 설정하는 것도 도움이 될 수 있따.

  - 하지만 소트 버퍼를 너무 크게 설정하면 서버의 메모리가 부족해져서 mysql 서버가 메모리 부족을 겪을 수 도 있기 때문에 

적설히 설정하는것이 좋다.

정렬 알고리즘

 - 레코드를 정렬할 때 레코드 전체를 소트 버퍼에 담을지 정렬 기준 컬럼만 소트 버퍼에 담을지 가지로 정렬 알고리즘을 나눌수 있다.

싱글 패스(Single pass)알고리즘

 - 소트 버퍼의 정렬 기준 칼럼을 포함해 select 되는 컬럼 전부를 담아서 정렬ㅅ을 수행하는 방법

투 패스(Two pass) 알고리즘

 - 정렬 대상 컬럼과 프라이머리 키값만 소트 버퍼에 담아서 정렬을 수해앟고 정렬된 순서대로 다시 프라이머리 키로 테이블을 읽어서 select 컬럼만 가져오는 알고리즘

 - 예전 버전의 mysql에서 사용하는 방법 5051 55 버전에도 특정 조건이 되면 이 방법을 사용함

 - 싱글패스 알고리즘은 대락 128k의 정렬 버퍼를 사용한다면 투 패스 알고리즘에서는 대략 7000건의 레코드를 정렬할 수 있지만 싱글 패스 알고리즘은 그것의  반 정도만 정렬 가능

5.x 버전은 싱글 패스 알고리즘을 사용하지만 다음과 같은 경우 투 패스를 사용함

 - 레코드의 크키가 max_length_for_sort_data 파라미터로 설정된 값보다 클 때

 - blob이나 test 타입의 컬럼이 select 대상에 포함될 때

 - 모든 컬럼을 (*)을 가져오도록 개발할때가 많다. 하지만 이는 정렬 버퍼를 몇 배에서 몇 십배까지 비효율적으로 만들 가능성이 크다'

정렬의 처리 방식

쿼리의 order dy가 사용되면 반드시 다음 3가지 처리 방식중 하나로 정렬

정렬 처리 방법	실행 계획의 extra 코멘트
인덱스 사용 ㅈ어렬	별도의 내용 표기 없음
드라이빙 테이블만 정렬(조인이 없는 경우 포함)	"Using filesort"가 표시됨
조인 결과를 임시 테이블로 저장한 후  임시 테이블에서 정렬	"Using temporary;Using filesort"가 같이 표시됨

  - 인덱스를 이요할 수 있다면 별도의 filesot 과정 없이 인덱스를 순서대로 읽어서 결과 봔환 ㅎ나다.

  - 인덱스를 이용할 수 없다면 where 조건에 일치하는 레코드를 검색해 정렬 버퍼에 저장하면서 정렬을 할것이다.

 옵티마이저는 정렬 레코드를 최소화하기 위해 다음 두 가지 방법중 하나를 선택

1. 드라이빙 테이블만 정렬한 다음 조인을 수행 . 

2. 조인이 끝나고 일치하는 레코드를 모두 가져온 후 정렬을 수행

인덱스를 이용한 정렬

 - 인덱스를 이용한 정렬을 위해서는 반드시 order by에 명시된 컬럼이 제일 먼저 읽는 테이블 (조인의 경우 드아리빙 테이블) 속하고 order by 순서대로 생성된 인덱스가 있어ㅑ 함,

 - where 절에 첫 번째 읽은 테이블의 컬럼에 대한 조건이 있따면 그 조건과 order by 는 같은 인덱스를 사용할 수 있어야 함.