SQL语句执行顺序

SQL执行


explain SELECT 
    ue.*,
    COUNT(ti.track_id) as track_count
FROM uav_event ue
LEFT JOIN trackinformation ti ON ue.cuavid = ti.cuavid
WHERE ue.start_time <= '2025-11-11 23:59:59'
    AND ue.end_time >= '2025-11-11 00:00:00'
    AND ue.data_type = '3'
    
GROUP BY ue.id
HAVING COUNT(ti.track_id) > 100
ORDER BY ue.start_time DESC;

这个SQL语句的执行顺序如下：

SQL执行顺序详解

1. FROM 和 JOIN→ 确定数据来源和表连接
2. WHERE→ 过滤符合条件的行
3. GROUP BY→ 对数据进行分组
4. HAVING→ 对分组后的数据进行过滤
5. SELECT→ 选择要显示的列
6. ORDER BY→ 对最终结果排序
7. LIMIT/OFFSET→ 分页限制（如果有的话）

具体步骤分析

第1步：FROM 和 JOIN

FROM uav_event ue
LEFT JOIN track_information ti ON ue.cuavid = ti.cuavid

从 uav_event 表中表中读取所有数据作为主表
通过 LEFT JOIN 关联 track_information 表
创建一个临时的笛卡尔积结果集

第2步：WHERE 条件过滤

WHERE ue.start_time <= '2025-01-05 23:59:59'
AND ue.end_time >= '2025-01-03 00:00:00'
AND ue.data_type = 'RADAR'

过滤掉不符合时间范围的记录
只保留 data_type = 'RADAR' 的数据
此时此时得到的是符合基本条件的原始数据集

第3步：GROUP BY 分组

GROUP BY ue.id

按照 uav_event 表的 ID 进行分组
同一架无人机的多条轨迹信息会合并到一个分组中

第4步：HAVING 分组后过滤

HAVING COUNT(ti.id) > 100

对上一步的分组结果进行二次过滤
只保留轨迹点数量大于100的分组
⚠️ 注意：这里有个潜在问题！如果某个无人机没有轨迹记录，COUNT(ti.id) 会是0会是0

第5步：SELECT 选择列

SELECT
ue.*,
COUNT(ti.id) as track_count

确定最终要输出的列
计算每组的轨迹点数量并命名为 track_count

第6步：ORDER BY 排序

ORDER BY ue.start_time DESC

对所有剩余的结果按开始时间降序排列

潜在问题和建议修正

当前的 HAVING COUNT(ti.id) > 100 可能会排除掉一些应该显示的结果。建议改为：

-- 方案1：使用内连接确保有轨迹数据的才参与计数
INNER JOIN track_information ti ON ue.cuavid = ti.cuavid
...
HAVING COUNT(ti.id) > 100

-- 方案2：或者修改判断逻辑
HAVING COUNT(ti.id) > 100 OR COUNT(ti.id) = 0

这样就能清晰地理解整个SQL语句是如何一步步执行的，以及每个阶段数据处理的目的。

SQL优化实战

要优化给定的SQL查询，需针对EXPLAIN结果中的性能瓶颈（全表扫描、未用索引、临时表）逐一解决。以下是详细的优化步骤和分析：

一、先看原始EXPLAIN结果的问题

从用户提供的EXPLAIN输出可知，查询的性能瓶颈主要在：

全表扫描（type=ALL）：

uav_event表（ue）：未用到索引（key=Null），扫描7340行，filtered=1.11%（仅1%的数据符合WHERE条件）。
trackinformation表（ti）：未用到索引（key=Null），扫描73499行，全表连接。

临时表（Extra=Using temporary）：GROUP BY时创建临时表，性能开销大。
连接效率低：LEFT JOIN未用到索引，依赖连接缓冲（Using join buffer）。

二、优化步骤

1. 修正WHERE条件的时间范围（关键！）

原始问题：
WHERE中的时间条件写反了！
ue.start_time <= '2025-11-1 23:59:59'（结束时间）与ue.end_time >= '2025-11-11 00:00:00'（开始时间）不可能同时满足（除非事件跨10天以上），导致filtered=1.11%（仅极少数据符合条件）。
修正后：
将时间范围调整为合理的区间（比如查询11月1日至11月11日的事件）：

