-- 假设：
-- 页大小：16KB
-- 主键 BIGINT：8 字节
-- 指针：6 字节
-- 每个非叶子节点可存：16KB / (8+6) ≈ 1170 条
-- 叶子节点每条数据约 1KB，每页存 16 条

-- 层高 3 的 B+ 树可存：1170 * 1170 * 16 ≈ 2190 万条数据
-- 层高 4 的 B+ 树可存：1170 * 1170 * 1170 * 16 ≈ 256 亿条数据

-- 结论：3~4 层 B+ 树就能支撑绝大多数业务表

-- InnoDB 表结构
CREATE TABLE users (
    id INT PRIMARY KEY,        -- 聚簇索引：叶子节点存储 id,name,email 等完整数据
    name VARCHAR(50),
    email VARCHAR(100)
);

-- 创建二级索引
CREATE INDEX idx_name ON users(name);

-- 通过二级索引查询
SELECT * FROM users WHERE name = '张三';

-- 执行步骤：
-- 1. 从 idx_name 索引树找到 '张三' 对应的主键值 id=1
-- 2. 用主键 id=1 到聚簇索引树查找完整数据行
-- 这就是"回表"

CREATE INDEX idx_a_b_c ON t (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 a = 1 ORDER BY b          -- 用到 a（排序用到 b）
WHERE a IN (1,2) AND b = 1      -- 用到 a,b（IN 会优化）

-- 不能用到索引的场景：
WHERE b = 2                     -- 跳过了 a
WHERE c = 3                     -- 跳过了 a,b
WHERE b = 2 AND c = 3           -- 跳过了 a

-- 部分用到索引的场景：
WHERE a = 1 AND c = 3           -- 只用到 a（b 被跳过，c 用不到）
WHERE a > 1 AND b = 2           -- 只用到 a（范围查询后失效）

-- 业务查询模式
-- Q1: SELECT * FROM orders WHERE user_id = ? AND status = ?
-- Q2: SELECT * FROM orders WHERE user_id = ? ORDER BY create_time DESC
-- Q3: SELECT * FROM orders WHERE status = ? AND create_time > ?

-- 索引设计方案
-- 联合索引 A: idx_user_status (user_id, status)  -- 覆盖 Q1
-- 联合索引 B: idx_user_create (user_id, create_time)  -- 覆盖 Q2
-- 联合索引 C: idx_status_create (status, create_time)  -- 覆盖 Q3

-- 索引：idx_name_age (name, age)

-- 需要回表（因为 * 包含未索引字段）
SELECT * FROM users WHERE name = '张三';

-- 覆盖索引（无需回表，所有字段都在索引中）
SELECT name, age FROM users WHERE name = '张三';

-- 联合索引 idx_name_age (name, age)

-- 没有ICP时：
SELECT * FROM users WHERE name LIKE '张%' AND age = 25;
-- 1. 通过 name LIKE '张%' 找到所有满足条件的索引记录
-- 2. 回表，拿到完整数据行
-- 3. 在服务层过滤 age = 25

-- 启用ICP后：
-- 1. 通过 name LIKE '张%' 遍历索引
-- 2. 在索引遍历过程中，直接判断 age = 25 是否满足
-- 3. 只回表 age=25 的记录

-- 查看 AHI 状态
SHOW ENGINE INNODB STATUS \G;  -- 查看 Hash searches/s 等
SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';  -- 默认 ON

-- 1. 对索引列使用函数
CREATE INDEX idx_create_time ON orders(create_time);
WHERE DATE(create_time) = '2024-01-01'        -- 索引失效
-- 优化：
WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'

-- 2. 隐式类型转换
CREATE INDEX idx_phone ON users(phone);  -- phone 是 VARCHAR
WHERE phone = 13812345678                     -- 索引失效（数字转字符串）
-- 优化：
WHERE phone = '13812345678'

-- 3. LIKE 以通配符开头
CREATE INDEX idx_name ON users(name);
WHERE name LIKE '%张三%'                      -- 索引失效
-- 优化：
WHERE name LIKE '张三%'                       -- 可以用到索引

-- 4. 联合索引违反最左前缀
CREATE INDEX idx_a_b_c ON t (a, b, c);
WHERE b = 1 AND c = 2                         -- 跳过了 a，索引失效

-- 5. 使用 != 或 <>
WHERE status != 1                              -- 索引失效（除外特殊情况）
-- 优化：
WHERE status IN (0, 2, 3)

-- 6. IS NOT NULL 可能导致失效
CREATE INDEX idx_email ON users(email);
WHERE email IS NOT NULL                        -- 可能索引失效
-- 优化：根据实际业务区分度判断

-- 7. OR 条件中有一个字段无索引
CREATE INDEX idx_name ON users(name);
WHERE name = '张三' OR age = 25               -- age 无索引，整个 OR 条件索引失效
-- 优化：给 age 也建索引，或用 UNION
WHERE name = '张三' UNION ALL SELECT * FROM users WHERE age = 25

-- 8. NOT IN / NOT EXISTS
WHERE id NOT IN (1, 2, 3)                      -- 索引失效
-- 优化：用 NOT EXISTS 或业务绕过

-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE id = 1;

-- 详细格式（JSON）
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE id = 1\G

-- MySQL 8.0.16+ 实际执行分析
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

-- 查看已执行的 SQL 执行计划（performance_schema）
SELECT * FROM performance_schema.events_statements_history
WHERE digest_text LIKE '%SELECT%users%'\G

-- const：主键或唯一索引等值查询，最多返回一行
-- 说明索引利用最充分，性能最优
EXPLAIN SELECT * FROM users WHERE id = 1;
-- type: const, key: PRIMARY

-- eq_ref：JOIN 时使用主键或唯一索引关联
-- 说明关联命中索引
EXPLAIN SELECT * FROM orders o
JOIN users u ON o.user_id = u.id;
-- 如果 u.id 是主键，type = eq_ref

-- ref：普通索引等值查询
-- 说明索引正确命中，但可能返回多行
EXPLAIN SELECT * FROM employee WHERE dept_id = 1;
-- type: ref, key: idx_dept_id

-- range：索引范围查询（>、<、BETWEEN、IN）
-- 说明命中索引做范围扫描
EXPLAIN SELECT * FROM employee WHERE salary BETWEEN 10000 AND 20000;
-- type: range, key: idx_salary

-- index：全索引扫描
-- 说明遍历了整个索引树，通常比全表扫描稍好
EXPLAIN SELECT COUNT(id) FROM employee;
-- type: index, key: PRIMARY, Extra: Using index

-- ALL：全表扫描（最差）
-- 说明没有使用索引，需要优化
EXPLAIN SELECT * FROM employee WHERE name = '张三';
-- type: ALL（name 无索引时）

-- 实战：通过 type 判断索引质量
-- 优秀：const, eq_ref, ref
-- 一般：range
-- 需优化：index, ALL

-- 例：如果 type 从 ref 降到 ALL，说明索引失效或缺失

-- 创建测试表
CREATE TABLE t_keylen (
    id INT PRIMARY KEY AUTO_INCREMENT,
    a INT,            -- 4字节 + NULL标记1字节
    b VARCHAR(100),   -- 变长 + NULL标记1字节 + 长度2字节
    c DATETIME,       -- 5字节 + NULL标记1字节
    INDEX idx_a_b_c (a, b, c)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 实战分析 key_len

-- 假设 utf8mb4 字符集（每个字符最多4字节）

-- 场景1：只用到了 a 列
EXPLAIN SELECT * FROM t_keylen WHERE a = 1;
-- key_len: 5（INT 4字节 + NULL标记1字节）

-- 场景2：用到了 a, b 列
EXPLAIN SELECT * FROM t_keylen WHERE a = 1 AND b = 'hello';
-- key_len: 410（INT 5 + VARCHAR(100)×4+2+1 = 408 + 5 = ？）
-- 实际：a=5 + (b: 100×4+2+1=403) = 408

-- 场景3：用到了 a, b, c 列
EXPLAIN SELECT * FROM t_keylen WHERE a = 1 AND b = 'hello' AND c = '2024-01-01';
-- key_len: 414（a=5 + b=403 + c=6 = 414）

-- 场景4：a 范围查询后，b、c 用不到
EXPLAIN SELECT * FROM t_keylen WHERE a > 1 AND b = 'hello';
-- key_len: 5（只用了 a 列）

-- 如果 key_len 明显小于联合索引的总长度
-- 说明有部分索引列没有用上（范围查询或跳过了中间列）

-- 例：联合索引 idx_a_b_c 总长假设为 414
EXPLAIN SELECT * FROM t_keylen WHERE a = 1 AND c = '2024-01-01';
-- key_len: 5（只用到了 a，c 因为跳过了 b 而用不到）

-- 1. Using index（最优）
-- 查询的所有字段都在索引中，无需回表
EXPLAIN SELECT id, a, b FROM t_keylen WHERE a = 1;
-- Extra: Using index

-- 2. Using filesort（需优化）
-- ORDER BY 字段不在索引中，MySQL 需要额外排序
EXPLAIN SELECT * FROM t_keylen WHERE a = 1 ORDER BY c;
-- Extra: Using filesort（联合索引是 a,b,c，跳过了 b 导致排序用不到索引）

-- 3. Using temporary（需优化）
-- GROUP BY 没有可用索引时使用临时表
EXPLAIN SELECT dept_id, COUNT(*) FROM employee GROUP BY dept_id;
-- 如果 dept_id 无索引：Extra: Using temporary; Using filesort

-- 4. Using join buffer（需优化）
-- JOIN 关联字段没有索引
EXPLAIN SELECT * FROM orders o LEFT JOIN users u ON o.user_id = u.id;
-- 如果 o.user_id 无索引：Extra: Using join buffer

-- 准备测试数据
CREATE TABLE emp (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    dept_id INT,
    salary DECIMAL(10,2),
    create_time DATETIME,
    INDEX idx_dept_salary (dept_id, salary)
);

-- 问题1：索引完全未使用（type = ALL）
EXPLAIN SELECT * FROM emp WHERE name = '张三';
-- type: ALL, possible_keys: NULL, key: NULL
-- 诊断：name 字段没有索引，需要加索引

-- 问题2：联合索引只用到了部分列（key_len 偏小）
EXPLAIN SELECT * FROM emp WHERE dept_id = 1 ORDER BY salary;
-- type: ref, key: idx_dept_salary, key_len: 5
-- 诊断：WHERE 用到 dept_id，ORDER BY 用了 salary 排序（索引有序性）
-- key_len=5 只用了 dept_id（INT + NULL），排序不增加 key_len

-- 问题3：索引失效（隐式类型转换）
CREATE INDEX idx_name ON emp(name);
EXPLAIN SELECT * FROM emp WHERE name = 123;
-- type: ALL, key: NULL
-- 诊断：name 是 VARCHAR，传入数字导致隐式类型转换，索引失效

-- 问题4：范围查询导致后续列索引失效
EXPLAIN SELECT * FROM emp WHERE dept_id > 1 AND salary = 10000;
-- type: range, key: idx_dept_salary, key_len: 5
-- 诊断：key_len=5 只用了 dept_id，range 后的 salary 用不到索引

-- 问题5：filesort 需要优化
EXPLAIN SELECT * FROM emp WHERE dept_id = 1 ORDER BY create_time;
-- Extra: Using filesort
-- 诊断：ORDER BY 字段不在索引 idx_dept_salary 中，需要加索引

-- 问题6：覆盖索引效果
EXPLAIN SELECT dept_id, salary FROM emp WHERE dept_id = 1;
-- Extra: Using index
-- 诊断：查询字段都在 idx_dept_salary 中，无需回表，性能好

-- 问题7：索引下推效果
EXPLAIN SELECT * FROM emp WHERE dept_id = 1 AND salary > 8000;
-- Extra: Using index condition
-- 诊断：dept_id 用于索引查找，salary > 8000 在索引层过滤，减少回表

-- 业务表：订单表
CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(32),
    user_id BIGINT,
    status TINYINT,
    amount DECIMAL(12,2),
    create_time DATETIME,
    pay_time DATETIME,
    INDEX idx_order_no (order_no)
);

-- 常见查询模式分析：
-- Q1: 用户查询自己的订单列表
SELECT * FROM orders WHERE user_id = ? ORDER BY create_time DESC;
-- 索引建议：idx_user_create (user_id, create_time)

-- Q2: 按状态查询订单（运营后台）
SELECT * FROM orders WHERE status = ? ORDER BY create_time DESC;
-- 索引建议：idx_status_create (status, create_time)

-- Q3: 按时间范围查询订单
SELECT * FROM orders WHERE create_time BETWEEN ? AND ?;
-- 索引建议：idx_create_time (create_time)

-- Q4: 查询某个用户某段时间的订单
SELECT * FROM orders
WHERE user_id = ? AND create_time BETWEEN ? AND ?;
-- 用了 idx_user_create 即可满足

-- 最终索引方案（避免冗余）：
-- 1. idx_user_create (user_id, create_time)  -- 覆盖 Q1, Q4
-- 2. idx_status_create (status, create_time)  -- 覆盖 Q2
-- 3. idx_order_no (order_no)                  -- 唯一订单号查询
-- 不需要单独的 idx_create_time，因为前两个联合索引已覆盖

-- 传统分页（深度分页时越来越慢）
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;
-- MySQL 会查到 100020 条，丢弃前 100000 条

-- 优化1：子查询延迟关联
SELECT * FROM orders
WHERE id > (
    SELECT id FROM orders ORDER BY id LIMIT 100000, 1
)
LIMIT 20;

-- 优化2：覆盖索引 + JOIN
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 100000, 20
) tmp ON o.id = tmp.id;