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 改写方案。

二、软硬件环境

三、环境搭建

3.1 虚拟机与系统初始化

1. 安装 openEuler 虚拟机（过程略，可参考网络教程）
2. 关闭防火墙与 SELinux（便于后续测试）

# 关闭 SELinux（永久生效，需重启）sed-i'7s/enforcing/disabled/'/etc/selinux/config# 关闭防火墙systemctl disable--nowfirewalld systemctl status firewalld# 检查状态

1. 修改主机名与时间同步

hostnamectl set-hostname serverbash# 编辑 /etc/chrony.conf，添加阿里云时间源vim/etc/chrony.conf# 添加：server ntp.aliyun.com iburstsystemctl 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

1. 安装基础编译工具

dnfinstall-ygcc gcc-c++makecmake zlib-devel bzip2-devel openssl-devel ncurses-devel sqlite-devel readline-devel libffi-devel tk-develwgettarvimtree net-tools openssh-server

3.2 编译安装 Python 3.11.9

由于 openEuler 自带的 Python 版本较低，我们需要源码编译安装 Python 3.11.9。

1. 下载源码包（在/usr/local/src目录下）
   访问 https://www.python.org/downloads/release/python-3119/ 下载Python-3.11.9.tgz，用 XFTP 上传到/usr/local/src。

2. 解压并编译

cd/usr/local/srctar-zxvfPython-3.11.9.tgzcdPython-3.11.9 ./configure--prefix=/usr/local/python3.11 --enable-sharedmake-j$(nproc)&&makeinstall

1. 配置动态链接库与软链接

echo"/usr/local/python3.11/lib">/etc/ld.so.conf.d/python311.conf ldconfigln-s/usr/local/python3.11/bin/python3.11 /usr/local/bin/python3ln-s/usr/local/python3.11/bin/pip3.11 /usr/local/bin/pip3bash# 或重启终端(reboot)python3-Vpip3-V

1. 安装项目 Python 依赖

配置阿里云 pip 镜像源，加速下载：

mkdir~/.pipvim~/.pip/pip.conf# 写入以下内容[global]index-url=http://mirrors.aliyun.com/pypi/simple/[install]trusted-host=mirrors.aliyun.com

安装依赖包（需使用我们刚安装的 Python 3.11 的 pip）：

pip3install--upgradepip pip3installpymysql python-dotenv tabulate langchain langchain-openai streamlit sqlparse

3.3 部署 MySQL 8.0.45

1. 下载 MySQL 二进制包
   访问 https://downloads.mysql.com/archives/community/ ，选择Linux - Generic，版本8.0.45，下载mysql-8.0.45-linux-glibc2.12-x86_64.tar.xz，上传至/root。

2. 解压并安装

cd/roottar-xvfmysql-8.0.45-linux-glibc2.12-x86_64.tar.xz-C/usr/local/cd/usr/localmvmysql-8.0.45-linux-glibc2.12-x86_64 mysqlgroupaddmysqluseradd-r-gmysql-s/bin/false mysqlcdmysqlmkdirdatachown-Rmysql: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-uroot-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 systemctlenable--nowmysqld systemctl status mysqld# 查看状态

添加 MySQL 命令到 PATH

echo'export PATH=$PATH:/usr/local/mysql/bin'>>~/.bash_profilesource~/.bash_profile mysql-V

3.4 创建测试数据库与订单表

mysql-uroot-p123456CREATEDATABASEtestdb;USEtestdb;CREATETABLEorder_info(idBIGINTAUTO_INCREMENTPRIMARYKEYCOMMENT'订单ID',user_idINTCOMMENT'用户ID',order_nameVARCHAR(200)COMMENT'商品名称',pay_amountDECIMAL(10,2)COMMENT'支付金额',create_timeDATETIMECOMMENT'下单时间')ENGINE=InnoDBCOMMENT='电商订单业务表';

任意插入 20 条示例数据。
验证数据：

SELECT*FROMorder_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

安全加固：

chmod600/opt/mysql_ai_tools/.env

4.2 数据库操作封装mysql_client.py

该文件负责连接 MySQL、执行查询、获取EXPLAIN计划，并拦截危险 SQL（仅允许 SELECT）。
核心代码：

