MySQL 느슨한 스캔 최적화: PostgreSQL 및 MSSQL에 대한 비교 성능 평가

COUNT DISTINCT 또는 SELECT DISTINCT 절을 충족시키는 가장 일반적인 방법은 전체 테이블이나 인덱스를 스캔하고 여기에서 고유한 값만 추출하는 것입니다. 이 작전에는 2가지 기본 전략이 있을 수 있습니다. 물론 일부 데이터베이스 엔진은 대략적인 계산을 위해 HyperLogLog 알고리즘도 지원하지만 이 기사에서는 보다 전통적인 접근 방식에 중점을 둘 것입니다. 또한 이 기사에서는 다음 쿼리가 의미상 동일하기 때문에 COUNT DISTINCT 연산을 나타내기 위해 "그룹화" 또는 "그룹화"라는 용어를 사용할 것입니다.

 select count(distinct A) from group_by_table; select count(*) from (select a from group_by_table group by a) as tmp;

해시 집계

이 기술은 일반적으로 쿼리가 인덱싱되지 않은 열의 값을 그룹화해야 할 때 사용됩니다. 이 프로세스 동안 키가 열 값의 고유한 조합을 나타내는 해시 테이블이 구성됩니다. 데이터베이스 엔진은 행을 검색하면서 각 행의 열 값에 대한 해시 값을 계산하고 해시 버킷에 동일한 해시가 포함된 실제 값을 저장합니다. 이 방법은 대규모 데이터 세트의 경우 메모리를 많이 사용할 수 있지만 서버에 해시 테이블을 저장할 메모리가 충분할 때 가장 빠른 방법인 경우가 많습니다.

스트림 집계

Stream Aggregation은 그룹화할 컬럼이 이미 정렬되어 있거나 거의 정렬된 경우에 사용됩니다. 데이터 스트림이 들어오면 데이터베이스 엔진은 현재 행 값을 이전 값과 비교합니다. 현재 행이 동일한 그룹에 속하는 경우 데이터베이스 엔진은 집계를 계속합니다. 값이 다르면 새 그룹이 시작되고 이전 그룹 값이 실행 계획에서 추가로 전달됩니다. 스트림 집계는 해시 집계에 비해 훨씬 적은 메모리를 사용하지만(하나의 값만 저장하면 됨) 데이터를 정렬해야 하며, 데이터가 아직 정렬되지 않은 경우 추가 정렬 작업이 필요할 수 있습니다.

그러나 이 두 전략 모두 여전히 전체 열 검색이 필요하며 PostgreSQL 및 MS SQL Server에는 없지만 MySQL에는 있는 낮은 카디널리티를 가진 열을 처리할 때 COUNT DISTINCT 작업에 대한 더 나은 전략이 있습니다.

루즈 스캔이란 무엇입니까?

Loose Scan은 특정 GROUP BY 작업의 컨텍스트, 특히 테이블의 총 행 수에 비해 상대적으로 적은 수의 행이 처리되는 시나리오에서 적용할 수 있는 고급 MySQL 최적화 기술입니다. 이 기술은 데이터베이스에서 읽어야 하는 데이터의 양을 줄여 쿼리 성능을 크게 향상시킵니다.

루즈 스캔의 기본 원리

본질적으로 Loose Scan 기술은 간단한 원칙에 따라 작동합니다. 즉, 전체 인덱스에서 한정된 행을 검색하는 대신(일명 'tight' 스캔) 각 그룹에 대해 첫 번째로 일치하는 행을 '느슨하게' 검색합니다. 일치하는 항목을 찾은 후 즉시 다음 그룹으로 이동합니다. 이 방법을 사용하면 평가해야 하는 행 수가 줄어들어 쿼리 실행에 소요되는 총 시간이 줄어듭니다.

MySQL 외에도 유사한 기술이 다른 데이터베이스 엔진에서도 구현됩니다. 이 기능은 "스킵 스캔"이라고 합니다. 신탁 , SQLite , 그리고 바퀴벌레DB , "점프 스캔" DB2 , 및 "하이브리드 스캔" 유가바이트DB .

