#!/bin/bash
# 盈尺（Surinch）MySQL 联合索引练习库 v1.0 —— 10 题，一次跑完
# 只能在测试实例上跑：会 DROP/CREATE 库 ${DB}，反复建删索引，并改 performance_schema 消费者开关。
# 测试：MySQL 8.0.46（官方 minimal 二进制），innodb_buffer_pool_size=1G，idx_lab.orders 200 万行 CRC32 自造数据，非客户数据。
# 用法：MYSQL="mysql -uroot -h127.0.0.1" DB=idx_lab ./mysql-index-practice-lab-v1.0.sh | tee result.txt
set -euo pipefail
MYSQL=${MYSQL:-"mysql"}
DB=${DB:-idx_lab}
case "$DB" in *_lab) ;; *) echo "DB 名必须以 _lab 结尾（防止误删业务库）"; exit 1;; esac
M="$MYSQL $DB"
ts(){ date '+%F %T.%3N'; }

init(){
  echo "[$(ts)] 建库造数 2,000,000 行"
  $MYSQL -e "DROP DATABASE IF EXISTS $DB; CREATE DATABASE $DB;"
  $M <<'SQL'
CREATE TABLE digits(d INT PRIMARY KEY); INSERT INTO digits VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE orders(
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  tenant_id SMALLINT NOT NULL, user_id INT NOT NULL, status TINYINT NOT NULL,
  sku_code VARCHAR(20) NOT NULL, coupon_code VARCHAR(16) NULL,
  amount DECIMAL(10,2) NOT NULL, created_at DATETIME NOT NULL, remark VARCHAR(100) NOT NULL
) ENGINE=InnoDB;
INSERT INTO orders(tenant_id,user_id,status,sku_code,coupon_code,amount,created_at,remark)
SELECT 1+MOD(CRC32(CONCAT('t',n)),20), 1+MOD(CRC32(CONCAT('u',n)),100000),
       ELT(1+MOD(CRC32(CONCAT('s',n)),20),1,1,1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,3,3,9),
       CONCAT('SKU',LPAD(MOD(CRC32(CONCAT('k',n)),50000),5,'0')),
       IF(MOD(CRC32(CONCAT('c',n)),50)=0, CONCAT('CP',n), NULL),
       ROUND(5+MOD(CRC32(CONCAT('a',n)),20000)/10,2),
       TIMESTAMP('2026-01-01') + INTERVAL MOD(CRC32(CONCAT('d',n)),23587200) SECOND, REPEAT('x',60)
FROM (SELECT a.d+b.d*10+c.d*100+e.d*1000+f.d*10000+g.d*100000+h.d*1000000 n
      FROM digits a,digits b,digits c,digits e,digits f,digits g,(SELECT 0 d UNION ALL SELECT 1) h) t;
ANALYZE TABLE orders;
SQL
  $M -t -e "SELECT COUNT(*) total, SUM(status=1) st1, SUM(status=9) st9, SUM(coupon_code IS NULL) coupon_null FROM orders;"
  $MYSQL -e "UPDATE performance_schema.setup_consumers SET ENABLED='YES' WHERE NAME='events_statements_history_long'; TRUNCATE performance_schema.events_statements_history_long;"
}