WHERE ue.start_time <= '2025-11-11 23:59:59'-- 事件结束时间不晚于查询结束时间
AND ue.end_time >= '2025-11-1 00:00:00'-- 事件开始时间不早于查询开始时间
AND ue.data_type = '3';

影响：filtered（符合条件的数据占比）会大幅提升，减少后续扫描的行数。

2. 为`uav_event`表建立`WHERE`条件的联合索引

问题：ue表的WHERE条件用了data_type（等值）、start_time（范围）、end_time（范围），但未建立对应索引，导致全表扫描。
优化：
建立联合索引（等值字段放前面，范围字段放后面，遵循左前缀原则）：

CREATE INDEX idx_ue_dt_st_et ON uav_event (data_type, start_time, end_time);

作用：

快速过滤data_type='3'的记录（等值查询）。
进一步过滤start_time和end_time在合理范围的记录（范围查询）。
使ue表的type从ALL变为range（范围扫描），rows（扫描行数）从7340大幅减少（比如降至几百行）。

3. 为`trackinformation`表建立连接索引

问题：LEFT JOIN的连接条件是ue.cuavid = ti.cuavid，但ti表的cuavid字段未建索引，导致全表扫描（type=ALL）。
优化：
为ti表的cuavid字段建立普通索引：

CREATE INDEX idx_ti_cuavid ON trackinformation (cuavid);

作用：

连接时通过索引快速查找ti.cuavid等于ue.cuavid的记录，ti表的type从ALL变为ref（索引查找），rows（扫描行数）从73499降至几十/几百行。

4. 用`INNER JOIN`代替`LEFT JOIN`（可选，但推荐）

问题：LEFT JOIN会保留ue表中未匹配ti表的记录，但HAVING COUNT(ti.track_id) > 100要求ti表必须有≥101条记录，因此LEFT JOIN的NULL记录会被HAVING过滤，无需保留。
优化：
将LEFT JOIN改为INNER JOIN，减少连接的数据量：

INNER JOIN trackinformation ti ON ue.cuavid = ti.cuavid;

作用：

仅连接ti表中有对应cuavid的记录，避免处理NULL值，提升连接效率。

5. 优化`GROUP BY`（避免临时表）

问题：GROUP BY ue.id会创建临时表（Extra=Using temporary），因为ue.id是主键，但WHERE条件的索引未包含id，导致无法利用索引排序。
优化：
将ue表的联合索引扩展为覆盖索引（包含id），使WHERE过滤后的数据直接按id排序：

CREATE INDEX idx_ue_dt_st_et_id ON uav_event (data_type, start_time, end_time, id);

作用：

索引包含id，WHERE过滤后的数据已按id排序（因start_time是范围，需确认数据库是否支持），GROUP BY无需创建临时表，Extra中的Using temporary会消失。

6. 优化`ORDER BY`（避免文件排序）

问题：ORDER BY ue.start_time DESC未利用索引，可能导致Using filesort（文件排序）。
优化：
将ue表的联合索引中start_time设为降序，使ORDER BY直接利用索引：

CREATE INDEX idx_ue_dt_stDesc_et_id ON uav_event (data_type, start_time DESC, end_time, id);

作用：

ORDER BY start_time DESC可直接扫描索引的反向顺序，避免Using filesort，提升排序效率。

**7. 避免`SELECT *`（减少数据传输）**

问题：SELECT ue.*会读取uav_event表的所有字段，增加磁盘IO和数据传输量。
优化：
仅查询业务需要的字段（如id、cuavid、start_time、end_time、data_type）：

SELECT
ue.id,
ue.cuavid,
ue.start_time,
ue.end_time,
ue.data_type,
COUNT(ti.track_id) AS track_count
FROM ...;

作用：

减少从磁盘读取的数据量，提升查询速度。

二、优化后的SQL与EXPLAIN预期

优化后的SQL（修正时间范围+内连接+索引）：

