mysql - 如何改进键表和另一个表之间的 JOIN 操作

Question

我的问题是我有两个不同的查询，它们在不同的环境下运行良好情况。

架构

  messages 
      message_id, entity_id, message, timestamp

   subscription
      user_id, entity_id

   users
      user_id

   entities
      entity_id

情况一:消息条目很多，且至少有一个相关的订阅条目

情况 2:很少的消息条目和/或很少或零相关的订阅条目

我的两个查询是:

 SELECT messages.*
   FROM messages
   STRAIGHT_JOIN subscription ON subscription.entity_id = messages.entity_id
   WHERE subscription.user_id = 1
   ORDER BY messages.timestamp DESC 
   LIMIT 50

此查询在情况 1(.000x 秒)下运行良好:大量消息条目和至少一个相关订阅条目。在情况 2 中，此查询将花费 1.7 秒以上。

 SELECT messages.*
   FROM messages
   INNER JOIN subscription ON subscription.entity_id = messages.entity_id
   WHERE subscription.user_id = 1
   ORDER BY messages.timestamp DESC 
   LIMIT 50

此查询在情况 2(.000x 秒)下运行良好:相关的消息条目和/或很少或零订阅条目很少。在情况 1 中，此查询将花费 1.3 秒以上。

有没有我可以使用的查询，它可以两全其美？如果没有，最好的方法是什么处理这个案子？

索引:

( subscription.user_id, subscription.entity_id )
( subscription.entity_id )
( messages.entity_id, messages.timestamp )
( messages.timestamp )

解释信息

限制 50

| id | select_type | table             | type   | possible_keys                           | key           | key_len | ref                                    | rows | Extra       |
|  1 | SIMPLE      | messages          | index  | idx_timestamp                           | idx_timestamp | 4       | NULL                                   |   50 |             |
|  1 | SIMPLE      | subscription      | eq_ref | PRIMARY,entity_id,user_id               | PRIMARY       | 16      | const, messages.entity_id              |    1 | Using index |

没有限制

| id | select_type | table             | type   | possible_keys                           | key           | key_len | ref                                    |   rows   | Extra         |
|  1 | SIMPLE      | messages          | ALL    | entity_id_2,entity_id                   | NULL          | NULL    | NUL                                    |   255069 | Using filesort|
|  1 | SIMPLE      | subscription      | eq_ref | PRIMARY,entity_id,user_id               | PRIMARY       | 16      | const, messages.entity_id              |        1 | Using index   |

创建表语句:

约 5000 行

subscription | CREATE TABLE `subscription` (
  `user_id`   bigint(20) unsigned NOT NULL,
  `entity_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`user_id`,`entity_id`),
  KEY `entity_id` (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

约 255,000 行

messages | CREATE TABLE `messages` (
  `message_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `entity_id` bigint(20) unsigned NOT NULL,
  `message` varchar(255) NOT NULL DEFAULT '',
  `timestamp` int(10) unsigned NOT NULL,
  PRIMARY KEY (`message_id`),
  KEY `entity_id` (`entity_id`,`timestamp`),
  KEY `idx_timestamp` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

Answer 1

尝试使用 AND 更改您的 WHERE

SELECT messages.*
   FROM messages
   STRAIGHT_JOIN subscription ON subscription.entity_id = messages.entity_id
        AND subscription.user_id = 1
   ORDER BY messages.timestamp DESC 
   LIMIT 50

或

SELECT messages.*
   FROM messages
   INNER JOIN subscription ON subscription.entity_id = messages.entity_id
           AND subscription.user_id = 1
   ORDER BY messages.timestamp DESC 
   LIMIT 50

或者可能是这样:

SELECT messages.*
FROM subscription 
STRAIGHT_JOIN messages ON subscription.entity_id = messages.entity_id
WHERE subscription.user_id = 1
ORDER BY messages.timestamp DESC 
LIMIT 50