mysql查询

4ngedf3f 于 2021-06-24 发布在 Mysql

关注(0)|答案(3)|浏览(237)

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

sql mysql mariadb innodb

来源：https://stackoverflow.com/questions/51057818/mysql-queries-in-big-data-table

3条答案

按热度按时间

fdbelqdn1#

如果我是你的情况，我会考虑使用分页数据库名称。我的意思是有多个history表，其中x是与内容相关的int。
既然这是一个历史记录表，那么是否可以在名称中包含部分日期？
您说过使用范围来搜索数据，所以如果在表名中使用year，您可以
2014年历史
2015年历史
2016年历史
2017年历史展
2018年历史展
等。
然后您可以搜索适用于您的日期范围的表。
如果需要从一个范围到多个表的日期，那么可以使用联合查询将两个结果集桥接成一个结果集。

赞(0）回复(0）举报 2021-06-24

bqucvtff2#

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

在不同的年份没有单独的表格。如果要删除旧数据，请考虑 PARTITION BY RANGE(TO_DAYS(...)) 为了获得 DROP PARTITION . (但这是另一种讨论。）

赞(0）回复(0）举报 2021-06-24

dgiusagp3#

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

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;

为什么？ place 以及 action 是数字。比较应该是一个数字。混合类型可以防止使用索引。
时间组件对于日期比较不有用。
限定所有列的名称只是一个好主意。
然后，对于这个查询，需要一个合理的索引 history(action, place, created_at, deleted_at) .
所以，我将从多列索引开始。
如果仍然存在性能问题，那么应该考虑基于 created_at 日期。

赞(0）回复(0）举报 2021-06-24

我来回答

mysql查询

3条答案

相关问题

热门标签

最新问答