MySQL 查询优化：百万级数据表的索引设计与 SQL 调优实践

-- 创建测试表来演示索引原理
CREATE TABLE user_behavior (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    action_type TINYINT NOT NULL,
    action_time DATETIME NOT NULL,
    ip_address VARCHAR(15),
    user_agent TEXT,
    page_url VARCHAR(500),
    session_id VARCHAR(64),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_action (user_id, action_type),
    INDEX idx_action_time (action_time),
    INDEX idx_session (session_id)
) ENGINE=InnoDB;

-- 插入测试数据（模拟百万级数据）
DELIMITER $$
CREATE PROCEDURE generate_test_data()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE batch_size INT DEFAULT 10000;
    DECLARE total_records INT DEFAULT 1000000;
    
    WHILE i <= total_records DO
        INSERT INTO user_behavior (
            user_id, action_type, action_time, ip_address, 
            user_agent, page_url, session_id
        ) VALUES 
        (
            FLOOR(1 + RAND() * 100000),
            FLOOR(1 + RAND() * 10),
            DATE_ADD('2024-01-01', INTERVAL FLOOR(RAND() * 365) DAY),
            CONCAT(
                FLOOR(1 + RAND() * 255), '.', 
                FLOOR(1 + RAND() * 255), '.', 
                FLOOR(1 + RAND() * 255), '.', 
                FLOOR(1 + RAND() * 255)
            ),
            'Mozilla/5.0 (compatible; TestBot/1.0)',
            CONCAT('/page/', FLOOR(1 + RAND() * 1000)),
            MD5(CONCAT(i, RAND()))
        );
        
        SET i = i + 1;
        
        -- 每批次提交，避免事务过大
        IF i % batch_size = 0 THEN
            COMMIT;
        END IF;
    END WHILE;
END$$
DELIMITER ;

-- 执行数据生成（注意：这会花费一些时间）
-- CALL generate_test_data();

-- 1. 主键索引（聚簇索引）
-- 主键索引将数据和索引存储在一起，查询效率最高
SELECT * FROM user_behavior WHERE id = 12345;

-- 2. 唯一索引
-- 保证数据唯一性的同时提供快速查询
ALTER TABLE user_behavior ADD UNIQUE INDEX uk_session_user (session_id, user_id);

-- 3. 复合索引（联合索引）
-- 支持多字段组合查询，遵循最左前缀原则
SELECT * FROM user_behavior 
WHERE user_id = 1001 AND action_type = 2;

-- 4. 前缀索引
-- 对于长字符串字段，只索引前几个字符
ALTER TABLE user_behavior ADD INDEX idx_url_prefix (page_url(50));

-- 5. 函数索引（MySQL 8.0+）
-- 对函数表达式建立索引
ALTER TABLE user_behavior ADD INDEX idx_date_func ((DATE(action_time)));

-- 查看索引使用情况
SHOW INDEX FROM user_behavior;

-- 分析索引效果
EXPLAIN SELECT * FROM user_behavior 
WHERE user_id = 1001 AND action_type = 2 
ORDER BY action_time DESC LIMIT 10;

-- 创建订单表来演示复合索引优化
CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_status TINYINT NOT NULL,
    order_amount DECIMAL(10,2) NOT NULL,
    payment_method TINYINT NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    province_id SMALLINT,
    city_id SMALLINT,
    
    -- 错误的索引设计示例
    -- INDEX idx_bad (order_status, user_id, created_at),
    
    -- 正确的索引设计：按选择性从高到低排序
    INDEX idx_user_status_time (user_id, order_status, created_at),
    INDEX idx_status_time (order_status, created_at),
    INDEX idx_amount_range (order_amount, created_at),
    INDEX idx_location (province_id, city_id, created_at)
) ENGINE=InnoDB;

-- 复合索引优化案例分析
-- 案例1：用户订单查询（最常见场景）
EXPLAIN SELECT * FROM orders 
WHERE user_id = 12345 
  AND order_status IN (1, 2, 3) 
  AND created_at >= '2024-01-01'
ORDER BY created_at DESC 
LIMIT 20;

-- 案例2：状态统计查询
EXPLAIN SELECT order_status, COUNT(*), AVG(order_amount)
FROM orders 
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY order_status;

