#!/usr/bin/env bash
# surinch.com · MySQL Using temporary / Using filesort 演示 v1.0
# 在测试库对比：无合适索引（Using filesort） vs (status, amount) / 覆盖索引
# 会对指定库做 DROP/CREATE INDEX；请勿对生产库直接跑。
# 用法：MYSQL_PWD=... ./mysql-filesort-temporary-demo-v1.0.sh [库名]
# 连接：MYSQL_HOST / MYSQL_TCP_PORT / MYSQL_USER / MYSQL_PWD；本机 socket 也可自行改。
set -euo pipefail
DB="${1:-filesort_lab}"
mysql_base=(mysql --protocol=TCP -N -B)
if [[ -n "${MYSQL_HOST:-}" ]]; then mysql_base+=(-h "$MYSQL_HOST"); fi
if [[ -n "${MYSQL_TCP_PORT:-}" ]]; then mysql_base+=(-P "$MYSQL_TCP_PORT"); fi
if [[ -n "${MYSQL_USER:-}" ]]; then mysql_base+=(-u "$MYSQL_USER"); fi

q() { "${mysql_base[@]}" "$DB" -e "$1" </dev/null; }
qt() { "${mysql_base[@]}" --table "$DB" -e "$1" </dev/null; }

echo "mysql_version=$(q 'SELECT VERSION();')"
echo "orders=$(q 'SELECT COUNT(*) FROM orders;')"

q "ALTER TABLE orders DROP INDEX idx_status_amount;" 2>/dev/null || true
q "ALTER TABLE orders DROP INDEX idx_created_cust_status;" 2>/dev/null || true
q "ANALYZE TABLE orders;" >/dev/null

echo "=== BEFORE: status=paid ORDER BY amount（期望 Using filesort）==="
qt "EXPLAIN SELECT id, customer_id, amount, created_at FROM orders WHERE status='paid' ORDER BY amount DESC LIMIT 50;"
qt "EXPLAIN ANALYZE SELECT id, customer_id, amount, created_at FROM orders WHERE status='paid' ORDER BY amount DESC LIMIT 50;"

echo "=== BEFORE: GROUP BY + ORDER BY 聚合（期望 Using temporary; Using filesort）==="
qt "EXPLAIN SELECT customer_id, status, COUNT(*) cnt, SUM(amount) revenue FROM orders WHERE created_at >= '2025-06-01' AND created_at < '2025-09-01' GROUP BY customer_id, status ORDER BY revenue DESC LIMIT 20;"
qt "EXPLAIN ANALYZE SELECT customer_id, status, COUNT(*) cnt, SUM(amount) revenue FROM orders WHERE created_at >= '2025-06-01' AND created_at < '2025-09-01' GROUP BY customer_id, status ORDER BY revenue DESC LIMIT 20;"

q "CREATE INDEX idx_status_amount ON orders (status, amount);"
q "CREATE INDEX idx_created_cust_status ON orders (created_at, customer_id, status, amount);"
q "ANALYZE TABLE orders;" >/dev/null

echo "=== AFTER: idx_status_amount（期望 Backward index scan，无 filesort）==="
qt "EXPLAIN SELECT id, customer_id, amount, created_at FROM orders WHERE status='paid' ORDER BY amount DESC LIMIT 50;"
qt "EXPLAIN ANALYZE SELECT id, customer_id, amount, created_at FROM orders WHERE status='paid' ORDER BY amount DESC LIMIT 50;"

echo "=== AFTER: 覆盖索引（扫描变快；聚合 ORDER BY 仍可能 temporary+filesort）==="
qt "EXPLAIN SELECT customer_id, status, COUNT(*) cnt, SUM(amount) revenue FROM orders WHERE created_at >= '2025-06-01' AND created_at < '2025-09-01' GROUP BY customer_id, status ORDER BY revenue DESC LIMIT 20;"
qt "EXPLAIN ANALYZE SELECT customer_id, status, COUNT(*) cnt, SUM(amount) revenue FROM orders WHERE created_at >= '2025-06-01' AND created_at < '2025-09-01' GROUP BY customer_id, status ORDER BY revenue DESC LIMIT 20;"
echo "done. Extra 里 Using filesort 消失、TREE 里出现 Index lookup … (reverse) 即排序被索引替代。"