-- 查看MySQL版本信息
SELECT @@version, @@version_comment;
-- 版本号格式：X.Y.Z
-- X: 主版本号（架构级变化）
-- Y: 次版本号（新特性）
-- Z: 修订版本号（Bug修复）
-- 示例：8.0.33
-- 8: 主版本
-- 0: 次版本  
-- 33: 修订版本

-- 分区表示例：按时间范围分区
CREATE TABLE sales (
    sale_id INT AUTO_INCREMENT,
    sale_date DATE,
    amount DECIMAL(10,2),
    region VARCHAR(50),
    PRIMARY KEY (sale_id, sale_date)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2008 VALUES LESS THAN (2009),
    PARTITION p2009 VALUES LESS THAN (2010),
    PARTITION p2010 VALUES LESS THAN (2011),
    PARTITION p_max VALUES LESS THAN MAXVALUE
);
-- 分区维护操作
ALTER TABLE sales REORGANIZE PARTITION p_max INTO (
    PARTITION p2011 VALUES LESS THAN (2012),
    PARTITION p_max VALUES LESS THAN MAXVALUE
);
-- 查询特定分区
SELECT * FROM sales PARTITION (p2010);

-- 创建定时事件
CREATE EVENT daily_maintenance
ON SCHEDULE EVERY 1 DAY STARTS '2024-01-01 02:00:00'
DO
BEGIN
    -- 每日数据归档
    CALL archive_old_data();
    
    -- 更新统计信息
    ANALYZE TABLE important_table;
    
    -- 清理临时数据
    DELETE FROM temp_sessions WHERE created_time < NOW() - INTERVAL 1 DAY;
END;
-- 事件调度器配置
SHOW VARIABLES LIKE 'event_scheduler';
SET GLOBAL event_scheduler = ON;
-- 查看事件状态
SELECT * FROM information_schema.EVENTS;

-- 配置行级复制
-- 在my.cnf中设置
[mysqld]
binlog_format = ROW
binlog_row_image = FULL
-- 查看复制格式
SHOW VARIABLES LIKE 'binlog_format';
-- 行级复制的优势：
-- 1. 更安全的数据一致性
-- 2. 减少锁竞争
-- 3. 更好的并行复制

// 在Java应用中利用分区特性
public class PartitionAwareDAO {
    
    public List<Sale> getSalesByYear(int year, DataSource dataSource) {
        // 根据年份选择合适的分区查询
        String sql = "SELECT * FROM sales PARTITION (p" + year + ") WHERE YEAR(sale_date) = ?";
        
        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, year);
            ResultSet rs = pstmt.executeQuery();
            
            List<Sale> sales = new ArrayList<>();
            while (rs.next()) {
                sales.add(mapToSale(rs));
            }
            return sales;
        } catch (SQLException e) {
            throw new DataAccessException("查询失败", e);
        }
    }
}

-- 5.5开始，InnoDB成为默认存储引擎
SHOW VARIABLES LIKE 'default_storage_engine';
-- 查看InnoDB状态
SHOW ENGINE INNODB STATUS;
-- 关键InnoDB配置优化
SET GLOBAL innodb_buffer_pool_size = 1024 * 1024 * 1024; -- 1GB
SET GLOBAL innodb_log_file_size = 256 * 1024 * 1024;     -- 256MB
SET GLOBAL innodb_flush_log_at_trx_commit = 1;

-- 配置半同步复制
-- 在主库上
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 1000; -- 1秒超时
-- 在从库上
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
-- 查看半同步复制状态
SHOW STATUS LIKE 'Rpl_semi_sync%';

-- 性能Schema使用示例
-- 查看最耗时的SQL
SELECT * FROM performance_schema.events_statements_summary_by_digest 
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;
-- 查看表IO统计
SELECT * FROM performance_schema.table_io_waits_summary_by_table 
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;
-- 查看锁等待
SELECT * FROM performance_schema.data_lock_waits;

