MySQL join 是扫描表而不是使用索引

Question

我有下表:

显示创建表 access_token_status;

CREATE TABLE `access_token_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `status` varchar(10) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `status` (`status`),
  KEY `idx_access_token_status_status_lookup_2` (`status`,`id`),
  KEY `idx_access_token_status_status_lookup_1` (`id`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

和

显示创建表用户；

CREATE TABLE `user` (
  `id` varchar(17) NOT NULL,
  `short_lived_access_token_status_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_status_lookup_1` (`id`,`short_lived_access_token_status_id`),
  KEY `idx_user_status_lookup_2` (`short_lived_access_token_status_id`,`id`),
  KEY `ix_user_short_lived_access_token_status_id` (`short_lived_access_token_status_id`),
  CONSTRAINT `user_ibfk_1` FOREIGN KEY (`short_lived_access_token_status_id`) REFERENCES `access_token_status` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

和

显示创建表帐户；

CREATE TABLE `account` (
  `id` varchar(17) NOT NULL,
  `user_id` varchar(17) NOT NULL,
  `track` tinyint(1) NOT NULL,
  `estimated_time_to_regain_access` int(11) NOT NULL,
  `media_list_fetched_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_account_next_fetch_lookup` (`user_id`,`estimated_time_to_regain_access`,`track`,`media_list_fetched_at`),
  CONSTRAINT `account_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
  CONSTRAINT `account_chk_1` CHECK ((`track` in (0,1)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

当我尝试解释以下查询时

explain select a.*
        from account a
        inner join user u
        on a.user_id = u.id
        inner  join access_token_status as s
        on u.short_lived_access_token_status_id = s.id and s.status = 'valid'
        where
            u.short_lived_access_token_status_id = 3
            and a.estimated_time_to_regain_access = 0
            and a.track = true
            and a.media_list_fetched_at > '2020-05-30 12:31:01'
        limit 1
        for update of a SKIP LOCKED

我得到这个输出:

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 's', NULL, 'const', 'PRIMARY,status,idx_access_token_status_status_lookup_2,idx_access_token_status_status_lookup_1', 'PRIMARY', '4', 'const', '1', '100.00', NULL
'1', 'SIMPLE', 'a', NULL, 'index', 'idx_account_next_fetch_lookup', 'idx_account_next_fetch_lookup', '80', NULL, '2', '50.00', 'Using where; Using index'
'1', 'SIMPLE', 'u', NULL, 'eq_ref', 'PRIMARY,idx_user_status_lookup_1,idx_user_status_lookup_2,ix_user_short_lived_access_token_status_id', 'PRIMARY', '70', 'media_meta.a.user_id', '1', '100.00', 'Using where'

有些表似乎没有使用索引。这对我来说是一个问题，因为扫描的行被锁定并且其他查询将跳过它们(由于 SKIP LOCKED 需要确保查询不会相互阻塞)

我不确定我缺少什么索引或者我是否需要更改查询中的某些内容

Answer 1

在您的查询中，您使用“u.short_lived_access_token_status_id = 3”，因此与“s”的连接看起来是不可能的(如果 s.id = 3 的 s.status 不是“有效的”)或多余的(如果是) .除非你从 s 中获取一些其他列。

现在让我们看看“u”用在哪里:

on a.user_id = u.id
...
    on u.short_lived_access_token_status_id = ...

因此您使用的是基于 u.short_lived_access_token_status_id = 3 的主要选择标准，您需要从中获取 u.id。因此，您的 idx_user_status_lookup_2 应该用作覆盖索引。

为什么不呢？可能是因为表太小以至于无关紧要，或者因为与 S 的连接干扰了优化器(您会注意到表 u 被评估为第三)。

如果可能的话，尝试删除与 s 的连接，看看会发生什么。