보통의 DBMS 의 목적은 데이터를 안전하게 저장 및 관리하고 사용자가 원하는 데이터를 빠르게 조회할 수 있게 해주는이다.
이러한 목적을 달성하려면 옵티마이저가 사용자의 쿼리를 최적으로 처리될 수 있게 쿼리의 실행 계획을 수립할 수 있어야 한다.
하지만 옵티마이저는 사용자, 관리자의 개입없이 항상 좋은 실행계획을 만들어 내는것은 아니다.
이러한 문제를 보완할 수 있도록 EXPLAIN 명령으로 옵티마이저가 수립한 실행계획을 확인 할 수 있게 해준다.
MySQL 서버의 실행계획에 가장 큰 영향을 미치는 통계정보, 실행 계획 읽는 순서, 실행계획 출력되는 키워드 등에 자세히 알아보려고한다.
통계정보
MySQL 8.0 이전에는 개괄적인 정보를 가지고 실행계획을 수립했기 때문에 정확성이 떨어졌다. 그래서 8.0 이후 부터는 인덱스 되지 않은 칼럼들에 대해서도 데이터 분포도를 수집해서 저장하는 히스토그램 정보가 도입됐다
히스토그램이 도입돼서 기존 테이블, 인덱스 통계정보가 필요하지 않은건 아니다.
비용 기반 최적화에서 가장 중요한 것은 통계정보다 0.1 초 걸릴거 1시간이 걸릴수 있다.
비용 최적화
비용(예상되는 소요시간, 자원 사용량)이 가장 적은 실행계획을 선택하는 방식, 규칙기반 옵티마이저의 단점을 극복하기 위해서 출현했다.
MySQL 5.6 버전부터는 InnoDB 스토리지 엔진을 사용하는 테이블에 대한 통계정보를 영구적으로 관리할 수 있게 했다.
그 전 까지는 각 테이블의 통계정보가 메모리에만 관리되어, SHOW INDEX 명령으로만 테이블의 인덱스 칼럼의 분포도를 볼 수 있었다.
만약 이처럼 메모리에서 관리가 될 경우 서버가 재시작 되면 지금까지 수집된 통계 정보가 모두 사라진다.
5.6 버전 부터는 DB의 innodb_index_stats 테이블과 innodb_table_stats 테이블로 관리가 가능해 서버가 재시작 돼도 기존의 통계 정보를 유지할 수 있다.
Table 을 생성할때 시스템 설정 변수 (innodb_stats_persistent) 를 통해 테이블의 저장할지 안 할지 정한다 default = 1(ON)
1 로 생성한 것만 테이블의 통계 정보가 조회된다. ALTER로 innodb_stats_persistent = 0 (단기적) 으로도 변경이 가능하다.
5.5 버전 까지는 테이블의 통계 정보가 메모리에만 저장된다고 했다. MySQL 서버가 재시작 되면 통계정보는 초기화 되고, 재 시작시 모든 테이블의 통계 정보는 다시 수집된다. 그리고 우리가 알지 못하는 순간에 어떠한 이벤트가 발생하면 자동으로 통계정보가 갱신된다.
하지만 통계정보가 자주 갱신되면 응용 프로그램의 쿼리를 풀 테이블 스캔으로 실행되는 상황이 발생할 수 있다. 이럴 때는 자동 갱신을 막을 수 있다.
innodb_stats_auto_recalc = OFF
1 = 5.5 이전 방식
0 = analyze table 실행시
default = 설정하지 않은것과 동일
Innodb_stats_persistent_sample_pages, Innodb_stats_transient_sample_pages 라고 테이블 블록을 샘플링 할지 말지의 대한 옵션이 주어진다.
Innodb_stats_persistent_sample_pages 시스템 변수를 높게 설정하면, 더 정확한 통계 정보를 수집할 수 있다. 하지만 너무 높으면 통계 정보 수집 시간이 길어진다.
히스토그램
과거에는 단순히 인덱스 된 칼럼의 유니크한 개수 정도만 가지고 있었다. 이것으로는 옵티마이저가 최적의 실행 계획을 수립하기에는 많이 부족했고, 옵티마이저는 이런 부족한 점을 메우기 위해 실행계획을 수립할 때 일부 페이지를 랜덤으로 가져와 참조하는 방식을 사용했다.
8.0 으로 업그레이드 하면서 칼럼의 데이터 분포도를 참조할 수 있는 히스토그램 정보를 활용할 수 있게 됐다.
히스토그램 은 칼럼 단위로 관리되면서 자동으로 수집되지 않고
ANALYZE TABLE ~~~ UPDATE HISTOGRAM 명령을 통해 수동으로 수집 및 관리된다.
이렇게 수집된 정보는 시스템 딕셔너리에 함께 저장되고 서버가 시작될 때 딕셔너리 히스토그램 정보를
information_schema 데이터 베이스의 column_statistics 테이블로 로드한다.
히스토그램 정보를 조회하려면 column_statistics 테이블을 select 해서 참조 할 수 있다.
다음과 같이 생성할 수 있다.
이런식으로 잘 생성됐는지 select 해보면
COLUMN_STATISTICS 에서 에러가 나게되는데
그 이유는 버전이 8.0으로 올라가면서 난다.
여기를 해결방안은 참고하면 된다.
삭제 방법:
analyze table member drop histogram on updated_time;
MySQL 8.0 버전에서는 2종류의 히스토그램 타입이 지원된다.
1. 싱글톤
- 칼럼 값 개별로 레코드 건수를 관리하는 히스토그램으로, Value-Based 히스토 그램, 도수 분포라고도 한다.
- 칼럼이 가지는 값별로 버킷이 할당
- 각 버킷이 칼럼의 값과 발생빈도의 비율의 값을 가진다.
2. 높이 균형 히스토그램
- 칼럼 값의 범위를 균등한 개수로 구분해서 관리하는 히스토그램으로 Heigh-Balanced 히스토그램이라고 불린다.
- 개수가 균등한 칼럼 값의 범위 별로 하나의 버킷이 할당
- 버킷의 범위 시작 값과, 마지막 값, 발생 빈도율과 각 버킷에 포함된 유니크한 값의 개수
그럼 이런 히스토그램을 어디에 쓰나???
특정 칼럼이 가지는 모든 값에 대한 분포도 정보를 가지지는 않지만 각 범위 별로 레코드의 건수와 유니크한 값의 개수 정보를 가지기 때문에 훨씬 정확한 예측을 할 수 있다.
히스토그램 정보가 없으면 옵티마이저는 데이터가 균등하게 분포돼 있을 것으로 예측한다. 하지만 히스토그램이 있으면 특정 범위의 데이터가 많고 적음을 식별할 수 있다.
히스토그램의 유무에 따라 쿼리가 10배까지도 성능차이가 날 수 있다.
각 컬럼에 대해 히스토그램 정보가 있으면 어느 테이블을 먼저 읽어야 조인의 횟수가 줄일 수 있는지 옵티마이저가 정확하게 판단할 수 있다.
쿼리의 검색 조건으로 많이 사용되는 칼럼은 보통 인덱스를 사용한다. 그런데 인덱스된 칼럼에 대해 히스토그램을 수집하는것이 좋을지 의
문이 든다.
MySQL 옵티마이저는 인덱스된 칼럼을 검색 조건으로 사용하는 경우 그 칼럼의 히스토그램은 사용하지 않고 실제 인덱스 다이브를 통해 직접 수집한 정보를 활용한다. 결론적으로 히스토그램은 주로 인덱스 되지 않은 칼럼에 대한 데이터 분포도를 참조하는 용도로 사용한다.
인덱스 다이브 :MySQL 서버에서 옵티마이저가 가장 나은 실행 계획을 선택하여 실제 인덱스의 B-Tree를 샘플링해서 살펴보는 작은 작업 메뉴얼
인덱스 다이브 작업은 어느 작업의 비용이 필요하고 IN 절에 값이 많은경우 실행 계획 만으로 상당한 비용이 들 수 있다.
코스트 모델 : 전체 쿼리의 비용을 계산하는데 필요한 단위 작업들의 비용.
각 단위작업의 비용을 DBMS 관리자가 조정할 수 있다.
8.0 부터 인덱싱 되지 않은 칼럼의 히스토그램이나 메모리에 상주 하고 있는 페이지의 비율 도 관리되고 실행 계획 수립에 사용된다.
댓글