MySQL：优化GROUP BY多个键 [英] MySQL: Optimization GROUP BY multiple keys

问题描述

我在MySql数据库中有一个PAYMENTS表：

  CREATE TABLE`PAYMENTS`（
`ID` BIGINT （20）NOT NULL AUTO_INCREMENT，
`USER_ID` BIGINT（20）NOT NULL，
`CATEGORY_ID` BIGINT（20）NOT NULL，
`AMOUNT` DOUBLE NULL DEFAULT NULL，
 PRIMARY KEY（`ID`），
 INDEX`PAYMENT_INDEX1`（`USER_ID`），
 INDEX`PAYMENT_INDEX2`（`CATEGORY_ID`），
 INDEX`PAYMENT_INDEX3`（`CATEGORY_ID`， `USER_ID`）
）ENGINE = InnoDB; 
  

我想获得ech类别中每个用户的汇总金额。以下是脚本：

 从PAYMENTS 
中选择总和（AMOUNT），USER_ID，CATEGORY_ID 
按USER_ID ，CATEGORY_ID; 
  

MySql的EXPLAIN命令显示Extra：Using temporary; Using filesort

如何摆脱使用临时&你有一个关于（CATEGORY_ID，USER_ID）的索引 但是您按 USER_ID，CATEGORY_ID 。

订单很重要！ 创建一个包含您实际使用的 GROUP BY 子句的索引 - 字段按照相同的顺序。

I have a table PAYMENTS in MySql database:

CREATE TABLE `PAYMENTS` (
    `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `USER_ID` BIGINT(20) NOT NULL,
    `CATEGORY_ID` BIGINT(20) NOT NULL,
    `AMOUNT` DOUBLE NULL DEFAULT NULL,
    PRIMARY KEY (`ID`),
    INDEX `PAYMENT_INDEX1` (`USER_ID`),
    INDEX `PAYMENT_INDEX2` (`CATEGORY_ID`),
    INDEX `PAYMENT_INDEX3` (`CATEGORY_ID`, `USER_ID`)
) ENGINE=InnoDB;

I want to get summary amount for each user in ech category. Here is script:

select sum(AMOUNT), USER_ID, CATEGORY_ID
from PAYMENTS
group by USER_ID, CATEGORY_ID;

MySql's EXPLAIN command shows Extra: "Using temporary; Using filesort"

How to rid of Using temporary & filesort?

解决方案

You have an index on (CATEGORY_ID, USER_ID) but you're grouping by USER_ID, CATEGORY_ID.

Order matters! Create an index that covers the GROUP BY clause you're actually using — with the fields in the same order.

这篇关于MySQL：优化GROUP BY多个键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！