有个 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
EXPLAIN:
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
|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||
1
Chatterleys 5 天前
为了优化你的 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 或分布式数据库。 |
2
BiChengfei 5 天前
看起来 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 表中 |
3
MoYi123 5 天前
1. 按照你的表字段来看, 我很怀疑 DISTINCT 是不是有用. 如果 distinct 能去掉很多重复的话, 你存的数据应该不是很合理.
2. 如果没有 limit, 可以把 order by 去掉, 对查询优化没什么用. 3. 这样就只剩下一个 join 了, 调整一下索引, 保证是 hash join 或者 merge join 就行. |
4
Richared 5 天前
start_time ,contactId 联合索引,先把主表排序。连接子表。这些数据要全部返回?我感觉怎么搞都不太行。从业务上下手吧。
|
5
fragrans23 OP @MoYi123 order by 要用吧,不然就排不了序了
|
6
fragrans23 OP @BiChengfei 类似上面 gpt 的用法吗,效果也不咋好。。
|
7
fragrans23 OP @Richared 嗯,要全部返回。。
|
8
xiangyuecn 5 天前
用子查询生成一个主要的排序值,在用这个排序值对表里面的数据进行排序
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 吓死 |
9
godall 5 天前
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 先后次序调整而已,你这样写我头一次看到。 |
10
fragrans23 OP @godall 总的数据量,数据量在慢慢增加,现在又 14 万左右了。distinc 确实没啥用。后面的 group by 主要是让相同的 contactId 放在一起,也按时间倒序。
|
11
fragrans23 OP @xiangyuecn 大佬牛逼,现在是 14 万左右数据,返回 5 秒左右。还能优化吗。。sql 是这样的
SELECT XXX FROM iqc_xml xml LEFT JOIN iqc_wav wav ON xml.filename = wav.filename LEFT JOIN (SELECT contactId, MAX(start_time) AS sort_value FROM iqc_xml GROUP BY contactId) AS tmp ON tmp.contactId = xml.contactId ORDER BY tmp.sort_value DESC, xml.contactId, xml.start_time DESC; |
12
xiangyuecn 5 天前
单独执行一下子查询,看看慢不慢,慢就加个 contactId,start_time 的联合索引
|
13
fragrans23 OP @xiangyuecn 这个联合索引之前加了,加了后有 5 秒的速度。SELECT contactId,MAX(start_time)这个子查询速度几十毫秒,不加 order by 进行排序不到一秒。非要这么查的话好像没啥办法了。。。
|
14
Mandelo 5 天前
@Chatterleys 照搬 gpt 小心被 ban
|