# 只保留主键，再建本题需要的索引
idx(){
  local drops
  drops=$($M -N -e "SELECT GROUP_CONCAT(DISTINCT CONCAT('DROP INDEX ',index_name)) FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name='orders' AND index_name<>'PRIMARY'")
  if [ "$drops" != "NULL" ]; then $M -e "ALTER TABLE orders $drops"; fi
  if [ $# -gt 0 ]; then $M -e "ALTER TABLE orders $*; ANALYZE TABLE orders" >/dev/null; fi
  echo "-- 当前索引: $($M -N -e "SELECT GROUP_CONCAT(x SEPARATOR '  ') FROM (SELECT CONCAT(index_name,'(',GROUP_CONCAT(column_name,IF(collation='D',' DESC','') ORDER BY seq_in_index),')') x FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name='orders' GROUP BY index_name) t")"
}

# 打印 EXPLAIN、EXPLAIN ANALYZE，再跑 3 次取 performance_schema 里的耗时和扫描行数
q(){
  local tag=$1 sql=$2
  echo; echo "### [$tag] $sql"
  $M -t -e "EXPLAIN $sql" | cut -c1-260
  $M -s -r -e "EXPLAIN ANALYZE $sql" | sed 's/^/  /' | cut -c1-260
  echo "  Handler: $($M -e "FLUSH STATUS; $sql; SHOW SESSION STATUS LIKE 'Handler_read%'" | grep '^Handler_read' | grep -v $'\t0$' | tr '\t\n' '= ' )"
  for i in 1 2 3; do $M --comments -e "/* $tag */ $sql" >/dev/null; done
  $MYSQL -t -e "SELECT '$tag' tag, ROUND(TIMER_WAIT/1e9,1) ms, ROWS_EXAMINED rows_examined, ROWS_SENT rows_sent, NO_INDEX_USED no_idx, SORT_ROWS sort_rows, CREATED_TMP_TABLES tmp FROM performance_schema.events_statements_history_long WHERE SQL_TEXT LIKE '/* $tag */%' ORDER BY EVENT_ID DESC LIMIT 3"
}

[ "${SKIP_INIT:-0}" = 1 ] || init
echo "[$(ts)] 预热"; $M -N -e "SELECT COUNT(*), SUM(amount) FROM orders"

echo; echo "===== Q1 联合索引不带最左列，一定用不上吗？"
idx "ADD INDEX idx_t_u_c(tenant_id,user_id,created_at)"
q Q1a "SELECT id, created_at FROM orders WHERE user_id=4242"
q Q1b "SELECT * FROM orders WHERE user_id=4242"
idx "ADD INDEX idx_t_u_c(tenant_id,user_id,created_at)" "," "ADD INDEX idx_u_c(user_id,created_at)"
q Q1c "SELECT * FROM orders WHERE user_id=4242"

echo; echo "===== Q2 跳过中间列：tenant_id=? AND created_at 范围，key_len 说明了什么？"
idx "ADD INDEX idx_t_u_c(tenant_id,user_id,created_at)"
q Q2a "SELECT * FROM orders WHERE tenant_id=7 AND created_at>='2026-09-01' AND created_at<'2026-09-02'"
# 同一条 SQL 关掉索引下推（ICP），看引擎层到底读了多少条索引记录
q Q2c "SELECT /*+ NO_ICP(orders) */ * FROM orders WHERE tenant_id=7 AND created_at>='2026-09-01' AND created_at<'2026-09-02'"
idx "ADD INDEX idx_t_u_c(tenant_id,user_id,created_at)" "," "ADD INDEX idx_t_c(tenant_id,created_at)"
q Q2b "SELECT * FROM orders WHERE tenant_id=7 AND created_at>='2026-09-01' AND created_at<'2026-09-02'"

echo; echo "===== Q3 等值列在前还是范围列在前？"
idx "ADD INDEX idx_u_c(user_id,created_at)" "," "ADD INDEX idx_c_u(created_at,user_id)"
q Q3a "SELECT * FROM orders FORCE INDEX(idx_u_c) WHERE user_id=4242 AND created_at>='2026-03-01' AND created_at<'2026-06-01'"
q Q3b "SELECT * FROM orders FORCE INDEX(idx_c_u) WHERE user_id=4242 AND created_at>='2026-03-01' AND created_at<'2026-06-01'"
q Q3d "SELECT /*+ NO_ICP(orders) */ * FROM orders FORCE INDEX(idx_c_u) WHERE user_id=4242 AND created_at>='2026-03-01' AND created_at<'2026-06-01'"
q Q3c "SELECT * FROM orders WHERE user_id=4242 AND created_at>='2026-03-01' AND created_at<'2026-06-01'"

echo; echo "===== Q4 等值 + ORDER BY 能免排序，IN 列表为什么又排序了？"
idx "ADD INDEX idx_t_c(tenant_id,created_at)"
q Q4a "SELECT * FROM orders WHERE tenant_id=7 ORDER BY created_at DESC LIMIT 20"
q Q4b "SELECT * FROM orders WHERE tenant_id IN (7,8) ORDER BY created_at DESC LIMIT 20"
q Q4c "(SELECT * FROM orders WHERE tenant_id=7 ORDER BY created_at DESC LIMIT 20) UNION ALL (SELECT * FROM orders WHERE tenant_id=8 ORDER BY created_at DESC LIMIT 20) ORDER BY created_at DESC LIMIT 20"

echo; echo "===== Q5 一升一降的 ORDER BY：8.0 降序索引"
idx "ADD INDEX idx_t_c(tenant_id,created_at)"
q Q5a "SELECT id, tenant_id, created_at FROM orders ORDER BY tenant_id ASC, created_at DESC LIMIT 20"
idx "ADD INDEX idx_t_cd(tenant_id,created_at DESC)"
q Q5b "SELECT id, tenant_id, created_at FROM orders ORDER BY tenant_id ASC, created_at DESC LIMIT 20"

echo; echo "===== Q6 OR 两边各有索引 / 只有一边有索引"
idx "ADD INDEX idx_user(user_id)" "," "ADD INDEX idx_sku(sku_code)"
q Q6a "SELECT * FROM orders WHERE user_id=4242 OR sku_code='SKU01234'"
idx "ADD INDEX idx_user(user_id)"
q Q6b "SELECT * FROM orders WHERE user_id=4242 OR sku_code='SKU01234'"
q Q6c "SELECT * FROM orders WHERE user_id=4242 UNION SELECT * FROM orders WHERE sku_code='SKU01234'"

echo; echo "===== Q7 区分度低的列：status=1 占 60%，强制走索引会怎样？"
idx "ADD INDEX idx_status(status)"
q Q7a "SELECT SUM(amount) FROM orders WHERE status=1"
q Q7b "SELECT SUM(amount) FROM orders FORCE INDEX(idx_status) WHERE status=1"
q Q7c "SELECT SUM(amount) FROM orders WHERE status=9"
q Q7d "SELECT SUM(amount) FROM orders IGNORE INDEX(idx_status) WHERE status=9"
q Q7e "SELECT SUM(amount) FROM orders IGNORE INDEX(idx_status) WHERE status=1"

echo; echo "===== Q8 LIKE 前缀 / 前后模糊 / 覆盖索引全扫"
idx "ADD INDEX idx_sku(sku_code)"
q Q8a "SELECT * FROM orders WHERE sku_code LIKE 'SKU0123%'"
q Q8b "SELECT * FROM orders WHERE sku_code LIKE '%01234'"
q Q8c "SELECT id, sku_code FROM orders WHERE sku_code LIKE '%01234'"

echo; echo "===== Q9 != / NOT IN / IS NULL 一定不走索引？"
idx "ADD INDEX idx_status(status)" "," "ADD INDEX idx_coupon(coupon_code)"
q Q9a "SELECT SUM(amount) FROM orders WHERE status NOT IN (1,2,3)"
q Q9b "SELECT SUM(amount) FROM orders WHERE status <> 1"
q Q9c "SELECT SUM(amount) FROM orders WHERE coupon_code IS NOT NULL"
q Q9d "SELECT SUM(amount) FROM orders WHERE coupon_code IS NULL"
q Q9e "SELECT SUM(amount) FROM orders IGNORE INDEX(idx_coupon) WHERE coupon_code IS NULL"

echo; echo "===== Q10 索引越多越好吗？写入代价（INSERT ... SELECT 20 万行）"
idx   # 先删光 orders 的二级索引：CREATE TABLE ... LIKE 会把源表当前的索引一起复制过去
for n in 0 2 6; do
  $M -e "DROP TABLE IF EXISTS w$n; CREATE TABLE w$n LIKE orders;"
  case $n in
    2) $M -e "ALTER TABLE w2 ADD INDEX(user_id,created_at), ADD INDEX(tenant_id,created_at)";;
    6) $M -e "ALTER TABLE w6 ADD INDEX(user_id,created_at), ADD INDEX(tenant_id,created_at), ADD INDEX(status), ADD INDEX(sku_code), ADD INDEX(coupon_code), ADD INDEX(created_at,user_id)";;
  esac
  for r in 1 2; do
    $M -e "TRUNCATE w$n"
    s=$(date +%s%N); $M -e "INSERT INTO w$n SELECT * FROM orders WHERE id<=200000"; e=$(date +%s%N)
    echo "Q10 二级索引 $n 个 第 $r 次: $(( (e-s)/1000000 )) ms"
  done
  $M -e "ANALYZE TABLE w$n" >/dev/null
  $M -N -e "SELECT 'Q10 size', table_name, ROUND(data_length/1048576,1) data_mb, ROUND(index_length/1048576,1) index_mb FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name='w$n'"
done
$M -e "DROP TABLE w0, w2, w6"
idx
echo "[$(ts)] 全部完成"