Qwen: Qwen3.5-9B 在「慢查询诊断与索引优化建议」的评测结果

这是该 AI 模型在此测试用例上的详细评测结果。

基本信息

  • 模型名称：Qwen: Qwen3.5-9B
  • 用例名称：慢查询诊断与索引优化建议
  • 测试类型：文本生成
  • 评测维度：SQL 数据库能力

系统提示词（System Prompt）

这是给 AI 模型的背景设定和角色指令：

你是一名资深数据库工程师，擅长 MySQL 性能优化。 回答要求： 1. 清晰指出 SQL 语句存在的性能问题 2. 给出具体的索引创建语句 3. 用简洁的语言解释优化原因

用户提示词（User Prompt）

这是用户给 AI 模型的具体任务要求：

有如下一张用户表和一条查询语句： ```sql -- 表结构（约 500 万行数据） CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(64), email VARCHAR(128), status TINYINT, created_at DATETIME ); -- 慢查询 SELECT id, username, email FROM users WHERE status = 1 ORDER BY created_at DESC LIMIT 20; ``` 请回答： 1. 该查询为什么会慢？ 2. 应该为哪些字段创建索引？请写出具体的 CREATE INDEX 语句。

任务要求

AI 模型需要满足以下要求：

  1. 正确识别出缺少索引导致全表扫描的问题
  2. 指出 ORDER BY 在无索引情况下需要额外排序（filesort）
  3. 给出合理的索引建议，至少包含 status 和 created_at 字段
  4. 写出语法正确的 CREATE INDEX 语句
  5. 简要说明索引如何改善该查询性能

评测结果

综合得分

该模型在此用例的得分为：91.3 分（满分 100 分），已通过

基础难度评测结果

  • 得分：91.3 分
  • 通过状态：已通过

模型生成产物

以下是该模型针对此任务的实际生成内容：

### 1. 该查询为什么会慢？ 该查询慢的主要原因如下： - **缺少合适的索引**：`status` 和 `created_at` 字段上都没有索引，导致 MySQL 可能需要全表扫描（500 万行数据），耗时严重。 - **触发文件排序（Filesort）**：`ORDER BY created_at DESC` 在没有索引支持的情况下，MySQL 需要在内存或磁盘上对结果集进行排序，性能开销大。 - **无法使用索引过滤 + 排序优化**：没有复合索引，导致过滤和排序无法协同优化，查询效率低。 --- ### 2. 应该为哪些字段创建索引？ 建议创建以下索引： ```sql -- 复合索引：用于 WHERE + ORDER BY 优化 CREATE INDEX idx_status_created ON users (status, created_at); -- 可选：如果经常单独按创建时间查询，可补充以下索引 CREATE INDEX idx_created_at ON users (created_at); ``` --- ### 3. 优化原因说明 | 问题 | 优化效果 | |--------------------|--------------------------------------------------------| | 全表扫描 | `idx_status_created` 可快速定位 `status=1` 的记录 | | 文件排序（Filesort）| 索引已按 `created_at` 排序，避免额外的排序开销 | | 查询效率 | 使用索引覆盖过滤 + 排序，大幅减少 I/O 与 CPU 消耗 | **索引顺序说明**： - `status` 作为等值查询字段放在前面，提高过滤效率； - `created_at` 放在后面，用于满足 `ORDER BY` 要求； - 若顺序相反，MySQL 仍可能触发排序，无法充分利用索引。 --- ### 优化建议总结 - ✅ 添加复合索引 `(status, created_at)` 是最佳选择； - ✅ 避免频繁使用 `ORDER BY` 前加 `LIMIT` 的大规模深分页； - ✅ 定期执行 `EXPLAIN` 分析执行计划，确保索引生效； - ⚠️ 注意索引数量不宜过多，以免写性能下降。

AI 评审点评

以下是 AI 评审对该模型输出的点评：

