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
abcfyk
V2EX  ›  MySQL

Mysql 如何提升 Group by + Having 型子查询的查询速度(千万级别数据)

  •  
  •   abcfyk · 2015-03-18 16:59:41 +08:00 · 10961 次点击
    这是一个创建于 3519 天前的主题,其中的信息可能已经有所发展或是发生改变。
    如题所示。
    有一出货记录表 outDetail。表中大约3000W条数据。
    字段如 outId, skuId, outDate, outQty等。

    现想查出最近三个月没有出货的SKU号。有如下SQL:
    select skuId from outDetail group by skuId having max(outDate) < date_sub(curdate(), interval 90 day);

    查询速度很慢。请问如何优化?
    25 条回复    2015-03-24 22:59:49 +08:00
    npc0der
        1
    npc0der  
       2015-03-18 17:11:32 +08:00   ❤️ 1
    额 找出 最近90天出库的 那批 skuId

    然后找出 整库的 skuId

    差集。。。。skuId 有索引的吧 应该会很快。。。。
    abcfyk
        2
    abcfyk  
    OP
       2015-03-18 17:16:20 +08:00
    @npc0der 目前是这么做的。但是目前SKU总数100W+, 最近90天有出货的大约30W。用PHP来筛选。但是我想尽量能在mysql层面做就在mysql里面做算了。用php脚本跑的话效率太差了。
    takatost
        3
    takatost  
       2015-03-18 17:20:50 +08:00   ❤️ 1
    这么大级别的数据量为何不分表
    Septembers
        4
    Septembers  
       2015-03-18 17:27:40 +08:00
    1. 建议做表分区
    2. 给时间做索引
    Septembers
        5
    Septembers  
       2015-03-18 17:27:58 +08:00   ❤️ 1
    1. 建议做表分区
    2. 给时间(outDate)做索引
    Septembers
        6
    Septembers  
       2015-03-18 17:28:50 +08:00
    @takatost 如果是PgSQL单表 15亿都压力不大
    jk2r
        7
    jk2r  
       2015-03-18 17:30:30 +08:00   ❤️ 1
    你先贴出create的sql/explain的结果,大家才好帮你看
    Mac
        8
    Mac  
       2015-03-18 18:33:59 +08:00
    LZ先说说慢到什么程度?
    abcfyk
        9
    abcfyk  
    OP
       2015-03-18 18:42:52 +08:00
    @takatost 因为有很多统计数据的业务需求需要所有出货记录一起统计。类似这种需求。。所以没有统计。。
    abcfyk
        10
    abcfyk  
    OP
       2015-03-18 18:43:47 +08:00
    @Septembers 表分区。。暂时比较难,outDate 已加索引。。
    abcfyk
        11
    abcfyk  
    OP
       2015-03-18 18:55:18 +08:00
    @jk2r

    类似这种:
    CREATE TABLE `outDetail`{
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `outId` int(11) NOT NULL,
    `skuId` varchar(30) NOT NULL,
    `outDate` datetime NOT NULL,
    `outQty` int(11) NOT NULL
    PRIMARY KEY (`id`),
    KEY `indexOutId` (`outId`),
    KEY `indexSkuId` (`skuId`),
    KEY `indexOutDate` (`outDate`),
    }ENGINE=InnoDB DEFAULT CHARSET=utf8


    explain 结果大概如下
    +----+-------------+-----------------+-------+---------------+-------------+---------+------+----------+-------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-----------------+-------+---------------+-------------+---------+------+----------+-------+
    | 1 | SIMPLE | outDetail | index | NULL | IndexSkuId | 92 | NULL | 23980757 | |
    +----+-------------+-----------------+-------+---------------+-------------+---------+------+----------+-------+
    abcfyk
        12
    abcfyk  
    OP
       2015-03-18 18:57:31 +08:00
    @Mac PHP把全部100W 的 SKU查出来存入变量,大概需要一小时。查找最近三个月出货的SKU存到变量又要一小时。光取数据还没求差集的时间就2小时了。
    O14
        13
    O14  
       2015-03-18 19:13:30 +08:00 via Android   ❤️ 1
    不知我理解的对不?
    SELECT DISTINCT skuId FROM outDetail WHERE outDate < date_sub(curdate(), interval 90 day) AND skuId NOT IN (SELECT DISTINCT skuId FROM outDetail WHERE outDate > date_sub(curdate(), interval 90 day));
    takatost
        14
    takatost  
       2015-03-18 19:53:40 +08:00 via iPhone
    @Septembers 可惜是mysql
    sohoer
        15
    sohoer  
       2015-03-18 20:59:09 +08:00   ❤️ 1
    select skuId from (select skuId from outDetail where outDate > date_sub(curdate(), interval 90 day)) as t group by skuId;
    frankzeng
        16
    frankzeng  
       2015-03-18 21:39:46 +08:00   ❤️ 1
    如果不是严格实时要求,你可以写个脚本把跑出来的数据存到另一张表里。
    cevincheung
        17
    cevincheung  
       2015-03-18 21:49:53 +08:00   ❤️ 1
    只有我来说上检索引擎吗?

    https://www.elastic.co
    yangqi
        18
    yangqi  
       2015-03-18 22:51:26 +08:00   ❤️ 1
    难道不是简单的一句
    SELECT DISTINCT skuId FROM outDetail WHERE outDate < date_sub(curdate(), interval 90 day);

    WispZhan
        19
    WispZhan  
       2015-03-18 23:08:30 +08:00   ❤️ 1
    Group by + Having 本身就没有效率可言吧……
    npc0der
        20
    npc0der  
       2015-03-19 09:02:21 +08:00   ❤️ 1
    取出你说的 30w 的这些skuId 不会很慢吧 3000w 的表不算很大啊 我这张表 38,643,224 记录 取出skuId 你到临时表里面去

    或者你这样 skuID 你 100w 的那张表 加一个字段 标示为 90天内出库了 更新下 这样 筛选出来也快 做分页查询 其他业务也方便。


    100w 表加个字段不会很慢的 表大了就不容易加字段了。
    abcfyk
        21
    abcfyk  
    OP
       2015-03-19 12:23:07 +08:00
    @sohoer 可能是思路问题。放mysql做也许不是个好办法。
    abcfyk
        22
    abcfyk  
    OP
       2015-03-19 12:36:56 +08:00
    @frankzeng 不是实时数据,所以目前做了一些小修改。

    @npc0der 后来又做了一些小修改。

    首先修改了取SKU的接口,只取SKUID,不取其他信息。能节省大量时间,再缓存到一些文件中。暂时没有放在redis,速度提升已经非常大了。只是还剩下一个内存占用的问题。

    就是PHP 无法手动释放内存,你 unset 了某个变量并不会马上释放这个变量占用的内存空间,、还是要等 GC 来自动回收。默认的128M内存真的是捉襟见肘。。。
    jhdxr
        23
    jhdxr  
       2015-03-19 22:45:10 +08:00
    @Septembers 我现在有个单表2亿的,在合适的索引的配合下,目前我的查询均可以在0.3s内完成。然后上个月把数据同样地拷了一份到postgresql里去,配合同样的索引,效率远不如mysql。用explain看了下也已经走了索引了。不知道能否指教下有哪些需要注意的点?
    li24361
        24
    li24361  
       2015-03-24 22:49:07 +08:00
    @frankzeng 这样的话,数据实时更新了,统计表的信息就不准确了,必须每次存量跑
    li24361
        25
    li24361  
       2015-03-24 22:59:49 +08:00
    @yangqi 我觉得也是额
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   5414 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 33ms · UTC 06:58 · PVG 14:58 · LAX 22:58 · JFK 01:58
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.