-- 案例3：金额范围查询
EXPLAIN SELECT * FROM orders 
WHERE order_amount BETWEEN 100.00 AND 1000.00
  AND created_at >= '2024-06-01'
ORDER BY order_amount DESC;

-- 索引使用情况分析
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    SEQ_IN_INDEX,
    COLUMN_NAME,
    CARDINALITY,
    SUB_PART,
    NULLABLE
FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME = 'orders'
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

-- 创建用户统计表演示覆盖索引
CREATE TABLE user_stats (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    stat_date DATE NOT NULL,
    page_views INT DEFAULT 0,
    unique_visits INT DEFAULT 0,
    bounce_rate DECIMAL(5,2) DEFAULT 0.00,
    avg_session_duration INT DEFAULT 0,
    conversion_count INT DEFAULT 0,
    
    -- 覆盖索引设计：包含查询所需的所有字段
    INDEX idx_cover_daily_stats (
        user_id, stat_date, 
        page_views, unique_visits, bounce_rate
    ),
    
    -- 覆盖索引：用户转化分析
    INDEX idx_cover_conversion (
        stat_date, user_id, 
        conversion_count, page_views
    )
) ENGINE=InnoDB;

-- 覆盖索引优化示例
-- 查询1：用户日常统计（使用覆盖索引，无需回表）
EXPLAIN SELECT 
    user_id,
    stat_date,
    page_views,
    unique_visits,
    bounce_rate
FROM user_stats 
WHERE user_id = 12345 
  AND stat_date BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY stat_date;

-- 查询2：转化率分析（使用覆盖索引）
EXPLAIN SELECT 
    stat_date,
    COUNT(DISTINCT user_id) as active_users,
    SUM(conversion_count) as total_conversions,
    SUM(page_views) as total_views,
    ROUND(SUM(conversion_count) / SUM(page_views) * 100, 2) as conversion_rate
FROM user_stats 
WHERE stat_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY stat_date
ORDER BY stat_date;

-- 验证覆盖索引效果
-- 使用SHOW STATUS查看Handler_read_*统计信息
FLUSH STATUS;
SELECT user_id, stat_date, page_views 
FROM user_stats 
WHERE user_id = 12345 LIMIT 10;

SHOW STATUS LIKE 'Handler_read%';

