当前位置: 首页 > 面试题库 >

6000万个条目,请从特定月份选择条目。如何优化数据库?

宰父衡
2023-03-14
问题内容

我有一个拥有6000万个条目的数据库

每个条目包含:

  • ID
  • 数据源ID
  • 一些数据
  • 约会时间
  1. 我需要从特定月份中选择条目。每个月约有200万个条目。
     select *
    

    from Entries
    where time between “2010-04-01 00:00:00” and “2010-05-01 00:00:00”

(查询大约需要1.5分钟)

  1. 我还想从给定的DataSourceID中选择特定月份的数据。(大约需要20秒)

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

有没有办法使它更快?我有什么选择?如何优化此数据库/查询?

编辑: 有大约。每秒钟60-100次插入!


问题答案:

利用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)

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

希望这可以帮助 :)



 类似资料:
  • 问题内容: 这是我一直在绞尽脑汁的一个问题。假设我有一个表,该表具有一系列时间戳和一个零件号作为主键。该表存储增量更改,这意味着对于每个时间戳记,如果字段更改,则记录该更改。如果该字段保持不变,则对于新的时间戳,该字段为NULL。这是基本思想。 是主键。第二条记录中的s表示自第一条记录以来未更改的值。 我想要做的是为按零件分组的每个字段选择最新值。例如,给定以上条目,a5部分的结果将为153,5,

  • 本文向大家介绍从基于特定月份的MySQL表中选择数据?,包括了从基于特定月份的MySQL表中选择数据?的使用技巧和注意事项,需要的朋友参考一下 使用MySQL中的方法选择基于月份的日期。让我们首先创建一个表- 使用插入命令在表中插入一些记录- 使用select语句显示表中的所有记录- 输出结果 这将产生以下输出- 以下是根据特定月份从表中选择数据的查询- 输出结果 这将产生以下输出-

  • 问题内容: 我在尝试从不在另一个表中的表中选择特定条目时在MySQL中遇到问题。我知道这句话听起来很疯狂,但这是我要尝试的一个例子。 表用户: 表文章: 表格视图(如果用户查看了特定的文章,则用于存储数据): 现在,我试图选择那些尚未阅读特定文章的用户,例如ID为10的文章。因此,他们在views表中没有条目。 我希望现在更有意义。感谢您的回答。V. 问题答案:

  • 问题内容: 我们是postgres的新手,我们有以下查询,通过该查询我们可以从每个类别中选择前N个记录。 以上查询的输出是这样的 但是我们的要求有所不同,我们想从每个类别中选择前n%个记录,其中n不固定,n基于每个组中某些元素的百分比。 问题答案: 要基于每个组中行数的百分比来检索行,可以使用两个窗口函数:一个对行进行计数,另一个对行赋予唯一编号。 SQLFiddle示例:http ://sqlf

  • 我有一个名为的雄辩模型,链接到一个名为的数据库表。 是否有一个雄辩的函数可以让我从数据库中提取一个随机问题(或一组随机问题)?如下所示: 或者