大数据表中的Mysql查询
大数据表中的Mysql查询
提问于 2018-06-27 16:15:06
我的mysql数据库表有问题。我在表中有超过2000万行。表结构如下所示。主要的问题是查询需要很长的时间来执行(有些查询需要超过20秒)。我尽可能地使用索引，但是许多查询使用日期范围和with date range，我的索引不起作用。此外，在查询中，我几乎使用了每一列。为了提高效率，我需要对我的数据表进行哪些更改？
`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,
`ywd` 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`) USING BTREE,
KEY `action` (`action`) USING BTREE,
KEY `original` (`original`) USING BTREE,
KEY `created_at` (`created_at`) USING BTREE,
KEY `bag` (`bag`) USING BTREE
ENGINE=InnoDB我的一些疑问是：
select SUM(amount) as amount,
SUM(comment) as price,
cate
from `history`
where ( `action` = '4'
and `place` = '28'
and `created_at` >= '2018-04-01 00:00:00'
and `created_at` <= '2018-04-30 23:59:59'
)
and `history`.`deleted_at` is null
group by `cate`;
select cate,
SUM(amount) AS kiekis,
SUM(IF(discount>0,(price*amount)-discount,(price*amount))) AS suma,
SUM(IF(discount>0,IF(discount_type=1,(discount*price)/100,discount),0)) AS nuolaida
from `history`
where ( `history`.`action` = '4'
and `history`.`created_at` >= '2018-01-01 00:00:00'
and `history`.`created_at` <= '2018-01-23 23:59:59'
)
and LENGTH(barcode) > 7
and `history`.`deleted_at` is null
group by `cate`;回答 1
Stack Overflow用户
发布于 2018-06-28 03:44:04
INDEX(a), INDEX(b)有一定的用途，但“复合”INDEX(a,b)更好地服务于某些查询。
where ( `action` = '4'
and `place` = '28'
and `created_at` >= '2018-04-01 00:00:00'
and `created_at` <= '2018-04-30 23:59:59'
)
and `history`.`deleted_at` is null 需求
INDEX(action, place, -- first, but in either order
deleted_at,
created_at) -- last我更喜欢这样写日期范围：
and `history`.`created_at` >= '2018-04-01'
and `history`.`created_at` < '2018-04-01' + INTERVAL 1 MONTH它比处理闰年、年终等要简单得多，而且它在DATE、DATETIME、DATETIME(6)、TIMESTAMP和TIMESTAMP(6)上都能“正确”工作。
为此
where ( `history`.`action` = '4'
and `history`.`created_at` >= '2018-01-01 00:00:00'
and `history`.`created_at` <= '2018-01-23 23:59:59'
)
and LENGTH(barcode) > 7
and `history`.`deleted_at` is null 我会尝试这个最有可能的：
INDEX(action, deleted_at, created_at) -- in this order对于不同的年份，不要有单独的表。如果您要删除旧数据，那么为了获得DROP PARTITION的速度，请考虑使用PARTITION BY RANGE(TO_DAYS(...))。(但这是另一种讨论。)
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51057818
复制相关文章
相似问题