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

coder 2023-10-12 原文

在我正在进行的一个项目中，我有两个表：
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/

mysql NULL id consumption performance join match

有关mysql - 优化用于查找两个表之间匹配的MySQL查询的更多相关文章

ruby - ECONNRESET (Whois::ConnectionError) - 尝试在 Ruby 中查询 Whois 时出错 - 2
我正在用Ruby编写一个简单的程序来检查域列表是否被占用。基本上它循环遍历列表，并使用以下函数进行检查。require'rubygems'require'whois'defcheck_domain(domain)c=Whois::Client.newc.query("google.com").available?end程序不断出错(即使我在google.com中进行硬编码)，并打印以下消息。鉴于该程序非常简单，我已经没有什么想法了-有什么建议吗？/Library/Ruby/Gems/1.8/gems/whois-2.0.2/lib/whois/server/adapters/base.
ruby-on-rails - Rails 常用字符串(用于通知和错误信息等) - 2
大约一年前，我决定确保每个包含非唯一文本的Flash通知都将从模块中的方法中获取文本。我这样做的最初原因是为了避免一遍又一遍地输入相同的字符串。如果我想更改措辞，我可以在一个地方轻松完成，而且一遍又一遍地重复同一件事而出现拼写错误的可能性也会降低。我最终得到的是这样的:moduleMessagesdefformat_error_messages(errors)errors.map{|attribute,message|"Error:#{attribute.to_s.titleize}#{message}."}enddeferror_message_could_not_find(obje
ruby-on-rails - 如何在 ruby 中使用两个参数异步运行 exe？ - 2
exe应该在我打开页面时运行。异步进程需要运行。有什么方法可以在ruby中使用两个参数异步运行exe吗？我已经尝试过ruby命令-system()、exec()但它正在等待过程完成。我需要用参数启动exe，无需等待进程完成是否有任何rubygems会支持我的问题？最佳答案您可以使用Process.spawn和Process.wait2:pid=Process.spawn'your.exe','--option'#Later...pid,status=Process.wait2pid您的程序将作为解释器的子进程执行。除
ruby-on-rails - Rails 应用程序之间的通信 - 2
我构建了两个需要相互通信和发送文件的Rails应用程序。例如，一个Rails应用程序会发送请求以查看其他应用程序数据库中的表。然后另一个应用程序将呈现该表的json并将其发回。我还希望一个应用程序将存储在其公共(public)目录中的文本文件发送到另一个应用程序的公共(public)目录。我从来没有做过这样的事情，所以我什至不知道从哪里开始。任何帮助，将不胜感激。谢谢! 最佳答案无论Rails是什么，几乎所有Web应用程序都有您的要求，大多数现代Web应用程序都需要相互通信。但是有一个小小的理解需要你坚持下去，网站不应直接访问彼此
ruby 正则表达式 - 如何替换字符串中匹配项的第 n 个实例 - 2
在我的应用程序中，我需要能够找到所有数字子字符串，然后扫描每个子字符串，找到第一个匹配范围(例如5到15之间)的子字符串，并将该实例替换为另一个字符串“X”。我的测试字符串s="1foo100bar10gee1"我的初始模式是1个或多个数字的任何字符串，例如，re=Regexp.new(/\d+/)matches=s.scan(re)给出["1","100","10","1"]如果我想用“X”替换第N个匹配项，并且只替换第N个匹配项，我该怎么做？例如，如果我想替换第三个匹配项“10”(匹配项[2])，我不能只说s[matches[2]]="X"因为它做了两次替换“1fooX0barXg
ruby - 匹配未转义的平衡定界符对 - 2
如何匹配未被反斜杠转义的平衡定界符对(其本身未被反斜杠转义)(无需考虑嵌套)？例如对于反引号，我试过了，但是转义的反引号没有像转义那样工作。regex=/(?!$1:"how\\"#expected"how\\`are"上面的正则表达式不考虑由反斜杠转义并位于反引号前面的反斜杠，但我愿意考虑。StackOverflow如何做到这一点？这样做的目的并不复杂。我有文档文本，其中包括内联代码的反引号，就像StackOverflow一样，我想在HTML文件中显示它，内联代码用一些spanMaterial装饰。不会有嵌套，但转义反引号或转义反斜杠可能出现在任何地方。
ruby-on-rails - 在 Rails 和 ActiveRecord 中查询时忽略某些字段 - 2
我知道我可以指定某些字段来使用pluck查询数据库。ids=Item.where('due_at但是我想知道，是否有一种方法可以指定我想避免从数据库查询的某些字段。某种反拔？posts=Post.where(published:true).do_not_lookup(:enormous_field) 最佳答案 Model#attribute_names应该返回列/属性数组。您可以排除其中一些并传递给pluck或select方法。像这样:posts=Post.where(published:true).select(Post.attr
Ruby Sinatra 配置用于生产和开发 - 2
我已经在Sinatra上创建了应用程序，它代表了一个简单的API。我想在生产和开发上进行部署。我想在部署时选择，是开发还是生产，一些方法的逻辑应该改变，这取决于部署类型。是否有任何想法，如何完成以及解决此问题的一些示例。例子:我有代码get'/api/test'doreturn"Itisdev"end但是在部署到生产环境之后我想在运行/api/test之后看到ItisPROD如何实现？最佳答案根据SinatraDocumentation:EnvironmentscanbesetthroughtheRACK_ENVenvironm
ruby - ＃之间？ Cooper 的 *Beginning Ruby* 中的错误或异常 - 2
在Cooper的书BeginningRuby中，第166页有一个我无法重现的示例。classSongincludeComparableattr_accessor:lengthdef(other)@lengthother.lengthenddefinitialize(song_name,length)@song_name=song_name@length=lengthendenda=Song.new('Rockaroundtheclock',143)b=Song.new('BohemianRhapsody',544)c=Song.new('MinuteWaltz',60)a.betwee
ruby - 当使用::指定模块时，为什么 Ruby 不在更高范围内查找类？ - 2
我刚刚被困在这个问题上一段时间了。以这个基地为例:moduleTopclassTestendmoduleFooendend稍后，我可以通过这样做在Foo中定义扩展Test的类:moduleTopmoduleFooclassSomeTest但是，如果我尝试通过使用::指定模块来最小化缩进:moduleTop::FooclassFailure这失败了:NameError:uninitializedconstantTop::Foo::Test这是一个错误，还是仅仅是Ruby解析变量名的方式的逻辑结果？最佳答案 Isthisabug,or

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

有关mysql - 优化用于查找两个表之间匹配的MySQL查询的更多相关文章

随机推荐