MySQL Gevşek Tarama Optimizasyonu: PostgreSQL ve MSSQL'e Karşı Karşılaştırmalı Bir Performans Değerlendirmesi

COUNT DISTINCT veya SELECT DISTINCT yan tümcesini karşılamanın en genel yolu, tüm tabloyu veya dizini taramak ve ondan yalnızca farklı değerleri çıkarmaktır. Bu operasyonda 2 temel strateji olabilir. Elbette bazı veritabanı motorları yaklaşık hesaplama için HyperLogLog algoritmasını da desteklemektedir ancak biz bu makalede daha geleneksel bir yaklaşıma odaklanacağız. Ayrıca bu makalenin ilerleyen kısımlarında COUNT DISTINCT işlemini temsil etmek için "gruplama ölçütü" veya "gruplandırma" terimini kullanacağım çünkü aşağıdaki sorgular anlamsal olarak eşittir.

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

Karma Toplama

Bu teknik genellikle bir sorgunun indekslenmemiş bir sütundaki değerleri gruplaması gerektiğinde kullanılır. Bu işlem sırasında anahtarların sütun değerlerinin benzersiz kombinasyonlarını temsil ettiği bir karma tablosu oluşturulur. Veritabanı motoru satırları tararken, her satırın sütun değeri için karma değerini hesaplar ve karma paketlerinde aynı karma ile gerçek değerleri saklar. Bu yöntem büyük veri kümeleri için yoğun bellek gerektirse de, sunucunun karma tablosunu depolamak için yeterli belleğe sahip olması genellikle en hızlı yaklaşımdır.

Akış Toplama

Akış Toplama, gruplanacak sütun zaten sıralanmışsa veya neredeyse sıralanmışsa kullanılır. Veri akışı geldikçe, veritabanı motoru mevcut satır değerini öncekiyle karşılaştırır. Geçerli satır aynı gruba aitse veritabanı motoru toplamaya devam eder. Değer farklılaştığında yeni bir grup başlatılır ve önceki grup değeri yürütme planında daha ileri aktarılır. Akış Toplama, Karma Toplama ile karşılaştırıldığında çok daha az bellek kullanır (yalnızca bir değerin saklanması gerekir), ancak verilerin sıralanmasını gerektirir; bu, veriler önceden sıralanmamışsa ekstra bir sıralama işlemi gerektirebilir.

Ancak bu stratejilerin her ikisi de hala tam bir sütun taraması gerektirir ve PostgreSQL ve MS SQL Server'ın sahip olmadığı ancak MySQL'in sahip olduğu düşük kardinaliteye sahip sütunlarla uğraşırken COUNT DISTINCT işlemi için daha iyi bir strateji vardır.

Gevşek Tarama Nedir?

Gevşek Tarama, belirli GROUP BY işlemleri bağlamında, özellikle tablodaki toplam satır sayısına kıyasla nispeten az sayıda satırın işlendiği senaryolarda uygulanabilen gelişmiş bir MySQL optimizasyon tekniğidir. Bu teknik, veritabanından okunması gereken veri miktarını azaltarak sorguların performansını önemli ölçüde artırır.

Gevşek Taramanın Temel Prensibi

Özünde, Gevşek Tarama tekniği basit bir prensiple çalışır: Nitelikli satırlar için tüm dizini taramak yerine ("sıkı tarama" olarak da bilinir), her grup için eşleşen ilk satırı "gevşek bir şekilde" tarar. Bir eşleşme bulduktan sonra hemen bir sonraki gruba geçer. Bu yöntem, değerlendirilmesi gereken satır sayısının azaltılmasını sağlar ve böylece bir sorgunun yürütülmesi için harcanan toplam süreyi azaltır.

MySQL'in yanı sıra benzer bir teknik diğer veritabanı motorlarında da uygulanmaktadır. Buna "Taramayı Atla" denir. Kahin , SQLite , Ve HamamböceğiDB , “Taramayı Atla” DB2 , ve “Karma Tarama” YugabyteDB .

Ortamın Ayarlanması

