#!/usr/bin/env bash
# MySQL 深分页 vs 延迟关联 vs 游标（keyset）分页 演示 v1.0（surinch.com · 盈尺）
# 只在测试实例上跑！会新建并重建库 page_lab（自造数据：orders 100 万行，约 10 秒）。
# 测试版本：MySQL 8.0.46。用法：MYSQL="mysql -uroot -p -h127.0.0.1" ./mysql-deep-pagination-demo-v1.0.sh
set -euo pipefail
MYSQL=${MYSQL:-mysql}
DB=page_lab
$MYSQL -e "DROP DATABASE IF EXISTS $DB; CREATE DATABASE $DB DEFAULT CHARACTER SET utf8mb4;"
$MYSQL $DB <<'SQL'
CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL, status TINYINT NOT NULL, amount DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL, note VARCHAR(200) NOT NULL,
  KEY idx_status_created (status, created_at)) ENGINE=InnoDB;
SET SESSION cte_max_recursion_depth = 2000000;
-- status=1 约 60%；created_at 落在约 3.5 天里，同一秒平均 3–4 行（故意制造排序列重复）
INSERT INTO orders (user_id, status, amount, created_at, note)
WITH RECURSIVE seq(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM seq WHERE n < 1000000)
SELECT FLOOR(1 + RAND(n) * 200000),
       CASE WHEN RAND(n*7) < 0.6 THEN 1 ELSE 2 + FLOOR(RAND(n*13) * 3) END,
       ROUND(RAND(n*3) * 1000, 2),
       TIMESTAMP('2026-01-01') + INTERVAL FLOOR(RAND(n*11) * 300000) SECOND,
       REPEAT('x', 120)
FROM seq;
ANALYZE TABLE orders;
DROP PROCEDURE IF EXISTS walk_ts_only;
DROP PROCEDURE IF EXISTS walk_ts_id;
DELIMITER //
CREATE PROCEDURE walk_ts_only(IN page INT)
BEGIN
  DECLARE last_ts DATETIME DEFAULT '1970-01-01';
  DECLARE got INT DEFAULT 1; DECLARE total INT DEFAULT 0; DECLARE pages INT DEFAULT 0;
  WHILE got > 0 DO
    SELECT COUNT(*), MAX(created_at) INTO got, last_ts FROM
      (SELECT created_at FROM orders WHERE status=1 AND created_at > last_ts ORDER BY created_at LIMIT page) p;
    SET total = total + got, pages = pages + IF(got>0,1,0);
  END WHILE;
  SELECT 'created_at only' AS cursor_cols, page AS page_size, pages, total AS rows_fetched;
END//
CREATE PROCEDURE walk_ts_id(IN page INT)
BEGIN
  DECLARE last_ts DATETIME DEFAULT '1970-01-01'; DECLARE last_id BIGINT DEFAULT 0;
  DECLARE got INT DEFAULT 1; DECLARE total INT DEFAULT 0; DECLARE pages INT DEFAULT 0;
  WHILE got > 0 DO
    SELECT COUNT(*) INTO got FROM (SELECT id FROM orders WHERE status=1 AND (created_at > last_ts OR (created_at = last_ts AND id > last_id)) ORDER BY created_at, id LIMIT page) p;
    IF got > 0 THEN
      SELECT created_at, id INTO last_ts, last_id FROM (SELECT created_at, id FROM orders WHERE status=1 AND (created_at > last_ts OR (created_at = last_ts AND id > last_id)) ORDER BY created_at, id LIMIT page) p ORDER BY created_at DESC, id DESC LIMIT 1;
    END IF;
    SET total = total + got, pages = pages + IF(got>0,1,0);
  END WHILE;
  SELECT '(created_at, id)' AS cursor_cols, page AS page_size, pages, total AS rows_fetched;
END//
DELIMITER ;
SQL
q() { echo; echo "== $1"; $MYSQL $DB -e "$2"; }
q "0 数据规模" "SELECT COUNT(*) total, SUM(status=1) status1, COUNT(DISTINCT created_at) distinct_ts FROM orders"
q "1 主键顺序 LIMIT 900000,20" "EXPLAIN ANALYZE SELECT * FROM orders ORDER BY id LIMIT 900000,20"
q "2 二级索引 status=1 ORDER BY created_at LIMIT 500000,20（每跳过一行都回表）" "EXPLAIN ANALYZE SELECT * FROM orders WHERE status=1 ORDER BY created_at LIMIT 500000,20"
q "3 延迟关联：先在索引里翻页取 id，再回表 20 行" "EXPLAIN ANALYZE SELECT o.* FROM orders o JOIN (SELECT id FROM orders WHERE status=1 ORDER BY created_at, id LIMIT 500000,20) t ON t.id=o.id ORDER BY o.created_at, o.id"
CUR=$($MYSQL -N $DB -e "SELECT CONCAT(created_at,'|',id) FROM orders WHERE status=1 ORDER BY created_at, id LIMIT 499999,1")
TS=${CUR%|*}; ID=${CUR#*|}; echo; echo "游标 = 第 500000 行的 (created_at, id) = ($TS, $ID)"
q "4 游标：行构造器写法 (created_at, id) > (...)（8.0.46 实测不走范围扫描）" "EXPLAIN ANALYZE SELECT * FROM orders WHERE status=1 AND (created_at, id) > ('$TS', $ID) ORDER BY created_at, id LIMIT 20"
q "5 游标：展开写法 created_at > ? OR (created_at = ? AND id > ?)" "EXPLAIN ANALYZE SELECT * FROM orders WHERE status=1 AND (created_at > '$TS' OR (created_at = '$TS' AND id > $ID)) ORDER BY created_at, id LIMIT 20"
q "6 游标只用 created_at 翻完全部 status=1：会漏行" "CALL walk_ts_only(100)"
q "7 游标用 (created_at, id) 翻完全部 status=1" "CALL walk_ts_id(100)"
echo; echo "完成。清理：$MYSQL -e 'DROP DATABASE page_lab'"