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

[markgor]

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

因为实际情况还有以下几个环节，一开始漏了写上来：

```
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 条结果，
但由于可售状态一直变，业务经常反馈数据更新不及时，所以想看看有没什么奇淫技巧。

先在这谢谢各位吴彦祖了。

[nonoyang]

看你 sql，skuid 对应的产品 id 是唯一的吧？

[lenmore]

价格表做点字段冗余，就不需要 join 这一堆表了。

[chenzheyu]

ORM 有提供 with 的方法吧，多表会用 whereIn 的方法实现。

[2i2Re2PLMaDnghL]

你应该用 price WHERE ... 表驱动其他的，因为 price 表作了一个很窄的 WHERE，必要的话可以临时构造 SELECT * FROM price WHERE 表来驱动。
如果你 sku 有可能对应零个 price 的话这个 SQL 大部分的时间好像会花费在 JOIN 出来然后立马删掉。

[zoharSoul]

价格表冗余 skuid 就够了

[markgor]

@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]

@markgor 那如果 skuID 对应的产品 ID 不经常变的话，完全可以直接在 price 表上冗余对于的产品 ID

[markgor]

@nonoyang
应该说 skuID 对应的产品 ID 永远不会变
但是 spu 表和 sku 表的 isActive 会经常改变，
这个时候除非把 spuIsActive 和 skuIsActive 还有 pid 增加到 tbl_price 这里；
但是引申出问题就是 spu 和 sku 的可售状态改变(isActive)，那 tbl_price 将进行大量更新.

[ricadro]

把 price 的联合主键换个顺序 时间放在前面试试 where 条件放到最前面 后面跟那些状态的判断

[disk]

非联合条件先预筛再连接，可能会好一点，具体得看 explain analyze 。按这个数据量来说的话，14 秒是正常的。

[cppc]

还是觉得应该在价格表冗余 sku，然后在查询结果上过滤，现在的查询把 product spu sku 全都走了一边，太多无用功

[cppc]

或者换个思路，你的痛点是“业务经常反馈数据更新不及时”，也没有要求实时更新，所以用还是你之前的方案，但是在备库搞，频率高一点，比如 10m 更新一次

[wowbaby]

商品这块我一般值用到 3 个表，商品表，sku 表，我都是分开查询，再使用 where in 查询，使用 join 性能非常低下，商品价格历史表我只沉于 skuId,goodid,price,datetime, 每次编辑商品的时候使用一个 goodId tag 缓存标签，如果用 es，同步一下。

几千万的数据 mysql 应该没有问题，（目前没遇到高并发场景，所有没测）, 我没有用 es 之类的服务，所以在列表页数据只能只读 mysql 我缓存了 30 秒，商品详情因为 goodId 的 tag 缓存是实时的。

[markgor]

@disk explain 看过了，属于正常，现在困惑的地方在于优化...
因为从 product->spu->sku->price 条件关联后数据几何倍增，就如您所说的 14 秒是正常.但总觉得有地方可以继续优化但被忽略了.


@cppc 现在查询都是在从库进行的，我之前也是想把 productID 做去 price 表中，但是因为还有 spu 和 sku 可售状态筛选的问题

[markgor]

@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]

* 奇妙的优化：把数据库换成 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]

@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]

@2i2Re2PLMaDnghL
Oracel 之前某个业务使用过，但就目前个人对 Oracle 各项优化方式和使用上并不熟悉，除非 Oracle 内部对查询器进行了优化，否则我觉得性能上估计相差不大，而且除去成本而言，用 Oracle 的话太多关联的程序需要改动，代价太大了；

之前也有业务上使用 Oracle，当时大概是因为 需要通过存储函数 触发 脚本运行 把数据提交去上级的 Oracle 中。

[2i2Re2PLMaDnghL]

@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]

@2i2Re2PLMaDnghL 感謝提點，後續我把那兩個思路也進行測試下；可能換 Oracle 會得到更好的效果，但投入也是巨大的。