# requires: pymysql
"""对比 LIMIT offset 深分页和游标（keyset）分页。

运行：MYSQL_URL=mysql://user:pass@127.0.0.1:3306/db python paginate.py
没有设置 MYSQL_URL 时只打印提示并正常退出。会创建并删除临时表 demo_page_orders。
"""
import os
import sys
import time
from urllib.parse import urlparse

import pymysql

TABLE = "demo_page_orders"
ROWS = 100_000
PAGE = 20


def connect(url: str) -> pymysql.connections.Connection:
    u = urlparse(url)
    return pymysql.connect(host=u.hostname, port=u.port or 3306, user=u.username, password=u.password or "",
                           database=u.path.lstrip("/"), autocommit=True)


def seed(cur) -> None:
    cur.execute(f"DROP TABLE IF EXISTS {TABLE}")
    cur.execute(f"""CREATE TABLE {TABLE} (
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        created_at DATETIME NOT NULL,
        note VARCHAR(100) NOT NULL,
        KEY idx_user_time_id (user_id, created_at, id)
    ) ENGINE = InnoDB""")
    batch = []
    for i in range(ROWS):
        batch.append((1, f"note-{i}"))  # 全部属于 user 1，便于构造深分页
        if len(batch) == 5000:
            cur.executemany(f"INSERT INTO {TABLE} (user_id, created_at, note) VALUES (%s, '2026-01-01', %s)", batch)
            batch.clear()
    # 时间按 id 递增；让每 3 行共用一个时间，演示“时间相同按 id 排序”
    cur.execute(f"UPDATE {TABLE} SET created_at = '2026-01-01' + INTERVAL (id DIV 3) SECOND")
    cur.execute(f"ANALYZE TABLE {TABLE}")
    cur.fetchall()


def offset_page(cur, offset: int) -> list[tuple]:
    cur.execute(f"SELECT id, created_at, note FROM {TABLE} WHERE user_id = 1 "
                f"ORDER BY created_at DESC, id DESC LIMIT %s OFFSET %s", (PAGE, offset))
    return list(cur.fetchall())


def deferred_join_page(cur, offset: int) -> list[tuple]:
    # 延迟关联：先在覆盖索引上找出 id，再回表取这一页的整行
    cur.execute(f"""SELECT o.id, o.created_at, o.note FROM {TABLE} o
        JOIN (SELECT id FROM {TABLE} WHERE user_id = 1 ORDER BY created_at DESC, id DESC LIMIT %s OFFSET %s) t
        ON o.id = t.id ORDER BY o.created_at DESC, o.id DESC""", (PAGE, offset))
    return list(cur.fetchall())


def keyset_page(cur, after: tuple | None) -> list[tuple]:
    if after is None:
        cur.execute(f"SELECT id, created_at, note FROM {TABLE} WHERE user_id = 1 "
                    f"ORDER BY created_at DESC, id DESC LIMIT %s", (PAGE,))
    else:
        created_at, last_id = after
        cur.execute(f"SELECT id, created_at, note FROM {TABLE} WHERE user_id = 1 "
                    f"AND (created_at < %s OR (created_at = %s AND id < %s)) "
                    f"ORDER BY created_at DESC, id DESC LIMIT %s", (created_at, created_at, last_id, PAGE))
    return list(cur.fetchall())


def timed(fn, *args) -> tuple[float, list[tuple]]:
    t = time.perf_counter()
    rows = fn(*args)
    return (time.perf_counter() - t) * 1000, rows


def main() -> int:
    url = os.environ.get("MYSQL_URL")
    if not url:
        print("未设置 MYSQL_URL，跳过（示例：mysql://app:app@127.0.0.1:3306/learn）")
        return 0
    conn = connect(url)
    cur = conn.cursor()
    try:
        seed(cur)
        offset = ROWS - 1000  # 很深的一页
        ms1, a = timed(offset_page, cur, offset)
        ms2, b = timed(deferred_join_page, cur, offset)
        # 游标分页：拿到上一页最后一行 (created_at, id)，直接从那里继续
        prev = offset_page(cur, offset - PAGE)[-1]
        ms3, c = timed(keyset_page, cur, (prev[1], prev[0]))
        assert a == b == c, "三种写法结果应该一致"
        print(f"OFFSET {offset}: {ms1:.1f} ms；延迟关联: {ms2:.1f} ms；游标分页: {ms3:.1f} ms（耗时与机器有关，看相对差异）")
        # 游标分页逐页翻完不重不漏
        seen, after = 0, None
        while True:
            rows = keyset_page(cur, after)
            if not rows:
                break
            seen += len(rows)
            after = (rows[-1][1], rows[-1][0])
            if seen >= 2000:
                break
        assert seen == 2000
        print("游标分页连续翻页正确")
        return 0
    finally:
        cur.execute(f"DROP TABLE IF EXISTS {TABLE}")
        conn.close()


if __name__ == "__main__":
    sys.exit(main())