mysql - 两个索引良好的表的单个 INNER JOIN 运行时间超过一分钟

Question

我有一个查询需要大约 90 秒才能运行，即使表应该有正确的索引。我不明白为什么。

我使用的是 MySQL，表是 InnoDB。

这是查询:

SELECT count(*)
FROM `following_lists` fl INNER JOIN users u 
ON fl.user_uuid = u.user_uuid
WHERE fl.following_query_id = 1000010 AND u.status <= 2

我希望这个查询从表 following_lists 开始，根据WHERE条件抓取约4K条记录，将这些记录加入表users通过其主键，检查用户表中字段的值，并返回结果记录的计数。为什么需要这么长时间？会不会是因为我加入表格的两个字段是 CHAR(40) 而不是整数？

这些是涉及的表及其索引:

CREATE TABLE `users` ( 
  `user_uuid` CHAR(40) NOT NULL, 
  `status` TINYINT UNSIGNED NOT NULL, 
  ...

  PRIMARY KEY (`user_uuid`), 
  ...
)

CREATE TABLE `following_lists` ( 
  `following_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `following_query_id` INT UNSIGNED NOT NULL,
  `user_uuid` CHAR(40) NOT NULL,

  PRIMARY KEY (`following_id`), 
  KEY `query_id` (`following_query_id`),
  KEY `user_uuid` (`user_uuid`)
)

这是解释查询的输出:

+----+-------------+-------+--------+--------------------+----------+---------+--------------+------+-------------+
| id | select_type | table |  type  |   possible_keys    |   key    | key_len |     ref      | rows |    Extra    |
+----+-------------+-------+--------+--------------------+----------+---------+--------------+------+-------------+
|  1 | SIMPLE      | fl    | ref    | query_id,user_uuid | query_id |       4 | const        | 3718 |             |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY            | PRIMARY  |     160 | fl.user_uuid |    1 | Using index |
+----+-------------+-------+--------+--------------------+----------+---------+--------------+------+-------------+

更多详情:

• 表格 following_lists有大约 25k 行，但只有 3718 行有 fl.following_query_id = 1000010 .

• 表格 users大约有 160k 行，但在连接中只应选择 3718 行。只有 40 条记录同时满足这两个条件 fl.following_query_id = 1000010 AND u.status <= 2 .

• 即使我删除了条件 AND u.status <= 2，查询还是很慢.

Answer 1

“拥有正确的索引”——毫无意义。

如果您正在使用 MyISAM，请不要。相反，切换到 InnoDB。

你需要 following_lists.id 吗？ (following_query_id, user_uuid) 是唯一的吗？如果是这样，请将它们设为 PRIMARY KEY。

如果以上都做不到，那就换

KEY `query_id` (`following_query_id`)

到

INDEX(following_query_id, user_uuid)

UUID 非常低效，尤其是在不必要地声明 utf8mb4 或具有大于必要大小的 CHAR 时。更改为 CHAR(36) CHARACTER SET ascii。 (注意 `EXPLAIN 中的“160”显着缩小。)

更多关于为什么 UUID 不利于性能的信息:http://mysql.rjweb.org/doc.php/uuid

你有多少内存？ innodb_buffer_pool_size 的设置是什么？ (听起来太低了。)

关于索引的更多信息:http://mysql.rjweb.org/doc.php/index_cookbook_mysql