使用mysql处理非常大的数据

对不起,很长的帖子!

我有一个包含~30个表(InnoDB引擎)的数据库.这些表中只有两个,即“事务”和“移位”非常大(第一个有150万行,移位有23k行).现在一切正常,我没有当前数据库大小的问题.

但是,我们将拥有一个类似的数据库(相同的数据类型,设计,…),但更大,例如,“交易”表将有大约10亿条记录(每天约2,300万笔交易),我们正在思考如何我们应该在MySQL中处理这么大量的数据? (这是读写密集型).我阅读了很多相关帖子,看看Mysql(更具体地说是InnoDB引擎)是否可以与数十亿条记录表现良好,但我仍然有一些问题.我读过的一些相关帖子如下:

> Can MySQL reasonably perform queries on billions of rows?
> Is InnoDB (MySQL 5.5.8) the right choice for multi-billion rows?
> Best data store for billions of rows
> How big can a MySQL database get before performance starts to degrade
> Why MySQL could be slow with large tables?
> Can Mysql handle tables which will hold about 300 million records?

到目前为止我已经理解为提高非常大的表的性能:

>(对于innoDB表,这是我的情况)增加innodb_buffer_pool_size(例如,高达80%的RAM).
另外,我发现了一些其他MySQL性能调整设置here in
percona blog

>在表上有适当的索引(在查询中使用EXPLAN)
>分区表
> MySQL Sharding或群集

这是我的问题/困惑:

>关于分区,我怀疑是否应该使用它.一方面,许多人建议在桌子非常大时提高性能.另一方面,我阅读了许多帖子,说它不会提高查询性能,也不会使查询运行得更快(例如,herehere).另外,我在MySQL Reference Manual读到InnoDB外键和MySQL分区不兼容(我们有外键).
>关于索引,现在它们表现良好,但据我所知,对于非常大的表索引更具限制性(正如Kevin Bedell在他的回答here中提到的).此外,索引加速读取,同时减慢写入(插入/更新).那么,对于我们将拥有这个大型数据库的新类似项目,我们应该首先插入/加载所有数据然后创建索引吗? (加快插入速度)
>如果我们不能对我们的大表(“事务”表)使用分区,那么提高性能的另一种选择是什么? (除了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

谢谢你的时间,

解决方法:

> MySQL可以合理地对数十亿行执行查询吗? – MySQL可以“处理”数十亿行. “合理”取决于查询;让我们看看他们.
> InnoDB(MySQL 5.5.8)是数十亿行的正确选择吗? – 5.7有一些改进,但5.5是相当不错的,尽管已经将近6 8岁,并且即将不再受到支持.
>数十亿行的最佳数据存储 – 如果您的意思是“引擎”,那么InnoDB.
>在性能开始降低之前,MySQL数据库有多大 – 再次,这取决于查询.我可以告诉你一个会崩溃的1K行表;我曾与数十亿行表一起工作.
>为什么MySQL可能会因大表而变慢? – 范围扫描导致I / O,这是缓慢的部分.
> Mysql可以处理将容纳约3亿条记录的表吗? – 再次,是的.限制大约是一万亿行.
>(对于innoDB表,这是我的情况)增加innodb_buffer_pool_size(例如,高达80%的RAM).此外,我在percona博客中找到了一些其他MySQL性能调整设置 – 是的
>在表上有适当的索引(在查询中使用EXPLAN) – 好吧,让我们看看它们.在这个关键领域可以犯很多错误.
>分区表 – “分区不是灵丹妙药!”我在my blog上竖起了这个
> MySQL Sharding – 目前这是DIY
> MySQL集群 – 目前最好的答案是一些基于Galera的选项(PXC,MariaDB 10,DIY w / Oracle). Oracle的“组复制”是一个可行的竞争者.
>分区不支持FOREIGN KEY或“全局”UNIQUE.
> UUID,就你所说的规模而言,不仅会减慢系统速度,还会实际杀死它. Type 1 UUIDs可能是一种解决方法.
>插入和索引构建速度 – 提供单个答案的变化太多.让我们看看您的暂定CREATE TABLE以及您打算如何提供数据.
>很多连接 – “规范化,但不要过度规范化”.特别是,不要标准化日期时间或浮点数或其他“连续”值.
>建立summary tables
>每天2,300万笔交易 – 如果是2.3M插入(30 /秒),则没有太大的性能问题.如果更复杂,则可能需要RAID,SSD,批处理等.
>处理这样的数据量 – 如果大多数活动都是“最近”行,那么buffer_pool将很好地“缓存”活动,从而避免I / O.如果活动是“随机”,则MySQL(或其他任何人)将遇到I / O问题.
>缩小数据类型有助于像您这样的表格.我怀疑你是否需要4个字节来指定fuel_type.有多个1字节方法.

上一篇:文件上传php $_FILES未定义索引错误


下一篇:字符串中子字符串的基本索引重复(python)