存储实体上的变化：MySQL是否是正确的解决方案？ [英] Storing changes on entities: Is MySQL the proper solution?

问题描述

我想存储我在实体表上所做的更改。这应该是一个日志。目前在MySQL中使用这个表来实现：

  CREATE TABLE`entitychange`（
`id` int ）unsigned NOT NULL AUTO_INCREMENT，
`entity_id` int（10）unsigned NOT NULL，
`entitytype`枚举（'STRING_1'，'STRING_2'，'SOMEBOOL'，'SOMEDOUBLE'，'SOMETIMESTAMP'） NOT NULL DEFAULT'STRING_1'，
`when` TIMESTAMP NOT NULL，
`value` TEXT，
 PRIMARY KEY（`id`）
）ENGINE = InnoDB DEFAULT CHARSET = latin1 ; 
  

• entity_id =我的实体的主键表。


• entitytype =字段在实体表中已更改。有时只有一个字段被改变，有时是多个。一个变化=一行


• value =字段的新值的字符串表示。

---

将Field entity.somedouble 3到2，我运行这些查询：

 更新实体SET somedouble = 2 WHERE entity_id = 123; 
 INSERT INTO entitychange（entity_id，entitytype，value）VALUES（123，'SOMEDOUBLE'，2）; 
  

---

我需要 select 最近15天的特定实体和实体类型的更改。例如：最近15天内，对于entity_id 123 的 SOMEDOUBLE 进行的最后一次更改。

现在，有两件事我不喜欢：

1. 所有数据存储为 TEXT - 虽然大多数（少于1％）不是真正的文本，但在我的例子中，大多数值都是 DOUBLE 。这是一个大问题吗？


2. 表插入时变得真的很慢，因为表已经有2亿行了。目前，由于这个原因，我的服务器负载高达10-15。

---

我的问题：我如何解决这两个瓶颈？我需要缩放。

我的方法是：

1. 如下所示： http://sqlfiddle.com/#!2/df9d0 （点击浏览） - 商店 entitychange 表中的更改，然后根据其数据类型在 entitychange_ [bool | timestamp | double | string]


2. 使用分区 HASH（entity_id） - 我想到了〜50个分区。


3. 我应该使用另一个数据库系统，也许MongoDB？




解决方案>

如果我面对你提到的问题，我会设计LOG表如bellow：

1. EntityName ：（String）正在被操纵的实体（必需）


2. ObjectId ：Entity


3. FieldName :( String）实体字段名称。


4. OldValue ：（String）实体字段旧值。


5. NewValue ：（String）实体字段新值。


6. UserCode ：应用程序用户唯一标识符。 （强制性）


7. TransactionCode ：任何更改实体的操作都需要具有唯一的事务代码（如GUID）（强制性）
   如果更改实体更改多个字段，则这些列将是跟踪更新中的所有更改（转录）的关键点。


8. ChangeDate ：交易日期。 （强制性）


9. FieldType ：枚举或文字显示字体类型，如TEXT或Double。 （强制性）

拥有此方法
可以跟踪任何实体（表）
报表可读
将只记录更改。
事务代码将是检测单个操作更改的关键点。


BTW

 将更改存储在entitychange表中，然后根据其数据类型在entitychange_ [bool | timestamp | double | string] 
  > 

不需要，在单表中，您将有更改和数据类型

 使用HASH（entity_id）分区
  

我宁愿分区通过ChangeDate或为alterDate创建足够旧的备份表，以便从主LOG表中备份和删除

 我应该使用另一个数据库系统，也许MongoDB？ 
  

任何数据库都有自己的prob和cons，可以在任何RDBMS上使用设计。
可以找到诸如MongoDB之类的基于文档的数据库的有用比较可以找到这里

希望有所帮助。

i want to store changes that i do on my "entity" table. This should be like a log. Currently it is implemented with this table in MySQL:

CREATE TABLE `entitychange` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `entity_id` int(10) unsigned NOT NULL,
  `entitytype` enum('STRING_1','STRING_2','SOMEBOOL','SOMEDOUBLE','SOMETIMESTAMP') NOT NULL DEFAULT 'STRING_1',
  `when` TIMESTAMP NOT NULL,
  `value` TEXT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

• entity_id = the primary key of my entity table.
• entitytype = the field that was changed in the entity table. sometimes only one field is changed, sometimes multiple. one change = one row.
• value = the string representation of the "new value" of the field.

---

Example when changing Field entity.somedouble from 3 to 2, i run those queries:

UPDATE entity SET somedouble = 2 WHERE entity_id = 123;
INSERT INTO entitychange (entity_id,entitytype,value) VALUES (123,'SOMEDOUBLE',2);

---

I need to select the changes of a specific entity and entitytype of the last 15 days. For example: The last changes with SOMEDOUBLE for entity_id 123 within the last 15 days.

Now, there are two things that i dislike:

1. All Data is stored as TEXT - although most (less than 1%) isn't really text, in my case, most values are DOUBLE. Is this a big problem?
2. The Table is getting really, really slow when inserting, since the table already has 200 million rows. Currently my Server load is up to 10-15 because of this.

---

My Question: How do i address those two "bottlenecks"? I need to scale.

My approaches would be:

1. Store it like this: http://sqlfiddle.com/#!2/df9d0 (click on browse) - Store the changes in the entitychange table and then store the value according to its datatype in entitychange_[bool|timestamp|double|string]
2. Use partitioning by HASH(entity_id) - i thought of ~50 partitions.
3. Should I use another database system, maybe MongoDB?

解决方案

If I were facing the problem you mentioned, I would design LOG table like bellow:

1. EntityName: (String) Entity that is being manipulated.(mandatory)
2. ObjectId: Entity that is being manipulated, primary key.
3. FieldName: (String) Entity field name.
4. OldValue: (String) Entity field old value.
5. NewValue: (String) Entity field new value.
6. UserCode: Application user unique identifier. (mandatory)
7. TransactionCode: Any operation changing the entities will need to have a unique transaction code (like GUID) (mandatory),
   In case of an update on an entity changing multiple fields,these column will be the key point to trace all changes in the update(transcation)
8. ChangeDate: Transaction date. (mandatory)
9. FieldType: enumeration or text showing the field type like TEXT or Double. (mandatory)

Having this approach
Any entity (table) could be traced
Reports will be readable
Only changes will be logged.
Transaction code will be the key point to detect changes by a single action.

BTW

Store the changes in the entitychange table and then store the value 
according to its datatype in entitychange_[bool|timestamp|double|string]

Won't be needed, in the single table you will have changes and data types

Use partitioning by HASH(entity_id)

I will prefer partitioning by ChangeDate or creating backup tables for changeDate that are old enough to be backed up and remover from the main LOG table

Should I use another database system, maybe MongoDB?

Any data base comes with its own prob and cons , you can use the design on any RDBMS. A useful comparison of documant based data bases like MongoDB could be found here

hope be helpful.

这篇关于存储实体上的变化：MySQL是否是正确的解决方案？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！