SELECT
ue.id,
ue.cuavid,
ue.start_time,
ue.end_time,
ue.data_type,
COUNT(ti.track_id) AS track_count
FROM uav_event ue
INNER JOIN trackinformation ti ON ue.cuavid = ti.cuavid
WHERE
ue.start_time <= '2025-11-11 23:59:59'-- 修正后的结束时间
AND ue.end_time >= '2025-11-1 00:00:00'-- 修正后的开始时间
AND ue.data_type = '3'
GROUP BY ue.id
HAVING track_count > 100
ORDER BY ue.start_time DESC;

三、优化后的EXPLAIN预期变化

字段	原始值	优化后预期值	说明
`ue.type`	`ALL`（全表）	`range`（范围）	用到联合索引`idx_ue_dt_stDesc_et_id`
`ue.key`	`Null`（未用）	`idx_ue_dt_stDesc_et_id`	用到扩展后的覆盖索引
`ue.rows`	7340	几百	联合索引过滤后，仅扫描符合条件的行
`ti.type`	`ALL`（全表）	`ref`（索引查找）	用到`idx_ti_cuavid`索引
`ti.rows`	73499	几十/几百	索引查找，仅扫描匹配`cuavid`的行
`Extra`	`Using temporary; Using filesort`	无或`Using index`	覆盖索引避免临时表，降序索引避免文件排序

四、进一步优化建议

用子查询替代GROUP BY：
如果track_count的统计逻辑复杂，可改用子查询先统计trackinformation表的track_count，再关联uav_event表，避免GROUP BY：

SELECT
ue.id,
ue.cuavid,
ue.start_time,
ue.end_time,
ue.data_type,
tc.track_count
FROM uav_event ue
INNER JOIN (
SELECT cuavid, COUNT(track_id) AS track_count
FROM trackinformation
GROUP BY cuavid
HAVING track_count > 100
) tc ON ue.cuavid = tc.cuavid
WHERE
ue.start_time <= '2025-11-11 23:59:59'
AND ue.end_time >= '2025-11-1 00:00:00'
AND ue.data_type = '3'
ORDER BY ue.start_time DESC;

作用：将GROUP BY从主查询移至子查询，减少主查询的计算量。

升级MySQL版本：
MySQL 8.0及以上支持索引反向扫描（DESC索引）和哈希连接（HASH JOIN），可进一步提升连接和排序效率。

总结：优化核心逻辑

索引优化：为WHERE条件、连接条件建立联合索引/覆盖索引，避免全表扫描。
连接优化：用INNER JOIN代替LEFT JOIN（符合业务逻辑时），减少数据量。
排序/分组优化：利用索引避免临时表和文件排序。
字段优化：避免SELECT *，仅查询必要字段。

通过以上优化，查询的响应时间可从秒级降至毫秒级（取决于数据量），资源消耗（CPU、内存）也会大幅减少。

执行sql

要将给定的SQL查询转换为MyBatis实现，需要完成参数封装、Mapper接口定义、XML映射文件编写、结果映射和分页处理等步骤。以下是完整的实现方案：

1. 定义参数对象（封装查询条件）

创建UavEventQueryParam类，封装所有查询参数（包括分页参数），避免方法参数过多：

import lombok.Data;
import java.time.LocalDateTime;

@Data
public class UavEventQueryParam {
// 查询时间范围（事件开始时间≤结束时间，事件结束时间≥开始时间）
private LocalDateTime startTime; // 例如：2025-11-11 00:00:00
private LocalDateTime endTime;// 例如：2025-11-11 23:59:59

// 数据类型（例如：'3'）
private String dataType;

// 轨迹数量阈值（HAVING COUNT(ti.track_id) > minTrackCount）
private Integer minTrackCount;// 例如：100

// 分页参数
private Integer pageNum = 1;// 默认第一页
private Integer pageSize = 10;// 默认每页10条
}

2. 定义实体类（映射查询结果）

创建UavEvent实体类，映射uav_event表的字段和track_count（轨迹数量）：

import lombok.Data;
import java.time.LocalDateTime;

