日志表的Mysql设计

Question

我想就事件记录器的 mysql 表设计提出建议。

我们的需求:- 跟踪很多 Action - 10 000 次操作/秒- 此时有 10 亿行

我们的硬件:- 2*Xeon (被系统视为32 CPU)- 128 GB 内存- 6*600 SSD 支持 Raid 10

我们的表格设计:

CREATE TABLE IF NOT EXISTS `log_event` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `id_event` smallint(6) NOT NULL,
  `id_user` bigint(20) NOT NULL,
  `date` int(11) NOT NULL,
  `data` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_event_2` (`id_event`,`data`),
  KEY `id_inscri` (`id_inscri`),
  KEY `date` (`date`),
  KEY `id_event_4` (`id_event`,`date`,`data`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8


ALTER TABLE `log_event`
  ADD CONSTRAINT `log_event_ibfk_1` FOREIGN KEY (`id_inscri`) REFERENCES `inscription` (`id_inscri`) ON DELETE CASCADE ON UPDATE CASCADE;

我们的问题: - 我们有一个自动增量作为主要的，但它并没有真正被使用。删除它有问题吗？如果我们删除它，我们将没有主键=>如何识别一行？

• 我们想做partionning，但是国外好像做不到？

• 我们不进行批量插入。在没有索引的内存表中插入并每 5 分钟复制一次数据是个好主意吗？

你有优化的想法吗？您有此类系统的最佳实践吗？

谢谢!

弗朗索瓦

Answer 1

关系表(关系)的主键可能有两种类型:

1. Natural - 存在于主题区完全确定关系表的每一行。自然主键可能是简单(如果只包含一列)或复杂(如果包含多列)。不建议在大字符串列上设置自然主键。

2. Artificial - 特殊列，由数据库设计者/开发人员注入(inject)以提高表性能，如果自然键很复杂，并且必须在相关表中使用(是某物的外键) ，或者如果它很简单，但是很大并且在作为外键复制到相关表中时会产生数据开销，或者如果搜索起来很复杂(例如，对 VARCHAR ID 的 CRUD 操作可能是比 INT ID 慢)。 可能还有其他原因。 TL;DR:人工键 - 一个特殊的列，用于完全确定关系表的每一行并提高其 CRUD 操作的性能。

We have an auto-increment as primary, but it is not really used. Is it a problem to remove it ? We will no have primary key if we remove it => How to identify a line ?

如果您不需要将您的表引用到另一个表(作为源)，那么您可能会删除人工键而不会产生任何后果。尽管如此，我还是建议您在此表中设置任何其他 PRIMARY KEY 以避免数据重复，并且为了显而易见(如果重要的话)。

您的表本身(如果正确 normalized )将自然键作为“关键候选者”之一。它可能很复杂(由几列组成)。这是正常的。但是不要为字符串设置primary，因为PRIMARY总是有index，会产生数据开销。如果是INT或"small"VARCHAR列的组合，则正常。

考虑作为一个选项: id_event + id_user + date。

---

We don't do bulk insert. Is it a good idea to insert in a Memory table without index and copy data every 5 minutes ?

这不是一个坏主意。但这不是一个好主意，直​​到它经过适当的测试。在实际使用之前尝试执行负载测试。

如果您不向其他人引用MEMORY 表，那么您仍然可以将它与任何其他InnoDB 表连接起来。但是您将失去 InnoDB 功能 ( referential integrity )。如果丢失父表 ON DELETE CASCADE ON UPDATE CASCADE 不是问题，那么它可能会完成。对于我来说，InnoDB 切换表引擎的速度并没有那么慢，就您而言。