mysql从5.6升级8.0后,sql查询变慢

阿梓 发布于 2020-03-16 14:36
阅读 46
收藏 0
分类:MySQL

听说mysql8比mysql5要快很多,我们就尝试将mysql从5.6升级到8.0,然后有些sql就变慢了

sql如下:

SELECT
   a.id AS 'id',
   CONCAT(a.firstname,' ',a.lastname) AS 'name',
   a.name AS fullName,
   a.firstname AS 'firstname',
   a.lastname AS 'lastname',
   a.teaching_style as teachingStyle,
   a.type_of_english as typeOfEnglish,
   a.avatar AS 'avatar',
   a.avatar_url AS 'avatarurl',
   c.course_id AS courseId,
   CONCAT(
   IFNULL(a.favorable_rate, '100'),
   '%'
   ) AS rate,
   f.file_path AS path
  FROM
  busi_teacher a
  LEFT JOIN busi_tea_schedule b  ON a.id = b.tea_id
  LEFT JOIN busi_tea_course c ON (a.id = c.tea_id AND c.issys = 1)
  LEFT JOIN busi_stu_course d ON b.stu_course = d.id
  LEFT JOIN busi_file f ON f.id = a.avatar
  WHERE   b. STATUS = 1
  AND b.can_normal = 1
  AND ( (a.is_fulltime = 1 AND b.is_contracted = 1) OR a.is_fulltime != 1 )
  AND b.ex_schedule_date > DATE_ADD(NOW(), INTERVAL 24 HOUR)
  AND (b.ex_schedule_date < '2019-12-25 08:00:00' OR b.ex_schedule_date > '2019-12-27 07:59:00')
  AND (b.ex_schedule_date < '2020-01-01 08:00:00' OR b.ex_schedule_date > '2020-01-02 07:59:00')
   AND a. STATUS IN (3, 7)
  AND b.del_flag = 0
  AND a.del_flag = 0
  AND a.isuse = 1
  AND (b.stu_course IS NULL OR d.course_status = 3)
  AND  FIND_IN_SET('152',c.course_id)
    AND HOUR(b.ex_schedule_date) < 21
    GROUP BY a.id
  ORDER BY b.is_contracted DESC, a.promoted DESC,(CASE a.is_fulltime WHEN 1 THEN 1 WHEN 0 THEN 2 ELSE 3 END),a.firstname
  LIMIT  10;

mysql版本5.6.16-log:执行0.157s,explain如下

up-241e59b6556c3035de3c5b00420006376dd.png

up-88b6df7fe9fd90c05c914e8763339cd2948.png

mysql版本8.0.16:执行2.080s,explain如下

up-06593bfbafd4c908443f2af7f60b1162a2a.png

up-d0ef442beab9c7849e0f00b3c134489cb5b.png

mysql8先执行a表,mysql5.6先执行b表。mysql8的Creating sort index花费了99%的时间,我将排序去掉后两个版本都执行一遍后,mysql8是要比mysql5.6要快,但是加上排序后mysql8就很慢了,感觉是在mysql8中排序并没有用到索引。有没有大神能看出来是哪儿出了问题吗,是mysql系统参数设置有问题吗?

最佳答案 2020-03-24 17:10

539
阿梓
阿梓

总结了一下,可能的原因有:

1.换mysql的时候5.6硬件的8核变为了8.0硬件的4核,虽然价格只便宜了100,但是应该是有影响的。

2.mysql5.6和8.0的参数配置不一样,8.0配置数量明显比5.6要多。

3.mysql5.6和mysql8.0的底层优化方式不同,就比如这个明显表执行顺序就不同。所以,这个问题就搁浅了,除非公司请一个专业的DBA。

请先 登录 后评论