-- InnoDB 聚簇索引特点：
-- 1. 数据行直接存储在叶子节点中
-- 2. 主键索引就是聚簇索引
-- 3. 如果没有主键，使用第一个唯一非空索引
-- 4. 如果没有唯一索引，InnoDB 会生成隐藏的 ROW_ID

-- 聚簇索引结构：
-- 叶子节点存储完整的数据行
-- +--------+-------------+-----+-----+
-- | id (PK)| name        | age | ... |
-- +--------+-------------+-----+-----+
-- | 1      | zhangsan    | 25  | ... |
-- | 2      | lisi        | 30  | ... |
-- | 3      | wangwu      | 28  | ... |
-- +--------+-------------+-----+-----+

CREATE TABLE users (
    id INT PRIMARY KEY,  -- 聚簇索引
    name VARCHAR(100),
    age INT,
    INDEX idx_age (age)   -- 非聚簇索引
);

-- 非聚簇索引（辅助索引）特点：
-- 1. 叶子节点存储索引键 + 主键值
-- 2. 一个表可以有多个非聚簇索引
-- 3. 查询时需要先通过非聚簇索引找到主键
-- 4. 再通过主键到聚簇索引中获取完整数据（回表）

-- 非聚簇索引 idx_age 结构：
-- +--------+--------+
-- | age    | id(PK) |
-- +--------+--------+
-- | 25     | 1      |
-- | 28     | 3      |
-- | 30     | 2      |
-- +--------+--------+

-- 查询 SELECT * FROM users WHERE age = 25：
-- 1. 在 idx_age 中找到 age=25 的记录，获取 id=1
-- 2. 在聚簇索引中找到 id=1 的完整数据
-- 3. 返回完整数据行

-- 覆盖索引：索引包含查询所需的所有列
-- 不需要回表，直接从索引中返回数据

-- 创建覆盖索引
CREATE INDEX idx_age_name ON users(age, name);

-- 使用覆盖索引（不需要回表）
SELECT age, name FROM users WHERE age > 25;
-- EXPLAIN: Extra = Using index

-- 需要回表的查询
SELECT * FROM users WHERE age > 25;
-- EXPLAIN: Extra = Using index condition

-- 验证是否使用覆盖索引
EXPLAIN SELECT age, name FROM users WHERE age = 25;
-- type=ref, key=idx_age_name, Extra=Using index

-- 回表的完整过程：
-- 1. 在非聚簇索引树中查找
-- 2. 获取满足条件的主键值
-- 3. 使用主键值在聚簇索引中查找完整数据
-- 4. 返回完整的用户数据

-- 示例表结构：
CREATE TABLE orders (
    id INT PRIMARY KEY,           -- 聚簇索引
    order_no VARCHAR(20),       -- 唯一索引
    customer_id INT,
    amount DECIMAL(10,2),
    status VARCHAR(10),
    created_at DATETIME,
    INDEX idx_customer (customer_id) -- 非聚簇索引
);

-- 执行 SELECT * FROM orders WHERE customer_id = 100
-- 1. 在 idx_customer 中找到 customer_id=100 的记录
-- 2. 获取主键 id（如 id=500）
-- 3. 在聚簇索引中查找 id=500 的完整记录
-- 4. 返回所有列数据

EXPLAIN SELECT * FROM orders WHERE customer_id = 100;
-- type: ref, key: idx_customer
-- Extra: Using index condition (需要回表)

-- 回表的性能影响：
-- 1. 增加磁盘 IO：每次回表可能产生一次磁盘读取
-- 2. 随机 IO：聚簇索引中查找是随机 IO
-- 3. 回表次数 = 索引命中的记录数

-- 回表次数过多的例子
SELECT * FROM orders WHERE customer_id > 100;
-- 假设 customer_id > 100 有 10000 条记录
-- 需要回表 10000 次，性能很差

EXPLAIN SELECT * FROM orders WHERE customer_id > 100;
-- type: range, key: idx_customer
-- rows: 10000 (预计回表次数)

-- 优化方案：使用覆盖索引
CREATE INDEX idx_cover ON orders(customer_id, order_no, amount, status);

-- 如果查询的列都在索引中，不需要回表
SELECT customer_id, order_no, amount, status
FROM orders WHERE customer_id > 100;

EXPLAIN SELECT customer_id, order_no, amount, status
FROM orders WHERE customer_id > 100;
-- Extra: Using index (覆盖索引，无需回表)

-- 方法 1：使用覆盖索引
-- 将需要查询的列都包含在索引中

-- 方法 2：优化查询条件
-- 避免返回大量数据
SELECT id, order_no FROM orders WHERE customer_id = 100
LIMIT 100;  -- 限制返回数量

-- 方法 3：使用主键查询
SELECT * FROM orders WHERE id = 500;
-- 直接在聚簇索引中查找，不需要回表

-- 方法 4：分页优化
-- 避免 OFFSET 过大导致的深度分页问题

-- 低效（回表次数多）：
SELECT * FROM orders ORDER BY id LIMIT 10000, 10;

-- 高效（基于主键范围）：
SELECT * FROM orders WHERE id > 10000 LIMIT 10;
-- 上一页最后一条记录的 id 作为起点

-- 方法 5：使用延迟关联
SELECT o.*
FROM orders o
INNER JOIN (
    SELECT id FROM orders WHERE customer_id = 100
    LIMIT 100
) t ON o.id = t.id;

