如何改进已经优化的查询，它需要18秒钟才能完成？

Question

所以我有一台512MB RAM的VPS，还有一个像这样的MySQL表：

CREATE TABLE `table1` (
  `id` int(20) unsigned NOT NULL auto_increment,
  `ts` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `value1` char(31) collate utf8_unicode_ci default NULL,
  `value2` varchar(100) collate utf8_unicode_ci default NULL,
  `value3` varchar(100) collate utf8_unicode_ci default NULL,
  `value4` mediumtext collate utf8_unicode_ci,
  `type` varchar(30) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `type` (`type`),
  KEY `date` (`ts`)
) ENGINE=MyISAM AUTO_INCREMENT=469692 DEFAULT CHARSET=utf8
  COLLATE=utf8_unicode_ci

如果我执行这样的查询，需要2~18秒才能完成：

SELECT `id`, `ts`, `value1`, `value2`, `value3` FROM table1 WHERE
`type` = 'something' ORDER BY `id` DESC limit 0,10; 

“EXPLAIN SELECT”指令告诉我：

  select_type: SIMPLE
         type: ref
possible_keys: type
          key: type
      key_len: 92
          ref: const
         rows: 7291
        Extra: Using where; Using filesort

我以为“使用文件排序”可能是问题所在，但事实证明并非如此。如果我删除ORDER BY和LIMIT，查询速度是相同的（我在测试中关闭了查询缓存SET @@query_cache_type=0;）。

mysql> EXPLAIN SELECT `id`,`ts`,`value1`,`value2`, `value3` 
       FROM table1 WHERE `type` = 'something'\G

  select_type: SIMPLE
         type: ref
possible_keys: type
          key: type
      key_len: 92
          ref: const
         rows: 7291
        Extra: Using where

不知道这是否重要，但行的逼近是不准确的：

SELECT COUNT(*) FROM table1 WHERE `type` = 'something';

返回22.8k行。查询似乎已经优化过了，我不知道如何进一步改进它。整个表包含370k行，大小约为4.6 GiB。可能是因为每行的类型随机变化（在整个表中随机分布），所以获取数据需要2~18秒才能从磁盘读取？有趣的是，当我使用只有几百行的类型时，这些查询也很慢。MySQL返回的行数大约是100行/秒！

|-------+------+-----------|
| count | time |   row/sec |
|-------+------+-----------|
| 22802 | 18.7 | 1219.3583 |
|    11 |  0.1 |      110. |
|   491 |  4.8 | 102.29167 |
|   705 |  5.6 | 125.89286 |
|   317 |  2.6 | 121.92308 |
|-------+------+-----------|

为什么它运行得这么慢？我能进一步优化查询吗？我应该将数据移动到更小的表中吗？

我认为自动分区是一个好主意，可以为每个类型动态创建一个新分区。但由于许多原因，包括最大分区数为1024，并且可能有任何类型，这是不可能的。我还可以尝试应用级分区，为每个新类型创建一个新表。我不想这样做，因为它会引入很大的复杂性。我不知道如何在所有表中为所有行设置唯一ID。此外，如果我达到每秒多次插入，性能会显著下降。

提前致谢。

- atlau

---

如果只选择索引列，例如“select id from...”，会发生什么？此外，表是否适合RAM，还是需要从磁盘中获取数千行？是什么类型的磁盘？在这种情况下，通常的答案是：确保您的磁盘速度快，并且所有表都适合RAM，这样您的磁盘就不重要了 :)（假设查询是一个简单的选择，并且索引已经正确设置） - Anders Forsgren

你是在哪里执行 select 语句的？你是直接在服务器上执行，还是通过网络/内网/VPN/互联网执行的？ - Joel C

找到一台内存充足的合适VPS可能比花费工程师小时数来解决这个问题更便宜（即使您解决了它，使用512 RAM，下个月您仍将面临另一个问题）。 - Karoly Horvath

我使用SSH登录MySQL命令行并执行查询。 - atlau

你真的有一个长度为31个字符的固定字符串吗？这将占用每行93个字节。将其更改为ASCII以获得固定的31个字节，或者使用VARCHAR（31）。 - Rick James

