Mysql -查询优化

Question

我有这样一个DB模式，

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中所看到的。

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

一些offers有shipSlugs，它映射到ships表中的slugs。

我正在尝试列出所有可能的serviceCodes、offerDate、propTypeId和shipSlug。由于还有其他因素--例如csv.extra_cols和csv.offerDuration --影响价格，所以我只想根据GROUP BY中使用的列获得MIN(Price)，因此进行了聚合。

我在每个列中都添加了索引，这些列要么涉及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。只有在更改列名结果或某些函数的结果(如min/max、concat_ws等)时，才行。

至于这群人。为了避免抛出错误，需要将所有非聚合列作为组by的一部分。因此，可以使用min()或max()作为示例:查找描述不会根据其对应的id更改。另一种方法是预先聚合除保留ID的查找代码描述之外的所有内容，然后加入以获取查找描述。

您正在寻找一个特定的类型和代码，我将尝试优化从这一点开始。我还对查询进行了重组，并使用MySQL am使用"STRAIGHT_JOIN“子句，该子句告诉引擎按照我告诉您的顺序运行查询。

现在，我已经使用MIN()创建了所有非分组列，因此查询将正确地获得每个支柱类型、服务代码、船运、年份/月的最低价格。但它看起来更像是旅行社的活动，有着不同的起源/目的地。如果没有看到更真实的示例数据，我认为这些MIN()应用程序在其他元素上是没有意义的。你可能还需要更多。例:以单人乘船为基础的邮轮显然少于双人间。因此，将入伙作为团队的一部分是有意义的。从A港到B港的旅行将与A港到C港完全不同，因此，我希望从A港到B港的起源和目的地也将是该集团的一部分。

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

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;

Answer 2

一些小窍门：

而不是

s.name AS shipname
...
LEFT JOIN ships...

做

( SELECT name FROM ships ... ) AS shipname

那张桌子需要INDEX(slug, name)。嗯..。这个表不是slug和name之间的一对一映射吗？如果是的话，抛出idships并拥有

PRIMARY KEY(slug),
UNIQUE(ships),
FULLTEXT(ships)

我看不出其他避免JOINs膨胀和GROUP BY通货紧缩的例子。