In-Memory Column Index (IMCI) özelliğinin karmaşık sorguları işleme yeteneğini geliştirmek amacıyla IMCI optimizer, sorgu dönüşüm kurallarını tablo sütunlarından alınan istatistiklerle birleştirir ve maliyet tabanlı optimizasyon kullanarak verimli çalıştırma planları oluşturur. Bu konuda IMCI sorgu optimizasyon özelliğinin çalışma biçimi, kullanımı ve sınırlamaları açıklanmaktadır.
Nasıl çalışır
SQL bildirimsel bir dildir; yani hangi sonucun döndürüleceğini belirtir ancak bu sonucun nasıl hesaplanacağını tanımlamaz. Herhangi bir SQL deyimi için doğru sonucu üretebilen birden fazla geçerli sorgu planı bulunabilir. Örneğin:
SELECT * FROM t0, t1, t2, t3 WHERE t0.a = t1.a AND t1.a = t2.a AND t2.a = t3.a AND t3.b = t1.b;
Yukarıdaki SQL deyimi için aşağıdaki iki sorgu planı da doğru sonucu döndürebilir.
Plan A ve Plan B, equivalent query plans olarak adlandırılır. Optimizer, bir SQL deyimi için eşdeğer sorgu planlarını dönüşümler uygulayarak keşfeder. Örneğin, t1 INNER JOIN t2 ve t2 INNER JOIN t1 bir çift eşdeğer sorgu planıdır. Optimizer, t1 INNER JOIN t2 ifadesinden t2 INNER JOIN t1 ifadesini üretebilir. Bu tür bir dönüşüm, query transformation rule olarak adlandırılır.
Sorgu optimizer şu iş akışını izler:
Optimizer, veritabanının bir SQL deyimini ayrıştırarak oluşturduğu ilk sorgu planını alır.
Ardından, eşdeğer sorgu planları oluşturmak için ilk plana sorgu dönüşüm kuralları uygular.
Optimizer, istatistikleri ve bir maliyet modelini kullanarak tahmini çalıştırma maliyeti en düşük olan planı seçer ve bunu nihai çalıştırma planı olarak çalıştırma motoruna iletir.
Sorgu optimizasyon özelliği, en iyi sorgu planını belirlemek üzere kardinalite tahmini ve maliyet hesaplaması yapmak için istatistiklere dayanır. IMCI'de tablo istatistikleri şunları içerir:
Bir histogram, bir sütunun değer dağılımını açıklar ve temel olarak tek bir tabloda değer aralıklarının ve eşitlik yüklemelerinin seçilebilirliğini tahmin etmek için kullanılır.
Bir sütundaki farklı değerlerin sayısı, temel olarak bir
Group Byyan tümcesindeki grup sayısını tahmin etmek için kullanılır ve eşitlik yüklemelerinin seçilebilirliğini tahmin etmeye de yardımcı olabilir.Sütunun benzersiz bir dizine veya yabancı anahtar kısıtlamasına sahip olup olmadığı gibi diğer kısıtlamalar.
Sorgu optimizer, bir sorgu planındaki her operatörün maliyetini iki faktöre göre hesaplar:
Operatör tarafından işlenen toplam satır sayısı; bu değer istatistiklerden tahmin edilebilir.
Sorgu planında kullanılan her operatörün algoritmik karmaşıklığı.
Bir operatör tarafından işlenen toplam satır sayısı, karmaşıklık fonksiyonundaki bir parametredir. Bir sorgu planının toplam çalıştırma maliyeti, plandaki tüm operatörlerin maliyetlerinin toplamıdır. Önceki şekildeki iki sorgu planı için hash join algoritmasının kullanıldığı varsayılırsa maliyet formülü şöyledir:
Costjoin=Cardinner+Cardouter
İki çalıştırma planının maliyetleri aşağıdaki gibidir:
CostA==10000+1+1000+100+10000+10=21111
CostB==10000+1+100+10+1000+10=11121
Hesaplama, Plan B'nin daha düşük bir çalıştırma maliyetine sahip olduğunu göstermektedir. Bu nedenle optimizer, Plan B'yi nihai çalıştırma planı olarak seçer.
Ön koşullar
PolarDB kümeniz aşağıdaki sürüm gereksinimlerinden birini karşılamalıdır:
PolarDB for MySQL 8.0.1, revizyon 8.0.1.1.31 veya üzeri.
PolarDB for MySQL 8.0.2, revizyon 8.0.2.2.12 veya üzeri.
Küme sürümünüzü doğrulamak için küme sürüm numaranızı görüntüleyin.
Sınırlamalar
Aşağıdaki senaryolar, kardinalite tahmininde önemli hatalara neden olabilir; bu durum optimizer'ın alt optimal bir sorgu planı seçmesine yol açabilir. Optimizer'ı daha iyi bir sorgu planına yönlendirmek için HINT sözdizimini kullanabilirsiniz.
Aynı tablonun farklı sütunlarında karşılaştırma operatörü kullanan bir yükleme içeren sorgular; örneğin
t1.c1>t1.c2.İstatistikler kullanılarak tahmin edilemeyen bir operatör içeren yükleme kullanan sorgular; örneğin
t1.c1 MOD 2=1veyat1.c2 LIKE '%ABC%'.Optimizasyon sırasında hesaplanamayan bir ifade içeren yükleme bulunan sorgular; örneğin
t1.c1+t1.c3>100.Bir operatörde yer alan sütunların, yükleme seçilebilirliğini tahmin etmek için gereken istatistiklerden yoksun olduğu sorgular; örneğin
SELECT a, SUM(b) FROM t1 HAVING SUM(b) > 10.Birden çok yüklemenin bir
ANDoperatörüyle bağlandığı durumlar; örneğint1.c1>10 AND t1.c3<5.Sorgunun çok fazla iç içe geçmiş katmana sahip olması.
Sorgunun çok fazla tabloyu birleştirmesi.
loose_imci_max_enum_join_pairsparametresini değiştirerek IMCI optimizer'ın aradığı birleşim sayısını ayarlayabilirsiniz.
Parametreler
IMCI sorgu optimizasyon özelliğini etkinleştirmek ve kullanmak için konsolda aşağıdaki parametreleri yapılandırabilirsiniz. Parametreleri nasıl ayarlayacağınızla ilgili talimatlar için bkz. Küme ve düğüm parametrelerini belirtin.
|
Parametre |
Açıklama |
|
loose_imci_optimizer_switch |
IMCI sorgu optimizasyon özelliğini kontrol eder. Aşağıdaki işaretlerden oluşur:
|
|
loose_imci_auto_update_statistic |
IMCI optimizer'ın istatistikler eskidiğinde bunları otomatik olarak yeniden toplayıp toplamayacağını belirtir. Geçerli değerler:
|
|
loose_imci_max_enum_join_pairs |
Birleşim yeniden sıralama etkinleştirildiğinde IMCI optimizer'ın aradığı maksimum eşdeğer sorgu planı sayısını belirtir. Geçerli değerler: 0 ila 4294967295. Varsayılan değer: 2000. |
Yordam
IMCI optimizasyon özelliğini kullanmak için önce seçtiğiniz stratejiye göre istatistikleri toplamanız gerekir. İstatistikleri topladıktan sonra özelliği etkinleştirin ve ardından sorgularınızı çalıştırın.
-
İstatistikleri toplayın.
İstatistikleri toplamak için aşağıdaki iki stratejiden birini kullanabilirsiniz:
IMCI optimizasyonu gerektiren tablolarda en son istatistikleri toplamak amacıyla
ANALYZE TABLEkomutunu düzenli aralıklarla çalıştırın.(Önerilen) IMCI'nin yeni etkinleştirildiği tablolar için başlangıç istatistiklerini oluşturmak üzere salt okunur bir düğümde
ANALYZE TABLEkomutunu çalıştırın. Ardından, istatistikleri otomatik olarak güncellemek içinloose_imci_auto_update_statisticparametresini ASYNC olarak ayarlayın.
-
IMCI sorgu optimizasyon özelliğini etkinleştirin.
Konsolda
loose_imci_optimizer_switchparametresini ayarlayarak IMCI sorgu optimizasyon özelliğini etkinleştirebilirsiniz. Sorgularınızı çalıştırın.
Performans karşılaştırması
Aşağıdaki örnekte, toplama işlevleri içeren çok tablolu bir sorgu olan TPC-H Q8 kullanılmaktadır.
SELECT
o_year,
SUM(
CASE
WHEN nation = 'BRAZIL' THEN volume
ELSE 0
END
) / SUM(volume) AS mkt_share
FROM
(
SELECT
EXTRACT(
year
FROM
o_orderdate
) AS o_year,
l_extendedprice * (1 - l_discount) AS volume,
n2.n_name AS nation
FROM
lineitem,
orders,
part,
supplier,
customer,
nation n1,
nation n2,
region
WHERE
p_partkey = l_partkey
AND s_suppkey = l_suppkey
AND l_orderkey = o_orderkey
AND o_custkey = c_custkey
AND c_nationkey = n1.n_nationkey
AND n1.n_regionkey = r_regionkey
AND r_name = 'AMERICA'
AND s_nationkey = n2.n_nationkey
AND o_orderdate BETWEEN DATE '1995-01-01'
AND DATE '1996-12-31'
AND p_type = 'ECONOMY ANODIZED STEEL'
) AS all_nations
GROUP By
o_year
ORDER BY
o_year;
IMCI optimizasyonu devre dışı bırakıldığında sorgu planı aşağıdaki gibidir:
Bu planda, çok sayıda birleşim büyük ara sonuç kümeleri üretir; bu durum veri miktarını ve sonraki operatörlerin işleme maliyetini artırarak sorgu gecikme süresinin uzamasına neden olur. TPC-H SF100 veri kümesiyle çalışan 32 çekirdekli bir makinede bu sorgunun tamamlanması 7,017 ms sürmüştür.IMCI optimizasyonu etkinleştirildiğinde sorgu planı aşağıdaki gibidir:
IMCI optimizer birleşimleri yeniden sıralayarak çoğu birleşim operatörünün çıktı boyutunu milyonlarca satıra düşürür. Bu durum, sonraki operatörlerin işleme maliyetini etkili bir şekilde azaltır. Aynı TPC-H SF100 veri kümesiyle çalışan 32 çekirdekli makinede bu sorgunun tamamlanması 1.900 ms sürmüş ve sorgu süresi %73 oranında azalmıştır.