mysql - 临时表(Memory Engine)和大物理表(1.7GB myisam)的join优化

Question

我在查询大型物理表(1.7GB，2300 万行)时遇到优化问题，我们称它为 p_table。

我需要使用 p_table 的主索引来选择数千行。我的第一次尝试是使用主索引进行 IN 查询，例如

SELECT * FROM p_table WHERE primary_key IN (111,222,333,[... 60.000 more]).

由于查询非常慢(50-60 秒)，我决定通过将所有主键添加到带有内存引擎的临时表中来优化它，然后按如下方式连接

CREATE TEMPORARY TABLE tmp_table (primary_key BIGINT(20) NOT NULL PRIMARY KEY) ENGINE=Memory;
INSERT IGNORE INTO t_table VALUES(111),(222),(333),[thousandsmore];
SELECT p.* FROM p_table AS p FORCE INDEX(PRIMARY) INNER JOIN tmp_table AS t  FORCE INDEX(PRIMARY)  ON t.primary_key = g.primary_key;

该解决方案加快了 x4 的查询速度，但仍然会导致服务器负载很大，每次查询大约需要 10-20 秒(取决于临时表的大小)。

查询的 EXPLAIN 显示它没有使用索引，即使我强制使用它们也是如此。

  ["id"]=>
  string(1) "1"
  ["select_type"]=>
  string(6) "SIMPLE"
  ["table"]=>
  string(1) "t"
  ["type"]=>
  string(3) "ALL"
  ["possible_keys"]=>
  string(7) "PRIMARY"
  ["key"]=>
  NULL
  ["key_len"]=>
  NULL
  ["ref"]=>
  NULL
  ["rows"]=>
  string(4) "64320"
  ["Extra"]=>
  string(0) ""

不幸的是，整个数据库超过 50GB，这意味着我买不起一个完整的内存数据库，而且像 p_table 这样的大表依赖于磁盘 I/O。

您对如何优化流程有什么建议吗？还有关于为什么索引没有被使用(或者更可能没有被 EXPLAIN 显示)的任何提示？

服务器信息:Debian 8.6MySQL 5.5.538GB内存Raid0 中的 SSD 磁盘(它是从属之一，Master 上的 Raid10)

非常感谢

Answer 1

为什么不使用索引

mysql 之所以不在表 t 上使用索引，是因为那是完全没有必要的。您的临时表(别名 t)没有过滤器，因此您将临时表中的每一行都与 p 连接起来。在这里使用索引没有任何好处。

来自手册:https://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html

MySQL uses indexes for these operations:

To find the rows matching a WHERE clause quickly.

To eliminate rows from consideration. If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows (the most selective index).

它不符合任何主要标准或上面列出的某些其他标准。但是，您的主键是覆盖索引，因此 mysql 确实可以选择使用索引而不是引用行。然而，这并没有带来任何特别的优势。因此决定不使用索引。

我实际上建议您删除临时表上的索引。这甚至可能会加快速度，因为将 60,000 行插入该表会更快。

你可以尝试的事情

查看是否可以对数据进行分区。例如，您要插入到临时表中的 ID 是否存在某种模式？看看您是否可以根据该模式进行分区

增加服务器上的内存。

使用更快的硬盘驱动器或跨多个硬盘驱动器 strip 化数据。

切换到可以在多个 CPU 内核上并行执行相同查询的 RDBMS。