【CLAUDE】候选答案整体质量较高，准确识别了慢查询的核心原因，给出了正确的复合索引方案，表达清晰有条理。与参考答案相比，主要差距在于：未建议通过 EXPLAIN 验证优化效果、未提及覆盖索引的进阶方案、对单列 `(created_at)` 索引的评价不够明确（应建议不使用）。总体而言，对于 basic 难度级别的问题，该回答达到了良好水准。 【GEMINI】模型表现优秀，不仅准确指出了性能瓶颈，还给出了符合 MySQL 索引最左前缀原则的优化方案。解释部分逻辑严密，且补充的优化建议具有实战参考价值，完全满足任务要求。 【KIMI】候选输出是一份高质量的 SQL 优化方案，准确识别了慢查询的根本原因，给出了正确的复合索引设计，并清晰解释了优化原理。整体表现优秀，接近参考答案水平，仅在索引建议的精简度上有轻微提升空间。

进阶难度评测结果

  • 得分：82.7 分
  • 通过状态：已通过

模型生成产物

以下是该模型针对此任务的实际生成内容：

你好，我是数据库工程师。针对你提供的订单表结构与慢查询问题，我将从 EXPLAIN 输出分析、核心瓶颈定位、索引方案设计以及查询重写四个维度进行深度剖析与优化。 --- ### 1. EXPLAIN 输出深度解读与性能瓶颈分析 根据提供的 EXPLAIN 结果，当前查询的性能问题主要源于**索引利用不充分**以及**排序开销过大**。以下是关键字段的逐项解读： * **`type: ref`** * **含义**：表示使用了非唯一索引的查找（Index Lookup）。MySQL 通过 `idx_user_id` 索引定位到 `user_id = 10086` 的记录。 * **评价**：这是合理的，比全表扫描（`ALL`）好。但 `ref` 意味着索引不是唯一的，MySQL 需要扫描该索引下所有的匹配项。 * **瓶颈**：`user_id` 索引区分度较高，但该行索引覆盖记录数高达 **850,000** 行。 * **`rows: 850000`** * **含义**：MySQL 优化器估算需要扫描的行数。 * **评价**：对于一个主键为 `BIGINT` 且总数据量 2000 万的表，单个用户 85 万条订单属于高频用户。 * **瓶颈**：查询只需要返回前 10 条记录，但数据库却读取了 85 万行数据。这导致大量的 I/O 消耗和 CPU 上下文切换。 * **`Extra: Using where; Using filesort`** * **`Using where`**：表示索引虽然定位到了数据，但索引本身不包含所有过滤条件，或者索引列的数据不足以直接过滤。这里是因为 `product_name` 无法使用索引。 * **`Using filesort`**：**这是最严重的性能瓶颈**。 * MySQL 无法利用现有索引来支持 `ORDER BY created_at DESC`。 * 数据库必须将扫描到的 850,000 行数据临时加载到内存中（或磁盘临时表），并进行全排序。 * 对于大规模数据，内存排序溢出到磁盘（Temp Table），其成本远高于简单的索引扫描。 **总结瓶颈**：查询在通过 `user_id` 索引获取 85 万行数据后，无法利用索引进行排序，被迫进行昂贵的 `filesort`，且无法利用 `product_name` 进行预过滤。 --- ### 2. `LIKE '%手机%'` 核心瓶颈分析与改进思路 #### 为什么 `LIKE '%手机%'` 是核心瓶颈？ 在 MySQL InnoDB 存储引擎中，`LIKE` 语句是否走索引取决于通配符的位置： 1. **`LIKE '手机%'` (前缀匹配)**：可以使用 `product_name` 上的普通 B-Tree 索引。因为 B-Tree 是有序的，可以直接定位到以“手机”开头的范围。 2. **`LIKE '%手机%'` (中间匹配)**：**不能使用普通 B-Tree 索引**。通配符 `%` 位于开头，破坏了 B-Tree 的有序性，MySQL 必须扫描 `product_name` 列的所有值来匹配“手机”，导致该字段无法被索引加速（Non-SARGable，非搜索参数化）。 在当前的查询中，由于 `product_name` 无法索引，优化器只能依赖 `idx_user_id` 找到 85 万行数据，然后对这 85 万行逐行检查 `product_name` 是否符合条件，最后再进行排序。 #### 改进思路 1. **查询重写（业务侧）**： * 如果业务允许，将 `LIKE '%手机%'` 改为 `LIKE '手机%'`（假设“手机”是商品名称的固定前缀或用户搜索习惯）。这能让 `product_name` 字段参与索引扫描，大幅减少扫描行数。 * 如果必须支持模糊搜索，建议引入 **全文索引（Full-Text Index）** 或使用 ES (Elasticsearch) 等搜索引擎处理，而不是依赖 MySQL B-Tree。 --- ### 3. 复合索引设计方案 在不修改查询语句的前提下（即必须保留 `LIKE '%手机%'`），优化的核心目标是：**消除 `Using filesort`** 并 **减少 `rows` 扫描量**（虽然受限于模糊查询，但可通过覆盖索引减少回表）。 #### 推荐索引语句 ```sql CREATE INDEX idx_user_created ON orders (user_id, created_at, product_name, amount, order_no); ``` *(注：`id` 为 PK，`amount` 和 `order_no` 为...