-- 创建分区表演示分区索引策略
CREATE TABLE transaction_log (
    id BIGINT AUTO_INCREMENT,
    user_id INT NOT NULL,
    transaction_type TINYINT NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    transaction_time DATETIME NOT NULL,
    status TINYINT NOT NULL,
    merchant_id INT,
    
    PRIMARY KEY (id, transaction_time),
    INDEX idx_user_type (user_id, transaction_type),
    INDEX idx_merchant_time (merchant_id, transaction_time),
    INDEX idx_amount_status (amount, status)
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(transaction_time)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 分区表查询优化示例
-- 查询1：利用分区裁剪
EXPLAIN PARTITIONS
SELECT * FROM transaction_log 
WHERE transaction_time >= '2024-01-01' 
  AND transaction_time < '2024-02-01'
  AND user_id = 12345;

-- 查询2：跨分区聚合查询
EXPLAIN PARTITIONS
SELECT 
    YEAR(transaction_time) as year,
    MONTH(transaction_time) as month,
    COUNT(*) as transaction_count,
    SUM(amount) as total_amount
FROM transaction_log 
WHERE user_id = 12345
  AND transaction_time >= '2023-01-01'
GROUP BY YEAR(transaction_time), MONTH(transaction_time);

-- 查看分区信息
SELECT 
    PARTITION_NAME,
    TABLE_ROWS,
    DATA_LENGTH,
    INDEX_LENGTH
FROM information_schema.PARTITIONS 
WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME = 'transaction_log';

-- WHERE条件优化示例

-- 1. 避免在WHERE子句中使用函数
-- 错误写法：无法使用索引
SELECT * FROM orders 
WHERE YEAR(created_at) = 2024 AND MONTH(created_at) = 6;

-- 正确写法：可以使用索引
SELECT * FROM orders 
WHERE created_at >= '2024-06-01' 
  AND created_at < '2024-07-01';

-- 2. 合理使用IN和EXISTS
-- 当子查询结果集较小时，使用IN
SELECT * FROM orders o
WHERE o.user_id IN (
    SELECT user_id FROM vip_users 
    WHERE level >= 3
);

-- 当主查询结果集较小时，使用EXISTS
SELECT * FROM orders o
WHERE EXISTS (
    SELECT 1 FROM order_items oi 
    WHERE oi.order_id = o.id 
      AND oi.product_category = 'electronics'
);

-- 3. 优化LIKE查询
-- 错误写法：无法使用索引
SELECT * FROM products WHERE product_name LIKE '%phone%';

-- 正确写法：可以使用前缀索引
SELECT * FROM products WHERE product_name LIKE 'iPhone%';

-- 4. 避免隐式类型转换
-- 错误写法：字符串字段与数字比较
SELECT * FROM orders WHERE order_id = '12345';

-- 正确写法：类型匹配
SELECT * FROM orders WHERE order_id = 12345;

-- 5. 优化OR条件
-- 低效写法
SELECT * FROM orders 
WHERE user_id = 12345 OR order_status = 1;

-- 高效写法：使用UNION
SELECT * FROM orders WHERE user_id = 12345
UNION
SELECT * FROM orders WHERE order_status = 1;

-- 6. 范围查询优化
-- 创建复合索引支持范围查询
ALTER TABLE orders ADD INDEX idx_status_amount_time (
    order_status, order_amount, created_at
);

-- 优化后的范围查询
SELECT * FROM orders 
WHERE order_status = 1 
  AND order_amount BETWEEN 100 AND 1000
  AND created_at >= '2024-01-01'
ORDER BY created_at DESC;

-- JOIN优化示例

-- 创建相关表
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    status TINYINT DEFAULT 1,
    created_at DATETIME,
    INDEX idx_status (status),
    INDEX idx_email (email)
) ENGINE=InnoDB;

CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    real_name VARCHAR(100),
    phone VARCHAR(20),
    address TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

-- 1. 选择合适的JOIN类型
-- INNER JOIN：只返回匹配的记录
SELECT u.username, p.real_name, o.order_amount
FROM users u
INNER JOIN user_profiles p ON u.id = p.user_id
INNER JOIN orders o ON u.id = o.user_id
WHERE u.status = 1 
  AND o.created_at >= '2024-01-01';

-- LEFT JOIN：返回左表所有记录
SELECT u.username, COALESCE(p.real_name, 'Unknown') as name
FROM users u
LEFT JOIN user_profiles p ON u.id = p.user_id
WHERE u.status = 1;

-- 2. 优化JOIN顺序（小表驱动大表）
-- MySQL优化器会自动选择，但我们可以使用STRAIGHT_JOIN强制顺序
SELECT /*+ STRAIGHT_JOIN */ u.username, o.order_amount
FROM users u
STRAIGHT_JOIN orders o ON u.id = o.user_id
WHERE u.status = 1;

-- 3. 使用子查询优化JOIN
-- 原始查询：可能产生笛卡尔积
SELECT DISTINCT u.username
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
WHERE oi.product_category = 'electronics';

-- 优化后：先过滤再JOIN
SELECT u.username
FROM users u
WHERE u.id IN (
    SELECT DISTINCT o.user_id
    FROM orders o
    JOIN order_items oi ON o.id = oi.order_id
    WHERE oi.product_category = 'electronics'
);

-- 4. 使用临时表优化复杂JOIN
-- 创建临时表存储中间结果
CREATE TEMPORARY TABLE temp_active_users AS
SELECT user_id, COUNT(*) as order_count
FROM orders 
WHERE created_at >= '2024-01-01'
GROUP BY user_id
HAVING COUNT(*) >= 5;

-- 使用临时表进行JOIN
SELECT u.username, t.order_count, p.real_name
FROM temp_active_users t
JOIN users u ON t.user_id = u.id
LEFT JOIN user_profiles p ON u.id = p.user_id;

-- 5. 分析JOIN性能
EXPLAIN FORMAT=JSON
SELECT u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2024-01-01'
GROUP BY u.id, u.username
HAVING COUNT(o.id) > 0;