-- 在线添加索引（不阻塞读写）
ALTER TABLE large_table ADD INDEX idx_email (email), ALGORITHM=INPLACE, LOCK=NONE;
-- 在线修改列类型
ALTER TABLE users MODIFY COLUMN last_login TIMESTAMP(6), ALGORITHM=INPLACE;
-- 查看DDL进度（5.7+）
SELECT * FROM information_schema.INNODB_TRX WHERE trx_state = 'RUNNING';
-- 支持的在线操作类型：
-- ✅ 添加/删除二级索引
-- ✅ 修改自增值
-- ✅ 重命名列
-- ✅ 设置列默认值

-- 索引条件下推（ICP）
EXPLAIN SELECT * FROM orders 
WHERE customer_id = 100 AND order_date > '2023-01-01';
-- 在5.6中，WHERE条件可以在存储引擎层过滤
-- 减少到Server层的数据传输
-- 批量键访问（BKA）
EXPLAIN SELECT * FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.country = 'US';
-- Multi-Range Read（MRR）
EXPLAIN SELECT * FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31';

// 使用Memcached协议直接访问InnoDB
public class MySQLNoSQLExample {
    
    public void demonstrateMemcachedAPI() {
        // 配置InnoDB Memcached插件
        // 1. 安装插件
        // INSTALL PLUGIN daemon_memcached SONAME 'libmemcached.so';
        
        // 2. Java客户端使用
        MemcachedClient client = new MemcachedClient(
            new InetSocketAddress("localhost", 11211));
        
        // 直接操作InnoDB表
        client.set("user:1001", 3600, "{\"name\":\"John\",\"age\":30}");
        
        // 获取数据
        String userData = (String) client.get("user:1001");
        System.out.println("User data: " + userData);
    }
}

-- 全局事务标识符（GTID）配置
-- 在my.cnf中
[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
-- 查看GTID状态
SHOW MASTER STATUS;
SHOW SLAVE STATUS\G
-- 基于GTID的复制配置
CHANGE MASTER TO 
MASTER_HOST='master1',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_AUTO_POSITION=1;
-- GTID格式：source_id:transaction_id
-- 示例：3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5

-- JSON数据类型操作
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    attributes JSON,
    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 插入JSON数据
INSERT INTO products (name, attributes) VALUES 
('Laptop', '{"brand": "Dell", "specs": {"cpu": "i7", "ram": "16GB"}, "price": 1299.99}'),
('Phone', '{"brand": "Apple", "specs": {"storage": "128GB", "color": "black"}, "price": 999.99}');
-- JSON查询函数
SELECT 
    id,
    name,
    attributes->>'$.brand' as brand,
    attributes->'$.specs.cpu' as cpu,
    JSON_EXTRACT(attributes, '$.price') as price
FROM products
WHERE attributes->>'$.brand' = 'Dell';
-- JSON索引（虚拟列+索引）
ALTER TABLE products 
ADD COLUMN brand_name VARCHAR(50) AS (attributes->>'$.brand'),
ADD INDEX idx_brand (brand_name);

-- 生成列示例
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary DECIMAL(10,2),
    bonus_rate DECIMAL(5,4),
    
    -- 虚拟生成列（不存储）
    full_name VARCHAR(101) AS (CONCAT(first_name, ' ', last_name)),
    
    -- 存储生成列
    total_compensation DECIMAL(10,2) AS (salary * (1 + bonus_rate)) STORED
);
-- 自动维护的列
INSERT INTO employees (first_name, last_name, salary, bonus_rate) 
VALUES ('John', 'Doe', 50000, 0.1);
SELECT * FROM employees;
-- 结果：full_name = 'John Doe', total_compensation = 55000.00

-- 配置多源复制
-- 从多个主库复制数据
-- 配置通道1
CHANGE MASTER TO 
MASTER_HOST='master1',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_AUTO_POSITION=1
FOR CHANNEL 'master1_channel';
-- 配置通道2  
CHANGE MASTER TO
MASTER_HOST='master2', 
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_AUTO_POSITION=1
FOR CHANNEL 'master2_channel';
-- 启动所有通道
START SLAVE FOR CHANNEL 'master1_channel';
START SLAVE FOR CHANNEL 'master2_channel';
-- 查看复制状态
SHOW SLAVE STATUS FOR CHANNEL 'master1_channel'\G

