MySQL语句优化

yaotomo 2019-09-08 06:26:48

SQL语句如下



EXPLAIN

SELECT sp.LevelName,

                                        CASE 

                                        WHEN sp.LevelName ='Cultivar' THEN '01' 

                                        WHEN sp.LevelName ='Form' THEN '02'

                                        WHEN sp.LevelName ='Variety' THEN '03'

                                        WHEN sp.LevelName ='Subspecies' THEN '04'

                                        WHEN sp.LevelName ='Species' THEN '05'

                                        WHEN sp.LevelName ='Subseries' THEN '06'

                                        WHEN sp.LevelName ='Series' THEN '07'

                                        WHEN sp.LevelName ='Subsection' THEN '08'

                                        WHEN sp.LevelName ='Section' THEN '09'

                                        WHEN sp.LevelName ='Subgenus' THEN '10'

                                        WHEN sp.LevelName ='Genus' THEN '11'

                                        WHEN sp.LevelName ='Subtribe' THEN '12'

                                        WHEN sp.LevelName ='Tribe' THEN '13'

                                        WHEN sp.LevelName ='Supertribe' THEN '14'

                                        WHEN sp.LevelName ='Subfamily' THEN '15'

                                        WHEN sp.LevelName ='Family' THEN '16'

                                        WHEN sp.LevelName ='Superfamily' THEN '17'

                                        WHEN sp.LevelName ='Parvorder' THEN '18'

                                        WHEN sp.LevelName ='Infraorder' THEN '19'

                                        WHEN sp.LevelName ='Suborder' THEN '20'

                                        WHEN sp.LevelName ='Order' THEN '21'

                                        WHEN sp.LevelName ='Superorder' THEN '22'

                                        WHEN sp.LevelName ='Magnorder' THEN '23'

                                        WHEN sp.LevelName ='Parvcohort' THEN '24'

                                        WHEN sp.LevelName ='Infracohort' THEN '25'

                                        WHEN sp.LevelName ='Subcohort' THEN '26'

                                        WHEN sp.LevelName ='Cohort' THEN '27'

                                        WHEN sp.LevelName ='Supercohort' THEN '28'

                                        WHEN sp.LevelName ='Megacohort' THEN '29'

                                        WHEN sp.LevelName ='Infralegion' THEN '30'

                                        WHEN sp.LevelName ='Sublegion' THEN '31'

                                        WHEN sp.LevelName ='Legion' THEN '32'

                                        WHEN sp.LevelName ='Superlegion' THEN '33'

                                        WHEN sp.LevelName ='Megalegion' THEN '34'

                                        WHEN sp.LevelName ='Parvclass' THEN '35'

                                        WHEN sp.LevelName ='Infraclass' THEN '36'

                                        WHEN sp.LevelName ='Subclass' THEN '37'

                                        WHEN sp.LevelName ='Class' THEN '38'

                                        WHEN sp.LevelName ='Superclass' THEN '39'

                                        WHEN sp.LevelName ='Megaclass' THEN '40'

                                        WHEN sp.LevelName ='Microphylum' THEN '41'

                                        WHEN sp.LevelName ='Infraphylum' THEN '42'

                                        WHEN sp.LevelName ='Subphylum' THEN '43'

                                        WHEN sp.LevelName ='Phylum' THEN '44'

                                        WHEN sp.LevelName ='Superphylum' THEN '45'

                                        WHEN sp.LevelName ='Infrakingdom' THEN '46'

                                        WHEN sp.LevelName ='Subkingdom' THEN '47'

                                        WHEN sp.LevelName ='Kingdom' THEN '48'

                                        WHEN sp.LevelName ='Superkingdom' THEN '49'

                                        WHEN sp.LevelName ='Domain' THEN '50'

                                        ELSE '51'

                                        END as LevelCode,

                                        sp.ID,sp.OK_ID,sp.Family,sp.SName,

                                        sp.Genus,sp.FamilyName,sp.GenusName,sp.CFamilyName,sp.CGenusName,

                                        sp.SPName,sp.Name_La,sp.SAuthor,sp.SNamePrefix,sp.Name_Zh,

                                        sp.ReferDate,sp.Nomen,sp.Preferred,p.ID as PhotoID,count(p.ID) as PhotoCount  

                                        FROM `plantsp` sp LEFT JOIN photo p ON sp.ID = p.SPID where sp.ID in ('4998','11631','732881','732882','732883') 

                                        GROUP BY sp.ID 

                                        ORDER BY LevelCode,PhotoCount desc limit 50

EXPLAIN结果如下

哪些字段加索引可以提升查询速度？
Extra里出现了Using filesort，说明排序没有用到索引，需要怎么加索引才能变成Using Index呢。
还有In查询应该如何优化？
其他的地方还有哪里可以优化的吗？

...全文

126 8 打赏收藏转发到动态举报

写回复

用AI写文章

8 条回复

切换为时间正序

请发表友善的回复…

发表回复

teagueli 2020-07-20

