利用InnoDB的聚簇主键索引可以提高性能。请参考
http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html。这将极大地提高性能。
create table datasources
(
year_id smallint unsigned not null,
month_id tinyint unsigned not null,
datasource_id tinyint unsigned not null,
id int unsigned not null,
data int unsigned not null default 0,
primary key (year_id, month_id, datasource_id, id)
)
engine=innodb;
select * from datasources where year_id = 2011 and month_id between 1 and 3;
select * from datasources where year_id = 2011 and month_id = 4 and datasouce_id = 100;
编辑2
忘记了我使用的是包含3个月数据的第一个测试脚本。这是单个月份的结果:0.34和0.69秒。
select d.* from datasources d where d.year_id = 2010 and d.month_id = 3 and datasource_id = 100 order by d.id desc limit 10;
+
| year_id | month_id | datasource_id | id | data |
+
| 2010 | 3 | 100 | 3290330 | 38434 |
| 2010 | 3 | 100 | 3290329 | 9988 |
| 2010 | 3 | 100 | 3290328 | 25680 |
| 2010 | 3 | 100 | 3290327 | 17627 |
| 2010 | 3 | 100 | 3290326 | 64508 |
| 2010 | 3 | 100 | 3290325 | 14257 |
| 2010 | 3 | 100 | 3290324 | 45950 |
| 2010 | 3 | 100 | 3290323 | 49986 |
| 2010 | 3 | 100 | 3290322 | 2459 |
| 2010 | 3 | 100 | 3290321 | 52971 |
+
10 rows in set (0.34 sec)
select d.* from datasources d where d.year_id = 2010 and d.month_id = 3 order by d.id desc limit 10;
+
| year_id | month_id | datasource_id | id | data |
+
| 2010 | 3 | 116 | 3450346 | 42455 |
| 2010 | 3 | 116 | 3450345 | 64039 |
| 2010 | 3 | 116 | 3450344 | 27046 |
| 2010 | 3 | 116 | 3450343 | 23730 |
| 2010 | 3 | 116 | 3450342 | 52380 |
| 2010 | 3 | 116 | 3450341 | 35700 |
| 2010 | 3 | 116 | 3450340 | 20195 |
| 2010 | 3 | 116 | 3450339 | 21758 |
| 2010 | 3 | 116 | 3450338 | 51378 |
| 2010 | 3 | 116 | 3450337 | 34687 |
+
10 rows in set (0.69 sec)
EDIT 1
决定使用上述模式测试大约分布在3年内的6000万行数据。每次查询都是“冷启动”,即每次单独运行,之后重启mysql以清除任何缓存并不进行查询缓存。
完整的测试脚本可以在此处找到:http://pastie.org/1723506或下面的代码:
正如您所看到的,这个模式在我的简陋台式电脑上表现非常出色:)
select count(*) from datasources;
+
| count(*) |
+
| 60306030 |
+
select count(*) from datasources where year_id = 2010;
+
| count(*) |
+
| 16691669 |
+
select
year_id, month_id, count(*) as counter
from
datasources
where
year_id = 2010
group by
year_id, month_id;
+
| year_id | month_id | counter |
+
| 2010 | 1 | 1080108 |
| 2010 | 2 | 1210121 |
| 2010 | 3 | 1160116 |
| 2010 | 4 | 1300130 |
| 2010 | 5 | 1860186 |
| 2010 | 6 | 1220122 |
| 2010 | 7 | 1250125 |
| 2010 | 8 | 1460146 |
| 2010 | 9 | 1730173 |
| 2010 | 10 | 1490149 |
| 2010 | 11 | 1570157 |
| 2010 | 12 | 1360136 |
+
12 rows in set (5.92 sec)
select
count(*) as counter
from
datasources d
where
d.year_id = 2010 and d.month_id between 1 and 3 and datasource_id = 100;
+
| counter |
+
| 30003 |
+
1 row in set (1.04 sec)
explain
select
d.*
from
datasources d
where
d.year_id = 2010 and d.month_id between 1 and 3 and datasource_id = 100
order by
d.id desc limit 10;
+
| id | select_type | table | type | possible_keys | key | key_len | ref |rows | Extra |
+
| 1 | SIMPLE | d | range | PRIMARY | PRIMARY | 4 | NULL |4451372 | Using where; Using filesort |
+
1 row in set (0.00 sec)
select
d.*
from
datasources d
where
d.year_id = 2010 and d.month_id between 1 and 3 and datasource_id = 100
order by
d.id desc limit 10;
+
| year_id | month_id | datasource_id | id | data |
+
| 2010 | 3 | 100 | 3290330 | 38434 |
| 2010 | 3 | 100 | 3290329 | 9988 |
| 2010 | 3 | 100 | 3290328 | 25680 |
| 2010 | 3 | 100 | 3290327 | 17627 |
| 2010 | 3 | 100 | 3290326 | 64508 |
| 2010 | 3 | 100 | 3290325 | 14257 |
| 2010 | 3 | 100 | 3290324 | 45950 |
| 2010 | 3 | 100 | 3290323 | 49986 |
| 2010 | 3 | 100 | 3290322 | 2459 |
| 2010 | 3 | 100 | 3290321 | 52971 |
+
10 rows in set (0.98 sec)
select
count(*) as counter
from
datasources d
where
d.year_id = 2010 and d.month_id between 1 and 3;
+
| counter |
+
| 3450345 |
+
1 row in set (1.64 sec)
explain
select
d.*
from
datasources d
where
d.year_id = 2010 and d.month_id between 1 and 3
order by
d.id desc limit 10;
+
| id | select_type | table | type | possible_keys | key | key_len | ref |rows | Extra |
+
| 1 | SIMPLE | d | range | PRIMARY | PRIMARY | 3 | NULL |6566916 | Using where; Using filesort |
+
1 row in set (0.00 sec)
select
d.*
from
datasources d
where
d.year_id = 2010 and d.month_id between 1 and 3
order by
d.id desc limit 10;
+
| year_id | month_id | datasource_id | id | data |
+
| 2010 | 3 | 116 | 3450346 | 42455 |
| 2010 | 3 | 116 | 3450345 | 64039 |
| 2010 | 3 | 116 | 3450344 | 27046 |
| 2010 | 3 | 116 | 3450343 | 23730 |
| 2010 | 3 | 116 | 3450342 | 52380 |
| 2010 | 3 | 116 | 3450341 | 35700 |
| 2010 | 3 | 116 | 3450340 | 20195 |
| 2010 | 3 | 116 | 3450339 | 21758 |
| 2010 | 3 | 116 | 3450338 | 51378 |
| 2010 | 3 | 116 | 3450337 | 34687 |
+
10 rows in set (1.98 sec)
希望这能帮到您 :)