Pagination besar: OFFSET lawan keyset dalam CFML, diukur pada 200,000 baris

---

title: "Pagination besar: OFFSET lawan keyset dalam CFML, diukur pada 200,000 baris" meta_title: "keyset pagination CFML: ganti OFFSET yang lambat pada MySQL" meta_description: "Pagination OFFSET mengambil 280 ms pada halaman dalam manakala keyset 1 ms pada 200,000 baris MySQL. Kod CFML, indeks komposit dan bukti gelinciran offset." slug: "pagination-keyset-cfml" kategori: "Panduan" tarikh: "2026-10-23"

OFFSET memaksa MySQL mengira dan membuang baris sebelum ia memulangkan dua puluh baris yang anda mahu, jadi kosnya naik mengikut kedalaman halaman. Keyset pagination, juga dipanggil cursor pagination, tidak melompat ke kedudukan ke-150,000: ia menyimpan kunci baris terakhir yang pembaca nampak, kemudian bertanya "beri saya dua puluh baris selepas kunci ini", dan indeks membawa terus ke tempat itu.

Pada rig ini, Lucee 7.1.1.17-RC dengan MySQL 8.4.11 dan 200,000 baris, OFFSET 150000 mengambil 280 ms (median tujuh larian) manakala keyset 1 ms. Bezanya pelan larian, dan pelan itu boleh dibaca dengan EXPLAIN. Setiap angka di bawah ialah keluaran sebenar rig.

Jadual ujian: 200,000 baris dalam sepuluh saat

Jadual ini meniru senarai artikel: id kunci utama, tajuk, dan dicipta sebagai lajur susunan, dengan indeks idx_dicipta seperti pada jadual sebenar.

<cfscript>
dsn = "kedai";
queryExecute("DROP TABLE IF EXISTS angka", {}, {datasource: dsn});
queryExecute("CREATE TABLE angka (n INT PRIMARY KEY)", {}, {datasource: dsn});
queryExecute("INSERT INTO angka VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)", {}, {datasource: dsn});
queryExecute("DROP TABLE IF EXISTS artikel_pagination", {}, {datasource: dsn});
queryExecute("CREATE TABLE artikel_pagination (id INT AUTO_INCREMENT PRIMARY KEY,
    tajuk VARCHAR(120) NOT NULL, dicipta DATETIME NOT NULL, KEY idx_dicipta (dicipta))", {}, {datasource: dsn});
