请教关于Mysql小表关联大表的优化问题

Question

问题描述

我有两张表
一张groupbuy_order表(团购订单表) 数据只有几百行
ID 主键
orderID 订单编号 (key索引)
groubuyID 团购编号 (key索引)

一张order表(订单表) 数据大概40W条
ID 主键
orderID 订单编号(unique key索引)
....
....
orderStatus 订单支付状态

问题出现的环境背景及自己尝试过哪些方法

昨天代码上线以后 CPU一直跑高(我们数据库是阿里云的2核4G) 导致其他业务也没有办法访问
后来请阿里云诊断了下 发现扫描与返回比很高 基本都是全表扫描
但是关联的索引我们已经都加过了

相关代码

// 请把代码文本粘贴到下方（请勿用图片代替代码）
多数慢查询都是这两个表关联,其他慢查询都是在此基础上变化的
SELECT a.orderID
FROM groupbuy_order a
LEFT JOIN order o ON o.orderID=a.orderID
WHERE groupbuyID = 'xxxxx'
and orderStatus = '已支付'

explain结果如下

你期待的结果是什么？实际看到的错误信息又是什么？

有什么好的解决方法嘛?

Answer 1

根据你的sql来看的话，问题可能在于这条where条件，当中有关于order表orderStatus的过滤条件，你可以先explain一下：

explain SELECT a.orderID
FROM groupbuy_order a 
LEFT JOIN order o ON o.orderID=a.orderID 
WHERE groupbuyID = 'xxxxx' 
and orderStatus = '已支付'

可以看到会去扫表：

针对这种情况，其实就可以建立一个给order表的orderId、orderStatus的联合索引：

ALTER TABLE `order` ADD  INDEX  ix_id_status ( `orderId`, `orderStatus` ) ; 

然后你再explain一下，就可以看到不会去扫表了：

希望能帮助到你。

Answer 2

你好 ,感谢回答 我添加联合索引以后跟你explain不一样 还是扫描了20W行

Answer 3

这个选错驱动表，大表做了驱动表，是不是 order 表也有一个 groupbuyID 字段，手动选择一下 a 表的 groupbuyID

SELECT a.orderID
FROM groupbuy_order a 
LEFT JOIN order o ON o.orderID=a.orderID 
WHERE a.groupbuyID = 'xxxxx' 
and o.orderStatus = '已支付'

---

如果还是选错驱动表的话，把 o 表的条件改为 having 中筛选

SELECT a.orderID
FROM groupbuy_order a 
LEFT JOIN order o ON o.orderID=a.orderID 
WHERE a.groupbuyID = 'xxxxx'
having o.orderStatus = '已支付'

还有一个办法就是把 left join 改为 STRAIGHT_JOIN，手动选择驱动表

SELECT a.orderID
FROM groupbuy_order a 
STRAIGHT_JOIN order o ON o.orderID=a.orderID 
WHERE a.groupbuyID = 'xxxxx' 
and o.orderStatus = '已支付'