优化MySQL查询以避免“使用临时”和“使用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; 

所花费的时间是非常大的，并且它的输出解释命令（第一查询）：

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秒的任何东西，这太多了，而且我确信我做错了什么。

每张表的行数大约为： 销售：100,000 产品：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;