使用说明

使用MCP进行PostgreSQL数据库管理

一个全面的数据库管理项目，演示了Docker容器化、PostgreSQL设置、模式设计和使用模型上下文协议（MCP）工具的数据库性能优化。

📋 目录

项目概述
特性
建筑
数据库模式
使用的技术
先决条件
安装和设置
用法
性能优化
数据库审核结果
项目结构
主要学习内容
文档
贡献
许可证

🎯 项目概述

这个项目通过一个生产就绪的PostgreSQL设置展示了现代数据库管理实践。它表明：

容器化数据库基础架构：基于Docker的PostgreSQL 16部署
模式设计：具有4个核心表的标准化电子商务数据库
性能优化：自动化数据库审计和索引建议
健康监测：缓存命中率分析和查询性能指标
MCP集成：使用模型上下文协议工具的数据库操作

数据库统计

度量	值
表格总数	4
总记录数	约43000行
数据库大小	约3.5 MB
缓存命中率	99.94%
客户	2,000
订单	10,000
项目	30,000
产品	1,000

✨ 特性

🔧 Docker容器化：独立的PostgreSQL 16环境
📊 数据库审核：自动健康检查和性能分析
🔍 指标优化：标识外键上缺少的索引
📈 性能指标：缓存命中率监控和查询分析
🛠️ 自动脚本：基于Python的数据库管理工具
📝 综合文档：模式文档、快速入门指南和投资组合资源

🏗️ 建筑

数据库模式

该项目实现了一个具有以下结构的规范化电子商务数据库：

核心表

customers -客户信息，包括注册详细信息、国家和级别
orders -包含状态、金额和区域数据的订单记录
products -产品目录，包括类别、定价和库存水平
order_items -连接订单与数量产品的连接表

实体关系

customers (1) ────< (many) orders (1) ────< (many) order_items
                                                        │
                                                        │ (many)
                                                        │
                                              products (1)

customers → orders （一对多）
orders → order_items （一对多）
products → order_items （一对多）

系统架构

┌─────────────────┐
│   Python Script │
│    (main.py)    │
└────────┬────────┘
         │
         │ Docker Exec
         ▼
┌─────────────────┐
│  Docker Container│
│   (pg-local)    │
│                 │
│  PostgreSQL 16  │
│  Port: 5433     │
└─────────────────┘

📊 数据库模式

实体关系图

