Mysql - 查询优化

Question

我有一个这样的数据库架构，

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema stack_vista
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema stack_vista
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `stack_vista` DEFAULT CHARACTER SET utf8 ;
USE `stack_vista` ;

-- -----------------------------------------------------
-- Table `stack_vista`.`csv`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `stack_vista`.`csv` (
  `idcsv` INT NOT NULL AUTO_INCREMENT,
  `serviceCode` VARCHAR(45) NULL,
  `offerDate` DATETIME NULL,
  `price` VARCHAR(45) NULL,
  `offerDuration` INT NULL,
  `occupancy` INT NULL,
  `extra_cols` VARCHAR(45) NULL,
  PRIMARY KEY (`idcsv`),
  INDEX `index_service_code` (`serviceCode` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `stack_vista`.`offers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `stack_vista`.`offers` (
  `idoffers` INT NOT NULL AUTO_INCREMENT,
  `shipSlug` VARCHAR(45) NULL,
  `offerLink` VARCHAR(45) NULL,
  `name` VARCHAR(45) NULL,
  `serviceCode` VARCHAR(45) NULL,
  `extra_cols` VARCHAR(45) NULL,
  PRIMARY KEY (`idoffers`),
  INDEX `index_offer_code` (`serviceCode` ASC),
  FULLTEXT INDEX `index_ship_slug` (`shipSlug` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `stack_vista`.`ships`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `stack_vista`.`ships` (
  `idships` INT NOT NULL AUTO_INCREMENT,
  `slug` VARCHAR(45) NULL,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`idships`),
  FULLTEXT INDEX `index_slug` (`slug` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `stack_vista`.`props`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `stack_vista`.`props` (
  `idprops` INT NOT NULL AUTO_INCREMENT,
  `idoffers` INT NOT NULL,
  `propName` VARCHAR(45) NULL,
  `propCode` VARCHAR(45) NULL,
  `type` VARCHAR(45) NULL,
  PRIMARY KEY (`idprops`),
  INDEX `fk_props_offers_idx` (`idoffers` ASC),
  INDEX `index_type` (`type` ASC),
  CONSTRAINT `fk_props_offers`
    FOREIGN KEY (`idoffers`)
    REFERENCES `stack_vista`.`offers` (`idoffers`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `stack_vista`.`location`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `stack_vista`.`location` (
  `idlocation` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `code` VARCHAR(45) NULL,
  `type` VARCHAR(45) NULL,
  PRIMARY KEY (`idlocation`),
  INDEX `index_type` (`type` ASC),
  INDEX `index_name` (`name` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `stack_vista`.`offers_has_location`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `stack_vista`.`offers_has_location` (
  `offers_idoffers` INT NOT NULL,
  `location_idlocation` INT NOT NULL,
  PRIMARY KEY (`offers_idoffers`, `location_idlocation`),
  INDEX `fk_offers_has_location_location1_idx` (`location_idlocation` ASC),
  INDEX `fk_offers_has_location_offers1_idx` (`offers_idoffers` ASC),
  CONSTRAINT `fk_offers_has_location_offers1`
    FOREIGN KEY (`offers_idoffers`)
    REFERENCES `stack_vista`.`offers` (`idoffers`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_offers_has_location_location1`
    FOREIGN KEY (`location_idlocation`)
    REFERENCES `stack_vista`.`location` (`idlocation`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

我的表 csv 是一个表，我在其中添加从 csv 导入的数据，并提供不同日期、位置和类型的不同服务/旅行的优惠。它可以有 10000-100000 条记录，大约有 900-1000 个服务，其组合如 group by 所示。

不同的服务映射到offers表中的servicCode，它在offers表中是唯一的。

一些优惠具有shipSlugs，它映射到ships表中的slugs。

I am trying to list all possible serviceCodes, offerDate, propTypeId, and shipSlug. Since there are further factors for example, csv.extra_cols and csv.offerDuration which affects the price, I just wanted to get MIN(Price) based on columns used in GROUP BY hence the aggregation.

我在涉及 where 子句或条件连接的每一列中添加了索引。

下面的查询当前需要 6-9 秒才能执行。可以进一步做些什么来优化这个查询:

SELECT 
    i.serviceCode AS serviceCode,
    i.offerDate AS offerDate,
    MIN(i.price) AS price,
    i.offerDuration AS duration,
    i.occupancy AS occupancy,
    p.idprops AS propTypeId,
    p.propName AS propTypeName,
    p.propCode AS propTypeCode,
    p.type AS type,
    g.idlocation AS destinationId,
    g.name AS destinationName,
    g.code AS destinationCode,
    g.type AS destinationType,
    o.idoffers AS offerId,
    o.offerlink AS offerlink,
    o.name AS name,
    o.shipSlug AS shipSlug,
    s.name AS shipName,
    CONCAT_WS('-',
            YEAR(i.offerDate),
            MONTH(i.offerDate)) AS offer_year_month_date
FROM
    csv i
        JOIN
    offers o ON o.serviceCode = i.serviceCode
        LEFT JOIN
    ships s ON o.shipSlug = s.slug
        JOIN
    props p ON o.idoffers = p.idoffers
        JOIN
    offers_has_location og ON o.idoffers = og.offers_idoffers
        JOIN
    location g ON g.idlocation = og.location_idlocation
WHERE
    p.type = 'travelType'
        AND g.code IN ('Earth' , 'River', 'HighLands')
GROUP BY serviceCode, offer_year_month_date, propTypeId, shipSlug;

Answer 1

一个简单的说明，如果列名与结果 AS 相同，则不需要 AS。仅当您要更改列名称结果或某些函数的结果(例如 min/max、concat_ws 等)时。

至于分组依据。您需要将所有非聚合列包含为分组依据的一部分，以免引发错误。因此，例如:根据相应的 id 永远不会改变的查找描述，您可以使用 min() 或 max()。另一种方法是预先聚合除保留 ID 的查找代码描述之外的所有内容，然后加入以获取查找描述。

您正在寻找特定的类型和代码，我会尝试以此为起点进行优化。我还重构了查询，并且在 MySQL 中使用“STRAIGHT_JOIN”子句，该子句告诉引擎按照我告诉您的顺序运行查询。

现在，我已经使用 MIN() 对所有非分组列进行了分组，因此查询将正确获取每种 Prop 类型、服务代码、船舶、年/月的最低价格。但它看起来更像是具有不同出发地/目的地的旅行社事件。如果没有看到更真实的示例数据，我认为这些 MIN() 应用在其他元素上是没有意义的。您可能需要的不止这些。例如:基于单人入住的游轮显然会少于双人入住。因此，将入住作为团体的一部分是有意义的。从港口 A 到 B 的行程与从港口 A 到 C 的行程完全不同，因此我预计出发地和目的地也将属于团体的一部分。

我认为您确实需要断言您所要求的数据，才能真正使您的输出有意义。

SELECT STRAIGHT_JOIN
      p.idprops AS propTypeId,
      o.serviceCode,
      o.shipSlug,
      CONCAT_WS('-',
         YEAR(i.offerDate),
         MONTH(i.offerDate)) AS offer_year_month_date,
      min(i.offerDate ) FirstOfferDate,
      min(i.price) AS price,
      min(i.offerDuration ) AS MinDuration,
      min( i.occupancy ) MinOccupancy,
      min( p.propName ) AS propTypeName,
      min( p.propCode ) AS propTypeCode,
      min( p.type ) as type,
      min( g.idlocation ) AS destinationId,
      min( g.name ) AS destinationName,
      min( g.code ) AS destinationCode,
      min( g.type ) AS destinationType,
      min( o.idoffers ) AS offerId,
      min( o.offerlink ) AS offerlink,
      min( o.name ),
      min( s.name ) AS shipName
   FROM
      props p 
        JOIN offers o
           ON p.idoffers = o.idoffers 
           JOIN csv i
              ON o.serviceCode = i.serviceCode
           LEFT JOIN
              ships s ON o.shipSlug = s.slug
        JOIN offers_has_location og 
           ON p.idoffers = og.offers_idoffers
           JOIN location g 
              ON og.location_idlocation = g.idlocation
             AND g.code IN ('Earth' , 'River', 'HighLands')
   WHERE
      p.type = 'travelType'
   GROUP BY 
      serviceCode, offer_year_month_date, propTypeId, shipSlug;