-- InnoDB 数据页结构（默认 16KB）：
-- +------------------+
-- | File Header      | 38 bytes - 文件头
-- +------------------+
-- | Page Header      | 56 bytes - 页头
-- +------------------+
-- | Infimum + Supremum| 26 bytes - 虚拟行
-- +------------------+
-- | User Records     | 变长 - 用户数据
-- +------------------+
-- | Free Space       | 变长 - 空闲空间
-- +------------------+
-- | Page Directory   | 变长 - 页目录
-- +------------------+
-- | File Trailer     | 8 bytes - 文件尾
-- +------------------+

-- 页内记录存储：
-- 1. 记录头信息（变长字段长度、NULL 标志等）
-- 2. 记录实际数据
-- 3. 记录通过 Page Directory 实现二分查找

-- B+ Tree 索引页结构：
-- +------------------+
-- | File Header      | 38 bytes
-- +------------------+
-- | Page Header      | 56 bytes
-- +------------------+
-- | User Records     | 索引记录
-- |  (非叶子节点)    | (最小键，指向子页指针)
-- |  (叶子节点)      | (键值，主键/行指针)
-- +------------------+
-- | Free Space       | 空闲空间
-- +------------------+
-- | Page Directory   | 槽位目录（记录分组）
-- +------------------+
-- | File Trailer     | 8 bytes
-- +------------------+

-- 非叶子节点记录格式：
-- | 记录头 | 索引键值 | 页面指针 |

-- 叶子节点记录格式（聚簇索引）：
-- | 记录头 | 主键值 | 其他列数据 |

-- 叶子节点记录格式（非聚簇索引）：
-- | 记录头 | 索引键值 | 主键值 |

-- 复合索引 (a, b, c) 的使用规则：
WHERE a = 1                     -- ✅ 使用索引 a
WHERE a = 1 AND b = 2            -- ✅ 使用索引 a, b
WHERE a = 1 AND b = 2 AND c = 3  -- ✅ 使用索引 a, b, c
WHERE b = 2                     -- ❌ 不使用索引
WHERE b = 2 AND c = 3           -- ❌ 不使用索引
WHERE a = 1 AND c = 3           -- ⚠️ 只使用索引 a

-- 原理：复合索引按从左到右的顺序构建 B+ Tree
-- 必须先使用前面的列，才能利用后续列的索引

-- 索引下推原理：
-- 在 MySQL 5.6+ 之前：
-- 1. 通过索引找到满足 a=1 的主键
-- 2. 回表获取完整数据
-- 3. 在 server 层过滤 b>100 的条件

-- MySQL 5.6+ 索引下推：
-- 1. 通过索引找到满足 a=1 的主键
-- 2. 在存储引擎层直接过滤 b>100 的条件
-- 3. 只对满足所有条件的记录回表

-- 示例索引：(a, b)
SELECT * FROM t WHERE a = 1 AND b > 100;

-- 验证索引下推
EXPLAIN SELECT * FROM t WHERE a = 1 AND b > 100;
-- Extra: Using index condition (使用了索引下推)

-- 索引合并原理：
-- MySQL 可以同时使用多个索引
-- 然后合并结果集

-- 索引合并类型：
-- 1. intersection（交集）- 多个索引结果取交集
-- 2. union（并集）- 多个索引结果取并集
-- 3. sort_union（排序并集）- 先排序再取并集

-- 示例：
SELECT * FROM users WHERE age = 25 AND name = 'zhangsan';

EXPLAIN SELECT * FROM users WHERE age = 25 AND name = 'zhangsan';
-- type: index_merge
-- key: idx_age, idx_name
-- Extra: Using intersect(idx_age,idx_name)

-- EXPLAIN 关键字段：
-- type: 连接类型（从好到差）
--   system > const > eq_ref > ref > range > index > ALL
--   const: 主键/唯一索引等值查询
--   ref: 非唯一索引等值查询
--   range: 索引范围查询
--   index: 全索引扫描
--   ALL: 全表扫描（需要优化）

-- key: 实际使用的索引
-- rows: 预计扫描的行数
-- Extra: 额外信息
--   Using index: 使用覆盖索引
--   Using where: 使用 WHERE 条件过滤
--   Using index condition: 使用索引下推
--   Using filesort: 需要额外排序
--   Using temporary: 使用临时表

EXPLAIN SELECT * FROM users WHERE age = 25;

-- 分析结果：
-- type: ref (使用了非唯一索引)
-- key: idx_age
-- rows: 10 (预计扫描 10 行)

-- 索引设计原则：
-- 1. 区分度高的列放在前面
CREATE INDEX idx_status_user ON orders(user_id, status);
-- ✅ user_id 区分度高，放前面

-- 2. 考虑查询的 WHERE 条件顺序
CREATE INDEX idx_age_name ON users(age, name);
-- 适合：WHERE age = ? 或 WHERE age = ? AND name = ?

-- 3. 控制索引数量
-- - 索引占用空间
-- - 影响 INSERT/UPDATE/DELETE 性能
-- - 建议单个表索引数 < 5

-- 4. 使用覆盖索引减少回表
CREATE INDEX idx_user_cover ON orders(user_id, status, created_at);
-- 覆盖：SELECT user_id, status FROM orders WHERE user_id = ?

-- 5. 避免在区分度低的列上建索引
-- 如：性别、状态（0/1）等