请教各位大佬一个 SQL 查询的问题

有个 sql 要求按照开始时间倒序，并且 contactId 相同的情况要放在一起（相同的也按时间倒序）。这个 sql 在数据量大了之后运行 10 多秒（八万条左右），接口就超时了。尝试加索引，速度还是很慢。有啥办法能优化吗。改 sql 的话也暂时没想到其他方法能实现这功能。。
select DISTINCT xml.started, xml.start_time startTime, xml.ani, xml.core_dnis coreDnis, xml.core_extension coreExtension, xml.poor, xml.contactId, xml.core_agentid coreAgentid, xml.core_agentname coreAgentname, xml.filename, xml.end_time endTime, xml.core_callid coreCallid, wav.tran_failed tranFailed, wav.sentiment_flag sentimentFlag
from
iqc_xml xml left join iqc_wav wav on xml.filename = wav.filename
where ##字段条件筛选##
ORDER BY MAX(xml.start_time) OVER (PARTITION BY xml.contactId) DESC, xml.contactId, xml.start_time DESC

|1|SIMPLE|xml||ALL|||||134595|100.0|Using temporary; Using filesort|
|1|SIMPLE|wav||ref|idx_filename|idx_filename|1023|xml.filename|1|100.0||

Chatterleys

13 天前

为了优化你的 SQL 查询，我们可以考虑以下几个方法：

### 1. 索引优化

首先，可以尝试创建覆盖索引以支持查询所需的列。对于你的查询，建议在 `iqc_xml` 和 `iqc_wav` 表上分别创建以下索引：

```sql
CREATE INDEX idx_xml_contactId_start_time ON iqc_xml(contactId, start_time);
CREATE INDEX idx_wav_filename ON iqc_wav(filename);
```

### 2. 子查询优化

将复杂的查询分成多个子查询可能会提高性能。你可以先创建一个子查询，按 `contactId` 和 `start_time` 排序，然后在外部查询中使用这个子查询。

```sql
WITH sorted_xml AS (
SELECT
xml.started,
xml.start_time,
xml.ani,
xml.core_dnis,
xml.core_extension,
xml.poor,
xml.contactId,
xml.core_agentid,
xml.core_agentname,
xml.filename,
xml.end_time,
xml.core_callid,
ROW_NUMBER() OVER (PARTITION BY xml.contactId ORDER BY xml.start_time DESC) as rn
FROM
iqc_xml xml
)
SELECT DISTINCT
sorted_xml.started,
sorted_xml.start_time startTime,
sorted_xml.ani,
sorted_xml.core_dnis coreDnis,
sorted_xml.core_extension coreExtension,
sorted_xml.poor,
sorted_xml.contactId,
sorted_xml.core_agentid coreAgentid,
sorted_xml.core_agentname coreAgentname,
sorted_xml.filename,
sorted_xml.end_time endTime,
sorted_xml.core_callid coreCallid,
wav.tran_failed tranFailed,
wav.sentiment_flag sentimentFlag
FROM
sorted_xml
LEFT JOIN
iqc_wav wav
ON
sorted_xml.filename = wav.filename
WHERE
##字段条件筛选##
ORDER BY
sorted_xml.contactId,
sorted_xml.rn
```

### 3. 分区表

如果你的数据量很大，使用分区表可以显著提高查询性能。你可以按 `contactId` 或 `start_time` 进行分区。

### 4. 优化连接条件

确保连接条件中的字段有合适的索引。如果连接字段 `filename` 没有索引，可以考虑创建索引。

### 5. 查询缓存

如果数据变化不频繁，可以考虑使用查询缓存。MySQL 的查询缓存可以显著提高相同查询的响应速度。

### 6. 服务器配置优化

优化 MySQL 服务器配置，如增大缓冲池大小、调整缓存大小等，可以提高查询性能。

以上方法可以逐步尝试，根据实际情况调整。如果仍有性能问题，可以考虑将查询逻辑移到应用程序中处理，或者使用更强大的数据库系统，如 PostgreSQL 或分布式数据库。

BiChengfei

13 天前

看起来 MAX(xml.start_time) OVER (PARTITION BY xml.contactId) DESC 破坏了索引，explain 中显示，只有 left join iqc_wav wav on xml.filename = wav.filename 的时候使用了索引。
建议 MAX(xml.start_time) OVER (PARTITION BY xml.contactId) DESC 改成一个冗余字段，放在 iqc_xml 表中

MoYi123

13 天前

1. 按照你的表字段来看, 我很怀疑 DISTINCT 是不是有用. 如果 distinct 能去掉很多重复的话, 你存的数据应该不是很合理.
2. 如果没有 limit, 可以把 order by 去掉, 对查询优化没什么用.
3. 这样就只剩下一个 join 了, 调整一下索引, 保证是 hash join 或者 merge join 就行.

Richared

13 天前

start_time ，contactId 联合索引，先把主表排序。连接子表。这些数据要全部返回？我感觉怎么搞都不太行。从业务上下手吧。

fragrans23

13 天前

@MoYi123 order by 要用吧，不然就排不了序了

fragrans23

13 天前

@BiChengfei 类似上面 gpt 的用法吗，效果也不咋好。。

fragrans23

13 天前

@Richared 嗯，要全部返回。。

xiangyuecn

13 天前

用子查询生成一个主要的排序值，在用这个排序值对表里面的数据进行排序

select xml.***,tmp.sort from 你的查询 n 个表 left join (子查询) as tmp on tmp.contactId=xml.contactId ..... order by tmp.sort,tab.同一个 contactId 下的排序

子查询里面就用 group by 查询出每个符合条件的 contactId 排最前面一条，生成一个排序值，8 万数据不加任何索引最多 200ms 吓死

godall

13 天前

1.总的数据量有多大？是 8w 条，还是返回结果 8w 条？如果是后者，8w 条返回肯定需要不少时间了。
2.你的条件其实就是 2 个 xml.start_time,xml.contactId,排序把？
简单说就是
select xml.*,wav.name
from xml join wav on xml.filename=wav.filename
orderby xml.start_time, xml.contactId DESC

所以，
（ 1 ）你的 distinct 不知道有什么用？理论上不应该存在完全相同的 2 条记录，因为你 distinct 后面跟了所有字段了。
这个相当于对所有字段去重了，肯定耗费性能，不建议这么做。要做也建议通过子查询方式进行二次去重。
（ 2 ） ORDER BY MAX(xml.start_time) OVER (PARTITION BY xml.contactId) DESC, xml.contactId, xml.start_time DESC
这个什么意思看不懂。排序我理解，要么第一次序是 starttime ，要么 contactID ，就是 order by A,B 先后次序调整而已，你这样写我头一次看到。