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

2015-11-25 04:54:30 322 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

深入骨髓的 MySQL 查询性能剖析：从系统负载到单行 DNA 级优化 MySQL查询性能优化全攻略：从系统负载分析到单行优化本文系统介绍了MySQL性能优化的完整方法论。首先从宏观层面讲解服务器负载诊断，包括使用SHOW GLOBAL STATUS、PROCESSLIST等命令进行基础健康检查，以及慢查询日志分析和pt-query-digest工具的使用。接着深入微观层面的SQL优化技术，重点介绍了如何利用MySQL性能模式(Performance Schema)这一强大的内部观测系统进行深度诊断。文章提供了大量可直接用于生产环境的命令、脚本和分析思路，涵盖了从CPU、内存阅读详情

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+，所以需要优化，求大神指导！

...全文