sql: SQL Data Sampling and Approximation Techniques using mysql 9.0

 

CREATE DATABASE IF NOT EXISTS JewelryAnalytics DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE JewelryAnalytics;
SET SESSION cte_max_recursion_depth=1000000;
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;
SET SESSION time_zone='+00:00';
SET SESSION max_execution_time=0;
 
-- ============================================================================
-- §0.0  运行方式必读（务必先读）
-- ============================================================================
-- 本脚本：MySQL 9.0 数据采样与近似聚合（Sampling & Approximation）
-- 主题：国际化珠宝集团 OA/ERP/CRM/HRM/MES/SCM/WMS/KMS 八域交易数据
--       + 万亿高并发 + 国际时区 + 多币种结算 下的"快但够用"的估算技术
-- 运行前提：MySQL 9.0.x，库 JewelryAnalytics 自动建/用；本脚本完全幂等。
--   方式A（命令行，推荐，避开 Workbench 30s 读超时）：
--     mysql -uroot -p --default-character-set=utf8mb4 < MySQL9_SamplingApprox_JewelryDemo.sql
--   方式B（Workbench）：整段执行。
-- 幂等说明：每次先 DROP 全部 samp_* 实验表/过程再重建造数，重复运行结果一致。
-- 度量口径：performance_schema.session_status 的 INNODB_ROWS_READ / CREATED_TMP_TABLES /
--           SORT_ROWS 增量，量化"引擎真实代价"，证明采样/近似省在哪。
-- ============================================================================
 
-- §0.1  MySQL 采样/近似能力速览（与其它数据库术语映射）
-- ----------------------------------------------------------------------------
-- ANSI/PG/SQLServer              MySQL 9.0 等价实现
-- -----------------------------  -------------------------------------------------
-- TABLESAMPLE SYSTEM(n)          无原生支持 → 用 WHERE MOD(pk,n)=r（系统抽样）
-- TABLESAMPLE BERNOULLI(p)       无原生支持 → 用 WHERE RAND()<p（伯努利抽样）
-- APPROX_COUNT_DISTINCT(col)     本实例 9.0.1 未提供；改用「采样外推/捕获-重捕」或下沉 ES
-- COUNT(DISTINCT col)            精确去重（通常建临时表，贵）
-- 列直方图 / 近似分布             ANALYZE TABLE t UPDATE HISTOGRAM ON c WITH N BUCKETS
-- 物化视图 / 预聚合              MySQL 无物化视图 → 用"汇总表(rollup)"手工预聚合
-- 优化器估算行数                 EXPLAIN 的 rows 列（便宜的近似结果量）
-- ============================================================================
 
-- §0.2  反模式速查表（本机已实测）
-- ----------------------------------------------------------------------------
-- 反模式                                  后果                                  正确做法
-- ORDER BY RAND() LIMIT n              全表扫描+filesort，深数据灾难        WHERE MOD(pk,n)=r 系统抽样
-- RAND()<p 抽样当审计依据              RAND() 非确定，结果不可重放          用 MOD 确定性抽样（可复现）
-- 采样外推/近似聚合做合规对账          有偏有误差，合规不允                 精确场合用 COUNT(DISTINCT) 全量
-- MOD(id,10)=0 假设 id 均匀            若 id 按区域分块→抽样有偏            分层抽样（系统内 MOD）
-- 跨币种 SUM(amount_orig) 抽样         混合币种+仅样本=双重错误             只 SUM(amount_usd)
-- 按 created_ts(UTC) 抽"本地日"        UTC 切割本地日→样本错位             用 create_local_date 生成列
-- 以为 MySQL 有 TABLESAMPLE           报错 1064                            用 MOD/RAND/预聚合代替
-- 直方图不更新就查                     优化器仍用旧统计                      ANALYZE TABLE UPDATE HISTOGRAM
-- ============================================================================
 
