SQL性能分析 基础方法

# 基础Prompt
Review this SQL query and tell me if it has any performance issues:

SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at > '2024-01-01'

SQL性能分析 CoT高级方法

# 思维链Prompt
Review this SQL query for performance issues. Think step by step:

Query:
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at > '2024-01-01'

Analysis steps:
1. Examine the SELECT clause - what's being returned?
2. Check the JOIN - is it efficient? What indexes are needed?
3. Analyze the WHERE clause - can it use indexes?
4. Consider row estimates - how many rows match?
5. Think about I/O costs - what's the disk access pattern?

Show reasoning for each step, then summarize issues found.

# Zero-Shot CoT - 只需简单添加这句话
Solve this problem step by step:

A store sells apples for $2 each. If you buy 5 or more, 
you get 20% off. How much do 7 apples cost?

# 模型会自行推理
Let me work through this step by step:
1. 7 apples × $2 = $14 (original price)
2. Since 7 >= 5, we get 20% off
3. Discount: $14 × 20% = $2.80
4. Final price: $14 - $2.80 = $11.20

# Few-Shot Prompt示例
"""
分析用户评论的情感倾向，返回结构化JSON。

示例1:
输入: "这个产品太棒了！完全超出预期，客服也很贴心。"
输出: {"sentiment": "positive", "score": 0.95, "keywords": ["太棒了", "超出预期", "贴心"]}

示例2:
输入: "等了一周还没发货，体验很差。"
输出: {"sentiment": "negative", "score": 0.2, "keywords": ["等了一周", "没发货", "体验差"]}

示例3:
输入: "东西还行，就是发货有点慢。"
输出: {"sentiment": "neutral", "score": 0.5, "keywords": ["还行", "发货慢"]}

现在分析:
输入: "刚收到货，质量很不错，就是包装有点简陋"
输出: """

# Few-Shot CoT示例
"""
数学问题：先推理，再给出答案

示例问题：小明有15个苹果，给了小红7个，又买了5个，现在有多少个？
思考过程：
1. 小明原有15个苹果
2. 给出小红7个：15 - 7 = 8个
3. 又买了5个：8 + 5 = 13个
最终答案：13个

示例问题：一件衬衫40元，打8折后再降价10元，实际付多少？
思考过程：
1. 原价40元
2. 打8折：40 × 0.8 = 32元
3. 再降10元：32 - 10 = 22元
最终答案：22元

现在请解答：
问题：一个班级有30人，2/5是女生，女生有多少人？"""

import asyncio
from collections import Counter

async def self_consistent_reasoning(
    problem: str, 
    num_samples: int = 5,
    temperature: float = 0.7
) -> str:
    """自一致性推理：生成多个答案，选择最一致的"""
    
    async def generate_one_attempt(attempt_num: int) -> str:
        prompt = f"""Solve this problem step-by-step (attempt {attempt_num}):

{problem}

Show your reasoning, then provide final answer in format:
ANSWER: <your answer>"""
        
        response = await openai.chat.completions.create(
            model="gpt-4",
            messages=[{"role": "user", "content": prompt}],
            temperature=temperature,
        )
        
        content = response.choices[0].message.content
        # 提取ANSWER后面的内容
        if "ANSWER:" in content:
            return content.split("ANSWER:")[-1].strip()
        return content

    # 并发生成多个答案
    tasks = [generate_one_attempt(i) for i in range(num_samples)]
    responses = await asyncio.gather(*tasks)
    
    # 统计最常见的答案
    answer_counts = Counter(responses)
    most_common = answer_counts.most_common(1)[0][0]
    
    return most_common

# 使用示例
result = await self_consistent_reasoning(
    "如果3台机器3分钟能生产3个零件，"
    "那么10台机器生产100个零件需要多少分钟？"
)
print(f"自一致性答案: {result}")

Problem: Design a caching strategy for a high-traffic API 
serving 10k req/sec.

Let's explore this as a tree of solutions:

LEVEL 1 - Broad Approaches:
A) In-memory caching (Redis/Memcached)
B) Edge caching (CDN)
C) Database query caching
D) Application-level caching

