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

请教 MYSQL 多表联查数据优化方式

  •  
  •   markgor · 2021-10-21 11:15:08 +08:00 · 2485 次点击
    这是一个创建于 890 天前的主题,其中的信息可能已经有所发展或是发生改变。

    Mariadb 10.1

    tbl_product 产品表,有 3k 多个产品; tbl_spu SPU 表,其中 tbl.spu.pid = tbl_product.id;平均一个产品对应 3 个 spu ; tbl_sku SKU 表,其中 tbl_sku.spuID = tbl_spu.id;平均一个 spu 对应 5 个 sku ; tbl_price 价格表,tbl_price.skuID = tbl_sku.id ;价格维度是 日期+skuID ;

    当我需要查询 10 条产品 7 日内最低的价格时候,现在伪 SQL 写法:

    SELECT 
    	p.*,min(price) as price 
    from 
    	tbl_product p LEFT JOIN 
        tbl_spu spu ON spu.pid = p.id LEFT JOIN
        tbl_sku sku ON sku.spuID = spu.id LEFT JOIN
        tbl_price price ON price.skuID = sku.id
    WHERE 
        price.bookDate BETWEEN '2021-10-10' AND '2021-10-17'
    GROUP BY p.id
    LIMIT 10
    

    该做的索引都已经做了,但查询执行需要 14 秒左右, 请问类似这种的有没有什么好的方法 /思路 去优化?

    想过上 ES,但 ES 太迟内存了, 想上训搜,但是怕结果也是一样,而且多了一个服务要维护,加上之前一个小的项目用过训搜,在索引清空重建的场景下偶尔出现异常,需要清空数据重新导入并进行索引。

    第 1 条附言  ·  2021-10-21 17:25:29 +08:00
    因为实际情况还有以下几个环节,一开始漏了写上来:

    ```
    tbl_product:
    id:唯一主键
    isAcitve:int(1)上架 /下架
    ...

    tbl_spu:
    id:主键
    pid:int 对应 tbl_product.id
    isActive:int(1)上架 /下架
    ...

    tbl_sku:
    id:主键
    spuID:int 对应 tbl_spu.id
    isActive:int(1)上架 /下架
    ...

    tbl_price:
    skuID:对应 tbl_sku.id
    bookDate:日期
    isActive:int(1)可售 /停售
    price:int 价格
    /*其中 skuID+bookDate 为联合主键*/

    SQL:

    SELECT
    p.*,min(price) as price
    from
    tbl_product p LEFT JOIN
    tbl_spu spu ON spu.pid = p.id LEFT JOIN
    tbl_sku sku ON sku.spuID = spu.id LEFT JOIN
    tbl_price price ON price.skuID = sku.id
    WHERE
    p.isActive = 1
    AND spu.isActive = 1
    AND sku.isActive = 1
    AND price.isActive = 1
    AND price.bookDate BETWEEN '2021-10-10' AND '2021-10-17'
    AND price.price > 0
    GROUP BY p.id
    LIMIT 10


    逻辑是查询 10 个可售产品;
    可售定义是 product/sku/spu/price 的 isActive 均为 1 且 tbl_price 的 price 大于零 且 价格范围在 '2021-10-10 ~ 2021-10-17'
    所以价格表无法冗余;


    现在实际遇到的情况是:
    tbl_product 已经有 26k+数据
    tbl_spu 有 100w+数据
    tbl_sku 有 150w+数据
    tbl_price 有 4000w+数据

    之前临时的解决方案是每日业务闲时(凌晨 3 点)通过 redis 缓存 10 条结果,
    但由于可售状态一直变,业务经常反馈数据更新不及时,所以想看看有没什么奇淫技巧。

    先在这谢谢各位吴彦祖了。
    20 条回复    2021-10-22 12:22:50 +08:00
    nonoyang
        1
    nonoyang  
       2021-10-21 11:32:28 +08:00   ❤️ 1
    看你 sql,skuid 对应的产品 id 是唯一的吧?
    lenmore
        2
    lenmore  
       2021-10-21 11:40:54 +08:00   ❤️ 1
    价格表做点字段冗余,就不需要 join 这一堆表了。
    chenzheyu
        3
    chenzheyu  
       2021-10-21 11:59:17 +08:00   ❤️ 1
    ORM 有提供 with 的方法吧,多表会用 whereIn 的方法实现。
    2i2Re2PLMaDnghL
        4
    2i2Re2PLMaDnghL  
       2021-10-21 12:15:40 +08:00   ❤️ 1
    你应该用 price WHERE ... 表驱动其他的,因为 price 表作了一个很窄的 WHERE,必要的话可以临时构造 SELECT * FROM price WHERE 表来驱动。
    如果你 sku 有可能对应零个 price 的话这个 SQL 大部分的时间好像会花费在 JOIN 出来然后立马删掉。
    zoharSoul
        5
    zoharSoul  
       2021-10-21 13:39:53 +08:00
    价格表冗余 skuid 就够了
    markgor
        6
    markgor  
    OP
       2021-10-21 15:00:19 +08:00
    @nonoyang skuID 对应的产品 ID 是唯一的;


    @lenmore
    @2i2Re2PLMaDnghL
    @zoharSoul
    因为实际情况还有以下几个环节,一开始漏了写上来:

    ```
    tbl_product:
    id:唯一主键
    isAcitve:int(1)上架 /下架
    ...

    tbl_spu:
    id:主键
    pid:int 对应 tbl_product.id
    isActive:int(1)上架 /下架
    ...

    tbl_sku:
    id:主键
    spuID:int 对应 tbl_spu.id
    isActive:int(1)上架 /下架
    ...

    tbl_price:
    skuID:对应 tbl_sku.id
    bookDate:日期
    isActive:int(1)可售 /停售
    price:int 价格
    /*其中 skuID+bookDate 为联合主键*/

    SQL:

    SELECT
    p.*,min(price) as price
    from
    tbl_product p LEFT JOIN
    tbl_spu spu ON spu.pid = p.id LEFT JOIN
    tbl_sku sku ON sku.spuID = spu.id LEFT JOIN
    tbl_price price ON price.skuID = sku.id
    WHERE
    p.isActive = 1
    AND spu.isActive = 1
    AND sku.isActive = 1
    AND price.isActive = 1
    AND price.bookDate BETWEEN '2021-10-10' AND '2021-10-17'
    AND price.price > 0
    GROUP BY p.id
    LIMIT 10


    逻辑是查询 10 个可售产品;
    可售定义是 product/sku/spu/price 的 isActive 均为 1 且 tbl_price 的 price 大于零 且 价格范围在 '2021-10-10 ~ 2021-10-17'
    所以价格表无法冗余;

    ```



    @chenzheyu
    ORM 感觉只是方便了 SQL 语句的读写,对于性能而言不会有帮助吧;
    left join 改为 where in 方式我还没试过,等等去试试,但估计结果差不多。
    nonoyang
        7
    nonoyang  
       2021-10-21 15:19:09 +08:00
    @markgor 那如果 skuID 对应的产品 ID 不经常变的话,完全可以直接在 price 表上冗余对于的产品 ID
    markgor
        8
    markgor  
    OP
       2021-10-21 17:19:47 +08:00
    @nonoyang
    应该说 skuID 对应的产品 ID 永远不会变
    但是 spu 表和 sku 表的 isActive 会经常改变,
    这个时候除非把 spuIsActive 和 skuIsActive 还有 pid 增加到 tbl_price 这里;
    但是引申出问题就是 spu 和 sku 的可售状态改变(isActive),那 tbl_price 将进行大量更新.
    ricadro
        9
    ricadro  
       2021-10-21 17:57:27 +08:00 via iPhone   ❤️ 1
    把 price 的联合主键换个顺序 时间放在前面试试 where 条件放到最前面 后面跟那些状态的判断
    disk
        10
    disk  
       2021-10-21 22:09:12 +08:00   ❤️ 1
    非联合条件先预筛再连接,可能会好一点,具体得看 explain analyze 。按这个数据量来说的话,14 秒是正常的。
    cppc
        11
    cppc  
       2021-10-21 23:28:29 +08:00   ❤️ 1
    还是觉得应该在价格表冗余 sku,然后在查询结果上过滤,现在的查询把 product spu sku 全都走了一边,太多无用功
    cppc
        12
    cppc  
       2021-10-21 23:35:15 +08:00
    或者换个思路,你的痛点是“业务经常反馈数据更新不及时”,也没有要求实时更新,所以用还是你之前的方案,但是在备库搞,频率高一点,比如 10m 更新一次
    wowbaby
        13
    wowbaby  
       2021-10-22 09:03:47 +08:00   ❤️ 1
    商品这块我一般值用到 3 个表,商品表,sku 表,我都是分开查询,再使用 where in 查询,使用 join 性能非常低下,商品价格历史表我只沉于 skuId,goodid,price,datetime, 每次编辑商品的时候使用一个 goodId tag 缓存标签,如果用 es,同步一下。

    几千万的数据 mysql 应该没有问题,(目前没遇到高并发场景,所有没测), 我没有用 es 之类的服务,所以在列表页数据只能只读 mysql 我缓存了 30 秒,商品详情因为 goodId 的 tag 缓存是实时的。
    markgor
        14
    markgor  
    OP
       2021-10-22 09:09:42 +08:00
    @disk explain 看过了,属于正常,现在困惑的地方在于优化...
    因为从 product->spu->sku->price 条件关联后数据几何倍增,就如您所说的 14 秒是正常.但总觉得有地方可以继续优化但被忽略了.


    @cppc 现在查询都是在从库进行的,我之前也是想把 productID 做去 price 表中,但是因为还有 spu 和 sku 可售状态筛选的问题
    markgor
        15
    markgor  
    OP
       2021-10-22 09:20:27 +08:00
    @wowbaby 业务形式不一样,我们的是酒店价格,所以价格是按日为单位,每天的都不一样,
    酒店--product
    房间--spu
    销售计划--sku

    常规商城的价格这个放去 sku 表中,
    但酒店由于每天房型价格都不一样,所以还会多一个价格表,记录销售计划和销售日期 的售价;
    比方说:
    汉庭酒店
    |--标准房
    |----当天可售
    |------......
    |------2021/09/01
    |------2021/09/02
    |------2021/09/03
    |------......
    |----提前 3 天预定
    |------......
    |------2021/09/01
    |------2021/09/02
    |------2021/09/03
    |------......
    |----不可取消
    |------......
    |------2021/09/01
    |------2021/09/02
    |------2021/09/03
    |------......
    |--豪华房
    |----当天可售
    |------......
    |------2021/09/01
    |------2021/09/02
    |------2021/09/03
    |------......
    |----连住优惠
    |------......
    |------2021/09/01
    |------2021/09/02
    |------2021/09/03
    |------......


    实际业务情况是
    可能哪天,整个房型都需要停售,这时候 spu 上的 isActive 就设置为 0 ;
    可能哪天,某个房型的销售计划停售,这时候这个 sku 的 isActive 就设置为 0 ;
    可能哪一天的房停售,这个时候 price 的 isActive 就会设置为 0 ;


    一般情况下,当携带酒店 ID 和入住日期去查询信息,返回时间基本是毫秒级别;
    仅仅是当需要显示列表形式的时候(只有入住日期没有酒店 ID ),查询时间十多秒。
    2i2Re2PLMaDnghL
        16
    2i2Re2PLMaDnghL  
       2021-10-22 09:36:36 +08:00   ❤️ 1
    * 奇妙的优化:把数据库换成 Oracle (

    且抛开这个
    我先问一下 SELECT * FROM tbl_price price WHERE price.bookDate BETWEEN '2021-10-10' AND '2021-10-17' 有多少数据?耗时?

    另外,逻辑上可行但依具体 SQL 实现不一定有效的优化:
    1. 为 price 表冗余 productid spuid 和 skuid,并星形而非链形 JOIN 到多表来读 isActive (并行替换串行)
    2. 不要先 JOIN 再 WHERE,而是 JOIN (SELECT * FROM tbl WHERE isActive=1 ) (本该被 SQL 优化器优化掉)

    WHERE IN 似乎与 RIGHT JOIN 性能一致。俺怀疑 price WHERE 之后是个小表,应该小表驱动大表。
    markgor
        17
    markgor  
    OP
       2021-10-22 10:58:23 +08:00
    @2i2Re2PLMaDnghL

    》且抛开这个
    SELECT id FROM tbl_price price WHERE price.bookDate BETWEEN '2021-10-10' AND '2021-10-17'
    170W 左右的數據,耗時 1.3 秒;

    SELECT * FROM tbl_price price WHERE price.bookDate BETWEEN '2021-10-10' AND '2021-10-17'
    170W 左右的數據,耗時 23 秒;

    第 2 點測試過,效果基本沒變化;
    第一點由於要加欄位,所以只能後續在測試環境中測試下
    markgor
        18
    markgor  
    OP
       2021-10-22 11:06:12 +08:00
    @2i2Re2PLMaDnghL
    Oracel 之前某个业务使用过,但就目前个人对 Oracle 各项优化方式和使用上并不熟悉,除非 Oracle 内部对查询器进行了优化,否则我觉得性能上估计相差不大,而且除去成本而言,用 Oracle 的话太多关联的程序需要改动,代价太大了;

    之前也有业务上使用 Oracle,当时大概是因为 需要通过存储函数 触发 脚本运行 把数据提交去上级的 Oracle 中。
    2i2Re2PLMaDnghL
        19
    2i2Re2PLMaDnghL  
       2021-10-22 11:25:00 +08:00
    @markgor 优化是脏活累活,不靠经济利益驱动很难搞的,Oracle 确实有多得多的 SQL 优化器,据说其优化 pass 有 MySQL (分支前)的 10 倍以上。

    这个数据量的话应当考虑从 price 而不是 product 开始 JOIN,即下面的 3.

    还有两个思路
    3. 把所有的 JOIN 的表换个方向。把 a LEFT JOIN b LEFT JOIN c 改成 c b a 。因为映射关系是从 price 开始一对一顺次映射到前几个表上去的,而且 price 表够小。
    4. 改成 INNER JOIN,因为你必须要求了 price 存在。但应该和 2. 一样并没有效果。
    markgor
        20
    markgor  
    OP
       2021-10-22 12:22:50 +08:00
    @2i2Re2PLMaDnghL 感謝提點,後續我把那兩個思路也進行測試下;可能換 Oracle 會得到更好的效果,但投入也是巨大的。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5422 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 34ms · UTC 07:37 · PVG 15:37 · LAX 00:37 · JFK 03:37
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.