-- 聚合查询优化示例

-- 1. 使用索引优化GROUP BY
-- 创建支持分组的索引
ALTER TABLE orders ADD INDEX idx_group_optimize (
    order_status, created_at, user_id, order_amount
);

-- 优化的分组查询
SELECT 
    order_status,
    DATE(created_at) as order_date,
    COUNT(*) as order_count,
    SUM(order_amount) as total_amount,
    AVG(order_amount) as avg_amount
FROM orders 
WHERE created_at >= '2024-01-01'
GROUP BY order_status, DATE(created_at)
ORDER BY order_status, order_date;

-- 2. 使用预聚合表优化
-- 创建日统计表
CREATE TABLE daily_order_stats (
    stat_date DATE PRIMARY KEY,
    order_status TINYINT,
    order_count INT DEFAULT 0,
    total_amount DECIMAL(15,2) DEFAULT 0.00,
    avg_amount DECIMAL(10,2) DEFAULT 0.00,
    
    UNIQUE KEY uk_date_status (stat_date, order_status),
    INDEX idx_status_date (order_status, stat_date)
) ENGINE=InnoDB;

-- 预聚合数据更新存储过程
DELIMITER $$
CREATE PROCEDURE update_daily_stats(IN target_date DATE)
BEGIN
    -- 删除当日旧数据
    DELETE FROM daily_order_stats WHERE stat_date = target_date;
    
    -- 插入新的聚合数据
    INSERT INTO daily_order_stats (
        stat_date, order_status, order_count, 
        total_amount, avg_amount
    )
    SELECT 
        DATE(created_at) as stat_date,
        order_status,
        COUNT(*) as order_count,
        SUM(order_amount) as total_amount,
        AVG(order_amount) as avg_amount
    FROM orders 
    WHERE DATE(created_at) = target_date
    GROUP BY DATE(created_at), order_status;
END$$
DELIMITER ;

-- 使用预聚合表查询（速度极快）
SELECT 
    stat_date,
    SUM(order_count) as daily_orders,
    SUM(total_amount) as daily_revenue
FROM daily_order_stats 
WHERE stat_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY stat_date
ORDER BY stat_date;

-- 3. 优化HAVING子句
-- 低效写法：先分组再过滤
SELECT user_id, COUNT(*) as order_count
FROM orders 
GROUP BY user_id
HAVING COUNT(*) > 10;

-- 高效写法：使用子查询预过滤
SELECT user_id, order_count
FROM (
    SELECT user_id, COUNT(*) as order_count
    FROM orders 
    GROUP BY user_id
) t
WHERE t.order_count > 10;

-- 4. 使用窗口函数优化排名查询
-- 查询每个用户的最近订单
SELECT 
    user_id,
    order_id,
    order_amount,
    created_at,
    ROW_NUMBER() OVER (
        PARTITION BY user_id 
        ORDER BY created_at DESC
    ) as rn
FROM orders
WHERE created_at >= '2024-01-01'
HAVING rn = 1;

-- EXPLAIN详细分析示例

-- 1. 基础EXPLAIN分析
EXPLAIN SELECT * FROM orders 
WHERE user_id = 12345 AND order_status = 1;

-- 2. EXPLAIN EXTENDED - 获取更多信息
EXPLAIN EXTENDED 
SELECT o.*, u.username 
FROM orders o 
JOIN users u ON o.user_id = u.id 
WHERE o.created_at >= '2024-01-01';

-- 查看优化器改写后的SQL
SHOW WARNINGS;

-- 3. EXPLAIN FORMAT=JSON - 获取详细的成本信息
EXPLAIN FORMAT=JSON
SELECT 
    user_id,
    COUNT(*) as order_count,
    SUM(order_amount) as total_amount
FROM orders 
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY user_id
HAVING COUNT(*) > 5
ORDER BY total_amount DESC
LIMIT 100;

-- 4. 分析不同类型的执行计划
-- 全表扫描示例（需要优化）
EXPLAIN SELECT * FROM orders 
WHERE order_amount > 1000;

-- 索引扫描示例（已优化）
EXPLAIN SELECT * FROM orders 
WHERE user_id = 12345 
ORDER BY created_at DESC;

