AnalyticDB:Menyesuaikan urutan join secara manual

Pengenalan fungsi

AnalyticDB for MySQL mendukung kueri join kompleks dan secara default menyesuaikan urutan join secara otomatis. Namun, pernyataan kueri dan kondisi filter tabel dapat berubah. Jika karakteristik data kompleks, penyesuaian otomatis mungkin tidak memilih urutan join yang optimal. Urutan join yang suboptimal dapat menghasilkan himpunan hasil antara yang besar dan penggunaan memori tinggi, sehingga menurunkan performa kueri.

Metode penyesuaian

• Sebelum penyesuaian

  • Pernyataan kueri

    Pernyataan Query10 asli adalah sebagai berikut:

    Catatan

    • Topik ini menggunakan Query10 dari TPC-H sebagai contoh untuk menunjukkan metode dan efek penyesuaian urutan join secara manual. Untuk informasi lebih lanjut tentang TPC-H, lihat TPC-H.

    • Fitur penyesuaian urutan join otomatis AnalyticDB for MySQL diaktifkan secara default. Hint /*+ reorder_joins=false*/ digunakan dalam pernyataan kueri berikut untuk mensimulasikan skenario dengan urutan join yang suboptimal.

    SELECT   c_custkey,
             c_name,
             Sum(l_extendedprice * (1 - l_discount)) AS revenue,
             c_acctbal,
             n_name,
             c_address,
             c_phone,
             c_comment
    FROM     customer c,
             orders o,
             lineitem l,
             nation n
    WHERE    c_custkey = o_custkey
    AND      l_orderkey = o_orderkey
    AND      o_orderdate >= date '1993-10-01'
    AND      o_orderdate <  date '1993-10-01' + INTERVAL '3' month
    AND      l_returnflag = 'R'
    AND      c_nationkey = n_nationkey
    GROUP BY c_custkey,
             c_name,
             c_acctbal,
             c_phone,
             n_name,
             c_address,
             c_comment
    ORDER BY revenue DESC
    LIMIT    20;

  • Gabungkan Pesanan

    Berdasarkan pernyataan SQL di atas, urutan join-nya adalah:

    customer JOIN orders JOIN lineitem JOIN nation;

  • Hasil kueri

    Hasil antara setiap join dalam rencana eksekusi adalah sebagai berikut:

    Catatan

    Untuk informasi lebih lanjut tentang cara melihat rencana eksekusi, lihat Menganalisis kueri menggunakan rencana eksekusi.

    1. Tabel customer di-join dengan tabel orders. Join ini menghasilkan 57.069 baris ke himpunan hasil antara tmp1.

    2. Himpunan hasil antara tmp1 di-join dengan tabel lineitem menggunakan INNER (Hash) Join pada kondisi o_orderkey = l_orderkey. Operasi ini memakan waktu 4,88 ms (21,67%) dan menghasilkan 114.705 baris ke himpunan hasil antara baru tmp2.

    3. Himpunan hasil antara tmp2 di-join dengan tabel nation. Join ini menghasilkan 114.705 baris sebagai hasil akhir. Node join bertipe INNER (Hash). Kondisi join-nya adalah left.c_nationkey = right.n_nationkey. Operasi ini memakan waktu 5,71 ms (12,07%).

    Jumlah kumulatif baris yang dihasilkan oleh ketiga join tersebut adalah: 57.069 + 114.705 + 114.705 = 286.479.

• Setelah penyesuaian

  • Pernyataan kueri

    Anda dapat menambahkan hint /*+ reorder_joins=false*/ ke pernyataan SQL untuk menonaktifkan fitur penyesuaian urutan join otomatis AnalyticDB for MySQL, lalu menyesuaikan urutan join secara manual. Pernyataan SQL dengan urutan yang telah disesuaikan adalah sebagai berikut:

    /*reorder_joins=false*/
    SELECT   c_custkey,
             c_name,
             Sum(l_extendedprice * (1 - l_discount)) AS revenue,
             c_acctbal,
             n_name,
             c_address,
             c_phone,
             c_comment
    FROM     customer c,
             orders o,
             nation n,
             lineitem l
    WHERE    c_custkey = o_custkey
    AND      c_nationkey = n_nationkey
    AND      l_orderkey = o_orderkey
    AND      o_orderdate >= date '1993-10-01'
    AND      o_orderdate <  date '1993-10-01' + INTERVAL '3' month
    AND      l_returnflag = 'R'
    GROUP BY c_custkey,
             c_name,
             c_acctbal,
             c_phone,
             n_name,
             c_address,
             c_comment
    ORDER BY revenue DESC
    LIMIT    20;

  • Gabungkan Pesanan

    Berdasarkan pernyataan SQL di atas, urutan join-nya adalah:

    customer JOIN orders JOIN nation JOIN lineitem

  • Hasil kueri

    Hasil antara setiap join dalam rencana eksekusi adalah sebagai berikut:

    Catatan

    Untuk informasi lebih lanjut tentang cara melihat rencana eksekusi, lihat Menganalisis kueri menggunakan rencana eksekusi.

    1. Tabel customer di-join dengan tabel orders. Join ini menghasilkan 57.069 baris ke himpunan hasil antara tmp1. Node join bertipe INNER (Hash). Kondisi join-nya adalah c_custkey = o_custkey. Operasi ini memakan waktu 4,75 ms (36,7%).

    2. Himpunan hasil antara tmp1 di-join dengan tabel nation. Join ini menghasilkan 57.069 baris ke himpunan hasil antara tmp2. Node join bertipe INNER (Hash). Kondisi join-nya adalah left.c_nationkey = right.n_nationkey. Operasi ini memakan waktu 5,63 ms (25,61%).

    3. Himpunan hasil antara tmp2 di-join dengan tabel lineitem menggunakan join INNER (Hash) pada kondisi left.o_orderkey = right.l_orderkey. Join ini menghasilkan 114.705 baris sebagai hasil akhir dan memakan waktu 5,29 ms (9,81% dari total waktu).

    Jumlah kumulatif baris yang dihasilkan oleh ketiga join tersebut adalah: 57.069 + 57.069 + 114.705 = 228.843.

    Ini merupakan pengurangan sebesar 20% dalam jumlah baris output dibandingkan dengan 286.479 baris sebelum penyesuaian. Perbandingan ini menunjukkan bahwa urutan join yang berbeda dapat memengaruhi ukuran himpunan hasil antara. Jika Anda menemukan bahwa urutan join di AnalyticDB for MySQL tidak optimal, Anda dapat menyesuaikannya secara manual untuk meningkatkan performa kueri.