请仔细阅读我的答案:(与您的类似)
500万行数据,15万行范围扫描时间为0.02秒。
MySQL和NoSQL:帮助我选择正确的数据库
然后按以下方式修改您的表引擎为InnoDB:
create table tag_date_value
(
tag_id smallint unsigned not null,
tag_date datetime not null,
value int unsigned not null default 0,
primary key (tag_id, tag_date)
)
engine=innodb;
您可能会考虑以下内容作为主键:
primary key (tag_id, tag_date, value)
但仅当值不是一些大型 varchar 类型时才能执行!
与之前相同的查询:
select
tag_date,
value
from
tag_date_value
where
tag_id = 1 and
tag_date between 'x' and 'y'
order by
tag_date;
希望这能帮到你 :)
编辑
哦,忘了提一下 - 不要使用alter table将引擎类型从mysiam改为innodb,而是将数据导出到csv文件中,并重新导入到新创建的空innodb表中。
请注意,在导出过程中我会对数据进行排序 - 聚簇索引是关键!
导出
select * into outfile 'tag_dat_value_001.dat'
fields terminated by '|' optionally enclosed by '"'
lines terminated by '\r\n'
from
tag_date_value
where
tag_id between 1 and 50
order by
tag_id, tag_date;
select * into outfile 'tag_dat_value_002.dat'
fields terminated by '|' optionally enclosed by '"'
lines terminated by '\r\n'
from
tag_date_value
where
tag_id between 51 and 100
order by
tag_id, tag_date;
导入
将数据按照正确的顺序导入回表格中!
start transaction;
load data infile 'tag_dat_value_001.dat'
into table tag_date_value
fields terminated by '|' optionally enclosed by '"'
lines terminated by '\r\n'
(
tag_id,
tag_date,
value
);
commit;
ORDER BY
时,是否有所帮助?您能否发布包括和不包括ORDER BY
的查询实际时间? - S.Lott