optimization - 在 MySQL 中高效查询 15,000,000 行表

Question

考虑以下数据库表:

• 包含 13,000,000 行的“消息”表(每条消息一行)。
• 包含 3,000,000 行(每个用户一行)的“用户”表。

以下查询用于获取一堆消息和对应的用户:

SELECT messages.id, messages.message, users.id, users.username
FROM messages
INNER JOIN users ON messages.user_id=users.id 
WHERE messages.id in (?, ?, ?, ? ... a total of 100 "?":s);

在每个查询中获取 100 条消息。

“消息”在 id(主键，BIGINT 不是自动生成)和 user_id 上编入索引。

"users"以 id 为索引(主键，INT 自动生成)。

数据库是使用 MyISAM 的 MySQL。

目前，查询的执行时间远远超过 3000 毫秒，这让我感到困惑，因为“消息”是在“id”上索引的，因此检索正确的行应该非常快。

我的问题是:考虑到描述场景和设置，3000 毫秒的查询时间是“正常的”还是我遗漏了什么？如果需要更多详细信息，请告诉我。

更新 #1: 以下是表定义:

CREATE TABLE messages (
  id bigint(20) NOT NULL DEFAULT '0',
  user_id int(11) NOT NULL DEFAULT '0',
  message varchar(160) NOT NULL DEFAULT '',
  PRIMARY KEY (id),
  KEY user_id (user_id),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE users (
  id int(11) NOT NULL DEFAULT '0',
  username varchar(32) NOT NULL DEFAULT '',
  PRIMARY KEY (id),
  UNIQUE KEY username (username),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

我在定义中观察到的唯一“非标准”内容是“messages.id”是 BIGINT 而不是 INT。这可能是一个提示吗？

Answer 1

我处理过包含数十亿行的 MyISAM 表，在某些行数限制之后我发现的一件事是优化器花了太长时间来决定如何处理查询，并且错误地执行了一些表扫描。我找不到描述它的确切页面，但我开始总是在我知道它应该如何请求对象的每个查询段上使用 FORCE_INDEX

http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

事实上，如果您使用的是这么大的表，则需要设计每个查询以使用您的索引，因此强制索引没有任何问题。如果必须，它仍然会扫描表，但 FORCE_INDEX 告诉它不要这样做，除非它绝对必须这样做。

另外，如果您的表很大，我假设您的索引也很大。你绝对需要确保你有正确的配置设置，并且你的 key_buffer 有足够的大小并且你有足够的 i/o。如果您正在运行 32 位 mysql(您不应该这样做)，则将您的 key_buffer 设置为 1GB(假设您有 1GB 可用)并使用“mysqlreport”检查其使用情况

如果您正在运行 64 位 mysql，请选择使其尽可能大，同时仍为操作系统留出空间来缓存文件和您正在运行的任何其他应用程序，因此如果可以的话，可能需要几 GB。

即使您的查询使用索引，如果索引无法在内存中正确缓冲，您仍然会访问磁盘，并且性能损失与索引大小和磁盘/可用 i/o 的速度成正比。

就 int 与 big int 而言，我看到的唯一明显的性能差异是在大 int 上执行计算，例如 SUM。 SUM 在 big int 上比在 int 上要慢得多，以至于我会考虑以不同的数量级存储数字，或者如果您需要对它们执行频繁的计算，则将它们分成两个 int。