-- §01  度量框架（samp_demo_*）：量化引擎真实代价
DROP TABLE IF EXISTS samp_demo_metric;
CREATE TABLE samp_demo_metric(
  tag VARCHAR(40) PRIMARY KEY,
  innodb_rows BIGINT NOT NULL DEFAULT 0,
  rnd_next BIGINT NOT NULL DEFAULT 0,
  rnd BIGINT NOT NULL DEFAULT 0,
  tmp_tables BIGINT NOT NULL DEFAULT 0,
  sort_rows BIGINT NOT NULL DEFAULT 0,
  ms INT NOT NULL DEFAULT 0,
  note VARCHAR(255)
);
 
DROP TABLE IF EXISTS samp_demo_result;
CREATE TABLE samp_demo_result(
  check_id VARCHAR(30) PRIMARY KEY,
  passed TINYINT NOT NULL,
  detail VARCHAR(400)
);
 
DROP TABLE IF EXISTS samp_demo_probe;
CREATE TABLE samp_demo_probe(
  id INT AUTO_INCREMENT PRIMARY KEY,
  label VARCHAR(60),
  errno INT,
  sql_state VARCHAR(10),
  msg VARCHAR(300),
  caught_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
DROP PROCEDURE IF EXISTS samp_snap;
DELIMITER //
CREATE PROCEDURE samp_snap()
BEGIN
  SELECT VARIABLE_VALUE INTO @s_ir FROM performance_schema.session_status WHERE VARIABLE_NAME='INNODB_ROWS_READ';
  SELECT VARIABLE_VALUE INTO @s_rn FROM performance_schema.session_status WHERE VARIABLE_NAME='HANDLER_READ_RND_NEXT';
  SELECT VARIABLE_VALUE INTO @s_rd FROM performance_schema.session_status WHERE VARIABLE_NAME='HANDLER_READ_RND';
  SELECT VARIABLE_VALUE INTO @s_tt FROM performance_schema.session_status WHERE VARIABLE_NAME='CREATED_TMP_TABLES';
  SELECT VARIABLE_VALUE INTO @s_sr FROM performance_schema.session_status WHERE VARIABLE_NAME='SORT_ROWS';
  SET @s_ts = UNIX_TIMESTAMP(NOW(6));
END//
DELIMITER ;
 
DROP PROCEDURE IF EXISTS samp_measure;
DELIMITER //
CREATE PROCEDURE samp_measure(IN p_tag VARCHAR(40))
BEGIN
  DECLARE v_ir BIGINT; DECLARE v_rn BIGINT; DECLARE v_rd BIGINT; DECLARE v_tt BIGINT; DECLARE v_sr BIGINT; DECLARE v_ms INT;
  SELECT VARIABLE_VALUE INTO v_ir FROM performance_schema.session_status WHERE VARIABLE_NAME='INNODB_ROWS_READ';
  SELECT VARIABLE_VALUE INTO v_rn FROM performance_schema.session_status WHERE VARIABLE_NAME='HANDLER_READ_RND_NEXT';
  SELECT VARIABLE_VALUE INTO v_rd FROM performance_schema.session_status WHERE VARIABLE_NAME='HANDLER_READ_RND';
  SELECT VARIABLE_VALUE INTO v_tt FROM performance_schema.session_status WHERE VARIABLE_NAME='CREATED_TMP_TABLES';
  SELECT VARIABLE_VALUE INTO v_sr FROM performance_schema.session_status WHERE VARIABLE_NAME='SORT_ROWS';
  SET v_ms = ROUND((UNIX_TIMESTAMP(NOW(6)) - @s_ts)*1000);
  INSERT INTO samp_demo_metric(tag,innodb_rows,rnd_next,rnd,tmp_tables,sort_rows,ms) VALUES(p_tag, v_ir-@s_ir, v_rn-@s_rn, v_rd-@s_rd, v_tt-@s_tt, v_sr-@s_sr, v_ms)
    ON DUPLICATE KEY UPDATE innodb_rows=v_ir-@s_ir, rnd_next=v_rn-@s_rn, rnd=v_rd-@s_rd, tmp_tables=v_tt-@s_tt, sort_rows=v_sr-@s_sr, ms=v_ms;
END//
DELIMITER ;
 
DROP PROCEDURE IF EXISTS samp_v;
DELIMITER //
CREATE PROCEDURE samp_v(IN p_check VARCHAR(30), IN p_pass TINYINT, IN p_detail VARCHAR(400))
BEGIN
  INSERT INTO samp_demo_result(check_id,passed,detail) VALUES(p_check,p_pass,p_detail)
    ON DUPLICATE KEY UPDATE passed=p_pass, detail=p_detail;
END//
DELIMITER ;
 
DROP PROCEDURE IF EXISTS samp_err_probe;
DELIMITER //
CREATE PROCEDURE samp_err_probe(IN p_label VARCHAR(60), IN p_sql TEXT)
BEGIN
  DECLARE v_no INT DEFAULT 0; DECLARE v_st VARCHAR(10) DEFAULT '00000'; DECLARE v_msg VARCHAR(300) DEFAULT '';
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  BEGIN
    GET DIAGNOSTICS CONDITION 1 v_no = MYSQL_ERRNO, v_st = RETURNED_SQLSTATE, v_msg = MESSAGE_TEXT;
  END;
  SET @samp_probe_sql = p_sql;
  PREPARE samp_pstmt FROM @samp_probe_sql;
  IF v_no = 0 THEN
    EXECUTE samp_pstmt;
    DEALLOCATE PREPARE samp_pstmt;
  END IF;
  INSERT INTO samp_demo_probe(label,errno,sql_state,msg) VALUES(p_label, v_no, v_st, v_msg);
END//
DELIMITER ;
 
-- §02  实验台：八域交易事实表 + 维度表 + 造数（国际化珠宝集团）
DROP TABLE IF EXISTS samp_dim_currency;
CREATE TABLE samp_dim_currency(code CHAR(3) PRIMARY KEY, name VARCHAR(40), symbol VARCHAR(8), usd_rate DECIMAL(12,6));
INSERT INTO samp_dim_currency VALUES
 ('CNY','人民币','¥',1/7.20),('USD','美元','$',1.0),('EUR','欧元','€',1.08),
 ('JPY','日元','¥',0.0067),('HKD','港元','HK$',1/7.80),('GBP','英镑','£',1.27);
 
DROP TABLE IF EXISTS samp_dim_country;
CREATE TABLE samp_dim_country(code CHAR(2) PRIMARY KEY, name VARCHAR(40), tz_offset_min INT, currency CHAR(3));
INSERT INTO samp_dim_country VALUES
 ('CN','中国',480,'CNY'),('US','美国',-300,'USD'),('FR','法国',60,'EUR'),
 ('JP','日本',540,'JPY'),('HK','中国香港',480,'HKD'),('GB','英国',0,'GBP'),
 ('DE','德国',60,'EUR'),('SG','新加坡',480,'USD');
 
-- 八域交易事实表（采样/近似主战场）
DROP TABLE IF EXISTS samp_txn;
CREATE TABLE samp_txn(
  txn_id         BIGINT PRIMARY KEY,
  system_code    CHAR(4) NOT NULL,          -- OA/ERP/CRM/HRM/MES/SCM/WMS/KMS
  doc_type       VARCHAR(30),
  customer_id    INT NOT NULL,
  category       VARCHAR(20),
  channel        VARCHAR(20),
  country_code   CHAR(2),
  currency_code  CHAR(3),
  amount_orig    DECIMAL(18,2),
  amount_usd     DECIMAL(18,2),             -- 已按汇率归一，结算只用它
  create_offset_min SMALLINT,              -- 时区偏移（分钟），冗余以便生成列建索引
  created_ts     DATETIME NOT NULL,         -- 统一存 UTC
  create_local_date DATE                    -- 生成列：本地营业日，用于带索引的时区过滤
    GENERATED ALWAYS AS (DATE(TIMESTAMPADD(MINUTE, create_offset_min, created_ts))) STORED,
  KEY ix_system (system_code),
  KEY ix_local_date (create_local_date),
  KEY ix_usd (amount_usd),
  KEY ix_customer (customer_id)
) ENGINE=InnoDB;
 
-- 造 200,000 行（500×400），八域、多国、多币种、中英混排、UTC 时间
INSERT INTO samp_txn(txn_id,system_code,doc_type,customer_id,category,channel,country_code,currency_code,amount_orig,amount_usd,create_offset_min,created_ts)
WITH RECURSIVE a(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM a WHERE n<500),
              b(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM b WHERE n<400),
              base AS (SELECT a.n*400 + b.n - 400 AS id FROM a JOIN b),
              enriched AS (
  SELECT
    id,
    ELT(1+id%8,'OA','ERP','CRM','HRM','MES','SCM','WMS','KMS') AS sys,
    ELT(1+id%10,'黄金','铂金','钻石','红宝石','蓝宝石','翡翠','珍珠','银','18K金','白金') AS cat,
    ELT(1+id%5,'门店','官网','微信','展会','经销商') AS ch,
    (id % 60000) + 1 AS cust,              -- 约 60000 个独立客户（高基数列，供近似去重演示）
    ELT(1+id%8,'CN','US','FR','JP','HK','GB','DE','SG') AS ctry,
    ELT(1+id%6,'采购','报销','合同','工单','运单','知识') AS dtype,
    ROUND(50 + (id%9500)/10.0, 2) AS amt
  FROM base
)
SELECT
  id, sys, dtype, cust, cat, ch, ctry,
  ELT(FIELD(ctry,'CN','US','FR','JP','HK','GB','DE','SG'),'CNY','USD','EUR','JPY','HKD','GBP','EUR','USD') AS cur,
  amt,
  ROUND(amt * ELT(FIELD(ctry,'CN','US','FR','JP','HK','GB','DE','SG'),1/7.20,1.0,1.08,0.0067,1/7.80,1.27,1.08,1.0), 2) AS amt_usd,
  ELT(FIELD(ctry,'CN','US','FR','JP','HK','GB','DE','SG'),480,-300,60,540,480,0,60,480) AS off,
  DATE_ADD('2025-09-01 00:00:00', INTERVAL (id % 365) DAY) + INTERVAL (id % 86400) SECOND AS cts
FROM enriched;
 
-- §03  近似聚合（采样外推）：MySQL 9.0.1 无内置 HLL 近似去重，用「采样+外推」做快估
SELECT '§03  近似聚合：采样外推 SUM/COUNT（MySQL 无内置 HLL 近似去重）' AS `-- 第3章 --`;
 
-- 3.1 精确总量
SELECT COUNT(*) INTO @total_rows FROM samp_txn;
SELECT SUM(amount_usd) INTO @exact_gmv FROM samp_txn;
 
-- 3.2 近似总量：伯努利抽样 RAND()<0.05（对 id 独立、无系统性偏差），按 1/抽样率 外推
SELECT COUNT(*), SUM(amount_usd) INTO @s_rows, @s_gmv FROM samp_txn WHERE RAND() < 0.05;
SET @approx_gmv = @s_gmv * (@total_rows / @s_rows);
 
-- 3.3 度量：精确聚合全扫 vs 伯努利采样聚合（抽样对 id 独立，无偏）
CALL samp_snap();
SELECT SUM(amount_usd) INTO @x1 FROM samp_txn;
CALL samp_measure('EXACT_agg');
CALL samp_snap();
SELECT SUM(amount_usd) INTO @x2 FROM samp_txn WHERE RAND() < 0.05;
CALL samp_measure('SAMPLE_agg');
 
SELECT '精确GMV_usd=' AS `-- `, @exact_gmv, '近似GMV_usd=' AS `-- `, @approx_gmv,
       CONCAT('误差=', ROUND(100*(@approx_gmv-@exact_gmv)/@exact_gmv,3), '%') AS `误差`;
 
-- 3.4 近似「独立客户数」是难题：需独立随机抽样或 HLL；周期性 MOD 抽样会破坏独立性，
--     不能直接套捕获-重捕(Lincoln-Petersen)。生产用应用层 HLL 或下沉 Elasticsearch。此处只演示 SUM 外推。
 
-- §04  系统抽样（Systematic）：MOD(pk) 确定性、可复现、走主键
SELECT '§04  系统抽样 MOD(pk,n)=r：确定性、可复现、万亿级友好' AS `-- 第4章 --`;
 
SELECT COUNT(*) INTO @sys_sample FROM samp_txn WHERE MOD(txn_id,10)=0;   -- 期望 ~10% = 20000
 
-- 分层抽样：每个 system_code 内部各取 10%（避免 id 按域分块导致的偏差）
SELECT system_code, COUNT(*) AS stratum_rows,
       SUM(MOD(txn_id,10)=0) AS sampled_in_stratum
FROM samp_txn GROUP BY system_code ORDER BY system_code;
 
-- §05  伯努利抽样（Bernoulli）：RAND()<p（全扫但无排序）；及 ORDER BY RAND() 灾难
SELECT '§05  伯努利抽样 RAND()<p  vs  ORDER BY RAND() 灾难' AS `-- 第5章 --`;
 
SELECT COUNT(*) INTO @bern_sample FROM samp_txn WHERE RAND() < 0.01;   -- 期望 ~1% = 2000
 
-- 度量：ORDER BY RAND() LIMIT（全量 RAND 计算+堆排，CPU 重）vs MOD 系统抽样（整数运算、无随机）
CALL samp_snap();
SELECT COUNT(*) INTO @r1 FROM samp_txn ORDER BY RAND() LIMIT 1000;
CALL samp_measure('RAND_sort');
CALL samp_snap();
SELECT COUNT(*) INTO @r2 FROM samp_txn WHERE MOD(txn_id,10)=0 LIMIT 1000;
CALL samp_measure('MOD_sample');
-- 非确定性演示：ORDER BY RAND() LIMIT 1 的首行 ≠ 确定性 ORDER BY txn_id LIMIT 1 的首行（不可重放）
SELECT txn_id INTO @ra FROM samp_txn ORDER BY RAND() LIMIT 1;
SELECT txn_id INTO @rd FROM samp_txn ORDER BY txn_id LIMIT 1;
 
-- §06  预聚合汇总表（rollup）：万亿级"近似"正解——查 O(小) 而非扫全表
SELECT '§06  预聚合汇总表(rollup)：万亿级近似/实时看板的正确姿势' AS `-- 第6章 --`;
 
DROP TABLE IF EXISTS samp_daily_rollup;
CREATE TABLE samp_daily_rollup(
  local_date DATE NOT NULL,
  system_code CHAR(4) NOT NULL,
  currency_code CHAR(3) NOT NULL,
  txn_cnt BIGINT NOT NULL,
  gmv_usd DECIMAL(20,2) NOT NULL,
  approx_buyers BIGINT NOT NULL,
  PRIMARY KEY (local_date, system_code, currency_code)
) ENGINE=InnoDB;
 
INSERT INTO samp_daily_rollup(local_date, system_code, currency_code, txn_cnt, gmv_usd, approx_buyers)
SELECT create_local_date, system_code, currency_code,
       COUNT(*), SUM(amount_usd), COUNT(DISTINCT customer_id)
FROM samp_txn
GROUP BY create_local_date, system_code, currency_code;
 
-- 万亿级查询：直接读 rollup（O(几百行)），不再扫事实表
SELECT system_code, SUM(txn_cnt) AS total_txn, ROUND(SUM(gmv_usd),2) AS total_gmv_usd,
       SUM(approx_buyers) AS approx_total_buyers
FROM samp_daily_rollup
GROUP BY system_code ORDER BY total_gmv_usd DESC;
 
-- §07  列直方图：ANALYZE TABLE UPDATE HISTOGRAM（近似分布/基数，供优化器与估算）
SELECT '§07  列直方图：UPDATE HISTOGRAM（近似分布）' AS `-- 第7章 --`;
ANALYZE TABLE samp_txn UPDATE HISTOGRAM ON amount_usd WITH 32 BUCKETS;
SELECT table_name, column_name,
       JSON_EXTRACT(histogram, '$.last_updated') AS last_updated,
       JSON_EXTRACT(histogram, '$.number_of_buckets') AS buckets
FROM information_schema.column_statistics
WHERE schema_name='JewelryAnalytics' AND table_name='samp_txn' AND column_name='amount_usd';
 
-- §08  EXPLAIN 估算行数：便宜的"近似结果量"
SELECT '§08  EXPLAIN 估算行数（优化器的近似）' AS `-- 第8章 --`;
EXPLAIN SELECT SUM(amount_usd) FROM samp_txn WHERE create_local_date='2025-12-25';
 
-- §09  国际时区 + 多币种 + 采样/近似 组合
SELECT '§09  时区/多币种 + 采样近似：本地日抽样、只用 amount_usd' AS `-- 第9章 --`;
 
-- 9.1  在"本地营业日"内做 10% 系统抽样，估算各域 GMV 与独立买家数
SELECT t.system_code, c.name AS country,
       COUNT(*) AS sampled_rows,
       ROUND(SUM(t.amount_usd),2) AS gmv_usd,            -- 正确：归一美元
       COUNT(DISTINCT t.customer_id) AS approx_buyers
FROM samp_txn t
JOIN samp_dim_country c ON c.code = t.country_code
WHERE t.create_local_date = '2025-12-25'
  AND MOD(t.txn_id,10) = 0
GROUP BY t.system_code, c.name ORDER BY gmv_usd DESC;
 
-- 9.2  时区正确性：生成列非 NULL；TIMESTAMPADD 可用；CONVERT_TZ 本机为 NULL（已知）
SELECT TIMESTAMPADD(MINUTE,480,'2026-01-01 00:00:00') INTO @tz_chk;
SELECT CONVERT_TZ('2026-01-01 00:00:00','UTC','Asia/Shanghai') INTO @tz_null;
 
-- 9.3  多币种一致性：amount_usd 与 amount_orig*rate 全部一致
SELECT COUNT(*) INTO @cur_ok FROM samp_txn
WHERE ABS(amount_usd - ROUND(amount_orig * CASE currency_code WHEN 'CNY' THEN 1/7.20 WHEN 'USD' THEN 1 WHEN 'EUR' THEN 1.08 WHEN 'JPY' THEN 0.0067 WHEN 'HKD' THEN 1/7.80 WHEN 'GBP' THEN 1.27 END,2)) < 0.02;
SELECT COUNT(*) INTO @cur_tot FROM samp_txn;
 
-- §10  自检（V1..V12）全部通过才算交付
-- V1 框架表存在
SELECT COUNT(*) INTO @v1 FROM information_schema.tables WHERE table_schema='JewelryAnalytics' AND table_name IN ('samp_demo_metric','samp_demo_result','samp_demo_probe');
CALL samp_v('V1_框架表存在', IF(@v1=3,1,0), CONCAT('框架表数=',@v1));
 
-- V2 近似聚合（采样外推）可用且 >0
CALL samp_v('V2_近似聚合可用', IF(@approx_gmv>0,1,0), CONCAT('approx_gmv=',@approx_gmv));
 
-- V3 系统抽样 MOD 约 10%（5%~20%）
CALL samp_v('V3_系统抽样约10%', IF(@sys_sample BETWEEN 10000 AND 40000,1,0), CONCAT('抽样行=',@sys_sample,' 全量=200000'));
 
-- V4 直方图已生成
SELECT COUNT(*) INTO @v4 FROM information_schema.column_statistics
WHERE schema_name='JewelryAnalytics' AND table_name='samp_txn' AND column_name='amount_usd';
CALL samp_v('V4_直方图已生成', IF(@v4>=1,1,0), CONCAT('histogram条目=',@v4));
 
-- V5 rollup 汇总表存在且 GMV>0
SELECT IFNULL((SELECT SUM(gmv_usd) FROM samp_daily_rollup),0) INTO @v5;
CALL samp_v('V5_汇总表GMV>0', IF(@v5>0,1,0), CONCAT('rollup总GMV_usd=',@v5));
 
-- V6 时区：生成列可用；CONVERT_TZ 本机 NULL（已知）
CALL samp_v('V6_时区生成列可用', IF(@tz_chk IS NOT NULL AND @tz_null IS NULL,1,0), CONCAT('TSadd非NULL=',IF(@tz_chk IS NULL,'N','Y'),' CONVERT_TZ=',IF(@tz_null IS NULL,'NULL(已知)','有值')));
 
-- V7 多币种：amount_usd 与 amount_orig*rate 全部一致
CALL samp_v('V7_多币种USD归一一致', IF(@cur_ok=@cur_tot,1,0), CONCAT('一致=',@cur_ok,'/',@cur_tot));
 
-- V8 近似 vs 精确：GMV 外推误差 < 5%
CALL samp_v('V8_近似误差<5%', IF(@exact_gmv>0 AND ABS(@approx_gmv-@exact_gmv)/@exact_gmv < 0.05,1,0), CONCAT('exact_gmv=',@exact_gmv,' approx_gmv=',@approx_gmv,' 误差=',ROUND(100*(@approx_gmv-@exact_gmv)/@exact_gmv,3),'%'));
 
-- V9 ORDER BY RAND() 非确定（不可重放）；MOD 系统抽样确定性可复现
CALL samp_v('V9_RAND非确定不可重放', IF(@ra <> @rd,1,0), CONCAT('rand首行=',@ra,' 确定首行=',@rd));
 
-- V10 预聚合汇总表读行远少于事实表全扫（万亿级成本赢点）
CALL samp_snap();
SELECT SUM(gmv_usd) INTO @x3 FROM samp_daily_rollup;
CALL samp_measure('ROLLUP_agg');
SELECT IFNULL((SELECT innodb_rows FROM samp_demo_metric WHERE tag='EXACT_agg'),0) INTO @ir_exact;
SELECT IFNULL((SELECT innodb_rows FROM samp_demo_metric WHERE tag='ROLLUP_agg'),0) INTO @ir_rollup;
CALL samp_v('V10_汇总表读行远少于事实表', IF(@ir_exact > @ir_rollup,1,0), CONCAT('事实表读行=',@ir_exact,' 汇总表读行=',@ir_rollup));
 
-- V11 错误探针：MySQL 无 ANSI TABLESAMPLE → 报错（演示替代方案必要）
CALL samp_err_probe('MySQL无TABLESAMPLE', 'SELECT * FROM samp_txn TABLESAMPLE SYSTEM(10)');
SELECT IFNULL((SELECT errno FROM samp_demo_probe WHERE label='MySQL无TABLESAMPLE' ORDER BY id DESC LIMIT 1),0) INTO @v11;
CALL samp_v('V11_TABLESAMPLE不支持', IF(@v11>0,1,0), CONCAT('捕获errno=',@v11));
 
-- V12 幂等：交易事实表量稳定 = 200000
SELECT COUNT(*) INTO @v12 FROM samp_txn;
CALL samp_v('V12_数据量稳定200000', IF(@v12=200000,1,0), CONCAT('txn行数=',@v12));
 
SELECT '==== 自检汇总：以下应全部 passed=1 ====' AS `-- 自检 --`;
SELECT check_id, passed, detail FROM samp_demo_result ORDER BY check_id;
 
-- §11  实测总表与结论
SELECT '§11.1  引擎真实代价对照（增量）' AS `-- 第11章 --`;
SELECT tag, innodb_rows AS 引擎读行, tmp_tables AS 临时表, sort_rows AS 排序行, ms AS 耗时ms, note
FROM samp_demo_metric ORDER BY tag;
 
SELECT '§11.2  错误探针（预期捕获，非致命）' AS `-- 第11章 --`;
SELECT label, errno, msg FROM samp_demo_probe ORDER BY id;
 
SELECT '§11.3  自检结果（全部 passed=1 为通过）' AS `-- 第11章 --`;
SELECT check_id, passed, detail FROM samp_demo_result ORDER BY check_id;
 
SELECT '§11.4  关键结论' AS `-- 第11章 --`;
SELECT '1) MySQL 无 ANSI TABLESAMPLE；系统抽样用 MOD(pk,n)=r（确定性、可复现、走主键）。' AS `结论1`;
SELECT '2) MySQL 9.0.1 无内置 HLL 近似去重；用「采样外推 SUM」或「捕获-重捕」估独立客户数，或下沉 ES。' AS `结论2`;
SELECT '3) ORDER BY RAND() LIMIT n = 全表扫描+filesort，深数据灾难；抽样请改用 MOD 或预聚合。' AS `结论3`;
SELECT '4) RAND() 非确定，结果不可重放；审计/对账需可复现时禁用，用 MOD 确定性抽样。' AS `结论4`;
SELECT '5) 万亿级"近似"正解=预聚合汇总表(rollup)：查 O(小) 而非扫全表；MySQL 无物化视图靠手工 rollup。' AS `结论5`;
SELECT '6) 列直方图(ANALYZE TABLE UPDATE HISTOGRAM)给优化器近似分布；EXPLAIN rows 是便宜的结果量近似。' AS `结论6`;
SELECT '7) 时区存 UTC，抽样按生成列 create_local_date(本地日) 过滤；多币种只 SUM(amount_usd)。' AS `结论7`;
SELECT '8) 精确场合(对账/合规)用 COUNT(DISTINCT) 全量；近似(看板/探索)才用采样外推或下沉 ES。' AS `结论8`;
 
SELECT '§11.5  八条方法清单（落到建表/查询）' AS `-- 第11章 --`;
SELECT '① 大盘近似用「采样外推」或下沉 ES（MySQL 无 HLL 近似去重）；② 抽样用 MOD(pk,n) 确定性；③ 禁 ORDER BY RAND()；' AS `清单`;
SELECT '④ 万亿级建 rollup 汇总表做近似/实时；⑤ 用 ANALYZE TABLE 更新直方图；⑥ 用 EXPLAIN 估算行数；' AS `清单`;
SELECT '⑦ 时区用生成列本地日+索引；⑧ 精确对账用 COUNT(DISTINCT)，只 SUM(amount_usd)。' AS `清单`;
 
SELECT '§11.6  脚本结构' AS `-- 第11章 --`;
SELECT '_s00 头部/速览/反模式；_s01 度量框架；_s02 八域建表造数；_s03 采样外推近似；' AS `结构`;
SELECT '_s04 系统抽样；_s05 伯努利/ORDER BY RAND陷阱；_s06 rollup；_s07 直方图；_s08 EXPLAIN；_s09 时区/多币种；_s10 自检；_s11 总结。' AS `结构`;