Veritabanlarında Sayfalandırma ve Sıralama Optimizasyonu

Modern web uygulamaları, kullanıcı deneyimini geliştirmek ve büyük veri setlerini daha verimli yönetmek için sık sık sayfalama kullanır. Ancak, milyonlarca kaydı içeren tablolarla uğraşırken, kötü optimize edilmiş sayfalama sorguları ciddi performans sorunlarına yol açabilir. Bu noktada, sorgu performansını önemli ölçüde artırmak için yalnızca WHERE koşulu sütunları için değil, aynı zamanda SIRALA ve sayfalama işlemlerinde kullanılan sütunlar için indeksler oluşturmak faydalı olabilir. Bu blog yazısında, MySQL'de bir SIRALA ... SINIRLA sorgusunun indeksli ve indeksiz nasıl davrandığını, AÇIKLAMA ANALİZİ kullanarak yürütme planı çıktıları karşılaştırarak inceleyeceğiz. Ayrıca PostgreSQL gibi diğer popüler veri tabanı sistemlerinde kullanılan benzer optimizasyon tekniklerinden de bahsedeceğiz. Hazırsanız, veri tabanı performansının iç yüzüne dalalım!

Modern web sayfaları, sorgu sonuçlarını göstermek için sayfalama kullanır. Bu yöntem, sorgu büyük satırlar döndürürse sorgu performansını da optimize eder. Genellikle SQL sorgularının where clause sütunları için indeks oluşturulmaktadır. Bunun yanında sayfalama ve sıralamaların optimizasyonu için de indeksler kullanabilirsiniz.

Örneğin, şöyle bir sayfalama sorgusu;

select * from product_orders order by order_datelimit 100

select * from product_orders order by order_datelimit 100

select * from product_orders order by order_datelimit 100

product_orders tablosu 5000000 kayıt içerir. İndeks olmadan, MySQL motoru tüm tablo satırlarını okuyup order_date'e göre sıralayıp sonra 100 kaydı sınırlandırmak zorundadır ki bu çok verimsizdir ve MYSQL veritabanında yürütme planında gösterilen maliyet 535090'dır.

İndeks Olmadan Yürütme Planı

explain format=tree
  select *
  from product_orders
  order by order_date
  limit 100
-> Limit: 100 row(s)  (cost=535090 rows=100)
  -> Sort: product_orders.order_date, limit input to 100 row(s) per chunk  (cost=535090 rows=4.82e+6)
    -> Table scan on product_orders  (cost=535090 rows=4.82e+6)

explain format=tree
  select *
  from product_orders
  order by order_date
  limit 100
-> Limit: 100 row(s)  (cost=535090 rows=100)
  -> Sort: product_orders.order_date, limit input to 100 row(s) per chunk  (cost=535090 rows=4.82e+6)
    -> Table scan on product_orders  (cost=535090 rows=4.82e+6)

explain format=tree
  select *
  from product_orders
  order by order_date
  limit 100
-> Limit: 100 row(s)  (cost=535090 rows=100)
  -> Sort: product_orders.order_date, limit input to 100 row(s) per chunk  (cost=535090 rows=4.82e+6)
    -> Table scan on product_orders  (cost=535090 rows=4.82e+6)

Order_date üzerinde indeks oluşturduktan ve yürütme planına baktıktan sonra maliyet 1.1'e düşer, bu da orijinal sorgu planından 500000 kat daha azdır.

İndeks İle Yürütme Planı

explain format=tree
select *
from product_orders
order by order_date
limit 100
-> Limit: 100 row(s)  (cost=1.1 rows=100)
  -> Index scan on product_orders using ix_order_date_product_orders  (cost=1.1 rows=100)

explain format=tree
select *
from product_orders
order by order_date
limit 100
-> Limit: 100 row(s)  (cost=1.1 rows=100)
  -> Index scan on product_orders using ix_order_date_product_orders  (cost=1.1 rows=100)

explain format=tree
select *
from product_orders
order by order_date
limit 100
-> Limit: 100 row(s)  (cost=1.1 rows=100)
  -> Index scan on product_orders using ix_order_date_product_orders  (cost=1.1 rows=100)