Mysql大数据表查询

Question

我的 mysql 数据库表有问题。我的表中有超过 2000 万行。表结构如下所示。主要问题是查询需要很长时间才能执行(有些查询需要超过 20 秒)。我尽可能使用索引，但是许多查询使用日期范围，而对于日期范围，我的索引不起作用。另外，在查询中我几乎使用每一列。我需要对数据表进行哪些更改以提高效率？

`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`;

Answer 1

您的第一个查询最好写为:

select SUM(h.amount) as amount, 
       SUM(h.comment) as price, 
       h.cate 
from history h
where h.action = 4 and 
      h.place = 28 and
      h.created_at >= '2018-04-01' and
      h.created_at < '2018-05-01' and
      h.deleted_at is null 
group by h.cate;

为什么？

• 地点和操作是数字。比较应该是一个数字。混合类型可以防止使用索引。
• 时间部分对于日期比较没有用处。
• 限定所有列名称是个好主意。

那么，对于这个查询，合理的索引是history(action, place,created_at,deleted_at)。

所以，我会从多列索引开始。

如果您仍然遇到性能问题，则应考虑根据 created_at 日期对数据进行分区。