优化用于查找两个表之间匹配的MySQL查询
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了优化用于查找两个表之间匹配的MySQL查询，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含18010字，纯文字阅读大概需要26分钟。
内容图文
在我正在进行的项目中,我有两个表：
>消费：包含来自客户的历史订单,其中包含指定其购买产品功能的字段(每行一个产品)
>产品：包含当前产品库存
数据库引擎是InnoDB.
目标：
>申请表必须显示双方的匹配,我的意思是：
>当我列出当前产品库存时,我想显示一列,显示有多少历史订单与特定产品匹配
>当我列出历史订单时,我想查看有多少产品与特定历史订单匹配
消费和产品表以及其他相关表的数据库结构：
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
;
我做了什么才能找到两个表之间的匹配：
我在消费和产品表之间创建了一个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
)
)
)
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
其他说明/注意事项：
>应用程序在两个表中使用两个具有相同名称的字段,以便在JOIN中使用ON查找匹配项
>该应用程序还使用更复杂的业务逻辑,例如,产品材料可以相等,也可以位于等价表或组内
>用户可以保存个人过滤器,这就是使用user_filter表的原因,因此用户可以保存多个“搜索”并快速从一个切换到另一个
>匹配必须显示LIVE,我的意思是,动态计算,而不是任何cronjob,因为用户过滤器将始终更改
>现在应用程序将使用的数据量将在消耗表中约为35k行,在产品表中约为1.5k行
>托管应用程序的服务器是运行MySQL的专用服务器(64GB RAM)
我有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(0,078秒网络).
注意：子查询JOIN单独运行会产生600k行.我想尝试以某种方式对其进行分组以使其变小.
更新2：通过在子查询中进行计数并因此减少用于JOIN的结果集来实现重大改进
基本上是子查询而不是返回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.
为什么这么多表与代码组等价？他们可以成为一个团体吗？你需要所有3列吗？你需要id,因为代码是独一无二的吗？在某种程度上,架构“过度规范化”,性能受到影响而没有太多帮助空间.
OR在某些情况下是性能杀手.
“相关子查询”在某些情况下很有用,但是这个通过JOIN可能更好：
AND `group` = ( SELECT `group` FROM quality WHERE id=cons.quality_id )
注意JOIN的聚合(例如,COUNT);你可能会得到一个夸大的价值.这是因为JOIN首先发生.
内容总结
以上是互联网集市为您收集整理的优化用于查找两个表之间匹配的MySQL查询全部内容，希望文章能够帮你解决优化用于查找两个表之间匹配的MySQL查询所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】