Veri büyümesi, istatistik sapmaları veya sürüm yükseltmeleri gibi nedenlerle çalıştırma planları beklenmedik şekilde değiştiğinde, genellikle SQL sorgularına doğrudan eniyileyici ipuçları (optimizer hints) eklemeniz gerekir. Uygulama kodunu değiştiremiyorsanız Statement Outline özelliği, SQL koduna dokunmadan bu ipuçlarını eklemenizi sağlar. Bu özellik, normalize edilmiş bir SQL özetini eşleştirerek ve sorguyu eniyileyici görmeden önce yapılandırılmış ipucuyla yeniden yazarak belirli bir ifade için çalıştırma planını sabitler.
PolarDB, şablonları eklemek, ön izlemek, incelemek ve silmek için saklı yordamlar içeren DBMS_OUTLN paketini sunar.
Bir şablon ekledikten sonra hemen çalıştığını doğrulayın.CALL dbms_outln.preview_outline('<schema>', '<query>')komutunu çalıştırın; boş olmayan bir sonuç, şablonun eşleştiğini onaylar. Alternatif olarak hedef sorgu üzerindeEXPLAINçalıştırın veExtrasütunundaUsing outline <ID>ifadesinin görünüp görünmediğini kontrol edin.
Ön Koşullar
Başlamadan önce aşağıdakileri yaptığınızdan emin olun:
-
Aşağıdaki sürümlerden birini çalıştıran bir PolarDB for MySQL kümesine sahip olun:
Alt sürümü 5.6.1.0.36 veya üzeri olan PolarDB for MySQL 5.6
Alt sürümü 5.7.1.0.2 veya üzeri olan PolarDB for MySQL 5.7
Alt sürümü 8.0.1.1.1 veya üzeri olan PolarDB for MySQL 8.0.1
PolarDB for MySQL 8.0.2
Küme sürümünüzü kontrol etmek için bkz. Sürüm numarasını görüntüleme.
PolarDB for MySQL 5.6, eniyileyici ipuçlarını desteklemez. 5.6 sürümündeki kümeler için add_index_outline (bir dizin ipucu) kullanın.
Çalışma Mantığı
Statement Outline, eşleşen bir SQL ifadesini yakalar ve eniyileyici görmeden önce bunu yapılandırılmış ipucuyla yeniden yazar. Eşleştirme, SQL metninin normalize edilmiş özetine dayanır ve isteğe bağlı olarak belirli bir veritabanı ile sınırlandırılabilir:
Schema_nameayarlanırsa hem veritabanı adı hem de SQL özeti şablon kuralıyla eşleşmelidir.Schema_nameboş bırakılırsa yalnızca SQL özeti eşleşmelidir.
Tüm ipuçları mysql.outline sistem tablosunda kalıcı olarak saklanır ve başlatma sırasında belleğe yüklenir.
Şablon Türü Seçimi
| Eniyileyici ipucu şablonu | Dizin ipucu şablonu | |
|---|---|---|
| Mekanizma | Sorgu içine /*+ ... */ ipuçlarını ekler |
Bir tablo referansına USE INDEX, FORCE INDEX veya IGNORE INDEX ekler |
| Kontrol Alanı | Birleşim sırası, birleşim algoritması, sorgu bloğu davranışı, oturum değişkenleri | Eniyileyicinin belirli bir tablo için hangi dizini kullanacağı |
| MySQL 5.6 desteği | Yok | Var |
| Yordam | add_optimizer_outline |
add_index_outline |
Hızlı Başlangıç
Aşağıdaki örneklerde en yaygın şablon işlemleri gösterilmektedir. Tüm örnekler CALL dbms_outln.<procedure>() sözdizimini kullanır.
Bir sorguyu eniyileyici ipucuyla sabitleme
Bir eniyileyici ipucu kullanarak belirli bir dizini zorlayın:
CALL dbms_outln.add_optimizer_outline(
'test', -- Schema_name
'/*+ INDEX(t1 i_a) */', -- Hint string
'SELECT test.t1.a AS a FROM test.t1' -- Original SQL
);
Şablonun eşleştiğini doğrulayın:
CALL dbms_outln.preview_outline('test', 'SELECT test.t1.a AS a FROM test.t1');
Bir sorguyu dizin ipucuyla sabitleme
Bir USE INDEX ipucu uygulamak için add_index_outline kullanın. Bu yaklaşım MySQL 5.6 kümelerinde çalışır:
CALL dbms_outln.add_index_outline(
'outline_db', -- Schema_name
'', -- Digest (leave blank; the system calculates it from Query)
1, -- Position: the 1st table in the SQL
'USE INDEX', -- Type
'ind_1', -- Index name(s)
'', -- Scope (blank = all query types)
"SELECT * FROM t1 WHERE t1.col1 = 1 AND t1.col2 = 'xpchild'" -- Original SQL
);
Birleşim sırasını sabitleme
it1 ve it2 tablolarının önce birleşmesini zorlayın, geri kalan tabloları eniyileyiciye bırakın:
CALL dbms_outln.add_optimizer_outline(
'outline_db',
'/*+ JOIN_PREFIX(it1, it2) */',
'SELECT it3.id3, it2.i2, it1.id2
FROM t3 it3, t1 it1, t2 it2
WHERE it3.i3 = it1.id1
AND it2.id2 = it1.id2
GROUP BY it3.id3, it1.id2'
);
Tek bir ifade için oturum değişkeni ayarlama
Tek bir ifade çalıştırması için bir oturum değişkenini geçersiz kılmak amacıyla SET_VAR ipuçlarını uygulayın:
CALL dbms_outln.add_optimizer_outline(
'test',
'/*+ SET_VAR(max_execution_time=1) */',
'SELECT * FROM t1'
);
Satır deposu veya sütun deposu salt okunur düğümlerine yönlendirme
Bellek İçi Sütunlu Dizin (IMCI) salt okunur düğümlerine sahip kümelerde ifadelerin yönlendirilmesini SET_VAR ile zorlayın:
-- Force execution on columnstore (IMCI) read-only nodes
CALL dbms_outln.add_optimizer_outline('test', '/*+ SET_VAR(cost_threshold_for_imci=0) */', 'SELECT test.t1.a AS a FROM test.t1');
-- Force execution on row-store read-only nodes
CALL dbms_outln.add_optimizer_outline('test', '/*+ SET_VAR(use_imci_engine=OFF) */', 'SELECT test.t1.a AS a FROM test.t1');
Şablonları Yönetme
Eniyileyici ipucu şablonu ekleme
CALL dbms_outln.add_optimizer_outline('<Schema_name>', '<Hint>', '<Query>');
| Parametre | Açıklama |
|---|---|
Schema_name |
Veritabanı adı. Yalnızca özetle eşleştirmek için boş bırakın. |
Hint |
Tam eniyileyici ipucu dizesi, örneğin /*+ MAX_EXECUTION_TIME(1000) */. |
Query |
Sabitlenecek orijinal SQL ifadesi. |
Queryçift tırnaklı dizeler içerdiğinde tümQuerydeğerini çift tırnak içine alın ve içinde tek tırnak kullanın veya tam tersini yapın.Queryiçinde tek tırnakla oluşturulan bir şablon, SQL'i tek veya çift tırnakla çalıştırmanızdan bağımsız olarak SQL ile eşleşir.
Örnek: Bir sorguya yürütme süresi sınırı sabitleyin:
Orijinal SQL:
SELECT * FROM t1 WHERE name = "Tom";
Şablon için tırnak işaretlerini normalleştirin:
SELECT * FROM t1 WHERE name = 'Tom';
Şablonu ekleyin:
CALL dbms_outln.add_optimizer_outline("", "/*+ max_execution_time(1000) */", "SELECT * FROM t1 WHERE name='Tom'");
Dizin ipucu şablonu ekleme
CALL dbms_outln.add_index_outline('<Schema_name>', '<Digest>', <Position>, '<Type>', '<Hint>', '<Scope>', '<Query>');
| Parametre | Açıklama |
|---|---|
Schema_name |
Veritabanı adı. |
Digest |
Digest_text değerini hash'leyerek elde edilen 64 baytlık hash dizesi. Sistemin bunu Query değerinden hesaplaması için boş bırakın. Bkz. STATEMENT_DIGEST(). |
Position |
İpucunun uygulanacağı tablonun 1 tabanlı konumu. SQL metnindeki N. tablo için Position değerini N olarak ayarlayın. |
Type |
Dizin ipucu türü: USE INDEX, FORCE INDEX veya IGNORE INDEX. |
Hint |
Virgülle ayrılmış dizin adları listesi, örneğin ind_1,ind_2. |
Scope |
İpucunu bir sorgu türüyle sınırlar: FOR JOIN, FOR ORDER BY veya FOR GROUP BY. Tüm sorgu türlerine uygulamak için boş bırakın. |
Query |
Sabitlenecek orijinal SQL ifadesi. |
Örnek:
CALL dbms_outln.add_index_outline(
'outline_db', '', 1, 'USE INDEX', 'ind_1', '',
"SELECT * FROM t1 WHERE t1.col1 = 1 AND t1.col2 = 'xpchild'"
);
Şablonu ön izleme
preview_outline , sorguyu çalıştırmadan belirli bir sorguyla eşleşen şablonları gösterir. Üretim ortamında güvenmeden önce bir şablonu doğrulamak için bunu kullanın.
CALL dbms_outln.preview_outline('<Schema_name>', '<Query>');
Örnek:
CALL dbms_outln.preview_outline('outline_db', "SELECT * FROM t1 WHERE t1.col1 = 1 AND t1.col2 = 'xpchild'");
Çıktı:
+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
| SCHEMA | DIGEST | BLOCK_TYPE | BLOCK_NAME | BLOCK | HINT |
+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
| outline_db | b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4c | TABLE | t1 | 1 | USE INDEX (`ind_1`) |
+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
1 row in set (0.01 sec)
Aktif şablonları görüntüleme
show_outline , isabet sayıları ve taşma sayılarıyla birlikte şu anda belleğe yüklenen tüm şablonları listeler.
CALL dbms_outln.show_outline();
Çıktı:
+------+------------+------------------------------------------------------------------+-----------+-------+------+-------------------------------------------------------+------+----------+-------------------------------------------------------------------------------------+
| ID | SCHEMA | DIGEST | TYPE | SCOPE | POS | HINT | HIT | OVERFLOW | DIGEST_TEXT |
+------+------------+------------------------------------------------------------------+-----------+-------+------+-------------------------------------------------------+------+----------+-------------------------------------------------------------------------------------+
| 33 | outline_db | 36bebc61fce7e32b93926aec3fdd790dad5d895107e2d8d3848d1c60b74bcde6 | OPTIMIZER | | 1 | /*+ SET_VAR(foreign_key_checks=OFF) */ | 1 | 0 | SELECT * FROM `t1` WHERE `id` = ? |
| 32 | outline_db | 36bebc61fce7e32b93926aec3fdd790dad5d895107e2d8d3848d1c60b74bcde6 | OPTIMIZER | | 1 | /*+ MAX_EXECUTION_TIME(1000) */ | 2 | 0 | SELECT * FROM `t1` WHERE `id` = ? |
| 34 | outline_db | d4dcef634a4a664518e5fb8a21c6ce9b79fccb44b773e86431eb67840975b649 | OPTIMIZER | | 1 | /*+ BNL(t1,t2) */ | 1 | 0 | SELECT `t1` . `id` , `t2` . `id` FROM `t1` , `t2` |
| 35 | outline_db | 5a726a609b6fbfb76bb8f9d2a24af913a2b9d07f015f2ee1f6f2d12dfad72e6f | OPTIMIZER | | 2 | /*+ QB_NAME(subq1) */ | 2 | 0 | SELECT * FROM `t1` WHERE `t1` . `col1` IN ( SELECT `col1` FROM `t2` ) |
| 36 | outline_db | 5a726a609b6fbfb76bb8f9d2a24af913a2b9d07f015f2ee1f6f2d12dfad72e6f | OPTIMIZER | | 1 | /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ | 2 | 0 | SELECT * FROM `t1` WHERE `t1` . `col1` IN ( SELECT `col1` FROM `t2` ) |
| 30 | outline_db | b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4c | USE INDEX | | 1 | ind_1 | 3 | 0 | SELECT * FROM `t1` WHERE `t1` . `col1` = ? AND `t1` . `col2` = ? |
| 31 | outline_db | 33c71541754093f78a1f2108795cfb45f8b15ec5d6bff76884f4461fb7f33419 | USE INDEX | | 2 | ind_2 | 1 | 0 | SELECT * FROM `t1` , `t2` WHERE `t1` . `col1` = `t2` . `col1` AND `t2` . `col2` = ? |
+------+------------+------------------------------------------------------------------+-----------+-------+------+-------------------------------------------------------+------+----------+-------------------------------------------------------------------------------------+
HIT: Bu şablonun eşleştiği ve uygulandığı süre sayısı.OVERFLOW: Şablonun ifadede hedef sorgu bloğunu veya tabloyu bulamadığı süre sayısı.
Şablon silme
CALL dbms_outln.del_outline(<Id>);
show_outline çıktısından ID değerini iletin. Şablon hem bellekten hem de mysql.outline sistem tablosundan hemen kaldırılır.
Örnek:
CALL dbms_outln.del_outline(32);
ID mevcut değilse sistem bir hata yerine uyarı kaydeder:
CALL dbms_outln.del_outline(1000);
-- Query OK, 0 rows affected, 2 warnings (0.00 sec)
SHOW WARNINGS;
+---------+------+----------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------+
| Warning | 7521 | Statement outline 1000 is not found in table |
| Warning | 7521 | Statement outline 1000 is not found in cache |
+---------+------+----------------------------------------------+
Şablonun Uygulandığını Doğrulama
Bir şablon ekledikten sonra aşağıdaki yöntemlerden birini kullanarak çalıştığını onaylayın.
Yöntem 1: preview_outline (önerilen)
CALL dbms_outln.preview_outline('outline_db', "SELECT * FROM t1 WHERE t1.col1 = 1 AND t1.col2 = 'xpchild'");
Boş olmayan bir sonuç kümesi şablonun eşleştiği anlamına gelir.
Yöntem 2: EXPLAIN
Hedef sorguda EXPLAIN çalıştırın. Şablon uygulandıysa Extra sütunu Using outline <ID> ifadesini içerir.
EXPLAIN SELECT * FROM t1 WHERE t1.col1 = 1 AND t1.col2 = 'xpchild';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+------------------------------+
| 1 | SIMPLE | t1 | NULL | ref | ind_1 | ind_1 | 5 | const | 1 | 100.00 | Using where; Using outline 1 |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+------------------------------+
ExtrasütunundakiUsing outline <ID>metni yalnızca şu durumlarda görünür:
Alt sürümü 8.0.1.0.34 veya üzeri olan PolarDB for MySQL 8.0.1
Alt sürümü 8.0.2.2.27 veya üzeri olan PolarDB for MySQL 8.0.2
Yeniden yazılan SQL'i görmek için EXPLAIN sonrasında SHOW WARNINGS çalıştırın:
SHOW WARNINGS;
+-------+------+----------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------+
| Note | 1003 | /* select#1 */ SELECT `outline_db`.`t1`.`id` AS `id`,... USE INDEX (`ind_1`) WHERE ... |
+-------+------+----------------------------------------------------------+
Sharding Outline
Tablo parçalama (sharding) senaryolarında fiziksel tablolar sayısal sonek desenini takip eder — t_001, t_002, ..., t_999. Standart bir Statement Outline yalnızca tam tablo adıyla eşleşir ve her tablo için ayrı bir kural gerektirir. Büyük bir parçalama kurulumunda bu durum yönetilemez hale gelir.
Sharding Outline, sondaki sayıları joker karakter deseni olarak ele alarak bu sorunu çözer. t_1, t_2 ve t_100 değerlerini tek bir t_? deseni olarak tanır; böylece tek bir kural eşleşen tüm tabloları kapsar.
| Standart Şablon | Sharding Şablonu | |
|---|---|---|
| Tablo adı eşleştirme | Tam eşleşme, örn. t_1 |
Joker karakter deseni, örn. t_? |
| Kapsam | Belirli bir tablo | Tutarlı bir adlandırma desenine sahip tüm parçalanmış tablolar |
| Gerekli kurallar | Her parçalanmış tablo için bir tane | Eşleşen tüm tablolar için tek bir kural |
| Bakım | Yüksek: toplu oluşturma, güncelleme ve silme gerekir | Düşük: tek bir kural tüm eşleşen tabloları yönetir |
Desteklenen sürümler:
Alt sürümü 8.0.1.1.54 veya üzeri olan PolarDB for MySQL 8.0.1
Alt sürümü 8.0.2.2.33 veya üzeri olan PolarDB for MySQL 8.0.2
Sharding Outline'u Etkinleştirme
PolarDB konsolu nda Parameter Settings bölümüne gidin ve
loose_outline_templated_digest_for_sharding_tableparametresiniONolarak ayarlayın. Ayrıntılar için bkz. Parametreleri ayarlama.Queryolarak herhangi bir parçalanmış tablo adıylaadd_optimizer_outline_shardingçağrısını yapın. Yordam, sondaki sayıları otomatik olarakt_?desenine dönüştürür.
CALL dbms_outln.add_optimizer_outline_sharding(
'test', -- Schema_name
'', -- Digest (leave blank)
1, -- Position
'/*+ MAX_EXECUTION_TIME(1000) */', -- Hint string
"SELECT t_1.c_1 FROM t_1" -- Any sharded table name works: t_1, t_2, t_100
);
Çalıştırmadan sonra kural t_? ile eşleşen tüm tablolar için geçerli olur; buna t_1, t_2 ve t_100 dahildir.
Parametreler
PolarDB konsolu ndaki Parameter Settings altında Statement Outline davranışını yapılandırın. Bkz. Parametreleri ayarlama.
| Parametre | Kapsam | Açıklama |
|---|---|---|
loose_opt_outline_enabled |
Global | Statement Outline'u etkinleştirir veya devre dışı bırakır. ON (varsayılan) veya OFF. |
loose_outline_templated_digest_for_sharding_table |
Oturum | Sharding Outline'u etkinleştirir veya devre dışı bırakır. ON (varsayılan) veya OFF. PolarDB for MySQL 8.0.1 alt sürümü 8.0.1.1.54 veya üzeri, ya da 8.0.2 alt sürümü 8.0.2.2.33 veya üzeri gerektirir. |
Ek: Statement Outline sistem tablosu
PolarDB tüm şablon kurallarını mysql.outline içinde saklar. Tablo başlatma sırasında otomatik olarak oluşturulur.
CREATE TABLE `mysql`.`outline` (
`Id` bigint(20) NOT NULL AUTO_INCREMENT,
`Schema_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
`Digest` varchar(64) COLLATE utf8_bin NOT NULL,
`Digest_text` longtext COLLATE utf8_bin,
`Type` enum('IGNORE INDEX','USE INDEX','FORCE INDEX','OPTIMIZER')
CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`Scope` enum('','FOR JOIN','FOR ORDER BY','FOR GROUP BY')
CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '',
`State` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'Y',
`Position` bigint(20) NOT NULL,
`Hint` text COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statement outline'
| Alan | Açıklama |
|---|---|
Id |
Benzersiz şablon kimliği. |
Schema_name |
Veritabanı adı. Boş olması, yalnızca özetle eşleştirileceği anlamına gelir. |
Digest |
Digest_text değerini hash'leyerek elde edilen 64 baytlık hash dizesi. Bkz. STATEMENT_DIGEST(). |
Digest_text |
Özet hesaplaması için kullanılan SQL ifadesinin normalize edilmiş biçimi. |
Type |
Eniyileyici ipuçları için OPTIMIZER. Dizin ipuçları için USE INDEX, FORCE INDEX veya IGNORE INDEX. |
Scope |
Dizin ipucu kapsamı: FOR JOIN, FOR ORDER BY veya FOR GROUP BY. Boş dize tüm sorgu türlerine uygulanır. Yalnızca dizin ipuçları için kullanılır. |
State |
Kuralın aktif olup olmadığı. Y (varsayılan) veya N. |
Position |
Eniyileyici ipuçları için: İpucunun hedeflediği sorgu bloğunun 1 tabanlı konumu. Dizin ipuçları için: SQL metnindeki tablonun 1 tabanlı konumu. |
Hint |
Eniyileyici ipuçları için: Tam ipucu dizesi, örn. /*+ MAX_EXECUTION_TIME(1000) */. Dizin ipuçları için: Virgülle ayrılmış dizin adları listesi, örn. ind_1,ind_2. |