AI 评审点评

以下是 AI 评审对该模型输出的点评：

【CLAUDE】候选答案整体质量较高，结构清晰，术语使用准确，对 EXPLAIN 关键字段的解读和 LIKE '%...%' 索引失效原理的分析均达到专业水准，优化思路多元且具有实际可行性。主要不足在于复合索引方案存在逻辑矛盾——将 product_name（VARCHAR 128，前置通配符无法利用B-Tree）纳入覆盖索引并作为优化依据，与参考答案的明确反对立场相悖，且未充分讨论该做法带来的索引膨胀代价。总结部分的简化方案（user_id, created_at）更合理，但与主体方案不一致，降低了答案的整体可信度。若能统一索引推荐方案并补充全文索引的具体SQL示例，答案质量将显著提升。 【GEMINI】模型表现优秀，具备深厚的数据库调优功底。对性能瓶颈的诊断逻辑严密，能够准确识别出排序和回表带来的开销。在索引设计上，虽然为了覆盖索引引入了较多字段，但在实际生产中应权衡索引维护成本与存储空间。整体建议非常具有参考价值。 【KIMI】候选输出展现了扎实的数据库优化基础，能够准确识别性能瓶颈并给出合理的优化方向。但在索引设计的核心决策上出现偏差：过度追求覆盖索引而将长字段 product_name 纳入复合索引，这会带来显著的存储和性能代价，与最佳实践相悖。此外，对全文索引的技术细节和 ES 集成方案的阐述不够深入。建议强化「索引字段选择性」和「索引体积权衡」的设计意识，避免为覆盖索引而牺牲整体性能。

困难难度评测结果

  • 得分：87.8 分
  • 通过状态：已通过

模型生成产物

以下是该模型针对此任务的实际生成内容：

