mysql，查询速率优化，来人啊

[喧嚣丶]

1.首先这是一个多行专列的查询，我使用语句拼接完成。
SELECT IFNULL(Region, '港澳台') AS '省份' ,sum(if(MODEL='WR-NET-073-ZI',1,0)) as 'WR-NET-073-ZI',sum(if(MODEL='WR-NET-073(A)-ZI',1,0)) as 'WR-NET-073(A)-ZI',sum(if(MODEL='WA300',1,0)) as 'WA300',sum(if(MODEL='T600R',1,0)) as 'T600R',sum(if(MODEL='STD',1,0)) as 'STD',sum(if(MODEL='PLW350A',1,0)) as 'PLW350A',sum(if(MODEL='PLW350',1,0)) as 'PLW350',sum(if(MODEL='N9',1,0)) as 'N9',sum(if(MODEL='N880RZ',1,0)) as 'N880RZ',sum(if(MODEL='N650R',1,0)) as 'N650R',sum(if(MODEL='N630R',1,0)) as 'N630R',sum(if(MODEL='N610RT',1,0)) as 'N610RT',sum(if(MODEL='N601RT',1,0)) as 'N601RT',sum(if(MODEL='N4',1,0)) as 'N4',sum(if(MODEL='N350RP',1,0)) as 'N350RP',sum(if(MODEL='N350R',1,0)) as 'N350R',sum(if(MODEL='N311RT',1,0)) as 'N311RT',sum(if(MODEL='N302RE',1,0)) as 'N302RE',sum(if(MODEL='N300RU',1,0)) as 'N300RU',sum(if(MODEL='N300RE',1,0)) as 'N300RE',sum(if(MODEL='N211RT',1,0)) as 'N211RT',sum(if(MODEL='N200RE',1,0)) as 'N200RE',sum(if(MODEL='N150RP',1,0)) as 'N150RP',sum(if(MODEL='N150R+',1,0)) as 'N150R+',sum(if(MODEL='N100RE',1,0)) as 'N100RE',sum(if(MODEL='N10',1,0)) as 'N10',sum(if(MODEL='MALATA',1,0)) as 'MALATA',sum(if(MODEL='f4012',1,0)) as 'f4012',sum(if(MODEL='ECOS_STD',1,0)) as 'ECOS_STD',sum(if(MODEL='DONG',1,0)) as 'DONG',sum(if(MODEL='CST',1,0)) as 'CST',sum(if(MODEL='CSE30A',1,0)) as 'CSE30A',sum(if(MODEL='CSB350A',1,0)) as 'CSB350A',sum(if(MODEL='CSB333P',1,0)) as 'CSB333P',sum(if(MODEL='CSB333A',1,0)) as 'CSB333A',sum(if(MODEL='CSB332A',1,0)) as 'CSB332A',sum(if(MODEL='CSB330M',1,0)) as 'CSB330M',sum(if(MODEL='CS342R',1,0)) as 'CS342R',sum(if(MODEL='CS341R',1,0)) as 'CS341R',sum(if(MODEL='CS340R',1,0)) as 'CS340R',sum(if(MODEL='CS160R',1,0)) as 'CS160R',sum(if(MODEL='CS132R',1,0)) as 'CS132R',sum(if(MODEL='CS120RN-13',1,0)) as 'CS120RN-13',sum(if(MODEL='CP0015',1,0)) as 'CP0015',sum(if(MODEL='C750R',1,0)) as 'C750R',sum(if(MODEL='AP0151',1,0)) as 'AP0151',sum(if(MODEL='AC5',1,0)) as 'AC5',sum(if(MODEL='A850R',1,0)) as 'A850R',sum(if(MODEL='A750R',1,0)) as 'A750R',sum(if(MODEL='A700R',1,0)) as 'A700R',sum(if(MODEL='04271',1,0)) as '04271',sum(if(MODEL='04212F',1,0)) as '04212F',sum(if(MODEL='04212',1,0)) as '04212', count(*) as '统计' from deviceinfo where region !='' and Area='' and ACTIVATION_TIME like '%2015%' GROUP BY REGION desc WITH ROLLUP;
2.简化语句例子：
SELECT IFNULL(Region, '港澳台') AS '省份' ,sum(if(MODEL='WR-NET-073-ZI',1,0)) as 'WR-NET-073-ZI', count(*) as '统计' from deviceinfo where region !='' and Area='' and ACTIVATION_TIME like '%2015%' GROUP BY REGION desc WITH ROLLUP;
3.现在数据库数据在30W+，一条语句要1.5S，这还只是查单个，查省份的情况就会极度变慢，大致在5S+，所以需要优化，求大神指导！

[ACMAIN_CHM]

以文本方式贴出 explain select ... show index from .. 以供分析。

[喧嚣丶]

这分别是：查询状态，索引设置，以及分析图，求救

[喧嚣丶]

[喧嚣丶]

求助啊求助啊

[fcy_n]

引用 2 楼 u014687647 的回复:

这分别是：查询状态，索引设置，以及分析图，求救

从explain中看出，无索引，使用临时表，全记录排序，结果快不起来，而且行转列显示 解决方法：修改like '%2015%'，建立索引，查看临时表是否为物理临时表，如是则临时表参数需要增大，解决filesort，应该可以快一些