mysql一对多关联 mysql一对多关联速度很慢

转载

mob6454cc690811 2023-08-11 15:22:32

文章标签 mysql一对多关联 mysql 数据库 sql 关联查询 文章分类 MySQL 数据库

mysql关联查询过慢问题

文章目录

mysql关联查询过慢问题
前言
二、查询创建新表sql

1.销售表1
2.销售表2
3，查询最终结果

三、查询过慢问题及原因

1，问题
2，原因

前言

因为公司项目需要统计订单数据的差异性，需要对多张千万级订单数据表进行差异比较，一开始通过表数据关联查询到结果然后直接用查询后的结果生成新表，一开始的时候都比较快，千万级的数据查询以及创建新表都是在几百秒的时间生成了结果表，但是在进行生成的新表关联查询的时候是出奇的慢

二、查询创建新表sql

1.销售表1

代码如下（示例）：

create table `清洗表-oms销售订单2`(id int primary key auto_increment)
(SELECT
	oso.sales_order_code AS '单据编号',
	SUBSTRING(os.store_code,5) AS '项目编码',
	os.store_name AS '项目名称',
	DATE_FORMAT(p.paid_time,'%Y-%m-%d') AS '下单日期',
	oso.trade_id AS '平台订单号',
	SUBSTRING_INDEX(oso.trade_id,"__",1) as '平台订单号-标准',
	oso.province_name AS '收货省',
	oso.city_name AS '收货市',
	oso.district_name AS '收货区',
	oso.address AS '收货地址',
	oso.contact AS '收货人',
	oso.mobile AS '收货号码',
	osod.sku_code AS '物料编码',
	osod.sku_name AS '物料名称',
	osod.quantity AS '数量',
	CONVERT(((osod.discount_amount+osod.actual_amount)/osod.quantity),DECIMAL(10,4)) AS '单价',
	(osod.discount_amount+osod.actual_amount) AS '金额',
	oso.reissue_reason AS '补发原因'
FROM
	oms_sales_order oso
	LEFT JOIN oms_sales_order_detail osod ON oso.sales_order_id = osod.sales_order_id
	LEFT JOIN oms_store os ON oso.store_id = os.store_id 
	LEFT JOIN oms_sales_order_payment p ON oso.sales_order_id = p.sales_order_id
	WHERE oso.created_time >= '2021-01-01 00:00:00' AND  oso.created_time < '2023-01-01 00:00:00')

2.销售表2

代码如下（示例）：

create table `清洗表-erp订单2`(id int primary key auto_increment)
(SELECT
	e.cache_id AS '单据编号',
	SUBSTRING(e.store_code,5) AS '项目编码' ,
	s.store_name AS '项目名称',
	DATE_FORMAT(e.mall_paid_time,'%Y-%m-%d') AS '下单日期',
	e.trade_id AS '平台订单号',
	e.province_name AS '收货省份',
	e.city_name AS '收货市',
	e.district_name AS '收货区',
	e.address AS '收货地址',
	e.contact AS '收货人',
	e.mobile AS '收货号码',
	e.mall_product_id AS '平台商品ID',
	e.mall_sku_id AS '平台规格ID',
	e.sku_code AS '物料编码',
	e.sku_name AS '物料名称',
	e.quantity AS '数量',
	e.actual_price AS '单价',
	CONVERT(e.actual_price * e.quantity,DECIMAL(10,4)) '金额' 
FROM
	erp_template_import_cache e LEFT JOIN oms_store s ON e.store_id = s.store_id WHERE cache_id > 12758627
UNION 
SELECT
	e.cache_id AS '单据编号',
	SUBSTRING(e.store_code,5) AS '项目编码' ,
	s.store_name AS '项目名称',
	DATE_FORMAT(e.mall_paid_time,'%Y-%m-%d') AS '下单日期',
	e.trade_id AS '平台订单号',
	e.province_name AS '收货省份',
	e.city_name AS '收货市',
	e.district_name AS '收货区',
	e.address AS '收货地址',
	e.contact AS '收货人',
	e.mobile AS '收货号码',
	e.mall_product_id AS '平台商品ID',
	e.mall_sku_id AS '平台规格ID',
	e.sku_code AS '物料编码',
	e.sku_name AS '物料名称',
	e.quantity AS '数量',
	e.actual_price AS '单价',
	CONVERT(e.actual_price * e.quantity,DECIMAL(10,4)) '金额'
FROM
	erp_template_import_cache_bacup20220524 e LEFT JOIN oms_store s ON e.store_id = s.store_id)

3，查询最终结果

代码如下（示例）：

create table `核对erp订单VSoms销售订单2`(id int primary key auto_increment)
  SELECT
	erp.id AS 'erpID',
	oms.id AS 'omsID',
	erp.`下单日期` AS 'erp下单时间',
	oms.`下单日期` AS 'oms下单时间',
	erp.`项目名称` as '项目名称',
	erp.`平台订单号` ,
	 oms.`平台订单号-标准` AS '平台订单号_标准',
	erp.`物料编码`,
	erp.`物料名称`,
	erp.`数量` as 'erp_订单明细_数量',
	erp.`金额` as 'erp_订单明细_金额',
	oms.`数量` AS 'oms_销售订单_数量',
	oms.`金额` as 'oms_销售订单_金额',
	erp.数量-oms.数量 as '差异_数量',
	erp.金额-oms.`金额` as '差异_金额'
FROM
	`清洗表-erp订单` erp
	LEFT JOIN `清洗表-oms销售订单` oms ON erp.`平台订单号` = oms.`平台订单号-标准`
	AND erp.`物料编码` = oms.`物料编码` 
	AND erp.`项目编码` = oms.`项目编码`
	AND erp.`下单日期` = oms.`下单日期`