-- 聚簇索引结构示意：
-- 主键索引叶子节点存储：
-- | PK(id) | col1 | col2 | col3 | ... | 完整行数据 |

-- 查询走主键：直接从聚簇索引叶子节点获取数据（最快）
SELECT * FROM users WHERE id = 100;

-- 二级索引 idx_email 的结构：
-- 叶子节点存储：| email | id(主键) |

-- 查询过程：
SELECT * FROM users WHERE email = '[email protected]';
-- 1. 在 idx_email B+Tree 中找到 email='[email protected]' → 获取 id=5
-- 2. 用 id=5 去聚簇索引（主键索引）中查找完整行 → 这个过程叫"回表"

-- 假设有索引：INDEX idx_email_status (email, status)

-- 覆盖索引（不回表）
SELECT email, status FROM users WHERE email = '[email protected]';
-- 索引叶子节点已包含 email 和 status，直接返回

-- 非覆盖索引（需要回表）
SELECT email, status, username FROM users WHERE email = '[email protected]';
-- 索引中没有 username，需要回表到聚簇索引获取

-- EXPLAIN 中的 Using index 表示覆盖索引
EXPLAIN SELECT email, status FROM users WHERE email = '[email protected]';
-- Extra: Using index ← 使用了覆盖索引

-- 联合索引：INDEX idx_abc (a, b, c)
CREATE TABLE test_index (
    id INT PRIMARY KEY,
    a INT,
    b INT,
    c INT,
    d INT,
    INDEX idx_abc (a, b, c)
);

-- 为 email 列创建前缀索引（只索引前 10 个字符）
ALTER TABLE users ADD INDEX idx_email_prefix (email(10));

-- 如何选择合适的前缀长度？
-- 目标：选择性（distinct 比例）接近全列
SELECT 
    COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS sel_5,
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel_10,
    COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) AS sel_15,
    COUNT(DISTINCT email) / COUNT(*) AS sel_full
FROM users;
-- 选择最接近 sel_full 且值稳定的前缀长度

-- ✅ 适合创建索引的场景
-- 1. WHERE 条件频繁使用的列
SELECT * FROM orders WHERE user_id = 100;      -- INDEX idx_user_id

-- 2. JOIN 关联列
SELECT * FROM orders o JOIN users u ON o.user_id = u.id;  -- orders.user_id 需要索引

-- 3. ORDER BY / GROUP BY 列
SELECT * FROM orders ORDER BY created_at DESC;  -- INDEX idx_created_at

-- 4. 高选择性的列（distinct 值多）
-- email、手机号 适合建索引
-- status、gender 不适合单独建索引（选择性低）

-- 5. 覆盖索引（查询列都在索引中）

-- 场景：电商订单查询
-- 查询模式：
-- 1. WHERE user_id = ? AND status = ? ORDER BY created_at DESC
-- 2. WHERE status = ? AND created_at BETWEEN ? AND ?
-- 3. WHERE user_id = ? ORDER BY created_at DESC

-- 推荐索引设计
ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, created_at);
ALTER TABLE orders ADD INDEX idx_status_time (status, created_at);

-- 查看表的所有索引
SHOW INDEX FROM orders;

-- 更详细的索引信息
SELECT 
    INDEX_NAME,
    GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS columns,
    NON_UNIQUE,
    INDEX_TYPE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'myapp' AND TABLE_NAME = 'orders'
GROUP BY INDEX_NAME, NON_UNIQUE, INDEX_TYPE;

-- 创建普通索引
ALTER TABLE orders ADD INDEX idx_user_id (user_id);

-- 创建唯一索引
ALTER TABLE users ADD UNIQUE INDEX uk_email (email);

-- 创建联合索引
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

-- 创建降序索引
ALTER TABLE orders ADD INDEX idx_created_desc (created_at DESC);

-- 创建不可见索引（测试索引是否有用）
ALTER TABLE orders ADD INDEX idx_test (column_name) INVISIBLE;

-- 将索引设为不可见（优化器不使用，但保留索引结构）
ALTER TABLE orders ALTER INDEX idx_test INVISIBLE;

-- 将索引设为可见
ALTER TABLE orders ALTER INDEX idx_test VISIBLE;

-- 删除索引
ALTER TABLE orders DROP INDEX idx_user_id;

-- 使用 DROP INDEX 语法
DROP INDEX idx_user_id ON orders;

-- 查看索引使用情况（需要开启 Performance Schema）
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_READ,
    COUNT_WRITE,
    COUNT_FETCH
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'myapp'
ORDER BY COUNT_READ DESC;

-- 查找从未使用过的索引（可以考虑删除）
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'myapp'
  AND INDEX_NAME IS NOT NULL
  AND COUNT_READ = 0
  AND INDEX_NAME != 'PRIMARY';

-- ❌ 索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2026;

-- ✅ 索引有效
SELECT * FROM users WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

-- ❌ 索引失效
SELECT * FROM users WHERE phone = 13800000000;  -- phone 是 VARCHAR

-- ✅ 索引有效
SELECT * FROM users WHERE phone = '13800000000';

-- ❌ 索引失效
SELECT * FROM users WHERE username LIKE '%san%';

-- ✅ 索引有效
SELECT * FROM users WHERE username LIKE 'zhang%';

-- ❌ 索引失效
SELECT * FROM users WHERE status != 1;  -- 不等于可能全表扫描

-- ✅ 改写为
SELECT * FROM users WHERE status IN (0, 2, 3);

-- 强制使用某个索引
SELECT * FROM orders FORCE INDEX (idx_user_id) WHERE user_id = 100;

-- 忽略某个索引
SELECT * FROM orders IGNORE INDEX (idx_user_id) WHERE user_id = 100;

-- 建议使用某个索引
SELECT * FROM orders USE INDEX (idx_user_id) WHERE user_id = 100;

-- MySQL 8.0+ 支持降序索引
ALTER TABLE orders ADD INDEX idx_created_desc (created_at DESC);

-- 查询最新订单（天然使用降序索引，无需 filesort）
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20;

-- 想删除某个索引，但不确定是否有用
-- 先设为不可见，观察一段时间
ALTER TABLE orders ALTER INDEX idx_suspicious INVISIBLE;

-- 监控是否有查询受影响
-- 如果没问题，再真正删除
ALTER TABLE orders DROP INDEX idx_suspicious;

-- 如果有问题，恢复可见
ALTER TABLE orders ALTER INDEX idx_suspicious VISIBLE;

-- 订单表常用查询模式及索引设计

-- 查询 1：用户的所有订单
-- SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC
ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);

-- 查询 2：按状态查询待处理订单
-- SELECT * FROM orders WHERE status = ? AND created_at > ?
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

-- 查询 3：订单号查询
-- SELECT * FROM orders WHERE order_no = ?
ALTER TABLE orders ADD UNIQUE INDEX uk_order_no (order_no);

-- 查询 4：查询用户的待支付订单（覆盖索引）
-- SELECT id, order_no, total_amount FROM orders WHERE user_id = ? AND status = 0
ALTER TABLE orders ADD INDEX idx_user_status_amount (user_id, status, total_amount);

-- 评估索引占用空间
SELECT 
    DATABASE_NAME,
    TABLE_NAME,
    INDEX_NAME,
    STAT_VALUE * @@innodb_page_size / 1024 / 1024 AS index_size_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size'
  AND DATABASE_NAME = 'myapp'
ORDER BY STAT_VALUE DESC;