原始sql,由于smdm_item表20多万数据,线上查询近10秒
SELECT
ccc.*
, spl2.entered_tax_included_price AS last_purchase_price,
sp.year_pc_num
FROM
(
SELECT
si.item_id,
si.item_id AS partner_item_id,
si.item_code,
hit.item_name,
si.item_number,
si.chart_code,
si.drawing_version,
si.common_name,
si.specifications,
si.model,
si.brand,
si.primary_uom_id,
hu.uom_code,
hu.uom_name,
sic.category_id,
sic.category_code,
sic.category_name,
hu.uom_name AS primary_uom_name,
hu.uom_code AS primary_uom_code,
si.order_uom_id,
hup.uom_name AS order_uom_name,
hup.uom_code AS order_uom_code,
si.source_code,
si.enabled_flag,
si.query_item_code,
st.tax_id,
st.description AS tax_description,
st.tax_code,
st.tax_rate,
si.item_abc AS item_abc_class,
si.planned_price,
ss.processed_date AS processed_date,
ss.po_header_id,
ss.po_line_id
FROM
smdm_item si
LEFT JOIN smdm_item_tl hit ON hit.item_id = si.item_id
AND hit.lang = 'zh_CN'
JOIN smdm_uom hu ON si.primary_uom_id = hu.uom_id
LEFT JOIN smdm_uom hup ON si.order_uom_id = hup.uom_id
LEFT JOIN smdm_item_category_assign sica ON si.item_id = sica.item_id
AND (
sica.default_flag = 1
OR (
1 = (
SELECT
count(1)
FROM
smdm_item_category_assign ssss
WHERE
ssss.item_id = si.item_id
)
)
)
LEFT JOIN smdm_item_category sic ON sica.category_id = sic.category_id
LEFT JOIN smdm_tax st ON si.tax_id = st.tax_id
LEFT JOIN (
SELECT
max(sppa.processed_date) AS processed_date,
MAX(sppa.po_header_id) AS po_header_id,
MIN(spl.po_line_id) AS po_line_id,
spl.item_id
FROM
sodr_po_line spl,
sodr_po_process_action sppa,
sodr_po_header sph
WHERE
spl.tenant_id = 2
AND spl.po_header_id = sppa.po_header_id
AND spl.po_header_id = sph.po_header_id
AND sph.confirmed_flag = 1
AND sph.company_id = 5
AND sppa.process_type_code = 'CONFIRM'
GROUP BY
spl.item_id
) ss ON si.item_id = ss.item_id
WHERE
si.tenant_id = 2
AND si.enabled_flag = 1
) ccc
LEFT JOIN sodr_po_line spl2 ON ccc.po_line_id = spl2.po_line_id
LEFT JOIN (
SELECT
sph.attribute_varchar5 AS year_pc_num,
sps.item_code
FROM
spcm_pc_header sph
JOIN spcm_pc_subject sps ON sps.pc_header_id = sph.pc_header_id
JOIN spcm_pc_type spt ON spt.pc_type_id = sph.pc_type_id
WHERE
sph.tenant_id = 2
AND sph.company_id = 5
AND sph.supplier_company_id = 35
and sph.pc_status_code = 'CONFIRMED'
AND sph.attribute_varchar5 LIKE 'NY%'
AND spt.pc_type_code = 'KJXY-A'
GROUP BY
sps.item_code
) sp ON sp.item_code = ccc.item_code
ORDER BY
ccc.partner_item_id DESC
LIMIT 10
主要看这个子查询,先注释掉两个条件发现查询是毫秒级:
查看执行过程:
调试过程中发现只要加上这两个条件其中之一,无论怎么改sql以及索引都无法优化
后发现将company_id改成其他的值
大数据表smdm_item si使用的索引发生了改变
推测原因:left join后的子查询的数据量会影响主表的索引使用
将主表强制使用主键索引解决问题smdm_item si FORCE INDEX(PRIMARY)
修改后sql:
SELECT
ccc.*
, spl2.entered_tax_included_price AS last_purchase_price,
sp.year_pc_num
FROM
(
SELECT
si.item_id,
si.item_id AS partner_item_id,
si.item_code,
hit.item_name,
si.item_number,
si.chart_code,
si.drawing_version,
si.common_name,
si.specifications,
si.model,
si.brand,
si.primary_uom_id,
hu.uom_code,
hu.uom_name,
sic.category_id,
sic.category_code,
sic.category_name,
hu.uom_name AS primary_uom_name,
hu.uom_code AS primary_uom_code,
si.order_uom_id,
hup.uom_name AS order_uom_name,
hup.uom_code AS order_uom_code,
si.source_code,
si.enabled_flag,
si.query_item_code,
st.tax_id,
st.description AS tax_description,
st.tax_code,
st.tax_rate,
si.item_abc AS item_abc_class,
si.planned_price,
ss.processed_date AS processed_date,
ss.po_header_id,
ss.po_line_id
FROM
smdm_item si FORCE INDEX(PRIMARY)
LEFT JOIN smdm_item_tl hit ON hit.item_id = si.item_id
AND hit.lang = 'zh_CN'
JOIN smdm_uom hu ON si.primary_uom_id = hu.uom_id
LEFT JOIN smdm_uom hup ON si.order_uom_id = hup.uom_id
LEFT JOIN smdm_item_category_assign sica ON si.item_id = sica.item_id
AND (
sica.default_flag = 1
OR (
1 = (
SELECT
count(1)
FROM
smdm_item_category_assign ssss
WHERE
ssss.item_id = si.item_id
)
)
)
LEFT JOIN smdm_item_category sic ON sica.category_id = sic.category_id
LEFT JOIN smdm_tax st ON si.tax_id = st.tax_id
LEFT JOIN (
SELECT
max(sppa.processed_date) AS processed_date,
MAX(sppa.po_header_id) AS po_header_id,
MIN(spl.po_line_id) AS po_line_id,
spl.item_id
FROM
sodr_po_line spl,
sodr_po_process_action sppa,
sodr_po_header sph
WHERE
spl.tenant_id = 2
AND spl.po_header_id = sppa.po_header_id
AND spl.po_header_id = sph.po_header_id
AND sph.confirmed_flag = 1
AND sph.company_id = 5
AND sppa.process_type_code = 'CONFIRM'
GROUP BY
spl.item_id
) ss ON si.item_id = ss.item_id
WHERE
si.tenant_id = 2
AND si.enabled_flag = 1
) ccc
LEFT JOIN sodr_po_line spl2 ON ccc.po_line_id = spl2.po_line_id
LEFT JOIN (
SELECT
sph.attribute_varchar5 AS year_pc_num,
sps.item_code
FROM
spcm_pc_header sph
JOIN spcm_pc_subject sps ON sps.pc_header_id = sph.pc_header_id
JOIN spcm_pc_type spt ON spt.pc_type_id = sph.pc_type_id
WHERE
sph.tenant_id = 2
AND sph.company_id = 5
AND sph.supplier_company_id = 35
and sph.pc_status_code = 'CONFIRMED'
AND sph.attribute_varchar5 LIKE 'NY%'
AND spt.pc_type_code = 'KJXY-A'
GROUP BY
sps.item_code
) sp ON sp.item_code = ccc.item_code
ORDER BY
ccc.partner_item_id DESC
LIMIT 10