Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.5k views
in Technique[技术] by (71.8m points)

兄弟们,MySQL三表关联查询优化,求救

我有3张表,分别是物流进出的表(stock_flow)、用户表(user)、商品表(product)。
现在需要关联查询 获取每个用户进出库了什么商品。

stock_flow表结构
id 主键
uid 用户ID
productID 商品ID

user表结构
id 主键
username 姓名

product表结构
id 主键
title 产品名

我自己写的SQL语句

SELECT
    a.*,
    b.title,
    c.username
FROM
    (select * from stock_flow GROUP BY uid,productID) a
     JOIN product b ON a.productID = b.id
     left join user c ON a.uid = c.id
     order by a.uid ASC

这样基本结果就是我要的
然后explain了一下
product表 Extra是 Using temporary; Using filesort
user表 Extra是 Using where; Using join buffer (Block Nested Loop)

现在就不清楚怎么优化,望赐教!


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

为什么不把title和username写入stock_flow啊,这样就不需要连表了

不改表的话,我感觉直接连表就行了啊

alter table stock_flow add index uid_pid (uid, productID);

SELECT a.*,
    b.title,
    c.username
from stock_flow a,product b,user c where a.productID = b.id and a.uid = c.id order by a.uid ASC;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...