我有一个消息表,它有1百万行(并且还在增长)。每个消息查询都涉及到选择isrequest=true或isrequest=false的行,但决不能同时选择这两个行。我的绝大多数查询都在查找isrequest=false。这个表的写入非常频繁,我需要保持快速写入(因为用户喜欢以低延迟相互发送消息)。还要注意,消息表当前除了主键之外没有其他列索引。
95%的行的isrequest=false,只有5%的行的isrequest=true。在这种情况下,索引isrequest布尔字段是否更有效?
另外,我知道索引列会消耗内存,但是对于所有列数据类型(在我的例子中,包括布尔值)来说,这个开销是否相等?
更新:
在与@rick james进一步分析之后,我们提出了一个新的表方案(注意,所有pk都是auto inc,因此时间相关性是可以辨别的):
MESSAGE (id=PK) (sender_id, recipient_id, conversation_id = FKs)
---------------------------------------------------------------
id sender_id recipient_id message conversation_id
1 1 2 "hows it going" 4
2 2 1 "great! hbu" 4
3 1 8 "hey man" 3
4 9 1 "please respond" 2
5 4 6 "goodnight girl" 1
CONVERSATION (id=PK) (userA_id, userB_id = FKs)
-----------------------------------------------
id userA_id userB_id
1 4 6
2 1 9
3 1 8
4 1 2
USERCONVERSATION (id=PK) (userA/B_id, conver_id, lastMsg_id = FKs)
------------------------------------------------------------------
id userA_id userB_id conver_id lastMsg_id isRequest
1 4 6 1 5 False
2 6 4 1 5 False
3 1 9 2 4 True
4 9 1 2 4 True
5 1 8 3 3 False
6 8 1 3 3 False
7 1 2 4 2 False
8 2 1 4 2 False
索引:
MESSAGE: index(id),
index(conversation_id, id)
CONVERSATION: index(id),
USERCONVERSATION: index(id),
index(user_id, isRequest),
index(user_id, lastMessage_id),
index(conversation_id)
应用程序中的查询:
由于如上所述的正确索引,应该执行以下查询。如果可以改进,请联系我们。
要获取变量userid的最新20个对话(包括最后一条消息的内容和其他用户的信息),请执行以下操作:
SELECT T4.userB_id, T4.username, T4.profilePic, T4.conver_id,
T4.message
(
SELECT T1.userB_id, T2.username, T2.profilePic, T1.conversation_id,
T1.lastMessage_id
FROM
(
SELECT userB_id, conversation_id, lastMessage_id
FROM rage.userconversation
WHERE userA_id = {userID}
AND isRequest=False
) AS T1
LEFT JOIN rage.user AS T2 ON T1.userB_id = T2.id AS T3
)
LEFT JOIN rage.message AS T4 ON T1.lastMessage_id = T4.id
ORDER BY T4.id DESC
LIMIT 20
word解释:当lastmessage存储在那里时,获取20个最近的userconversation行。要查找给定用户最近的20行,请选择user\u id=userid的所有行,并按lastmessage\u id desc排序。这是准确的,因为message\u id是自动递增的。除了最后一条消息外,我们还需要获取会话中其他用户的一些用户数据(配置文件图片、用户名)。我们通过左连接来实现这一点。
结果:
RESULT (for userID = 1)
---------------------------------------------------------------
userB_id username profilePic message conver_id
8 John 8.jpg "hey man" 3
2 Daisy 2.jpg "great! hbu" 4
然后,当用户点击一个会话时,由于我们有会话id,我们只需:
SELECT * FROM rage.message WHERE conversation_id={conver_id} ORDER BY id DESC LIMIT 20
希望由于我们索引了(会话\u id,id),排序很快。
3条答案
按热度按时间apeeds0o1#
你有多种选择。根据您的描述,以下两个选项中的一个似乎是合适的:
第一个键是
IsRequest
.一种分区方案,包括
IsRequest
.另一种可能是两张独立的table。
但是,因为我怀疑您的查询是否返回了95%的行,甚至5%,所以毫无疑问还有其他过滤器。为这些过滤器创建索引可能比为布尔标志创建索引更重要。
vwkv1x7d2#
使用综合指数。让我们看看整个
WHERE
条款给你准确的细节。例子
将受益于
(列名称的排列顺序也不重要,它是真是假也不重要。)
你的榜样
OR
破坏索引的使用UNION
在两个查询之间可以避免OR
.在编写查询时,没有索引对查询有用。
将有两个嵌套表扫描。
也许 吧
(我不知道下面的例子是否也一样。)
如果要分页,请参见以下内容:http://mysql.rjweb.org/doc.php/pagination#pagination_and_union
更接近
使用这两个索引:
1
INDEX
对于每个子查询。每个都是最佳的,加上“覆盖”。(我看不出
isRequest
,所以我把它忘了。我怀疑,如果需要该列,则可以将其添加到索引中而不会损失效率(如果将其放置在适当的位置)qqrboqgw3#
对于这个查询,或者其他查询,最好在表中有另一列。它将是一个唯一的数字,比如“conversation\u id”,它是从发送者和接收者的唯一对派生出来的。
一种粗略的方法(但不一定是最佳的方法)是从这个有序对的不同值中得出:
那么
INDEX(conversation_id, id)
可能是讨论的问题的关键。在这一点上,我们可以在讨论布尔值时加上。我怀疑这最终会是最佳索引:(或者可能交换了前两列)。