Evaluate each approach:
- Cost
- Complexity
- Hit rate potential
- Invalidation challenges

Select top 2 most promising approaches.

LEVEL 2 - Deep dive on selected approaches:
For each selected approach:
- Specific implementation details
- Scaling characteristics
- Failure modes
- Monitoring strategy

LEVEL 3 - Hybrid solution:
Can we combine the best elements of both?

Show the complete reasoning tree, then recommend final architecture.

You are a research assistant with access to these tools:
- search(query): Search the web for information
- read_url(url): Read the contents of a webpage  
- calculate(expression): Evaluate a math expression
- python(code): Execute Python code

Task: Find the current market cap of the top 3 AI companies 
and calculate the total.

Use this format for each step:
Thought: [What I need to do next and why]
Action: [tool_name(arguments)]
Observation: [Result from the tool]

... (repeat until task is complete)

Final Answer: [Summary with calculations]

Begin:

from dataclasses import dataclass
from typing import Literal

@dataclass
class ReActStep:
    thought: str
    action: str
    observation: str | None = None

class ReActAgent:
    def __init__(self, tools: dict):
        self.tools = tools
        self.steps: list[ReActStep] = []
    
    async def run(self, task: str, max_iterations: int = 10) -> str:
        """执行ReAct循环"""
        
        for i in range(max_iterations):
            # 1. Thought - 推理下一步
            thought_prompt = self._build_thought_prompt(task)
            thought = await self.llm.generate(thought_prompt)
            
            # 2. Parse action
            action_type, action_args = self._parse_action(thought)
            
            if action_type == "Final Answer":
                return action_args
            
            # 3. Execute action
            if action_type in self.tools:
                observation = await self.tools[action_type](**action_args)
            else:
                observation = f"Unknown tool: {action_type}"
            
            # 4. Record step
            self.steps.append(ReActStep(thought, action_type, observation))
        
        return "Max iterations reached"
    
    def _parse_action(self, text: str) -> tuple[str, dict]:
        """解析Thought文本中的Action"""
        # 提取 Action: tool_name(args) 格式
        # 返回 (tool_name, {arg_name: value})
        pass

You are a code review assistant. Follow these constitutional principles:

1. Security First: Never suggest code with SQL injection, 
   XSS, or authentication bypass vulnerabilities

2. Maintainability: Prioritize readable code over clever code

3. Performance Awareness: Flag O(n²) algorithms when 
   O(n log n) alternatives exist

4. Error Handling: All async operations must have error handlers

5. Testing: Suggest testable designs over tightly coupled ones

Review this code:
[paste code]

First, review normally. Then, critique your own review 
against each constitutional principle above. 
Did you miss anything? Revise if needed.

Final review:

API Design Constitution:

1. RESTful: Follow REST principles unless there's compelling reason not to

2. Versioning: All APIs must be versioned (v1, v2, etc.)

3. Error Format: Errors must return 
   {status, error, message, details}

4. Authentication: All endpoints except health checks require auth

5. Rate Limiting: Consider and document rate limit strategy

6. Documentation: Every endpoint needs OpenAPI/Swagger docs

7. Backward Compatibility: Avoid breaking changes within versions

System Design 避免：一个巨大Prompt

# 不要这样做
"Analyze this codebase and create a microservices architecture 
plan with migration strategy"

System Design 推荐：分解为多个专注Prompt

# Prompt 1: 识别业务域
"Analyze this monolith and identify distinct business domains"
→ 输出: domains_list

# Prompt 2: 映射资源
"For each domain in {domains_list}, map which database tables, 
API endpoints, and business logic belong to it"
→ 输出: resource_mappings

# Prompt 3: 分析依赖
"Identify dependencies between domains. Which are tightly coupled?"
→ 输出: dependency_graph

# Prompt 4: 确定迁移顺序
"Recommend migration order based on coupling analysis. 
Which services to extract first?"
→ 输出: migration_sequence

# Prompt 5: 详细设计
"For the first service [X], design the service boundaries, 
API contract, and data migration strategy"
→ 输出: detailed_plan

