背景问题说明
项目中的课程预约记录查询功能,线下门店反馈说进入到页面需要等2分钟,按理来说,现在数据记录数并不大,应该不会到2分钟.看了一下查询sql,发现仅sql的执行时间就已经1分19秒.经过处理,相同的数据,查询用时已经降到34毫秒,虽然不算是最优的处理方式,但是应该能满足门店的使用了.下面就说一下问题处理过程,希望对有sql优化有同样困惑的同学能所有帮助.
问题处理过程
1.表结构说明以及问题sql
sql中涉及表:manage_course_record、manage_staff、manage_staff_card、manage_course_table、manage_course、manage_user_studio、manage_studio；所有表有主键索引，其中仅manage_staff_card中card_no有唯一索引。
问题sql:
SELECT manage_course_record.id course_record_id,manage_staff_card.`card_no`,manage_staff.`real_name` staff_name,manage_staff.`mobile`, DATE_FORMAT(manage_course_record.`start_time`,'%Y-%m-%d %H:%i') start_time, manage_course.`course_name`, manage_user_studio.`user_name` teacher_name,manage_studio.studio_name,manage_studio.id'studioId', manage_course_record.`status`,manage_card.`card_name`,manage_course_record.apply_count,manage_staff_card.card_type,manage_course_record.type course_type ,IF(NOW()>manage_course_record.start_time,TRUE,FALSE) course_start_flag FROM manage_course_record FORCE INDEX(in_type_create_time) LEFT JOIN manage_staff_card ON manage_staff_card.`card_no`=manage_course_record.`card_no` LEFT JOIN manage_staff ON manage_course_record.`login`=manage_staff.`login` LEFT JOIN manage_card ON manage_card.`id`=manage_staff_card.`card_id` LEFT JOIN manage_course_table ON manage_course_table.`id`=manage_course_record.`data_id` AND manage_course_table.`studio_id`=manage_course_record.`studio_id` LEFT JOIN manage_course ON manage_course.`id`=manage_course_table.`course_id` AND manage_course.`studio_id`=manage_course_table.`studio_id` LEFT JOIN manage_user_studio ON manage_user_studio.`login`=manage_course_table.`teacher_id` AND manage_user_studio.`studio_id`=manage_course_table.`studio_id` LEFT JOIN manage_studio ON manage_studio.`id`= manage_course_record.`studio_id` WHERE manage_course_record.`type` IN (1,2) AND manage_staff_card.`studio_id`=manage_course_record.`studio_id` ORDER BY manage_course_record.create_time DESC
2.查看执行计划：
explain 查询sql
发现manage_staff、manage_user_studio中type类型均为all,all执行效率最低,所以进行添加索引.
alter table manage_staff add index index_login (login); alter table manage_user_studio add index index_login_studioId (login,studio_id);
添加之后发现查询时间并没有提高多少.
测试发现执行sql时,添加排序操作与不添加排序操作执行时间相差很大.不添加倒序查询时用时:0.026秒.
SELECT manage_course_record.id course_record_id,manage_staff_card.`card_no`,manage_staff.`real_name` staff_name,manage_staff.`mobile`, DATE_FORMAT(manage_course_record.`start_time`,'%Y-%m-%d %H:%i') start_time, manage_course.`course_name`, manage_user_studio.`user_name` teacher_name,manage_studio.studio_name,manage_studio.id'studioId', manage_course_record.`status`,manage_card.`card_name`,manage_course_record.apply_count,manage_staff_card.card_type,manage_course_record.type course_type ,IF(NOW()>manage_course_record.start_time,TRUE,FALSE) course_start_flag FROM manage_course_record LEFT JOIN manage_staff_card ON manage_staff_card.`card_no`=manage_course_record.`card_no` LEFT JOIN manage_staff ON manage_course_record.`login`=manage_staff.`login` LEFT JOIN manage_card ON manage_card.`id`=manage_staff_card.`card_id` LEFT JOIN manage_course_table ON manage_course_table.`id`=manage_course_record.`data_id` AND manage_course_table.`studio_id`=manage_course_record.`studio_id` LEFT JOIN manage_course ON manage_course.`id`=manage_course_table.`course_id` AND manage_course.`studio_id`=manage_course_table.`studio_id` LEFT JOIN manage_user_studio ON manage_user_studio.`login`=manage_course_table.`teacher_id` AND manage_user_studio.`studio_id`=manage_course_table.`studio_id` LEFT JOIN manage_studio ON manage_studio.`id`= manage_course_record.`studio_id` WHERE manage_course_record.`type` IN (1,2) AND manage_staff_card.`studio_id`=manage_course_record.`studio_id`
发现问题应该是存在于按照创建时间排序上,对manage_course_record中的type与create_time进行添加联合索引.
ALTER TABLE manage_course_record ADD INDEX index_type_create_time (type,create_time);
但是执行计划中type还是显示all.
到这里的时候会有疑问,明明已经加上索引了,但是不能生效.并且排序类型是using filesort,查询资料发现这种按照文件排序的方式效率是很低的.至于添加排序索引之后为何不生效猜测是mysql进行执行查询时内部优化器认为按照创建时间查询不是最优的方式,所以不使用创建时间作为索引进行查询.
关于排序的处理这里想到两种处理方式,一种是排序放到逻辑层中处理;另一种是对manage_course_record表强制使用索引进行查询,在manage_course_record后面添加 FORCE INDEX(index_type_create_time)
执行sql如下:
SELECT manage_course_record.id course_record_id,manage_staff_card.`card_no`,manage_staff.`real_name` staff_name,manage_staff.`mobile`, DATE_FORMAT(manage_course_record.`start_time`,'%Y-%m-%d %H:%i') start_time, manage_course.`course_name`, manage_user_studio.`user_name` teacher_name,manage_studio.studio_name,manage_studio.id'studioId', manage_course_record.`status`,manage_card.`card_name`,manage_course_record.apply_count,manage_staff_card.card_type,manage_course_record.type course_type ,IF(NOW()>manage_course_record.start_time,TRUE,FALSE) course_start_flag FROM manage_course_record FORCE INDEX(in_type_create_time) LEFT JOIN manage_staff_card ON manage_staff_card.`card_no`=manage_course_record.`card_no` LEFT JOIN manage_staff ON manage_course_record.`login`=manage_staff.`login` LEFT JOIN manage_card ON manage_card.`id`=manage_staff_card.`card_id` LEFT JOIN manage_course_table ON manage_course_table.`id`=manage_course_record.`data_id` AND manage_course_table.`studio_id`=manage_course_record.`studio_id` LEFT JOIN manage_course ON manage_course.`id`=manage_course_table.`course_id` AND manage_course.`studio_id`=manage_course_table.`studio_id` LEFT JOIN manage_user_studio ON manage_user_studio.`login`=manage_course_table.`teacher_id` AND manage_user_studio.`studio_id`=manage_course_table.`studio_id` LEFT JOIN manage_studio ON manage_studio.`id`= manage_course_record.`studio_id` WHERE manage_course_record.`type` IN (1,2) AND manage_staff_card.`studio_id`=manage_course_record.`studio_id` ORDER BY manage_course_record.create_time DESC
修改完成之后查询时间为0.035秒.至此,问题解决!
以上是mysql查询时间过慢进行优化的一次记录,希望对有同样需求的同学有所帮助和借鉴!
