-- 使用 EXPLAIN 分析查询执行计划
EXPLAIN SELECT u.name, o.order_id, o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.age > 25 AND o.status = 'completed';

-- 更详细的分析（包含执行时间）
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;

-- 联表查询示例：users -> orders -> order_items -> products

-- 假设：users表100行，orders表10000行，order_items表50000行，products表1000行
-- 优化原则：小表驱动大表，有索引的表放在内层

-- ❌ 错误的连接顺序（大表驱动小表）
SELECT *
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

-- ✅ 正确的连接顺序（小表驱动大表）
SELECT *
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

-- 查看优化器选择的执行计划
EXPLAIN SELECT *
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

-- 强制使用小表作为驱动表
SELECT STRAIGHT_JOIN *
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

-- 不同的JOIN类型执行过程：

-- 1. INNER JOIN（内连接）
--    只返回两个表中满足连接条件的记录
SELECT u.name, o.order_id
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- 执行过程：遍历驱动表，查找匹配行，组合返回

-- 2. LEFT JOIN（左外连接）
--    返回左表所有记录，右表无匹配则显示NULL
SELECT u.name, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- 执行过程：遍历左表每行，查找右表匹配，无匹配则NULL填充

-- 3. RIGHT JOIN（右外连接）
--    返回右表所有记录，左表无匹配显示NULL
SELECT u.name, o.order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- 4. CROSS JOIN（笛卡尔积）
--    返回两表所有记录的组合
SELECT u.name, o.order_id
FROM users u
CROSS JOIN orders o;
-- 执行过程：左表每行 × 右表每行 = 结果集

-- 5. 自连接
--    表与自身连接，用于层级数据
SELECT a.name AS employee, b.name AS manager
FROM employees a
LEFT JOIN employees b ON a.manager_id = b.id;

-- 联表查询性能优化技巧：

-- 1. 确保连接列有索引
--    索引加速连接条件的匹配
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_order_id ON order_items(order_id);
CREATE INDEX idx_product_id ON order_items(product_id);

-- 2. 使用覆盖索引
--    减少回表操作
CREATE INDEX idx_covering ON orders(user_id, status, total_amount);
SELECT user_id, status, total_amount
FROM orders WHERE user_id = 1;

-- 3. 减少连接数量
--    过多的表连接会显著降低性能
--    将不需要的表移除，或使用子查询替代

-- 4. 合理使用连接类型
--    优先使用INNER JOIN，避免笛卡尔积
SELECT u.name, o.order_id
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed';

-- 5. 添加过滤条件
--    先过滤减少数据量，再连接
SELECT u.name, o.order_id
FROM users u
INNER JOIN (
    SELECT * FROM orders WHERE status = 'completed'
) o ON u.id = o.user_id;

-- 6. 使用EXPLAIN分析
EXPLAIN FORMAT=JSON
SELECT u.name, o.order_id
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed';

-- INSERT语句执行详解：

-- 示例表结构
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 执行过程：
-- 1. 解析SQL，验证语法和表结构
-- 2. 检查用户权限
-- 3. 获取MDL锁（Metadata Lock）保护表结构
-- 4. 开启BUF_PAGE，定位插入位置
-- 5. 检查唯一索引约束（记录到undo log）
-- 6. 修改数据页，插入新记录
-- 7. 写入redo log（物理日志，确保持久性）
-- 8. 写入binlog（逻辑日志，用于复制）
-- 9. 提交事务（两阶段提交）
-- 10. 释放锁，返回自增ID

-- 普通INSERT
INSERT INTO users (name, email, age) VALUES ('张三', 'zhangsan@example.com', 25);

-- 批量INSERT
INSERT INTO users (name, email, age) VALUES 
    ('李四', 'lisi@example.com', 30),
    ('王五', 'wangwu@example.com', 28);

-- INSERT...SELECT
INSERT INTO users (name, email, age)
SELECT name, email, age FROM old_users;

-- INSERT...ON DUPLICATE KEY UPDATEUPS（ERT）
INSERT INTO users (name, email, age) VALUES ('张三', 'zhangsan@example.com', 30)
ON DUPLICATE KEY UPDATE age = 30;

-- REPLACE（存在则删除后插入）
REPLACE INTO users (id, name, email, age) VALUES (1, '张三', 'zhangsan@example.com', 28);

-- InnoDB事务日志机制：

-- 1. UNDO日志（回滚日志）
--    记录修改前的数据，用于回滚
--    INSERT: 记录插入的主键
--    UPDATE: 记录修改前的数据
--    DELETE: 记录删除前的完整数据

-- 2. REDO日志（重做日志）
--    记录修改后的数据，用于崩溃恢复
--    循环写入，先于数据页刷新到磁盘
--    innodb_log_file_size 控制大小
--    innodb_log_buffer_size 控制缓冲区大小

-- 3. BINLOG（二进制日志）
--    记录SQL语句，用于主从复制
--    statement: 记录SQL语句
--    row: 记录行数据变化
--    mixed: 混合模式

-- 查看事务相关配置
SHOW VARIABLES LIKE 'innodb_log%';
SHOW VARIABLES LIKE 'binlog_format';

-- 两阶段提交（保证数据一致性）
-- 1. .prepare阶段：写redo log，标记为prepare
-- 2. 写入binlog
-- 3. .commit阶段：标记为commit，完成提交

-- UPDATE语句执行详解：

