MySql GROUP BY 使用文件排序 - 查询优化

托比亚萨纳雷拉

我有一张这样的表:

CREATE TABLE `purchase` (
  `fact_purchase_id` binary(16) NOT NULL,
  `purchase_id` int(10) unsigned NOT NULL,
  `purchase_id_primary` int(10) unsigned DEFAULT NULL,
  `person_id` int(10) unsigned NOT NULL,
  `person_id_owner` int(10) unsigned NOT NULL,
  `service_id` int(10) unsigned NOT NULL,
  `fact_count` int(10) unsigned NOT NULL DEFAULT '0',
  `fact_type` tinyint(3) unsigned NOT NULL,
  `date_fact` date NOT NULL,
  `purchase_name` varchar(255) DEFAULT NULL,
  `activation_price` decimal(7,2) unsigned NOT NULL DEFAULT '0.00',
  `activation_price_total` decimal(7,2) unsigned NOT NULL DEFAULT '0.00',
  `renew_price` decimal(7,2) unsigned DEFAULT '0.00',
  `renew_price_total` decimal(7,2) unsigned NOT NULL DEFAULT '0.00',
  `activation_cost` decimal(7,2) unsigned DEFAULT '0.00',
  `activation_cost_total` decimal(7,2) unsigned NOT NULL DEFAULT '0.00',
  `renew_cost` decimal(7,2) unsigned DEFAULT '0.00',
  `renew_cost_total` decimal(7,2) unsigned NOT NULL DEFAULT '0.00',
  `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`fact_purchase_id`),
  KEY `purchase_id_idx` (`purchase_id`),
  KEY `person_id_idx` (`person_id`),
  KEY `person_id_owner_idx` (`person_id_owner`),
  KEY `service_id_idx` (`service_id`),
  KEY `fact_type_idx` (`fact_type`),
  KEY `renew_price_idx` (`renew_price`),
  KEY `renew_cost_idx` (`renew_cost`),
  KEY `renew_price_year_idx` (`renew_price_year`),
  KEY `renew_cost_year_idx` (`renew_cost_year`),
  KEY `date_created_idx` (`date_created`),
  KEY `purchase_id_primary_idx` (`purchase_id_primary`),
  KEY `fact_count` (`fact_count`),
  KEY `renew_price_year_total_idx` (`renew_price_total`),
  KEY `renew_cost_year_total_idx` (`renew_cost_total`),
  KEY `date_fact` (`date_fact`) USING BTREE,
  CONSTRAINT `purchase_person_fk` FOREIGN KEY (`person_id`) REFERENCES `person` (`person_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `purchase_person_owner_fk` FOREIGN KEY (`person_id_owner`) REFERENCES `person` (`person_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `purchase_service_fk` FOREIGN KEY (`service_id`) REFERENCES `service` (`service_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我正在启动这个查询:

SELECT 
    purchase.date_fact,
    UNIX_TIMESTAMP(purchase.date_fact),
    COUNT(DISTINCT purchase.purchase_id) AS Num
FROM
    purchase
WHERE
    purchase.date_fact >= '2017-01-01'
    AND purchase.date_fact <= '2017-01-31'
    AND purchase.fact_type = 3
    AND purchase.purchase_id_primary IS NULL
GROUP BY purchase.date_fact

该表共包含 5.629.670 条记录,并EXPLAIN在查询上运行,我得到以下结果:

  • rows = 2.814.835
  • possible_keys = fact_type_idx,purchase_id_primary_idx,date_fact
  • key = fact_type_idx
  • key_len = 1
  • ref = const
  • filtered = 25.00
  • Extra = Using index condition;Using where;Using filesort

执行查询需要30-35 秒这等得太久了。

问题是GROUP BY要应用文件排序原因。应用于ORDER BY NULL查询不会改变任何东西

我可以使用覆盖索引,但我只需要在此查询中使用 date_fact:我可以使用哪些字段?

如何避免文件排序GROUP BY如何优化查询以使其更快?

我将此表用于统计目的(OLAP)。也许有任何更好的 DBMS 用于此目的?

我正在运行 MySql Server 5.7.17。

谢谢

戈登·利诺夫

对于此查询:

SELECT p.date_fact, UNIX_TIMESTAMP(p.date_fact),
       COUNT(DISTINCT p.purchase_id) AS Num
FROM purchase p
WHERE p.date_fact >= '2017-01-01' AND
      p.date_fact <= '2017-01-31' AND
      p.fact_type = 3 AND
      p.purchase_id_primary IS NULL
GROUP BY p.date_fact;

我会推荐一个复合索引(fact_type, purchase_id_primary, date_fact, purchase_id)前两个键在WHERE. 第三个有不等式,第四个允许索引“覆盖”查询(查询中的所有列都在索引中)。

我还要补充一点:如果你不需要COUNT(DISTINCT),那就不要使用它。purchase_id可能已经是独一无二的purchase

本文收集自互联网,转载请注明来源。

如有侵权,请联系[email protected] 删除。

编辑于
0

我来说两句

0条评论
登录后参与评论

相关文章

来自分类Dev

MySQL查询GROUP BY WHERE IN

来自分类Dev

使用相关子查询优化MySQL查询

来自分类Dev

使用“ NOT IN”优化MySQL查询

来自分类Dev

使用几乎相同的子查询优化MySQL查询

来自分类Dev

为group_concat函数优化MySQL查询

来自分类Dev

使用GROUP BY和COUNT优化/重写LINQ查询

来自分类Dev

使用GROUP BY优化以相同字段开头的几个查询

来自分类Dev

优化MySQL查询,该查询使用子查询;MariaDB更快

来自分类Dev

laravel MySQL查询优化

来自分类Dev

如何使用MySQL子查询和GROUP BY

来自分类Dev

如何以慢速排序依据优化MYSQL查询

来自分类Dev

如何使用多个选择查询优化UNION mysql查询?

来自分类Dev

MySQL查询优化与多个分组或排序

来自分类Dev

MySQL GROUP BY查询

来自分类Dev

MySQL查询GROUP BY WHERE IN

来自分类Dev

使用GROUP BY的子查询中的MySQL COUNT

来自分类Dev

MySQL联接查询GROUP BY

来自分类Dev

MySQL优化使用WHERE子句的子查询?

来自分类Dev

优化使用IN和MAX(date)的MySQL查询

来自分类Dev

使用EXPLAIN输出优化慢速MySQL查询

来自分类Dev

MySQL:SUM / MAX / MIN GROUP BY查询优化

来自分类Dev

使用左联接优化MySql查询

来自分类Dev

使用索引优化MySQL查询性能

来自分类Dev

如何使用GROUP BY AND ROLLUP对MySQL查询的输出重新排序?

来自分类Dev

优化 MySQL GROUP BY 查询

来自分类Dev

MySQL 查询中的 GROUP BY

来自分类Dev

使用 subselect 优化 MySQL 查询

来自分类Dev

MySql 在主查询中使用 group by 对子查询进行排序

来自分类Dev

使用 GROUP BY 时的 MySQL 慢查询