#!/usr/bin/env bash
# MySQL 直方图 vs 默认 10% 过滤估计 演示 v1.0（surinch.com · 盈尺）
# 只在测试实例跑！会 DROP/CREATE 库 mysql_hist_lab（自造约 20 万行）。
# 测试版本：MySQL 8.0.46。用法：MYSQL="/opt/mysql/bin/mysql -uroot" ./mysql-histogram-stale-stats-demo-v1.0.sh
set -euo pipefail
MYSQL=${MYSQL:-mysql}
DB=mysql_hist_lab

q() { echo; echo "== $1"; $MYSQL -e "$2"; }

q "0 建库造数（channel 无索引，倾斜：web≈90% / batch≈0.5%）" "
DROP DATABASE IF EXISTS $DB;
CREATE DATABASE $DB;
USE $DB;
CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  channel VARCHAR(16) NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  created_at DATE NOT NULL,
  KEY idx_customer (customer_id)
) ENGINE=InnoDB;
SET SESSION cte_max_recursion_depth=1000000;
INSERT INTO orders (customer_id, channel, amount, created_at)
WITH RECURSIVE seq AS (SELECT 1 n UNION ALL SELECT n+1 FROM seq WHERE n < 200000)
SELECT 1+(n%5000),
  CASE WHEN n%200=0 THEN 'batch' WHEN n%67=0 THEN 'api' WHEN n%12=0 THEN 'app' ELSE 'web' END,
  ROUND(10+(n%990)/10,2),
  DATE_ADD('2025-01-01', INTERVAL (n%600) DAY)
FROM seq;
ANALYZE TABLE orders;
SELECT channel, COUNT(*) c, ROUND(100*COUNT(*)/SUM(COUNT(*)) OVER(),2) pct
FROM orders GROUP BY channel ORDER BY c DESC;
"

q "1 无直方图：batch / web 的 filtered 都是默认 10%" "
USE $DB;
EXPLAIN SELECT id FROM orders WHERE channel='batch';
EXPLAIN SELECT id FROM orders WHERE channel='web';
EXPLAIN ANALYZE SELECT id FROM orders WHERE channel='batch'\G
"

q "2 建 singleton 直方图（32 buckets）后再看 filtered" "
USE $DB;
ANALYZE TABLE orders UPDATE HISTOGRAM ON channel WITH 32 BUCKETS;
SELECT COLUMN_NAME,
  JSON_UNQUOTE(JSON_EXTRACT(HISTOGRAM,'$.\"histogram-type\"')) AS hist_type,
  JSON_LENGTH(JSON_EXTRACT(HISTOGRAM,'$.\"buckets\"')) AS buckets_len,
  JSON_UNQUOTE(JSON_EXTRACT(HISTOGRAM,'$.\"last-updated\"')) AS last_updated
FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME='$DB';
EXPLAIN SELECT id FROM orders WHERE channel='batch';
EXPLAIN SELECT id FROM orders WHERE channel='web';
EXPLAIN ANALYZE SELECT id FROM orders WHERE channel='batch'\G
"

q "3 过期：再插入 40000 行 batch，不刷新直方图" "
USE $DB;
SET SESSION cte_max_recursion_depth=1000000;
INSERT INTO orders (customer_id, channel, amount, created_at)
WITH RECURSIVE seq AS (SELECT 1 n UNION ALL SELECT n+1 FROM seq WHERE n < 40000)
SELECT 1+(n%5000), 'batch', 1.00, '2026-10-01' FROM seq;
SELECT channel, COUNT(*) c FROM orders GROUP BY channel ORDER BY c DESC;
EXPLAIN SELECT id FROM orders WHERE channel='batch';
EXPLAIN ANALYZE SELECT id FROM orders WHERE channel='batch'\G
"

q "4 刷新直方图；并证明普通 ANALYZE TABLE 不会改 last-updated" "
USE $DB;
ANALYZE TABLE orders UPDATE HISTOGRAM ON channel WITH 32 BUCKETS;
EXPLAIN SELECT id FROM orders WHERE channel='batch';
SELECT JSON_UNQUOTE(JSON_EXTRACT(HISTOGRAM,'$.\"last-updated\"')) AS before_plain_analyze
FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME='$DB';
ANALYZE TABLE orders;
SELECT JSON_UNQUOTE(JSON_EXTRACT(HISTOGRAM,'$.\"last-updated\"')) AS after_plain_analyze
FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME='$DB';
"

echo
echo "完成。库名 $DB 可按需 DROP DATABASE。"