抱歉篇幅有点长!
我有一个包含约30个表格(使用InnoDB引擎)的数据库。其中只有两张表,“transaction”和“shift”比较大(第一张表有150万行,第二张表有23k行)。现在一切都正常运作,我没有当前数据库大小的问题。
然而,我们将会有一个类似的数据库(相同的数据类型、设计等),但更大,例如,“transaction”表将会有约10亿条记录(每天约230万次交易),我们正在思考如何应对MySQL中这么多的数据量?(读写都很密集)。我阅读了很多相关的帖子,想知道Mysql(尤其是InnoDB引擎)是否能够处理亿级别的记录,但我仍然有一些问题。我阅读过的一些相关帖子如下:
- MySQL能否在数十亿行上合理执行查询?
- 对于拥有数十亿行的数据,InnoDB(MySQL 5.5.8)是正确的选择吗?
- 处理数十亿行数据的最佳数据存储方式
- MySQL数据库可以增长到多大才会开始降低性能?
- 为什么MySQL在大表情况下可能会很慢?
- Mysql能够处理将容纳约3亿条记录的表吗?
- (对于我的情况)增加
innodb_buffer_pool_size
的大小(例如,最多可达 RAM 的 80%)。此外,我在 percona 博客中找到了其他一些 MySQL 性能调整设置。 - 在表上使用适当的索引(使用查询的 EXPLAN)
- 对表进行分区
- MySQL 分片或集群
关于分区,我有一些疑惑,不确定我们是否应该使用它。一方面,许多人建议在表非常大的情况下使用分区来提高性能。另一方面,我读到很多帖子说它并不能提高查询性能,并且不能使查询运行更快(例如 这里和 这里)。而且,我在 MySQL参考手册中看到 InnoDB外键和MySQL分区不兼容(我们有外键)。
关于索引,现在它们表现良好,但据我所知,对于非常大的表,索引更加严格限制(正如Kevin Bedell在这里中提到的)。此外,索引加速读取操作,但会减缓写入操作(插入/更新)。因此,对于我们将要拥有这个大型数据库的新项目,我们应该先插入/加载所有数据,然后再创建索引吗?(以加快插入速度)
如果我们无法为我们的大表(“交易”表)使用分区,那么除了MySQl变量设置(例如
innodb_buffer_pool_size
)之外,改进性能的替代选择是什么?我们应该使用Mysql集群吗?(我们也有许多联接)
编辑
这是我们最大的表格"transaction"的show create table
语句:
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
谢谢您的时间,