尽管覆盖索引，MySQL MyISAM慢count（）查询

Question

我正在拔头发试图找出我做错了什么。该表非常简单：

CREATE TABLE `icd_index` (
  `icd` char(5) NOT NULL,
  `core_id` int(11) NOT NULL,
  `dx_order` tinyint(4) NOT NULL,
  PRIMARY KEY (`icd`,`dx_order`,`core_id`),
  KEY `core` (`core_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

如您所见，我创建了一个覆盖该表的所有三列的覆盖索引，并在上添加了一个core_id潜在索引的附加索引。这是一对多链接表，每个链接表都core_id映射到一个或多个链接表icd。该表包含6500万行。

所以，这就是问题所在。假设我想知道有多少人的icd代码为“ 25000”。[如果您想知道的话，那就是糖尿病]。我写了一个查询，看起来像这样：

SELECT COUNT(core_id) FROM icd_index WHERE icd='25000'

这需要60秒钟以上的时间才能执行。我曾以为，由于icd列在涵盖索引中排在第一位，因此算起来会很快。

更令人困惑的是，一旦我运行了一次查询，它现在就会非常快速地运行。我以为那是因为查询被缓存了，但是即使I RESET QUERY CACHE，查询现在也可以在几分之一秒内运行。但是，如果我等待足够长的时间，它似乎又会变慢-而且我不知道为什么。

我缺少明显的东西。我是否需要一个索引icd？这是6500万行所能达到的最佳性能吗？为什么运行查询然后重置缓存会影响速度？结果是否存储在索引中？

编辑：我正在运行MySQL 5.6（以防万一）。

这是EXPLAIN查询的：

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  icd_index   ref PRIMARY PRIMARY 15  const   910104  Using where; Using index

Answer 1

这是怎么回事。

The SELECT COUNT (...) icd_index where icd='25000'

将使用索引，该索引是与数据分开的BTree。但是它以这种方式扫描：

1. 查找具有icd =‘25000’的第一个条目。这几乎是瞬时的。
2. 向前扫描，直到找到icd的变化。这将仅扫描索引，而不接触数据。根据EXPLAIN，将有大约910,104个索引条目要扫描。

现在，让我们看一下该索引的BTree。根据索引中的字段，每行将恰好是22个字节，加上一些开销（估计为40％）。MyISAM索引块为1KB（参见InnoDB的16KB）。我估计每个块有33行。910,104
/
33说，要读取COUNT，需要读取大约27K块。（注意COUNT(core_id)需要检查是否core_id为空，COUNT(*)不是这样；这是一个很小的差异。）在普通硬盘驱动器上读取27K块大约需要270秒。您很幸运能在60秒内完成。

第二次运行在key_buffer中找到了所有这些块（假设key_buffer_size至少为27MB），因此它不必等待磁盘。因此，它要快得多。（这将忽略查询缓存，您可以明智地刷新或使用SQL_NO_CACHE。）

5.6恰好无关紧要（但是感谢您提及它），因为此过程自4.0或更低版本以来没有更改（除了utf8不存在；更多内容请参见下文）。

切换到InnoDB可以通过两种方式提供帮助。主键将与数据“聚集”在一起，而不是存储为单独的BTree。因此，一旦数据或PK被缓存，另一个立即可用。块的数量将更像是5K，但它们将是16KB块。如果高速缓存很冷，这些文件可能会更快地加载。

您问“我是否需要一个单独的icd索引？”-那么这会使MyISAM
BTree的大小缩小到每行约21个字节，因此BTree的大小约为21/27倍，没有太大的改进（至少对于冷缓存情况）。

另一个想法是， 如果 icd始终是数字且始终是数字，则使用MEDIUMINT UNSIGNED，ZEROFILL如果它可以有前导零，则继续使用。

糟糕，我没注意到字符集。（我已经固定了上面的数字，但让我详细说明。）

• CHAR（5）允许5个 字符 。
• ascii每个 字符 占用1 个字节 。 __
• utf8每个 字符 最多占用3 个字节 。 __
• 因此，CHAR（5）CHARACTER SET utf8 始终 占用15 个字节 。 __

将列更改为CHAR(5) CHARACTER SET ascii会将其缩小到5个字节。

将其更改为MEDIUMINT UNSIGNED ZEROFILL会将其缩小为3个字节。

缩小数据将使I / O速度大致成比例（在另外两个字段中再允许6个字节之后）。