MaxCompute:SQL sorgu optimizasyonu

Yürütme için tek bir örnek zorlama

Bu darboğazdan kaçınmak için küresel toplamaya gereksinim duyup duymadığınızı gözden geçirin. Gerekliyse iki aşamalı toplama deseni kullanın: önce yüksek kardinaliteli bir anahtara göre gruplayarak kısmi toplamayı tüm örneklerde paralel olarak çalıştırın ardından nihai toplamayı çok daha küçük olan ara sonuç kümesi üzerinde yapın.

Hatalı örnek sayısının etkisi

Örnek sayısını yapılandırma

• Tablo okuma görevleri (mapper'lar) için

  • Yöntem 1: Genel bir bölme boyutu parametresi ayarlama.

    -- Configure the maximum amount of input data per mapper instance. Unit: MB.
    -- Default value: 256. Valid values: [1,Integer.MAX_VALUE].
    SET odps.sql.mapper.split.size=<value>;

  • Yöntem 2: Tablo başına kontrol için sorgu ipucu kullanma. split_size ipucu genel parametreyi belirli bir tablo okuma işlemi için geçersiz kılar ve sorgunun geri kalanını etkilemeden daha ince ayarlı kontrol sağlar.

    -- Split the src table into subtasks of 1 MB each.
    SELECT a.key FROM src a /*+split_size(1)*/ JOIN src2 b ON a.key=b.key;

  • Yöntem 3: Verileri tablo düzeyinde boyuta satır sayısına veya belirtilen bir DOP'a göre bölme.

  odps.sql.mapper.split.size parametresi tüm mapper aşamalarına genel olarak uygulanır ve minimum değeri 1 MB'dır. Satırların küçük olduğu ancak hesaplama açısından pahalı olduğu durumlar için yani bölme başına veri hacmini değiştirmeden daha fazla örnek istediğiniz durumlarda bunun yerine aşağıdaki tablo düzeyindeki parametreleri kullanın.

  Tablo düzeyinde DOP ayarı yapmak için aşağıdaki parametreleri kullanın:

  • Tablo düzeyinde örnek başına veri bölme boyutunu ayarlama.

    SET odps.sql.split.size = {"table1": 1024, "table2": 512};

  • Tablo düzeyinde örnek başına işlenen satır sayısını ayarlama.

    SET odps.sql.split.row.count = {"table1": 100, "table2": 500};

  • DOP'u doğrudan tablo düzeyinde ayarlama.

    SET odps.sql.split.dop = {"table1": 1, "table2": 5};

  Not

  odps.sql.split.row.count ve odps.sql.split.dop parametreleri yalnızca dahili tablolar işlem dışı tablolar ve kümelendirilmemiş tablolar için geçerlidir.

• Okuma dışı görevler (reducer'lar ve joiner'lar) için

  • Yöntem 1: Reducer örnek sayısını ayarlama. Bu ayar sorgudaki tüm reducer görevlerine uygulanır.

    -- Set the number of reducer instances.
    -- Valid values: [1,99999].
    SET odps.stage.reducer.num=<value>;

  • Yöntem 2: Joiner örnek sayısını ayarlama. Bu ayar sorgudaki tüm joiner görevlerine uygulanır.

    -- Set the number of joiner instances.
    -- Valid values: [1,99999].
    SET odps.stage.joiner.num=<value>;

  • Yöntem 3: Yukarı akış mapper sayısını ayarlama. Reducer örnek sayısı kendinden önce gelen mapper aşamasından türetilir. Mapper sayısını artırmak reducer paralelliğini dolaylı olarak artırır.

Bir sorgudaki her pencere fonksiyonu genellikle ayrı bir reduce işini tetikler. Bir sorgu birden fazla pencere fonksiyonu içerdiğinde bu durum kaynak tüketimini önemli ölçüde artırır. MaxCompute aşağıdaki iki koşulun da karşılanması durumunda birden fazla pencere fonksiyonunu otomatik olarak tek bir reduce işinde birleştirir:

• OVER yan tümceleri aynıdır yani aynı PARTITION BY ve ORDER BY koşullarını içerir.

• Pencere fonksiyonları aynı SELECT ifadesinde yer alır.

Aşağıdaki sorguda hem RANK() hem de ROW_NUMBER() fonksiyonları aynı OVER yan tümcesini paylaşır ve aynı SELECT ifadesinde görünür bu nedenle otomatik birleşmeye uygundur. MaxCompute bunları iki ayrı iş yerine tek bir reduce işinde çalıştırır.

SELECT
RANK() OVER (PARTITION BY A ORDER BY B desc) AS RANK,
ROW_NUMBER() OVER (PARTITION BY A ORDER BY B desc) AS row_num
FROM MyTable;

Bir IN alt sorgusu kullanarak filtreleme yapan bir sorguyu ele alalım:

SELECT * FROM table_a a WHERE a.col1 IN (SELECT col1 FROM table_b b WHERE xxx);

Eğer table_b üzerindeki alt sorgu col1 için 9.999'dan fazla değer döndürürse MaxCompute şu hatayı bildirir: records returned from subquery exceeded limit of 9999. Bu sınırı kaldırmak için sorguyu bir JOIN olarak yeniden yazın:

SELECT a.* FROM table_a a JOIN (SELECT DISTINCT col1 FROM table_b b WHERE xxx) c ON (a.col1 = c.col1);

MaxCompute'un bir JOIN sırasında bölüm budama uygulayabilmesi için bölümlenmiş tabloları join işlemi gerçekleşmeden önce filtreleyin sonrasında değil. Erken filtreleme yapılmazsa sistem önce tüm bölümler üzerinde JOIN işlemini gerçekleştirir ardından filtreyi uygular bu da gerekenden çok daha fazla verinin taranmasına neden olur.

Şu kuralları izleyin:

• JOIN işleminden önce bir alt sorgu içinde ana tablo üzerinde bölüm sınırlama koşullarını uygulayın.

• Ana tabloyu filtreleyen diğer WHERE yan tümcelerini SQL ifadesinin sonuna yerleştirin.

• İkincil tablo üzerindeki bölüm sınırlama koşullarını ON yan tümcesinde veya bir alt sorguda uygulayın nihai WHERE yan tümcesinde değil.

Aşağıdaki örnekler bu uygulamaları göstermektedir.

SELECT * FROM A JOIN (SELECT * FROM B WHERE dt=20150301)B ON B.id=A.id WHERE A.dt=20150301;
SELECT * FROM A JOIN B ON B.id=A.id WHERE B.dt=20150301; -- We recommend that you do not use this statement. The system performs the JOIN operation before it performs partition pruning. This increases the amount of data and causes the query performance to deteriorate. 
SELECT * FROM (SELECT * FROM A WHERE dt=20150301)A JOIN (SELECT * FROM B WHERE dt=20150301)B ON B.id=A.id;

Dize toplama işlemleri için wm_concat genellikle collect_list fonksiyonundan daha iyi performans gösterir. Aşağıdaki örnekler her iki fonksiyon kullanılarak yapılan eşdeğer işlemleri göstermektedir.

-- Implement the collect_list function.
SELECT concat_ws(',', sort_array(collect_list(key))) FROM src;
-- Implement the wm_concat function for better performance.
SELECT wm_concat(',', key) WITHIN GROUP (ORDER BY key) FROM src;

-- Implement the collect_list function.
SELECT array_join(collect_list(key), ',') FROM src;
-- Implement the wm_concat function for better performance.
SELECT wm_concat(',', key) FROM src;