Sanırım bu kadar teori yeterli, hadi pratik kısma geçelim ve MySQL'de Gevşek Tarama ile PostgreSQL ve Microsoft SQL Server'ın karşılaştırmalı bir analizini yapalım. Dizüstü bilgisayarımda MySQL 8.0.33, PostgreSQL 15.3 ve MS SQL 2022-CU4 ile en yeni Docker konteynerlerini kullanacağım. 1 milyon satır ve farklı kardinaliteye sahip tamsayı veri türünde üç sütun içeren bir tablo oluşturacağım. İlk sütunda 100 bin benzersiz değer, ikincisinde 1 bin ve üçüncüsünde yalnızca 10 benzersiz değer bulunur. Üç ayrı kümelenmemiş dizin oluşturacağım ve her sütunda COUNT DISTINCT sorguları çalıştıracağım. Her sorgu sadece veritabanlarını ısıtmak için 5 kez, ardından geçen süre hesaplamasıyla 20 kez daha yürütülecek ve daha sonra toplam yürütme süresini karşılaştıracağız. Bu yüzden tüm veritabanı motorlarını oldukça eşit bir duruma getirmeye çalıştım.

Tüm veritabanlarında örnek tabloyu başlatmak için kullandığım bir komut dosyası var:

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

Veritabanı motorlarının performansını karşılaştırmak için kullanılan sorgular:

 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

Sonuçlar

Tüm veritabanları, veri kardinalitesinin yüksek olduğu (1 milyon satırdan 100 bin benzersiz değer) A sütununda hemen hemen aynı performansı gösterir. PostgreSQL'in toplam yürütme süresi 4,72 saniye, MS SQL Server - 1,42 saniye ve MySQL - 3,11 saniyeydi. Ancak önem derecesinin yalnızca 1000 benzersiz değer olduğu B sütununda, MySQL'in toplam yürütme süresi 58,6 milisaniyeye düşerken PostgreSQL bunu 4,2 saniyede ve MS SQL Server 1,08 saniyede gerçekleştirir. **MySQL ikinci sorguyu PostgreSQL'den 70 kat, MS SQL Server'dan 18 kat daha hızlı tamamlar.**Yalnızca 10 benzersiz değerin bulunduğu C sütununda durum daha da iyidir: MySQL - 12,5ms, PostgreSQL - 3,33s ve MS SQL Sunucusu - 1.02s. **Son örnekte MySQL son derece hızlıdır ve PostgreSQL'den 250 kat, MS SQL Server'dan ise 80 kat daha iyi performans göstermektedir.
**

Aşağıda logaritmik ölçeğin kullanıldığı bir görselleştirme bulunmaktadır. 20 yürütmeden sonraki toplam yürütme süresini gösterir.

MS SQL ve PostgreSQL'de Performansı Artırmak İçin Neler Yapılabilir?

PostgreSQL ve MS SQL Server şu anda böyle bir optimizasyona sahip olmasa da, bu motorlardaki SELECT/COUNT DISTINCT sorgularınızın performansını artırmak için yapabileceğiniz bir numara var. Buradaki fikir, varsayılan tam Dizin Taraması'na güvenmek yerine dizinde birkaç arama yapmaktır.\

Örneğin PostgreSQL'de tüm benzersiz değerleri bulmak için özyinelemeli bir sorgu yapabilirsiniz. İlk yinelemede sütundan minimum değer seçilirken diğer her yinelemede öncekinden daha büyük bir sonraki değer seçilir.

 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;

Aynı numarayı MS SQL Server'da da yapabiliriz. Ancak ne yazık ki, MS SQL Server özyinelemeli sorguları TOP veya toplama operatörlerini desteklemiyor, bu nedenle sonucu depolamak ve LOOP kullanarak yineleme yapmak için geçici bir tablo kullanacağım. Elbette bu daha fazla ek yüke neden oluyor, ancak görünen o ki, SQL Server'da bu tür bir optimizasyonu tamamlamanın başka genel bir yolu yok.

 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;

Şimdi bu değiştirilmiş sorguların orijinal ve MySQL ile karşılaştırıldığında nasıl çalıştığını karşılaştıralım. Değiştirilen sorgulara A1, B1 ve C1 olarak başvuracağım. İşte tam sonuçların yer aldığı tablo.

Çözüm

Sonuçlar oldukça açıktır; Gevşek Tarama, indeks kullanılırken SELECT DISTINCT veya COUNT DISTINCT sorguları için değerlendirilen satır sayısını önemli ölçüde azaltmaya yardımcı olan harika bir optimizasyondur. PostgreSQL, düşük kardinaliteli sütunları MySQL ile aynı etkinlikle işlemek için karmaşık özyinelemeli sorgular yazmanıza izin vermesine rağmen, kardinalitenin yüksek olduğu A sütununda önemli bir performans düşüşüne neden olur. MS SQL Server açıkça bu örnekte dışarıdan biri olsa da bazı geçici çözümler yine de orijinal sorgudan daha iyidir.

Umarım PostgreSQL ve MS SQL Server sonraki sürümlerde Taramayı Atla optimizasyonunu uygular.