-- 索引覆盖示例（最优）
EXPLAIN SELECT user_id, order_status, created_at 
FROM orders 
WHERE user_id BETWEEN 10000 AND 20000;

-- 5. 执行计划关键指标解读
-- 创建性能分析视图
CREATE VIEW v_explain_analysis AS
SELECT 
    'type' as metric, 
    'system > const > eq_ref > ref > range > index > ALL' as description
UNION ALL
SELECT 'key', '使用的索引名称，NULL表示未使用索引'
UNION ALL
SELECT 'key_len', '索引使用的字节数，越小越好'
UNION ALL
SELECT 'rows', '预估扫描的行数，越小越好'
UNION ALL
SELECT 'Extra', '额外信息：Using index(覆盖索引), Using filesort(需要排序)等';

-- 查看分析指南
SELECT * FROM v_explain_analysis;

-- 6. 复杂查询的执行计划分析
EXPLAIN 
SELECT 
    u.username,
    COUNT(DISTINCT o.id) as order_count,
    SUM(o.order_amount) as total_spent,
    MAX(o.created_at) as last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id 
    AND o.created_at >= '2024-01-01'
LEFT JOIN user_profiles p ON u.id = p.user_id
WHERE u.status = 1
GROUP BY u.id, u.username
HAVING COUNT(DISTINCT o.id) > 0
ORDER BY total_spent DESC
LIMIT 50;

-- 慢查询日志配置和分析

-- 1. 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1.0;  -- 记录执行时间超过1秒的查询
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 2. 查看慢查询配置
SHOW VARIABLES LIKE '%slow%';
SHOW VARIABLES LIKE '%long_query_time%';

-- 3. 性能监控查询
-- 查看当前连接和查询状态
SELECT 
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME,
    STATE,
    LEFT(INFO, 100) as QUERY_PREVIEW
FROM information_schema.PROCESSLIST 
WHERE COMMAND != 'Sleep'
ORDER BY TIME DESC;

-- 4. 索引使用统计
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    CARDINALITY,
    CASE 
        WHEN CARDINALITY = 0 THEN 'Unused Index'
        WHEN CARDINALITY < 10 THEN 'Low Selectivity'
        ELSE 'Good Selectivity'
    END as INDEX_QUALITY
FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_NAME, INDEX_NAME;

-- 5. 表空间和索引大小分析
SELECT 
    TABLE_NAME,
    ROUND(DATA_LENGTH / 1024 / 1024, 2) as DATA_SIZE_MB,
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) as INDEX_SIZE_MB,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) as TOTAL_SIZE_MB,
    TABLE_ROWS,
    ROUND(INDEX_LENGTH / DATA_LENGTH * 100, 2) as INDEX_RATIO_PERCENT
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_TYPE = 'BASE TABLE'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

-- 6. 创建性能监控存储过程
DELIMITER $$
CREATE PROCEDURE analyze_query_performance()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE query_text TEXT;
    DECLARE exec_time DECIMAL(10,6);
    
    -- 声明游标
    DECLARE cur CURSOR FOR 
        SELECT sql_text, exec_time 
        FROM mysql.slow_log 
        WHERE start_time >= DATE_SUB(NOW(), INTERVAL 1 HOUR);
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 创建临时分析表
    CREATE TEMPORARY TABLE temp_slow_analysis (
        query_pattern VARCHAR(500),
        avg_exec_time DECIMAL(10,6),
        max_exec_time DECIMAL(10,6),
        query_count INT,
        optimization_priority INT
    );
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO query_text, exec_time;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 分析查询模式并插入结果
        -- 这里可以添加更复杂的分析逻辑
        
    END LOOP;
    
    CLOSE cur;
    
    -- 返回分析结果
    SELECT * FROM temp_slow_analysis 
    ORDER BY optimization_priority DESC;
    
    DROP TEMPORARY TABLE temp_slow_analysis;
END$$
DELIMITER ;

-- 7. 实时性能指标监控
SELECT 
    'Queries per second' as metric,
    ROUND(
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Queries') /
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Uptime'),
        2
    ) as value
UNION ALL
SELECT 
    'Slow queries ratio',
    ROUND(
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Slow_queries') /
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Queries') * 100,
        4
    )
