MySQL 8.0'ın evrimi, veritabanı mühendislerinin karmaşık analitik yüklerle nasıl yaklaşım geliştirdiği konusunda bir paradigma değişimi işaret etti. Yıllarca geliştiriciler, koşullu toplam, sıralama ve hareketli ortalamaları hesaplamak için karmaşık özyinelemeli birleşimlere (self-joins), geçici tablolara ve kullanıcı tanımlı değişkenlere bağımlı kalmak zorunda kaldı. Bu geçici çözümler genellikle verimsiz, okunması zor ve performans darboğazlarına yatkın oluyordu. Ortak Tablo İfadeleri (CTE) ve yerleşik Pencere Fonksiyonlarının tanıtılmasıyla MySQL, nihayet kurumsal düzeyde analitik veritabanları ligine girdi. Bu yazı, sorgularınızı optimize etmek, kodun sürdürülebilirliğini artırmak ve verilerinizden daha derin içgörüler elde etmek için bu özelliklerin nasıl kullanılacağını incelemektedir.
Ortak Tablo İfadelerinin (CTE) Gücü
Pencere fonksiyonlarına dalmadan önce, karmaşık sorgu mantığını okunabilir hale getiren sözdizimini ele almalıyız: CTE. WITH ifadesi kullanılarak tanımlanan bir CTE, büyük ve karmaşık bir sorguyu, daha küçük, yönetilebilir ve adlandırılmış sonuç kümelerine ayırmanıza olanak tanır. Türetilmiş tablolardan (alt sorgular) farklı olarak, CTE'ler özyinelemeli olabilir, ana sorgu içinde birden fazla kez referans gösterilebilir ve okunabilirliği önemli ölçüde artırır.
Kullanıcının toplam harcama tutarını kategori bazında hesaplamayı, ancak sadece son ayda belirli bir eşiğin üzerinde harcama yapan kullanıcıları dikkate almayı gerektiren bir senaryo düşünün. Bir CTE olmadan, birden fazla alt sorguyu iç içe geçirmek, "spagetti kod" kâbusuna yol açabilir. Bir CTE, veri filtreleme mantığını, toplama mantuğundan ayırarak sorguyu kendi kendini açıklayan hale getirir.
WITH HighValueUsers AS (
SELECT user_id, SUM(amount) as total_spent
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
GROUP BY user_id
HAVING SUM(amount) > 1000
)
SELECT hvu.user_id, c.category_name, SUM(o.amount) as category_total
FROM HighValueUsers hvu
JOIN orders o ON hvu.user_id = o.user_id
JOIN categories c ON o.category_id = c.id
GROUP BY hvu.user_id, c.category_name;
Veritabanı İçi Analitik İçin Pencere Fonksiyonlarının Kullanılması
Pencere Fonksiyonları, MySQL 8.0'daki gerçek oyun değiştiricilerdir. Satırları çökerten toplama fonksiyonlarının aksine, pencere fonksiyonları mevcut satıra bağlı bir tablo satırı kümesi üzerinde hesaplamalar yapar. Bu, verilerinizin ayrıntılı yapısını korurken analitik bir bağlam eklemenizi sağlar.
Sözdizimi FUNCTION() OVER (PARTITION BY ... ORDER BY ...) desenini izler. OVER ifadesi pencere çerçevesini tanımlar. Verileri bölebilir (benzer şekilde GROUP BY gibi) ve ardından koşullu toplam, hareketli ortalamalar veya yüzdelik dilimler gibi metrikleri hesaplamak için sıralama ve çerçeveleme (ROWS veya RANGE kullanarak) uygulayabilirsiniz.
Koşullu Toplam ve Sıralama Hesaplama
En yaygın kullanım durumlarından biri, belirli gruplar içinde bir liderlik tablosu veya koşullu toplam oluşturmaktır. RANK(), DENSE_RANK() ve ROW_NUMBER() fonksiyonları, eşitlik durumlarını zarif bir şekilde çözen sıralama sorununu çözer.
SELECT
transaction_id,
customer_id,
amount,
transaction_date,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY transaction_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) as running_total,
RANK() OVER (
PARTITION BY customer_id
ORDER BY amount DESC
) as purchase_rank
FROM transactions
WHERE transaction_date >= '2023-01-01';
Bu örnekte, koşullu toplam ve sıralamanın tüm veri seti üzerinden değil, her kullanıcı için bireysel olarak hesaplanmasını sağlamak için verileri customer_id bazında böleriz. Bu, özyinelemeli birleşimlere veya geçici tablolara olan ihtiyacı tamamen ortadan kaldırır.
Pencere Çerçeveleri ile Performans Optimizasyonu
İleri düzey optimizasyon, pencere çerçevesini nasıl tanımladığınızda yatar. Varsayılan olarak MySQL, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW kullanır; bu, veri türlerine ve sıralama sütunlarına bağlı olarak belirsiz olabilir. Analitik sorgular için ROWS açıkça tanımlamak genellikle daha öngörülebilirdir.
Örneğin, son 7 günlük hareketli bir ortalama hesaplamak klasik bir zaman serisi problemidir. MySQL 8.0'da, tabloyu kendisiyle yedi kez birleştirmeden bunu verimli bir şekilde hesaplamak için kayan bir pencere çerçevesi tanımlayabilirsiniz.
SELECT
date,
revenue,
AVG(revenue) OVER (
ORDER BY date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) as moving_avg_7_day
FROM daily_metrics;
Bu yaklaşım, date sütunundaki sıralı dizini kullanarak veritabanı motorunun pencereyi sonuç kümesi üzerinde verimli bir şekilde kaydırmasını sağlar. Büyük veri setleri için prosedürel döngülerden veya alt sorgulardan çok daha hızlıdır.
CTE'leri ve Pencere Fonksiyonlarını Birleştirme
En güçlü sorgular genellikle her iki özelliği de birleştirir. Verilerinizi temizlemek, filtrelemek veya ön işleme tabi tutmak için bir CTE kullanabilir ve ardından nihai analitik metrikleri elde etmek için ana sorguda pencere fonksiyonlarını uygulayabilirsiniz. Bu sorumluluk ayrımı, karmaşık veri boru hatlarını sürdürülebilir kılmak için kritiktir.
WITH DailySales AS (
SELECT
sale_date,
region,
SUM(sales_amount) as daily_total
FROM sales_data
GROUP BY sale_date, region
),
WeeklyTrends AS (
SELECT
sale_date,
region,
daily_total,
AVG(daily_total) OVER (
PARTITION BY region
ORDER BY sale_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) as weekly_avg,
LAG(daily_total, 1) OVER (
PARTITION BY region
ORDER BY sale_date
) as prev_day_sales
FROM DailySales
)
SELECT * FROM WeeklyTrends
WHERE daily_total > weekly_avg;
Sonuç
MySQL 8.0, ağır analitik yükler için birincil veritabanı olmasını engelleyen engelleri kaldırdı. Ortak Tablo İfadeleri ve Pencere Fonksiyonlarını ustaca kullanarak, veritabanı mühendisleri daha temiz, daha verimli ve daha sürdürülebilir SQL yazabilirler. Bu araçlar sadece sorgu mantığının karmaşıklığını azaltmakla kalmaz, aynı zamanda veritabanı motorunun yürütme planlarını daha etkili bir şekilde optimize etmesine de olanak tanır. Veri projelerinizde ilerledikçe, analitik yeteneklerinizi dönüştürmek ve daha iyi iş içgörülerini desteklemek için bu özellikleri benimseyin.