erDiagram
    CUSTOMERS ||--o{ ORDERS : "places"
    ORDERS ||--o{ ORDER_ITEMS : "contains"
    PRODUCTS ||--o{ ORDER_ITEMS : "included_in"
    
    CUSTOMERS {
        int customer_id PK
        varchar email
        date registration_date
        varchar country
        varchar tier
    }
    
    ORDERS {
        int order_id PK
        int customer_id FK
        date order_date
        varchar status
        numeric total_amount
        varchar region
    }
    
    PRODUCTS {
        int product_id PK
        varchar name
        varchar category
        numeric price
        int stock_quantity
    }
    
    ORDER_ITEMS {
        int item_id PK
        int order_id FK
        int product_id FK
        int quantity
        numeric price
    }

表详细信息

有关详细的表模式、列定义和关系，请参见 docs/database_schema.md.

🛠️ 使用的技术

PostgreSQL 16：企业级关系数据库管理系统
码头工人：用于孤立数据库环境的容器化平台
MCP（模型上下文协议）：数据库管理和操作工具
Python 3.12+：用于自动化和数据库管理的脚本语言
结构化查询语言：用于模式设计和优化的数据库查询语言

📦 先决条件

在开始之前，请确保已安装以下内容：

Docker桌面 （或Docker引擎）- 安装Docker
Python 3.12或更高版本 - 安装Python
MCP服务器 已配置（Docker和PostgreSQL MCP）-可选，但建议
Git -用于克隆存储库

🚀 安装和设置

步骤1：克隆存储库

git clone <repository-url>
cd MCP-data-series

步骤2：创建PostgreSQL容器

使用以下配置启动PostgreSQL 16容器：

docker run -d \
  --name pg-local \
  -p 5433:5432 \
  -e POSTGRES_USER=app \
  -e POSTGRES_PASSWORD=app \
  -e POSTGRES_DB=demo \
  postgres:16

备注：集装箱使用港口 5433 避免与端口上的本地PostgreSQL安装冲突 5432.

步骤3：验证容器是否正在运行

docker ps | grep pg-local

您应该看到容器正在运行。如果没有，请检查日志：

docker logs pg-local

步骤4：创建架构并设置权限

连接到数据库并创建架构：

docker exec -i pg-local psql -U app -d demo << EOF
-- Grant permissions
GRANT ALL PRIVILEGES ON DATABASE demo TO app;

-- Create schema
CREATE SCHEMA IF NOT EXISTS app AUTHORIZATION app;
EOF

步骤5：加载样本数据

使用架构和数据加载示例数据库：

docker exec -i pg-local psql -U app -d demo < demo_data.sql

这将创建所有表，并用约43000条示例记录填充它们。

步骤6：验证安装

运行数据库审核脚本以验证一切正常：

python main.py

您应该看到显示表、统计信息、缓存性能和索引审核结果的输出。

💻 用法

正在运行数据库操作

执行主脚本以执行全面的数据库审核：

python main.py

预期产量：

============================================================
PostgreSQL Database Management - Portfolio Project
============================================================

📊 Database Tables:
------------------------------------------------------------
  ✓ customers
  ✓ order_items
  ✓ orders
  ✓ products

📈 Table Statistics:
------------------------------------------------------------
[Table statistics with row counts and sizes]

💾 Cache Performance:
------------------------------------------------------------
  Cache Hit Rate: 99.94% ✅ Excellent

🔍 Index Audit:
------------------------------------------------------------
  Missing indexes on foreign keys:
    ⚠️  orders.customer_id
    ⚠️  order_items.order_id
    ⚠️  order_items.product_id

🚀 Optimization Recommendations:
------------------------------------------------------------
  CREATE INDEX idx_orders_customer_id ON app.orders(customer_id);
  CREATE INDEX idx_order_items_order_id ON app.order_items(order_id);
  CREATE INDEX idx_order_items_product_id ON app.order_items(product_id);

连接到数据库

直接连接到PostgreSQL数据库：

docker exec -i pg-local psql -U app -d demo

使用PostgreSQLManager类

这 PostgreSQLManager 类为数据库操作提供了多种方法：

from main import PostgreSQLManager

# Initialize manager
db = PostgreSQLManager()

# List all tables
tables = db.list_tables()
print(tables)

# Get table statistics
stats = db.get_table_stats()
print(stats)

# Check cache hit rate
cache_rate = db.get_cache_hit_rate()
print(f"Cache Hit Rate: {cache_rate}%")

# Check for missing indexes
missing = db.check_indexes()
print(missing)

# Generate optimization SQL
sql_statements = db.generate_optimization_sql()
for sql in sql_statements:
    print(sql)

示例查询

加入客户和订单

SELECT 
    o.order_id,
    o.order_date,
    o.total_amount,
    c.email,
    c.country,
    c.tier
FROM app.orders o
JOIN app.customers c ON o.customer_id = c.customer_id
LIMIT 100;

获取产品订单详细信息

SELECT 
    oi.item_id,
    o.order_date,
    p.name as product_name,
    p.category,
    oi.quantity,
    oi.price
FROM app.order_items oi
JOIN app.orders o ON oi.order_id = o.order_id
JOIN app.products p ON oi.product_id = p.product_id
WHERE o.order_date > '2025-01-01'
LIMIT 100;

📈 性能优化

已识别的问题

数据库审计发现了三个关键的性能瓶颈：

缺少索引 orders.customer_id：导致对10K行表进行顺序扫描
缺少索引 order_items.order_id：30K行表上的顺序扫描
缺少索引 order_items.product_id：连接时性能下降

优化查询

应用推荐的指标来提高性能：

-- Create foreign key indexes
CREATE INDEX idx_orders_customer_id ON app.orders(customer_id);
CREATE INDEX idx_order_items_order_id ON app.order_items(order_id);
CREATE INDEX idx_order_items_product_id ON app.order_items(product_id);

-- Update statistics for query planner
ANALYZE app.orders;
ANALYZE app.order_items;

预期性能改进

优化	预期改进
`orders.customer_id` 索引	加入速度提高30-50%
`order_items.order_id` 索引	查询速度提高40-60%
`order_items.product_id` 索引	产品查询改善35-50%

验证优化

创建索引后，验证改进：

-- Check index usage
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan as index_scans
FROM pg_stat_user_indexes
WHERE schemaname = 'app'
ORDER BY idx_scan DESC;

🔍 数据库审核结果

问题	影响	优先级	状态
缺少FK索引	对大型表进行顺序扫描	高	✅ 已识别
缓存命中率	99.94%（优秀）	-	✅ 最佳
查询性能	0.448ms执行时间	-	✅ 很好

健康检查指标

缓存命中率：99.94%（优秀-目标：>99%）
查询执行时间：0.448ms（性能良好）
数据库大小：约3.5 MB（高效存储）
索引覆盖率：发现3个缺失索引

📁 项目结构

MCP-data-series/
├── README.md                    # This file - comprehensive project documentation
├── QUICK_START.md              # Quick setup guide
├── PORTFOLIO_GUIDE.md          # Portfolio presentation guide
├── main.py                     # Database operations and audit script
├── pyproject.toml              # Python project configuration
├── demo_data.sql               # Database schema and sample data (43K+ records)
├── .gitignore                  # Git ignore patterns
├── .python-version             # Python version specification
└── docs/                       # Additional documentation
    ├── database_schema.md      # Detailed schema documentation
    └── portfolio-presentation.md # Portfolio presentation tips

🎓 展示的关键学习和技能

该项目展示了在以下方面的熟练程度：

容器编排
- Docker容器生命周期管理
- 端口映射和配置
- 容器网络和隔离
数据库设计
- 电子商务系统的规范化模式设计
- 外键关系和引用完整性
- 数据建模最佳实践
性能调整
- 使用EXPLAIN ANALYZE进行查询分析
- 指数优化策略
- 缓存监控和优化
- 执行计划分析
数据库管理
- 用户和权限管理
- 模式创建和组织
- 数据库健康监控
- 自动化审计脚本
MCP集成
- 使用模型上下文协议进行数据库操作
- 自动化数据库管理工作流程
查询优化
- 识别性能瓶颈
- 创建战略指标
- 分析查询执行计划
- 衡量绩效改进
数据库健康监控
- 缓存命中率分析
- 指数覆盖审计
- 查询性能指标
- 自动健康检查

📚 文档

快速启动.md：5分钟后开始
投资组合指南.md：如何在你的作品集中展示这个项目
docs/database_schema.md：详细的数据库模式文档
docs/portfolio-presentation.md：投资组合演示技巧

🐛 故障排除

端口5432已在使用中

容器配置为使用端口 5433 默认情况下。如果您需要使用其他端口：

docker run -d \
  --name pg-local \
  -p <your-port>:5432 \
  -e POSTGRES_USER=app \
  -e POSTGRES_PASSWORD=app \
  -e POSTGRES_DB=demo \
  postgres:16

容器已存在

如果容器名称冲突：

docker rm -f pg-local

然后使用设置说明重新创建容器。

无法连接到数据库

检查集装箱状态：
```
docker ps -a | grep pg-local
```
检查容器日志：
```
docker logs pg-local
```
如果需要，重新启动容器：
```
docker start pg-local
```

权限被拒绝错误

确保 app 用户具有适当的权限：

docker exec -i pg-local psql -U app -d demo -c "GRANT ALL PRIVILEGES ON SCHEMA app TO app;"

🤝 贡献

这是一个展示数据库管理技能的投资组合项目。欢迎提出意见、建议和改进！

分叉存储库
创建要素分支(git checkout -b feature/amazing-feature)
提交您的更改(git commit -m 'Add some amazing feature')
推到分支(git push origin feature/amazing-feature)
打开拉取请求

📄 许可证

此项目根据MIT许可证获得许可-有关详细信息，请参阅许可证文件。

欢迎将此作为学习资源！

🙏 致谢

PostgreSQL社区 -优秀的文档和工具
码头工人 -用于集装箱化平台
MCP协议 -用于数据库管理工具
Python社区 -用于强大的数据库库

🎯 项目亮点

作为投资组合项目的一部分构建，展示：

✅ 真实世界的数据库管理技能
✅ 生产就绪的PostgreSQL设置
✅ 性能优化专业知识
✅ 容器编排熟练程度
✅ 自动化数据库管理
✅ 全面的文件

关键指标：

📊 管理43000+条记录
⚡ 99.94%的缓存命中率
🚀 已确定40-60%的性能改进
🔧 建议进行3项关键优化

有关快速设置，请参阅快速启动.md
关于投资组合的展示，请参见投资组合指南.md