数据较多 , 而需要联表查询时 , 查询缓慢 --解决

最新推荐文章于 2024-12-29 16:24:09 发布

iteye_12373

最新推荐文章于 2024-12-29 16:24:09 发布

阅读量511

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql 文章标签：联表查询缓慢

本文链接：https://blog.youkuaiyun.com/iteye_12373/article/details/82654585

mysql 专栏收录该内容

3 篇文章

订阅专栏

环境：
mysql5.6.25

现在我有三张表：
services_log（日志表，大约10万条数据），customer（客户表），sys_dict（字典表）

需要显示日志信息、客户名、字典值

原来sql：


SELECT
	a.id AS "id",
	a.autorkey AS "autorkey",
	a.remote_addr AS "remoteAddr",
	a.service_name AS "serviceName",
	a.params AS "params",
	a.create_date AS "createDate",
	a.remarks AS "remarks",
	c.hotel_name AS hotelName,
	b.label AS servicesLabelName
FROM
	services_log a
JOIN sys_dict b ON a.service_name = b.value
JOIN customer c ON a.autorkey = c.author_key
ORDER BY
	a.create_date DESC
LIMIT 30

[quote]受影响的行: 0
时间: 1.612s
[/quote]

分析发现联表查询导致查询时间变长。

更改如下：


SELECT
		a.id AS "id",
		a.autorkey AS "autorkey",
		a.remote_addr AS "remoteAddr",
		a.service_name AS "serviceName",
		a.params AS "params",
		a.create_date AS "createDate",
		a.remarks AS "remarks",
		(select c.hotel_name from customer c where a.autorkey = c.author_key) as hotelName,
		(select b.label from  sys_dict b where a.service_name = b.value) as servicesLabelName
FROM 
		services_log a
ORDER BY 
		a.create_date DESC
LIMIT 30

[quote]受影响的行: 0
时间: 0.451s[/quote]

如果需要加入查询条件：


SELECT
		a.id AS "id",
		a.autorkey AS "autorkey",
		a.remote_addr AS "remoteAddr",
		a.service_name AS "serviceName",
		a.params AS "params",
		a.create_date AS "createDate",
		a.remarks AS "remarks",
		(select c.hotel_name from customer c where a.autorkey = c.author_key) as hotelName,
		(select b.label from  sys_dict b where a.service_name = b.value) as servicesLabelName
FROM 
		services_log a
WHERE a.autorkey in (select id from customer where hotel_name like '%长青国际酒店%' )
ORDER BY 
		a.create_date DESC
LIMIT 30