Topik ini menjelaskan cara mengelola dan mengoptimalkan rencana eksekusi.
Optimizer menghasilkan rencana eksekusi untuk setiap Pernyataan SQL. Dalam banyak kasus, permintaan ke database dari aplikasi klien terdiri dari pernyataan yang sama dengan nilai parameter berbeda. Jika pernyataan-pernyataan tersebut diparameterisasi, maka pernyataan tersebut dianggap sama. Anda dapat membuat cache berdasarkan query SQL yang diparameterisasi untuk menyimpan rencana eksekusi dan informasi lainnya tanpa parameter. Cache ini disebut plan cache.
Plan cache digunakan untuk memastikan stabilitas query kompleks selama pembaruan versi. Query kompleks mencakup query yang melibatkan beberapa operasi JOIN. Hal ini dilakukan dengan menyimpan rencana eksekusi query SQL secara persisten dalam plan cache, sehingga rencana eksekusi tetap tersedia selama pembaruan versi. Proses ini dikenal sebagai pengelolaan rencana eksekusi.PolarDB-X.
Alur Kerja
Saat mengeksekusi pernyataan pada PolarDB-X, pernyataan tersebut diproses melalui prosedur berikut:
Pernyataan SQL diparameterisasi dengan mengganti semua parameter menggunakan karakter placeholder (
?).Pernyataan SQL yang diparameterisasi digunakan sebagai kunci untuk memeriksa apakah rencana eksekusi untuk pernyataan tersebut tersimpan dalam plan cache. Jika tidak ada rencana eksekusi yang tersimpan, optimizer akan dioptimalkan ulang.
Pernyataan sederhana dieksekusi langsung tanpa melalui pengelolaan rencana eksekusi.
Pernyataan kompleks dieksekusi berdasarkan rencana eksekusi tetap yang tersimpan dalam baseline. Jika beberapa rencana eksekusi tersedia, rencana dengan biaya terendah dipilih.
Plan cache
Secara default, fitur plan cache diaktifkan di PolarDB-X. Anda dapat mengeksekusi pernyataan EXPLAIN untuk melihat rencana eksekusi dari sebuah pernyataan SQL. Dalam hasil yang dikembalikan, HitCache menunjukkan apakah pernyataan SQL mengenai plan cache. Setelah fitur plan cache diaktifkan, PolarDB-X memparameterisasi setiap pernyataan SQL dengan mengganti semua konstanta menggunakan karakter placeholder (?) dan membuat daftar parameter. Dalam rencana eksekusi, Anda dapat melihat bahwa pernyataan SQL untuk operator LogicalView berisi karakter placeholder (?).
Pengelolaan rencana eksekusi
Setelah pernyataan SQL yang kompleks diproses oleh fitur plan cache, pernyataan tersebut juga tunduk pada pengelolaan rencana eksekusi.
Plan cache dan pengelolaan rencana eksekusi menggunakan pernyataan SQL yang diparameterisasi sebagai kunci untuk memilih rencana eksekusi. Rencana eksekusi dari semua pernyataan SQL disimpan dalam plan cache. Hanya pernyataan SQL yang kompleks yang diproses dalam pengelolaan rencana eksekusi. Satu atau lebih rencana eksekusi yang dioptimalkan diberikan untuk setiap pernyataan SQL karena variasi parameter.
Dalam pengelolaan rencana eksekusi, setiap pernyataan SQL sesuai dengan satu baseline, dan setiap baseline berisi satu atau lebih rencana eksekusi. Biaya setiap rencana eksekusi diperkirakan berdasarkan parameter yang digunakan dalam pernyataan SQL, dan rencana eksekusi dengan biaya terendah dipilih. Saat rencana eksekusi dalam plan cache diberikan untuk pengelolaan rencana eksekusi, SQL Plan Management (SPM) memulai proses untuk memeriksa apakah rencana eksekusi tersebut dikenal. Jika rencana eksekusi tersebut dikenal, SPM memeriksa apakah rencana tersebut menghasilkan biaya terendah. Jika rencana eksekusi tersebut tidak dikenal, SPM memeriksa apakah rencana tersebut perlu dieksekusi untuk menentukan apakah rencana tersebut perlu dioptimalkan.
Petunjuk untuk melakukan O&M
PolarDB-X menyediakan berbagai perintah untuk mengelola rencana eksekusi. Sintaks berikut digunakan:
BASELINE (LOAD|PERSIST|CLEAR|VALIDATE|LIST|DELETE) [Signed Integer,Signed Integer....]
BASELINE (ADD|FIX) SQL (HINT Select Statemtnt)BASELINE (ADD|FIX) SQL <HINT> <Select Statement>: Menyimpan rencana eksekusi yang dimodifikasi oleh hint sebagai rencana eksekusi tetap.
BASELINE LOAD: Memperbarui informasi baseline tertentu dalam tabel sistem ke memori agar informasi tersebut berlaku.
BASELINE LOAD_PLAN: Memperbarui informasi rencana eksekusi tertentu dalam tabel sistem ke memori agar informasi tersebut berlaku.
BASELINE LIST: Mencantumkan informasi tentang semua baseline.
BASELINE PERSIST: Menyimpan baseline tertentu ke disk.
BASELINE PERSIST_PLAN: Menyimpan rencana eksekusi tertentu ke disk.
BASELINE CLEAR: Menghapus baseline dari memori.
BASELINE CLEAR_PLAN: Menghapus rencana eksekusi dari memori.
BASELINE DELETE: Menghapus baseline dari disk.
BASELINE DELETE_PLAN: Menghapus rencana eksekusi dari disk.
Praktik terbaik untuk mengoptimalkan rencana eksekusi
Setelah data berubah atau mesin optimizer dari PolarDB-X ditingkatkan, rencana eksekusi yang lebih baik mungkin tersedia untuk pernyataan SQL yang sama. Selama evolusi rencana otomatis, SPM mencakup rencana eksekusi yang lebih baik yang secara otomatis ditemukan oleh Plan Enumerator dalam baseline pernyataan SQL tersebut. Anda juga dapat menggunakan instruksi SPM untuk mengoptimalkan rencana eksekusi.
Setelah pernyataan
EXPLAINdieksekusi, hasil yang dikembalikan menunjukkan bahwa rencana eksekusi dari pernyataan SQL menggunakan Hash Join, dan hanya rencana eksekusi ini yang termasuk dalam daftar baseline untuk pernyataan SQL tersebut.EXPLAIN SELECT * FROM lineitem JOIN part ON l_partkey=p_partkey WHERE p_name LIKE '%green%';Informasi berikut tentang rencana eksekusi dikembalikan:
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | LOGICAL PLAN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Gather(parallel=true) | | ParallelHashJoin(condition="l_partkey = p_partkey", type="inner") | | LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) | | LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)", parallel=true) | | HitCache:true | | | | | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 7 rows in set (0.06 sec)Eksekusi pernyataan SQL berikut untuk melihat baseline:
BASELINE LIST;Informasi berikut dikembalikan:
+-------------+--------------------------------------------------------------------------------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+ | BASELINE_ID | PARAMETERIZED_SQL | PLAN_ID | EXTERNALIZED_PLAN | FIXED | ACCEPTED | +-------------+--------------------------------------------------------------------------------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+ | -399023558 | SELECT * FROM lineitem JOIN part ON l_partkey = p_partkey WHERE p_name LIKE ? | -935671684 | Gather(parallel=true) ParallelHashJoin(condition="l_partkey = p_partkey", type="inner") LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)", parallel=true) | 0 | 1 | +-------------+--------------------------------------------------------------------------------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+ 1 row in set (0.02 sec)Jika performa pernyataan SQL dalam kondisi tertentu meningkat setelah Batched Key Access join (BKA join, juga dikenal sebagai lookup join) digunakan, solusi yang disarankan adalah memberikan hint kepada PolarDB-X untuk menghasilkan rencana eksekusi yang diharapkan. Sintaks hint yang digunakan dalam BKA join adalah
/*+TDDL:BKA_JOIN(lineitem, part)*/. EksekusiEXPLAIN [HINT] [SQL]untuk memeriksa apakah rencana eksekusi memenuhi harapan.EXPLAIN /*+TDDL:bka_join(lineitem, part)*/ SELECT * FROM lineitem JOIN part ON l_partkey=p_partkey WHERE p_name LIKE '%green%';Informasi berikut tentang rencana eksekusi dikembalikan:
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | LOGICAL PLAN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Gather(parallel=true) | | ParallelBKAJoin(condition="l_partkey = p_partkey", type="inner") | | LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) | | Gather(concurrent=true) | | LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)") | | HitCache:false | | | | | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 8 rows in set (0.14 sec)Setelah hint ditentukan, BKA join digunakan sebagai algoritma join. Baseline tidak berubah. Jika Anda ingin menggunakan rencana eksekusi sebelumnya setiap kali mengeksekusi pernyataan SQL ini, Anda harus menambahkan rencana eksekusi ke baseline.
Anda dapat menggunakan perintah
BASELINE ADDyang disediakan oleh pengelolaan rencana eksekusi untuk menambahkan rencana eksekusi untuk pernyataan SQL. Dalam hal ini, dua kelompok rencana eksekusi ada dalam baseline pernyataan SQL tersebut pada saat yang bersamaan. Plan Enumerator memilih rencana eksekusi dengan biaya lebih rendah.BASELINE ADD SQL /*+TDDL:bka_join(lineitem, part)*/ SELECT * FROM lineitem JOIN part ON l_partkey=p_partkey WHERE p_name LIKE '%green%';Informasi berikut dikembalikan:
+-------------+--------+ | BASELINE_ID | STATUS | +-------------+--------+ | -399023558 | OK | +-------------+--------+ 1 row in set (0.09 sec)Eksekusi pernyataan SQL berikut untuk melihat baseline:
BASELINE LIST;Informasi berikut dikembalikan:
+-------------+--------------------------------------------------------------------------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+ | BASELINE_ID | PARAMETERIZED_SQL | PLAN_ID | EXTERNALIZED_PLAN | FIXED | ACCEPTED | +-------------+--------------------------------------------------------------------------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+ | -399023558 | SELECT * FROM lineitem JOIN part ON l_partkey = p_partkey WHERE p_name LIKE ? | -1024543942 | Gather(parallel=true) ParallelBKAJoin(condition="l_partkey = p_partkey", type="inner") LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) Gather(concurrent=true) LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)") | 0 | 1 | | -399023558 | SELECT * FROM lineitem JOIN part ON l_partkey = p_partkey WHERE p_name LIKE ? | -935671684 | Gather(parallel=true) ParallelHashJoin(condition="l_partkey = p_partkey", type="inner") LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)", parallel=true) | 0 | 1 | +-------------+--------------------------------------------------------------------------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+ 2 rows in set (0.03 sec)Hasil sebelumnya dari perintah
BASELINE LISTmenunjukkan bahwa rencana eksekusi berbasis BKA_JOIN ditambahkan ke baseline pernyataan SQL tersebut. Jika Anda mengeksekusi pernyataanEXPLAINuntuk pernyataan SQL ini, Anda dapat menemukan bahwa saatp_name LIKE ?dalam pernyataan SQL berubah, PolarDB-X memilih rencana eksekusi yang berbeda.Untuk membuat PolarDB-X selalu menggunakan rencana eksekusi sebelumnya, Anda dapat menggunakan perintah
BASELINE FIXuntuk memaksa PolarDB-X menggunakan rencana eksekusi yang ditentukan.BASELINE FIX SQL /*+TDDL:bka_join(lineitem, part)*/ SELECT * FROM lineitem JOIN part ON l_partkey=p_partkey WHERE p_name LIKE '%green%';Informasi berikut dikembalikan:
+-------------+--------+ | BASELINE_ID | STATUS | +-------------+--------+ | -399023558 | OK | +-------------+--------+ 1 row in set (0.07 sec) mysql> baseline list\G *************************** 1. row *************************** BASELINE_ID: -399023558 PARAMETERIZED_SQL: SELECT * FROM lineitem JOIN part ON l_partkey = p_partkey WHERE p_name LIKE ? PLAN_ID: -1024543942 EXTERNALIZED_PLAN: Gather(parallel=true) ParallelBKAJoin(condition="l_partkey = p_partkey", type="inner") LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) Gather(concurrent=true) LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)") FIXED: 1 ACCEPTED: 1 *************************** 2. row *************************** BASELINE_ID: -399023558 PARAMETERIZED_SQL: SELECT * FROM lineitem JOIN part ON l_partkey = p_partkey WHERE p_name LIKE ? PLAN_ID: -935671684 EXTERNALIZED_PLAN: Gather(parallel=true) ParallelHashJoin(condition="l_partkey = p_partkey", type="inner") LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)", parallel=true) FIXED: 0 ACCEPTED: 1 2 rows in set (0.01 sec)Eksekusi pernyataan
EXPLAINlagi untuk melihat rencana eksekusi.EXPLAIN SELECT * FROM lineitem JOIN part ON l_partkey=p_partkey WHERE p_name LIKE '%green%';Informasi berikut tentang rencana eksekusi dikembalikan:
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | LOGICAL PLAN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Gather(parallel=true) | | ParallelBKAJoin(condition="l_partkey = p_partkey", type="inner") | | LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) | | Gather(concurrent=true) | | LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)") | | HitCache:true | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 8 rows in set (0.01 sec)Setelah perintah
BASELINE FIXdieksekusi, nilai dariFIXEDdalam rencana eksekusi yang berisi BKA join adalah 1. Dengan cara ini, bahkan jika tidak ada hint yang ditambahkan, pernyataanEXPLAINdieksekusi untuk pernyataan SQL ini berdasarkan rencana eksekusi tanpa memandang kondisi yang ditentukan.