你好，我是数据库架构师。针对你提供的 5000 万行商品表及慢查询场景，这是一个典型的“高基数过滤 + 模糊匹配 + 排序 + 深分页”的复杂查询。在 MySQL 原生引擎下，该查询存在严重的性能隐患。 以下是基于执行计划、索引原理及数据分布的深度诊断与优化方案。 ### 1. 执行计划深度解读 当前 `EXPLAIN` 输出揭示了查询的核心路径与代价： | 字段 | 值 | 深度解读 | | :--- | :--- | :--- | | **type** | `ref` | MySQL 使用 `idx_shop` 索引进行查找。由于 `shop_id` 不是唯一键，它实际上是在该索引树上扫描所有 `shop_id=888` 的记录点，这是一个**定长索引查找（Point Lookup）**，但返回了 120 万行数据（即该店铺商品总数）。 | | **possible_keys** | `idx_shop` | 优化器认为 `idx_shop` 是最佳选择，因为 `shop_id` 是等值匹配，且位于现有索引首位。 | | **key** | `idx_shop` | 实际使用的索引。 | | **rows** | `1200000` | **关键瓶颈**。扫描了 120 万行索引记录。对于 5000 万表，这意味着 2.4% 的数据量被加载到内存中。 | | **Extra** | `Using index condition; Using where; Using filesort` | **核心代价来源**：1. `Using index condition`: 说明 `WHERE` 中的 `status` 和 `LIKE` 条件是在索引扫描过程中或扫描后通过回表或索引过滤进行的。2. `Using where`: 必须回表（Table Lookup）或读取非索引列（如 `title` 的全文匹配）来验证 `LIKE` 条件。3. `Using filesort`: **致命开销**。`ORDER BY sales_count` 无法利用索引顺序（因为 `sales_count` 不在 `idx_shop` 中），MySQL 必须在内存中建立临时表对这 120 万行数据进行全排序。 | **实际 I/O 与 CPU 开销估算：** * **I/O**: 读取 120 万行索引指针，随后回表读取 120 万行数据页（假设未覆盖），产生大量随机 I/O。 * **CPU**: `filesort` 需要将 120 万行数据排序。即使 `LIMIT 10`，MySQL 也必须扫描足够多的行以跳过前 200 行（深分页），排序开销与 `LIMIT` 无关，仅与数据量有关。 * **内存**: `Using filesort` 会申请临时表内存，若超过 `sort_buffer_size`，将触发磁盘临时文件排序（Disk Sort），性能暴跌。 ### 2. 多维度瓶颈定位 该查询存在四个维度的性能问题，按严重性排序： 1. **深分页（Deep Pagination）`:** `OFFSET 200` 要求 MySQL 必须扫描并丢弃前 200 个符合排序条件的记录才能找到第 210 条。在 `filesort` 场景下，这 200 条可能占据总扫描量的 10%~20%，且无法利用索引快速跳过。 2. **模糊匹配（Fuzzy Match）`:** `title LIKE '%无线耳机%'` 是 B-Tree 索引的杀手。`%` 前缀导致无法使用前缀索引，MySQL 必须扫描所有匹配 `shop_id` 的行，逐行检查 `title` 内容。 3. **排序未命中索引（Index Order）**: `ORDER BY sales_count DESC` 列不在索引树中，导致无法利用索引顺序消除排序（Index Merge Sort），必须执行文件排序。 4. **索引选择性与覆盖不足**: 现有 `idx_shop` 仅支持过滤 `shop_id`，无法支持 `status`、`sales_count` 过滤或排序。`status` 作为高选择性列（假设 50% 上架率），应作为索引的一部分以缩小扫描范围。 ### 3. 查询重写方案 针对上述瓶颈，提供两种可落地的方案： #### 方案一：Keyset 分页（游标分页）+ 索引优化 **适用条件**：用户是“无限滚动”模式，或分页深度可控，且必须保留在 MySQL 内优化。 **局限性**：不支持 `LIKE` 模糊查询（需结合方案二），不适用于“点击跳转第 50 页”的场景。 ```sql -- 伪代码：从上条记录处继续查询 SELECT id, title,...

AI 评审点评

以下是 AI 评审对该模型输出的点评：

【GEMINI】这是一份非常专业的数据库优化报告。模型展现了深厚的资深架构师功底，不仅在技术细节（如 EXPLAIN 解读、索引原理）上表现严谨，而且在工程实践（如 ES 架构升级、Keyset 分页、写入性能权衡）上给出了具有前瞻性的建议。方案逻辑清晰，术语使用精确，完全符合高并发大数据量场景下的性能调优要求。 【KIMI】整体而言，候选人表现出了较强的数据库性能优化能力，能够准确识别性能瓶颈并给出合理的优化方案。在索引设计和查询重写方面表现出色，但在覆盖索引的全面性和高并发场景下的工程可行性考虑还有提升空间。

相关链接

您可以通过以下链接查看更多相关内容：

加载中...
100%