记一次数据库慢查询的处理方法

1.案发现场

  今天打开系统，发现有个页面一直报接口超时，然后定位到该接口和对应的查询sql，拿到navicat中去执行
  发现执行效率确实很慢，sql和执行时间如下：

SELECT DISTINCT
	r.id,
	r.province,
	r.city,
	r.district,
	r.NAME,
	r.location,
	r.create_time AS createTime,
	s.username AS maintainUser,
	r.update_time AS updateTime,
	m.maintain_user_id AS maintainUserId,
	r.STATUS,
	r.service_life AS serviceLife,
	r.facility_type AS facilityType,
	r.inspector_user_id AS inspectorUserId,
	ins.username AS inspectorUser,
	r.length AS length 
FROM
	t_road r FORCE INDEX ( name_index )
	LEFT JOIN t_road_maintain m ON r.id = m.road_id 
	AND m.delete_flag = 0
	LEFT JOIN sys_user s ON m.maintain_user_id = s.user_id
	LEFT JOIN sys_user ins ON r.inspector_user_id = ins.user_id
	LEFT JOIN t_road_detail t ON t.road_id = r.id
	LEFT JOIN t_driving_point p ON p.longitude = t.longitude 
	AND p.latitude = t.latitude 
	AND p.delete_flag = 0 
WHERE
	r.delete_flag = 0 
	AND r.NAME != '' 
	AND p.car_id IN ( 1, 104, 30, 103, 101, 102, 105, 106 ) 
ORDER BY
	r.id  limit 0, 10

执行耗时，达到了惊人的71.178s

2.解决过程

2.1使用FORCE INDEX(index_name)来强制走索引

首先使用explain查看执行计划，看是不是有查询没有使用索引，具体的执行计划如下：

通过执行计划，我们可以发现p表为全表扫描，索引失效，于是想使用 FORCE INDEX(index_name)
让sql强制走索引，修改后的sql如下：

SELECT DISTINCT
	r.id,
	r.province,
	r.city,
	r.district,
	r.NAME,
	r.location,
	r.create_time AS createTime,
	s.username AS maintainUser,
	r.update_time AS updateTime,
	m.maintain_user_id AS maintainUserId,
	r.STATUS,
	r.service_life AS serviceLife,
	r.facility_type AS facilityType,
	r.inspector_user_id AS inspectorUserId,
	ins.username AS inspectorUser,
	r.length AS length 
FROM
	t_road r FORCE INDEX ( name_index )
	LEFT JOIN t_road_maintain m ON r.id = m.road_id 
	AND m.delete_flag = 0
	LEFT JOIN sys_user s ON m.maintain_user_id = s.user_id
	LEFT JOIN sys_user ins ON r.inspector_user_id = ins.user_id
	LEFT JOIN t_road_detail t ON t.road_id = r.id
	LEFT JOIN t_driving_point p FORCE INDEX(index_car_id) ON p.longitude = t.longitude 
	AND p.latitude = t.latitude 
	AND p.delete_flag = 0 
WHERE
	r.delete_flag = 0 
	AND r.NAME != '' 
	AND p.car_id IN ( 1, 104, 30, 103, 101, 102, 105, 106 ) 
ORDER BY
	r.id  limit 0, 10

修改之后发现效果微乎其微，有所改善，但是效果不显著，执行时间为56.591秒

通过执行计划，我们可以发现修改后的sql确实使用了索引

2.2新建聚集索引

在p表的lon和lat字段上新建一个聚集索引，使用如下sql：

ALTER TABLE t_driving_point ADD INDEX `index_lon_lat` (`longitude`,`latitude`);

添加完上面的联合索引后，发现执行效率快到飞起：耗时0.617s

通过执行计划发现查询使用的是我们添加的联合索引：index_lon_lat

3.总结

发现慢查询之后我们首先通过执行计划看是否索引失效，然后让其使用合适的索引或新建索引，从而解决慢查询问题，以提升sql的执行效率。