我正在运行be查询
SELECT packages.id, packages.title, subcat.id, packages.weight
FROM packages ,provider, packagestosubcat,
packagestocity, subcat, usertosubcat,
usertocity, usertoprovider
WHERE packages.endDate >'2011-03-11 06:00:00' AND
usertosubcat.userid = 1 AND
usertocity.userid = 1 AND
packages.providerid = provider.id AND
packages.id = packagestosubcat.packageid AND
packages.id = packagestocity.packageid AND
packagestosubcat.subcatid = subcat.id AND
usertosubcat.subcatid = packagestosubcat.subcatid AND
usertocity.cityid = packagestocity.cityid AND
(
provider.providertype = 'reg' OR
(
usertoprovider.userid = 1 AND
provider.providertype != 'reg' AND
usertoprovider.providerid = provider.ID
)
)
GROUP BY packages.title
ORDER BY subcat.id, packages.weight DESC
当我运行explain时，除了usertoprovider表上的扫描似乎没有问题，它似乎没有使用表的键：
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE usertocity ref user,city user 4 const 4 Using temporary; Using filesort
1 SIMPLE packagestocity ref city,packageid city 4 usertocity.cityid 419
1 SIMPLE packages eq_ref PRIMARY,enddate PRIMARY 4 packagestocity.packageid 1 Using where
1 SIMPLE provider eq_ref PRIMARY,providertype PRIMARY 4 packages.providerid 1 Using where
1 SIMPLE packagestosubcat ref subcatid,packageid packageid 4 packages.id 1 Using where
1 SIMPLE subcat eq_ref PRIMARY PRIMARY 4 packagestosubcat.subcatid 1
1 SIMPLE usertosubcat ref userid,subcatid subcatid 4 const 12 Using where
1 SIMPLE usertoprovider ALL userid,providerid NULL NULL NULL 3735 Using where
正如您在上面的查询中看到的，条件本身是：
provider.providertype = 'reg' OR
(
usertoprovider.userid = 1 AND
provider.providertype != 'reg' AND
usertoprovider.providerid = provider.ID
)
provider和usertoprovider表都被索引。提供程序在providerid和providertype上有索引，而usertoprovider在userid和providerid上有索引
密钥的基数是：
provider.id=47，provider.type=1，usertoprovider.userid=1245，usertoprovider.providerid=6
所以很明显索引没有被使用。
更进一步，为了测试它，我继续说：
复制了usertoprovider表
将providertype为'reg'的所有提供程序值插入到克隆的表中
将条件简化为（usertoprovider.userid=1和usertoprovider.provider ID=provider.ID）
查询执行时间已从8.1317秒更改。到0.0387秒。
不过，providertype为'reg'的提供程序值对所有用户都有效，我希望避免将这些值插入到所有用户的usertoprovider表中，因为这些数据是多余的。
有人能解释一下为什么MySQL仍然运行一个完整的扫描并且不使用密钥吗？怎样才能避免呢？
最佳答案
似乎provider.providertype != 'reg'是多余的（始终为true），除非provider.providertype可以为空，并且您希望查询在空时失败。
尽管MySQL可能允许!=，但<>不应该成为标准SQL吗？
关于表扫描的成本
完全表扫描并不一定比遍历索引更昂贵，因为遍历索引仍然需要多个页面访问。在许多数据库引擎中，如果表足够小，可以放在几页中，并且行数足够小，那么进行表扫描会更便宜。数据库引擎根据表的数据和索引统计信息做出此类决策。
这个案子
然而，在您的情况下，这也可能是因为OR子句中的另一条腿：!=。如果providertype是“reg”，那么这个查询将连接到usertoprovider的所有行（很可能不是您想要的），因为它是一个多表交叉连接。
数据库引擎确定您可能无论如何都需要usertoprovider中的所有表行是正确的（除非所有provider类型都不是“reg”，但引擎也可能知道！）.
查询隐藏了这个事实，因为您正在（MASSIVE！）结果集稍后返回包ID，这样就看不到返回了多少usertoprovider行。但它会跑得很慢。去掉GROUPBY子句，找出您实际强制数据库引擎工作的行数！!!
如果您填写usertoprovider表，您会看到一个巨大的速度改进，这是因为随后每一行都参与一个连接，并且在“reg”的情况下没有发生完全交叉连接。以前，如果usertoprovider中有1000行，那么type=“reg”的每一行都会将结果集扩展1000倍。现在，该行只与usertoprovider中的一行联接，结果集不会展开。
如果您确实想传递providertype为'reg'的任何内容，但不是在多对多映射表中，那么最简单的方法可能是使用子查询：
从from子句中删除usertoprovider
执行以下操作：provider.providertype = 'reg'
另一种方法是在usertoprovider上使用外部联接——表中不包含“reg”的任何行都将返回一行NULL，而不是展开结果集。