Home / Questions / 96048
Accepted
Bernardo Harreguy
Asked: 2020-08-20 03:41:06 +0800 CST 2020-08-20 03:41:06 +0800 CST

如何执行更快的 MYSQL 查询

  • 772

嗨，情况如何？我正在尝试执行数据库查询（MYSQL），我需要尽可能快地对其进行优化，现在我已经从 2 秒下降到 1.27，它仍然很慢，这是查询中的表：

价格表

CREATE TABLE `precios` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `porcentaje` DECIMAL(4,2) NOT NULL DEFAULT '0.00',
    `precio` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    `precio2` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    `ganancia` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    `costo` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    `idproducto` INT(11) NOT NULL DEFAULT '0',
    `numerodepreciodelista` INT(10) NOT NULL DEFAULT '0',
    `impuesto` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    `ingresosbrutos` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    PRIMARY KEY (`id`, `idproducto`)
)

条码表

CREATE TABLE `codigobarras` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `idproducto` INT(11) NOT NULL DEFAULT '0',
    `codigobarras` TEXT NOT NULL,
    `idrubro` INT(11) NOT NULL DEFAULT '0',
    `idpromocion` INT(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
)

产品类别表

CREATE TABLE `categoriadeproductos` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `nombre` TEXT NOT NULL,
    `ganancia` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    PRIMARY KEY (`id`)
)

供应商表

CREATE TABLE `proveedores` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(50) NOT NULL DEFAULT '0',
    `descripcion` TEXT NULL,
    `domicilio` VARCHAR(50) NULL DEFAULT NULL,
    `localidad` VARCHAR(50) NULL DEFAULT NULL,
    `telefono` VARCHAR(50) NULL DEFAULT NULL,
    `fax` VARCHAR(50) NULL DEFAULT NULL,
    `codigo postal` VARCHAR(50) NULL DEFAULT NULL,
    `contacto` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)

产品表

    CREATE TABLE `productos` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `nombre` TEXT NOT NULL,
    `categoria` INT(11) NOT NULL,
    `proveedor` INT(11) NOT NULL,
    `cantidad` DECIMAL(11,2) NOT NULL DEFAULT '0.00',
    `cantidadbultoabierto` DECIMAL(11,2) NOT NULL DEFAULT '0.00',
    `cantidadbultocerrado` DECIMAL(11,2) NOT NULL DEFAULT '0.00',
    `stockminimo` DECIMAL(10,2) NOT NULL DEFAULT '4.00',
    `fecha` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `codigobarras` VARCHAR(50) NOT NULL DEFAULT '',
    `imagen` BLOB NULL,
    `eliminado` TINYINT(1) NOT NULL DEFAULT '0',
    `especial` TINYINT(4) NOT NULL DEFAULT '0',
    `ingresosbrutos` DECIMAL(3,2) NOT NULL DEFAULT '0.00',
    `tipodeproducto` INT(11) NOT NULL DEFAULT '0',
    `ultimamodificacion` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `id` (`id`),
    INDEX `nombre` (`id`)
)

现在我正在做的查询如下

SELECT productos.id, productos.nombre as Nombre
, categoriadeproductos.nombre AS Categoria
, proveedores.nombre AS Proveedor 
, cantidad as Cantidad
, stockminimo AS 'Stock Mínimo'
, (precios.precio) AS Precio
, (precios.precio2) AS 'Precio %'
, (precios.costo) AS 'P. costo'
, (precios.ganancia) AS Ganancia
, ultimamodificacion AS 'Fecha última modificación'
, (select codigobarras.codigobarras From codigobarras WHERE codigobarras.idproducto = productos.id LIMIT 1) AS 'Código de Barras' 
FROM productos INNER JOIN (precios, proveedores, categoriadeproductos) ON  proveedores.id = productos.proveedor AND categoriadeproductos.id = productos.categoria AND precios.idproducto = productos.id WHERE precios.numerodepreciodelista = 0 AND productos.eliminado = 0;

大概总是在 1.2 到 1.3 秒之间，查询的解释如下：

