#!/usr/bin/env bash
# 盈尺（Surinch）· MySQL ORDER BY id LIMIT 走错主键索引 演示脚本 v1.0（2026-11-01）
# 仅限测试实例！脚本会 DROP/CREATE 库 olimit_lab，并写入 200 万行自造数据（约 300 MB）。
# 实测环境：MySQL 8.0.46，innodb_buffer_pool_size=1G，默认 optimizer_switch。
# 用法：MYSQL="mysql -uroot -p -h127.0.0.1" bash mysql-order-by-limit-wrong-index-lab-v1.0.sh
set -euo pipefail
MYSQL=${MYSQL:-mysql}
DB=olimit_lab
run() { $MYSQL "$@"; }

echo "== 1/4 造数据（200 万行；id 越大 created_at 越新）=="
run <<'SQL'
DROP DATABASE IF EXISTS olimit_lab;
CREATE DATABASE olimit_lab;
USE olimit_lab;
SET SESSION cte_max_recursion_depth = 10000;
CREATE TABLE seq (n INT PRIMARY KEY);
INSERT INTO seq WITH RECURSIVE s(n) AS (SELECT 0 UNION ALL SELECT n+1 FROM s WHERE n < 1999) SELECT n FROM s;
CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  shop_id INT NOT NULL,
  status TINYINT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL,
  note VARCHAR(100) NOT NULL,
  KEY idx_shop_created (shop_id, created_at)
) ENGINE=InnoDB;
-- shop 1：在营大店（约 29%）；shop 2..2001：普通店；shop 9001：只在前 5% 的 id 里有 2000 单
INSERT INTO orders (shop_id, status, amount, created_at, note)
SELECT CASE WHEN rn <= 100000 AND rn % 50 = 0 THEN 9001 WHEN rn % 10 < 3 THEN 1 ELSE 2 + (rn * 7919) % 2000 END,
       rn % 5, ROUND((rn % 9973) / 10, 2), TIMESTAMP('2025-01-01 00:00:00') + INTERVAL rn * 15 SECOND, REPEAT('x', 60)
FROM (SELECT a.n * 1000 + b.n + 1 AS rn FROM seq a JOIN seq b ON b.n < 1000 WHERE a.n < 2000) t ORDER BY rn;
-- shop 9002：已迁走的大店，4 万单全在前 10% 的 id 里
UPDATE orders SET shop_id = 9002 WHERE id <= 200000 AND id % 5 = 1;
ANALYZE TABLE orders;
SELECT shop_id, COUNT(*) AS cnt, MIN(id), MAX(id) FROM orders WHERE shop_id IN (1, 777, 9002) GROUP BY shop_id;
SQL

q() { # 标签 SQL：打印计划、耗时（3 次）、Handler 计数
  local label=$1 sql=$2
  local plan
  plan=$(run -N $DB -e "EXPLAIN FORMAT=TREE $sql" | grep -oE "using [A-Za-z_]+( \(reverse\))?" | head -1)
  for i in 1 2 3; do
    r=$(run -N $DB -e "FLUSH STATUS; SET @t=NOW(6); $sql; SELECT CONCAT('ms=',ROUND(TIMESTAMPDIFF(MICROSECOND,@t,NOW(6))/1000,1)); SHOW SESSION STATUS WHERE Variable_name IN ('Handler_read_prev','Handler_read_next');" | grep -E "^ms=|Handler" | tr '\n\t' '  ')
    echo "$label | $plan | run$i $r"
  done
}
S="SELECT id, amount, created_at FROM orders"
OFF="/*+ SET_VAR(optimizer_switch='prefer_ordering_index=off') */"

echo "== 2/4 默认计划 vs 关掉 prefer_ordering_index =="
for s in 9002 1 777; do
  q "shop$s default" "$S WHERE shop_id = $s ORDER BY id DESC LIMIT 20"
  q "shop$s ordering_off" "SELECT $OFF id, amount, created_at FROM orders WHERE shop_id = $s ORDER BY id DESC LIMIT 20"
done
for L in 1 2000; do q "shop9002 LIMIT $L" "$S WHERE shop_id = 9002 ORDER BY id DESC LIMIT $L"; done

echo "== 3/4 加 (shop_id, id) 索引：不加 hint / 加 INDEX hint =="
run $DB -e "ALTER TABLE orders ADD INDEX idx_shop_id (shop_id, id), ALGORITHM=INPLACE, LOCK=NONE; ANALYZE TABLE orders;" >/dev/null
for s in 9002 1 777; do
  q "shop$s newidx_nohint" "$S WHERE shop_id = $s ORDER BY id DESC LIMIT 20"
  q "shop$s newidx_hint" "SELECT /*+ INDEX(orders idx_shop_id) */ id, amount, created_at FROM orders WHERE shop_id = $s ORDER BY id DESC LIMIT 20"
done

echo "== 4/4 改写成 ORDER BY created_at DESC, id DESC（前提：created_at 随 id 单调）=="
for s in 9002 1 777; do q "shop$s rewrite" "$S WHERE shop_id = $s ORDER BY created_at DESC, id DESC LIMIT 20"; done
echo "完成。清理：DROP DATABASE olimit_lab;"