@Data
public class UavEvent {
// uav_event表字段
private Long id;
private String cuavid;// 无人机ID
private Long stationId;// 站点ID
private LocalDateTime startTime; // 事件开始时间
private LocalDateTime endTime;// 事件结束时间
private String dataType;// 数据类型（例如：'3'）

// 关联统计字段（轨迹数量）
private Integer trackCount;// 轨迹点数量（COUNT(ti.track_id)）
}

3. 定义Mapper接口（数据库操作方法）

创建UavEventMapper接口，定义查询方法，接收UavEventQueryParam参数，返回List<UavEvent>（分页由PageHelper处理）：

import com.example.param.UavEventQueryParam;
import com.example.entity.UavEvent;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;

@Mapper
public interface UavEventMapper {
/**
* 查询符合条件的无人机事件（带轨迹数量统计）
* @param param 查询参数（包含时间范围、数据类型、轨迹阈值、分页）
* @return 无人机事件列表（带轨迹数量）
*/
List<UavEvent> selectUavEventsWithTrackCount(UavEventQueryParam param);
}

4. 编写XML映射文件（SQL实现）

创建uavEventMapper.xml（位于resources/mapper目录下），编写select语句，处理JOIN、WHERE、GROUP BY、HAVING、ORDER BY等逻辑：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mapper.UavEventMapper">

<!-- 结果映射：将查询结果映射到UavEvent实体类 -->
<resultMap id="UavEventResultMap" type="com.example.entity.UavEvent">
<id property="id" column="id"/> <!-- 主键映射 -->
<result property="cuavid" column="cuavid"/>
<result property="stationId" column="station_id"/> <!-- 数据库字段：station_id → 实体属性：stationId（驼峰转换） -->
<result property="startTime" column="start_time"/>
<result property="endTime" column="end_time"/>
<result property="dataType" column="data_type"/>
<result property="trackCount" column="track_count"/> <!-- 统计字段：track_count → 实体属性：trackCount -->
</resultMap>

<!-- 查询方法：对应Mapper接口的selectUavEventsWithTrackCount方法 -->
<select id="selectUavEventsWithTrackCount" parameterType="com.example.param.UavEventQueryParam" resultMap="UavEventResultMap">
SELECT
ue.id,
ue.cuavid,
ue.station_id,
ue.start_time,
ue.end_time,
ue.data_type,
COUNT(ti.track_id) AS track_count-- 统计轨迹点数量，别名track_count
FROM uav_event ue
LEFT JOIN trackinformation ti ON ue.cuavid = ti.cuavid-- 关联轨迹表（LEFT JOIN保留ue表所有记录）
WHERE
<!-- 事件时间范围：事件开始时间≤查询结束时间，事件结束时间≥查询开始时间（重叠事件） -->
ue.start_time <= #{endTime}
AND ue.end_time >= #{startTime}
<!-- 数据类型过滤（非空时才生效） -->
<if test="dataType != null and dataType != ''">
AND ue.data_type = #{dataType}
</if>
GROUP BY ue.id-- 按事件ID分组（id为主键，确保分组唯一）
HAVING COUNT(ti.track_id) > #{minTrackCount}-- 过滤轨迹点数量超过阈值的事件
ORDER BY ue.start_time DESC-- 按事件开始时间降序排序
</select>

</mapper>

5. Service层实现（分页处理）

使用PageHelper插件实现分页（需引入依赖），简化分页逻辑：

5.1 引入PageHelper依赖（Spring Boot项目）

在pom.xml中添加：

<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.4.7</version> <!-- 最新版本可查询Maven仓库 -->
</dependency>

5.2 Service层代码