+------+--------------------+----------------------+--------+-------------------+---------+---------+---------------------------------+------+--------------------------------------------------------+
| id   | select_type        | table                | type   | possible_keys     | key     | key_len | ref                             | rows | Extra                                      |
+------+--------------------+----------------------+--------+-------------------+---------+---------+---------------------------------+------+--------------------------------------------------------+
|    1 | PRIMARY            | precios              | ALL    | NULL              | NULL    | NULL    | NULL                            | 2073 | Using where                                      |
|    1 | PRIMARY            | productos            | eq_ref | PRIMARY,id,nombre | PRIMARY | 4       | sistema_tpv2.precios.idproducto |    1 | Using where                                      |
|    1 | PRIMARY            | proveedores          | ALL    | PRIMARY           | NULL    | NULL    | NULL                            |    2 | Using where; Using join buffer (flat, BNL join)        |
|    1 | PRIMARY            | categoriadeproductos | ALL    | PRIMARY           | NULL    | NULL    | NULL                            |    3 | Using where; Using join buffer (incremental, BNL join) |
|    2 | DEPENDENT SUBQUERY | codigobarras         | ALL    | NULL              | NULL    | NULL    | NULL                            | 1926 | Using where                                      |
+------+--------------------+----------------------+--------+-------------------+---------+---------+---------------------------------+------+--------------------------------------------------------+

查询说明

该查询列出了数据库中找到的所有产品。每个产品包含一个类别和一个供应商，它们通过其 ID 或索引指向后续表。这部分非常优化，但复杂的是价格，每个产品都有一个成本、一个价格、一个小数价格（price2）、基于成本的利润（利润），并且在价格表中，有一个 PRODUCTID 字段引用该产品，并且对于每个产品可能有几个价格。每个价格都由 LISTPRICENUMBER 标识，默认情况下它是 0，这是基本价格，在查询中我只对显示这个感兴趣。然后在 WHERE 我放 numberoflistprices = 0。最后，对于每个产品，都有一个条形码（codigobarras），每个产品可以有多个，因此我必须将其限制为 1，因为在查询中，每个条形码指的是 PRODUCTID 的产品场地。如您所见，在查询中我使用了内部联接，但我无法优化它超过 1.20 秒。希望能找到更好的方法！谢谢，从现在开始

澄清：所有表格都是产品的次要表格。 辅助表：

条码

Codigobarras 包含产品条形码。idproduct字段是指每个产品的id，每个产品可以有多个barcode，即一个产品可以有多个barcode。

价格

price 包含每个产品的价格。productid 字段是指产品 id。价格必须作为条件 numberoflistprice = 0，因为其他价格对这个查询不感兴趣。

类别

每个产品都有一个类别，产品表中的类别字段是指它所属的类别的id

提供者

和category一样，每个产品都属于一个provider，每个provider都有一个id，provider字段就是指这个。

在供应商和类别中，价格和条形码中的每个表只有 3 条记录，与产品的记录数相同，可能更多，产品有 2800 条记录

这是我有file.sql的基本sql

mysql

2 Answers

  1. Best Answer

    在我的测试机器上，原始查询需要 1.4 秒。

    查询可以从两个方面改进：

    不必要的嵌套查询

    INNER JOIN我消除的第一件事是嵌套查询，因为它是不必要的，因为与为获得的每条记录管理附加查询相比，表联合具有更好的性能。

    SELECT productos.id,
       productos.nombre AS Nombre,
       categoriadeproductos.nombre AS Categoria,
       proveedores.nombre AS Proveedor,
       cantidad AS Cantidad,
       stockminimo AS 'Stock Mínimo',
       (precios.precio) AS Precio,
       (precios.precio2) AS 'Precio %',
       (precios.costo) AS 'P. costo',
       (precios.ganancia) AS Ganancia,
       ultimamodificacion AS 'Fecha última modificación',
       codigobarras.codigobarras AS 'Código de Barras'
FROM productos
INNER JOIN precios
    ON precios.numerodepreciodelista = 0 AND precios.idproducto = productos.id 
INNER JOIN codigobarras
    ON codigobarras.idproducto = productos.id
INNER JOIN (proveedores, categoriadeproductos)
ON proveedores.id = productos.proveedor
    AND categoriadeproductos.id = productos.categoria
