我应该用低'true'基数索引布尔字段吗?

falq053o  于 2021-07-26  发布在  Java
关注(0)|答案(3)|浏览(302)

我有一个消息表,它有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),排序很快。

apeeds0o

apeeds0o1#

你有多种选择。根据您的描述,以下两个选项中的一个似乎是合适的:
第一个键是 IsRequest .
一种分区方案,包括 IsRequest .
另一种可能是两张独立的table。
但是,因为我怀疑您的查询是否返回了95%的行,甚至5%,所以毫无疑问还有其他过滤器。为这些过滤器创建索引可能比为布尔标志创建索引更重要。

vwkv1x7d

vwkv1x7d2#

使用综合指数。让我们看看整个 WHERE 条款给你准确的细节。
例子

WHERE IsRequest = True
  AND UserId = 12345

将受益于

INDEX(IsRequest, UserId)

(列名称的排列顺序也不重要,它是真是假也不重要。)
你的榜样 OR 破坏索引的使用 UNION 在两个查询之间可以避免 OR .
在编写查询时,没有索引对查询有用。
将有两个嵌套表扫描。
也许 吧
(我不知道下面的例子是否也一样。)

( SELECT  m1.id, m1.sender_id, m1.recipient_id, m1.message ...
    FROM  myapp_message AS m1
    LEFT JOIN  app_message AS m2
         ON  m1.sender_id = m2.sender_id
        AND  m1.id < m2.id
    WHERE  m2.id IS NULL
      AND  m1.sender_id = {userID}
      AND  m1.isRequest = False
    order by  id desc
    LIMIT  20
) UNION ALL (
SELECT  m1.id, m1.sender_id, m1.recipient_id, m1.message ...
    FROM  myapp_message AS m1
    LEFT JOIN  app_message AS m2
         ON  m1.recipient_id = m2.recipient_id
        AND  m1.id < m2.id
    WHERE  m2.id IS NULL
      AND  m1.recipient_id= {userID}
      AND  m1.isRequest = False
    order by  id desc
    LIMIT  20 
)   ORDER BY id DESC LIMIT 20

如果要分页,请参见以下内容:http://mysql.rjweb.org/doc.php/pagination#pagination_and_union
更接近

SELECT  m...
    FROM
      ( SELECT xid, MAX(mid) AS mid
        FROM
        (
          ( SELECT  recipient_id AS xid,
                    MAX(mid) AS mid      -- The last message TO each recipient
                FROM  WHERE sender_id = 1234  -- FROM the user in question
                GROUP BY  recipient_id
                ORDER BY 2 DESC   -- ("2nd column")
                LIMIT  20                        
          )
          UNION ALL
          ( SELECT  sender_id AS xid,
                    MAX(mid) AS mid      -- The last message FROM each sender
                FROM  WHERE recipient_id = 1234  -- TO the user
                GROUP BY  sender_id
                ORDER BY 2 DESC
                LIMIT  20
          )
        ) AS y
        GROUP BY xid       -- yes, repeated
        ORDER BY mid DESC  -- yes, repeated
        LIMIT 20           -- yes, repeated
      ) AS x
    JOIN messages AS m  ON m.mid = x.mid

使用这两个索引:

INDEX(sender_id, recipient_id, mid)
INDEX(recipient_id, sender_id, mid)

1 INDEX 对于每个子查询。每个都是最佳的,加上“覆盖”。
(我看不出 isRequest ,所以我把它忘了。我怀疑,如果需要该列,则可以将其添加到索引中而不会损失效率(如果将其放置在适当的位置)

qqrboqgw

qqrboqgw3#

对于这个查询,或者其他查询,最好在表中有另一列。它将是一个唯一的数字,比如“conversation\u id”,它是从发送者和接收者的唯一对派生出来的。
一种粗略的方法(但不一定是最佳的方法)是从这个有序对的不同值中得出:

(LEAST(sender_id, recipient_id), GREATEST(recipient_id, sender_id))

那么 INDEX(conversation_id, id) 可能是讨论的问题的关键。在这一点上,我们可以在讨论布尔值时加上。我怀疑这最终会是最佳索引:

INDEX(conversation_id, isRequest, id)

(或者可能交换了前两列)。

相关问题