Select 'ddd' b,ii.item_id, ii.item_code, ii.item_cn_name, ii.item_en_name,
year(mda.period_start_date) y, month(mda.period_end_date) m, mda.metadata_type, mda.metadata_code,
mda.period_type, mda.data_id, md.data_value, mda.data_attr_code, mda.data_scale_code, mda.data_unit_code
FROM csf_maec.maec_data_attr mda LEFT JOIN csf_maec.item_info ii ON mda.item_id=ii.item_id
LEFT JOIN csf_maec.maec_data md ON mda.data_id = md.data_id
WHERE ii.item_code ='001.001.001.002'
AND mda.period_type = 'Y'
and mda.data_attr_code = 'CV'
and (
(mda.metadata_type = 'PRICE' AND mda.metadata_code='CURRENT')
or
(mda.metadata_type = 'AREA' AND mda.metadata_code='PBJ')
or
(mda.metadata_type = 'INDUSTRY' AND mda.metadata_code='INDUSTRY_1ST')
)
GROUP BY mda.data_id
HAVING count(mda.data_id)=3 ;explain之后的结论如下:'1', 'SIMPLE', 'ii', 'const', 'PRIMARY,idx_item_info_unq,idx_item_code', 'idx_item_info_unq', '62', 'const', '1', 'Using temporary; Using filesort'
'1', 'SIMPLE', 'mda', 'ref', 'item_id', 'item_id', '4', 'const', '45672', 'Using where'
'1', 'SIMPLE', 'md', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'csf_maec.mda.data_id', '1', ''maec_data_attr表数据量180W,maec_data表140W,item_info表1000条,整个sql查询出来结果是32条记录。
year(mda.period_start_date) y, month(mda.period_end_date) m, mda.metadata_type, mda.metadata_code,
mda.period_type, mda.data_id, md.data_value, mda.data_attr_code, mda.data_scale_code, mda.data_unit_code
FROM csf_maec.maec_data_attr mda LEFT JOIN csf_maec.item_info ii ON mda.item_id=ii.item_id
LEFT JOIN csf_maec.maec_data md ON mda.data_id = md.data_id
WHERE ii.item_code ='001.001.001.002'
AND mda.period_type = 'Y'
and mda.data_attr_code = 'CV'
and (
(mda.metadata_type = 'PRICE' AND mda.metadata_code='CURRENT')
or
(mda.metadata_type = 'AREA' AND mda.metadata_code='PBJ')
or
(mda.metadata_type = 'INDUSTRY' AND mda.metadata_code='INDUSTRY_1ST')
)
GROUP BY mda.data_id
HAVING count(mda.data_id)=3 ;explain之后的结论如下:'1', 'SIMPLE', 'ii', 'const', 'PRIMARY,idx_item_info_unq,idx_item_code', 'idx_item_info_unq', '62', 'const', '1', 'Using temporary; Using filesort'
'1', 'SIMPLE', 'mda', 'ref', 'item_id', 'item_id', '4', 'const', '45672', 'Using where'
'1', 'SIMPLE', 'md', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'csf_maec.mda.data_id', '1', ''maec_data_attr表数据量180W,maec_data表140W,item_info表1000条,整个sql查询出来结果是32条记录。
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货