mysql - 如何改进耗时 18 秒的已优化查询？

Question

所以我有一个 512mb 内存的 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。另外，如果我到达多次插入/秒，性能会显着下降。

提前致谢。

Answer 1

该查询需要一个多列索引:

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

不幸的是，正如您所说，它在没有 ORDER 的情况下也很慢所以它很慢是因为记录分散在磁盘上并且它必须进行大量查找。缓存后，它应该非常快(注意:22.8/370 * 4.6G = 283M，因此如果您进行其他事件/查询，这些记录将不会在内存中保存很长时间，甚至可能不适合。)。

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