Mysql 执行查询语句时间长的解决方案
问题描述:
根据业务需要修改了一个sql查询语句,执行后查询时间平均十几秒,sql语句修改前执行时间不到一秒。因此对sql语句进行优化。sql语句如下:
SELECT
p.v_contractname AS contractname
FROM
xq_projectcontract p
LEFT JOIN xq_pub_projecttype pt ON p.v_fprojecttype = pt.v_code
LEFT JOIN bp_pub_position po ON p.v_projectlocation = po.FCODE
LEFT JOIN eauser eu ON eu.userid = p.v_applyperson
LEFT JOIN eadept ed ON ed.deptid = p.v_unitid
LEFT JOIN eadept se ON se.deptid = p.v_send_unitid
LEFT JOIN xq_projectcontract_deptdetail dept ON p.id = dept.v_contractid
LEFT JOIN xq_projectcontract_detail pd ON p.id = pd.v_contractid
LEFT JOIN (
SELECT
attache.SOURCEID,
attache.ATTCHE_TYPE
FROM
cp_attache attache
WHERE
attache.ATTCHE_STATUS = '1'
AND attache.ATTCHE_TYPE = '12'
) attacheresult ON attacheresult.SOURCEID = p.id
WHERE
p.d_checkfinishtime >= '2018-01-01'
AND p.d_checkfinishtime < date_add('2018-04-10', INTERVAL 1 DAY)
AND p.v_status = '0'
GROUP BY
p.id;
红字部分是新业务需要添加的语句,使用两个方法将问题解决:
1.红字部分语句的写法是子查询,使用子查询的话,红字中的cp_attache表的索引将无效,执行效率会大打折扣。所以将语句修改如下:
SELECT
p.v_contractname AS contractname
FROM
xq_projectcontract p
LEFT JOIN xq_pub_projecttype pt ON p.v_fprojecttype = pt.v_code
LEFT JOIN bp_pub_position po ON p.v_projectlocation = po.FCODE
LEFT JOIN eauser eu ON eu.userid = p.v_applyperson
LEFT JOIN eadept ed ON ed.deptid = p.v_unitid
LEFT JOIN eadept se ON se.deptid = p.v_send_unitid
LEFT JOIN xq_projectcontract_deptdetail dept ON p.id = dept.v_contractid
LEFT JOIN xq_projectcontract_detail pd ON p.id = pd.v_contractid
LEFT JOIN cp_attache attache ON attacheresult.SOURCEID = p.id AND attache.ATTCHE_STATUS = '1' AND attache.ATTCHE_TYPE = '12'
WHERE
p.d_checkfinishtime >= '2018-01-01'
AND p.d_checkfinishtime < date_add('2018-04-10', INTERVAL 1 DAY)
AND p.v_status = '0'
GROUP BY
p.id;
修改为红字部分,去掉子查询写法,直接左连接cp_attache表和相关条件,执行后的时间为3秒左右。执行效果还是没有达到预期,依然比修改前的执行时间长,继续优化。
2.查看语句没有问题后,将目标转为主表xq_projectcontract的id字段。原因是cp_attache表的SOURCEID和主表的id字段有关联关系。查看主表的id字段,发现id字段的数据类型是int。添加红字部分语句前执行时间小于1秒,查看其他左连接中有关联关系的字段数据类型,发现都是和主表的字段数据类型相一致。查看cp_attache表的SOURCEID数据类型,发现数据类型是varchar,执行时间长的问题找到。主表的id类型为int,而从表与主表相关联的字段类型是varchar,在查询执行中,数据库引擎需要将两者数据类型进行统一处理再进行查询,执行时间必然变长。所以,将cp_attache从表的SOURCEID数据类型修改为int。再次执行,执行时间小于1秒。解决问题。