import com.example.entity.UavEvent;
import com.example.mapper.UavEventMapper;
import com.example.param.UavEventQueryParam;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class UavEventService {

@Autowired
private UavEventMapper uavEventMapper;

/**
* 查询无人机事件（带轨迹数量统计和分页）
* @param param 查询参数
* @return 分页结果（包含总记录数、当前页数据等）
*/
public PageInfo<UavEvent> getUavEventsWithTrackCount(UavEventQueryParam param) {
// 1. 处理默认参数（避免null）
if (param.getPageNum() == null) param.setPageNum(1);
if (param.getPageSize() == null) param.setPageSize(10);
if (param.getMinTrackCount() == null) param.setMinTrackCount(100); // 默认阈值100

// 2. 开启分页（PageHelper会自动拦截后续查询，添加LIMIT语句）
PageHelper.startPage(param.getPageNum(), param.getPageSize());

// 3. 执行查询（返回的list已分页）
List<UavEvent> eventList = uavEventMapper.selectUavEventsWithTrackCount(param);

// 4. 封装分页结果（PageInfo包含总记录数、当前页、每页大小等信息）
return new PageInfo<>(eventList);
}
}

6. 控制层调用（示例）

创建UavEventController，接收前端请求，调用Service层方法：

import com.example.entity.UavEvent;
import com.example.param.UavEventQueryParam;
import com.example.service.UavEventService;
import com.github.pagehelper.PageInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.format.annotation.DateTimeFormat;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.time.LocalDateTime;

@RestController
@RequestMapping("/api/uav/events")
public class UavEventController {

@Autowired
private UavEventService uavEventService;

/**
* 查询无人机事件（带轨迹数量统计）
* @param startTime 事件开始时间（格式：yyyy-MM-dd HH:mm:ss）
* @param endTime事件结束时间（格式：yyyy-MM-dd HH:mm:ss）
* @param dataType数据类型（例如：'3'）
* @param pageNum当前页（默认1）
* @param pageSize每页大小（默认10）
* @return 分页结果
*/
@GetMapping
public PageInfo<UavEvent> getUavEvents(
@RequestParam(required = false) @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") LocalDateTime startTime,
@RequestParam(required = false) @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") LocalDateTime endTime,
@RequestParam(required = false) String dataType,
@RequestParam(required = false, defaultValue = "1") Integer pageNum,
@RequestParam(required = false, defaultValue = "10") Integer pageSize) {

// 封装参数
UavEventQueryParam param = new UavEventQueryParam();
param.setStartTime(startTime);
param.setEndTime(endTime);
param.setDataType(dataType);
param.setPageNum(pageNum);
param.setPageSize(pageSize);
param.setMinTrackCount(100); // 轨迹数量阈值（可前端传递）

// 调用Service层方法
return uavEventService.getUavEventsWithTrackCount(param);
}
}

关键说明

时间范围逻辑：
ue.start_time <= #{endTime} AND ue.end_time >= #{startTime}用于查询与指定时间范围重叠的事件（例如：事件跨天但包含查询当天的部分时间）。
GROUP BY逻辑：
按ue.id（主键）分组，确保每个事件只出现一次，且SELECT语句中的非聚合字段（如ue.cuavid、ue.station_id）都依赖于主键（符合SQL标准）。
HAVING逻辑：
过滤轨迹点数量超过minTrackCount的事件（COUNT(ti.track_id)统计轨迹表中对应的记录数）。
PageHelper分页：
PageHelper.startPage(pageNum, pageSize)会自动拦截后续的select语句，添加LIMIT和OFFSET，返回分页结果。PageInfo包含总记录数、当前页、每页大小等信息，方便前端展示。

测试示例

假设前端请求：

GET /api/uav/events?startTime=2025-11-11 00:00:00&endTime=2025-11-11 23:59:59&dataType=3&pageNum=1&pageSize=10

Service层会执行以下SQL（PageHelper自动添加LIMIT 10 OFFSET 0）：

SELECT
ue.id,
ue.cuavid,
ue.station_id,
ue.start_time,
ue.end_time,
ue.data_type,
COUNT(ti.track_id) AS track_count
FROM uav_event ue
LEFT JOIN trackinformation ti ON ue.cuavid = ti.cuavid
WHERE
ue.start_time <= '2025-11-11 23:59:59'
AND ue.end_time >= '2025-11-11 00:00:00'
AND ue.data_type = '3'
GROUP BY ue.id
HAVING COUNT(ti.track_id) > 100
ORDER BY ue.start_time DESC
LIMIT 10 OFFSET 0;

扩展说明

