4
请教 MYSQL 多表联查数据优化方式
source link: https://www.v2ex.com/t/809455
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
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 条附言 · 17 小时 28 分钟前
因为实际情况还有以下几个环节，一开始漏了写上来：
```
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 条结果，
但由于可售状态一直变，业务经常反馈数据更新不及时，所以想看看有没什么奇淫技巧。
先在这谢谢各位吴彦祖了。
```
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 条结果，
但由于可售状态一直变，业务经常反馈数据更新不及时，所以想看看有没什么奇淫技巧。
先在这谢谢各位吴彦祖了。
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK