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查询应该如何优化?
其他的地方还有哪里可以优化的吗?

解决方案 »

  1.   

    把 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
                                            
                                            
      

  2.   


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

  3.   

    用了group by 就会Using filesort
      

  4.   

    但是必须要用group by呀
      

  5.   

    但是必须要用group by呀
    你要用就逃不了。。
    但可以从另一方式处理。。看你的语句,其实主要是要数量才用的group;要不你给sp表加个photo数量的字段,photo新增和删除的时候,来更新这个字段。。
    你这里就都不用连表查询了
      

  6.   

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