V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
fragrans23
V2EX  ›  MySQL

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

  •  
  •   fragrans23 · 161 天前 · 1589 次点击
    这是一个创建于 161 天前的主题,其中的信息可能已经有所发展或是发生改变。

    有个 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||

    14 条回复    2024-06-13 19:58:33 +08:00
    Chatterleys
        1
    Chatterleys  
       161 天前
    为了优化你的 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
        2
    BiChengfei  
       161 天前
    看起来 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
        3
    MoYi123  
       161 天前
    1. 按照你的表字段来看, 我很怀疑 DISTINCT 是不是有用. 如果 distinct 能去掉很多重复的话, 你存的数据应该不是很合理.
    2. 如果没有 limit, 可以把 order by 去掉, 对查询优化没什么用.
    3. 这样就只剩下一个 join 了, 调整一下索引, 保证是 hash join 或者 merge join 就行.
    Richared
        4
    Richared  
       161 天前
    start_time ,contactId 联合索引,先把主表排序。连接子表。这些数据要全部返回?我感觉怎么搞都不太行。从业务上下手吧。
    fragrans23
        5
    fragrans23  
    OP
       161 天前
    @MoYi123 order by 要用吧,不然就排不了序了
    fragrans23
        6
    fragrans23  
    OP
       161 天前
    @BiChengfei 类似上面 gpt 的用法吗,效果也不咋好。。
    fragrans23
        7
    fragrans23  
    OP
       161 天前
    @Richared 嗯,要全部返回。。
    xiangyuecn
        8
    xiangyuecn  
       161 天前
    用子查询生成一个主要的排序值,在用这个排序值对表里面的数据进行排序

    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
        9
    godall  
       161 天前
    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 先后次序调整而已,你这样写我头一次看到。
    fragrans23
        10
    fragrans23  
    OP
       161 天前
    @godall 总的数据量,数据量在慢慢增加,现在又 14 万左右了。distinc 确实没啥用。后面的 group by 主要是让相同的 contactId 放在一起,也按时间倒序。
    fragrans23
        11
    fragrans23  
    OP
       161 天前
    @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;
    xiangyuecn
        12
    xiangyuecn  
       161 天前
    单独执行一下子查询,看看慢不慢,慢就加个 contactId,start_time 的联合索引
    fragrans23
        13
    fragrans23  
    OP
       161 天前
    @xiangyuecn 这个联合索引之前加了,加了后有 5 秒的速度。SELECT contactId,MAX(start_time)这个子查询速度几十毫秒,不加 order by 进行排序不到一秒。非要这么查的话好像没啥办法了。。。
    Mandelo
        14
    Mandelo  
       161 天前
    @Chatterleys 照搬 gpt 小心被 ban
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2814 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 15:00 · PVG 23:00 · LAX 07:00 · JFK 10:00
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.