使用mysql处理非常大的数据

Question

抱歉，很长的帖子！

我有一个包含约30个表的数据库（InnoDB引擎）。这些表中只有两个表，即“ transaction”和“
shift”非常大（第一个表有150万行，而shift有23000行）。现在一切正常，我对当前的数据库大小没有任何问题。

但是，我们将有一个类似的数据库（相同的数据类型，设计等），但数据库更大，例如，“事务”表将具有约 10亿条记录 （每天约有 230
万笔交易），我们正在考虑如何我们应该在MySQL中处理如此大量的数据吗？（这是读写密集型）。我阅读了许多相关的文章，以查看Mysql（更具体地说是InnoDB引擎）是否可以在数十亿条记录中表现良好，但是我仍然有一些疑问。我读过的一些相关文章如下：

• MySQL可以合理地对数十亿行执行查询吗？
• InnoDB（MySQL 5.5.8）是数十亿行的正确选择吗？
• 数十亿行的最佳数据存储
• 在性能开始下降之前，MySQL数据库能达到多少？
• 为什么使用大表的MySQL可能会变慢？
• Mysql可以处理将容纳约3亿条记录的表吗？

到目前为止，我对提高超大表的性能了解到的是：

1. （对于我的情况，对于innoDB表）增加了innodb_buffer_pool_size（例如，高达80％的RAM）。另外，我在percona博客中找到了其他一些MySQL性能调整设置
2. 在表上有适当的索引（对查询使用EXPLAN）
3. 分区表
4. MySQL分片或集群

这是我的问题/困惑：

• 关于分区，我有些疑问是否应该使用它。一方面，很多人建议它在表很大时提高性能。另一方面，我读过很多文章说它不能提高查询性能，也不能使查询运行得更快（例如，here和here）。另外，我在《MySQL参考手册》中读到 InnoDB外键和MySQL分区不兼容 （我们有外键）。

• 关于指标，现在他们表现良好，但据我了解，对于非常大的表的索引是更严格（如凯文·比德尔在他的回答中提到在这里）。同样，索引可加快读取速度，而减慢写入（插入/更新）速度。那么，对于拥有这个大数据库的新的类似项目，是否应该首先插入/加载所有数据，然后创建索引？（以加快插入速度）

• 如果我们不能对大表（“事务”表）使用分区，那么有什么替代方案可以提高性能呢？（MySQl变量设置除外，例如innodb_buffer_pool_size）。我们应该使用Mysql集群吗？（我们也有很多加入）

这是show create table我们最大的表“ transaction” 的语句：

  CREATE TABLE `transaction` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `terminal_transaction_id` int(11) NOT NULL,
 `fuel_terminal_id` int(11) NOT NULL,
 `fuel_terminal_serial` int(11) NOT NULL,
 `xboard_id` int(11) NOT NULL,
 `gas_station_id` int(11) NOT NULL,
 `operator_id` text NOT NULL,
 `shift_id` int(11) NOT NULL,
 `xboard_total_counter` int(11) NOT NULL,
 `fuel_type` int(11) NOT NULL,
 `start_fuel_time` int(11) NOT NULL,
 `end_fuel_time` int(11) DEFAULT NULL,
 `preset_amount` int(11) NOT NULL,
 `actual_amount` int(11) DEFAULT NULL,
 `fuel_cost` int(11) DEFAULT NULL,
 `payment_cost` int(11) DEFAULT NULL,
 `purchase_type` int(11) NOT NULL,
 `payment_ref_id` text,
 `unit_fuel_price` int(11) NOT NULL,
 `fuel_status_id` int(11) DEFAULT NULL,
 `fuel_mode_id` int(11) NOT NULL,
 `payment_result` int(11) NOT NULL,
 `card_pan` text,
 `state` int(11) DEFAULT NULL,
 `totalizer` int(11) NOT NULL DEFAULT '0',
 `shift_start_time` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `terminal_transaction_id` (`terminal_transaction_id`,`fuel_terminal_id`,`start_fuel_time`) USING BTREE,
 KEY `start_fuel_time_idx` (`start_fuel_time`),
 KEY `fuel_terminal_idx` (`fuel_terminal_id`),
 KEY `xboard_idx` (`xboard_id`),
 KEY `gas_station_id` (`gas_station_id`) USING BTREE,
 KEY `purchase_type` (`purchase_type`) USING BTREE,
 KEY `shift_start_time` (`shift_start_time`) USING BTREE,
 KEY `fuel_type` (`fuel_type`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1665335 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

谢谢你的时间，

Answer 1

• MySQL可以合理地对数十亿行执行查询吗？-MySQL可以“处理”数十亿行。“合理地”取决于查询；让我们看看他们。

• InnoDB（MySQL 5.5.8）是数十亿行的正确选择吗？-5.7进行了一些改进，但是5.5很好，尽管它 已经有6年 8年历史了，并且 几乎 不再受到支持。

• 数十亿行的最佳数据存储-如果您指的是“ Engine”，则为InnoDB。

• 在性能开始下降之前，MySQL数据库能达到多大？再次，这取决于查询。我可以向您展示一个将崩溃的1K行表。我已经处理了十亿行表格。

• 为什么使用大表的MySQL可能会变慢？-范围扫描导致I / O，这是最慢的部分。

• Mysql可以处理将容纳约3亿条记录的表吗？-再说一次。限制大约在一万亿行。

• （对于我的情况是innoDB表）增加innodb_buffer_pool_size（例如，最多80％的RAM）。另外，我在percona博客中找到了其他一些MySQL性能调整设置-是的

• 在表上有适当的索引（对查询使用EXPLAN）-好，让我们来看一下。在此 关键 区域中可能会犯很多错误。

• 分区表-“分区不是万能的！” 我在 我的博客中 竖琴 __

• MySQL分片-目前是DIY

• MySQL集群-当前最好的答案是一些基于Galera的选项（PXC，MariaDB 10，带有Oracle的DIY）。Oracle的“组复制”是一个可行的竞争者。

• 分区不支持FOREIGN KEY或“全局” UNIQUE。

• 您所谈论的UUID不仅会减慢系统速度，而且实际上会杀死它。 类型1 UUID 可能是一种解决方法。

• 插入和建立索引的速度-太多的变化无法给出一个答案。让我们看看您的初步想法CREATE TABLE以及打算如何输入数据。

• 许多联接-“规范化，但不要过度规范化”。特别是，请勿标准化日期时间或浮点数或其他“连续”值。

• 做 汇总表

• 每天230万笔交易-如果那是230万次 插入 （30 /秒），则不会有太大的性能问题。如果更复杂，则可能需要RAID，SSD，批处理等。

• 处理这样的数据量-如果大多数活动都与“最近”的行有关，则buffer_pool将很好地“缓存”该活动，从而避免了I / O。如果活动是“随机的”，则MySQL（或 其他任何 人）将遇到I / O问题。

• 缩小数据类型有助于像您这样的表。我怀疑您是否需要4个字节来指定fuel_type。有多种1字节方法。