환경 설정

이론은 충분하다고 생각합니다. 실제적인 부분으로 넘어가서 MySQL과 PostgreSQL 및 Microsoft SQL Server의 Loose Scan을 비교 분석해 보겠습니다. 내 노트북에서 MySQL 8.0.33, PostgreSQL 15.3 및 MS SQL 2022-CU4가 포함된 최신 Docker 컨테이너를 사용하겠습니다. 카디널리티가 다른 정수 데이터 유형의 100만 행과 3개 열이 있는 하나의 테이블을 생성하겠습니다. 첫 번째 열에는 100,000개의 고유 값이 있고, 두 번째 열에는 1,000개, 세 번째 열에는 10개의 고유 값만 있습니다. 세 개의 별도 비클러스터형 인덱스를 만들고 각 열에 대해 COUNT DISTINCT 쿼리를 실행하겠습니다. 각 쿼리는 데이터베이스를 워밍업하기 위해 5번 실행되고 경과 시간 계산을 통해 20번 더 실행되며 나중에 총 실행 시간을 비교하겠습니다. 그래서 저는 모든 데이터베이스 엔진을 거의 동일한 상황에 놓으려고 노력했습니다.

모든 데이터베이스에서 샘플 테이블을 초기화하는 데 사용한 스크립트가 있습니다.

 -- MySQL create table numbers ( id int not null ); insert into numbers(id) with tmp as ( select a.id + b.id * 10 + c.id * 100 + d.id * 1000 as id from (select 0 as id union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a cross join (select 0 as id union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b cross join (select 0 as id union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c cross join (select 0 as id union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d ) select id from tmp; create table group_by_table ( id int not null, a int not null, b int not null, c int not null, primary key (id) ); insert into group_by_table(id, a, b, c) with tmp as ( select a.id + b.id * 10000 as id from numbers as a cross join numbers as b ) select id, floor(rand() * 100000) as a, floor(rand() * 1000) as b, floor(rand() * 10) as c from tmp where id < 1000000; create index idx_group_by_table_a on group_by_table(a); create index idx_group_by_table_b on group_by_table(b); create index idx_group_by_table_c on group_by_table(c); -- PostgreSQL create table group_by_table ( id int not null, a int not null, b int not null, c int not null, primary key (id) ); insert into group_by_table(id, a, b, c) select id, floor(random() * 100000) as a, floor(random() * 1000) as b, floor(random() * 10) as c from generate_series(1, 1000000, 1) as numbers(id); create index idx_group_by_table_a on group_by_table(a); create index idx_group_by_table_b on group_by_table(b); create index idx_group_by_table_c on group_by_table(c); -- MS SQL Server create table group_by_table ( id int not null, a int not null, b int not null, c int not null, primary key clustered (id) ); with tmp as ( select row_number() over (order by (select 1)) - 1 as id from sys.all_columns as a cross join sys.all_columns as b ) insert into group_by_table(id, a, b, c) select id, floor(rand(checksum(newid())) * 100000) as a, floor(rand(checksum(newid())) * 1000) as b, floor(rand(checksum(newid())) * 10) as c from tmp where id < 1000000; create nonclustered index idx_group_by_table_a on group_by_table(a); create nonclustered index idx_group_by_table_b on group_by_table(b); create nonclustered index idx_group_by_table_c on group_by_table(c);

데이터베이스 엔진의 성능을 비교하는 데 사용되는 쿼리는 다음과 같습니다.

 select count(*) from (select a from group_by_table group by a) as tmp; -- ~ 100 thousand unique values select count(*) from (select b from group_by_table group by b) as tmp; -- 1000 unique values select count(*) from (select c from group_by_table group by c) as tmp; -- 10 unique values

결과

모든 데이터베이스는 데이터 카디널리티가 높은(100만 행 중 10만 고유 값) 열 A에서 거의 동일하게 수행됩니다. PostgreSQL의 총 실행 시간은 4.72초, MS SQL Server는 1.42초, MySQL은 3.11초였습니다. 그러나 카디널리티가 1000개의 고유 값인 열 B에서는 MySQL의 총 실행 시간이 58.6밀리초로 떨어지는 반면 PostgreSQL은 4.2초, MS SQL Server는 1.08초가 소요됩니다. **MySQL은 PostgreSQL보다 70배, MS SQL Server보다 18배 빠르게 두 번째 쿼리를 완료합니다.** 고유 값이 10개만 있는 열 C의 상황은 훨씬 더 좋습니다: MySQL - 12.5ms, PostgreSQL - 3.33s 및 MS SQL 서버 - 1.02s. **마지막 예에서 MySQL은 엄청나게 빠르며 PostgreSQL보다 250배 이상, MS SQL Server보다 80배 이상 성능이 뛰어납니다.
**

아래에는 로그 눈금을 사용한 시각화가 있습니다. 20회 실행 후 총 실행 시간을 표시합니다.

성능을 향상시키기 위해 MS SQL과 PostgreSQL에서 무엇을 할 수 있습니까?

현재 PostgreSQL과 MS SQL Server에는 이러한 최적화가 부족하지만 이러한 엔진에서 SELECT/COUNT DISTINCT 쿼리의 성능을 향상시키기 위해 수행할 수 있는 트릭이 있습니다. 아이디어는 기본 전체 인덱스 스캔에 의존하는 대신 인덱스에서 여러 조회를 수행하는 것입니다.\

예를 들어 PostgreSQL에서는 재귀 쿼리를 수행하여 모든 고유 값을 찾을 수 있습니다. 첫 번째 반복에서는 열에서 최소값을 선택하고, 다른 모든 반복에서는 이전 값보다 큰 다음 값을 선택합니다.

 with recursive t as ( select min(a) as x from group_by_table union all select (select min(a) from group_by_table where a > tx) from t where tx is not null ) select count(*) from ( select x from t where x is not null union all select null where exists (select 1 from group_by_table where a is null) ) as tmp;

MS SQL Server에서도 같은 방법을 사용할 수 있습니다. 하지만 안타깝게도 MS SQL Server 재귀 쿼리는 TOP 또는 집계 연산자를 지원하지 않으므로 임시 테이블을 사용하여 결과를 저장하고 LOOP를 사용하여 반복하겠습니다. 물론 오버헤드가 더 많이 발생하지만 SQL Server에서 이러한 최적화를 완료하는 다른 일반적인 방법은 없는 것 같습니다.

 create table #result (x int); declare @current int; select top (1) @current = a from group_by_table order by a; while @@rowcount > 0 begin insert into #result values (@current); select top (1) @current = a from group_by_table where a > @current order by a; end; select count(*) from #result;

이제 이러한 수정된 쿼리가 원본 쿼리와 MySQL과 비교하여 어떻게 실행되는지 비교해 보겠습니다. 수정된 쿼리를 A1, B1 및 C1로 참조하겠습니다. 다음은 전체 결과가 포함된 표입니다.

결론

결과는 매우 분명합니다. Loose Scan은 인덱스를 사용할 때 SELECT DISTINCT 또는 COUNT DISTINCT 쿼리에 대해 평가되는 행 수를 크게 줄이는 데 도움이 되는 훌륭한 최적화입니다. PostgreSQL을 사용하면 MySQL과 동일한 효율성으로 낮은 카디널리티 열을 처리하기 위해 복잡한 재귀 쿼리를 작성할 수 있지만 카디널리티가 높은 열 A에서는 상당한 성능 저하가 있습니다. MS SQL Server는 분명히 이 예에서 외부인이지만 일부 해결 방법은 여전히 원래 쿼리보다 낫습니다.

PostgreSQL과 MS SQL Server가 다음 버전에서 Skip Scan 최적화를 구현하기를 바랍니다.