首页 \ 问答 \ 数据库设计 - 如何构建(Database design - how to structure)

数据库设计 - 如何构建(Database design - how to structure)

目前,我有一个表,它的填充速度非常快。 我有50个设备。 我每隔30秒从每台设备收集数据。 因此,在我们添加10,000个设备后,它们每月将生成876,000,000条记录 - 这是很多!

INSERT INTO unit_data
(`id`,`dt`,`id_unit`,`data1`,`data2`,
`ip`,`unique_id`,`loc_age`,`reason_code`,
`data3`,`data4`,`Odo`,`event_time_gmt_unix`,
`switches`,`on_off`,`data5`)

这是我的关系

  PRIMARY KEY (`id`),
  UNIQUE KEY `id_unit_data_UNIQUE` `id`),
  KEY `fk_gp2` (`id_unit`),
  KEY `unit_dt_id` (`dt`,`id_unit`),
  KEY `unit_id_dt` (`id_unit`,`dt`),
  CONSTRAINT `fk_gp2` FOREIGN KEY (`id_unit`) REFERENCES `unit` (`id_unit`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1049392 DEFAULT CHARSET=utf8$$

我面临着非常复杂的查询和报告,当我这样做时,我们的系统没有响应并且达到执行超时。 (这是2mil +记录)

我需要重新思考并重新实现数据库结构。 目前我正在考虑其中任何一个

  • 为每个单元创建新表
  • 为每个月的每个单元创建新表

你会建议什么?


Currently, I have one table, and it is getting populated very fast. I have 50 devices. I gather data from each device every 30 seconds. Therefore, after we add 10,000 devices, they would generate 876,000,000 records per month-- which is a lot!

INSERT INTO unit_data
(`id`,`dt`,`id_unit`,`data1`,`data2`,
`ip`,`unique_id`,`loc_age`,`reason_code`,
`data3`,`data4`,`Odo`,`event_time_gmt_unix`,
`switches`,`on_off`,`data5`)

here are my relationships

  PRIMARY KEY (`id`),
  UNIQUE KEY `id_unit_data_UNIQUE` `id`),
  KEY `fk_gp2` (`id_unit`),
  KEY `unit_dt_id` (`dt`,`id_unit`),
  KEY `unit_id_dt` (`id_unit`,`dt`),
  CONSTRAINT `fk_gp2` FOREIGN KEY (`id_unit`) REFERENCES `unit` (`id_unit`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1049392 DEFAULT CHARSET=utf8$$

I am facing pretty complex queries and reports, and when I do them, our system is not responding and hitting execution timeout. (this is with 2mil+ records)

I need to rethink and re-implement the database structure. And currently I am thinking about either

  • Create new table for each unit
  • Create new table for each unit for each month

What would you suggest?


原文:https://stackoverflow.com/questions/15366477
更新时间:2019-06-22 04:19

最满意答案

创建新表是一个不错的主意,但你不需要实现它,MySql已经有了这样的工具 - 谷歌的关键字“mysql +分区”。 我建议使用它因为你不需要改变你的查询,mysql本身就会关心它。 只需将“partition by”关键字添加到您的create table语句中即可。

还有一个技巧:我建议你将大量信息收集到一些大表中,并有时从中选择一些数据。 但是插入许多新行会导致表被锁定(不可用于选择)和重建索引(我确信您的表已被索引)。 在我目前的项目中,我正在做类似于你的事情,我建议你做以下事情:

1)创建BIG-TABLE的表克隆。 它应该与BIG-TABLE具有相同的结构,但有一个区别 - table-clone没有索引。

2)当您从设备接收数据时,将其放入表克隆中。

3)写一些机器人代理,每小时或每天将记录从小桌子放到大桌子 - 这取决于你,但最好的情况是选择这样的间隔,桌子的大小足够小,可以做全扫描(记住,它没有编入索引)。

4)当你想要执行SELECT查询时,你可以在2个表中 - 在索引的BIG表中 - 足够快,因为没有人试图将数据插入其中(有时只有机器人做),而小表中的fullscan - 也足够快,因为你可以保持小。

5)机器人应该在平静的时间醒来c-可能是在晚上。


