优化大数据接口：从20秒到500毫秒的实践

# 性能分析代码片段
import cProfile
import pstats

def profile_report_api():
    pr = cProfile.Profile()
    pr.enable()
    
    # 调用原有的报表生成函数
    generate_financial_report(request_data)
    
    pr.disable()
    stats = pstats.Stats(pr)
    stats.sort_stats('cumtime').print_stats(10)

# Copilot辅助生成的分页查询代码
async def get_paginated_financial_data(
    start_date: str, 
    end_date: str, 
    page: int = 1, 
    page_size: int = 100
):
    """
    获取分页的财务数据
    """
    skip = (page - 1) * page_size
    
    # Copilot建议的优化查询语句
    query = """
        SELECT id, transaction_date, amount, description, category
        FROM financial_transactions
        WHERE transaction_date BETWEEN %s AND %s
        ORDER BY transaction_date DESC
        LIMIT %s OFFSET %s
    """
    
    # 使用异步数据库连接执行查询
    results = await database.execute_query(
        query, (start_date, end_date, page_size, skip)
    )
    
    # 获取总数用于前端分页控件
    count_query = """
        SELECT COUNT(*) 
        FROM financial_transactions 
        WHERE transaction_date BETWEEN %s AND %s
    """
    total_count = await database.execute_scalar(
        count_query, (start_date, end_date)
    )
    
    return {
        "data": results,
        "pagination": {
            "current_page": page,
            "page_size": page_size,
            "total_count": total_count,
            "total_pages": (total_count + page_size - 1) // page_size
        }
    }

# Copilot生成的缓存装饰器
def cache_response(ttl: int = 300):
    """
    缓存API响应装饰器
    """
    def decorator(func):
        @wraps(func)
        async def wrapper(*args, **kwargs):
            # 生成唯一的缓存键
            cache_key = f"{func.__name__}:{str(args)}:{str(kwargs)}"
            
            # 尝试从缓存获取
            cached_data = await redis_client.get(cache_key)
            if cached_data:
                return json.loads(cached_data)
            
            # 执行实际函数
            result = await func(*args, **kwargs)
            
            # 缓存结果
            await redis_client.setex(
                cache_key, ttl, json.dumps(result)
            )
            
            return result
        return wrapper
    return decorator

# 应用缓存装饰器
@cache_response(ttl=600)  # 缓存10分钟
async def get_financial_report(start_date, end_date, page=1):
    return await get_paginated_financial_data(
        start_date, end_date, page
    )

# 启用Gzip压缩的FastAPI中间件配置
from fastapi import FastAPI
from fastapi.middleware.gzip import GZipMiddleware

app = FastAPI()

# Copilot建议的压缩配置
app.add_middleware(
    GZipMiddleware,
    minimum_size=1000,  # 对大于1KB的响应启用压缩
)