UNION ALL
SELECT 
    'Index usage ratio',
    ROUND(
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Handler_read_key') /
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Handler_read_rnd_next') * 100,
        2
    );

-- 性能基准测试框架

-- 1. 创建基准测试表
CREATE TABLE benchmark_results (
    id INT AUTO_INCREMENT PRIMARY KEY,
    test_name VARCHAR(100) NOT NULL,
    query_type VARCHAR(50) NOT NULL,
    execution_time DECIMAL(10,6) NOT NULL,
    rows_examined BIGINT,
    rows_returned BIGINT,
    index_used VARCHAR(100),
    test_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_test_name (test_name),
    INDEX idx_test_time (test_timestamp)
) ENGINE=InnoDB;

-- 2. 基准测试存储过程
DELIMITER $$
CREATE PROCEDURE run_benchmark_test(
    IN test_name VARCHAR(100),
    IN test_query TEXT,
    IN iterations INT DEFAULT 10
)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE start_time DECIMAL(20,6);
    DECLARE end_time DECIMAL(20,6);
    DECLARE exec_time DECIMAL(10,6);
    
    -- 预热查询缓存
    SET @sql = test_query;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    -- 执行基准测试
    WHILE i <= iterations DO
        -- 清除查询缓存
        RESET QUERY CACHE;
        
        -- 记录开始时间
        SET start_time = UNIX_TIMESTAMP(NOW(6));
        
        -- 执行测试查询
        SET @sql = test_query;
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        -- 记录结束时间
        SET end_time = UNIX_TIMESTAMP(NOW(6));
        SET exec_time = end_time - start_time;
        
        -- 记录结果
        INSERT INTO benchmark_results (
            test_name, query_type, execution_time
        ) VALUES (
            test_name, 'SELECT', exec_time
        );
        
        SET i = i + 1;
    END WHILE;
    
    -- 返回统计结果
    SELECT 
        test_name,
        COUNT(*) as test_count,
        ROUND(AVG(execution_time), 6) as avg_time,
        ROUND(MIN(execution_time), 6) as min_time,
        ROUND(MAX(execution_time), 6) as max_time,
        ROUND(STDDEV(execution_time), 6) as stddev_time
    FROM benchmark_results 
    WHERE test_name = test_name
      AND test_timestamp >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
    GROUP BY test_name;
END$$
DELIMITER ;

-- 3. 执行基准测试
-- 测试无索引查询
CALL run_benchmark_test(
    'no_index_test',
    'SELECT * FROM orders WHERE order_amount > 500',
    20
);

-- 测试有索引查询
CALL run_benchmark_test(
    'with_index_test',
    'SELECT * FROM orders WHERE user_id = 12345',
    20
);

-- 测试复合索引查询
CALL run_benchmark_test(
    'composite_index_test',
    'SELECT * FROM orders WHERE user_id = 12345 AND order_status = 1',
    20
);

-- 4. 基准测试结果分析
SELECT 
    test_name,
    ROUND(AVG(execution_time), 6) as avg_execution_time,
    COUNT(*) as test_runs,
    ROUND(STDDEV(execution_time), 6) as time_variance
FROM benchmark_results 
WHERE test_timestamp >= DATE_SUB(NOW(), INTERVAL 1 DAY)
GROUP BY test_name
ORDER BY avg_execution_time DESC;

-- 分库分表策略实现

-- 1. 水平分表策略
-- 按用户ID分表（假设分成16个表）
CREATE TABLE orders_0 LIKE orders;
CREATE TABLE orders_1 LIKE orders;
-- ... 创建orders_2到orders_15

-- 分表路由函数
DELIMITER $$
CREATE FUNCTION get_table_suffix(user_id INT) 
RETURNS INT
READS SQL DATA
DETERMINISTIC
BEGIN
    RETURN user_id % 16;
END$$
DELIMITER ;