动态条件：XML中的<if test="dataType != null and dataType != ''">用于处理非空条件（数据类型为空时，不添加该过滤条件）。
结果映射：resultMap中的column对应数据库字段，property对应实体类属性（MyBatis默认支持驼峰转换，无需手动映射所有字段，可通过mybatis.configuration.map-underscore-to-camel-case=true开启）。
性能优化：
为uav_event表的start_time、end_time、data_type字段建立联合索引（idx_ue_start_end_data），加速WHERE条件过滤。
为trackinformation表的cuavid字段建立普通索引（idx_ti_cuavid），加速LEFT JOIN查询。

通过以上实现，成功将原始SQL转换为可维护、可扩展的MyBatis代码，覆盖了查询条件、关联查询、分组统计、分页等需求。

posted @ 2025-11-19 17:38 我不想学编丿程阅读(12) 评论(0) 收藏举报

刷新页面返回顶部

Loading

我不想学编丿程

SQL语句执行顺序

SQL执行

SQL执行顺序详解

具体步骤分析

第1步：FROM 和 JOIN

第2步：WHERE 条件过滤

第3步：GROUP BY 分组

第4步：HAVING 分组后过滤

第5步：SELECT 选择列

第6步：ORDER BY 排序

潜在问题和建议修正

SQL优化实战

一、先看原始EXPLAIN结果的问题

二、优化步骤

1. 修正WHERE条件的时间范围（关键！）

2. 为`uav_event`表建立`WHERE`条件的联合索引

3. 为`trackinformation`表建立连接索引

4. 用`INNER JOIN`代替`LEFT JOIN`（可选，但推荐）

5. 优化`GROUP BY`（避免临时表）

6. 优化`ORDER BY`（避免文件排序）

**7. 避免`SELECT *`（减少数据传输）**

二、优化后的SQL与EXPLAIN预期

三、优化后的EXPLAIN预期变化

四、进一步优化建议

总结：优化核心逻辑

执行sql

1. 定义参数对象（封装查询条件）

2. 定义实体类（映射查询结果）

3. 定义Mapper接口（数据库操作方法）

4. 编写XML映射文件（SQL实现）

5. Service层实现（分页处理）

5.1 引入PageHelper依赖（Spring Boot项目）

5.2 Service层代码

6. 控制层调用（示例）

关键说明

测试示例

扩展说明

公告

Loading

我不想学编丿程

SQL语句执行顺序

SQL执行

SQL执行顺序详解

具体步骤分析

第1步：FROM 和 JOIN

第2步：WHERE 条件过滤

第3步：GROUP BY 分组

第4步：HAVING 分组后过滤

第5步：SELECT 选择列

第6步：ORDER BY 排序

潜在问题和建议修正

SQL优化实战

一、先看原始EXPLAIN结果的问题

二、优化步骤

1. 修正WHERE条件的时间范围（关键！）

2. 为uav_event表建立WHERE条件的联合索引

3. 为trackinformation表建立连接索引

4. 用INNER JOIN代替LEFT JOIN（可选，但推荐）

5. 优化GROUP BY（避免临时表）

6. 优化ORDER BY（避免文件排序）

7. 避免SELECT *（减少数据传输）

二、优化后的SQL与EXPLAIN预期

三、优化后的EXPLAIN预期变化

四、进一步优化建议

总结：优化核心逻辑

执行sql

1. 定义参数对象（封装查询条件）

2. 定义实体类（映射查询结果）

3. 定义Mapper接口（数据库操作方法）

4. 编写XML映射文件（SQL实现）

5. Service层实现（分页处理）

5.1 引入PageHelper依赖（Spring Boot项目）

5.2 Service层代码

6. 控制层调用（示例）

关键说明

测试示例

扩展说明

公告

2. 为`uav_event`表建立`WHERE`条件的联合索引

3. 为`trackinformation`表建立连接索引

4. 用`INNER JOIN`代替`LEFT JOIN`（可选，但推荐）

5. 优化`GROUP BY`（避免临时表）

6. 优化`ORDER BY`（避免文件排序）

**7. 避免`SELECT *`（减少数据传输）**