MySQL 结课项目：基于大模型的智能 SQL 查询与调优系统（InnoAI SQL 助手）
·
适用课程：数据库系统概论 / MySQL 实践
技术栈：openEuler、MySQL 8.0、Python 3.11、Streamlit、大模型 API（腾讯云 TokenHub）
项目源码：见文末
一、项目简介
在日常数据库运维和业务分析中，我们经常需要编写 SQL 查询或优化慢查询。对于初学者来说，写 SQL 容易，但写出高效、安全的 SQL 却不容易。本项目结合大模型（LLM） 的自然语言理解能力，实现：
- 自然语言 → SQL：输入中文业务需求，自动生成并执行 SELECT 查询，返回表格结果 + AI 业务解读。
- SQL 性能调优：输入任意 SELECT 语句，自动获取
EXPLAIN执行计划，并由大模型给出索引建议和 SQL 改写方案。
二、软硬件环境
| 类别 | 规格 / 版本 | 用途 |
|---|---|---|
| 操作系统 | openEuler 22.03 SP4 / RHEL 9 | 运行数据库与 Python 程序 |
| MySQL | 8.0.45 Community | 存储订单测试数据 |
| Python | 3.11.9（源码编译） | 主开发语言 |
| 大模型平台 | 腾讯云 TokenHub（兼容 OpenAI 接口） | 提供 NL2SQL 与调优能力 |
| Python 依赖 | pymysql, python-dotenv, langchain-openai, streamlit, tabulate | 数据库、AI、Web 界面 |
| 网络 | 虚拟机需能访问外网（调用大模型 API） |
三、环境搭建
3.1 虚拟机与系统初始化
- 安装 openEuler 虚拟机（过程略，可参考网络教程）
- 关闭防火墙与 SELinux（便于后续测试）
# 关闭 SELinux（永久生效，需重启）
sed -i '7s/enforcing/disabled/' /etc/selinux/config
# 关闭防火墙
systemctl disable --now firewalld
systemctl status firewalld # 检查状态
- 修改主机名与时间同步
hostnamectl set-hostname server
bash
# 编辑 /etc/chrony.conf，添加阿里云时间源
vim /etc/chrony.conf
# 添加：server ntp.aliyun.com iburst
systemctl restart chronyd
chronyc sources # 查看同步状态
server ntp.aliyun.com iburst
stratumweight 0
driftfile /var/lib/chrony/drift
rtcsync
makestep 10 3
bindcmdaddress 127.0.0.1
bindcmdaddress ::1
keyfile /etc/chrony.keys
commandkey 1
generatecommandkey
logchange 0.5
logdir /var/log/chrony
- 安装基础编译工具
dnf install -y gcc gcc-c++ make cmake zlib-devel bzip2-devel openssl-devel ncurses-devel sqlite-devel readline-devel libffi-devel tk-devel wget tar vim tree net-tools openssh-server
3.2 编译安装 Python 3.11.9
由于 openEuler 自带的 Python 版本较低，我们需要源码编译安装 Python 3.11.9。
- 下载源码包（在
/usr/local/src目录下）
访问 https://www.python.org/downloads/release/python-3119/ 下载Python-3.11.9.tgz，用 XFTP 上传到/usr/local/src。
- 解压并编译
cd /usr/local/src
tar -zxvf Python-3.11.9.tgz
cd Python-3.11.9
./configure --prefix=/usr/local/python3.11 --enable-shared
make -j$(nproc) && make install
- 配置动态链接库与软链接
echo "/usr/local/python3.11/lib" > /etc/ld.so.conf.d/python311.conf
ldconfig
ln -s /usr/local/python3.11/bin/python3.11 /usr/local/bin/python3
ln -s /usr/local/python3.11/bin/pip3.11 /usr/local/bin/pip3
bash # 或重启终端(reboot)
python3 -V
pip3 -V
- 安装项目 Python 依赖
配置阿里云 pip 镜像源，加速下载：
mkdir ~/.pip
vim ~/.pip/pip.conf
# 写入以下内容
[global]
index-url = http://mirrors.aliyun.com/pypi/simple/
[install]
trusted-host = mirrors.aliyun.com
安装依赖包（需使用我们刚安装的 Python 3.11 的 pip）：
pip3 install --upgrade pip
pip3 install pymysql python-dotenv tabulate langchain langchain-openai streamlit sqlparse
3.3 部署 MySQL 8.0.45
- 下载 MySQL 二进制包
访问 https://downloads.mysql.com/archives/community/ ，选择Linux - Generic，版本8.0.45，下载mysql-8.0.45-linux-glibc2.12-x86_64.tar.xz，上传至/root。
- 解压并安装
cd /root
tar -xvf mysql-8.0.45-linux-glibc2.12-x86_64.tar.xz -C /usr/local/
cd /usr/local
mv mysql-8.0.45-linux-glibc2.12-x86_64 mysql
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
cd mysql
mkdir data
chown -R mysql:mysql .
bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
# 注意复制输出的临时密码，例如：A temporary password is generated for root@localhost: xxxxxxxx
启动 MySQL 并修改 root 密码
bin/mysqld_safe --user=mysql & # 后台启动
# 等待几秒后连接
bin/mysql -u root -p
# 输入临时密码
mysql> alter user 'root'@'localhost' identified with mysql_native_password by '123456';
mysql> flush privileges;
mysql> exit;
配置 systemd 服务（方便后续管理）
创建 /etc/my.cnf：
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
tmpdir = /tmp
socket = /tmp/mysql.sock
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
default-storage-engine = INNODB
log_error = error.log
创建 /usr/lib/systemd/system/mysqld.service：
[Unit]
Description=MySQL Server
After=network.target remote-fs.target nss-lookup.target
[Service]
Type=notify
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE=65535
LimitNPROC=65535
Restart=on-failure
RestartPreventExitStatus=1
TimeoutSec=0
[Install]
WantedBy=multi-user.target
然后执行：
systemctl daemon-reload
systemctl enable --now mysqld
systemctl status mysqld # 查看状态
添加 MySQL 命令到 PATH
echo 'export PATH=$PATH:/usr/local/mysql/bin' >> ~/.bash_profile
source ~/.bash_profile
mysql -V
3.4 创建测试数据库与订单表
mysql -uroot -p123456
CREATE DATABASE testdb;
USE testdb;
CREATE TABLE order_info (
id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '订单ID',
user_id INT COMMENT '用户ID',
order_name VARCHAR(200) COMMENT '商品名称',
pay_amount DECIMAL(10,2) COMMENT '支付金额',
create_time DATETIME COMMENT '下单时间'
) ENGINE=InnoDB COMMENT='电商订单业务表';
任意插入 20 条示例数据。
验证数据：
SELECT * FROM order_info;
四、项目代码编写
在 /opt/mysql_ai_tools 目录下创建以下文件：.env、mysql_client.py、prompts.py、main.py、web_main.py。
目录结构：
/opt/mysql_ai_tools/
├── .env
├── main.py
├── mysql_client.py
├── prompts.py
└── web_main.py
4.1 环境变量配置文件 .env
# MySQL 配置
MYSQL_HOST=127.0.0.1
MYSQL_PORT=3306
MYSQL_USER=root
MYSQL_PASSWORD=Root
MYSQL_DB=testdb
# 大模型配置
LLM_API_KEY=sk-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
LLM_BASE_URL=https://tokenhub.tencentmaas.com/v1
LLM_MODEL_NAME=deepseek-v4-pro-202606
LLM_TEMPERATURE=0
安全加固：
chmod 600 /opt/mysql_ai_tools/.env
4.2 数据库操作封装 mysql_client.py
该文件负责连接 MySQL、执行查询、获取 EXPLAIN 计划，并拦截危险 SQL（仅允许 SELECT）。
核心代码：
# -*- coding: utf-8 -*-
# 文件名：mysql_client.py
# 功能：MySQL8.0数据库统一封装类
# 作用：封装数据库连接、普通查询、EXPLAIN执行计划、SQL安全拦截，统一抛出友好异常，给上层业务调用
import pymysql
import os
import re
from dotenv import load_dotenv
# 加载项目根目录下.env文件的数据库配置
load_dotenv()
class Mysql80Client:
# 数据库操作封装类，所有数据库相关操作统一在此管理
def __init__(self):
# 初始化时读取环境变量，参数缺失则设置兜底默认值，防止程序直接崩溃
self.host = os.getenv("MYSQL_HOST", "127.0.0.1")
self.port = int(os.getenv("MYSQL_PORT", "3306"))
self.user = os.getenv("MYSQL_USER", "root")
self.password = os.getenv("MYSQL_PASSWORD", "")
self.database = os.getenv("MYSQL_DB", "testdb")
# 数据库连接对象，初始为空
self.conn = None
# 实例创建后自动建立数据库连接
self.connect()
def connect(self):
"""创建数据库连接，捕获连接异常并抛出可读错误信息"""
try:
self.conn = pymysql.connect(
host=self.host,
port=self.port,
user=self.user,
password=self.password,
database=self.database,
charset='utf8mb4', # 支持中文、emoji完整字符集
cursorclass=pymysql.cursors.DictCursor # 查询结果以字典返回，方便按字段取值
)
except pymysql.MySQLError as e:
# MySQL专属连接错误，提示账号、地址、密码排查方向
raise Exception(f"数据库连接失败，请检查地址/账号/密码：{e.args[1]}")
except Exception as e:
# 其余未知连接异常统一捕获
raise Exception(f"数据库连接异常：{str(e)}")
@staticmethod
def _check_sql_safety(sql: str) -> None:
"""
静态私有安全校验方法
核心防护：拦截增删改、建表删表等危险操作，仅允许SELECT查询，防止AI生成危险SQL篡改数据
"""
# 去除首尾空格并转为大写，统一匹配规则
sql_trim = sql.strip().upper()
# 危险操作关键字黑名单
danger_keywords = ["INSERT", "UPDATE", "DELETE", "DROP", "ALTER", "CREATE", "TRUNCATE", "REPLACE"]
for kw in danger_keywords:
# 单词边界匹配，避免字段名包含关键字时误拦截
if re.search(r'\b' + re.escape(kw) + r'\b', sql_trim):
raise Exception(f"安全拦截：禁止执行 {kw} 类型语句，仅支持 SELECT 查询")
def execute_query(self, sql: str):
"""
执行普通SELECT查询
:param sql: 待执行查询语句
:return: (字段名列表, 全部数据行字典列表)
"""
# 执行SQL前先做安全校验，拦截危险语句
self._check_sql_safety(sql)
try:
# with自动管理游标，用完自动释放资源
with self.conn.cursor() as cursor:
cursor.execute(sql)
# 提取查询结果表头字段
columns = [desc[0] for desc in cursor.description]
# 读取全部查询数据
rows = cursor.fetchall()
return columns, rows
except pymysql.MySQLError as e:
# 捕获SQL语法、表不存在等数据库执行错误
raise Exception(f"SQL执行失败（错误码 {e.args[0]}）：{e.args[1]}")
except Exception as e:
# 通用查询异常兜底
raise Exception(f"查询异常：{str(e)}")
def get_explain_plan(self, sql: str):
"""
获取SQL执行计划EXPLAIN，用于性能调优分析
:param sql: 待分析SELECT语句
:return: (执行计划表头, 执行计划详情数据)
"""
# 同样先校验SQL安全性
self._check_sql_safety(sql)
# 拼接EXPLAIN关键字，生成分析语句
explain_sql = f"EXPLAIN {sql}"
try:
with self.conn.cursor() as cursor:
cursor.execute(explain_sql)
columns = [desc[0] for desc in cursor.description]
rows = cursor.fetchall()
return columns, rows
except pymysql.MySQLError as e:
raise Exception(f"获取执行计划失败：{e.args[1]}")
except Exception as e:
raise Exception(f"执行计划异常：{str(e)}")
def close(self):
"""安全关闭数据库连接，释放资源，避免长时间占用连接池"""
# 判断连接存在且未关闭才执行关闭操作
if self.conn and not self.conn._closed:
self.conn.close()
4.3 提示词管理 prompts.py
集中管理 NL2SQL 和 SQL 调优的 Prompt，并提供一个工具函数 extract_sql 从大模型回复中提取纯 SQL。
# -*- coding: utf-8 -*-
# 文件名：prompts.py
# 功能：统一管理项目全部大模型提示词模板，附带SQL提取工具静态方法
# 作用：把AI提示词和业务代码解耦，统一约束模型输出格式，降低SQL解析报错概率
import re
class UnifiedPrompt:
"""
提示词统一管理类
优势：所有SQL生成、性能分析提示词集中存放，表结构仅维护一处，修改不用多处同步；
通过严格规则约束大模型输出，减少格式错乱、编造字段、危险SQL等幻觉问题
"""
# ===================== 全局共用数据表结构 =====================
# 只在此维护订单表字段，下方两套提示词会自动引用，改表结构只需改这里一处
TABLE_SCHEMA = """
表名: order_info (订单信息表)
字段说明:
- id: 订单ID (主键，INT类型)
- user_id: 用户ID (INT类型)
- order_name: 商品名称 (VARCHAR类型)
- pay_amount: 支付金额 (DECIMAL类型)
- create_time: 下单时间 (DATETIME类型)
"""
# ===================== 模板1：自然语言转SQL专用提示词 =====================
NL_TO_SQL_PROMPT = f"""
你是严谨的 MySQL 8.0 数据库开发工程师。
【任务目标】
根据用户自然语言描述的业务需求，生成可直接执行、无语法错误的MySQL查询SQL。
【表结构参考】
{TABLE_SCHEMA}
【强制输出规则】
1. 只能生成 SELECT 查询语句，绝对不允许生成 INSERT/UPDATE/DELETE/DROP 等修改、删除数据的语句。
2. 只能使用上面列出的5个字段，禁止自己编造不存在的字段名。
3. 查询字段可使用中文别名，格式固定为：字段 AS 别名。
4 SQL语法遵循MySQL8.0标准，所有关键字统一大写，方便程序解析。
5. 最终SQL必须包裹在 ```sql ```Markdown代码块内，方便代码提取。
6. 禁止输出任何解释、说明文字，只返回纯SQL代码块，减少解析干扰。
7. 中文别名内部不能带空格，例：订单ID（正确）、订单 ID（错误），避免数据库语法报错。
【用户需求】
{{user_input}}
"""
# ===================== 模板2：SQL性能调优分析专用提示词 =====================
SQL_TUNE_PROMPT = f"""
你是资深 MySQL DBA 性能优化专家。
【任务目标】
根据原始SQL + EXPLAIN执行计划数据，定位查询性能问题并给出可直接落地的优化方案。
【表结构参考】
{TABLE_SCHEMA}
【待分析SQL】
{{sql_input}}
【执行计划数据】
{{explain_data}}
【输出要求】
1. 先点明核心性能问题：全表扫描、无索引、索引失效、扫描行数过多等。
2. 给出完整建索引SQL语句，可直接复制执行。
3. 若原SQL写法存在缺陷，提供改写后的完整优化SQL。
4. 内容简洁、分点罗列，不输出多余废话，便于用户快速阅读。
"""
@staticmethod
def extract_sql(response_text: str) -> str:
"""
静态工具方法：从大模型返回的完整文本里剥离出纯净SQL语句
三层匹配优先级，兼容不同大模型的输出格式，提升提取成功率
:param response_text: 大模型原始完整返回内容
:return: 清洗后的纯SQL字符串，提取失败返回空字符串
"""
# 空文本直接返回
if not response_text:
return ""
# 优先级1：匹配最标准markdown sql代码块（项目提示词强制要求的格式）
match = re.search(r"```sql\s*(.*?)\s*```", response_text, re.DOTALL | re.IGNORECASE)
if match:
return match.group(1).strip()
# 优先级2：兼容自定义<sql>标签格式（备用兼容方案）
match = re.search(r"<sql>\s*(.*?)\s*</sql>", response_text, re.DOTALL | re.IGNORECASE)
if match:
return match.group(1).strip()
# 优先级3：兜底匹配，直接抓取以SELECT开头、分号结尾的SQL片段
match = re.search(r"(SELECT\s+.*?;)", response_text, re.DOTALL | re.IGNORECASE)
if match:
return match.group(1).strip()
# 三层规则全部匹配不到，说明无有效SQL，返回空
return ""
# 全局单例实例，外部文件导入后直接调用 prompt_helper.方法名，无需重复实例化
prompt_helper = UnifiedPrompt()
4.4 核心业务逻辑 main.py
本文件是项目的“大脑”，封装了：
- 配置校验
- 大模型客户端初始化
- SQL 清洗函数（修复中文标点、关键字大小写等）
nl2sql_query(user_input)：自然语言 → SQL → 执行 → AI 总结sql_tune_analyze(raw_sql)：获取 EXPLAIN → AI 调优建议- 命令行交互菜单
main_cli()
由于代码较长，此处略去完整代码，但须确保 main.py 中正确导入：
from mysql_client import Mysql80Client
from prompts import UnifiedPrompt, prompt_helper
from langchain_openai import ChatOpenAI
from tabulate import tabulate
4.5 Web 可视化入口 web_main.py
基于 Streamlit 构建，复用 main.py 的业务函数，提供友好的 Web 界面。
关键部分：
import streamlit as st
from main import check_config, nl2sql_query, sql_tune_analyze
st.set_page_config(page_title="InnoAI SQL 助手", layout="wide")
# ... 自定义 CSS ...
if st.button("生成并执行"):
with st.spinner("AI 正在生成 SQL..."):
result = nl2sql_query(user_input)
if result["success"]:
st.code(result["sql"], language="sql")
st.dataframe(result["rows"])
st.info(result["summary"])
else:
st.error(result["error"])
五、功能测试（命令行）
5.1 启动程序
python3 /opt/mysql_ai_tools/main.py
菜单如下：
========================================
InnoAI SQL 助手
1. 自然语言生成SQL，自动查询并AI总结数据
2. 输入SQL语句，AI分析执行计划并给出调优方案
0. 退出程序
请输入功能序号：
5.2 测试用例 1：自然语言查询
输入功能序号 1，然后输入需求：
1001、1002、1003每个用户的订单总消费金额与订单笔数，按总消费从高到低排序
程序会自动生成 SQL：
SELECT user_id AS 用户ID, SUM(pay_amount) AS 总消费金额, COUNT(id) AS 订单笔数
FROM order_info
WHERE user_id IN (1001, 1002, 1003)
GROUP BY user_id
ORDER BY 总消费金额 DESC;
并输出表格和 AI 业务总结。
5.3 测试用例 2：SQL 调优
输入功能序号 2，粘贴上面生成的 SQL（可故意加上中文别名空格测试清洗功能）。程序会输出 EXPLAIN 表格和调优建议，例如建议创建联合索引 (user_id, pay_amount)。
六、Web 可视化部署与访问
6.1 启动 Streamlit 服务（后台常驻）
创建 systemd 服务文件 /etc/systemd/system/mysql-ai-web.service：
[Unit]
Description=InnoAI SQL Web Tool
After=network.target mysqld.service
[Service]
Type=simple
User=root
WorkingDirectory=/opt/mysql_ai_tools
ExecStart=/usr/local/python3.11/bin/python3 -m streamlit run web_main.py --server.address 0.0.0.0 --server.port 8501 --server.headless true
Restart=always
RestartSec=3
[Install]
WantedBy=multi-user.target
启动服务：
systemctl daemon-reload
systemctl enable --now mysql-ai-web
systemctl status mysql-ai-web # 查看状态
6.2 浏览器访问
查看虚拟机 IP：
hostname -I # 例如 192.168.24.138
在宿主机浏览器访问 http://192.168.24.138:8501，即可看到 Web 界面。
七、项目总结与心得体会
通过本项目，我深入实践了：
- MySQL 8.0 的部署、用户权限管理、表结构设计与数据导入。
- Python 操作 MySQL（pymysql）以及异常处理。
- 大模型 API 的调用，Prompt 工程（约束输出格式）。
- SQL 安全防护（仅允许 SELECT）和 SQL 语法清洗。
- 使用 Streamlit 快速搭建可视化工具，并部署为系统服务。
最大的收获是理解了 执行计划（EXPLAIN） 的实际意义——全表扫描、文件排序、索引失效等问题，以及如何通过添加联合索引或改写 SQL 来优化性能。同时，大模型与数据库的结合也让我看到了 AI 辅助运维的潜力。
八、源码与参考
- 项目完整代码见GitHub 仓库，后续补充链接。
- 腾讯云 TokenHub 文档：https://cloud.tencent.com/
- MySQL 官方文档：https://dev.mysql.com/doc/
openEuler 是由开放原子开源基金会孵化的全场景开源操作系统项目，面向数字基础设施四大核心场景（服务器、云计算、边缘计算、嵌入式），全面支持 ARM、x86、RISC-V、loongArch、PowerPC、SW-64 等多样性计算架构
更多推荐
3
0- 0
已为社区贡献1条内容
所有评论(0)