Answer 1

您需要为该查询创建一个多列索引：

KEY `typeid` (`type`, `id`)

很抱歉，如你所说，没有ORDER时也很慢，因为记录在磁盘上分散，需要进行大量的查找。一旦缓存，速度应该会很快（注意：22.8/370 * 4.6G = 283M，如果您进行其他活动/查询，则这些记录不会长时间存在于内存中，甚至可能不适合放置）。

执行以验证I/O瓶颈。大量的RAM可以解决您的问题。SSD也可以解决您的问题。但是RAM更便宜 ;)

- Karoly Horvath

---

为什么/如何会有帮助？海报报告称，即使删除 ORDER BY，速度仍然相同。 - user166390

我已经在我的开发机上尝试了多列索引，但没有发现任何速度提升（尽管我没有在服务器上尝试）。是的，问题可能是因为磁盘寻道，正如我在问题中所说的那样。 - atlau

在512MB中缓存370k个这样大小的记录？;) 不，先生，今天不行。 - user166390

@pst：你只需要在内存中保留工作集，我不知道该数据库的使用模式。但是，512M对于一个数据库服务器来说是荒谬的。 - Karoly Horvath

Answer 2

有很多方法可以改进查询。在您的情况下，我看到您的索引必须是相当巨大的，因为索引的Unicode VARCHAR（30）列负责key_len：92。以下是您可以尝试的内容：用更小的东西替换大的VARCHAR索引。保留type列，但删除索引并创建一个新的索引列typeidx，您可以将其创建为INT UNSIGNED（如果可能，则为SMALLINT）。

创建类似于此的表：

CREATE TABLE `typetable` (
  `typeidx` INT UNSIGNED NOT NULL auto_increment,
  `type` varchar(30) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`typeidx`),
  UNIQUE KEY `type` (`type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

你可以使用现有的类型来填充它

INSERT INTO typetable (type) SELECT DISTINCT type FROM table1;

然后你需要使用类似的方式更新table1.typeidx。

UPDATE table1 t1 JOIN typetable tt USING (type)
   SET t1.typeidx = tt.typeidx

现在你的旧查询可以变成这样

SELECT `id`,`ts`,`value1`,`value2`, `value3` 
   FROM table1 WHERE `typeidx` = (SELECT typeidx FROM typetable WHERE type = 'something')

当然，您还需要维护typetable并在创建新值时从type中插入它们。

- Josh Davis

---

这样做的唯一目的是将密钥长度从92减少？ - atlau

是的。对于 INT，密钥长度将为4，对于 MEDIUMINT，密钥长度将为3，对于 SMALLINT，密钥长度将为2。或者，您可以查看 “ENUM”类型，这不需要更改您的 SELECT 查询，但每次插入 type 的新值时都需要更新 ENUM。它还限制了65535个可能的值。 - Josh Davis

此外，如果“type”值仅由ASCII字符组成，则应将其字符集更改为ASCII，以便字符编码为1个字节而不是3个或更多。 - Josh Davis

Josh：你回答让我感到好奇的是查询（没有连接）。你今天过得不好吗？ - Micromega

很抱歉，我不知道你在说什么，如果你对查询有具体的问题，我很乐意消除任何误解。顺便问一下，你尝试过这个解决方案吗？ - Josh Davis

Answer 3

如果你非常渴望优化，可以尝试重新排列你的表格。首先，你要从一个类型中选择并按顺序排列每一行，然后将其重写到一个新表格中，并一个接一个地添加其他类型到该表格中。我建议进行一种表格碎片整理，但我没有任何经验。

- Micromega

---

这是MyISAM，因此任何DDL都将创建表的副本，而简单的ALTER TABLE table1 ORDER BY type也应执行相同操作。当然，如果您的磁盘没有足够的连续空闲空间，新表仍可能是碎片化的。 - Josh Davis

DDL的定义是什么？ - Micromega

Answer 4

我没有比实现垂直分区更好的想法。我创建了一个没有mediumtext列的完全相同的表格，复制了整个不包含该列的表格，现在18秒的查询只需要100毫秒！新表只有55mb。

- atlau