-- 示例
UPDATE users SET age = 30, name = '张三' WHERE id = 1;

-- 执行过程：
-- 1. 解析SQL，确定要修改的列和条件
-- 2. 根据WHERE条件定位要修改的行
-- 3. 对要修改的行加排他锁（X锁）
-- 4. 记录旧值到UNDO日志（用于回滚）
-- 5. 修改数据页中的数据
-- 6. 如果有索引，更新索引
-- 7. 写入REDO日志
-- 8. 写入BINLOG
-- 9. 提交事务
-- 10. 返回影响行数

-- 简单UPDATE
UPDATE users SET age = 30 WHERE id = 1;

-- 条件UPDATE
UPDATE users SET age = age + 1 WHERE age < 30;

-- 关联UPDATE
UPDATE users u
INNER JOIN orders o ON u.id = o.user_id
SET u.total_orders = u.total_orders + 1,
    o.updated_at = NOW()
WHERE o.status = 'completed';

-- ORDER BY + LIMIT UPDATE
UPDATE users SET status = 'inactive' 
WHERE last_login < '2023-01-01'
ORDER BY last_login ASC LIMIT 100;

-- UPDATE语句的锁机制：

-- 1. 行锁（Record Lock）
--    锁定满足条件的行
UPDATE users SET age = 30 WHERE id = 1;
--    只锁定id=1这一行

-- 2. 间隙锁（Gap Lock）
--    锁定记录之间的间隙，防止幻读
UPDATE users SET age = 30 WHERE age > 20 AND age < 30;
--    锁定(20,30)这个间隙

-- 3. 临键锁（Next-Key Lock）
--    行锁+间隙锁的组合

-- 查看当前锁
SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 死锁示例
-- 会话1: UPDATE users SET age = 30 WHERE id = 1;  (获取id=1锁)
-- 会话2: UPDATE users SET age = 31 WHERE id = 2;  (获取id=2锁)
-- 会话1: UPDATE users SET age = 32 WHERE id = 2;  (等待id=2锁)
-- 会话2: UPDATE users SET age = 33 WHERE id = 1;  (死锁!)

-- 避免死锁
-- 1. 保持一致的锁顺序
-- 2. 使用低隔离级别
-- 3. 尽量使用索引，减少锁范围
-- 4. 减小事务大小，缩短持有锁的时间

-- DELETE语句执行详解：

-- 示例
DELETE FROM users WHERE id = 1;

-- 执行过程：
-- 1. 解析SQL，确定删除条件
-- 2. 根据WHERE条件定位要删除的行
-- 3. 对要删除的行加排他锁（X锁）
-- 4. 记录完整数据到UNDO日志（支持回滚）
-- 5. 将记录标记为"已删除"（不是立即删除）
-- 6. 更新所有相关索引
-- 7. 写入REDO日志
-- 8. 写入BINLOG
-- 9. 提交事务
-- 10. 后台Purge线程真正删除数据

-- 简单DELETE
DELETE FROM users WHERE id = 1;

-- 条件DELETE
DELETE FROM users WHERE age < 18;

-- 关联DELETE
DELETE u FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'cancelled';

-- ORDER BY + LIMIT DELETE
DELETE FROM users 
WHERE status = 'inactive'
ORDER BY created_at ASC
LIMIT 100;

-- TRUNCATE（高效清空表）
TRUNCATE TABLE users;
-- TRUNCATE vs DELETE：
-- - TRUNCATE: 重建表，自增计数重置，快
-- - DELETE: 逐行删除，记录日志，慢

-- DELETE的删除机制：

-- 1. 标记删除 vs 真正删除
--    InnoDB中，DELETE只是标记记录为已删除
--    真正删除由后台Purge线程处理
--    这是为了支持MVCC和事务回滚

-- 2. 空间回收
--    删除后空间不会立即归还操作系统
--    InnoDB会复用这些空间存储新数据

-- 3. 释放空间
--    OPTIMIZE TABLE: 重建表，释放空间
OPTIMIZE TABLE users;

--    ALTER TABLE ENGINE=InnoDB: 同样效果
ALTER TABLE users ENGINE=InnoDB;

-- 4. Purge机制
--    后台线程定期清理已标记删除的记录
--    清理undo log和索引
SHOW VARIABLES LIKE 'innodb_purge_threads';

-- 5. 快速删除大量数据
--    分批删除，避免长时间锁表
DELETE FROM logs 
WHERE created_at < '2023-01-01' 
LIMIT 10000;
--    循环执行直到删除完成

-- 性能优化建议：

-- SELECT优化
-- 1. 使用索引，避免全表扫描
-- 2. 只查询需要的列
-- 3. 避免在WHERE中对字段使用函数
-- 4. 使用EXPLAIN分析执行计划

-- INSERT优化
-- 1. 批量INSERT比单条快
-- 2. 禁用唯一索引检查：SET unique_checks=0;
-- 3. 禁用外键检查：SET foreign_key_checks=0;
-- 4. 使用LOAD DATA INFILE批量导入

-- UPDATE优化
-- 1. 尽量使用主键或索引列更新
-- 2. 减少更新字段数量
-- 3. 批量更新时使用LIMIT分批
-- 4. 避免更新大字段

-- DELETE优化
-- 1. 使用主键删除最快
-- 2. 大量删除时使用分批
-- 3. 使用TRUNCATE替代DELETE清空表
-- 4. 删除后使用OPTIMIZE TABLE回收空间