sql:SQL Pagination and Limiting Results using mysql 9.0
CREATE DATABASE IF NOT EXISTS JewelryAnalytics DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE JewelryAnalytics;
SET SESSION time_zone = '+00:00';
SET SESSION cte_max_recursion_depth = 100000;
SET SESSION group_concat_max_len = 1000000;
SET SESSION optimizer_switch = 'index_condition_pushdown=on,mrr=on,derived_merge=on,skip_scan=on';
SET SESSION transaction_isolation = 'REPEATABLE-READ';
SET SESSION sql_safe_updates = 0;
SET SESSION innodb_lock_wait_timeout = 50;
/* ==========================================================================================
MySQL 9.0 · SQL 分页与结果限制（OFFSET/LIMIT vs 键集分页）· 国际化珠宝集团全链路示例
------------------------------------------------------------------------------------------
主题：Compare OFFSET/FETCH and keyset pagination —— 让大结果集稳定、快速、友好。
行业：国际化珠宝集团，覆盖 OA / ERP / CRM / HR / MES / SCM 六大域，统一落在交易中枢
gl_trade_order（全球贸易订单事实表）。
额外约束（用户要求）：
· 万亿级高并发场景（用 1,000,000 行实测 + 工程化讨论外推）
· 国际时区时间差（订单统一存 UTC，按 UTC 排序/分页，按本地时区展示）
· 国际各种货币结算（原始币种金额 + 归一化 USD 金额 + 汇率快照日）
------------------------------------------------------------------------------------------
本脚本与「SQL Server 2025 系列」同规格：可直接运行、自带造数、自带度量与自检。
全部 0 错误、幂等、可反复执行（已建表则跳过造数，只跑实验）。
========================================================================================== */
/* ==========================================================================================
★ 0.0 如何在 MySQL Workbench / 其他 GUI 里跑本脚本（必读，否则会误报「错误」）
------------------------------------------------------------------------------------------
本脚本是**整份顺序执行**的设计，强烈建议二选一：
方式①（最稳，推荐）：用命令行 mysql.exe 整份跑，输出进文件，完全不受 GUI 客户端限制：
"C:\Program Files\MySQL\MySQL Server 9.0\bin\mysql.exe" ^
--defaults-extra-file=_my.demo.cnf --default-character-set=utf8mb4 -t ^
< MySQL9_Pagination_JewelryDemo.sql > 验证输出.txt
其中 _my.demo.cnf 内含 [client] user=root / password=你的密码 / host=127.0.0.1 / port=3306。
方式②（仍用 Workbench）：请先改 3 个偏好，否则会被客户端限制打断（这不是 SQL 错误）：
Edit → Preferences → SQL Editor → Query Results
· 「Limit Rows」：取消勾选（GUI 会偷偷给每条 SELECT 末尾追加 LIMIT 0,1000，
叠加本脚本多行注释 SELECT 会触发 "Operation canceled / Error Code 0"）
· 「Maximum number of result sets」：调大（如 1000），否则跑到一半弹
"Maximum result count reached"，点 Yes 会让连接失步、后续全失败
Edit → Preferences → SQL Editor
· 「DBMS connection read timeout interval」：设为 600（默认 30 秒）。
本脚本个别深翻页 / 全表 COUNT 实验在弱机(128M buffer pool)上可能 >30 秒，
不调高会报 "Error 2013 Lost connection" —— 这是客户端超时，服务器没挂。
一句话：要么 mysql.exe 整份跑，要么 Workbench 里关掉 Limit Rows + 调高结果集上限 + 超时设 600。
========================================================================================== */
/* ==========================================================================================
§0.1 MySQL 9.0 与 SQL Server 在「分页」上的关键差异（先看这 6 条，后面才不会踩坑）
------------------------------------------------------------------------------------------
1. MySQL **没有** SQL 标准的 `OFFSET ... FETCH NEXT ... ROWS` 语法；等价写法是
`LIMIT <行数> OFFSET <偏移>` 或 `LIMIT <偏移>, <行数>`。
本脚本统一用 `LIMIT 20 OFFSET 500000` 这种清晰写法。
2. 行值比较 `(a,b) < (x,y)` 在 MySQL 9.0 完全支持 —— 这是**键集分页（seek 法）**能写得干净的关键。
SQL Server 用 `(a,b) < (x,y)` 也支持，但很多人不知道；这里统一演示。
3. 时区：MySQL 的 `TIMESTAMP` 内部**永远存 UTC**，按会话 `time_zone` 自动转换显示。
⚠ 本机 `mysql.time_zone_name` 表为空（命名时区未加载），故 `CONVERT_TZ(ts,'UTC','Asia/Shanghai')`
一律返回 NULL —— 不能用。改用 `TIMESTAMPADD(MINUTE, 固定偏移分钟, ts)` 硬编码各时区偏移
（上海 +480 / 纽约冬令时 -300 / 巴黎 +60 等）。SQL Server 用 `AT TIME ZONE`。
→ 分页的「游标」必须建立在 UTC 上，否则跨时区排序会错。
4. 货币：MySQL 没有 `MONEY` 类型，用 `DECIMAL(18,2)`；多币种要自己存「原始金额 + 汇率 + 归一化金额」。
按金额分页**必须**按归一化金额（如 USD），绝不可按原始金额跨币种排。
5. `LIMIT` 大偏移的深翻页在 MySQL 与 SQL Server 一样烂：都要先读再丢弃偏移行。
MySQL 额外有「回表不可见」特性（见上一篇 Select 专题），所以本脚本用 `Innodb_rows_read`
+ 耗时双指标来量化。
6. 计数总页数：`COUNT(*)` 在万亿级是昂贵操作；MySQL 没有 `APPROXIMATE COUNT` 的官方语法
（`EXPLAIN` 的 `rows` 是估算），工程上靠「分区估算 + 分片汇总 + 不精确页码」解决。
========================================================================================== */
/* ==========================================================================================
§0.2 反模式速查表（本机实测倍率；行数倍数按 Innodb_rows_read 口径）
─────┬────────────────────────────────────────┬──────────────────────────────┬──────────────
编号 │ 反模式 │ 修正写法 │ 本机实测
─────┼────────────────────────────────────────┼──────────────────────────────┼──────────────
OF │ OFFSET 500000 深翻页（读后丢弃） │ 键集分页 (ts,id)<(cursor) │ 引擎读行 ≈ 数十倍
DF │ 深翻页直接 SELECT * 回表 │ 延迟关联（先取 id 再回表） │ 回表行数大幅下降
ST │ OFFSET 翻页时并发插入 → 漏看/重复 │ 键集游标（追加型数据零漂移） │ 稳定性 100%
TZ │ 按「本地时间」排序/分页 │ 按 UTC 排序、本地仅展示 │ 排序正确性 100%
CU │ 按原始金额跨币种分页 │ 按归一化 USD 金额分页 │ 排序正确性 100%
JMP│ 每页都 COUNT(*) 算总页数 │ 首几页 OFFSET + 深页键集混合 │ COUNT 成本省掉
========================================================================================== */
/* ==========================================================================================
§0.3 度量框架（原生 MySQL 计数器，SESSION 级取差值）
------------------------------------------------------------------------------------------
思路：在每条被测 SQL 前后各抓一次 `performance_schema.session_status` 的关键计数器，
取差值即「这条语句让引擎多读了多少行 / 多用多少毫秒」。
关键计数器：
· INNODB_ROWS_READ —— InnoDB 从聚簇/二级索引读出的总行数（含回表），本脚本主指标
· HANDLER_READ_RND_NEXT —— 全表扫描/未用索引的逐行读计数（深翻页会暴涨）
度量结果落到 demo_metric；对照结论落到 demo_result；断言落到 demo_verify。
所有「只看不取」的查询用 BLACKHOLE 表 demo_sink 吸收，避免几百 MB 输出。
========================================================================================== */
DROP TABLE IF EXISTS demo_sink;
CREATE TABLE demo_sink(line VARCHAR(4000)) ENGINE=BLACKHOLE;
DROP TABLE IF EXISTS demo_baseline;
CREATE TABLE demo_baseline(
h_rnd_next BIGINT UNSIGNED DEFAULT 0,
innodb_rows BIGINT UNSIGNED DEFAULT 0,
t_ms BIGINT UNSIGNED DEFAULT 0
);
DROP TABLE IF EXISTS demo_metric;
CREATE TABLE demo_metric(
tag VARCHAR(40) NOT NULL PRIMARY KEY,
h_rnd_next BIGINT UNSIGNED DEFAULT 0,
innodb_rows BIGINT UNSIGNED DEFAULT 0,
ms BIGINT UNSIGNED DEFAULT 0
);
DROP TABLE IF EXISTS demo_result;
CREATE TABLE demo_result(
seq INT, label VARCHAR(80), chapter VARCHAR(8),
bad_rows BIGINT, good_rows BIGINT, rows_ratio DECIMAL(16,2),
bad_rnd BIGINT, good_rnd BIGINT, rnd_ratio DECIMAL(16,2),
time_ratio DECIMAL(16,2),
passed TINYINT, note VARCHAR(260)
);
DROP TABLE IF EXISTS demo_verify;
CREATE TABLE demo_verify(
check_name VARCHAR(150) PRIMARY KEY,
passed TINYINT,
detail VARCHAR(400)
);
DELIMITER //
DROP PROCEDURE IF EXISTS demo_reset//
CREATE PROCEDURE demo_reset()
BEGIN
DELETE FROM demo_result;
DELETE FROM demo_verify;
DELETE FROM demo_metric;
END//
DROP PROCEDURE IF EXISTS demo_begin//
CREATE PROCEDURE demo_begin(p_tag VARCHAR(40))
BEGIN
DELETE FROM demo_baseline;
INSERT INTO demo_baseline(h_rnd_next, innodb_rows, t_ms)
SELECT
CAST((SELECT VARIABLE_VALUE FROM performance_schema.session_status WHERE VARIABLE_NAME='HANDLER_READ_RND_NEXT') AS UNSIGNED),
CAST((SELECT VARIABLE_VALUE FROM performance_schema.session_status WHERE VARIABLE_NAME='INNODB_ROWS_READ') AS UNSIGNED),
CAST(UNIX_TIMESTAMP(NOW(6))*1000 AS UNSIGNED);
END//
DROP PROCEDURE IF EXISTS demo_end//
CREATE PROCEDURE demo_end(p_tag VARCHAR(40))
BEGIN
INSERT INTO demo_metric(tag, h_rnd_next, innodb_rows, ms)
SELECT p_tag,
CAST((SELECT VARIABLE_VALUE FROM performance_schema.session_status WHERE VARIABLE_NAME='HANDLER_READ_RND_NEXT') AS UNSIGNED) - h_rnd_next,
CAST((SELECT VARIABLE_VALUE FROM performance_schema.session_status WHERE VARIABLE_NAME='INNODB_ROWS_READ') AS UNSIGNED) - innodb_rows,
CAST(UNIX_TIMESTAMP(NOW(6))*1000 AS UNSIGNED) - t_ms
FROM demo_baseline;
END//
/* p_rule: 'R' 引擎读行倍数断言（bad >= thr*good）；'T' 耗时倍数；'I' 反向（good>=thr*bad）；
'L' 坏写法读行更多（bad>good 即键集胜）；'N' 不断言 */
DROP PROCEDURE IF EXISTS demo_report//
CREATE PROCEDURE demo_report(
p_seq INT, p_label VARCHAR(80), p_chapter VARCHAR(8),
p_bad_tag VARCHAR(40), p_good_tag VARCHAR(40),
p_rule CHAR(1), p_threshold DECIMAL(10,2)
)
BEGIN
DECLARE v_bad_innodb, v_good_innodb, v_bad_rnd, v_good_rnd, v_bad_ms, v_good_ms BIGINT;
DECLARE v_rows_ratio, v_rnd_ratio, v_time_ratio DECIMAL(16,2);
DECLARE v_passed TINYINT DEFAULT 0;
SELECT innodb_rows, h_rnd_next, ms INTO v_bad_innodb, v_bad_rnd, v_bad_ms FROM demo_metric WHERE tag=p_bad_tag;
SELECT innodb_rows, h_rnd_next, ms INTO v_good_innodb, v_good_rnd, v_good_ms FROM demo_metric WHERE tag=p_good_tag;
SET v_rows_ratio = CASE WHEN v_good_innodb>0 THEN v_bad_innodb/v_good_innodb ELSE NULL END;
SET v_rnd_ratio = CASE WHEN v_good_rnd>0 THEN v_bad_rnd/v_good_rnd ELSE NULL END;
SET v_time_ratio = CASE WHEN v_good_ms>0 THEN v_bad_ms/v_good_ms ELSE NULL END;
IF p_rule='R' THEN
SET v_passed = IF(v_bad_innodb >= p_threshold * v_good_innodb, 1, 0);
ELSEIF p_rule='T' THEN
SET v_passed = IF(v_bad_ms >= p_threshold * v_good_ms, 1, 0);
ELSEIF p_rule='I' THEN
SET v_passed = IF(v_good_innodb >= p_threshold * v_bad_innodb, 1, 0);
ELSEIF p_rule='L' THEN
SET v_passed = IF(v_bad_innodb > v_good_innodb, 1, 0);
ELSE
SET v_passed = 1;
END IF;
INSERT INTO demo_result(seq,label,chapter,bad_rows,good_rows,rows_ratio,bad_rnd,good_rnd,rnd_ratio,time_ratio,passed,note)
VALUES(p_seq,p_label,p_chapter,v_bad_innodb,v_good_innodb,v_rows_ratio,v_bad_rnd,v_good_rnd,v_rnd_ratio,v_time_ratio,v_passed,
CONCAT('引擎读行 ',v_bad_innodb,'→',v_good_innodb,' (×',COALESCE(v_rows_ratio,0),
'); 回表 ',v_bad_rnd,'→',v_good_rnd,' (×',COALESCE(v_rnd_ratio,0),
'); 耗时 ',v_bad_ms,'→',v_good_ms,'ms'));
END//
DROP PROCEDURE IF EXISTS demo_v//
CREATE PROCEDURE demo_v(p_name VARCHAR(150), p_ok TINYINT, p_detail VARCHAR(400))
BEGIN
INSERT INTO demo_verify(check_name,passed,detail)
VALUES(p_name, IFNULL(p_ok,0), IFNULL(p_detail,'（无明细）'))
ON DUPLICATE KEY UPDATE passed=VALUES(passed), detail=VALUES(detail);
END//
/* 错误探针：预期某条 SQL 会报错，捕获 errno 并登记；用于证明「这种写法确实会被拦」 */
DROP PROCEDURE IF EXISTS demo_err_probe//
CREATE PROCEDURE demo_err_probe(p_label VARCHAR(60), p_sql TEXT)
BEGIN
DECLARE v_no INT DEFAULT 0;
DECLARE v_msg TEXT DEFAULT '';
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1 v_no = MYSQL_ERRNO, v_msg = MESSAGE_TEXT;
END;
SET @sql = p_sql;
PREPARE stmt FROM @sql;
IF v_no = 0 THEN
EXECUTE stmt;
END IF;
IF v_no = 0 THEN
DEALLOCATE PREPARE stmt;
END IF;
IF v_no = 0 THEN
SELECT CONCAT('[',p_label,'] 预期会报错，但实际没有报错') AS `-- 错误探针 --`;
CALL demo_v(CONCAT('错误探针·',p_label), 0, '预期会报错但实际上未报错');
ELSE
SELECT CONCAT('[',p_label,'] ★错误 ',v_no,' : ',LEFT(v_msg,200)) AS `-- 错误探针 --`;
CALL demo_v(CONCAT('错误探针·',p_label), 1, CONCAT('errno=',v_no,' ',LEFT(v_msg,200)));
END IF;
END//
DELIMITER ;
/* ==========================================================================================
§00.2 实验台：国际化珠宝集团六域模型 + 造数（1,000,000 行交易中枢 + 配套维表/业务表）
------------------------------------------------------------------------------------------
中枢事实表 gl_trade_order（ERP 销售 + CRM 客户 + SCM 履约 + 结算归一化金额）串起所有域。
维度/业务表：dim_country(时区) / dim_currency(汇率) / dim_customer(CRM) /
hr_employee(HR) / mes_work_order(MES) / oa_approval(OA) / scm_shipment(SCM)。
时间统一存 UTC；金额存「原始币种 + 汇率 + 归一化 USD + 汇率快照日(UTC date)」。
========================================================================================== */
SET SESSION cte_max_recursion_depth = 100000;
SET SESSION group_concat_max_len = 1000000;
SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
DELIMITER //
DROP PROCEDURE IF EXISTS lab_build//
CREATE PROCEDURE lab_build()
BEGIN
DECLARE v_cnt BIGINT DEFAULT 0;
DECLARE v_exists INT DEFAULT 0;
SELECT COUNT(*) INTO v_exists
FROM information_schema.tables
WHERE table_schema = 'JewelryAnalytics' AND table_name = 'gl_trade_order';
IF v_exists = 1 THEN
SELECT COUNT(*) INTO v_cnt FROM gl_trade_order;
END IF;
IF v_exists = 1 AND v_cnt = 1000000 THEN
SELECT 'lab: gl_trade_order 已存在 1,000,000 行，跳过造数（如需重建请先 DROP TABLE）。' AS `-- lab --`;
ELSE
SELECT 'lab: 开始建表并造数（首次约 3-5 分钟）……' AS `-- lab --`;
/* ---- 维度：国家（含 IANA 时区名） ---- */
DROP TABLE IF EXISTS dim_country;
CREATE TABLE dim_country(
country_code CHAR(2) PRIMARY KEY,
country_name VARCHAR(40) NOT NULL,
tz_name VARCHAR(40) NOT NULL,
region VARCHAR(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO dim_country(country_code,country_name,tz_name,region) VALUES
('CN','中国','Asia/Shanghai','APAC'),('US','美国','America/New_York','AMER'),
('JP','日本','Asia/Tokyo','APAC'),('HK','中国香港','Asia/Hong_Kong','APAC'),
('FR','法国','Europe/Paris','EMEA'),('DE','德国','Europe/Berlin','EMEA'),
('GB','英国','Europe/London','EMEA'),('SG','新加坡','Asia/Singapore','APAC'),
('AE','阿联酋','Asia/Dubai','EMEA'),('IN','印度','Asia/Kolkata','APAC'),
('BR','巴西','America/Sao_Paulo','AMER'),('AU','澳大利亚','Australia/Sydney','APAC');
/* ---- 维度：货币（静态演示汇率：1 单位本币 = ? USD） ---- */
DROP TABLE IF EXISTS dim_currency;
CREATE TABLE dim_currency(
currency_code CHAR(3) PRIMARY KEY,
currency_name VARCHAR(40) NOT NULL,
to_usd DECIMAL(18,8) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO dim_currency(currency_code,currency_name,to_usd) VALUES
('CNY','人民币',0.1400),('USD','美元',1.0000),('JPY','日元',0.0067),
('HKD','港元',0.1280),('EUR','欧元',1.0800),('GBP','英镑',1.2700),
('SGD','新加坡元',0.7400),('AED','迪拉姆',0.2700),('INR','卢比',0.0120),
('BRL','雷亚尔',0.1800),('AUD','澳元',0.6600);
/* ---- CRM：客户 ---- */
DROP TABLE IF EXISTS dim_customer;
CREATE TABLE dim_customer(
customer_id INT PRIMARY KEY,
customer_name VARCHAR(80) NOT NULL,
country_code CHAR(2) NOT NULL,
segment VARCHAR(20),
created_ts TIMESTAMP NOT NULL DEFAULT '2024-01-01 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO dim_customer(customer_id,customer_name,country_code,segment,created_ts)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<5000)
SELECT n,
CONCAT(ELT(1+n%6,'VipClient','Retail','Wholesale','Member','Corp','Boutique'), LPAD(n,5,'0')),
ELT(1+n%12,'CN','US','JP','HK','FR','DE','GB','SG','AE','IN','BR','AU'),
ELT(1+n%3,'VIP','Retail','Wholesale'),
TIMESTAMPADD(DAY, n%1000, '2024-01-01 00:00:00')
FROM s;
/* ---- HR：员工 ---- */
DROP TABLE IF EXISTS hr_employee;
CREATE TABLE hr_employee(
emp_id INT PRIMARY KEY,
emp_name VARCHAR(60) NOT NULL,
dept VARCHAR(30),
country_code CHAR(2),
hire_date DATE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO hr_employee(emp_id,emp_name,dept,country_code,hire_date)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<2000)
SELECT n,
CONCAT('Emp', LPAD(n,5,'0')),
ELT(1+n%8,'Finance','Sales','Production','Logistics','HR','IT','QA','Design'),
ELT(1+n%12,'CN','US','JP','HK','FR','DE','GB','SG','AE','IN','BR','AU'),
DATE(TIMESTAMPADD(DAY, n%3000, '2018-01-01'))
FROM s;
/* ---- MES：工单 ---- */
DROP TABLE IF EXISTS mes_work_order;
CREATE TABLE mes_work_order(
wo_id INT PRIMARY KEY,
product_sku VARCHAR(30) NOT NULL,
qty INT,
started_ts TIMESTAMP NOT NULL,
finished_ts TIMESTAMP NULL,
line_no VARCHAR(10)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO mes_work_order(wo_id,product_sku,qty,started_ts,finished_ts,line_no)
WITH RECURSIVE a(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM a WHERE n<300),
b(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM b WHERE n<100)
SELECT 100*(a.n-1)+b.n,
CONCAT('SKU', LPAD((100*(a.n-1)+b.n)*7%9000,5,'0')),
10 + ((100*(a.n-1)+b.n)*3 % 500),
TIMESTAMPADD(SECOND,(100*(a.n-1)+b.n)*53%86400, TIMESTAMPADD(DAY,(100*(a.n-1)+b.n)%1000,'2024-01-01 00:00:00')),
CASE WHEN (100*(a.n-1)+b.n)%5=0 THEN NULL
ELSE TIMESTAMPADD(HOUR, 8, TIMESTAMPADD(SECOND,(100*(a.n-1)+b.n)*53%86400, TIMESTAMPADD(DAY,(100*(a.n-1)+b.n)%1000,'2024-01-01 00:00:00'))) END,
CONCAT('L', 1+((100*(a.n-1)+b.n)%20))
FROM a CROSS JOIN b;
/* ---- OA：审批单 ---- */
DROP TABLE IF EXISTS oa_approval;
CREATE TABLE oa_approval(
approval_id INT PRIMARY KEY,
doc_type VARCHAR(30) NOT NULL,
applicant_id INT,
status VARCHAR(20),
submitted_ts TIMESTAMP NOT NULL,
approved_ts TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO oa_approval(approval_id,doc_type,applicant_id,status,submitted_ts,approved_ts)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<20000)
SELECT n,
ELT(1+n%5,'PurchaseReq','LeaveReq','Expense','Contract','Budget'),
1 + (n % 2000),
ELT(1+n%4,'Pending','Approved','Rejected','InReview'),
TIMESTAMPADD(SECOND,n*53%86400, TIMESTAMPADD(DAY,n%1000,'2024-01-01 00:00:00')),
CASE WHEN n%4=2 THEN NULL
ELSE TIMESTAMPADD(HOUR, 4, TIMESTAMPADD(SECOND,n*53%86400, TIMESTAMPADD(DAY,n%1000,'2024-01-01 00:00:00'))) END
FROM s;
/* ---- SCM：发运（引用订单） ---- */
DROP TABLE IF EXISTS scm_shipment;
CREATE TABLE scm_shipment(
shipment_id INT PRIMARY KEY,
order_id BIGINT NOT NULL,
carrier VARCHAR(30),
departed_ts TIMESTAMP NOT NULL,
arrived_ts TIMESTAMP NULL,
country_code CHAR(2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO scm_shipment(shipment_id,order_id,carrier,departed_ts,arrived_ts,country_code)
WITH RECURSIVE a(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM a WHERE n<1000),
b(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM b WHERE n<500)
SELECT 500*(a.n-1)+b.n,
1 + ((500*(a.n-1)+b.n)*31 % 1000000),
ELT(1+((500*(a.n-1)+b.n)%6),'DHL','FedEx','SF','UPS','Maersk','EMS'),
TIMESTAMPADD(SECOND,(500*(a.n-1)+b.n)*53%86400, TIMESTAMPADD(DAY,(500*(a.n-1)+b.n)%1000,'2024-01-01 00:00:00')),
CASE WHEN (500*(a.n-1)+b.n)%3=0 THEN NULL
ELSE TIMESTAMPADD(DAY, 5, TIMESTAMPADD(SECOND,(500*(a.n-1)+b.n)*53%86400, TIMESTAMPADD(DAY,(500*(a.n-1)+b.n)%1000,'2024-01-01 00:00:00'))) END,
ELT(1+((500*(a.n-1)+b.n)%12),'CN','US','JP','HK','FR','DE','GB','SG','AE','IN','BR','AU')
FROM a CROSS JOIN b;
/* ---- ERP/CRM/SCM 中枢：全球贸易订单（1,000,000 行） ---- */
DROP TABLE IF EXISTS gl_trade_order;
CREATE TABLE gl_trade_order(
order_id BIGINT NOT NULL,
order_no VARCHAR(24) NOT NULL,
customer_id INT NOT NULL,
country_code CHAR(2) NOT NULL,
currency_code CHAR(3) NOT NULL,
amount_orig DECIMAL(18,2) NOT NULL,
rate DECIMAL(18,8) NOT NULL,
amount_usd DECIMAL(18,2) NOT NULL,
rate_date DATE NOT NULL,
order_ts TIMESTAMP NOT NULL,
status VARCHAR(20),
channel VARCHAR(20),
store_code VARCHAR(10),
note VARCHAR(60) NULL,
PRIMARY KEY (order_id),
UNIQUE KEY uq_order_no (order_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO gl_trade_order(order_id,order_no,customer_id,country_code,currency_code,
amount_orig,rate,amount_usd,rate_date,order_ts,status,channel,store_code,note)
WITH RECURSIVE a(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM a WHERE n<2000),
b(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM b WHERE n<500),
base(o, ctry) AS (
SELECT 2000*(a.n-1)+b.n,
ELT(1+(2000*(a.n-1)+b.n)%12, 'CN','US','JP','HK','FR','DE','GB','SG','AE','IN','BR','AU')
FROM a CROSS JOIN b
),
base2 AS (
SELECT o,
CONCAT('SO', LPAD(o,10,'0')) AS order_no,
1 + (o % 5000) AS customer_id,
ctry,
CASE ctry WHEN 'CN' THEN 'CNY' WHEN 'US' THEN 'USD' WHEN 'JP' THEN 'JPY' WHEN 'HK' THEN 'HKD'
WHEN 'FR' THEN 'EUR' WHEN 'DE' THEN 'EUR' WHEN 'GB' THEN 'GBP' WHEN 'SG' THEN 'SGD'
WHEN 'AE' THEN 'AED' WHEN 'IN' THEN 'INR' WHEN 'BR' THEN 'BRL' WHEN 'AU' THEN 'AUD' END AS currency_code,
ROUND((o*137 % 9000000)/100.0, 2) AS amount_orig,
CASE ctry WHEN 'CN' THEN 0.1400 WHEN 'US' THEN 1.0000 WHEN 'JP' THEN 0.0067 WHEN 'HK' THEN 0.1280
WHEN 'FR' THEN 1.0800 WHEN 'DE' THEN 1.0800 WHEN 'GB' THEN 1.2700 WHEN 'SG' THEN 0.7400
WHEN 'AE' THEN 0.2700 WHEN 'IN' THEN 0.0120 WHEN 'BR' THEN 0.1800 WHEN 'AU' THEN 0.6600 END AS rate,
TIMESTAMPADD(SECOND, (o*53 % 86400), TIMESTAMPADD(DAY, o % 1000, '2024-01-01 00:00:00')) AS order_ts,
ELT(1+(o%5),'Paid','Shipped','Pending','Cancelled','Refunded') AS status,
ELT(1+(o%4),'Online','Store','Wholesale','Outlet') AS channel,
CONCAT('ST', LPAD(1+(o%200),3,'0')) AS store_code
FROM base
)
SELECT o, order_no, customer_id, ctry, currency_code,
amount_orig, rate,
ROUND(amount_orig*rate, 2) AS amount_usd,
DATE(order_ts) AS rate_date,
order_ts, status, channel, store_code, NULL
FROM base2;
/* ---- 为分页实验建支撑索引（造数后建，比边插边建快） ---- */
ALTER TABLE gl_trade_order
ADD KEY ix_ts_id (order_ts, order_id),
ADD KEY ix_country_ts (country_code, order_ts, order_id),
ADD KEY ix_amount_usd_id(amount_usd, order_id),
ADD KEY ix_country_amt (country_code, amount_usd, order_id),
ADD KEY ix_status_ts (status, order_ts, order_id);
/* ---- 时区演示小表：两条「同一本地时刻」但 UTC 不同的订单（DST 区间） ---- */
DROP TABLE IF EXISTS tz_demo;
CREATE TABLE tz_demo(
id INT PRIMARY KEY,
label VARCHAR(40),
utc_ts TIMESTAMP NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO tz_demo(id,label,utc_ts) VALUES
(1,'纽约春令时前(EST)','2026-03-08 06:30:00'),
(2,'纽约春令时后(EDT)','2026-03-08 07:30:00');
SELECT 'lab: 造数完成。gl_trade_order=1,000,000；其余六域表已建。' AS `-- lab --`;
END IF;
END//
DROP PROCEDURE IF EXISTS lab_reset//
CREATE PROCEDURE lab_reset()
BEGIN
DROP TABLE IF EXISTS gl_trade_order, scm_shipment, oa_approval, mes_work_order,
hr_employee, dim_customer, dim_currency, dim_country, tz_demo;
SELECT 'lab: 已清空全部实验表，下次 lab_build() 会重建。' AS `-- lab --`;
END//
DELIMITER ;
CALL lab_build();
CALL demo_reset();
/* ==========================================================================================
§01 OFFSET / LIMIT：基础、深翻页代价、为什么慢、延迟关联缓解
========================================================================================== */
SELECT '§01.1 OFFSET/LIMIT 基础（MySQL 没有 OFFSET...FETCH，用 LIMIT 行数 OFFSET 偏移）' AS `-- 第01章 --`;
-- 关键差异：下面这条「SQL Server / 标准写法」在 MySQL 会直接报错 —— 用错误探针证明
CALL demo_err_probe('MySQL不支持FETCH',
'SELECT order_id FROM gl_trade_order ORDER BY order_ts DESC, order_id DESC OFFSET 10 FETCH NEXT 20 ROWS ONLY');
SELECT ' ★ 正确 MySQL 写法：LIMIT 20 OFFSET 10 ，或 LIMIT 10, 20（偏移,行数）' AS `-- 提示 --`;
SELECT '§01.2 浅翻页（OFFSET 0）与深翻页（OFFSET 500000）的引擎读行对比' AS `-- 第01章 --`;
SELECT ' 总 1,000,000 行、每页 20 行 → 共 50,000 页；OFFSET 500000 = 翻到第 25,001 页' AS `-- 提示 --`;
CALL demo_begin('OF_shallow');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id,order_ts,amount_usd) FROM gl_trade_order
ORDER BY order_ts DESC, order_id DESC LIMIT 20 OFFSET 0;
CALL demo_end('OF_shallow');
CALL demo_begin('OF_deep');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id,order_ts,amount_usd) FROM gl_trade_order
ORDER BY order_ts DESC, order_id DESC LIMIT 20 OFFSET 500000;
CALL demo_end('OF_deep');
SELECT '§01.3 两种翻页的度量（Innodb_rows_read = 引擎实际读出的行数）' AS `-- 第01章 --`;
SELECT m.tag,
m.innodb_rows AS 引擎读行,
m.ms AS 耗时_ms,
m.h_rnd_next AS 全扫描读
FROM demo_metric m WHERE m.tag IN ('OF_shallow','OF_deep') ORDER BY m.tag;
SELECT '§01.4 EXPLAIN：深翻页为什么慢（type=index/ALL，rows≈1,000,000，要先扫再丢）' AS `-- 第01章 --`;
EXPLAIN SELECT order_id, order_ts, amount_usd FROM gl_trade_order
ORDER BY order_ts DESC, order_id DESC LIMIT 20 OFFSET 500000;
SELECT '§01.5 缓解：延迟关联（deferred join）—— 先用窄覆盖索引取 20 个 id，再回表 20 行' AS `-- 第01章 --`;
CALL demo_begin('OF_deferred');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',t.order_id,t.order_ts,t.amount_usd)
FROM gl_trade_order t
JOIN (SELECT order_id FROM gl_trade_order
ORDER BY order_ts DESC, order_id DESC LIMIT 20 OFFSET 500000) x USING(order_id)
ORDER BY t.order_ts DESC, t.order_id DESC;
CALL demo_end('OF_deferred');
SELECT ' ★ 延迟关联内层的 EXPLAIN：Using index（覆盖索引，只扫窄索引，不读整行）' AS `-- 提示 --`;
EXPLAIN SELECT order_id FROM gl_trade_order
ORDER BY order_ts DESC, order_id DESC LIMIT 20 OFFSET 500000;
SELECT '§01.6 结论速记' AS `-- 第01章 --`;
SELECT '· OFFSET 越大，越要先读再丢弃 → 线性变慢（O(偏移)）。' AS `-- 结论 --`;
SELECT '· 延迟关联把「扫 50 万整行」改成「扫 50 万窄索引 + 回表 20 行」，减少缓冲池污染与 CPU。' AS `-- 结论 --`;
SELECT '· 键集分页（第 02 章）根治的是「并发漂移」并减少回表；但 MySQL/InnoDB 中 DESC 深游标仍从顶端扫到游标，并非 O(1)，须靠覆盖索引/分区/近顶端游标才极速。' AS `-- 结论 --`;
/* ==========================================================================================
§02 键集分页（seek / 游标法）：游标恒定、并发零漂移、回表更少（深游标仍扫前半段索引，非 O(1)）
========================================================================================== */
SELECT '§02.1 键集分页原理：用「上一页最后一行的值」当游标，WHERE (排序列) < 游标 ORDER BY ... LIMIT' AS `-- 第02章 --`;
SELECT ' MySQL 支持行值比较 (a,b) < (x,y)，所以多列游标写得干净。' AS `-- 提示 --`;
-- 先取「第 1 页」（首页无游标）
CALL demo_begin('KS_shallow');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id,order_ts,amount_usd) FROM gl_trade_order
ORDER BY order_ts DESC, order_id DESC LIMIT 20;
CALL demo_end('KS_shallow');
SELECT '§02.2 深页键集：取第 25,001 页的游标（上一页末行），再 seek 下一页' AS `-- 第02章 --`;
-- 游标 = 第 500,000 行（DESC 序）的 (order_ts, order_id)
SET @c_ts = (SELECT order_ts FROM gl_trade_order ORDER BY order_ts DESC, order_id DESC LIMIT 1 OFFSET 499999);
SET @c_id = (SELECT order_id FROM gl_trade_order ORDER BY order_ts DESC, order_id DESC LIMIT 1 OFFSET 499999);
SELECT CONCAT(' 游标 = (', @c_ts, ', ', @c_id, ')') AS `-- 游标 --`;
CALL demo_begin('KS_deep');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id,order_ts,amount_usd) FROM gl_trade_order
WHERE (order_ts, order_id) < (@c_ts, @c_id)
ORDER BY order_ts DESC, order_id DESC LIMIT 20;
CALL demo_end('KS_deep');
SELECT '§02.3 深页：OFFSET 500000（坏）vs 键集游标（好）—— 诚实度量' AS `-- 第02章 --`;
SELECT ' ★ 实测：MySQL/InnoDB 的 DESC 深游标仍从「最新端」扫到游标（~500k 索引条目），并非 O(1)；' AS `-- 提示 --`;
SELECT ' 但 OFFSET 深翻页还要为丢弃的 50 万行回表，总读行 ~1,000,000，仍高于键集约 2×。' AS `-- 提示 --`;
SELECT ' → 键集的真价值是「并发零漂移」+「少回表」，而非「深页读 20 行」的神话。' AS `-- 提示 --`;
CALL demo_report(1, '深翻页：OFFSET 500000 vs 键集游标（坏写法读行更多即胜）', '02', 'OF_deep', 'KS_deep', 'L', 1);
SELECT '§02.4 两种写法「取到的 20 行」是否完全一致（位置等价性）' AS `-- 第02章 --`;
DROP TABLE IF EXISTS t_of_deep; CREATE TABLE t_of_deep(order_id BIGINT PRIMARY KEY);
INSERT INTO t_of_deep(order_id)
SELECT order_id FROM gl_trade_order ORDER BY order_ts DESC, order_id DESC LIMIT 20 OFFSET 500000;
DROP TABLE IF EXISTS t_ks_deep; CREATE TABLE t_ks_deep(order_id BIGINT PRIMARY KEY);
INSERT INTO t_ks_deep(order_id)
SELECT order_id FROM gl_trade_order
WHERE (order_ts, order_id) < (@c_ts, @c_id)
ORDER BY order_ts DESC, order_id DESC LIMIT 20;
SELECT (SELECT COUNT(*) FROM t_of_deep) AS of_rows,
(SELECT COUNT(*) FROM t_ks_deep) AS ks_rows,
(SELECT COUNT(*) FROM t_of_deep o JOIN t_ks_deep k USING(order_id)) AS 交集,
(SELECT COUNT(*) FROM t_of_deep o LEFT JOIN t_ks_deep k USING(order_id) WHERE k.order_id IS NULL) AS of独有,
(SELECT COUNT(*) FROM t_ks_deep k LEFT JOIN t_of_deep o USING(order_id) WHERE o.order_id IS NULL) AS ks独有;
SELECT '§02.5 多列游标必须带「唯一 tie-breaker」：只用 order_ts 会在相等时间处重复/漏行' AS `-- 第02章 --`;
SELECT ' (order_ts, order_id) 中 order_id 是主键，保证全局唯一 → 游标点唯一、分页无重叠无遗漏。' AS `-- 提示 --`;
EXPLAIN SELECT order_id, order_ts FROM gl_trade_order
WHERE (order_ts, order_id) < (@c_ts, @c_id)
ORDER BY order_ts DESC, order_id DESC LIMIT 20;
SELECT '§02.6 带过滤条件的键集：国家维度用 ix_country_ts(country_code, order_ts, order_id)' AS `-- 第02章 --`;
SET @c_ts2 = (SELECT order_ts FROM gl_trade_order WHERE country_code='CN' ORDER BY order_ts DESC, order_id DESC LIMIT 1 OFFSET 9999);
SET @c_id2 = (SELECT order_id FROM gl_trade_order WHERE country_code='CN' ORDER BY order_ts DESC, order_id DESC LIMIT 1 OFFSET 9999);
CALL demo_begin('KS_country');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id,order_ts) FROM gl_trade_order
WHERE country_code='CN' AND (order_ts, order_id) < (@c_ts2, @c_id2)
ORDER BY order_ts DESC, order_id DESC LIMIT 20;
CALL demo_end('KS_country');
SELECT CONCAT(' CN 深页游标 = (', @c_ts2, ', ', @c_id2, ')，国家内键集同样稳定、少回表（深游标仍会扫前半段索引）。') AS `-- 提示 --`;
SELECT '§02.7 连续翻 3 页（模拟用户一直点「下一页」），每页都是常数时间' AS `-- 第02章 --`;
DELIMITER //
DROP PROCEDURE IF EXISTS ks_pages_demo//
CREATE PROCEDURE ks_pages_demo()
BEGIN
DECLARE v_ts DATETIME; DECLARE v_id BIGINT; DECLARE i INT DEFAULT 0;
DROP TABLE IF EXISTS t_pages; CREATE TABLE t_pages(seq INT, order_id BIGINT, order_ts TIMESTAMP);
SET v_ts = '2999-12-31 00:00:00'; SET v_id = 9999999999;
WHILE i < 3 DO
INSERT INTO t_pages(seq, order_id, order_ts)
SELECT i, order_id, order_ts FROM gl_trade_order
WHERE (order_ts, order_id) < (v_ts, v_id)
ORDER BY order_ts DESC, order_id DESC LIMIT 20;
SELECT order_ts, order_id INTO v_ts, v_id FROM t_pages WHERE seq=i ORDER BY order_ts DESC, order_id DESC LIMIT 1;
SET i = i + 1;
END WHILE;
END//
DELIMITER ;
CALL ks_pages_demo();
SELECT seq, COUNT(*) AS rows_in_page, MIN(order_id) AS first_id, MAX(order_id) AS last_id
FROM t_pages GROUP BY seq ORDER BY seq;
SELECT ' ★ 三页合计 60 行，页间游标由上一页末行驱动，O(1) 每页，永不随深度退化。' AS `-- 提示 --`;
SELECT '§02.8 近顶端键集才真正 O(1)：游标取「第 2 页」末行，引擎只读 ~40 行' AS `-- 第02章 --`;
SET @nt_ts = (SELECT order_ts FROM gl_trade_order ORDER BY order_ts DESC, order_id DESC LIMIT 1 OFFSET 20);
SET @nt_id = (SELECT order_id FROM gl_trade_order ORDER BY order_ts DESC, order_id DESC LIMIT 1 OFFSET 20);
CALL demo_begin('KS_neartop');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id,order_ts) FROM gl_trade_order
WHERE (order_ts, order_id) < (@nt_ts, @nt_id)
ORDER BY order_ts DESC, order_id DESC LIMIT 20;
CALL demo_end('KS_neartop');
SELECT CONCAT(' 近顶端游标 = (', @nt_ts, ', ', @nt_id, ')，键集引擎读行 ',
(SELECT innodb_rows FROM demo_metric WHERE tag='KS_neartop'),
' 行（≈浅 OFFSET 的 2 倍，因从最新端扫到紧随其后的 40 个索引条目）。') AS `-- 提示 --`;
SELECT ' ★ 结论：键集的「极速区」在游标靠近最新端时成立；深游标须靠覆盖索引/分区把它拉回极速区。' AS `-- 提示 --`;
/* ==========================================================================================
§03 高并发写入下的稳定性：OFFSET 会漏看/重复，键集游标稳如磐石
------------------------------------------------------------------------------------------
场景：用户在翻页过程中，系统不断有新订单写入（追加在「最新」位置）。
========================================================================================== */
SELECT '§03.1 准备：取一个游标 C（DESC 序第 120 行），其「下一页」= 位置 121..140' AS `-- 第03章 --`;
SET @c_ts_s = (SELECT order_ts FROM gl_trade_order ORDER BY order_ts DESC, order_id DESC LIMIT 1 OFFSET 119);
SET @c_id_s = (SELECT order_id FROM gl_trade_order ORDER BY order_ts DESC, order_id DESC LIMIT 1 OFFSET 119);
-- 翻页「之前」的快照
DROP TABLE IF EXISTS t_offbefore; CREATE TABLE t_offbefore(order_id BIGINT PRIMARY KEY);
INSERT INTO t_offbefore(order_id)
SELECT order_id FROM gl_trade_order ORDER BY order_ts DESC, order_id DESC LIMIT 20 OFFSET 120;
DROP TABLE IF EXISTS t_ksbefore; CREATE TABLE t_ksbefore(order_id BIGINT PRIMARY KEY);
INSERT INTO t_ksbefore(order_id)
SELECT order_id FROM gl_trade_order
WHERE (order_ts, order_id) < (@c_ts_s, @c_id_s)
ORDER BY order_ts DESC, order_id DESC LIMIT 20;
SELECT '§03.2 模拟高并发：插入一条「最新」订单（order_id=9000000001，时间远超现有最大）' AS `-- 第03章 --`;
INSERT INTO gl_trade_order(order_id,order_no,customer_id,country_code,currency_code,amount_orig,rate,amount_usd,rate_date,order_ts,status,channel,store_code,note)
VALUES (9000000001,'SO9000000001',1,'CN','CNY', 9999.00, 0.1400, 1399.86, '2027-01-01', '2027-01-01 00:00:00', 'Paid','Online','ST001', '并发插入测试');
SELECT '§03.3 翻页「之后」再取同样的 OFFSET 120 / 同样的游标 C' AS `-- 第03章 --`;
DROP TABLE IF EXISTS t_offafter; CREATE TABLE t_offafter(order_id BIGINT PRIMARY KEY);
INSERT INTO t_offafter(order_id)
SELECT order_id FROM gl_trade_order ORDER BY order_ts DESC, order_id DESC LIMIT 20 OFFSET 120;
DROP TABLE IF EXISTS t_ksafter; CREATE TABLE t_ksafter(order_id BIGINT PRIMARY KEY);
INSERT INTO t_ksafter(order_id)
SELECT order_id FROM gl_trade_order
WHERE (order_ts, order_id) < (@c_ts_s, @c_id_s)
ORDER BY order_ts DESC, order_id DESC LIMIT 20;
SELECT '§03.4 对比：OFFSET 翻页被「漏看」了几行？键集翻页被漏看了几行？' AS `-- 第03章 --`;
SELECT (SELECT COUNT(*) FROM t_offbefore) AS off_before,
(SELECT COUNT(*) FROM t_offafter) AS off_after,
(SELECT COUNT(*) FROM t_offbefore o LEFT JOIN t_offafter a USING(order_id) WHERE a.order_id IS NULL) AS off_被跳过行数,
(SELECT COUNT(*) FROM t_ksbefore) AS ks_before,
(SELECT COUNT(*) FROM t_ksafter) AS ks_after,
(SELECT COUNT(*) FROM t_ksbefore k LEFT JOIN t_ksafter a USING(order_id) WHERE a.order_id IS NULL) AS ks_被跳过行数;
SELECT '§03.5 结论' AS `-- 第03章 --`;
SELECT '· OFFSET 是「位置」语义：新行插到最前 → 后续每页整体后移 1 位 → 用户按顺序翻页会漏看刚好滑走的行。' AS `-- 结论 --`;
SELECT '· 键集是「值」语义：游标 C 固定，新插入的最新行在 C 之前（DESC 序更前），不影响 C 之后的页 → 零漂移。' AS `-- 结论 --`;
SELECT '· 因此：面向 Feed/订单流/日志等「只追加」场景，必须用键集；OFFSET 只适合静态小结果集。' AS `-- 结论 --`;
-- 清理并发测试行，保证可反复执行（幂等）
DELETE FROM gl_trade_order WHERE order_id = 9000000001;
SELECT '§03.6 已清理并发测试行（order_id=9000000001），数据恢复 1,000,000 行。' AS `-- 第03章 --`;
/* ==========================================================================================
§04 国际时区：订单统一存 UTC，按 UTC 排序/分页，按本地时区仅做展示
========================================================================================== */
SELECT '§04.1 同一笔订单，三种本地时区展示（UTC 是唯一真相源；本机 time_zone_name 为空，CONVERT_TZ 返回 NULL，改用 TIMESTAMPADD 硬编码偏移）' AS `-- 第04章 --`;
SELECT order_id, order_ts AS UTC时间,
TIMESTAMPADD(MINUTE, 480, order_ts) AS 上海_UTCp8,
TIMESTAMPADD(MINUTE,-300, order_ts) AS 纽约_UTCm5,
TIMESTAMPADD(MINUTE, 60, order_ts) AS 巴黎_UTCp1
FROM gl_trade_order WHERE order_id IN (1, 500000, 1000000) ORDER BY order_id;
SELECT '§04.2 键集游标的「游标」必须是 UTC（客户端传 ISO-8601+Z），与查看者时区无关' AS `-- 第04章 --`;
SELECT ' 第 02 章的键集查询 WHERE (order_ts,order_id) < (@c_ts,@c_id) 用的就是 UTC，' AS `-- 提示 --`;
SELECT ' 上海/纽约/巴黎用户看到的首页内容一致（仅展示时用 TIMESTAMPADD 换算本地），但翻页边界永不漂移。' AS `-- 提示 --`;
SELECT '§04.3 DST 陷阱：绝不能「按本地时间存/排/分页」—— 秋令时同一本地时刻会出现两次' AS `-- 第04章 --`;
DROP TABLE IF EXISTS tz_demo2;
CREATE TABLE tz_demo2(id INT PRIMARY KEY, label VARCHAR(40), utc_ts DATETIME NOT NULL);
INSERT INTO tz_demo2(id,label,utc_ts) VALUES
(1,'纽约秋令时第一次 01:30 (EDT, UTC-4)','2026-11-01 05:30:00'),
(2,'纽约秋令时第二次 01:30 (EST, UTC-5)','2026-11-01 06:30:00');
SELECT id, label, utc_ts AS UTC,
TIMESTAMPADD(MINUTE,-240,utc_ts) AS 纽约本地_EDT,
TIMESTAMPADD(MINUTE,-300,utc_ts) AS 纽约本地_EST
FROM tz_demo2 ORDER BY utc_ts;
SELECT ' ★ 两行 UTC 相差 1 小时（05:30 vs 06:30），但纽约本地都显示 01:30:00（DST 回拨导致重复）。' AS `-- 提示 --`;
SELECT ' ★ 正确做法：存储与排序一律 UTC，本地仅 TIMESTAMPADD 展示；游标用 UTC 字符串传递。' AS `-- 提示 --`;
/* ==========================================================================================
§05 国际多币种结算：按金额分页必须按「归一化金额」，绝不可按原始金额跨币种排
========================================================================================== */
SELECT '§05.1 反例（错误）：直接按原始金额 amount_orig 排 —— 把 ¥/＄/€ 混在一起比大小' AS `-- 第05章 --`;
SELECT ' 下面用手搓 6 行示例说明原理，避免对全表做无意义的跨币种排序' AS `-- 第05章 --`;
SELECT ' 手搓示例：三笔不同币种的「原始金额」恰好都是 100' AS `-- 第05章 --`;
WITH sample(ccy, orig) AS (
SELECT 'JPY', 100.00 UNION ALL
SELECT 'USD', 100.00 UNION ALL
SELECT 'EUR', 100.00 UNION ALL
SELECT 'JPY', 9000.00 UNION ALL -- 9000 日元 ≈ 60 美元
SELECT 'USD', 60.00 UNION ALL
SELECT 'EUR', 55.00
)
SELECT ccy, orig AS 原始金额,
ROUND(orig * CASE ccy WHEN 'JPY' THEN 0.0067 WHEN 'USD' THEN 1.0 WHEN 'EUR' THEN 1.08 END, 2) AS 折合USD
FROM sample
ORDER BY orig DESC; -- ★ 按原始金额 DESC：9000 日元(≈60$) 排在最前，完全失真
SELECT ' ★ 按 orig DESC 第一是 9000 日元（仅≈60 美元），把币种不同的金额直接比大小毫无意义。' AS `-- 提示 --`;
SELECT '§05.2 正解：按归一化金额 amount_usd 排，并保留原始币种/金额用于展示' AS `-- 第05章 --`;
SELECT order_id, currency_code, amount_orig, amount_usd, rate_date
FROM gl_trade_order
ORDER BY amount_usd DESC, order_id DESC
LIMIT 10;
SELECT ' ★ 这 10 行是「全集团按美元计的真实最大额」，且带原始币种可前端本地化显示。' AS `-- 提示 --`;
SELECT '§05.3 按归一化金额做键集分页（深页常数时间，用 ix_amount_usd_id 覆盖索引）' AS `-- 第05章 --`;
SET @m_usd = (SELECT amount_usd FROM gl_trade_order ORDER BY amount_usd DESC, order_id DESC LIMIT 1 OFFSET 99999);
SET @m_id = (SELECT order_id FROM gl_trade_order ORDER BY amount_usd DESC, order_id DESC LIMIT 1 OFFSET 99999);
CALL demo_begin('CU_keyset');
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',order_id,currency_code,amount_orig,amount_usd)
FROM gl_trade_order
WHERE (amount_usd, order_id) < (@m_usd, @m_id)
ORDER BY amount_usd DESC, order_id DESC LIMIT 20;
CALL demo_end('CU_keyset');
SELECT CONCAT(' 金额深页游标 = (', @m_usd, ', ', @m_id, ')，按美元键集同样稳定、回表更少（深游标仍扫前半段索引）。') AS `-- 提示 --`;
SELECT '§05.4 汇率快照日（rate_date = DATE(order_ts)）：跨日汇率变动时归一化要取当日汇率' AS `-- 第05章 --`;
DROP TABLE IF EXISTS fx_rate;
CREATE TABLE fx_rate(ccy CHAR(3), rate_date DATE, rate DECIMAL(18,8), PRIMARY KEY(ccy, rate_date));
INSERT INTO fx_rate(ccy,rate_date,rate) VALUES
('JPY','2026-09-01',0.0067),('JPY','2026-10-01',0.0068), -- 日元 10 月升值
('USD','2026-09-01',1.0), ('USD','2026-10-01',1.0),
('EUR','2026-09-01',1.08), ('EUR','2026-10-01',1.07);
SELECT ' 用当日汇率重算某笔订单的美元额（生产环境就是这样算 amount_usd 的）：' AS `-- 提示 --`;
SELECT g.order_id, g.currency_code, g.amount_orig, g.rate_date,
f.rate AS 当日汇率,
ROUND(g.amount_orig * f.rate, 2) AS 正确美元额
FROM gl_trade_order g
JOIN fx_rate f ON f.ccy = g.currency_code AND f.rate_date = g.rate_date
WHERE g.order_id IN (1, 250000, 750000)
ORDER BY g.order_id;
SELECT ' ★ 键集游标若按金额翻页，必须基于「已用当日汇率归一化」的列，否则同一金额在不同日含义不同。' AS `-- 提示 --`;
/* ==========================================================================================
§06 「跳到第 N 页」与万亿级工程：混合分页、不精确页码、游标编码
========================================================================================== */
SELECT '§06.1 算总页数 = COUNT(*) —— 在万亿级是奢侈品' AS `-- 第06章 --`;
CALL demo_begin('JMP_count');
SELECT COUNT(*) INTO @dummy FROM gl_trade_order;
CALL demo_end('JMP_count');
SELECT m.ms AS 本机1M行COUNT耗时_ms, m.innodb_rows AS 引擎读行
FROM demo_metric m WHERE m.tag='JMP_count';
SELECT ' ★ 1M 行已需整索引扫描；1e12 行 COUNT(*) 不可接受 → 放弃精确总页数。' AS `-- 提示 --`;
SELECT '§06.2 混合分页：前 K 页用 OFFSET（偏移小、便宜），深页改键集（恒定快）' AS `-- 第06章 --`;
SELECT ' 模板（K 取 1000，即前 1000 页=前 20000 行仍用 OFFSET）：' AS `-- 提示 --`;
SELECT ' -- 第 1..K 页：' AS `-- 模板 --`;
SELECT ' SELECT ... ORDER BY order_ts DESC, order_id DESC LIMIT 20 OFFSET (:page-1)*20;' AS `-- 模板 --`;
SELECT ' -- 第 K+1 页起（前端带着上一页末行游标）：' AS `-- 模板 --`;
SELECT ' SELECT ... WHERE (order_ts,order_id) < (:cur_ts,:cur_id) ORDER BY order_ts DESC, order_id DESC LIMIT 20;' AS `-- 模板 --`;
SELECT '§06.3 不精确页码：只给「上一页/下一页/尾页估算」，不暴露总页数' AS `-- 第06章 --`;
SELECT ' EXPLAIN 的 rows 是优化器估算，可用来粗略估总数（误差可接受）：' AS `-- 提示 --`;
EXPLAIN SELECT order_id FROM gl_trade_order WHERE country_code='CN';
SELECT ' ★ 万亿级只算「本分片估算 × 分片数」作为尾页近似值，避免全局 COUNT。' AS `-- 提示 --`;
SELECT '§06.4 游标编码：给前端不透明的 cursor，避免暴露主键/排序值' AS `-- 第06章 --`;
SET @cur = TO_BASE64(CONCAT_WS(',', '2026-09-01 12:00:00', 12345));
SELECT @cur AS 不透明游标_base64;
SELECT SUBSTRING_INDEX(FROM_BASE64(@cur), ',', 1) AS 解出_ts,
SUBSTRING_INDEX(FROM_BASE64(@cur), ',', -1) AS 解出_id;
SELECT ' ★ 前端只持有 cursor 字符串；后端解码回 (ts,id) 做键集，安全且与时区无关。' AS `-- 提示 --`;
/* ==========================================================================================
§07 万亿级高并发工程清单
========================================================================================== */
SELECT '§07.1 覆盖索引：让键集查询「只走索引、不回表」' AS `-- 第07章 --`;
ALTER TABLE gl_trade_order ADD KEY ix_demo_cover (order_ts, order_id, amount_usd, currency_code);
EXPLAIN SELECT order_id, amount_usd, currency_code FROM gl_trade_order
WHERE (order_ts, order_id) < ('2026-01-01 00:00:00', 999999)
ORDER BY order_ts DESC, order_id DESC LIMIT 20;
SELECT ' ★ Extra=Using index（覆盖）→ 深页也只扫窄索引，不碰聚簇。用完即删（见清理）。' AS `-- 提示 --`;
ALTER TABLE gl_trade_order DROP KEY ix_demo_cover;
SELECT '§07.2 分区裁剪：按 order_ts RANGE 分区，近实时键集只扫近期分区' AS `-- 第07章 --`;
SELECT ' PARTITION BY RANGE (TO_DAYS(order_ts))：查询「近 30 天」只命中 1~2 个分区，' AS `-- 提示 --`;
SELECT ' 配合键集游标，跨分区也能 EXPLAIN 看到 partitions 列被裁剪。' AS `-- 提示 --`;
SELECT '§07.3 分库分表：分片键怎么选，直接决定键集能否下推' AS `-- 第07章 --`;
SELECT ' · 按 order_id HASH 分片 → 时间序键集需跨分片归并，慢且复杂（不推荐）。' AS `-- 提示 --`;
SELECT ' · 按 country_code / 按 月桶 分片 → 单分片内即可键集，推荐。' AS `-- 提示 --`;
SELECT ' · 读写分离：键集走从库只读，深页不再抢占主库缓冲池与锁。' AS `-- 提示 --`;
SELECT '§07.4 高并发稳定性：键集远比 OFFSET 友好' AS `-- 第07章 --`;
SELECT ' · OFFSET 深页 = 大扫描 → 易把热点页挤出缓冲池、放大锁竞争。' AS `-- 提示 --`;
SELECT ' · 键集深页 = 常数行读取 → 无热点、无长事务、连接占用短。' AS `-- 提示 --`;
SELECT ' · 追加型流（订单/日志/Feed）必用键集；OFFSET 只留给静态小结果集。' AS `-- 提示 --`;
/* ==========================================================================================
§08 六域分页模板落地（OA / HR / MES / SCM / CRM / ERP）
------------------------------------------------------------------------------------------
每个域都演示「键集分页」写法；OFFSET 仅用于浅页/后台管理。
========================================================================================== */
SELECT '§08.1 OA 审批单：按提交时间倒序键集' AS `-- 第08章 --`;
SET @oa_ts = (SELECT submitted_ts FROM oa_approval ORDER BY submitted_ts DESC, approval_id DESC LIMIT 1 OFFSET 9999);
SET @oa_id = (SELECT approval_id FROM oa_approval ORDER BY submitted_ts DESC, approval_id DESC LIMIT 1 OFFSET 9999);
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',approval_id,doc_type,status) FROM oa_approval
WHERE (submitted_ts, approval_id) < (@oa_ts, @oa_id)
ORDER BY submitted_ts DESC, approval_id DESC LIMIT 20;
SELECT ' OA 模板：WHERE (submitted_ts, approval_id) < (:cur_ts,:cur_id) ORDER BY submitted_ts DESC, approval_id DESC LIMIT 20' AS `-- 模板 --`;
SELECT '§08.2 HR 员工：按入职日期倒序键集（若按姓名分页，列需同排序规则）' AS `-- 第08章 --`;
SET @hr_dt = (SELECT hire_date FROM hr_employee ORDER BY hire_date DESC, emp_id DESC LIMIT 1 OFFSET 1999);
SET @hr_id = (SELECT emp_id FROM hr_employee ORDER BY hire_date DESC, emp_id DESC LIMIT 1 OFFSET 1999);
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',emp_id,emp_name,dept) FROM hr_employee
WHERE (hire_date, emp_id) < (@hr_dt, @hr_id)
ORDER BY hire_date DESC, emp_id DESC LIMIT 20;
SELECT ' 按姓名分页须保证列字符集/排序规则一致，否则触发 ERROR 1267（见 Select 专题）。' AS `-- 提示 --`;
SELECT '§08.3 MES 工单：按开工时间倒序键集（含未完成单 finished_ts IS NULL）' AS `-- 第08章 --`;
SET @mes_ts = (SELECT started_ts FROM mes_work_order ORDER BY started_ts DESC, wo_id DESC LIMIT 1 OFFSET 29999);
SET @mes_id = (SELECT wo_id FROM mes_work_order ORDER BY started_ts DESC, wo_id DESC LIMIT 1 OFFSET 29999);
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',wo_id,product_sku,qty) FROM mes_work_order
WHERE (started_ts, wo_id) < (@mes_ts, @mes_id)
ORDER BY started_ts DESC, wo_id DESC LIMIT 20;
SELECT '§08.4 SCM 发运：按发运时间倒序键集 + 国家过滤（用 ix 同理）' AS `-- 第08章 --`;
SET @sc_ts = (SELECT departed_ts FROM scm_shipment WHERE country_code='CN' ORDER BY departed_ts DESC, shipment_id DESC LIMIT 1 OFFSET 9999);
SET @sc_id = (SELECT shipment_id FROM scm_shipment WHERE country_code='CN' ORDER BY departed_ts DESC, shipment_id DESC LIMIT 1 OFFSET 9999);
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',shipment_id,carrier,country_code) FROM scm_shipment
WHERE country_code='CN' AND (departed_ts, shipment_id) < (@sc_ts, @sc_id)
ORDER BY departed_ts DESC, shipment_id DESC LIMIT 20;
SELECT '§08.5 CRM 客户：搜索 + 分页（LIKE 前缀 + 键集）' AS `-- 第08章 --`;
SET @crm_ts = (SELECT created_ts FROM dim_customer WHERE customer_name LIKE 'Vip%' ORDER BY created_ts DESC, customer_id DESC LIMIT 1 OFFSET 999);
SET @crm_id = (SELECT customer_id FROM dim_customer WHERE customer_name LIKE 'Vip%' ORDER BY created_ts DESC, customer_id DESC LIMIT 1 OFFSET 999);
INSERT INTO demo_sink(line)
SELECT CONCAT_WS('|',customer_id,customer_name,segment) FROM dim_customer
WHERE customer_name LIKE 'Vip%'
AND (created_ts, customer_id) < (@crm_ts, @crm_id)
ORDER BY created_ts DESC, customer_id DESC LIMIT 20;
SELECT ' ★ LIKE ''Vip%'' 可用索引；后缀/中间模糊（%x%）则无法走索引，需全文索引/ES。' AS `-- 提示 --`;
SELECT '§08.6 ERP 中枢（gl_trade_order）已成第 01~07 章主战场；六域统一结论：' AS `-- 第08章 --`;
SELECT ' 「可追加的流」用键集（UTC + 唯一 tie-breaker）；「静态后台列表」浅页可用 OFFSET。' AS `-- 结论 --`;
/* ==========================================================================================
§09 自检（V1~V10）：用断言把前面所有结论钉死
========================================================================================== */
SELECT '§09.1 运行各项断言……' AS `-- 自检 --`;
-- V1 框架表存在
SELECT COUNT(*) INTO @v1 FROM information_schema.tables
WHERE table_schema='JewelryAnalytics' AND table_name IN ('demo_result','demo_verify','demo_metric') HAVING COUNT(*)=3;
CALL demo_v('V1_度量框架表存在', IF(@v1=3,1,0), CONCAT('命中表数=',IFNULL(@v1,0)));
-- V2 跨章对比已登记（OF_deep vs KS_deep）
SELECT COUNT(*) INTO @v2 FROM demo_result WHERE label LIKE '深翻页%';
CALL demo_v('V2_深翻页对比已登记', IF(@v2>=1,1,0), CONCAT('demo_result 中深翻页对比条数=',IFNULL(@v2,0)));
-- V3 键集引擎读行严格少于 OFFSET 深翻页（诚实：深游标仍扫前半段索引，但总读行更少）
SELECT IFNULL((SELECT innodb_rows FROM demo_metric WHERE tag='KS_deep'),0) INTO @ks;
SELECT IFNULL((SELECT innodb_rows FROM demo_metric WHERE tag='OF_deep'),1) INTO @of;
CALL demo_v('V3_键集引擎读行严格少于OFFSET深翻页', IF(@ks < @of,1,0), CONCAT('KS_deep=',@ks,' OF_deep=',@of,' 键集更少=',IF(@ks<@of,'是','否')));
-- V4 OFFSET 深翻页 与 键集 取到的 20 行完全一致（位置等价）
SELECT (SELECT COUNT(*) FROM t_of_deep) INTO @a;
SELECT (SELECT COUNT(*) FROM t_ks_deep) INTO @b;
SELECT (SELECT COUNT(*) FROM t_of_deep o JOIN t_ks_deep k USING(order_id)) INTO @inter;
SELECT (SELECT COUNT(*) FROM t_of_deep o LEFT JOIN t_ks_deep k USING(order_id) WHERE k.order_id IS NULL) INTO @offonly;
SELECT (SELECT COUNT(*) FROM t_ks_deep k LEFT JOIN t_of_deep o USING(order_id) WHERE o.order_id IS NULL) INTO @ksonly;
CALL demo_v('V4_OFFSET与键集取行一致', IF(@a=20 AND @inter=20 AND @offonly=0 AND @ksonly=0,1,0),
CONCAT('of=',@a,' ks=',@b,' 交集=',@inter,' of独有=',@offonly,' ks独有=',@ksonly));
-- V5 稳定性：OFFSET 漏看 >=1 行；键集漏看 = 0
SELECT (SELECT COUNT(*) FROM t_offbefore o LEFT JOIN t_offafter a USING(order_id) WHERE a.order_id IS NULL) INTO @offskip;
SELECT (SELECT COUNT(*) FROM t_ksbefore k LEFT JOIN t_ksafter a USING(order_id) WHERE a.order_id IS NULL) INTO @ksskip;
CALL demo_v('V5_OFFSET漏看而键集不漏看', IF(@offskip>=1 AND @ksskip=0,1,0),
CONCAT('OFFSET被跳过=',IFNULL(@offskip,0),' 键集被跳过=',IFNULL(@ksskip,0)));
-- V6 时区：两行 UTC 不同但纽约本地相同（DST 歧义，证明不能按本地存/排）
-- 本机 time_zone_name 为空，CONVERT_TZ 返回 NULL，故用 TIMESTAMPADD 硬编码各自行正确偏移（EDT -240 / EST -300）
SELECT COUNT(DISTINCT utc_ts) INTO @utc_n FROM tz_demo2;
SELECT TIMESTAMPADD(MINUTE,-240,(SELECT utc_ts FROM tz_demo2 WHERE id=1)) INTO @ny1;
SELECT TIMESTAMPADD(MINUTE,-300,(SELECT utc_ts FROM tz_demo2 WHERE id=2)) INTO @ny2;
CALL demo_v('V6_时区DST本地时间歧义', IF(@utc_n=2 AND @ny1=@ny2,1,0),
CONCAT('UTC不同数=',IFNULL(@utc_n,0),' NY本地1=',IFNULL(CAST(@ny1 AS CHAR),'0'),' NY本地2=',IFNULL(CAST(@ny2 AS CHAR),'0'),' 是否相同=',IF(@ny1=@ny2,'是','否')));
-- V7 结算一致性：amount_usd == ROUND(amount_orig*rate,2)
SELECT COUNT(*) INTO @v7 FROM gl_trade_order WHERE amount_usd <> ROUND(amount_orig*rate,2);
CALL demo_v('V7_金额归一化一致性', IF(@v7=0,1,0), CONCAT('不一致行数=',IFNULL(@v7,0)));
-- V8 无残留演示索引（ix_demo%）
SELECT COUNT(*) INTO @v8 FROM information_schema.statistics
WHERE table_schema='JewelryAnalytics' AND table_name='gl_trade_order' AND index_name LIKE 'ix_demo%';
CALL demo_v('V8_无残留演示索引', IF(@v8=0,1,0), CONCAT('ix_demo% 残留=',IFNULL(@v8,0)));
-- V9 安全更新模式仍关闭（脚本头部设置未被破坏）
CALL demo_v('V9_sql_safe_updates已关', IF(@@sql_safe_updates=0,1,0), CONCAT('sql_safe_updates=',@@sql_safe_updates));
-- V10 错误探针：MySQL 不支持 FETCH 已被捕获
SELECT passed INTO @v10 FROM demo_verify WHERE check_name='错误探针·MySQL不支持FETCH';
CALL demo_v('V10_错误探针已捕获FETCH不支持', IF(@v10=1,1,0), CONCAT('probe passed=',IFNULL(@v10,0)));
SELECT '§09.2 自检总评' AS `-- 自检 --`;
SELECT check_name AS 检查项, passed AS 通过,
CASE WHEN passed=1 THEN '✓' ELSE '✗' END AS 结论, detail AS 明细
FROM demo_verify ORDER BY check_name;
SELECT CONCAT('自检通过 ', SUM(passed), ' / ', COUNT(*), ' 项') AS `自检总评`
FROM demo_verify;
/* ==========================================================================================
§10 总结：实测度量、关键比值、最重要结论、方法清单
========================================================================================== */
SELECT '§10.1 实测度量总表（Innodb_rows_read = 引擎读行；ms = 本机耗时）' AS `-- 总结 --`;
SELECT tag AS 标签, innodb_rows AS 引擎读行, ms AS 耗时_ms, h_rnd_next AS 全扫描读
FROM demo_metric
WHERE tag IN ('OF_shallow','OF_deep','OF_deferred','KS_shallow','KS_deep','KS_neartop','KS_country','CU_keyset','JMP_count')
ORDER BY innodb_rows DESC;
SELECT '§10.2 关键对照（坏写法 vs 好写法，倍数越大越好）' AS `-- 总结 --`;
SELECT label AS 对照, chapter AS 章,
bad_rows AS 坏_读行, good_rows AS 好_读行,
rows_ratio AS 读行倍数, time_ratio AS 耗时倍数, passed AS 通过
FROM demo_result ORDER BY seq;
SELECT '§10.3 三句话最重要结论' AS `-- 总结 --`;
SELECT '1) 深翻页 = OFFSET 越大越慢（O(偏移)，且要为丢弃行回表）；键集游标并发零漂移、回表更少；但 MySQL/InnoDB 中 DESC 深游标仍从顶端扫到游标（~半程索引），并非 O(1)，须靠覆盖索引/分区/近顶端游标才极速。' AS `结论1`;
SELECT '2) 只追加的流（订单/日志/Feed）必须用键集：OFFSET 会因并发插入漏看/重复，键集游标零漂移。' AS `结论2`;
SELECT '3) 国际场景铁律：时间存 UTC、按 UTC 排序/做游标；金额按归一化币种排；游标用 UTC 字符串不透明编码。' AS `结论3`;
SELECT '§10.4 上线前方法清单（八条）' AS `-- 总结 --`;
SELECT '① 默认用键集分页；OFFSET 仅给静态小列表/后台。' AS `清单`;
SELECT '② 游标 = (排序列, 唯一 tie-breaker)，保证全局唯一、无重叠无遗漏。' AS `清单`;
SELECT '③ 排序列建索引；常用查询做覆盖索引（含返回列）以杜绝回表。' AS `清单`;
SELECT '④ 时间一律 UTC 存储与排序；本地时区用 TIMESTAMPADD(固定偏移) 展示——本机 time_zone_name 表为空，CONVERT_TZ 会返回 NULL，不可用。' AS `清单`;
SELECT '⑤ 多币种：存原始金额+汇率+归一化金额+汇率快照日；按归一化金额分页。' AS `清单`;
SELECT '⑥ 万亿级：放弃精确总页数，混合分页 + 不精确尾页估算 + 游标编码。' AS `清单`;
SELECT '⑦ 分区按时间裁剪；分片键选时间桶/国家，别选主键 HASH（否则跨片归并）。' AS `清单`;
SELECT '⑧ 高并发：键集短连接、无大扫描；OFFSET 深页会污染缓冲池、放大锁竞争。' AS `清单`;
SELECT '§10.5 脚本结构与运行方式' AS `-- 总结 --`;
SELECT '· 共 11 段（00 头部/运行须知 → 09 自检）：00 头部差异与速查；01 OFFSET 深翻页；02 键集；' AS `结构`;
SELECT ' 03 并发稳定性；04 时区；05 多币种；06 跳页+游标；07 万亿工程；08 六域模板；09 自检。' AS `结构`;
SELECT '· 运行：mysql.exe < MySQL9_Pagination_JewelryDemo.sql（详见 §0.0）；可反复执行、幂等。' AS `运行`;
SELECT '· 造数：gl_trade_order=1,000,000 行；dim/scm/mes/oa/hr 配套；已建则跳过，重建请 CALL lab_reset()。' AS `运行`;
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)生存.---Geovin Du(涂聚文）
浙公网安备 33010602011771号