mysql 优化实战

 1.优化前sql

 EXPLAIN 
SELECT DISTINCT
t.user_id,
t.work_date,
t.should_start_time,
t.start_time,
checkin_status,
t.should_end_time,
t.end_time,
t.checkout_status,
acomment,
dept.dept_name,
`user`.user_name,
raw.record_address
FROM
`hr_work` t
LEFT JOIN user_user `user` ON `user`.user_id = t.user_id
LEFT JOIN hr_dept dept ON dept.hr_dept_id = `user`.dept_id
LEFT JOIN (
SELECT
CONVERT (MIN(record_time), CHAR) startDate,
CONVERT (Max(record_time), CHAR) endDate,
date_format(record_time, '%Y%m%d') work_date,
record_latitude,
record_longitude,
record_address,
user_id
FROM
hr_work_rawdata
WHERE
1 = 1
GROUP BY
LEFT (record_time, 10)
ORDER BY
record_time ASC
) raw ON raw.work_date = t.work_date
WHERE
`user`.active_flag = 1
 AND t.user_id = '8QI3'
AND (
t.work_date >= '20170701'
AND t.work_date <= '20170726'
)
ORDER BY
t.work_date ASC

2.优化以后

 explain SELECT 
t.user_id,
t.work_date,
t.should_start_time,
t.start_time,
checkin_status,
t.should_end_time,
t.end_time,
t.checkout_status
acomment,
dept.dept_name,
`user`.user_name,
raw.record_address
FROM
`hr_work` t 
STRAIGHT_JOIN user_user `user` ON `user`.user_id = t.user_id
LEFT JOIN hr_dept dept ON dept.hr_dept_id = `user`.dept_id
left  join (
 SELECT


CONVERT (MIN(record_time), CHAR) startDate,
CONVERT (Max(record_time), CHAR) endDate,
create_time,
    record_address
FROM
hr_work_rawdata force index(groupbytime)
WHERE
hr_work_rawdata.create_time>= '20170701'
AND hr_work_rawdata.create_time <= '20170726'
group by hr_work_rawdata.create_time 


) raw ON raw.create_time  = t.work_date




WHERE `user`.active_flag = 1
 AND


t.user_id = '8QI3'
AND (
t.work_date >= '20170701'
AND t.work_date <= '20170726'
)
group by t.user_id,t.work_date order by t.work_date

优化思考：

1.没有确定驱动表，首先用STRAIGHT_JOIN 确定驱动表

2.关于全表扫描，建立组合索引，workdateuser(user_id,work_date),groupbytime(create_time,user_id)

这两部是扫描行数降低

3.distinct，临时表，排序问题，使用group by 代替distinct。distinct真正的使用场景不是去除重复，而是去重重复统计，包括group by 如何使用

https://yq.aliyun.com/ziliao/69592这篇文章介绍的不错