怎么样提高 两表的多次关联查询性能？

Question

在数据库中，有两张表ms_property ，ms_messages表ms_messages存放内容， 表ms_property存放属性，属性不是固定数量的，可能有很多，记录量约50W级
两表通过msgid关联，一条内容记录对应不定数的属性记录，记录数约6万级表ms_propertypropid msgid property
-----------------------------
1 1 11
2 1 22
3 1 33
4 1 44
5 1 AA
6 1 BB
7 1 CC
.
.
.
8 2 11
9 2 22
10 2 33
11 2 44
12 2 AA
13 2 BB
14 2 CC
.
.
.表ms_messagesmsgid message
----------------------------
1 msg1
2 msg2
.
.
.为了查到符合指定属性要求的记录，我目前用下面的SQL，性能特别慢SELECT msg.msgid,msg.content FROM ms_messages msg
JOIN ms_property prop ON prop.msgid = msg.msgid
WHERE msg.status = 1
AND prop.msgid in(SELECT DISTINCT msgid FROM ms_property WHERE property = '11')
AND prop.msgid in(SELECT DISTINCT msgid FROM ms_property WHERE property <> '22')
AND prop.msgid in(SELECT DISTINCT msgid FROM ms_property WHERE property IN ( '11','AA'))
AND prop.msgid in(SELECT DISTINCT msgid FROM ms_property WHERE property NOT IN ('33','BB'))
AND prop.msgid in(SELECT DISTINCT msgid FROM ms_property WHERE property IN ('44,','22','CC')
GROUP BY msg.msgid
LIMIT 20那位可以指定一下，怎么去优化SQL，或是有什么简洁的设计思路

Answer 1

1
prop.msgid in(括弧里的多个语句可以合成一个 ....)2 in 里边的 distinct 没必要3  可以用 exists  join 代替in

Answer 2

SELECT msg.msgid,msg.content FROM ms_messages msg
JOIN ms_property prop ON prop.msgid = msg.msgid
WHERE msg.status = 1
AND prop.msgid in(SELECT msgid FROM ms_property WHERE property <> '22'
and property IN ( '11','AA')
AND property NOT IN ('33','BB')
AND property IN ('44,','22','CC'))
GROUP BY msg.msgid
LIMIT 20

Answer 3

感谢回楼上，其实用的mysql用过JOIN ms_property prop1 ON prop1.msgid = prop.msgid where prop1.property ***的方法，性能更差

Answer 4

SELECT msg.msgid,msg.content FROM ms_messages msg
JOIN ms_property prop ON prop.msgid = msg.msgid
WHERE msg.status = 1
AND prop.msgid in(
SELECT msgid FROM ms_property WHERE property IN ( '11','AA','44,','22','CC') 
AND property NOT IN ('33','BB','22'))
GROUP BY msg.msgid
LIMIT 20

Answer 5

join  然后 prop1.msgid = prop.msgid两列建索引  不可能更差

Answer 6

两个表都建有索引表ms_property
propid（pk) msgid property都有是索引

Answer 7

SELECT msgid FROM ms_property WHERE property IN ( '11','AA','44,','22','CC') 
归并到一个条件里，查出来的数据就不对了

Answer 8

MySQL的开发问题尽量不要题错版，以免发生不必要的错误和麻烦。

Answer 9

呵呵，主要还是这个版的高手多，人气旺，不过这问题不局限于mysql来解决，主要涉及如何设计表结构和构建SQL语句