在我正在进行的一个项目中,我有两个表: 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+行,它只返回与产品或消费量相同的行,这取决于您要查找的内容。为此,matchescont被移动到子查询内部而不是外部,groupby也被更改,这取决于您要显示的列表。
这就是最终查询现在的样子:
列出消耗量并计算与每种消耗量相匹配的产品:
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添加了一个过滤器,它大大减小了结果集的大小。
2条答案
按热度按时间8e2ybdfx1#
为什么需要left join client\u在cli\u gr.client\u id=cons.client\u id上有\u group cli\u gr它从未使用过
为什么需要按cons.id、prod.id分组?如果选择所有字段,则可能只选择需要的字段
试试这个选择,我想会更快
也许最好在后台进行计算计数,并将此字段添加到产品和消费表中。
g0czyy6m2#
如果
client_has_group
是“many:1“,那是错误的做法。你不需要额外的table。INT
总是4字节。考虑较小的数据类型。最终,数据库的大小可能会增加您的问题。你真的需要吗
date_add
以及date_upd
. 它们看起来像是你永远不会用到的杂物。避免
IN ( SELECT ... )
如果可行的话。切换到JOIN
或者EXISTS
.为什么有这么多表是代码+组+等价的?他们可能是一个群体吗?你需要全部三列吗?你需要吗
id
自code
是UNIQUE
? 在这种情况下,模式会“过度规范化”,性能会受到影响,而不会占用太多空间。OR
在某些情况下是性能杀手。“相关子查询”在某些情况下是有用的,但这一项可能通过
JOIN
:小心骨料(例如,
COUNT
)与JOIN
; 你可能得到一个膨胀的价值。这是因为JOIN
先发生。