-- 分表查询示例
SET @user_id = 12345;
SET @table_suffix = get_table_suffix(@user_id);
SET @sql = CONCAT('SELECT * FROM orders_', @table_suffix, ' WHERE user_id = ', @user_id);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 2. 垂直分表策略
-- 将大字段分离到单独的表
CREATE TABLE orders_basic (
    id BIGINT PRIMARY KEY,
    user_id INT NOT NULL,
    order_status TINYINT NOT NULL,
    order_amount DECIMAL(10,2) NOT NULL,
    created_at DATETIME NOT NULL,
    
    INDEX idx_user_status (user_id, order_status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB;

CREATE TABLE orders_detail (
    order_id BIGINT PRIMARY KEY,
    shipping_address TEXT,
    order_notes TEXT,
    extended_info JSON,
    
    FOREIGN KEY (order_id) REFERENCES orders_basic(id)
) ENGINE=InnoDB;

-- 3. 读写分离配置（应用层实现）
-- 主库写操作
INSERT INTO orders_basic (user_id, order_status, order_amount, created_at)
VALUES (12345, 1, 299.99, NOW());

-- 从库读操作（通过应用程序路由到从库）
SELECT * FROM orders_basic 
WHERE user_id = 12345 
ORDER BY created_at DESC 
LIMIT 10;

-- 4. 分布式事务处理
-- 使用XA事务保证跨库一致性
XA START 'order_transaction_001';

-- 在订单库执行
INSERT INTO orders_basic (user_id, order_status, order_amount, created_at)
VALUES (12345, 1, 299.99, NOW());

-- 在库存库执行
UPDATE inventory SET stock_count = stock_count - 1 
WHERE product_id = 1001;

XA END 'order_transaction_001';
XA PREPARE 'order_transaction_001';
XA COMMIT 'order_transaction_001';

-- 5. 分片键选择策略
-- 创建分片配置表
CREATE TABLE shard_config (
    table_name VARCHAR(50) PRIMARY KEY,
    shard_key VARCHAR(50) NOT NULL,
    shard_count INT NOT NULL,
    shard_algorithm VARCHAR(20) NOT NULL,
    
    INDEX idx_table (table_name)
) ENGINE=InnoDB;

INSERT INTO shard_config VALUES 
('orders', 'user_id', 16, 'hash'),
('user_behavior', 'user_id', 16, 'hash'),
('transaction_log', 'transaction_time', 12, 'range');

-- 缓存策略实现

-- 1. 查询结果缓存表
CREATE TABLE query_cache (
    cache_key VARCHAR(255) PRIMARY KEY,
    cache_value LONGTEXT NOT NULL,
    expire_time DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_expire (expire_time)
) ENGINE=InnoDB;

-- 2. 缓存管理存储过程
DELIMITER $$
CREATE PROCEDURE set_query_cache(
    IN cache_key VARCHAR(255),
    IN cache_value LONGTEXT,
    IN ttl_seconds INT DEFAULT 3600
)
BEGIN
    INSERT INTO query_cache (cache_key, cache_value, expire_time)
    VALUES (cache_key, cache_value, DATE_ADD(NOW(), INTERVAL ttl_seconds SECOND))
    ON DUPLICATE KEY UPDATE 
        cache_value = VALUES(cache_value),
        expire_time = VALUES(expire_time);
END$$

CREATE FUNCTION get_query_cache(cache_key VARCHAR(255))
RETURNS LONGTEXT
READS SQL DATA
BEGIN
    DECLARE result LONGTEXT DEFAULT NULL;
    
    SELECT cache_value INTO result
    FROM query_cache 
    WHERE cache_key = cache_key 
      AND expire_time > NOW();
    
    RETURN result;
END$$

-- 清理过期缓存
CREATE PROCEDURE cleanup_expired_cache()
BEGIN
    DELETE FROM query_cache WHERE expire_time <= NOW();
END$$
DELIMITER ;

-- 3. 预计算表策略
-- 用户统计预计算表
CREATE TABLE user_stats_cache (
    user_id INT PRIMARY KEY,
    total_orders INT DEFAULT 0,
    total_amount DECIMAL(15,2) DEFAULT 0.00,
    avg_order_amount DECIMAL(10,2) DEFAULT 0.00,
    last_order_date DATETIME,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_updated (updated_at)
) ENGINE=InnoDB;

-- 更新用户统计的触发器
DELIMITER $$
CREATE TRIGGER update_user_stats_after_order
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO user_stats_cache (
        user_id, total_orders, total_amount, 
        avg_order_amount, last_order_date
    )
    SELECT 
        NEW.user_id,
        COUNT(*),
        SUM(order_amount),
        AVG(order_amount),
        MAX(created_at)
    FROM orders 
    WHERE user_id = NEW.user_id
    ON DUPLICATE KEY UPDATE
        total_orders = VALUES(total_orders),
        total_amount = VALUES(total_amount),
        avg_order_amount = VALUES(avg_order_amount),
        last_order_date = VALUES(last_order_date);
END$$
DELIMITER ;

-- 4. 物化视图模拟（MySQL不支持物化视图，用表+定时任务模拟）
CREATE TABLE mv_daily_sales AS
SELECT 
    DATE(created_at) as sale_date,
    COUNT(*) as order_count,
    SUM(order_amount) as total_sales,
    AVG(order_amount) as avg_order_value,
    COUNT(DISTINCT user_id) as unique_customers
FROM orders 
WHERE created_at >= '2024-01-01'
GROUP BY DATE(created_at);

-- 添加索引
ALTER TABLE mv_daily_sales ADD PRIMARY KEY (sale_date);

-- 定时刷新物化视图的存储过程
DELIMITER $$
CREATE PROCEDURE refresh_daily_sales_mv()
BEGIN
    DECLARE last_date DATE;
    
    -- 获取最后更新日期
    SELECT MAX(sale_date) INTO last_date FROM mv_daily_sales;
    
    -- 删除今天的数据（如果存在）
    DELETE FROM mv_daily_sales WHERE sale_date = CURDATE();
    
    -- 插入最新数据
    INSERT INTO mv_daily_sales
    SELECT 
        DATE(created_at) as sale_date,
        COUNT(*) as order_count,
        SUM(order_amount) as total_sales,
        AVG(order_amount) as avg_order_value,
        COUNT(DISTINCT user_id) as unique_customers
    FROM orders 
    WHERE DATE(created_at) >= IFNULL(last_date, '2024-01-01')
    GROUP BY DATE(created_at);
END$$
DELIMITER ;

-- 参数调优查询和建议

-- 1. 当前重要参数查看
SELECT 
    VARIABLE_NAME,
    VARIABLE_VALUE,
    CASE VARIABLE_NAME
        WHEN 'innodb_buffer_pool_size' THEN 
            CONCAT('建议设置为物理内存的70-80%，当前约为 ', 
                   ROUND(VARIABLE_VALUE/1024/1024/1024, 2), 'GB')
        WHEN 'max_connections' THEN 
            CONCAT('当前最大连接数，建议根据应用并发量调整')
        WHEN 'innodb_log_file_size' THEN 
            CONCAT('重做日志大小，当前为 ', 
                   ROUND(VARIABLE_VALUE/1024/1024, 2), 'MB')
        ELSE '需要根据具体场景调优'
    END as suggestion
FROM information_schema.GLOBAL_VARIABLES 
WHERE VARIABLE_NAME IN (
    'innodb_buffer_pool_size',
    'max_connections',
    'innodb_log_file_size',
    'innodb_flush_log_at_trx_commit',
    'sort_buffer_size',
    'tmp_table_size'
)
ORDER BY VARIABLE_NAME;

-- 2. 性能状态监控
SELECT 
    'Buffer Pool Hit Rate' as metric,
    ROUND(
        (1 - (
            (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
            (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
        )) * 100, 2
    ) as value_percent,
    '> 99%为优秀' as benchmark
UNION ALL
SELECT 
    'Query Cache Hit Rate',
    ROUND(
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Qcache_hits') /
        ((SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Qcache_hits') +
         (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Com_select')) * 100, 2
    ),
    '> 80%为良好'
UNION ALL
SELECT 
    'Table Lock Wait Rate',
    ROUND(
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Table_locks_waited') /
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Table_locks_immediate') * 100, 2
    ),
    '< 1%为优秀';

-- 3. 连接和线程状态分析
SELECT 
    'Current Connections' as metric,
    (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Threads_connected') as current_value,
    (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'max_connections') as max_value,
    ROUND(
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Threads_connected') /
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'max_connections') * 100, 2
    ) as usage_percent;