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

Question

在MYSQL数据库中，有两张表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

尝试过用 JOIN ms_property prop1 ON prop1.msgid = prop.msgid where prop1.property ***的方法，性能更差 

Answer 2

show index from ms_property 贴出来看一下。

Answer 3

两张表ms_property ，ms_messages都有索引
ms_messages
-----
msgid(pk)ms_property
---------------
propid(pk) msgid(idx) property(idx)

Answer 4

将IN->INNER JOIN
SELECT msg.msgid,msg.content FROM ms_messages msg
JOIN ms_property prop ON prop.msgid = msg.msgid
INNER JOIN (SELECT DISTINCT msgid FROM ms_property WHERE property = '11') BB
ON prop.msgid=BB.msgid
....
WHERE msg.status = 1

Answer 5

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Commentsms_msg_property 0 PRIMARY 1 proid A 79096 NULL NULL BTREE sms_msg_property 1 msgid 1 msgid A 7909 NULL NULL YES BTREE sms_msg_property 1 classid 1 classid A 6591 NULL NULL YES BTREE sms_msg_property 1 property 1 property A 79096 NULL NULL YES BTREE

Answer 6

"Table" "Non_unique" "Key_name" "Seq_in_index" "Column_name" "Collation" "Cardinality" "Sub_part" "Packed" "Null" "Index_type" "Comment"
"sms_msg_property" "0" "PRIMARY" "1" "proid" "A" "79096" \N \N "" "BTREE" ""
"sms_msg_property" "1" "msgid" "1" "msgid" "A" "7909" \N \N "YES" "BTREE" ""
"sms_msg_property" "1" "classid" "1" "classid" "A" "6591" \N \N "YES" "BTREE" ""
"sms_msg_property" "1" "property" "1" "property" "A" "79096" \N \N "YES" "BTREE" ""

Answer 7

SELECT DISTINCT msgid FROM ms_property WHERE property = '11')先把这些DISTINCT  去掉试一下。

Answer 8

首先，如狼头所说，distinct似无必要其次，不知lz的sql是随便写的，还是真实代码，如果是真实代码，根据布尔逻辑的分配率原则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')等价于AND prop.msgid in (SELECT msgid FROM ms_property 
WHERE property = '11'
  AND property <> '22'
  AND property IN ( '11','AA')
  AND property NOT IN ('33','BB')
  AND property IN ('44,','22','CC')
)其中，又可以辅以结合律：
  AND property IN ('11','11','AA','44','22','CC')
  AND property NOT IN ('22','33','BB')

Answer 9

AND prop.msgid in (
    SELECT msgid FROM ms_property 
     WHERE property IN ('11','11','AA','44','22','CC')
       AND property NOT IN ('22','33','BB')
)

Answer 10

AND prop.msgid in (SELECT msgid FROM ms_property 
WHERE property = '11'
  AND property <> '22'
  AND property IN ( '11','AA')
  AND property NOT IN ('33','BB')
  AND property IN ('44,','22','CC')
)之后需要程序判断参数值才能优化