首页新闻找找看学习计划

mysql查询速度优化

0
悬赏园豆:5 [待解决问题]

SELECT DISTINCT l.SPID,p.CFamilyName,p.FamilyName,p.CGenusName,p.GenusName,p.Name_Zh,p.Name_La,p.Kingdom FROM location_photo l
LEFT JOIN plantsp p
ON l.SPID = p.ID
WHERE l.LName !=''
查询时间需要56秒,查询结果27833条。

plantsp 表的ID字段和location_photo表的SPID字段都建立了索引
plantsp表ID为主键,索引类型为Normal,索引方式为BTree,数据大概350万条,其中数据基本不会变化,可认为是静态数据
location_photo表的SPID字段不是主键,索引类型为Normal,索引方式为BTree,数据大概500万条,数据会不断插入和更新
这个查询要1分钟左右

SELECT DISTINCT SPID FROM location_photo 只需要1秒种,查询结果27833条
SELECT SPID FROM location_photo 需要67秒
SELECT ID FROM plantsp 需要69秒
SELECT DISTINCT ID FROM plantsp 需要77秒 (是主键这么查没什么意义,只是说明一下查询时间)

请教应该如何优化

寻找薛定谔的猫的主页 寻找薛定谔的猫 | 菜鸟二级 | 园豆:211
提问于:2018-12-06 21:37

感觉你是不是可以使用内连接,然后group by

保镖 1周前

select * from (select distinct spid from location_photo) as a left join plantsp as b on a.spid=b.id 试试

?,???? 1周前

@?,????: 谢谢,确实是个好方法,花费25秒。但还是很慢,用户体验太差。

寻找薛定谔的猫 1周前
< >
分享
所有回答(7)
0

mysql 服务器什么配置?

dudu | 园豆:41694 (高人七级) | 2018-12-06 21:43

16G内存
Intel(R) Xeon(R) CPU E5-2680 v3 @ 2.50GHz 2.49GHz

支持(0) 反对(0) 寻找薛定谔的猫 | 园豆:211 (菜鸟二级) | 2018-12-06 21:48

@寻找薛定谔的猫: SELECT SPID FROM location_photo 会返回多少条记录

支持(0) 反对(0) dudu | 园豆:41694 (高人七级) | 2018-12-06 21:56

@dudu: 500万

支持(0) 反对(0) 寻找薛定谔的猫 | 园豆:211 (菜鸟二级) | 2018-12-07 18:03

@寻找薛定谔的猫: 一次 SELECT 500条数据这么慢是正常的

支持(0) 反对(0) dudu | 园豆:41694 (高人七级) | 2018-12-07 18:06

@dudu: 不是500,是500万。但是SELECT DISTINCT SPID FROM location_photo,结果28000只需要1秒。

支持(0) 反对(0) 寻找薛定谔的猫 | 园豆:211 (菜鸟二级) | 2018-12-08 10:31
0

你的条件是l.LName !='',可以考虑把这个条件先过滤之后,再进行左连接。

幻天芒 | 园豆:36041 (高人七级) | 2018-12-06 22:44

试过,也很慢

支持(0) 反对(0) 寻找薛定谔的猫 | 园豆:211 (菜鸟二级) | 2018-12-07 18:03
0

适当加索引

会长 | 园豆:1410 (小虾三级) | 2018-12-07 09:07

涉及的字段,该加索引的都加过了

支持(0) 反对(0) 寻找薛定谔的猫 | 园豆:211 (菜鸟二级) | 2018-12-07 18:03
0

在location_photo 表列(LName) 上加索引

AYard | 园豆:290 (菜鸟二级) | 2018-12-07 09:16

这个我加过

支持(0) 反对(0) 寻找薛定谔的猫 | 园豆:211 (菜鸟二级) | 2018-12-07 18:04

@寻找薛定谔的猫:
location_photo 表加上联合索引(LName,SPID)
plantsp 表加联合索引(ID,CFamilyName,FamilyName,CGenusName,GenusName,Name_Zh,Name_La,Kingdom)
这样试试。

支持(0) 反对(0) AYard | 园豆:290 (菜鸟二级) | 2018-12-07 18:43
0

distinct 可以换成 group by

Rise-亟 | 园豆:2674 (老鸟四级) | 2018-12-07 09:18

请问应该怎么修改

支持(0) 反对(0) 寻找薛定谔的猫 | 园豆:211 (菜鸟二级) | 2018-12-07 18:06

@寻找薛定谔的猫: distinct 后面跟的列,同样用于group by.
比如,distinct写法:select distinct id from table,
group by写法:select id from table group by id

支持(0) 反对(0) Rise-亟 | 园豆:2674 (老鸟四级) | 2018-12-07 18:20

@Rise-亟: 谢谢回复,查询需要1分钟。

支持(0) 反对(0) 寻找薛定谔的猫 | 园豆:211 (菜鸟二级) | 2018-12-08 10:34
0

l.LName有没有索引?

保镖 | 园豆:317 (菜鸟二级) | 2018-12-07 13:17

后来加过还是不行

支持(0) 反对(0) 寻找薛定谔的猫 | 园豆:211 (菜鸟二级) | 2018-12-07 18:04
0

你可以分组+join的方法

峰芳 | 园豆:211 (菜鸟二级) | 2018-12-09 10:16

请教具体的语句实现?

支持(0) 反对(0) 寻找薛定谔的猫 | 园豆:211 (菜鸟二级) | 2018-12-09 21:43
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册