首页 \ 问答 \ 数据库设计 - 如何构建(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 ...

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

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

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

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

数据库设计......如何表示类似多态的类型(Database design… how to represent polymorphic like types)

我不太关注你的表结构也不是你的最终目标,但你可以消除中间人又名clipboard 。 只需创建一个或多个clipboard_item表:每种类型一个或类似类型的公共表。 你可以有一个clipboard_item_text表: id ,INT AUTO_INCREMENT user ,INT(?),FK到user / owner item ,TEXT 现在,您(所有剪贴板表) item与其owner之间始终具有参照完整性。 (就像你只是将父表id ish值插入子表一样,你可以使用任何子表。) 如果您 ...

如何在数据库设计中建模属性单元?(How to model attribute units in a database design?)

有趣的问题...... 有两条明显的路线: id load_kW fuel_consumption_tonnes -------------------------------------------------- 1 1154 89.4 2 1199 54.2 这对于人类来说很容易阅读,并且相当合乎逻辑。 但是,如果某些读数为“千克”,其他读数为“吨”,则必须将这些读数转换为“读数”表; 这个过程必须“无损”,并且是幂等的。 例如,“8940 ...

如何通过查询获取MySQL中的数据库结构(How to get database structure in MySQL via query)

我认为你以后是DESCRIBE DESCRIBE table; 您也可以使用SHOW TABLES SHOW TABLES; 获取数据库中的表的列表。 I think that what you're after is DESCRIBE DESCRIBE table; You can also use SHOW TABLES SHOW TABLES; to get a list of the tables in your database.

如何使用我拥有的视频网站设计更好或功能更强的数据库?(How can I design a better or functional database for a video site with what I have?)

您似乎忘记将user_profile_id列放在User Profile表中,以及所有其他列的主键字段? 此外,鉴于影片与标签的关系是多对多,您需要一个桥接表,其中包含movie_id和tag_id。 根据您当前的设计,电影只能有一个标签。 It appears you forget to put the user_profile_id column in the User Profile table, as well as primary key fields for all the other ...

DataBase(datamodel)构建一个文件夹结构(DataBase (datamodel) to build a folder structure)

为了存储在数据库中,最简单和最直接的方法是为每个文件夹/节点设置一个parent_folder_id。 在大多数情况下,这应该足够好,特别是要构建文件夹对象结构并根据对象模型进行操作。 取决于你的要求,有一个很常见的情况,你需要 找出某个文件夹下的所有子文件夹 通过SQL直接从数据库执行查找。 如果这是您要查找的内容,那么您可能会看到一个有趣的方法:每个数据库记录都会有两个额外的数字字段,我们称它为“左”和“右” 假设这样一棵树: ROOT + A | + A1 | + A2 + ...

相关文章

更多

最新问答

更多
  • Android宽度:100%修复(网站接管问题)(Android width:100% fix (website takeover issue))
  • C ++函数/方法设计的良好实践(Good practice in C++ function/method design)
  • 计算其他表中不存在的所有记录 - SQL查询(Count all records that does not exist to other table - SQL Query)
  • 为什么我要用JPA共享ID?(Why do I get shared Ids with JPA?)
  • asp.net - 如何显示来自html格式的数据行的字段(asp.net - how to display a field from data row that is in html format)
  • 我们如何使用ActiveRecord从连接表中删除行?(How can we delete rows from a join table by using ActiveRecord?)
  • ng-class搞乱了类的顺序(ng-class messing with the order of classes)
  • oracle 12g无效数字错误(oracle 12g invalid number error)
  • 更改ng-src值onclick(Change ng-src value onclick)
  • 如何在android中自动添加自定义依赖项以创建新项目?(How to add custom dependencies automatically in android for ever a new project is created?)
  • datetime函数在PHP中(datetime function in php)
  • 在javascript中获取会话数组的值(in javascript get the value of a session array)
  • 如何在UTF8中编译LaTeX?(How can I compile LaTeX in UTF8? [closed])
  • Rspec:“array.should == another_array”,但不用担心订单(Rspec: “array.should == another_array” but without concern for order)
  • Logcat错误:无法在android片段中加载视图(Logcat error: unable to load view in android fragments)
  • JavaFX的。(JavaFX. Adding items to the list in different threads is not working)
  • 从GDATA日历资源迁移到Google Calendar Resource api(Migrate from GDATA calendar resource to Google Calendar Resource api)
  • SSRS 2008 - 以零情景处理分割(SSRS 2008 - Dealing with division by zero scenarios)
  • 我如何以编程方式添加一个listView列标题的点击事件(How can I add a listView column header a click event programmatically)
  • Wxpython:无法检索有关列表控件项XXX的信息(Wxpython: Couldn't retrieve information about list control item XXX)
  • 使用Tortoise SVN在SVN存储库中移动目录(Move Directory across SVN repository using Tortoise SVN)
  • 天蓝色服务结构集群中的web api无状态服务是否在一段时间不活动后进入休眠状态?(Do web api stateless services in azure service fabric cluster go to sleep after a period of inactivity?)
  • 我可以设置intelliJ来突出显示PHP编码风格吗?(Can I set intelliJ to highlight php coding style?)
  • 用javafx创建一个Truetype字体文件(Creating a Truetype Font file with javafx)
  • Spring ftp配置错误(Spring ftp configuration is wrong)
  • 使用gsub去除多个字符(Using gsub to strip multiple characters)
  • 续订推送证书并保持当前的App Store App正常工作(Renew Push certificate and keep current App Store App working)
  • js:ES5和ES6之间关于'this'关键字用法的一个令人困惑的观点(js: one confusing point about 'this' keyword usage between ES5 and ES6)
  • window.onload vs $(document).ready()(window.onload vs $(document).ready())
  • 在Swift中,如何声明一个符合一个或多个协议的特定类型的变量?(In Swift, how can I declare a variable of a specific type that conforms to one or more protocols?)