masa = getTickCount();
queryExecute("INSERT INTO artikel_pagination (tajuk, dicipta)
    SELECT CONCAT('Artikel ', a.n*10000 + b.n*1000 + c.n*100 + d.n*10 + e.n),
           DATE_SUB('2026-10-01 00:00:00', INTERVAL (a.n*10000 + b.n*1000 + c.n*100 + d.n*10 + e.n) MINUTE)
    FROM angka a, angka b, angka c, angka d, angka e", {}, {datasource: dsn});
queryExecute("INSERT INTO artikel_pagination (tajuk, dicipta)
    SELECT CONCAT('Artikel ', 100000 + a.n*10000 + b.n*1000 + c.n*100 + d.n*10 + e.n),
           DATE_SUB('2026-10-01 00:00:00', INTERVAL (100000 + a.n*10000 + b.n*1000 + c.n*100 + d.n*10 + e.n) MINUTE)
    FROM angka a, angka b, angka c, angka d, angka e", {}, {datasource: dsn});
n = queryExecute("SELECT COUNT(*) AS n FROM artikel_pagination", {}, {datasource: dsn}).n;
writeOutput("baris=" & n & " masa_sisip_ms=" & (getTickCount() - masa) & chr(10));
</cfscript>

baris=200000 masa_sisip_ms=10330

Kuncinya ialah silang lima jadual angka: satu INSERT ... SELECT menulis 100,000 baris tanpa gelung CFML. Kalau jadual anda baru 5,000 baris, bahagian seterusnya tidak terpakai.

Kenapa OFFSET lambat: MySQL membaca semua baris dahulu

Pelan larian untuk halaman pada OFFSET 150000:

type=ALL key= rows=199909 extra=Using filesort

type=ALL bermaksud imbasan penuh, rows=199909 bermaksud pengoptimum menjangka ia menyentuh hampir dua ratus ribu baris, dan Using filesort bermaksud ia menyusun hasil itu dalam fail sementara sebelum membuang 150,000 baris pertama. Indeks komposit (dicipta, id) tidak mengubah pelan ini: EXPLAIN masih melaporkan type=ALL dan Using filesort, dengan masa median 280 ms. Indeks sepadan ORDER BY membantu keyset, bukan OFFSET dalam, kerana kedudukan tetap dikira daripada awal.

Offset 199,000 lebih pantas daripada 150,000 kerana pelan menyusun seluruh jadual: kedudukan hampir tidak mengubah kos, dan variasi antara larian lebih besar daripada bezanya.

Cara mengukur angka ini

Setiap angka di atas ialah median tujuh larian selepas pemanasan, diukur dengan getTickCount() di sekeliling queryExecute(). Satu bacaan tunggal boleh tersasar antara 132 dan 458 ms pada jadual yang sama, jadi median lebih berguna. Nilai 0 bermaksud di bawah satu milisaat.

Keyset ikut id: satu perbandingan, satu baris kunci

Bentuk paling mudah keyset ialah kunci utama: pembaca menghantar id baris terakhir yang dia nampak.

SELECT id, tajuk, dicipta FROM artikel_pagination
WHERE id > :k ORDER BY id LIMIT 20

Pelan itu menjadi type=range key=PRIMARY, dan masa jatuh ke bawah satu milisaat. Tetapi bentuk ini hanya betul apabila susunan senarai ialah id; kebanyakan senarai artikel disusun ikut tarikh.

Keyset mesti ikut susunan sebenar, bukan id sahaja

Kalau senarai disusun ikut dicipta DESC, id DESC, kursor mesti membawa kedua-dua nilai. Menapis id > :kursor pada senarai ikut tarikh melompat ke baris yang salah, kerana id tidak sehala dengan dicipta, dan ia berjalan tanpa sebarang ralat.

<cfscript>
dsn = "kedai";
ix = queryExecute("SHOW INDEX FROM artikel_pagination WHERE Key_name = 'idx_dicipta_id'", {}, {datasource: dsn});
if (!ix.recordCount) queryExecute("ALTER TABLE artikel_pagination ADD KEY idx_dicipta_id (dicipta, id)", {}, {datasource: dsn});
anchor = queryExecute("SELECT dicipta, id FROM artikel_pagination ORDER BY dicipta DESC, id DESC LIMIT 1 OFFSET 99999", {}, {datasource: dsn});
q = queryExecute("SELECT id, tajuk, dicipta FROM artikel_pagination
    WHERE (dicipta < :d) OR (dicipta = :d AND id < :i)
    ORDER BY dicipta DESC, id DESC LIMIT 20",
    {d: {value: anchor.dicipta, cfsqltype: "cf_sql_timestamp"},
     i: {value: anchor.id, cfsqltype: "cf_sql_integer"}}, {datasource: dsn});
writeOutput("baris=" & q.recordCount & " id_pertama=" & q.id[1] & chr(10));
for (row in queryExecute("EXPLAIN SELECT id FROM artikel_pagination
        WHERE (dicipta < :d) OR (dicipta = :d AND id < :i) ORDER BY dicipta DESC, id DESC LIMIT 20",
        {d: {value: anchor.dicipta, cfsqltype: "cf_sql_timestamp"},
         i: {value: anchor.id, cfsqltype: "cf_sql_integer"}}, {datasource: dsn})) {
    writeOutput("type=" & row.type & " key=" & (isNull(row.key) ? "-" : row.key)
        & " extra=" & (isNull(row.extra) ? "-" : row.extra) & chr(10));
}
</cfscript>

Query ini mengambil median 1 ms dengan type=range pada idx_dicipta dan Using index condition; Backward index scan. Dua perkara penting: syarat (dicipta < :d) OR (dicipta = :d AND id < :i) memastikan baris dengan tarikh sama tidak hilang, dan id sebagai pemutus seri menghalang baris dilangkau atau diulang.

Nota cfsqltype: :d mesti cf_sql_timestamp, bukan cf_sql_varchar, kerana teks yang dibandingkan dengan lajur DATETIME tidak menggunakan indeks seperti dijangka. Cara menulis parameter queryExecute dengan betul ada dalam queryExecute dalam CFML.

Bukti offset menggelincir, bukan teori

Masalah kedua OFFSET bukan kelajuan tetapi ketepatan. Offset bermaksud "kedudukan ke-N dalam hasil sekarang". Apabila satu baris baharu masuk di atas senarai antara dua permintaan, setiap kedudukan beralih satu.

<cfscript>
dsn = "kedai";
function idsMula(s) {
    var a = [];
    for (r in queryExecute("SELECT id FROM artikel_pagination ORDER BY dicipta DESC, id DESC LIMIT 20 OFFSET " & s, {}, {datasource: dsn})) a.append(r.id);
    return a;
}
queryExecute("DELETE FROM artikel_pagination WHERE tajuk = 'Artikel baharu'", {}, {datasource: dsn});
idA = idsMula(99980);
idB = idsMula(100000);
queryExecute("INSERT INTO artikel_pagination (tajuk, dicipta) VALUES ('Artikel baharu', '2026-10-02 00:00:00')", {}, {datasource: dsn});
idB2 = idsMula(100000);
bertindih = 0;
for (x in idA) if (arrayFind(idB2, x)) bertindih++;
hilang = 0;
for (x in idB) if (!arrayFind(idB2, x)) hilang++;
q = queryExecute("SELECT id FROM artikel_pagination WHERE id > :x ORDER BY id LIMIT 20",
    {x: {value: idA[arrayLen(idA)], cfsqltype: "cf_sql_integer"}}, {datasource: dsn});
sama = 0;
for (r in q) if (arrayFind(idA, r.id)) sama++;
writeOutput("A_terakhir=" & idA[arrayLen(idA)] & " B_sebelum=" & idB[1] & " B_selepas=" & idB2[1]
    & " bertindih=" & bertindih & " hilang=" & hilang & " keyset_bertindih=" & sama & chr(10));
queryExecute("DELETE FROM artikel_pagination WHERE tajuk = 'Artikel baharu'", {}, {datasource: dsn});
</cfscript>

A_terakhir=90910 B_sebelum=140161 B_selepas=90910 bertindih=1 hilang=1 keyset_bertindih=0

Selepas satu sisipan, halaman B bermula dengan baris yang sudah ada di hujung halaman A: satu baris berulang, satu baris beralih keluar. Keyset tidak terjejas kerana ia bertanya "selepas kunci ini", bukan "pada kedudukan ini".

Jumlah halaman dan harga COUNT(*)

"Halaman 3 daripada 250" memerlukan COUNT(*). Pada 200,000 baris, kiraan penuh mengambil median 19 ms (18 hingga 28 ms): kecil berbanding 280 ms, tetapi dibayar pada setiap halaman dan membesar bersama jadual. Tiga pilihan jujur: cache kiraan itu, tunjukkan anggaran tanpa nombor halaman, atau simpan COUNT(*) untuk panel admin jarang dibuka. Halaman pertama yang mahal patut dicache, contohnya dalam cache query dengan cachedwithin.

Bila OFFSET masih pilihan yang wajar

Pada jadual 10,000 baris, offset 1,000 dan 9,000 kedua-duanya median 4 ms, dan itu kos menyusun seluruh jadual.

Untuk senarai dalam memori, query of queries mempunyai kosnya sendiri yang patut diukur dahulu.

Reka bentuk API: kursor yang telus

Kursor terbaik ialah rentetan legap yang klien tidak perlu faham: ia menghalang klien membina kursor sendiri. Satu bentuk berkesan ialah base64 daripada dicipta|id, dibina dengan toBase64(dateTimeFormat(dicipta, "yyyy-mm-dd HH:nn:ss") & "|" & id). Halaman berikutnya membacanya dengan toString(toBinary(kursor)), memecah pada |, dan menghantar tarikh sebagai cf_sql_timestamp. Kedua-dua fungsi ada dalam Lucee tanpa sambungan. Kursor ini bukan penyulitan: jangan letak rahsia, dan sahkan bentuknya sebelum masuk ke SQL.

Kesilapan yang mematahkan keyset

• ORDER BY tanpa lajur unik: dua baris berkongsi dicipta, satu dilangkau.
• Kursor hanya id untuk senarai ikut tarikh: berjalan tanpa ralat, baris salah.
• > dan bukan < pada susunan menurun: baris berulang pada setiap sempadan halaman.
• Tarikh dihantar sebagai teks: indeks tidak digunakan seperti dijangka.
• Mencampur OFFSET dengan kursor: kos kedudukan masih dibayar.
• Tiada indeks sepadan ORDER BY: (dicipta, id) memerlukan indeks (dicipta, id), bukan dua indeks berasingan.
• OFFSET dalam UPDATE atau DELETE bertopeng LIMIT menyentuh baris yang tidak dijangka.

Sebelum dan selepas perubahan, mulakan dengan profil prestasi dalam CFML: median tujuh larian lebih berguna daripada satu bacaan.

Soalan Lazim (FAQ)

Adakah keyset sesuai untuk semua senarai? Tidak. Ia sesuai apabila pembaca bergerak ke hadapan sahaja. Laporan admin yang perlu melompat ke halaman 7 masih munasabah dengan OFFSET.

Berapa besar jadual sebelum OFFSET patut ditukar? Pada rig ini, 10,000 baris memberi 4 ms pada offset 9,000, manakala 200,000 baris memberi 172 hingga 280 ms pada halaman dalam. Tukar apabila masa melebihi bajet, bukan pada satu nombor ajaib.

Adakah menambah indeks cukup untuk mempercepat OFFSET? Tidak. Selepas indeks komposit (dicipta, id) ditambah, OFFSET 150000 masih type=ALL dengan Using filesort dan median 280 ms.

Kenapa kursor saya memulangkan baris yang sama dua kali? Biasanya ORDER BY tiada lajur unik, atau perbandingan menggunakan >= dan bukan >.

Bolehkah saya tunjuk jumlah halaman tanpa COUNT(*)? Boleh: cache kiraan itu, tunjukkan anggaran, atau buang nombor halaman. COUNT(*) mengambil median 19 ms pada 200,000 baris, jadi ia kos berulang, bukan punca utama kelembapan.

Sumber

• MySQL 8.4, SELECT dengan LIMIT dan OFFSET: https://dev.mysql.com/doc/refman/8.4/en/select.html
• MySQL 8.4, output EXPLAIN: https://dev.mysql.com/doc/refman/8.4/en/explain-output.html
• Lucee, queryExecute: https://docs.lucee.org/reference/functions/queryexecute.html
• Lucee, toBase64: https://docs.lucee.org/reference/functions/tobase64.html