ApsaraDB for SelectDB:Eşzamanlı maddileştirilmiş görünüm

Söz dizimi

CREATE MATERIALIZED VIEW <mv_name> AS <query>
[PROPERTIES ("key" = "value")]

query formatı:

SELECT select_expr [, select_expr ...]
FROM <base_table_name>
[GROUP BY column_name [, column_name ...]]
[ORDER BY column_name [, column_name ...]]

Parametreler

SELECT deyimi kısıtlamaları:

• Yalnızca tek tablo — alt sorgu yok.

• Sütunlar artan değerli sütunlar, sabitler, yinelenen ifadeler veya pencere işlevleri içermemelidir.

• SELECT ifadesi bölümleme anahtarı veya kovalama sütunlarını içeriyorsa, bu sütunlar maddileştirilmiş görünümde Anahtar sütunları olmalıdır.

• İzin verilen yan tümceler: WHERE, GROUP BY, ORDER BY.

• Yasaklanan yan tümceler: JOIN, HAVING, LIMIT, LATERAL VIEW.

Toplama işlevi kısıtlamaları:

Desteklenen toplama işlevleri:

ORDER BY otomatik tamamlama kuralları (ORDER BY belirtildiğinde):

• Toplama türü görünüm: tüm gruplama sütunları sıralama sütunu olur.

• Toplama dışı tür görünüm: sütunların ilk 36 baytı sıralama sütunu olur.

• Üçten az sütun otomatik olarak tamamlanırsa ilk üç sütun kullanılır.

• GROUP BY belirtilirse ORDER BY gruplama sütunlarıyla eşleşmelidir.

Tasarım ilkeleri

• Paylaşılan kalıpları soyutlayın. Maddileştirilmiş görünümü, birden çok sorgunun paylaştığı toplama kalıplarına göre tanımlayın. Yalnızca tek bir sorgu tarafından eşleştirilen bir görünüm, minimal fayda için depolama alanı tüketir.

• Yalnızca yaygın boyutları kapsayın. Her boyut kombinasyonu için maddileştirilmiş görünüm gerekmez. Üretim sorgularında en sık görülen kombinasyonları hedefleyin.

Örnekler

Aşağıdaki örneklerde bu temel tablo kullanılmaktadır:

CREATE TABLE duplicate_table (
    k1 INT NULL,
    k2 INT NULL,
    k3 BIGINT NULL,
    k4 BIGINT NULL
)
DUPLICATE KEY (k1, k2, k3, k4)
DISTRIBUTED BY HASH(k4) BUCKETS 3;

Örnek 1: Yeniden sıralanmış önekle sütun alt kümesi

CREATE MATERIALIZED VIEW k1_k2 AS
SELECT k2, k1 FROM duplicate_table;

Sonuç: Yalnızca k2 ve k1 içeren, k2 sütununu baştaki sıralama sütunu olarak kullanan bir görünüm. k2 üzerinde filtreleme yapan sorgular, temel tablonun yerine bu görünümün önek dizinini kullanabilir.

Örnek 2: Açık sıralama düzeni

CREATE MATERIALIZED VIEW k2_order AS
SELECT k2, k1 FROM duplicate_table ORDER BY k2;

Örnek 3: Toplama

CREATE MATERIALIZED VIEW k1_k2_sumk3 AS
SELECT k1, k2, sum(k3)
FROM duplicate_table
GROUP BY k1, k2;

Sonuç: k1 ve k2 sütunlarını Anahtar sütunları, sum(k3) değerini ise toplanmış değer olarak içeren bir görünüm. GROUP BY kullanıldığı ve ORDER BY atlandığı için SelectDB k1, k2 sütunlarını sıralama sütunları olarak otomatik tamamlar.

SHOW ALTER TABLE MATERIALIZED VIEW FROM <database>;

SHOW ALTER TABLE MATERIALIZED VIEW FROM test_db;

+--------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+
| JobId  | TableName     | CreateTime          | FinishTime          | BaseIndexName | RollupIndexName | RollupId | TransactionId | State    | Msg  | Progress | Timeout |
+--------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+
| 494349 | sales_records | 2020-07-30 20:04:56 | 2020-07-30 20:04:57 | sales_records | store_amt       | 494350   | 133107        | FINISHED |      | NULL     | 2592000 |
+--------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+

DESC <table_name> ALL;

DESC duplicate_table ALL;

+-----------------+---------------+---------------+--------+--------------+------+-------+---------+-------+---------+------------+-------------+
| IndexName       | IndexKeysType | Field         | Type   | InternalType | Null | Key   | Default | Extra | Visible | DefineExpr | WhereClause |
+-----------------+---------------+---------------+--------+--------------+------+-------+---------+-------+---------+------------+-------------+
| duplicate_table | DUP_KEYS      | k1            | INT    | INT          | Yes  | true  | NULL    |       | true    |            |             |
|                 |               | k2            | INT    | INT          | Yes  | true  | NULL    |       | true    |            |             |
|                 |               | k3            | BIGINT | BIGINT       | Yes  | true  | NULL    |       | true    |            |             |
|                 |               | k4            | BIGINT | BIGINT       | Yes  | true  | NULL    |       | true    |            |             |
|                 |               |               |        |              |      |       |         |       |         |            |             |
| k2_order        | DUP_KEYS      | mv_k2         | INT    | INT          | Yes  | true  | NULL    |       | true    | `k2`       |             |
|                 |               | mv_k1         | INT    | INT          | Yes  | false | NULL    | NONE  | true    | `k1`       |             |
|                 |               |               |        |              |      |       |         |       |         |            |             |
| k1_k2           | DUP_KEYS      | mv_k2         | INT    | INT          | Yes  | true  | NULL    |       | true    | `k2`       |             |
|                 |               | mv_k1         | INT    | INT          | Yes  | true  | NULL    |       | true    | `k1`       |             |
|                 |               |               |        |              |      |       |         |       |         |            |             |
| k1_k2_sumk3     | AGG_KEYS      | mv_k1         | INT    | INT          | Yes  | true  | NULL    |       | true    | `k1`       |             |
|                 |               | mv_k2         | INT    | INT          | Yes  | true  | NULL    |       | true    | `k2`       |             |
|                 |               | mva_SUM__`k3` | BIGINT | BIGINT       | Yes  | false | NULL    | SUM   | true    | `k3`       |             |
+-----------------+---------------+---------------+--------+--------------+------+-------+---------+-------+---------+------------+-------------+

