Mysql 缓慢 "OR"运算符，但联合或两个单独的联接似乎没有选择

Question

我遇到了“OR”运算符在 mysql 中未使用任何索引的典型性能问题:

SELECT sms.smsID, sms.phonenumber, sms.text, date, mbr.name, mbr.prename 
FROM sms INNER JOIN (SELECT MAX(smsID) as smsID, phonenumber FROM sms 
group by phonenumber) sms2 ON sms.phonenumber = sms2.phonenumber AND 
sms.smsID = sms2.smsID
LEFT JOIN mbr ON mbr.telephone = sms.phonenumber OR mbr.mobilphone = 
sms.phonenumber where sms.smsmodem = 'GSM1'
order by date desc LIMIT 0,50

查询应返回最近 50 条收到的短信，但每个电话号码仅返回一条。然后它应该将其与mbr表连接起来，以查找电话号码是否与mbr表中的人名相关联。无论如何，在 mbr 表中，电话号码可能位于“电话”或“手机”列中，因此我们必须对两者进行检查。 where 子句中的 OR 运算符使查询时间超过 20 秒，因为它没有使用 mbr.telephone 和 mbr.mobilephone 存在的索引。

找到的解决方案是执行如下所示的 LEFT JOIN 构造: SQL - left join with OR operator (MySQL)或像这样的 UNION 构造: MySql JOIN query with OR clause very slow

但这两种情况我都不能使用。左连接构造将返回四列，而不是仅两列:

SELECT sms.smsID, sms.phonenumber, sms.text, date, mbr.name,    
mbr.prename, mbr2.name, mbr2.prename FROM sms INNER JOIN (SELECT 
MAX(smsID) as smsID, phonenumber FROM sms group by phonenumber) sms2 ON 
sms.phonenumber = sms2.phonenumber AND sms.smsID = sms2.smsID LEFT JOIN 
mbr ON mbr.telephone = sms.phonenumber left join mbr as mbr2 ON 
mbr2.mobilphone = sms.phonenumber where sms.smsmodem = 'GSM1' order by 
date desc LIMIT 0,50

联合解决方案只会返回两列，但会返回每条收到的消息两次，一次包含发送者的名称，一次不包含(因为一个选择会在 mbr 中找到相应的行，而另一个则不会):

SELECT sms.smsID, sms.phonenumber, sms.text, date, mbr.name,    
mbr.prename, mbr2.name, mbr2.prename FROM sms INNER JOIN (SELECT 
MAX(smsID) as smsID, phonenumber FROM sms group by phonenumber) sms2 ON 
sms.phonenumber = sms2.phonenumber AND sms.smsID = sms2.smsID LEFT JOIN 
mbr ON mbr.telephone = sms.phonenumber where sms.smsmodem = 'GSM1'
UNION SELECT sms.smsID, sms.phonenumber, sms.text, date, mbr.name,    
mbr.prename, mbr2.name, mbr2.prename FROM sms INNER JOIN (SELECT 
MAX(smsID) as smsID, phonenumber FROM sms group by phonenumber) sms2 ON 
sms.phonenumber = sms2.phonenumber AND sms.smsID = sms2.smsID LEFT JOIN 
mbr ON mbr.mobilephone = sms.phonenumber where sms.smsmodem = 'GSM1'
order by date desc LIMIT 0,50

我有点想知道这些“OR”运算符问题在 2016 年仍然存在......

Answer 1

OR 低效率仍然存在，因为没有简单(或困难)的解决方案。即使内置解决方案看起来也很像 UNION 解决方法。

让我们从不同的角度来看一下解决方案。您似乎有“一个跨列展开的数组”。在这种情况下，它是一个简单的 2 元素数组，但它引起了麻烦。我指的是电话/手机号码。

考虑在主表中不包含任何此类数字，而是使用另一个数字表。它可能有 3 列:smsID、number 和 number_type(如果需要)。通过在 Numbers 表中进行一次查找，可以消除OR。

由于查询似乎是“分组最大值”，我还将向您指出我的 blog有效的方法来做到这一点。