我有一个大型 MySQL、MyISAM 表,大约有 400 万行,在 core 2 duo、8G RAM 笔记本电脑上运行。
该表有 30 列,包括 varchar、decimal 和 int 类型。
我在 varchar(16) 上有一个索引。我们将此列称为:“indexed_varchar_column”。
我的查询是
SELECT 9 columns FROM the_table WHERE indexed_varchar_column = 'something';
对于我查询的每个“东西”,它总是返回大约 5000 行。
查询的 EXPLAIN 返回以下内容:
+----+-------------+-------------+------+----------------------------------------------------+--------------------------------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+----------------------------------------------------+--------------------------------------------+---------+-------+------+-------------+
| 1 | SIMPLE | the_table | ref | many indexes including indexed_varchar_column | another_index NOT: indexed_varchar_column! | 19 | const | 5247 | Using where |
+----+-------------+-------------+------+----------------------------------------------------+--------------------------------------------+---------+-------+------+-------------+
首先,我不确定为什么选择 another_index 。事实上,它选择的索引是 indexed_varchar_column 和另外 2 列(构成所选列的一部分)的复合索引。也许这是有道理的,因为它可能会使事情变得更快一些,因为不必读取查询中的 2 列。真正的问题是下一个:
对于我匹配的每个“某物”,查询需要 5 秒。第二次我查询“某事”时,需要 0.15 秒(我猜是因为查询正在被缓存)。当我对“something_new”运行另一个查询时,又需要 5 秒。所以,它是一致的。
问题是:我发现创建一个索引(另一个复合索引,包括我的 indexed_varchar_column)并再次删除它会导致所有针对新“something_other”的进一步查询只需要 0.15 秒。请注意 1) 我创建了一个索引 2) 再次删除它。所以一切都处于相同的状态。
我猜想构建和删除索引所需的所有操作都会使 SQL 引擎缓存一些内容,然后再重新使用。当我在查询上运行 EXPLAIN 时,我得到的结果与以前完全相同。
如何继续了解创建删除索引过程中缓存的内容,以便我可以在不操作索引的情况下缓存它?
UPDATE:
根据 Marc B 的评论,建议当 mySQL 创建索引时,它会在内部执行 SELECT...我尝试了以下操作:
SELECT * FROM my_table;
花费了 30 秒,返回了 400 万行。好处是所有进一步的查询再次变得非常快(直到我重新启动系统)。请注意,重新启动后查询再次变慢。我猜这是因为 mySQL 正在使用某种操作系统缓存。
任何想法?如何显式缓存我猜测的表?
更新2:也许我应该提到这个表可能严重碎片化。它有 400 万行,但我定期删除许多旧字段。我还添加新的。由于我每天都有很大的 ID 间隙(对于已删除的行),因此我删除主索引 (ID) 并使用连续的数字再次创建它。该表可能非常分散,因此 IO 一定是一个问题...不知道该怎么办。