我正在开发仓库控制系统,由php,laravel框架,mariadb编写。为了获得关于每个产品的所有信息,我们使用products "history“表,该表记录了对特定产品采取的所有操作。这个表开始扩展得非常快,现在我们有大约1500万行innoDB表,它开始工作很慢,特别是在运行function时,它需要对销售、创建、丢弃等产品的数量进行全面分析,然后在一个查询中使用全部1500万行。所以我开始寻找方法,如何管理这个大表,因为索引不再起作用了。我开始考虑按日期对这个表进行拆分/分区,也许是操作?因此,也许有人对此有任何经验,可以与我分享一些建议?非常感谢你的帮助!
CREATE TABLE `history` ( `id` int(11) NOT NULL AUTO_INCREMENT, `barcode` varchar(100) DEFAULT NULL, `bag` varchar(100) DEFAULT NULL, `action` int(10) unsigned DEFAULT NULL, `place` int(10) unsigned DEFAULT NULL, `price` decimal(10,2) DEFAULT NULL, `old_price` decimal(10,2) DEFAULT NULL, `user` int(11) DEFAULT NULL, `amount` int(10) DEFAULT NULL, `rotation` int(10) unsigned DEFAULT NULL, `discount` decimal(10,2) DEFAULT NULL, `discount_type` tinyint(2) unsigned DEFAULT NULL, `original` int(10) unsigned DEFAULT NULL, `was_in_shop` int(10) unsigned DEFAULT NULL, `cate` int(10) unsigned DEFAULT NULL COMMENT 'grupe', `sub_cate` int(10) unsigned DEFAULT NULL, `comment` varchar(255) DEFAULT NULL, `helper` varchar(255) DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `barcode` (`barcode`), KEY `action` (`action`), KEY `original` (`original`), KEY `created_at` (`created_at`), KEY `bag` (`bag`) ) ENGINE=InnoDB AUTO_INCREMENT=16274267 DEFAULT CHARSET=utf8
例如查询:
select cate,
SUM(amount) AS amount, SUM(IF(discount>0,(price*amount)-discount,
(price*amount))) AS sum, SUM(IF(discount>0,IF(discount_type=1,
(discount*price)/100,discount),0)
) AS discount from history
where (history.action = '4'
and history.created_at >= '2017-11-01 00:00:00'
and history.created_at <= '2017-11-23 23:59:59'
)
and LENGTH(barcode) > 7
and history.deleted_at is null
group by cate
此查询用于获取有关已售出产品的金额、金额和折扣信息(操作4)在此示例中,它是2017-11-01和2017-11-23之间的信息,EXPLAIN
给了我以下信息:
id - 1 select_type - SIMPLE table - history type - ref possible_keys - action,created_at key - action key_len - 5 ref - const rows - 1444272 Extra - Using where; Using temporary; Using filesort
因此,从2017-01-01到目前为止,它需要150万行和表,所以2年后将需要300万行,以此类推……当我只需要拿到2017-11年度产品的销售信息时。我还有很多类似于这个的查询。
发布于 2018-04-09 09:07:37
INT
占用4个字节;其他大小为available.PARTITIONing
本身不提供任何performance.history.deleted_at is null
--请考虑实际删除行。INDEX(action, created_at)
。(一次只使用一个索引。)最大的改进来自于构建和维护汇总表;请参阅http://mysql.rjweb.org/doc.php/summarytables。然后对它们运行查询。而且大多数索引都可以消失。
修复其中的一些;然后我可以进一步帮助您。
More
一条评论询问如何以两种不同的方式维护汇总表id。任何一种都可能是可行的,这取决于更多尚未指明的细节:
INSERT INTO Fact
表,然后立即使用IODKU插入或更新汇总表。INSERT .. SELECT ..
以捕获尚未汇总的行,并将计数/小计放入汇总表。后一种选择是可行的,但有两件事需要注意:
PRIMARY KEY
,如日期(日或小时)和几个维度值,那么您就像是在使用INSERT
时遇到了麻烦。或者避免将其作为PK (从而导致多行,这不是“坏”的),或者在表单INSERT ... ON DUPLICATE KEY ... SELECT ... GROUP BY ...;
中使用IODKU并使用VALUES(xx)
函数。https://stackoverflow.com/questions/49631578
复制相似问题