# -*- coding: utf-8 -*-# 文件名：mysql_client.py# 功能：MySQL8.0数据库统一封装类# 作用：封装数据库连接、普通查询、EXPLAIN执行计划、SQL安全拦截，统一抛出友好异常，给上层业务调用importpymysqlimportosimportrefromdotenvimportload_dotenv# 加载项目根目录下.env文件的数据库配置load_dotenv()classMysql80Client:# 数据库操作封装类，所有数据库相关操作统一在此管理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()defconnect(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# 查询结果以字典返回，方便按字段取值)exceptpymysql.MySQLErrorase:# MySQL专属连接错误，提示账号、地址、密码排查方向raiseException(f"数据库连接失败，请检查地址/账号/密码：{e.args[1]}")exceptExceptionase:# 其余未知连接异常统一捕获raiseException(f"数据库连接异常：{str(e)}")@staticmethoddef_check_sql_safety(sql:str)->None:""" 静态私有安全校验方法 核心防护：拦截增删改、建表删表等危险操作，仅允许SELECT查询，防止AI生成危险SQL篡改数据 """# 去除首尾空格并转为大写，统一匹配规则sql_trim=sql.strip().upper()# 危险操作关键字黑名单danger_keywords=["INSERT","UPDATE","DELETE","DROP","ALTER","CREATE","TRUNCATE","REPLACE"]forkwindanger_keywords:# 单词边界匹配，避免字段名包含关键字时误拦截ifre.search(r'\b'+re.escape(kw)+r'\b',sql_trim):raiseException(f"安全拦截：禁止执行{kw}类型语句，仅支持 SELECT 查询")defexecute_query(self,sql:str):""" 执行普通SELECT查询 :param sql: 待执行查询语句 :return: (字段名列表, 全部数据行字典列表) """# 执行SQL前先做安全校验，拦截危险语句self._check_sql_safety(sql)try:# with自动管理游标，用完自动释放资源withself.conn.cursor()ascursor:cursor.execute(sql)# 提取查询结果表头字段columns=[desc[0]fordescincursor.description]# 读取全部查询数据rows=cursor.fetchall()returncolumns,rowsexceptpymysql.MySQLErrorase:# 捕获SQL语法、表不存在等数据库执行错误raiseException(f"SQL执行失败（错误码{e.args[0]}）：{e.args[1]}")exceptExceptionase:# 通用查询异常兜底raiseException(f"查询异常：{str(e)}")defget_explain_plan(self,sql:str):""" 获取SQL执行计划EXPLAIN，用于性能调优分析 :param sql: 待分析SELECT语句 :return: (执行计划表头, 执行计划详情数据) """# 同样先校验SQL安全性self._check_sql_safety(sql)# 拼接EXPLAIN关键字，生成分析语句explain_sql=f"EXPLAIN{sql}"try:withself.conn.cursor()ascursor:cursor.execute(explain_sql)columns=[desc[0]fordescincursor.description]rows=cursor.fetchall()returncolumns,rowsexceptpymysql.MySQLErrorase:raiseException(f"获取执行计划失败：{e.args[1]}")exceptExceptionase:raiseException(f"执行计划异常：{str(e)}")defclose(self):"""安全关闭数据库连接，释放资源，避免长时间占用连接池"""# 判断连接存在且未关闭才执行关闭操作ifself.connandnotself.conn._closed:self.conn.close()

4.3 提示词管理prompts.py

集中管理 NL2SQL 和 SQL 调优的 Prompt，并提供一个工具函数extract_sql从大模型回复中提取纯 SQL。

# -*- coding: utf-8 -*-# 文件名：prompts.py# 功能：统一管理项目全部大模型提示词模板，附带SQL提取工具静态方法# 作用：把AI提示词和业务代码解耦，统一约束模型输出格式，降低SQL解析报错概率importreclassUnifiedPrompt:""" 提示词统一管理类 优势：所有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. 内容简洁、分点罗列，不输出多余废话，便于用户快速阅读。 """@staticmethoddefextract_sql(response_text:str)->str:""" 静态工具方法：从大模型返回的完整文本里剥离出纯净SQL语句 三层匹配优先级，兼容不同大模型的输出格式，提升提取成功率 :param response_text: 大模型原始完整返回内容 :return: 清洗后的纯SQL字符串，提取失败返回空字符串 """# 空文本直接返回ifnotresponse_text:return""# 优先级1：匹配最标准markdown sql代码块（项目提示词强制要求的格式）match=re.search(r"```sql\s*(.*?)\s*```",response_text,re.DOTALL|re.IGNORECASE)ifmatch:returnmatch.group(1).strip()# 优先级2：兼容自定义<sql>标签格式（备用兼容方案）match=re.search(r"<sql>\s*(.*?)\s*</sql>",response_text,re.DOTALL|re.IGNORECASE)ifmatch:returnmatch.group(1).strip()# 优先级3：兜底匹配，直接抓取以SELECT开头、分号结尾的SQL片段match=re.search(r"(SELECT\s+.*?;)",response_text,re.DOTALL|re.IGNORECASE)ifmatch:returnmatch.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中正确导入：

frommysql_clientimportMysql80ClientfrompromptsimportUnifiedPrompt,prompt_helperfromlangchain_openaiimportChatOpenAIfromtabulateimporttabulate

4.5 Web 可视化入口web_main.py

基于 Streamlit 构建，复用main.py的业务函数，提供友好的 Web 界面。

关键部分：

importstreamlitasstfrommainimportcheck_config,nl2sql_query,sql_tune_analyze st.set_page_config(page_title="InnoAI SQL 助手",layout="wide")# ... 自定义 CSS ...ifst.button("生成并执行"):withst.spinner("AI 正在生成 SQL..."):result=nl2sql_query(user_input)ifresult["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：

SELECTuser_idAS用户ID,SUM(pay_amount)AS总消费金额,COUNT(id)AS订单笔数FROMorder_infoWHEREuser_idIN(1001,1002,1003)GROUPBYuser_idORDERBY总消费金额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 systemctlenable--nowmysql-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/