MySQL查询性能、EXPLAIN执行计划

首先,是这么一段执行时间24秒的sql:

SQL 1:

SELECT
	d.id,
	d.project_code,
	d.push_type,
	d.push_name,
	d.push_to,
	d.push_from,
	d.push_way,
	tr.isread,
	d.push_content,
	d.push_attachment,
	d.add_time,
	d.push_status,
	d.push_reviewer,
	push_time,
	d.reviewer_time,
	d.push_class,
	d.rel_object,
	p.short_name AS project_name,
	u1. NAME AS push_to_name,
	u2. NAME AS push_from_name,
	u3. NAME AS push_reviewer_name
FROM tb_push_data d
    LEFT JOIN tb_project p ON p.short_code = d.project_code
    LEFT JOIN tb_user u1 ON u1. CODE = d.push_to
    LEFT JOIN tb_user u2 ON u2. CODE = d.push_from
    LEFT JOIN tb_user u3 ON u3. CODE = d.push_reviewer
    LEFT JOIN tb_push_log tr ON tr.pushdata_id = d.id
WHERE 1 = 1
    AND d.add_time BETWEEN '2017-01-01' and '2018-01-11'
GROUP BY d.id
LIMIT 0,100

受影响的行: 0
时间: 24.933s

可以看到,仅仅是100条数据,查询时间竟然高达24秒,于是我尝试将左外连接改为交叉连接:

SQL 2:

SELECT
	d.id,
	d.project_code,
	d.push_type,
	d.push_name,
	d.push_to,
	d.push_from,
	d.push_way,
	tr.isread,
	d.push_content,
	d.push_attachment,
	d.add_time,
	d.push_status,
	d.push_reviewer,
	push_time,
	d.reviewer_time,
	d.push_class,
	d.rel_object,
	p.short_name AS project_name,
	u1. NAME AS push_to_name,
	u2. NAME AS push_from_name,
	u3. NAME AS push_reviewer_name
FROM 
	tb_push_data d,
	tb_project p,
	tb_user u1,
	tb_user u2,
	tb_user u3,
	tb_push_log tr
WHERE 1 = 1
	and p.short_code = d.project_code
	and u1. CODE = d.push_to
	and u2. CODE = d.push_from
	and u3. CODE = d.push_reviewer
	and tr.pushdata_id = d.id
	AND d.add_time BETWEEN '2017-01-01' and '2018-01-11'
GROUP BY d.id
LIMIT 0,100

受影响的行: 0
时间: 0.189s

这一下查询时间终于算是正常了,看来很多人说的数据库查询不用外键不用join还是有道理的。
但是这又出现了一个问题,因为SQL 2没有使用外连接,比如我某条数据是没有推送人的,那这条记录都显示不出来了。

那么,针对目前的这种情况,该如何优化?

首先,我陷入了两个误区:

  1. 这个限制了只返回100条,并不是满足条件的只有100条,所以不能说数据量小
  2. 至于 LEFT JOIN交叉连接,两者意义不一样,查询计划不一样,满足条件的数据量也不一样,所以没有可比性。需要的数据出不来了,所以明显是少了,少了数据,速度变更快不正常么?

解决方案

对于这种优化的问题,应该先拿到执行计划:EXPLAIN
也就是在查询sql的前面加上EXPLAIN,得到如下结果:

image_1c3kruqi21t54mq211d911715s7m.png-41.8kB

可以看到,LEFT JOIN tb_push_log tr ON tr.pushdata_id = d.id 这个没走索引,而且看起来数据量不少,那么就要先在上面加索引试试:

alter table tb_push_log add key(pushdata_id)

问题解决!

posted on 2018-01-12 17:46  Yoooshiki  阅读(198)  评论(0编辑  收藏  举报

导航