mysql - 优化用于查找两个表之间匹配的MySQL查询-6ren

mysql - 优化用于查找两个表之间匹配的MySQL查询

转载作者：可可西里更新时间：2023-11-01 07:38:23

在我正在进行的一个项目中，我有两个表：
consumption：包含来自客户的历史订单，其中的字段指定他们购买的产品的功能（每行一个产品）
product：包含当前产品库存
数据库引擎是innodb。
目标：
应用程序必须显示两边的匹配项，我的意思是：
当我列出当前产品库存时，我想显示一个列，显示与特定产品匹配的历史订单数
当我列出历史订单时，我想看看有多少产品符合特定的历史订单
consumption和product表以及其他相关表的数据库结构：

CREATE TABLE `consumption` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `created_by_id` INT(11) NULL DEFAULT NULL,
    `client_id` INT(11) NOT NULL,
    `data_import_id` INT(11) NULL DEFAULT NULL,
    `tmp_consumption_id` INT(11) NULL DEFAULT NULL,
    `material_id` INT(11) NULL DEFAULT NULL,
    `quality_id` INT(11) NULL DEFAULT NULL,
    `thick` DECIMAL(10,3) NULL DEFAULT NULL,
    `thick_max` DECIMAL(10,3) NULL DEFAULT NULL,
    `width` DECIMAL(10,2) NULL DEFAULT NULL,
    `width_max` DECIMAL(10,2) NULL DEFAULT NULL,
    `long` INT(11) NULL DEFAULT NULL,
    `long_max` INT(11) NULL DEFAULT NULL,
    `purchase_price` DECIMAL(10,2) NULL DEFAULT NULL,
    `sale_price` DECIMAL(10,2) NULL DEFAULT NULL,
    `comments` VARCHAR(255) NULL DEFAULT NULL,
    `annual_consumption` DECIMAL(10,3) NULL DEFAULT NULL,
    `type` ENUM('consumption','request') NULL DEFAULT 'consumption',
    `date_add` DATETIME NOT NULL,
    `date_upd` DATETIME NOT NULL,
    `covering_grammage` VARCHAR(64) NULL DEFAULT NULL,
    `asp_sup_acab` VARCHAR(64) NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_consumption_client1` (`client_id`),
    INDEX `created_by_id` (`created_by_id`),
    INDEX `material_id` (`material_id`),
    INDEX `quality_id` (`quality_id`),
    CONSTRAINT `consumption_ibfk_1` FOREIGN KEY (`material_id`) REFERENCES `material` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT `consumption_ibfk_2` FOREIGN KEY (`quality_id`) REFERENCES `quality` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT `fk_consumption_client1` FOREIGN KEY (`client_id`) REFERENCES `client` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=30673
;

CREATE TABLE `product` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `warehouse_id` INT(11) NULL DEFAULT NULL,
    `created_by_id` INT(11) NULL DEFAULT NULL,
    `data_import_id` INT(11) NULL DEFAULT NULL,
    `tmp_product_id` INT(11) NULL DEFAULT NULL,
    `code` VARCHAR(32) NOT NULL,
    `material_id` INT(11) NULL DEFAULT NULL,
    `quality_id` INT(11) NULL DEFAULT NULL,
    `covering_id` INT(11) NULL DEFAULT NULL,
    `finish_id` INT(11) NULL DEFAULT NULL,
    `source` VARCHAR(128) NULL DEFAULT NULL,
    `thickness` DECIMAL(10,3) NULL DEFAULT NULL,
    `width` INT(11) NULL DEFAULT NULL,
    `tons` DECIMAL(10,3) NULL DEFAULT NULL,
    `re` INT(11) NULL DEFAULT NULL,
    `rm` INT(11) NULL DEFAULT NULL,
    `a_percent` INT(11) NULL DEFAULT NULL,
    `comments` VARCHAR(255) NULL DEFAULT NULL,
    `price` DECIMAL(10,2) NULL DEFAULT NULL,
    `deleted` TINYINT(1) NOT NULL DEFAULT '0',
    `date_add` DATETIME NOT NULL,
    `date_upd` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `warehouse_id` (`warehouse_id`),
    INDEX `material_id` (`material_id`),
    INDEX `quality_id` (`quality_id`),
    INDEX `covering_id` (`covering_id`),
    INDEX `finish_id` (`finish_id`),
    CONSTRAINT `product_ibfk_1` FOREIGN KEY (`material_id`) REFERENCES `material` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT `product_ibfk_2` FOREIGN KEY (`quality_id`) REFERENCES `quality` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT `product_ibfk_3` FOREIGN KEY (`covering_id`) REFERENCES `covering` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT `product_ibfk_4` FOREIGN KEY (`finish_id`) REFERENCES `finish` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT `product_ibfk_5` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouse` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=740
