logo标签列表

6000万条记录，选择某个月的记录。如何优化数据库？

mysqlsqlquery-optimization
9

我有一个包含6千万条目的数据库。

每个条目都包含：

  • ID
  • 数据源ID
  • 一些数据
  • 日期时间

  1. I need to select entries from certain month. Each month contains approximately 2 million entries.

     select * 
   from Entries 
  where time between "2010-04-01 00:00:00" and "2010-05-01 00:00:00"

    (query takes approximately 1.5 minutes)

  2. I'd also like to select data from certain month from a given DataSourceID. (takes approximately 20 seconds)

大约有50-100个不同的DataSourceIDs。

有没有办法让这个过程更快？我的选择是什么？ 如何优化这个数据库/查询？

编辑：每秒大约有60-100个插入操作！

我目前没有索引。表格只是按ID排序的。 - JBeurer
4个回答
7
要更快地获取特定年份和月份的条目，您需要对time列进行索引。具体操作请参考此文档。
CREATE INDEX idx_time ON ENTRIES(time) USING BTREE;

此外，还需要使用：
SELECT e.* 
  FROM ENTRIES e
 WHERE e.time BETWEEN '2010-04-01' AND DATE_SUB('2010-05-01' INTERVAL 1 SECOND)

因为BETWEEN是包含的，所以使用您发布的查询将获得任何日期为“2010-05-01 00:00:00”的数据。

我还想从给定的DataSourceID中选择特定月份的数据

您可以为datasourceid列添加单独的索引：

CREATE INDEX idx_time ON ENTRIES(datasourceid) USING BTREE;

...或者设置一个覆盖索引以包括这两列：

CREATE INDEX idx_time ON ENTRIES(time, datasourceid) USING BTREE;

一个覆盖索引需要使用查询中左边的列才能使用。在这个例子中，将 time 放在前面对于你提到的两种情况都可行 -- 不需要使用 datasourceid 就可以使用索引。但是，你必须通过查看 EXPLAIN 输出来测试你的查询，以了解什么最适合你的数据和正在执行的查询。

话虽如此，索引会减缓 INSERT、UPDATE 和 DELETE 语句的速度。如果列数据具有很少不同的值（即基数低），那么索引就没有多大价值 -- 例如，布尔列是索引的一个不好的选择。

一个请求2百万行的查询通常会倾向于使用表扫描而不是索引范围查找，所以我认为MySQL不会使用索引(time)。两百万个表查找的代价太高了。这个索引必须是覆盖索引。 - Andomar
@Andomar：没错，但我喜欢提供选项和工具来审查，这样人们就能学到东西。我们也见过有人隐藏细节。 - OMG Ponies
在ENTRIES表上添加时间和数据源ID索引会显著减慢添加操作吗？因为这种操作非常频繁。 - JBeurer
正如最后一段所述，是的。所有事情都有取舍，你必须决定什么更重要，并可能重新构建以更好地支持两者。 - OMG Ponies
哇，为时间添加索引将查询时间从1.5分钟降至3秒钟。现在只希望这不会显著减缓插入速度。 - JBeurer
6
利用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, -- needed for uniqueness
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;

-- etc..

编辑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)

希望这能帮到您 :)
1
这会影响插入操作的性能吗？因为在有60百万条记录的数据库中，每秒钟100次插入，这可能会成为一个问题，不是吗？ - JBeurer
插入不应该成为问题，因为您可能是按照聚集PK顺序进行插入的，这对于InnoDB来说是高效的 :P - Jon Black
1
如果行没有被更新或删除，那么数据是否会自然地围绕着他的“时间”列聚集，因为按照时间顺序插入行会产生这样的副作用，如果你知道我的意思的话？分配块的基本机制将决定这一点，但我不知道MySQL是如何做到的。你知道它是如何工作的吗？我的意思是，使用具有自动递增主键的InnoDB表可能提供相同的聚集效益，但没有肥大的键。 - Ronnis
如果他正在使用MyISAM并按时间顺序插入，则新数据将附加到文件末尾。但是，MyISAM不支持聚簇主键，这意味着与等效的InnoDB模式相比需要更多的I/O操作。 - Jon Black
3秒 vs 0.36秒 - 在我看来毫无疑问。 - Jon Black
显示剩余2条评论
2

您可以使用索引来以查询速度为代价换取磁盘使用量。从 time 列开始的索引可以加速对特定月份查询的速度：

create index IX_YourTable_Date on YourTable (time, DataSourceID, ID, SomeData)

由于索引从time字段开始，MySQL可以在索引上进行关键范围扫描。这应该是最快的方式。索引应包括查询中的所有列，否则MySQL将不得不为每一行从索引到表数据进行查找。由于您要查询200万行，MySQL很可能会忽略未覆盖的索引。（覆盖索引=包含查询中所有行的索引。）
如果您从不查询ID，则可以重新定义表以使用(time, DataSourceID, ID)作为主键：
alter table YourTable add primary key (time, DataSourceID, ID)

这将加速对time的搜索，而不会占用磁盘空间，但对ID的搜索将非常缓慢。

1

如果你还没有在时间字段上建立索引，我建议你尝试一下。

对于DataSourceID，你可以尝试使用枚举而不是varchar/int。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接