MySQL索引仅在简单查询中略微提高速度

Question

我有一个包含大约8000万行的表格。

CREATE TABLE `mytable` (
`date` date NOT NULL,
`parameters` mediumint(8) unsigned NOT NULL,
`num` tinyint(3) unsigned NOT NULL,
`val1` int(11) NOT NULL,
`val2` int(10) NOT NULL,
`active` tinyint(3) unsigned NOT NULL,
`ref` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`ref`) USING BTREE,
KEY `parameters` (`parameters`)
) ENGINE=MyISAM AUTO_INCREMENT=79092001 DEFAULT CHARSET=latin1

这个内容是关于it技术的，它主要围绕着两个主要列："参数"和"日期"。 "参数"有大约67,000个可能的值。 对于每个"参数"，有大约1200行，每一行都有不同的日期。 因此，对于每个日期，有67,000行。 1200 * 67,000 = 80,400,000。

表格大小为1.5GB，索引大小为1.4GB。

现在，我想查询该表以检索一个"参数"的所有行（实际上我想为每个参数都执行此操作，但这是一个好的开始）。

SELECT val1 FROM mytable WHERE parameters=1;

第一次运行需要8秒钟的结果

对于不同但接近参数值（2、3、4 ...）的后续运行是瞬间的。

对于“远离”的值（参数=1000）的运行再次在8秒钟内给出结果。

我进行了没有索引的相同查询的测试，并在20秒内获得结果，因此我认为索引正在EXPLAIN中显示，但没有给出性能的显着跃升：

+----+-------------+----------+------+---------------+------------+---------+-------+------+-------+
| id | select_type | table    | type | possible_keys | key        | key_len | ref   | rows | Extra |
+----+-------------+----------+------+---------------+------------+---------+-------+------+-------+
|  1 | SIMPLE      | mytable  | ref  | parameters    | parameters | 3       | const | 1097 |       |
+----+-------------+----------+------+---------------+------------+---------+-------+------+-------+

但我仍然为如此简单的请求（无需加入，直接在索引上）所花费的时间感到困惑。

服务器是一台两年老的Ubuntu系统的2个CPU四核2.6GHz服务器，具有4G的RAM。 我已经将key_buffer参数提高到1G，并重新启动了mysql，但没有看到任何改变。

我应该认为这是正常现象吗？ 还是我做错了什么？ 我觉得通过正确的配置，请求应该几乎是立即响应的。

- Bastien

---

它可能正在从磁盘读取。检查操作系统的IO统计信息。 - Karoly Horvath

是的，它似乎正在从磁盘读取，尽管它说它正在使用索引。我怎样才能确保它确实使用索引而不是去磁盘？ - Bastien

我应该将key_buffer参数简单地提高到超过索引的大小吗？还是超过表和索引大小之和？还有其他要考虑的配置参数吗？ - Bastien

这完全取决于……您只关心这个表/查询吗？有许多要考虑的事情，例如：MySQL分区。 - Karoly Horvath

尝试使用 pack_keys=1 以在内存中拥有更大的索引部分。 - pascal

Answer 1

尝试使用覆盖索引，即创建一个包含您需要的两个列的索引。它不需要第二个磁盘I/O从主表中提取值，因为数据在索引中就在那里。

- David Pope

---

我实际上需要超过2列，不认为我应该创建一个6列的索引！ - Bastien