-- 使用Sys Schema进行性能诊断
-- 查看最耗资源的查询
SELECT * FROM sys.statement_analysis 
ORDER BY avg_latency DESC 
LIMIT 10;
-- 查看索引使用情况
SELECT * FROM sys.schema_index_statistics
WHERE table_schema = 'mydb';
-- 查看内存使用
SELECT * FROM sys.memory_global_total;
-- 查看锁等待
SELECT * FROM sys.innodb_lock_waits;
-- 查看IO统计
SELECT * FROM sys.io_global_by_file_by_bytes;

-- 密码验证策略
SHOW VARIABLES LIKE 'validate_password%';
-- 密码过期策略
ALTER USER 'app_user'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;
-- 密码历史
SET GLOBAL validate_password_history = 6;
-- SSL连接强制
CREATE USER 'secure_user'@'%' REQUIRE SSL;
GRANT ALL ON mydb.* TO 'secure_user'@'%';

-- 窗口函数示例：销售分析
SELECT 
    salesperson,
    sale_date,
    amount,
    -- 累计求和
    SUM(amount) OVER (PARTITION BY salesperson ORDER BY sale_date) as running_total,
    
    -- 移动平均
    AVG(amount) OVER (
        PARTITION BY salesperson 
        ORDER BY sale_date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) as moving_avg,
    
    -- 排名
    RANK() OVER (PARTITION BY salesperson ORDER BY amount DESC) as rank_in_person,
    
    -- 百分比
    amount / SUM(amount) OVER (PARTITION BY salesperson) * 100 as percent_of_total
    
FROM sales
ORDER BY salesperson, sale_date;
-- 常用窗口函数：
-- ✅ ROW_NUMBER(), RANK(), DENSE_RANK()
-- ✅ LEAD(), LAG()
-- ✅ FIRST_VALUE(), LAST_VALUE()
-- ✅ NTILE()

-- 递归CTE：组织架构查询
WITH RECURSIVE org_chart AS (
    -- 锚点：顶级管理者
    SELECT 
        employee_id,
        name,
        title,
        manager_id,
        0 as level,
        CAST(name AS CHAR(500)) as path
    FROM employees 
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归：下属员工
    SELECT 
        e.employee_id,
        e.name, 
        e.title,
        e.manager_id,
        oc.level + 1,
        CONCAT(oc.path, ' -> ', e.name)
    FROM employees e
    INNER JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT 
    employee_id,
    LPAD('', level * 4, ' ') || name as indented_name,
    title,
    level,
    path
FROM org_chart
ORDER BY path;
-- 非递归CTE：复杂查询简化
WITH monthly_sales AS (
    SELECT 
        YEAR(sale_date) as year,
        MONTH(sale_date) as month,
        SUM(amount) as total_sales
    FROM sales 
    GROUP BY YEAR(sale_date), MONTH(sale_date)
),
sales_growth AS (
    SELECT
        year,
        month,
        total_sales,
        LAG(total_sales) OVER (ORDER BY year, month) as prev_sales,
        (total_sales - LAG(total_sales) OVER (ORDER BY year, month)) / 
        LAG(total_sales) OVER (ORDER BY year, month) * 100 as growth_rate
    FROM monthly_sales
)
SELECT * FROM sales_growth WHERE growth_rate IS NOT NULL;

-- 角色基础权限管理
-- 创建角色
CREATE ROLE read_only;
CREATE ROLE data_operator;
CREATE ROLE admin_user;
-- 为角色授权
GRANT SELECT ON mydb.* TO read_only;
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO data_operator;
GRANT ALL PRIVILEGES ON mydb.* TO admin_user;
-- 将角色授予用户
CREATE USER 'app_user'@'%' IDENTIFIED BY 'secure_password';
GRANT read_only, data_operator TO 'app_user'@'%';
-- 激活角色
SET DEFAULT ROLE ALL TO 'app_user'@'%';
-- 查看角色权限
SHOW GRANTS FOR 'app_user'@'%';

-- 原子DDL确保操作完整性
-- 在8.0中，DDL操作要么完全成功，要么完全回滚
-- 示例：创建包含错误语句的存储过程
DELIMITER $$
CREATE PROCEDURE problematic_procedure()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- 有效的DDL
    CREATE TABLE temp_data (id INT PRIMARY KEY);
    
    -- 这里会失败（语法错误）
    CREAT INDEX invalid_syntax ON temp_data(id);
    
    -- 如果任何语句失败，所有操作都会回滚
    COMMIT;
END$$
DELIMITER ;
-- 在5.7中，第一个表会被创建，第二个失败
-- 在8.0中，整个操作原子性回滚

-- 测试索引效果而不删除
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255),
    phone VARCHAR(20),
    INDEX idx_email (email),
    INDEX idx_phone (phone) INVISIBLE  -- 创建为不可见索引
);
-- 优化器会忽略不可见索引
EXPLAIN SELECT * FROM users WHERE phone = '123-456-7890';
-- 临时启用测试
SET SESSION optimizer_switch = 'use_invisible_indexes=on';
EXPLAIN SELECT * FROM users WHERE phone = '123-456-7890';
SET SESSION optimizer_switch = 'use_invisible_indexes=off';
-- 根据测试结果决定是否可见
ALTER TABLE users ALTER INDEX idx_phone VISIBLE;

