前言:
今天开发过程中遇到一个SQL问题,现象是两个表left join关联,数据量都不大,关联的ID都创建了索引。但是执行完需要10.971 s,有点不可思议。
排查过程:
原SQL如下:
SELECT
my400cr.id metaDimensionId,
date(my400cr.count_begin_time) creditDate,
12 creditType,
'开班电话' creditName,
my400cr.cp_name userName,
my400cr.college_id collegeId,
my400cr.family_id familyId,
my400cr.group_id groupId,
my400cr.college_name collegeName,
my400cr.family_name familyName,
my400cr.group_name groupName,
my400cr.stu_name stuName,
my400cr.stu_id stuId
FROM
meta_yx_exam_record my400cr
LEFT JOIN appeal a ON my400cr.stu_id = a.stu_id
AND my400cr.ord_id = a.ord_id
AND my400cr.count_begin_time = a.count_begin_time
AND a.type = 1
LEFT JOIN credit_appeal ca ON ca.meta_dimension_id = my400cr.id
WHERE
a.id IS NULL
AND ifnull(ca.id, 0) = 0
AND my400cr.exam_time IS NULL
AND my400cr.college_id IS NOT NULL
AND my400cr.display = 1
AND my400cr.count_begin_time >= CONCAT('2019-12-27', ' 00:00:00')
AND my400cr.count_begin_time <= CONCAT('2020-01-02', ' 23:59:59');
执行计划如下:
可见IDX_CREDIT_APPEAL_META_DIMENSION_ID索引没有生效,这是一个简单的左连接查询,后经查看表结构发现,关联的ID类型并不一致。
ca.meta_dimension_id = my400cr.id
ca表的meta_dimension_id字段类型为varchar,而my400r表的主键ID类型为int。于是将关联的SQL稍作修改,修改部分如下:
LEFT JOIN credit_appeal ca ON ca.meta_dimension_id = convert(my400cr.id, char)
执行SQL语句发现执行时间依旧是10s多,再次做修改:
LEFT JOIN credit_appeal ca ON ca.meta_dimension_id = cast(my400cr.id as char)
依然执行很慢。
最后考虑用concat函数将int类型字段转为varchar,如下:
LEFT JOIN credit_appeal ca ON ca.meta_dimension_id = concat(my400cr.id, '')
果然,执行时间变为200ms,效率提升50倍。