WHERE productos.eliminado = 0
GROUP BY productos.id

    现在执行时间是 850 毫秒。减少 40%（几乎一半）。

    但他DESCRIBE是：

    1       SIMPLE  precios NULL    ALL     NULL    NULL    NULL    NULL    2073    10.00   Using where; Using temporary; Using filesort
1       SIMPLE  productos       NULL    eq_ref  PRIMARY,id,nombre       PRIMARY 4       pruebas.precios.idproducto      1       10.00   Using where
1       SIMPLE  proveedores     NULL    ALL     PRIMARY NULL    NULL    NULL    2       50.00   Using where; Using join buffer (Block Nested Loop)
1       SIMPLE  categoriadeproductos    NULL    ALL     PRIMARY NULL    NULL    NULL    3       33.33   Using where; Using join buffer (Block Nested Loop)
1       SIMPLE  codigobarras    NULL    ALL     NULL    NULL    NULL    NULL    1926    10.00   Using where; Using join buffer (Block Nested Loop)

    表索引

    您的查询遇到的第二个问题（最重要的）是表之间的关联是使用未索引的字段建立的，因此要找到它们，您必须一遍又一遍地查看表的所有记录。

    如果我们为每个相关字段和到eliminado（在此查询中具有重要相关性）添加索引：

    ALTER TABLE `precios` ADD INDEX( `idproducto`);
ALTER TABLE `codigobarras` ADD INDEX(`idproducto`);
ALTER TABLE `productos` ADD INDEX( `eliminado`);

    通过删除嵌套查询，我们将查询时间减少到仅 140 毫秒，比初始查询（十分之一）减少 90%，比改进后的查询时间减少 84%。

    在这种情况下DESCRIBE，他给了我们：

    id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    filtered        Extra
1       SIMPLE  proveedores     NULL    ALL     PRIMARY NULL    NULL    NULL    2       100.00  Using temporary; Using filesort
1       SIMPLE  precios NULL    ALL     idproducto      NULL    NULL    NULL    2073    10.00   Using where; Using join buffer (Block Nested Loop)
1       SIMPLE  productos       NULL    eq_ref  PRIMARY,id,nombre,eliminado     PRIMARY 4       pruebas.precios.idproducto      1       9.98    Using where
1       SIMPLE  categoriadeproductos    NULL    ALL     PRIMARY NULL    NULL    NULL    3       33.33   Using where; Using join buffer (Block Nested Loop)
1       SIMPLE  codigobarras    NULL    ref     idproducto      idproducto      4       pruebas.precios.idproducto      1       100.00  NULL
    • 6

  2. 我建议您对 MySQL 中的数据规范化和关系进行一些研究，目前您在字段名称方面遇到一些问题，并且您没有关联表或为字段创建索引codigodebarras.idproducto，或者在这种情况下应该由关系productos.proveedor调用什么idproveedor你正在咨询 将索引添加到您关联的字段并正确创建关系应该会提高速度。

    我附上以下查询以使查询更加详细并删除子查询。

    SELECT 
pr.id, 
pr.nombre as Nombre,
cp.nombre AS Categoria,
pv.nombre AS Proveedor, 
cantidad as Cantidad,
stockminimo AS 'Stock Mínimo',
(p.precio) AS Precio,
(p.precio2) AS 'Precio %',
(p.costo) AS 'P. costo',
(p.ganancia) AS Ganancia,
ultimamodificacion AS 'Fecha última modificación',
cod.codigobarras AS 'Código de Barras'
FROM productos pr 
INNER JOIN precios p ON p.idproducto = pr.id
INNER JOIN proveedores pv ON pv.id = pr.proveedor 
INNER JOIN categoriadeproductos cp ON cp.id = pr.categoria
INNER JOIN codigobarras cod ON cod.idproducto = pr.id -- Probablemente devuelva mas de un resultados si tenes mas de un Codigo de Barra para un solo producto
WHERE 
p.numerodepreciodelista = 0 AND pr.eliminado = 0;

    我希望这有助于进一步澄清您的问题。干杯

    • 3