打赏
举报

用了group by 就会Using filesort

yaotomo 2020-07-20

打赏
举报

建立索引。。。

蜕变之痛 2019-10-04

打赏
举报

1.p.spid 建立索引；
2.如果sp.id是主键，增加索引 sp.levelName即可，否则建立组合索引。
3.其它也看出可以优化的。
4.建议用新版mysql，自动优化了不少。你这里重点操作的是临时表，有的建立索引没用。

yaotomo 2019-09-09

打赏
举报

引用 1 楼 AHUA1001 的回复:

把 ON后边的内容，放到WHERE里，当然，这样做，可能会对主表的数据有损失。如果还不行，把IN换成=，IN的5个值，换成UNION ALL关联。

谢谢大佬，请问ON后边的内容，放到WHERE里。和直接用ON的效率，有什么区别？搞不懂。

AHUA1001 2019-09-09

打赏
举报

把 ON后边的内容，放到WHERE里，当然，这样做，可能会对主表的数据有损失。如果还不行，把IN换成=，IN的5个值，换成UNION ALL关联。 SELECT sp.LevelName, CASE WHEN sp.LevelName ='Cultivar' THEN '01' WHEN sp.LevelName ='Form' THEN '02' WHEN sp.LevelName ='Variety' THEN '03' WHEN sp.LevelName ='Subspecies' THEN '04' WHEN sp.LevelName ='Species' THEN '05' WHEN sp.LevelName ='Subseries' THEN '06' WHEN sp.LevelName ='Series' THEN '07' WHEN sp.LevelName ='Subsection' THEN '08' WHEN sp.LevelName ='Section' THEN '09' WHEN sp.LevelName ='Subgenus' THEN '10' WHEN sp.LevelName ='Genus' THEN '11' WHEN sp.LevelName ='Subtribe' THEN '12' WHEN sp.LevelName ='Tribe' THEN '13' WHEN sp.LevelName ='Supertribe' THEN '14' WHEN sp.LevelName ='Subfamily' THEN '15' WHEN sp.LevelName ='Family' THEN '16' WHEN sp.LevelName ='Superfamily' THEN '17' WHEN sp.LevelName ='Parvorder' THEN '18' WHEN sp.LevelName ='Infraorder' THEN '19' WHEN sp.LevelName ='Suborder' THEN '20' WHEN sp.LevelName ='Order' THEN '21' WHEN sp.LevelName ='Superorder' THEN '22' WHEN sp.LevelName ='Magnorder' THEN '23' WHEN sp.LevelName ='Parvcohort' THEN '24' WHEN sp.LevelName ='Infracohort' THEN '25' WHEN sp.LevelName ='Subcohort' THEN '26' WHEN sp.LevelName ='Cohort' THEN '27' WHEN sp.LevelName ='Supercohort' THEN '28' WHEN sp.LevelName ='Megacohort' THEN '29' WHEN sp.LevelName ='Infralegion' THEN '30' WHEN sp.LevelName ='Sublegion' THEN '31' WHEN sp.LevelName ='Legion' THEN '32' WHEN sp.LevelName ='Superlegion' THEN '33' WHEN sp.LevelName ='Megalegion' THEN '34' WHEN sp.LevelName ='Parvclass' THEN '35' WHEN sp.LevelName ='Infraclass' THEN '36' WHEN sp.LevelName ='Subclass' THEN '37' WHEN sp.LevelName ='Class' THEN '38' WHEN sp.LevelName ='Superclass' THEN '39' WHEN sp.LevelName ='Megaclass' THEN '40' WHEN sp.LevelName ='Microphylum' THEN '41' WHEN sp.LevelName ='Infraphylum' THEN '42' WHEN sp.LevelName ='Subphylum' THEN '43' WHEN sp.LevelName ='Phylum' THEN '44' WHEN sp.LevelName ='Superphylum' THEN '45' WHEN sp.LevelName ='Infrakingdom' THEN '46' WHEN sp.LevelName ='Subkingdom' THEN '47' WHEN sp.LevelName ='Kingdom' THEN '48' WHEN sp.LevelName ='Superkingdom' THEN '49' WHEN sp.LevelName ='Domain' THEN '50' ELSE '51' END as LevelCode, sp.ID,sp.OK_ID,sp.Family,sp.SName, sp.Genus,sp.FamilyName,sp.GenusName,sp.CFamilyName,sp.CGenusName, sp.SPName,sp.Name_La,sp.SAuthor,sp.SNamePrefix,sp.Name_Zh, sp.ReferDate,sp.Nomen,sp.Preferred,p.ID as PhotoID,count(p.ID) as PhotoCount FROM `plantsp` sp LEFT JOIN photo p ON sp.ID = p.SPID where sp.ID in ('4998','11631','732881','732882','732883') WHERE sp.ID = p.SPID where sp.ID in ('4998','11631','732881','732882','732883') GROUP BY sp.ID ORDER BY LevelCode,PhotoCount desc limit 50