所以我有一台512MB RAM的VPS,还有一个像这样的MySQL表:
返回22.8k行。查询似乎已经优化过了,我不知道如何进一步改进它。整个表包含370k行,大小约为4.6 GiB。可能是因为每行的类型随机变化(在整个表中随机分布),所以获取数据需要2~18秒才能从磁盘读取?有趣的是,当我使用只有几百行的类型时,这些查询也很慢。MySQL返回的行数大约是100行/秒!
为什么它运行得这么慢?我能进一步优化查询吗?我应该将数据移动到更小的表中吗?
我认为自动分区是一个好主意,可以为每个类型动态创建一个新分区。但由于许多原因,包括最大分区数为1024,并且可能有任何类型,这是不可能的。我还可以尝试应用级分区,为每个新类型创建一个新表。我不想这样做,因为它会引入很大的复杂性。我不知道如何在所有表中为所有行设置唯一ID。此外,如果我达到每秒多次插入,性能会显著下降。
提前致谢。
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。此外,如果我达到每秒多次插入,性能会显著下降。
提前致谢。
select
语句的?你是直接在服务器上执行,还是通过网络/内网/VPN/互联网执行的? - Joel C