graph TD
    A[MySQL 8.0 改进] --> B[性能]
    A --> C[架构]
    A --> D[功能]
    A --> E[安全]

    B --> B1[隐藏索引]
    B --> B2[降序索引]
    B --> B3[分组排序优化]
    B --> B4[InnoDB性能提升]

    C --> C1[数据字典重构]
    C --> C2[移除查询缓存]
    C --> C3[原子DDL]
    C --> C4[redo log优化]

    D --> D1[窗口函数]
    D --> D2[公用表表达式CTE]
    D --> D3[JSON增强]
    D --> D4[角色管理]

    E --> E1[密码过期策略]
    E --> E2[更安全的认证插件]
    E --> E3[权限控制细化]

graph LR
    subgraph 5.7[MySQL 5.7]
        A1[.frm文件<br/>表结构定义]
        A2[数据字典<br/>存于MyISAM系统表]
    end

    subgraph 8.0[MySQL 8.0]
        B1[统一数据字典<br/>存储在InnoDB]
        B2[移除.frm文件]
        B3[事务性DDL<br/>原子操作]
    end

-- 8.0 中不再有 .frm 文件
-- 表结构信息存储在 data dictionary 中
mysql> ls -la /var/lib/mysql/test/
-- 只看到 .ibd 文件，没有 .frm 了！

-- 原子DDL：DDL操作要么全部成功，要么全部回滚
-- 5.7中DROP TABLE失败了可能留下残余文件
-- 8.0中保证一致性

-- 5.7：删除索引有风险，必须删除后才能重建
DROP INDEX idx_name ON t;   -- 删除后才发现查询变慢，后悔了...

-- 8.0：先隐藏，确认无影响再删除
ALTER TABLE t ALTER INDEX idx_name INVISIBLE;
-- 观察一段时间，确认查询都没用到这个索引
ALTER TABLE t ALTER INDEX idx_name VISIBLE;  -- 恢复
-- 或者确定要删除
DROP INDEX idx_name ON t;

-- 5.7：降序排序时无法使用索引
CREATE INDEX idx_age_name ON t (age DESC, name ASC);
-- 5.7 会忽略 DESC，实际存储为 ASC

-- 8.0：真正支持降序索引
CREATE INDEX idx_age_name ON t (age DESC, name ASC);
-- age 列按降序存储
-- 查询 ORDER BY age DESC 可以直接使用索引！

-- 5.7：用子查询+变量实现排名
SELECT @row_number:=@row_number+1 AS row_num, name, score
FROM scores, (SELECT @row_number:=0) AS t
ORDER BY score DESC;

-- 8.0：窗口函数，简洁高效
SELECT
    name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS rank,
    RANK() OVER (ORDER BY score DESC) AS dense_rank,
    LEAD(score, 1) OVER (ORDER BY score DESC) AS next_score
FROM scores;

-- 8.0：CTE替代复杂的子查询

-- 递归CTE：生成连续日期序列
WITH RECURSIVE dates AS (
    SELECT '2024-01-01' AS dt
    UNION ALL
    SELECT dt + INTERVAL 1 DAY FROM dates
    WHERE dt < '2024-01-31'
)
SELECT * FROM dates;

-- 非递归CTE：提高可读性
WITH
    high_value_users AS (
        SELECT user_id, SUM(amount) AS total
        FROM orders
        GROUP BY user_id
        HAVING total > 10000
    )
SELECT u.name, h.total
FROM users u
JOIN high_value_users h ON u.id = h.user_id;

-- 8.0：JSON路径表达式增强
SELECT JSON_EXTRACT('{"a": {"b": 1}}', '$.a.b');  -- 1

-- JSON表函数：将JSON展开为关系表
SELECT * FROM
JSON_TABLE('[{"x":2,"y":"foo"},{"x":3,"y":"bar"}]',
    '$[*]' COLUMNS (
        x INT PATH '$.x',
        y VARCHAR(100) PATH '$.y'
    )
) AS jt;
-- 输出：
-- x  | y
-- 2  | foo
-- 3  | bar

-- 8.0 角色管理
CREATE ROLE 'db_admin', 'db_readonly';
GRANT ALL ON test.* TO 'db_admin';
GRANT SELECT ON test.* TO 'db_readonly';
GRANT 'db_admin' TO 'user1'@'localhost';

-- 密码过期策略
CREATE USER 'user2'@'localhost'
IDENTIFIED BY 'password123'
PASSWORD EXPIRE INTERVAL 90 DAY;

-- 5.7 → 8.0 迁移检查
-- 1. 数据字典升级不可逆
mysql_upgrade --force

-- 2. 认证插件变化
ALTER USER 'root'@'localhost'
IDENTIFIED WITH mysql_native_password BY 'password';

-- 3. GROUP BY 行为变化
-- 8.0 默认更严格的 SQL MODE
SET sql_mode = 'ONLY_FULL_GROUP_BY';