kknongchang_2506/app/Module/Transfer/Database/optimization.sql

-- Transfer模块数据库优化SQL
-- 用于提升查询性能和数据库效率

-- =====================================================
-- 索引优化
-- =====================================================

-- 1. transfer_orders表索引优化
-- 用户订单查询索引（最常用）
CREATE INDEX idx_transfer_orders_user_created ON kku_transfer_orders(user_id, created_at DESC);

-- 应用订单查询索引
CREATE INDEX idx_transfer_orders_app_status ON kku_transfer_orders(transfer_app_id, status, created_at DESC);

-- 外部订单ID查询索引（唯一性约束）
CREATE UNIQUE INDEX idx_transfer_orders_out_order ON kku_transfer_orders(out_id, out_order_id);

-- 状态和类型查询索引
CREATE INDEX idx_transfer_orders_status_type ON kku_transfer_orders(status, type, created_at DESC);

-- 处理时间查询索引
CREATE INDEX idx_transfer_orders_processing ON kku_transfer_orders(status, created_at) 
WHERE status IN (1, 20); -- 已创建和处理中状态

-- 完成时间统计索引
CREATE INDEX idx_transfer_orders_completed ON kku_transfer_orders(status, completed_at) 
WHERE status = 100; -- 已完成状态

-- 2. transfer_apps表索引优化
-- 应用标识符唯一索引
CREATE UNIQUE INDEX idx_transfer_apps_keyname ON kku_transfer_apps(keyname);

-- 外部应用ID索引
CREATE INDEX idx_transfer_apps_out_id ON kku_transfer_apps(out_id);

-- 启用状态索引
CREATE INDEX idx_transfer_apps_enabled ON kku_transfer_apps(is_enabled, created_at DESC);

-- =====================================================
-- 分区表优化（可选，适用于大数据量）
-- =====================================================

-- 按月分区transfer_orders表（适用于订单量很大的情况）
-- 注意：需要在创建表时就设计分区，这里仅作为参考

/*
-- 创建分区表的示例（需要重新设计表结构）
CREATE TABLE kku_transfer_orders_partitioned (
    -- 所有字段定义...
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
    PARTITION p202501 VALUES LESS THAN (202502),
    PARTITION p202502 VALUES LESS THAN (202503),
    PARTITION p202503 VALUES LESS THAN (202504),
    -- 继续添加分区...
    PARTITION p_future VALUES LESS THAN MAXVALUE
);
*/

-- =====================================================
-- 查询优化建议
-- =====================================================

-- 1. 常用查询的优化版本

-- 用户订单列表查询（分页）
-- 优化前：SELECT * FROM kku_transfer_orders WHERE user_id = ? ORDER BY created_at DESC LIMIT ?, ?
-- 优化后：使用覆盖索引
EXPLAIN SELECT id, transfer_app_id, out_order_id, type, status, out_amount, amount, created_at 
FROM kku_transfer_orders 
WHERE user_id = ? 
ORDER BY created_at DESC 
LIMIT ?, ?;

-- 应用订单统计查询
-- 优化前：SELECT COUNT(*), SUM(amount) FROM kku_transfer_orders WHERE transfer_app_id = ?
-- 优化后：添加状态条件减少扫描行数
EXPLAIN SELECT 
    COUNT(*) as total_count,
    SUM(CASE WHEN status = 100 THEN amount ELSE 0 END) as completed_amount,
    SUM(CASE WHEN status = 100 THEN 1 ELSE 0 END) as completed_count
FROM kku_transfer_orders 
WHERE transfer_app_id = ? 
AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);

-- 今日订单统计
-- 使用日期范围而不是DATE函数
EXPLAIN SELECT status, COUNT(*), SUM(amount)
FROM kku_transfer_orders 
WHERE created_at >= CURDATE() 
AND created_at < DATE_ADD(CURDATE(), INTERVAL 1 DAY)
GROUP BY status;

-- =====================================================
-- 数据清理和维护
-- =====================================================

-- 1. 定期清理过期数据（可选）
-- 删除6个月前的已完成订单（根据业务需求调整）
-- DELETE FROM kku_transfer_orders 
-- WHERE status = 100 
-- AND completed_at < DATE_SUB(NOW(), INTERVAL 6 MONTH)
-- LIMIT 1000; -- 分批删除，避免锁表

-- 2. 表维护命令
-- 优化表结构
-- OPTIMIZE TABLE kku_transfer_orders;
-- OPTIMIZE TABLE kku_transfer_apps;

-- 分析表统计信息
-- ANALYZE TABLE kku_transfer_orders;
-- ANALYZE TABLE kku_transfer_apps;

-- =====================================================
-- 监控查询
-- =====================================================

-- 1. 慢查询监控
-- 查找执行时间超过1秒的查询
SELECT 
    query_time,
    lock_time,
    rows_sent,
    rows_examined,
    sql_text
FROM mysql.slow_log 
WHERE sql_text LIKE '%transfer_orders%' 
AND query_time > 1
ORDER BY query_time DESC
LIMIT 10;

-- 2. 索引使用情况监控
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    CARDINALITY,
    SUB_PART,
    NULLABLE,
    INDEX_TYPE
FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME IN ('kku_transfer_orders', 'kku_transfer_apps')
ORDER BY TABLE_NAME, SEQ_IN_INDEX;

-- 3. 表大小监控
SELECT 
    TABLE_NAME,
    ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS 'Size (MB)',
    TABLE_ROWS,
    ROUND((INDEX_LENGTH / 1024 / 1024), 2) AS 'Index Size (MB)'
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME IN ('kku_transfer_orders', 'kku_transfer_apps');

-- =====================================================
-- 性能测试查询
-- =====================================================

-- 测试用户订单查询性能
SET @user_id = 1;
SET @start_time = NOW();

SELECT SQL_NO_CACHE id, out_order_id, type, status, amount, created_at
FROM kku_transfer_orders 
WHERE user_id = @user_id 
ORDER BY created_at DESC 
LIMIT 20;

SELECT TIMEDIFF(NOW(), @start_time) as query_time;

-- 测试应用统计查询性能
SET @app_id = 1;
SET @start_time = NOW();

SELECT SQL_NO_CACHE
    COUNT(*) as total_orders,
    SUM(CASE WHEN status = 100 THEN 1 ELSE 0 END) as completed_orders,
    SUM(CASE WHEN status = -1 THEN 1 ELSE 0 END) as failed_orders,
    SUM(amount) as total_amount
FROM kku_transfer_orders 
WHERE transfer_app_id = @app_id;

SELECT TIMEDIFF(NOW(), @start_time) as query_time;

-- =====================================================
-- 备份和恢复建议
-- =====================================================

-- 1. 定期备份重要数据
-- mysqldump --single-transaction --routines --triggers kku_laravel kku_transfer_orders kku_transfer_apps > transfer_backup.sql

-- 2. 增量备份策略
-- 使用binlog进行增量备份，确保数据安全

-- 3. 数据恢复测试
-- 定期测试备份文件的完整性和恢复速度

-- =====================================================
-- 配置优化建议
-- =====================================================

-- MySQL配置优化建议（my.cnf）
/*
# InnoDB优化
innodb_buffer_pool_size = 1G  # 根据内存大小调整
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# 查询缓存
query_cache_type = 1
query_cache_size = 128M

# 连接优化
max_connections = 200
thread_cache_size = 16

# 慢查询日志
slow_query_log = 1
long_query_time = 1
log_queries_not_using_indexes = 1
*/