mysql - 优化 MySQL 查询以避免 “Using temporary” 和 “Using filesort”

Question

我知道有一千个类似的问题，但我已经花了两周时间试图找到这个查询的真正解决方案。

此查询来自销售点计划。此查询与用户选择所需报告(期间、总计等)以及是否需要分组结果的表单相关。

这很不方便。查询是在 VB.NET 中生成的，我的意思是，通过代码生成，并且它将根据用户的选择(不同的总计、期间、组等)而变化，因此通过解决这个问题，我应该能够继续“创建"通过代码，表单的所有其他查询。

在本例中，该查询是按家庭分组的总计查询。

大部分时间 (>99%) 都浪费在发送数据上(显示查询 # 的配置文件)

表格如下:

CREATE TABLE `product` (
  `idProduct` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `idFamily` tinyint(3) unsigned DEFAULT NULL,
  `Codigo` char(10) NOT NULL,
  `Nombre` char(70) DEFAULT NULL COMMENT 'Nombre corto',
-- five more integer columns
  PRIMARY KEY (`idProduct`),
  KEY `fk_p_idFamily` (`idFamily`),
  CONSTRAINT `fk_p_idFamily` FOREIGN KEY (`idFamily`) REFERENCES `family`  (`idFamily`),
) ENGINE=InnoDB AUTO_INCREMENT=19420 DEFAULT CHARSET=latin1 PACK_KEYS=0;

CREATE TABLE `family` (
  `idFamily` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `Nombre` char(30) NOT NULL,
  `Descripcion` char(255) DEFAULT NULL,
  `Borrado` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`idFamily`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=latin1 PACK_KEYS=0

CREATE TABLE `document` (
`idDocument` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`Nombre` char(25) NOT NULL,
`Descripcion` char(100) DEFAULT NULL,
`Borrado` tinyint(1) NOT NULL DEFAULT '0',
`NoComputa` tinyint(1) NOT NULL DEFAULT '0',
  `Rectifica` tinyint(1) NOT NULL DEFAULT '0',
  `CalculoSumatorioPVP` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`idDocument`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 PACK_KEYS=0

CREATE TABLE `soldproduct` (
  `idProduct` smallint(5) unsigned NOT NULL,
  `idSale` int(10) unsigned NOT NULL,
  `PrecioCompra` decimal(7,2) NOT NULL ,
  `PrecioVenta` decimal(7,2) NOT NULL ,
  `DtoProd` decimal(7,4) DEFAULT NULL ,
  `BrutoUd` decimal(7,2) NOT NULL ,
  `PVPUd` decimal(7,2) NOT NULL ,
  `Cantidad` decimal(9,3) DEFAULT NULL ,
  PRIMARY KEY (`idProduct`,`idSale`),
  KEY `fk_pv_idSale` (`idSale`),
  CONSTRAINT `fk_pv_idProduct` FOREIGN KEY (`idProduct`) REFERENCES `product` (`idProduct`),
  CONSTRAINT `fk_pv_idSale` FOREIGN KEY (`idSale`) REFERENCES `sales` (`idSale`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 PACK_KEYS=0

CREATE TABLE `sales` (
  `idSale` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `idDocument` tinyint(3) unsigned NOT NULL,
  `idEstadoVenta` tinyint(3) unsigned NOT NULL,
  `idCliente` smallint(5) unsigned NOT NULL,
  `idFormaPago` tinyint(3) unsigned NOT NULL,
  `idEmpleado` tinyint(3) unsigned NOT NULL ,
  `idTienda` tinyint(3) unsigned DEFAULT NULL,
  `idTipoVenta` tinyint(3) unsigned NOT NULL,
  `FechaVenta` datetime DEFAULT NULL COMMENT 'Fecha de Venta',
  `PrecioCompraTotal` decimal(10,2) DEFAULT NULL,
  `IVA` decimal(7,4) DEFAULT NULL,
  -- ten more decimal columns
  -- five more datetime columns
  -- ten more char columns
  `Borrado` tinyint(1) NOT NULL DEFAULT '0' ,
  `Historia` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`idVenta`),
  KEY `fk_v_idTienda` (`idTienda`),
  KEY `fk_v_idCliente` (`idCliente`),
  KEY `fk_v_idEmpleado` (`idEmpleado`),
  KEY `fk_v_idTipoVenta` (`idTipoVenta`),
  KEY `fk_v_idFormaPago` (`idFormaPago`),
  KEY `fk_v_idDocument` (`idDocument`),
  KEY `fk_v_idEstadoVenta` (`idEstadoVenta`),
  KEY `idx_v_FechaVenta` (`FechaVenta`),
  CONSTRAINT `fk_v_idCliente` FOREIGN KEY (`idCliente`) REFERENCES `cliente` (`idCliente`),
  CONSTRAINT `fk_v_idDocument` FOREIGN KEY (`idDocument`) REFERENCES `document` (`idDocument`),
  CONSTRAINT `fk_v_idEmpleado` FOREIGN KEY (`idEmpleado`) REFERENCES `empleado` (`idEmpleado`),
  CONSTRAINT `fk_v_idEstadoVenta` FOREIGN KEY (`idEstadoVenta`) REFERENCES `estadoventa` (`idEstadoVenta`),
  CONSTRAINT `fk_v_idFormaPago` FOREIGN KEY (`idFormaPago`) REFERENCES `formapago` (`idFormaPago`),
  CONSTRAINT `fk_v_idTienda` FOREIGN KEY (`idTienda`) REFERENCES `tienda` (`idTienda`),
  CONSTRAINT `fk_v_idTipoVenta` FOREIGN KEY (`idTipoVenta`) REFERENCES `tipoventa` (`idTipoVenta`)
) ENGINE=InnoDB AUTO_INCREMENT=101770 DEFAULT CHARSET=latin1 PACK_KEYS=0

查询是这样的:

SELECT  f.Nombre  ,SUM(sp.PrecioVenta*sp.Cantidad)  
FROM soldproduct sp, sales s, document doc, family f, product p 
WHERE s.idDocument = doc.idDocument AND doc.NoComputa = FALSE
AND p.idProduct = sp.idProduct AND sp.idSale = s.idSale 
AND p.idFamily = f.idFamily AND p.Borrado = FALSE 
AND s.Borrado = FALSE AND s.Historia = FALSE AND s.idTienda = 1 
AND s.FechaVenta BETWEEN '2013-01-01' AND '2014-01-01' GROUP BY f.idFamily;

我也尝试过这个(我还删除了文档表，以防它负责)

SELECT ProductFamily.Nombre, SUM(sp.PrecioVenta*sp.Cantidad) 
FROM 
(SELECT idSale FROM sales WHERE Borrado = FALSE AND Historia = FALSE AND idTienda = 1 
AND FechaVenta BETWEEN '2013-01-01' AND '2014-01-01') SalesidSale
JOIN
soldproduct sp
ON sp.idSale = SalesidSale.idSale
JOIN
(SELECT p.idProduct, p.idFamily, f.Nombre FROM product p, family f WHERE 
p.idFamily = f.idFamily AND p.Borrado = FALSE) ProductFamily
ON ProductFamily.idProduct = sp.idProduct
GROUP BY ProductFamily.idFamily;

花费的时间非常长，它对 EXPLAIN 命令的输出是(第一个查询):

id  select_type table   type    possible_keys                                               key                 key_len ref                     rows    Extra
1   SIMPLE      v       range   PRIMARY,fk_v_idTienda,fk_v_idDocument,idx_v_FechaVenta      idx_v_FechaVenta    6       NULL                    7387    "Using index condition; Using where; Using MRR; Using temporary; Using filesort"
1   SIMPLE      doc     ALL     PRIMARY                                                     NULL                NULL    NULL                    4       "Using where; Using join buffer (Block Nested Loop)"
1   SIMPLE      pv      ref     PRIMARY,fk_pv_idSale                                        fk_pv_idSale        4       gemalia.s.idSale        4       NULL
1   SIMPLE      p       eq_ref  PRIMARY,fk_p_idFamily                                       PRIMARY             2       gemalia.sp.idProduct    1       "Using where"
1   SIMPLE      f       eq_ref  PRIMARY                                                     PRIMARY             1       gemalia.p.idFamily      1       NULL

我希望有人可以帮助我，我尝试创建索引、子查询等，但我无法得到低于 40 秒的任何内容，这太多了，而且我确信我做错了什么..

每个表的行数大约为:销量:10万产品:20,000家庭:35已售产品: 1,100,000文档:4

非常感谢。

Answer 1

尝试这个查询，我们试图在其中利用短路。

编辑

 SELECT  
   f.Nombre, 
   SUM(sp.PrecioVenta*sp.Cantidad)  
 FROM 
   soldproduct sp
 INNER JOIN 
   sales s
 ON 
   (s.idTienda = 1 AND AND 
   s.Borrado = FALSE AND 
   s.Historia = FALSE AND 
   sp.idSale = s.idSale)
 INNER JOIN
   document doc
 ON
   (doc.NoComputa = FALSE AND
   s.idDocument = doc.idDocument) 
 INNER JOIN
   family f
 ON
   (p.idFamily = f.idFamily) 
 INNER JOIN
   product p 
 ON
   (p.Borrado = FALSE AND         
   p.idProduct = sp.idProduct)
 WHERE 
   s.FechaVenta BETWEEN '2013-01-01' AND '2014-01-01' 
 GROUP BY 
   f.idFamily;