explain
select sql_no_cache
'ddd' aac3qwe,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 ;
分析结果如下:
'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', '46339', 'Using where'
'1', 'SIMPLE', 'md', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'csf_maec.mda.data_id', '1', ''大家帮我看看,还有优化的可能吗?现在需要0.250秒(开发人员要求在小于0.1秒就可以了),查询最后的记录为32条。maec_data_attr表180W记录,maec_data表150W记录,item_info表1000多记录。
select sql_no_cache
'ddd' aac3qwe,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 ;
分析结果如下:
'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', '46339', 'Using where'
'1', 'SIMPLE', 'md', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'csf_maec.mda.data_id', '1', ''大家帮我看看,还有优化的可能吗?现在需要0.250秒(开发人员要求在小于0.1秒就可以了),查询最后的记录为32条。maec_data_attr表180W记录,maec_data表150W记录,item_info表1000多记录。
LEFT JOIN能否 修改成INNER JOIN
能提高一点,你的PROFILE 看一下执行顺序按你的情况会扫描 ii,mda,md而不用索引的最好扫描情况是ii,md,mda这样一般会少扫描几十万行,
另外改进用索引,把你的表结构和show index 给看一下
SHOW INDEX 贴出来看看