我有一个MySql查询，运行时间非常长（大约7秒）。查询的这部分中的或似乎有问题：“（tblprivateetem.userid=”？userid或tblprivateetem.userid=1）”。如果跳过“或tblprivateetem.userid=1”部分，只需0.01秒。由于我需要那个部分，我需要找到一种方法来优化这个查询。有什么想法吗？
查询：
SELECT
tbladdeditem.addeditemid,
tblprivateitem.iitemid,
tblprivateitem.itemid
FROM tbladdeditem
INNER JOIN tblprivateitem
ON tblprivateitem.itemid=tbladdeditem.itemid
AND (tblprivateitem.userid=?userid OR tblprivateitem.userid=1)
WHERE tbladdeditem.userid=?userid
说明：
id select_type table type possible_keys key key_len ref rows extra
1 SIMPLE tbladdeditem ref userid userid 4 const 293 Using where
1 SIMPLE tblprivateitem ref userid,itemid itemid 4 tbladdeditem.itemid 2 Using where
桌子：
tbladdeditem包含100000行：
CREATE TABLE `tbladdeditem` (
`addeditemid` int(11) NOT NULL auto_increment,
`itemid` int(11) default NULL,
`userid` mediumint(9) default NULL,
PRIMARY KEY (`addeditemid`),
KEY `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
tblprivateetem包含27万行：
CREATE TABLE `tblprivateitem` (
`privateitemid` int(11) NOT NULL auto_increment,
`userid` mediumint(9) default '1',
`itemid` int(10) NOT NULL,
`iitemid` mediumint(9) default NULL,
PRIMARY KEY (`privateitemid`),
KEY `userid` (`userid`),
KEY `itemid` (`itemid`) //Changed this index to only use itemid instead
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
最佳答案
更新
我让我的查询和模式完全匹配您原来的问题，多列键和所有。唯一可能的区别是我用200万个条目填充了每个表。我的查询（你的查询）在0.15秒内运行。
delimiter $$
set @userid = 6
$$
SELECT
tbladdeditem.addeditemid, tblprivateitem.iitemid, tblprivateitem.itemid
FROM tbladdeditem
INNER JOIN tblprivateitem
ON tblprivateitem.itemid=tbladdeditem.itemid
AND (tblprivateitem.userid=@userid or tblprivateitem.userid = 1)
WHERE tbladdeditem.userid=@userid
我有和您一样的解释，而且对于我的数据，我的查询返回了超过1000个匹配项，没有任何问题。完全不知所措，因为你真的不应该有这些问题——你是否有可能运行一个非常有限的MySQL版本？你是64位的吗？有足够的记忆？
我假设你的查询没有很好地执行，当我的时候，假设我已经解决了你的问题。所以现在我吃乌鸦。我会张贴一些我去过的街道。但我告诉你，你的查询，你发布它的方式原来工作得很好。我只能想象你的MySQL被硬盘或其他什么东西打败了。对不起，我帮不了你。
以前的响应（也是更新）
我在自己的数据库中分解并重新创建了你的问题。在尝试了
userid和itemid上的独立索引之后，我在几秒钟内无法获得查询，因此我按照查询的指示设置了非常特定的多列键。请注意，在tbladdeditem上，多列查询以itemid开头，而在tblprivateitem上，列是相反的：这是我使用的模式：
CREATE TABLE `tbladdeditem` (
`addeditemid` int(11) NOT NULL AUTO_INCREMENT,
`itemid` int(11) NOT NULL,
`userid` mediumint(9) NOT NULL,
PRIMARY KEY (`addeditemid`),
KEY `userid` (`userid`),
KEY `i_and_u` (`itemid`,`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `tblprivateitem` (
`privateitemid` int(11) NOT NULL AUTO_INCREMENT,
`userid` mediumint(9) NOT NULL DEFAULT '1',
`itemid` int(10) NOT NULL,
`iitemid` mediumint(9) NOT NULL,
PRIMARY KEY (`privateitemid`),
KEY `userid` (`userid`),
KEY `itemid` (`itemid`),
KEY `u_and_i` (`userid`,`itemid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
我把200万条随机数据填到每个表格里。我做了一些假设：
用户id从1到2000不等
项目ID介于1和10000之间
这将为每个用户在每个表中提供大约1000个条目。
以下是查询的两个版本（我使用workbench作为编辑器）：
版本1-对连接执行所有筛选。
结果：0.016秒返回1297行
delimiter $$
set @userid = 3
$$
SELECT
a.addeditemid,
p.iitemid,
p.itemid
FROM tblprivateitem as p
INNER JOIN tbladdeditem as a
ON (p.userid in (1, @userid))
AND p.itemid = a.itemid
AND a.userid = @userid
$$
下面是解释：
EXPLAIN:
id select_type table type key ref rows extra
1 SIMPLE p range u_and_i 2150 Using where; Using index
1 SIMPLE a ref i_and_u 1 Using where; Using index
版本2-前置过滤器
结果：0.015秒返回1297行
delimiter $$
set @userid = 3
$$
SELECT
a.addeditemid,
p.iitemid,
p.itemid
from
(select userid, itemid, iitemid from tblprivateitem
where userid in (1, @userid)) as p
join tbladdeditem as a on p.userid = a.userid and a.itemid = p.itemid;
where a.userid = @userid
$$
下面是解释：
id select_type table type key ref rows extra
1 PRIMARY <derived2> ALL null null 2152
1 PRIMARY a ref i_and_u p.itemid,const 1 Using where; Using index
2 DERIVED p1 range u_and_i 2150 Using where
关于mysql - 帮我优化这个MySql查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/7258025/