-- 管理CPU资源分配
-- 创建资源组
CREATE RESOURCE GROUP batch_processing
TYPE = USER
VCPU = 2-3
THREAD_PRIORITY = 5;
CREATE RESOURCE GROUP user_queries  
TYPE = USER
VCPU = 0-1
THREAD_PRIORITY = 10;
-- 将查询分配到资源组
SET RESOURCE GROUP batch_processing;
CALL heavy_data_processing_procedure();
SET RESOURCE GROUP user_queries;
SELECT * FROM quick_lookup_table;
-- 重置资源组
SET RESOURCE GROUP = NULL;

-- 使用Sys Benchmark进行性能测试（概念）
-- 8.0相比5.7的性能提升：
-- ✅ 读性能：+20%
-- ✅ 写性能：+30% 
-- ✅ 高并发：+50%
-- ✅ 复杂查询：+100%
-- 实际测试场景示例
-- 测试查询：窗口函数 vs 传统方式
-- 8.0窗口函数写法：执行时间 0.5秒
-- 5.7传统子查询写法：执行时间 2.1秒

-- 升级前兼容性检查
-- 1. 语法兼容性
SELECT * FROM information_schema.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%旧语法%';
-- 2. 关键字检查
SHOW VARIABLES LIKE 'group_replication%';
-- 3. 默认值变更
SELECT @@sql_mode;
-- 4. 存储引擎状态
SELECT ENGINE, COUNT(*) 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
GROUP BY ENGINE;
-- 5. 字符集检查
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION 
FROM information_schema.TABLES 
WHERE TABLE_COLLATION != 'utf8mb4_0900_ai_ci';

// 连接器版本兼容性
public class ConnectorCompatibility {
    
    public void checkCompatibility() {
        // MySQL 8.0需要Connector/J 8.0+
        // 在pom.xml中配置：
        /*
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.33</version>
        </dependency>
        */
        
        // 连接URL变化
        String url5_7 = "jdbc:mysql://localhost:3306/db?useSSL=false";
        String url8_0 = "jdbc:mysql://localhost:3306/db?useSSL=false&serverTimezone=Asia/Shanghai";
        
        // 时区设置必须
        // 新的默认认证插件：caching_sha2_password
    }
    
    // 处理新的默认字符集
    public void handleNewCharset() {
        // 8.0默认字符集：utf8mb4
        // 默认排序规则：utf8mb4_0900_ai_ci
        
        // 在Java中确保正确配置
        String url = "jdbc:mysql://localhost:3306/db?" +
                   "characterEncoding=utf8" +
                   "&useUnicode=true" +
                   "&serverTimezone=Asia/Shanghai";
    }
}