SHOW CREATE MATERIALIZED VIEW <mv_name> ON <table_name>;

SHOW CREATE MATERIALIZED VIEW id_col1 ON table3;

+-----------+----------+----------------------------------------------------------------+
| TableName | ViewName | CreateStmt                                                     |
+-----------+----------+----------------------------------------------------------------+
| table3    | id_col1  | create materialized view id_col1 as select id,col1 from table3 |
+-----------+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

Devam eden oluşturma işlemini iptal etme

Oluşturma görevi henüz tamamlanmamışsa aşağıdaki komutla iptal edin:

CANCEL ALTER TABLE MATERIALIZED VIEW FROM <database>.<table_name>;

Örnek:

CANCEL ALTER TABLE MATERIALIZED VIEW FROM test_db.duplicate_table;

Görünüm zaten oluşturulmuşsa bu komutun etkisi olmaz — bunun yerine DROP kullanın.

Tamamlanmış maddileştirilmiş görünümü kaldırma

DROP MATERIALIZED VIEW [IF EXISTS] <mv_name> ON <table_name>;

Örnek:

-- List materialized views before dropping
DESC duplicate_table ALL;

-- Drop the view named k1_k2
DROP MATERIALIZED VIEW k1_k2 ON duplicate_table;

-- Confirm the view is gone
DESC duplicate_table ALL;

EXPLAIN <your_query>;

BITMAP_UNION ile kesin ayrıntılı sayım

Senaryo: Birden çok boyuta göre gruplandırılmış benzersiz kullanıcıların (veya yüksek kardinaliteli herhangi bir tam sayı sütununun) sayılması.

Tipik sorgu:

SELECT advertiser, channel, COUNT(DISTINCT user_id)
FROM advertiser_view_record
GROUP BY advertiser, channel;

Büyük tablolar üzerinde COUNT(DISTINCT ...) kullanımı maliyetlidir. BITMAP_UNION içeren bir maddileştirilmiş görünüm, verileri önceden tekilleştirerek sorgunun ham satırlar yerine toplanmış bit haritalarını okumasını sağlar.

Görünümü oluşturun:

CREATE MATERIALIZED VIEW advertiser_uv AS
SELECT advertiser, channel, bitmap_union(to_bitmap(user_id))
FROM advertiser_view_record
GROUP BY advertiser, channel;

user_id INT türündedir, bu nedenle to_bitmap() işleviyle sarmalayın ve ardından bitmap_union uygulayın. Bu işlem, tam sayıları işlevin gerektirdiği bit haritası biçimine dönüştürür.

Görünüm oluşturulduktan sonra (State = FINISHED), orijinal sorgu otomatik olarak şu şekilde yeniden yazılır:

SELECT advertiser, channel, bitmap_union_count(to_bitmap(user_id))
FROM advertiser_uv
GROUP BY advertiser, channel;

Eşleşmeyi doğrulayın:

EXPLAIN SELECT advertiser, channel, COUNT(DISTINCT user_id)
FROM advertiser_view_record
GROUP BY advertiser, channel;

EXPLAIN çıktısında OlapScanNode düğümünü bulun. rollup değerinin advertiser_uv olduğunu doğrulayın. Ayrıca count(distinct) işleminin bitmap_union_count(to_bitmap) olarak yeniden yazıldığını teyit edin.

HLL_UNION ile yaklaşık ayrıntılı sayım

Senaryo: Kesin hassasiyetin gerekli olmadığı ve sorgu hızının daha önemli olduğu benzersiz sayımların tahmini.

CREATE MATERIALIZED VIEW approx_uv AS
SELECT advertiser, channel, hll_union(hll_hash(user_id))
FROM advertiser_view_record
GROUP BY advertiser, channel;

user_id üzerinde approx_count_distinct, ndv, hll_union_agg veya hll_raw_agg kullanan sorgular bu görünümle otomatik olarak eşleşir.

user_id HLL_UNION(HLL_HASH(col)) formatı kullanılırken DECIMAL olamaz.

Önek dizini yeniden sıralama

Senaryo: Sıkça çalıştırılan bir sorgu, temel tablonun sıralama anahtarının baştaki sütunu olmayan bir sütun üzerinde filtreleme yapar.

Temel tablo DUPLICATE KEY (k1, k2, k3, k4) kullanıyor ancak sorgular genellikle k2 üzerinde filtreleme yapıyorsa:

CREATE MATERIALIZED VIEW k2_order AS
SELECT k2, k1 FROM duplicate_table ORDER BY k2;

WHERE k2 = ... içeren sorgular artık bu görünümün önek dizinini kullanarak tarama aralığını önemli ölçüde azaltabilir.