Creating new tables is a nice idea, but you don't need to implement it, MySql already has such tool - google for keywords "mysql+partitioning". I recommend to use it because you needn't to change your queries, mysql itself cares about it. Just add "partition by" keyword to your create table statment.

One more trick for you: I suggest you are gathering a lot of information to some big table and also select sometimes some data from it. But inserting many new rows provokes table to be locked (unavailable for selects) and rebuild indexes (I'm sure that your table is indexed). In my current project I'm doing something similar to you and I advice you to do the following:

1) create table-clone of your BIG-TABLE. It should has the same structure with BIG-TABLE with one difference - table-clone doesn't has indexes.

2) when you recieve data from your devices put it into table-clone.

3) write some robot-agent which will put records from small table into big table each hour or each day - it depends to you but the best case is choose such interval that size of table will be small enough to do fullscan(remember, it isn't indexed).

4) when you want to perform SELECT query, you do it in 2 tables - in indexed BIG table - fast enough because nobody tryes to insert data into it(only robot do it sometimes), and fullscan in small table - also fast enough because you can keep it small.

5) robot should wake up in the calm time c- may be at night.

2013-03-13

相关问答

更多

我应该如何设计/构建我的数据库和ActiveRecord关联?(How should I design/structure my database and ActiveRecord associations? has_many :through?)

has_many:通过 如果要将多个FoodEntries为Document ,则只使用has_many :through关系,如下所示: #app/models/document.rb Class Document < ActiveRecord::Base has_many :food_entries_types has_many :food_entries, through: :food_entries_types end #app/models/food_entry_typ ...

挂出网站的数据库设计[关闭](Database design for hang out website [closed])

你绝对应该阅读有关数据库,外键等的内容。这些都是基础知识,如果没有这方面的知识你将无法获得。 但是为了让你开始,这里有一个简单的结构,可以满足你的要求。 希望我理解正确,因为你真的在这里给出了很少的细节。 表: 1)CLUB - 具有静态属性 - 不要经常更改ID,姓名,地址,网站,电话号码等。 数据行示例: ID name website phone 1 clubA www.aclub.com 565-554-666 2 clubB www.bclub.

如何改进数据库设计?(how to improve database design?)

在我的头脑中,我可能会用这样的东西: 该数据模型具有以下特征: 歌曲和艺术家之间存在多对多的关系(由中间的“链接”表格实现:SONG_ARTIST)。 相册位于单独的表格中。 如果您想要的只是专辑名称,这并不是特别重要,但我假设您以后会想要更多字段。 SONG_NAME和ALBUM_NAME 不是各自表格中的键 - 可能有多首歌曲(或专辑)共享相同的名称。 另一方面,GENRE_NAME是(备用)密钥。 ARTIST_NAME是否应该成为备用密钥是值得怀疑的。 我选择在我的模型中做到这一点,但这需 ...

带书桌的在线食品配送系统数据库设计[关闭](Database design for online food delivery system with book a table [closed])

正如我的评论中所述,这是一个基于意见的问题,可以有许多不同的答案。 此问题也不适合像stackoverflow这样的网站。 但是,让我们试着帮助你。 Customer user(FK) # why do you need this? 如果你想扩展django User类,这不会真正做到这一点。 客户和用户之间需要1:1的关系。 另外在您的其他模型中,您应该进一步参考Customer ,而不是User 。 所以说吧: Customer(models.Model): user =

数据库设计问题(Database design question)

如果你开始考虑Joel的建议,请到这里 。 或在这里 或在这里 如果你不相信他们,那就建立他提到的4张桌子。 只有4个,不需要很长时间。 然后将一些数据加载到它们中...然后尝试写出您想要写入的查询... 改变列的含义: 这可以真正与基数估计拧紧。 你的晚餐盘子可能在4-20的范围内,音乐会的座位在1000-2000之间。一些基数计算考虑了从最小值到最大值的假设分布以及假设和均等分布(当缺少其他统计数据时)... 从4到2000意味着GENERIC_COLUMN = n的任何地方,你会碰到的行数

Mysql数据库设计为curency交换网站应用程序(Mysql database design for curency exchange website application)

