#!/usr/bin/env bash
# surinch.com · MySQL 覆盖索引 vs 回表 演示 v1.0
# 在测试库对比：仅 (uid) 二级索引（回表） vs (uid, amount, status) 覆盖索引（Using index）
# 会对指定库做 DROP/CREATE INDEX；请勿对生产库直接跑。默认库名 shop。
# 用法：MYSQL_PWD=... ./mysql-covering-index-explain-demo-v1.0.sh [库名]
# 连接：MYSQL_HOST / MYSQL_TCP_PORT / MYSQL_USER / MYSQL_PWD
set -euo pipefail
DB="${1:-shop}"
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; }
qg() { "${mysql_base[@]}" "$DB" -e "$1\G" </dev/null; }

echo "mysql_version=$(q 'SELECT VERSION();')"
echo "users=$(q 'SELECT COUNT(*) FROM users;') orders=$(q 'SELECT COUNT(*) FROM orders;')"
UID_PICK="$(q 'SELECT uid FROM orders GROUP BY uid ORDER BY COUNT(*) DESC LIMIT 1;')"
echo "demo_uid=${UID_PICK} rows=$(q "SELECT COUNT(*) FROM orders WHERE uid=${UID_PICK};")"

q "ALTER TABLE orders DROP INDEX idx_orders_uid;" 2>/dev/null || true
q "ALTER TABLE orders DROP INDEX idx_orders_uid_covering;" 2>/dev/null || true
q "CREATE INDEX idx_orders_uid ON orders (uid);"
q "ANALYZE TABLE orders;" >/dev/null

echo "=== BEFORE covering (idx_orders_uid) ==="
qg "EXPLAIN SELECT id, amount, status FROM orders WHERE uid=${UID_PICK};"
qg "EXPLAIN ANALYZE SELECT id, amount, status FROM orders WHERE uid=${UID_PICK};"

q "ALTER TABLE orders DROP INDEX idx_orders_uid;"
q "CREATE INDEX idx_orders_uid_covering ON orders (uid, amount, status);"
q "ANALYZE TABLE orders;" >/dev/null

echo "=== AFTER covering (idx_orders_uid_covering) ==="
qg "EXPLAIN SELECT id, amount, status FROM orders WHERE uid=${UID_PICK};"
qg "EXPLAIN ANALYZE SELECT id, amount, status FROM orders WHERE uid=${UID_PICK};"
echo "done. Extra 出现 Using index 即覆盖索引命中；TREE 格式会写 Covering index lookup。"