Sayfa numaraları arttıkça sayfalı sorgular yavaşlar; çünkü standart MySQL yürütme modeli tüm aday satırları SQL katmanına iletir ve bu satırlar OFFSET değerine göre hemen elenir. PolarDB for MySQL, gereksiz satırlar iletilmeden önce filtrelenmesi için LIMIT OFFSET değerlendirmesini depolama altyapısı katmanına aşağı iterek (pushdown) bu ek yükü azaltır.
Nasıl çalışır?
Standart MySQL'de LIMIT yan tümcesi SQL katmanında değerlendirilir. Depolama altyapısı tüm aday satırları SQL katmanına gönderir; SQL katmanı ise OFFSET değerine göre satırları atlayarak nihai sonuç kümesini döndürür. Büyük OFFSET değerlerine sahip sayfalı sorgularda bu durum, depolama altyapısının anında elenen çok sayıda satırı iletmesine neden olur ve sayfa sayısı arttıkça maliyet doğrusal olarak büyür.
LIMIT OFFSET pushdown etkinleştirildiğinde PolarDB, satırları SQL katmanına ulaşmadan önce depolama altyapısı katmanında filtreler. Bu işlem başlıca iki senaryoda gerçekleşir:
SQL katmanında WHERE koşulu yoksa: Yüklemeler
detach_range_conditionyüklem itme özelliği aracılığıyla tamamen aşağı itildiğinde SQL katmanında filtreleme kalmaz. Bu durumda LIMIT OFFSET doğrudan aşağı itilebilir.Tablo aramalı ikincil dizin erişimi: Bir sorgu ikincil bir dizin kullanıyor ve ayrıca birincil tablodan sütunlara ihtiyaç duyuyorsa LIMIT OFFSET pushdown, depolama altyapısı katmanında gereksiz tablo aramalarını atlar ve zaten elenecek olan satırların alınmasını önler.
Önkoşullar
Bu özelliği etkinleştirmeden önce PolarDB for MySQL kümenizin aşağıdaki sürüm gereksinimlerini karşıladığından emin olun:
Sürüm 8.0, düzeltme sürümü 8.0.1.1.16 veya üzeri
Sürüm 8.0, düzeltme sürümü 8.0.2.2.0 veya üzeri
Küme sürümünüzü kontrol etmek için bkz. Altyapı sürümünü sorgulama.
Sınırlamalar
LIMIT OFFSET pushdown yalnızca OFFSET değeri 512'den büyük olduğunda uygulanır. Küçük OFFSET değerlerinde atlanan satır sayısı az olduğu için pushdown koordinasyonunun ek yükü, standart yürütme yoluna kıyasla sağladığı faydayı aşar.
Bu kısıtlamayı kaldırmak için ignore_polar_optimizer_rule parametresini ON olarak ayarlayın. Talimatlar için bkz. Küme ve düğüm parametrelerini belirtin.
| Parametre | Düzey | Varsayılan | Açıklama |
|---|---|---|---|
ignore_polar_optimizer_rule |
Genel ve oturum | OFF | OFFSET eşik kısıtlamasını denetler. Eşiği devre dışı bırakmak ve herhangi bir OFFSET değeri için pushdown işleminde izin vermek üzere ON olarak ayarlayın. |
LIMIT OFFSET pushdown özelliğini etkinleştirin veya devre dışı bırakın
LIMIT OFFSET pushdown özelliği varsayılan olarak etkindir. Durumunu değiştirmek için loose_optimizer_switch parametresini kullanın. Parametreleri değiştirme talimatları için bkz. Küme ve düğüm parametrelerini belirtin.
| Parametre | Düzey | Değişken | Varsayılan | Açıklama |
|---|---|---|---|---|
loose_optimizer_switch |
Genel ve oturum | limit_offset_pushdown |
ON | LIMIT OFFSET pushdown özelliğini etkinleştirir veya devre dışı bırakır. |
loose_optimizer_switch |
Genel ve oturum | detach_range_condition |
ON | Yüklem itmeyi etkinleştirir veya devre dışı bırakır. WHERE koşulları mevcut olduğunda LIMIT OFFSET pushdown bu özelliğe bağlıdır. |
Pushdown işleminin aktif olduğunu doğrulayın
Sorgunuz üzerinde EXPLAIN komutunu çalıştırın ve Extra alanını kontrol edin. LIMIT OFFSET pushdown aktif olduğunda çıktı Using limit-offset pushdown ifadesini içerir. Bu ifade yoksa sorgu pushdown koşullarını karşılamamıştır; en yaygın nedenler için bkz. Pushdown işleminin uygulanmadığı durumlar.
Aşağıdaki örnekler TPC-H şemasını kullanır.
Yüklem koşulu yok (Q1)
WHERE yan tümcesi olmayan tam tablo taraması. Birincil tabloya doğrudan erişilir. SQL katmanında filtreleme kalmadığı için LIMIT OFFSET aşağı itilir.
EXPLAIN
SELECT *
FROM lineitem
LIMIT 10000000, 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: lineitem
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 59440464
filtered: 100.00
Extra: Using limit-offset pushdown
Extra: Using limit-offset pushdown değeri, satır filtrelemenin SQL katmanı yerine depolama altyapısı katmanında gerçekleştiğini doğrular.
Birincil anahtar üzerinde aralık koşulları (Q2)
WHERE koşulları birincil anahtara dayandığında yüklem itme özelliği (detach_range_condition), bunları SQL katmanından kaldırır ve LIMIT OFFSET'in aşağı itilmesine olanak tanır.
EXPLAIN SELECT * FROM lineitem WHERE l_orderkey > 10 AND l_orderkey < 60000000 LIMIT 10000000, 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: lineitem
partitions: NULL
type: range
possible_keys: PRIMARY,i_l_orderkey,i_l_orderkey_quantity
key: PRIMARY
key_len: 4
ref: NULL
rows: 29720232
filtered: 100.00
Extra: Using limit-offset pushdown
Tablo aramalı ikincil dizin (Q3)
Bir ikincil dizin kullanıldığında ve sorgu birincil tablodan sütunlar gerektirdiğinde LIMIT OFFSET pushdown, sonuç penceresinin dışında kalan satırlar için gereksiz birincil tablo aramalarını atlar.
EXPLAIN SELECT * FROM lineitem WHERE l_partkey > 10 AND l_partkey < 200000 LIMIT 5000000, 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: lineitem
partitions: NULL
type: range
possible_keys: i_l_partkey,i_l_suppkey_partkey
key: i_l_suppkey_partkey
key_len: 5
ref: NULL
rows: 11123302
filtered: 100.00
Extra: Using limit-offset pushdown
Dizine sahip ORDER BY
Bir ORDER BY yan tümcesi bir dizin tarafından karşılandığında yüklemeler SQL katmanından kaldırılır ve LIMIT OFFSET aşağı itilir.
EXPLAIN SELECT * FROM lineitem WHERE l_partkey > 10 AND l_partkey < 200000 ORDER BY l_partkey LIMIT 5000000, 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: lineitem
partitions: NULL
type: range
possible_keys: i_l_partkey,i_l_suppkey_partkey
key: i_l_suppkey_partkey
key_len: 5
ref: NULL
rows: 11123302
filtered: 100.00
Extra: Using limit-offset pushdown
Pushdown işleminin uygulanmadığı durumlar
LIMIT OFFSET pushdown için SQL katmanında filtreleme işi kalmamış olmalıdır. Extra alanında Using limit-offset pushdown görünmüyorsa aşağıdaki koşullardan herhangi birinin geçerli olup olmadığını kontrol edin:
| Koşul | Pushdown işleminin atlanma nedeni |
|---|---|
| OFFSET değeri 512 veya daha az | Az sayıda satırı atlamak, pushdown ek yükü olmadan daha düşük maliyetlidir. Geçersiz kılmak için ignore_polar_optimizer_rule parametresini ON olarak ayarlayın. |
| WHERE koşulları tamamen depolama altyapısına itilemiyor | SQL katmanında kalan filtreleme, LIMIT OFFSET'in aşağı itilmesini engeller. Bu durum, sorgu bir filesort veya dizinsiz bir ORDER BY kullandığında gerçekleşir. |
Performans iyileştirmeleri
Aşağıdaki şekil, ölçek faktörü 10 olan bir TPC-H veri kümesiyle ölçülen sorgu süresi iyileştirmelerini gösterir; LIMIT OFFSET pushdown özelliği etkin ve devre dışı iken Q1, Q2 ve Q3 sorguları karşılaştırılmıştır.