你应该使用这样的表: id | currency | rate | start-date 当您搜索货币时,您只需要查找具有正确日期的条目(最新开始日期,即早于您搜索的日期)。 选择: SELECT * FROM table WHERE date < searchDate ORDER BY date DESC LIMIT 1; 如果费率发生变化,您只需添加一个新行,并使用当前的SYSDATE。 选择: INSERT INTO table VALUES (id, 'EUR', '1.4', SYS

我如何绘制数据库设计?(How can I draw my database design?)

我认为你可以使用MySQL Workbench,它应该有反向工程(从数据库到模型)。 http://dev.mysql.com/workbench/ I think you can use MySQL Workbench, it's supposed to have reverse engineering (from database to model). http://dev.mysql.com/workbench/

需要为场景设计数据库(Need Database Design for the Scenario)

我认为你需要改变一下你的方法。 首先,您应该设计包含业务数据的数据库,而不必担心如何保存搜索对话框状态等内容。 在这一点上包括这些只会混淆设计。 你给出的图表看起来不错。 您应该删除表'IdentityType'。 这不是一个好桌子 - 它包含两种完全不同的东西。 这始终是SQL数据库中出错的线索。 而在OO中,我们可能有两个类ContactIdentity和BuildingIdentity继承自公共基类或接口,这不是在SQL中执行它的正确方法。 现在,您应该能够使每个搜索页面都在Javascri ...

相关文章

更多

最新问答

更多
  • 使用javascript为用户延迟加载内容的更好方法是什么?(What is a better way to lazy load content for users with javascript?)
  • 如何修复无法将类型的值...转换为预期的参数类型inout _(How to fix Cannot convert value of type … to expected argument type inout _)
  • 从哪里开始解析JSON?(Where to start with parsing JSON?)
  • Mail.php不适用于.htaccess(Mail.php doesn't work with .htaccess)
  • 使用htaccess设置子域(Setting subdomains using htaccess)
  • mod_pagespeed和java应用服务器(mod_pagespeed and java app servers)
  • Win32:如何崩溃?(Win32: How to crash?)
  • FFmpeg中RGB到YUV转换的错误(Error in RGB to YUV conversion in FFmpeg)
  • 如何在Android中首次打开应用时创建弹出窗口?(How to create pop-up when first open app in Android?)
  • 通过动态传递表名和列名来反转COLUMN DATA(Reversing COLUMN DATA by dynamically passing table name and Column Name)
  • 如何计算C#中ICollection中有多少元素?(How can I count how many elements are in an ICollection in C#)
  • 从pg-promise查询中获得结果(get result out of a pg-promise query)
  • 使用管理器注册一个类,然后调用子类重写方法(Register a class with a manager, then call the sub classes overridden methods)
  • 传递&在查询字符串中(Passing & in query string)
  • 谷歌浏览器打印预览不会第一次加载页面(google chrome print preview does not load the page the first time)
  • FsLexYacc:Tests / MiniProject“在.fsproj中导入错误”(FsLexYacc : Tests/MiniProject “incorrect Import in .fsproj”)
  • 创建一个模型方法,计算用户的帖子并在rspec(Rails)中测试?(Create a model method that counts a user's posts and test in rspec (Rails)?)
  • 记录线程的奇怪行为(Strange behaviour of logging thread)
  • 移动数组中的索引(Moving indices in an array)
  • MVC 5 - > MVC 5.1迁移。(MVC 5 -> MVC 5.1 Migration. Intellisense issues)
  • 使用函数在z3中创建列表(Creating List in z3 using function)
  • 如何进行html随机重定向(How to make a html random redirect)
  • 角度js:使用角度过滤器分割日期,月份和时间(Angular js: spliting date,month and time using angular filter)
  • 将所有模块导入一个文件夹,并按其属性之一使用它们(Import all modules in one folder and use them by one of its attributes)
  • 只需要在git的当前分支上获得合并列表(Just get list of merge happened on my current branch in git)
  • React-Native导入库并在多个组件中可用(React-Native import a library and make available in multiple components)
  • 如何使用jQuery为固定(偏移)位置设置div动画?(How to animate div for a fixed (offset) position using jQuery?)
  • operator ==和vector confusion(operator== and vector confusion)
  • 如何获取所选图像映射alt值?(How to get selected image map alt value?)
  • 在角度,如何使用取消用户事件的美元间隔,如页面更改?(In angular, how to use cancel an $interval on user events, like page change?)