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

喧嚣丶 2015-11-25 04:54:30
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+,所以需要优化,求大神指导!
...全文
236 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
fcy_n 2015-12-08
  • 打赏
  • 举报
回复
引用 2 楼 u014687647 的回复:
这分别是:查询状态,索引设置,以及分析图,求救
从explain中看出,无索引,使用临时表,全记录排序,结果快不起来,而且行转列显示 解决方法:修改like '%2015%',建立索引,查看临时表是否为物理临时表,如是则临时表参数需要增大,解决filesort,应该可以快一些
喧嚣丶 2015-11-30
  • 打赏
  • 举报
回复
求助啊求助啊
喧嚣丶 2015-11-26
  • 打赏
  • 举报
回复
喧嚣丶 2015-11-26
  • 打赏
  • 举报
回复

这分别是:查询状态,索引设置,以及分析图,求救
ACMAIN_CHM 2015-11-25
  • 打赏
  • 举报
回复
以文本方式贴出 explain select ... show index from .. 以供分析。

679

社区成员

发帖
与我相关
我的任务
社区描述
智能路由器通常具有独立的操作系统,包括OpenWRT、eCos、VxWorks等,可以由用户自行安装各种应用,实现网络和设备的智能化管理。
linuxpython 技术论坛(原bbs)
社区管理员
  • 智能路由器社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