Extract bug information from this GitHub issue and return 
as valid JSON matching this schema:

interface BugReport {
  severity: 'critical' | 'high' | 'medium' | 'low',
  category: string,
  affected_versions: string[],
  reproduction_steps: string[],
  expected_behavior: string,
  actual_behavior: string,
  proposed_fix?: string,
  estimated_effort_hours?: number
}

Issue text:
"The login page crashes on Safari 16 when clicking submit 
after entering credentials. It works fine on Chrome and Firefox. 
Started happening after v2.3.0 deploy. Console shows 
TypeError: Cannot read property 'token' of undefined..."

Return ONLY valid JSON, no explanation:
{

# 在生成JSON后，追加验证步骤
[After generating structured output]

Now validate your JSON output:
1. Check all required fields are present
2. Verify types match the schema (string, number, array, etc.)
3. Confirm enum values are from allowed list
4. Check array items follow item schema
5. Validate string formats (dates, emails, URLs)

If validation fails, regenerate corrected JSON.

from pydantic import BaseModel, Field, ValidationError
from typing import Optional

class BugReport(BaseModel):
    severity: Literal['critical', 'high', 'medium', 'low']
    category: str
    affected_versions: list[str]
    reproduction_steps: list[str]
    expected_behavior: str
    actual_behavior: str
    proposed_fix: Optional[str] = None
    estimated_effort_hours: Optional[float] = None

async def extract_bug_report(issue_text: str) -> BugReport:
    """从GitHub issue提取结构化Bug报告"""
    
    prompt = f"""Extract bug information as valid JSON:

{issue_text}

Schema:
{BugReport.model_json_schema()}

Return ONLY valid JSON:"""
    
    response = await openai.chat.completions.create(
        model="gpt-4",
        messages=[{"role": "user", "content": prompt}],
        response_format={"type": "json_object"},
    )
    
    data = json.loads(response.choices[0].message.content)
    return BugReport(**data)  # Pydantic自动验证

I'm going to give you a prompt I wrote, and I want you to improve it.

My prompt:
"Write a function to process user data"

Analyze this prompt and provide:

1. What's missing? (context, constraints, format, edge cases)
2. What's ambiguous? (what assumptions would you have to make?)
3. An improved version that would get better results
4. Why your version is better

Then, let's use your improved version to write the actual function.

Before/After 基础版本

Write a test for this login function.

function login(email, password) {
  // ...implementation
}

Before/After 元提示优化版本

Write comprehensive unit tests for this login function.

Function signature:
function login(email: string, password: string): Promise<{
  success: boolean, 
  token?: string, 
  error?: string
}>

Requirements:
- Test framework: Jest
- Coverage: Happy path, validation errors, authentication failures, edge cases
- Style: AAA pattern (Arrange, Act, Assert)
- Include: Setup/teardown, mocks for API calls
- Document: Each test with clear description

Test cases to cover:
1. Valid credentials return success + token
2. Invalid email format returns validation error
3. Wrong password returns auth error
4. Empty fields return validation errors
5. SQL injection attempt is safely handled
6. API timeout is handled gracefully

Provide complete, runnable tests with all imports.

// Context Engineering Stack
const context = [
  // Layer 1: 系统身份和约束
  { 
    role: 'system', 
    content: `You are a senior code reviewer.
    Constraints: Only flag issues with severity >= medium.
    Output format: JSON array of findings.`
  },

  // Layer 2: 检索的文档 (RAG)
  { 
    role: 'system', 
    content: `Relevant style guide sections:
    ${await vectorSearch(codeSnippet, 'style-guide-index')}`
  },

  // Layer 3: 最近上下文
  { 
    role: 'system', 
    content: `Previous review findings for this PR:
    ${await getPriorFindings(prNumber)}`
  },

  // Layer 4: 工具定义
  { 
    tools: [searchCodebase, runLinter, checkTypes] 
  },

  // Layer 5: 实际用户Prompt
  { 
    role: 'user', 
    content: `Review this diff:\n${diff}` 
  }
]

// "Prompt"只是第5层——上下文工程是1-4层