;
CREATE TABLE `client` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `zone_id` INT(11) NULL DEFAULT NULL,
    `zone2_id` INT(11) NULL DEFAULT NULL,
    `code` VARCHAR(64) NOT NULL,
    `business_name` VARCHAR(255) NULL DEFAULT NULL,
    `fiscal_name` VARCHAR(255) NULL DEFAULT NULL,
    `nif` VARCHAR(15) NULL DEFAULT NULL,
    `contact_short_name` VARCHAR(128) NULL DEFAULT NULL,
    `contact_full_name` VARCHAR(128) NULL DEFAULT NULL,
    `email` VARCHAR(255) NULL DEFAULT NULL,
    `group` VARCHAR(255) NULL DEFAULT NULL,
    `status` TINYINT(1) NOT NULL DEFAULT '1',
    `date_add` DATETIME NOT NULL,
    `date_upd` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `code_UNIQUE` (`code`),
    INDEX `zone_id` (`zone_id`),
    INDEX `zone2_id` (`zone2_id`),
    CONSTRAINT `client_ibfk_1` FOREIGN KEY (`zone_id`) REFERENCES `zone` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=443
;
CREATE TABLE `client_group` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `code` VARCHAR(15) NOT NULL,
    `date_add` DATETIME NOT NULL,
    `date_upd` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `code` (`code`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=49
;
CREATE TABLE `client_has_group` (
    `client_id` INT(11) NOT NULL,
    `group_id` INT(11) NOT NULL,
    INDEX `client_id` (`client_id`),
    INDEX `group_id` (`group_id`),
    CONSTRAINT `client_has_group_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `client` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT `client_has_group_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `client_group` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
CREATE TABLE `covering` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `code` VARCHAR(128) NOT NULL,
    `group` VARCHAR(128) NULL DEFAULT NULL,
    `equivalence` VARCHAR(128) NULL DEFAULT NULL,
    `date_add` DATETIME NOT NULL,
    `date_upd` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `code_UNIQUE` (`code`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=55
;
CREATE TABLE `finish` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `code` VARCHAR(128) NOT NULL,
    `group` VARCHAR(128) NULL DEFAULT NULL,
    `equivalence` VARCHAR(128) NULL DEFAULT NULL,
    `date_add` DATETIME NOT NULL,
    `date_upd` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `code_UNIQUE` (`code`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=42
;
CREATE TABLE `material` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `code` VARCHAR(128) NOT NULL,
    `group` VARCHAR(128) NULL DEFAULT NULL,
    `equivalence` VARCHAR(128) NULL DEFAULT NULL,
    `date_add` DATETIME NOT NULL,
    `date_upd` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `code_UNIQUE` (`code`),
    INDEX `group` (`group`),
    INDEX `equivalence` (`equivalence`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=46
;
CREATE TABLE `quality` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `code` VARCHAR(128) NOT NULL,
    `group` VARCHAR(128) NULL DEFAULT NULL,
    `equivalence` VARCHAR(128) NULL DEFAULT NULL,
    `date_add` DATETIME NOT NULL,
    `date_upd` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `code_UNIQUE` (`code`),
    INDEX `group` (`group`),
    INDEX `equivalence` (`equivalence`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=980
;
CREATE TABLE `user_filter` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `user_id` INT(11) NOT NULL,
    `filter_type` ENUM('consumption','product') NOT NULL DEFAULT 'consumption',
    `name` VARCHAR(255) NOT NULL,
    `is_default` TINYINT(1) NOT NULL DEFAULT '0',
    `client_status` TINYINT(1) NULL DEFAULT NULL,
    `client_group` VARCHAR(45) NULL DEFAULT NULL,
    `material` VARCHAR(15) NULL DEFAULT NULL,
    `quality` VARCHAR(64) NULL DEFAULT NULL,
    `thickness` VARCHAR(45) NULL DEFAULT NULL,
    `width` VARCHAR(45) NULL DEFAULT NULL,
    `tons` VARCHAR(45) NULL DEFAULT NULL,
    `covering` VARCHAR(45) NULL DEFAULT NULL,
    `finish` VARCHAR(45) NULL DEFAULT NULL,
    `re` VARCHAR(45) NULL DEFAULT NULL,
    `rm` VARCHAR(45) NULL DEFAULT NULL,
    `a_percent` VARCHAR(45) NULL DEFAULT NULL,
    `comments` VARCHAR(255) NULL DEFAULT NULL,
    `price` VARCHAR(45) NULL DEFAULT NULL,
    `warehouse` VARCHAR(45) NULL DEFAULT NULL,
    `date` VARCHAR(45) NULL DEFAULT NULL,
    `type` ENUM('consumption','request') NULL DEFAULT NULL,
    `date_add` DATETIME NOT NULL,
    `date_upd` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_user_filter_user1` (`user_id`),
    INDEX `filter_type` (`filter_type`),
    CONSTRAINT `fk_user_filter_user1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=5
;
CREATE TABLE `warehouse` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(128) NOT NULL,
    `zone_id` INT(11) NULL DEFAULT NULL,
    `zone2_id` INT(11) NULL DEFAULT NULL,
    `date_add` DATETIME NOT NULL,
    `date_upd` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `zone_id` (`zone_id`),
    INDEX `zone2_id` (`zone2_id`),
    CONSTRAINT `warehouse_ibfk_1` FOREIGN KEY (`zone_id`) REFERENCES `zone` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=37
;
CREATE TABLE `zone` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `zone2_id` INT(11) NULL DEFAULT NULL,
    `name` VARCHAR(128) NOT NULL,
    `date_add` DATETIME NOT NULL,
    `date_upd` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `zone2_id` (`zone2_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=49
;

我所做的是能够找到两个表之间的匹配：
我已经在 consumption和 product表之间创建了一个左连接查询（如果需要，也可以与其他表连接）。
看起来像这样：

SELECT cons.`id` as `consumption_id`, cons.`client_id` as `consumption_client_id`, cons.`material_id` as `consumption_material_id`, cons.`quality_id` as `consumption_quality_id`, cons.`thick` as `consumption_thick`, cons.`thick_max` as `consumption_thick_max`, cons.`width` as `consumption_width`, cons.`width_max` as `consumption_width_max`, cons.`long` as `consumption_long`, cons.`long_max` as `consumption_long_max`, cons.`type` as `consumption_type`, cons.`date_add` as `consumption_date_add`, prod.`id` as `product_id`, prod.`warehouse_id` as `product_warehouse_id`, prod.`code` as `product_code`, prod.`material_id` as `product_material_id`, prod.`quality_id` as `product_quality_id`, prod.`covering_id` as `product_covering_id`, prod.`finish_id` as `product_finish_id`, prod.`thickness` as `product_thickness`, prod.`width` as `product_width`, prod.`tons` as `product_tons` 
      FROM consumption cons 
        INNER JOIN client cli
          ON cli.id=cons.client_id
        LEFT JOIN client_has_group cli_gr
          ON cli_gr.client_id=cons.client_id
        LEFT JOIN product prod
          ON 

          (
            (cons.material_id=prod.material_id)
              OR 
            prod.material_id IN (
              SELECT id FROM material WHERE `equivalence`=(
                  SELECT `equivalence` FROM material WHERE id=cons.material_id
                )
                AND `group`=(
                  SELECT `group` FROM material WHERE id=cons.material_id
                )
            )
          )


 AND 

          (
            (cons.quality_id=prod.quality_id)
              OR 
            prod.quality_id IN (
              SELECT id FROM quality WHERE `equivalence`=(
                  SELECT `equivalence` FROM quality WHERE id=cons.quality_id
                )
                AND `group`=(
                  SELECT `group` FROM quality WHERE id=cons.quality_id
                )
            )
          )


 AND (prod.thickness  >= (cons.thick - 0.1) AND prod.thickness <= (cons.thick_max + 0.1))
 AND (prod.width  >= (cons.width - 1000) AND prod.width <= (cons.width_max + 1000)) 

             WHERE 1 > 0 AND prod.deleted=0 AND cli.status=1 AND cons.date_add >= '2017-10-08 00:00:00' 
        GROUP BY cons.id, prod.id

当我想列出产品并显示每个产品的消耗量匹配时，我有一个主查询，它只列出产品，然后我将该查询与上面的上一个查询连接起来，并按产品id对匹配项进行分组计数。

SELECT t.*,
       count(f.consumption_id) AS matchesCount
FROM `product` t
LEFT JOIN (...previous query here...) f ON f.product_id=t.id
GROUP BY t.id

其他注意事项：
应用程序使用两个表中具有相同名称的两个字段，以便使用 ON中的 JOIN查找匹配项。
应用程序还使用更复杂的业务逻辑，例如，产品材料可以相等，也可以在等价表或组内
用户可以保存个人过滤器，这就是为什么使用 user_filter表，因此作为一个用户，我可以保存多个“搜索”，并从一个快速切换到另一个
匹配项必须实时显示，我的意思是，动态计算，而不是通过任何cronjob，因为用户过滤器总是会改变
应用程序现在要处理的数据量在consumption表中约为35k行，在product表中约为1.5k行。
应用程序所在的服务器是运行mysql的专用服务器（64gbram）
我的性能很好，有3K行消费和100个产品，现在有10K+消费和600个产品，开始从nginx获得网关超时。猜测查询耗时太长。
我已经知道，如果 ON原因有很多条件，它将工作得更快，因为结果集更小，但是如果条件非常宽，它将给出超时，我猜结果行太多。也许连接会产生数百万行。
我想问的是：
为了在两个表之间进行数据的“实时匹配”，我是否走在正确的路径上？使用join是一个好的解决方案吗？我想不出别的办法来做这件事。
除了尝试优化查询和索引之外，我是否可以做一些服务器调整来充分利用服务器硬件？
在另一个项目中做过类似工作的人有没有其他的技巧或技巧？
更新1：在此处添加完整查询，以列出与消耗量匹配的产品：

SELECT t.*,
       count(f.consumption_id) AS matchesCount
FROM `product` t
LEFT JOIN (


SELECT cons.`id` as `consumption_id`, cons.`client_id` as `consumption_client_id`, cons.`material_id` as `consumption_material_id`, cons.`quality_id` as `consumption_quality_id`, cons.`thick` as `consumption_thick`, cons.`thick_max` as `consumption_thick_max`, cons.`width` as `consumption_width`, cons.`width_max` as `consumption_width_max`, cons.`long` as `consumption_long`, cons.`long_max` as `consumption_long_max`, cons.`type` as `consumption_type`, cons.`date_add` as `consumption_date_add`, prod.`id` as `product_id`, prod.`warehouse_id` as `product_warehouse_id`, prod.`code` as `product_code`, prod.`material_id` as `product_material_id`, prod.`quality_id` as `product_quality_id`, prod.`covering_id` as `product_covering_id`, prod.`finish_id` as `product_finish_id`, prod.`thickness` as `product_thickness`, prod.`width` as `product_width`, prod.`tons` as `product_tons` 
      FROM consumption cons 
        INNER JOIN client cli
          ON cli.id=cons.client_id
        LEFT JOIN client_has_group cli_gr
          ON cli_gr.client_id=cons.client_id
        LEFT JOIN product prod
          ON 

          (
            (cons.material_id=prod.material_id)
              OR 
            prod.material_id IN (
              SELECT id FROM material WHERE `equivalence`=(
                  SELECT `equivalence` FROM material WHERE id=cons.material_id
                )
                AND `group`=(
                  SELECT `group` FROM material WHERE id=cons.material_id
                )
            )
          )



             WHERE 1 > 0 AND prod.deleted=0 AND cli.status=1 AND cons.date_add >= '2017-10-08 00:00:00' 
        GROUP BY cons.id, prod.id


) f ON f.product_id=t.id
GROUP BY t.id

查询时间：00:02:41（+0078秒。网络）。
注意：子查询连接单独运行会产生600K行。我正在考虑设法把它分组，以便使它更小。
更新2：通过在子查询中进行计数，从而减少用于联接的结果集，实现了重大改进
基本上，子查询并没有返回600K+行，它只返回与产品或消耗量一样多的行，这取决于您要查找的是什么。为此，matchescount被移到子查询内部而不是外部，group by也被更改，这取决于要显示的列表。
最后的查询现在是这样的：
列出消耗量并计算与每个消耗量匹配的产品：

SELECT SQL_NO_CACHE `t`.*, 
          IFNULL(f.matchesCount, 0) AS matchesCount
   FROM `consumption` `t`
   LEFT JOIN
     (SELECT cons.`id` AS `consumption_id`,
             cons.`client_id` AS `consumption_client_id`,
             cons.`material_id` AS `consumption_material_id`,
             cons.`quality_id` AS `consumption_quality_id`,
             cons.`thick` AS `consumption_thick`,
             cons.`thick_max` AS `consumption_thick_max`,
             cons.`width` AS `consumption_width`,
             cons.`width_max` AS `consumption_width_max`,
             cons.`long` AS `consumption_long`,
             cons.`long_max` AS `consumption_long_max`,
             cons.`type` AS `consumption_type`,
             cons.`date_add` AS `consumption_date_add`,
             prod.`id` AS `product_id`,
             prod.`warehouse_id` AS `product_warehouse_id`,
             prod.`code` AS `product_code`,
             prod.`material_id` AS `product_material_id`,
             prod.`quality_id` AS `product_quality_id`,
             prod.`covering_id` AS `product_covering_id`,
             prod.`finish_id` AS `product_finish_id`,
             prod.`thickness` AS `product_thickness`,
             prod.`width` AS `product_width`,
             prod.`tons` AS `product_tons`,
             count(prod.`id`) AS matchesCount
      FROM consumption cons
      INNER JOIN client cli ON cli.id=cons.client_id
      LEFT JOIN product prod ON ((cons.material_id=prod.material_id)
                                 OR prod.material_id IN
                                   (SELECT id
                                    FROM material
                                    WHERE `equivalence`=
                                        (SELECT `equivalence`
                                         FROM material
                                         WHERE id=cons.material_id )
                                      AND `group`=
                                        (SELECT `group`
                                         FROM material
                                         WHERE id=cons.material_id ) ))
      AND ((cons.quality_id=prod.quality_id)
           OR prod.quality_id IN
             (SELECT id
              FROM quality
              WHERE `equivalence`=
                  (SELECT `equivalence`
                   FROM quality
                   WHERE id=cons.quality_id )
                AND `group`=
                  (SELECT `group`
                   FROM quality
                   WHERE id=cons.quality_id ) ))
      AND (prod.thickness >= (cons.thick - 0.1)
           AND prod.thickness <= (cons.thick_max + 0.1))
      AND (prod.width >= (cons.width - 1000)
           AND prod.width <= (cons.width_max + 1000))
      WHERE 1 > 0
        AND prod.deleted=0
        AND cli.status=1
        AND cons.date_add >= '2017-10-08 00:00:00'
      GROUP BY cons.id) f ON f.consumption_id=t.id

   GROUP BY t.id

列出与每种产品相匹配的产品并计算消耗量：

SELECT SQL_NO_CACHE t.*,
          IFNULL(f.matchesCount, 0) AS matchesCount
   FROM `product` `t`
   LEFT JOIN
     (SELECT cons.`id` AS `consumption_id`,
             cons.`client_id` AS `consumption_client_id`,
             cons.`material_id` AS `consumption_material_id`,
             cons.`quality_id` AS `consumption_quality_id`,
             cons.`thick` AS `consumption_thick`,
             cons.`thick_max` AS `consumption_thick_max`,
             cons.`width` AS `consumption_width`,
             cons.`width_max` AS `consumption_width_max`,
             cons.`long` AS `consumption_long`,
             cons.`long_max` AS `consumption_long_max`,
             cons.`type` AS `consumption_type`,
             cons.`date_add` AS `consumption_date_add`,
             prod.`id` AS `product_id`,
             prod.`warehouse_id` AS `product_warehouse_id`,
             prod.`code` AS `product_code`,
             prod.`material_id` AS `product_material_id`,
             prod.`quality_id` AS `product_quality_id`,
             prod.`covering_id` AS `product_covering_id`,
             prod.`finish_id` AS `product_finish_id`,
             prod.`thickness` AS `product_thickness`,
             prod.`width` AS `product_width`,
             prod.`tons` AS `product_tons`,
             count(cons.`id`) AS matchesCount
      FROM consumption cons
      INNER JOIN client cli ON cli.id=cons.client_id
      LEFT JOIN product prod ON cons.material_id=prod.material_id
      AND cons.quality_id=prod.quality_id
      WHERE 1 > 0
        AND prod.deleted=0
        AND cli.status=1
      GROUP BY prod.id) f ON f.product_id=t.id
   WHERE deleted=0
   GROUP BY t.id

两个查询的执行时间都不到1秒（每个）。
注意：我仍然在我的应用程序中使用前面的查询，例如，当我想要一个与单一消费相匹配的产品列表的细分时，或者相反。在这种情况下，我已经为每个消费id或产品id添加了一个过滤器，它大大减少了结果集的大小。

最佳答案

如果client_has_group是“many:1”，那么这样做是错误的。你不需要额外的桌子。
INT总是4字节。考虑更小的数据类型。最终数据库的大小可能会增加您的问题。
你真的需要date_add和date_upd吗？它们看起来像是你永远不会用到的杂物。
尽可能避免IN ( SELECT ... )。切换到JOIN或EXISTS。
为什么有这么多的表具有代码+组+等价性？他们能成为一个整体吗？你需要全部三列吗？您需要id吗，因为code是UNIQUE？有一点是，模式“过度规范化”，性能受到影响，而对空间没有多大帮助。
OR在某些情况下是性能杀手。
“相关子查询”在某些情况下很有用，但通过aJOIN可能更好：

AND `group` = ( SELECT `group` FROM quality WHERE id=cons.quality_id )

小心带有 COUNT的聚合（例如， JOIN）；您可能会得到一个膨胀值。这是因为 JOIN首先发生。

关于mysql - 优化用于查找两个表之间匹配的MySQL查询，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/52703810/

文章推荐： android - RelativeLayout 问题 - 图像与 ScrollView 重叠

文章推荐： android - 如何获取位置经纬度并从后台服务更新

文章推荐： android - 将android项目导入eclipse时出错

文章推荐： mysql - SQL 根据存储在序列化数组中的流派选择图书

awk - 如果行与“foo”匹配，线上方与“bar”匹配，线下方与“baz”匹配，则删除行？
使用sed和/或awk，仅在行包含字符串“ foo”并且行之前和之后的行分别包含字符串“ bar”和“ baz”时，我才希望删除行。因此，对于此输入： blah blah foo blah bar
c# - 如何按 X% 匹配 2 个字符串(即 >90% 匹配)
例如: S1: "some filename contains few words.txt" S2:“一些文件名包含几个单词 - draft.txt” S3:“一些文件名包含几个单词 - 另一个 dr
R 合并数据帧，允许不精确的 ID 匹配(例如，附加字符 1234 匹配 ab1234)
我正在尝试处理一些非常困惑的数据。我需要通过样本 ID 合并两个包含不同类型数据的大数据框。问题是一张表的样本 ID 有许多不同的格式，但大多数都包含用于匹配其 ID 中某处所需的 ID 字符串，例如
css - 匹配 col-md 时显示 div，匹配 col-sm 时不显示
我想在匹配特定屏幕尺寸时显示特定图像。在这种情况下，对于 Bootstrap ，我使用 col-xx-## 作为我的选择。但似乎它并没有真正按照我认为应该的方式工作。基本思路，我想显示一种全屏图像，
apache - mod_rewrite 问题 : RewriteCond %{REQUEST_FILENAME} ! -f 匹配，即使 REQUEST_FILENAME 不应(完全)匹配
出于某种原因，这条规则 RewriteCond %{REQUEST_FILENAME} !-f RewriteCond %{REQUEST_FILENAME} !-d RewriteRule ^(.*
F# 匹配 ->
我想做类似的东西(Nemerle 语法) def something = match(STT) | 1 with st= "Summ" | 2 with st= "AVG" =>
JavaScript 匹配
假设这是我的代码 var str="abc=1234587;abc=19855284;abc=1234587;abc=19855284;abc=1234587;abc=19855284;abc=123
JavaScript 匹配
我怎样才能得到这个字符串的数字:'(31.5393701, -82.46235569999999)' 我已经在尝试了，但这离解决方案还很远:) text.match(/$(\d+),(\d+)$/
JavaScript 匹配
如何去除输出中的逗号 (,)？有没有更好的方法从字符串或句子中搜索 url。 alert(" http://www.cnn.com df".match(/https?:\/\/([-\w\.]+
Python - 匹配
a = ('one', 'two') b = ('ten', 'ten') z = [('four', 'five', 'six'), ('one', 'two', 'twenty')] 我正在尝试
vba - 循环遍历行和列时的索引/匹配
我已经编写了以下代码，我希望用它来查找从第 21 列到另一张表中最后一行的值，并根据这张表中 A 列和另一张表中 B 列中的值将它们返回到这张表床单。当我使用下面的代码时，我得到一个工作表错误。你能
Excel 匹配 IF 语句未正确评估
我在以下结构中有两列 A B 1 49 4922039670 我已经能够评估 =LEN(A1)如2 , =LEFT(B1,2)如49 , 和 =LEFT(B1,LEN(A1)
基于行首的 Vim 匹配
我有一个文件，其中一行可以以 + 开头, -或 * .在其中一些行之间可以有以字母或数字(一般文本)开头的行(也包含这些字符，但不在第 1 列中!)。知道这一点，设置匹配和突出显示机制的最简单方法是
正则表达式:匹配，但如果在评论中则不匹配
我有一个数据字段文件，其中可能包含注释，如下所示: id, data, data, data 101 a, b, c 102 d, e, f 103 g, h, i // has to do with
匹配 url 的正则表达式模式
我有以下模式:/^\/(?P.+)$/匹配:/url . 我的问题是它也匹配 /url/page ，如何忽略/在这个正则表达式中？该模式应该: 模式匹配:/url 模式不匹配:/url/page 提
r - R中多维度的聚类/匹配
我有一个非常庞大且复杂的数据集，其中包含许多对公司的观察。公司的一些观察是多余的，我需要制作一个键来将多余的观察映射到一个单独的观察。然而，判断他们是否真的代表同一家公司的唯一方法是通过各种变量的相似
xpath 匹配 - 查找值不在值集中的标签是否存在
我有以下 XML A B C 我想查找 if not(exists(//Record/subRecord
javascript - 匹配/不匹配的正则表达式上没有出现警报框？
我制作了一个正则表达式来验证潜在的比特币地址，现在当我单击报价按钮时，我希望根据正则表达式检查表单中输入的值，但它不起作用。 https://jsfiddle.net/arkqdc8a/5/ var
sql - 检查支架是否平衡/匹配
我有一些 MS Word 文档，我已将其全部内容转移到 SQL 表中。内容包含多个方括号和大括号，例如 [{a} as at [b],] {c,} {d,} etc 我需要进行检查以确保括号平衡/匹
JavaScript Unicode 匹配
我正在使用 Node.js 从 XML 文件读取数据。但是当我尝试将文件中的数据与文字进行比较时，它不匹配，即使它看起来相同: const parser: xml2js.Parser = new

可可西里

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

mysql - 优化用于查找两个表之间匹配的MySQL查询