公司做了一个BI系统，以mysql5.6版为查询库，innodb数据引擎，数据库装在一台redhat5的云服务器上，云服务器目前配置8核/16G内存/500G硬盘。目前我们把10几个业务表的数据都整合在一张查询表上，现在一般的页面查询只能达到6-7秒。
客户对这一结果不是很满意，要求达到3-4秒。然后我们在硬件上已经提出要求达到16核/32G内存，同时在SQL语法上也作了优化，也建了索引，但索引已经没什么用了，因为我们的SQL语句很长，条件也比较复杂，无法命中索引，实在不知道 如何提升速度了，其实现在的单表数据量才120几万条。
在这里想问一下达人们有没有好的建议和办法？网上查了不少内容：用高并发数据库、做数据库集群、用MYCAT或greenplum等分布式平台、换ORACLE数据库。
如果有达人做过这类项目，帮我们解决一下。或者留下您的联系方式，一旦解决我们会重谢！
因为客户要求我们做的系统，所有业务必须通过SQL来完成查询和统计。一般的SQL业务如下：
select DISTINCT deptname,deptid,servicename,qlbm,zbjs,jbjbjs,cnjbjs,tqdtbj,tqqt,cndqbj,cgcnqx,cgfdqx,pjfd,pjcn,pjbl,pjtq,tqbjl,dqbjl,cqbjl,cntq,sjtq,service_code from (
select b.DEPTNAME as deptname,b.DEPTID as deptid,b.SERVICENAME as servicename,b.zbjs as zbjs,b.qlbm as qlbm,
b.jbjbjs as jbjbjs,b.cnjbjs as cnjbjs,b.cnjtqdtbj as tqdtbj,b.cnjtqqt as tqqt,
b.cnjcndqbj as cndqbj,b.cnjcgcnqx as cgcnqx,b.cnjcgfdqx as cgfdqx,
IFNULL(ROUND(b.cnjpjfd,2),'0.00') as pjfd,
IFNULL(ROUND(b.cnjpjcn,2),'0.00') as pjcn,
IFNULL(ROUND(b.cnjpjbl,2),'0.00') as pjbl,
IFNULL(ROUND(b.cnjpjtq,2),'0.00') as pjtq,
IFNULL(CONCAT(ROUND(b.cnjtqbjl*100,2),'%'),'0.00%') as tqbjl,
IFNULL(CONCAT(ROUND(b.cnjdqbjl*100,2),'%'),'0.00%') as dqbjl,
IFNULL(CONCAT(ROUND(b.cnjcqbjl*100,2),'%'),'0.00%') as cqbjl,
IFNULL(CONCAT(ROUND(b.cnjcntq*100,2),'%'),'0.00%') as cntq,
IFNULL(CONCAT(ROUND(b.cnjsjtq*100,2),'%'),'0.00%') as sjtq,
b.servicecode as service_code
from
(select
a.DEPTNAME,a.DEPTID,a.SERVICENAME,a.servicecode,count(a.PROJID) as zbjs,
CONCAT_WS('-',A.QL_MAINITEM_ID,A.QL_SUBITEM_ID) as qlbm,
sum(case when a.BJTYPE='1' then 1 else 0 end) as jbjbjs,
sum(case when a.BJTYPE='2' then 1 else 0 end) as cnjbjs,
sum(case when a.BJTYPE='2' and a.ADVANCEDAY>0 and substring(a.ACCEPT_TIME,1,10)=substring(a.TRANSACT_TIME,1,10) then 1 else 0 end) as cnjtqdtbj,
sum(case when a.BJTYPE='2' and a.ADVANCEDAY>0 and substring(a.ACCEPT_TIME,1,10)<>substring(a.TRANSACT_TIME,1,10) then 1 else 0 end) as cnjtqqt,
sum(case when a.BJTYPE='2' and a.ADVANCEDAY=0 then 1 else 0 end) as cnjcndqbj,
sum(case when a.BJTYPE='2' and a.ADVANCEDAY sum(case when a.BJTYPE='2' and a.TIMEADVANCERATE sum(case when a.BJTYPE='2' and a.ANTICIPATE_DAY is not null then a.ANTICIPATE_DAY else 0 end)/sum(case when a.BJTYPE='2' then 1 else 0 end) as cnjpjfd,
sum(case when a.BJTYPE='2' and a.PROMISE_DAY is not null then a.PROMISE_DAY else 0 end)/sum(case when a.BJTYPE='2' then 1 else 0 end) as cnjpjcn,
sum(case when a.BJTYPE='2' then IFNULL(a.PROMISE_DAY,0)-IFNULL(a.ADVANCEDAY,0) else 0 end)/sum(case when a.BJTYPE='2' then 1 else 0 end) as cnjpjbl,
sum(case when a.BJTYPE='2' then a.ADVANCEDAY else 0 end)/sum(case when a.BJTYPE='2' then 1 else 0 end) as cnjpjtq,
sum(case when a.BJTYPE='2' and a.ADVANCEDAY>0 then 1 else 0 end)/sum(case when a.BJTYPE='2' then 1 else 0 end) as cnjtqbjl,
sum(case when a.BJTYPE='2' and a.ADVANCEDAY=0 then 1 else 0 end)/sum(case when a.BJTYPE='2' then 1 else 0 end) as cnjdqbjl,
sum(case when a.BJTYPE='2' and a.ADVANCEDAY sum(case when a.BJTYPE='2' then a.ANTICIPATE_DAY*(case when a.ANTICIPATE_TYPE=1 then 1 when a.ANTICIPATE_TYPE=2 then 30 when a.ANTICIPATE_TYPE=3 then 365 when a.ANTICIPATE_TYPE=4 then 1 else 0 end)-IFNULL(a.PROMISE_DAY,0) else 0 end)/
sum(case when a.BJTYPE='2' then a.ANTICIPATE_DAY*(case when a.ANTICIPATE_TYPE=1 then 1 when a.ANTICIPATE_TYPE=2 then 30 when a.ANTICIPATE_TYPE=3 then 365 when a.ANTICIPATE_TYPE=4 then 1 else 0 end) else 0 end) as cnjcntq,
sum(case when a.BJTYPE='2' then a.ANTICIPATE_DAY*(case when a.ANTICIPATE_TYPE=1 then 1 when a.ANTICIPATE_TYPE=2 then 30 when a.ANTICIPATE_TYPE=3 then 365 when a.ANTICIPATE_TYPE=4 then 1 else 0 end)-IFNULL(a.PROMISE_DAY,0)+IFNULL(a.ADVANCEDAY,0) else 0 end)/
sum(case when a.BJTYPE='2' then a.ANTICIPATE_DAY*(case when a.ANTICIPATE_TYPE=1 then 1 when a.ANTICIPATE_TYPE=2 then 30 when a.ANTICIPATE_TYPE=3 then 365 when a.ANTICIPATE_TYPE=4 then 1 else 0 end) else 0 end) as cnjsjtq
from analyse_bjxxxxb_partition a
where A.DEPTID in ('001008002016','001008002016001','001008002016002','001008002016003','001008002016004','001008002016005','001008002016006','001008002016007','001008002016008','001008002016009','001008002016010','001008002016011','001008002016012','001008002016013','001008002016014','001008002016015','001008002016016','001008002016017','001008002016018','001008002016019','001008002016020','001008002016021','001008002016022','001008002016023','001008002016025','001008002016026','001008002016027','001008002016028','001008002016029','001008002016030','001008002016031','001008002016032','001008002016034','001008002016036','001008002016037','001008002016038','001008002016039','001008002016040','001008002016041','001008002016042','001008002016043','001008002016044','001008002016046','001008002016047','001008002016048','001008002016049','001008002016050','001008002016051','001008002016052','001008002016053','001008002016054','001008002016055','001008002016056','001008002016060','001008002016063','001008002016064','001008002016077','001008002016086','001008002016089','001008002016094','001008002016096','001008002016116','001008002016120','001008002016121','001008002016127','001008002016128','001008002016129','001008002016130','001008002016131','001008002016132','001008002016133','001008002016138','001008002016145','001008002016147','001008002016148','001008002016149','001008002016150','001008002016155','001008002016158','001008002016160','001008002016162','001008002016163','001008002016164') and 1=1
AND substring(a.TRANSACT_TIME,1,10) >= '2016-06-09' and substring(a.TRANSACT_TIME,1,10) <= '2016-07-31'
AND A.SERVICENAME NOT LIKE '$%'
AND a.TRANSACT_RESULT <> '作废办结'
and a.TRANSACT_TIME is not null
and a.ACCEPT_TIME is not null
and ( A.DEPTID in ( '001008002016','001008002016007','001008002016037','001008002016155','001008002016040','001008002016042','001008002016055','001008002016001','001008002016128','001008002016002','001008002016003','001008002016004','001008002016005','001008002016012','001008002016006','001008002016008','001008002016009','001008002016010','001008002016011','001008002016013','001008002016027','001008002016014','001008002016015','001008002016016','001008002016017','001008002016018','001008002016019','001008002016020','001008002016021','001008002016022','001008002016023','001008002016025','001008002016127','001008002016026','001008002016028','001008002016029','001008002016030','001008002016031','001008002016032','001008002016130','001008002016034','001008002016036','001008002016038','001008002016039','001008002016041','001008002016043','001008002016096','001008002016049','001008002016044','001008002016047','001008002016048','001008002016050','001008002016052','001008002016121','001008002016063','001008002016131','001008002016054','001008002016132','001008002016133','001008002016129','001008002016046','001008002016051','001008002016053','001008002016056','001008002016060','001008002016064','001008002016077','001008002016086','001008002016089','001008002016094','001008002016116','001008002016120','001008002016138','001008002016145','001008002016147','001008002016148','001008002016149','001008002016150','001008002016158','001008002016160','001008002016162','001008002016163','001008002016164' ) )
and ( A.ADDRESS_KIND in ( '0' ) or '0' in ('0') )
and (A.ql_kind ='00' or A.ql_kind ='01' or A.ql_kind ='03' or A.ql_kind ='04' or A.ql_kind ='05' or A.ql_kind ='06' or A.ql_kind ='07' or A.ql_kind ='08' or A.ql_kind ='09' or A.ql_kind ='10' or (A.ql_kind ='10' and A.QLFL='其他') or (A.ql_kind ='10' and A.QLFL='年检') or (A.ql_kind ='10' and A.QLFL='其他审批权') or (A.ql_kind ='10' and A.QLFL='备案') or (A.ql_kind ='10' and A.QLFL='监督检查') or A.ql_kind ='13' or A.ql_kind ='14' )
and ( A.SERVICECODE in ( '1' ) or '1' in ( '1' ) )
group by a.SERVICECODE with rollup
) b
) A;
就你这sql 肯定没办法达到要求 我们当时做一款erp也大致是这么个需求 说说我们解决办法吧 两套处理办法
第一种 实时性要求很高的
比如当前订单数 为发货的订单 等等 我们专门给设计了一张表 这张表用来存放它要看的数据 实际操作中 比如下一个订单
除了给订单表里面插入数据 我们也会给这个表里写一份 写入的是客户最想看的 所以我说这个你得改业务 单优化数据库是没用的
第二种 实时性要求不高的
比如 日盘点 我们设计成计划任务 每天晚上跑cornd插入一张汇总表里 他看的时候只给他看汇总后的信息
这两张方法就避免了大规模的连表查询和数据组合 也就是说数据在生成的时候已经写入统计查询了 希望能帮到你
我表示支持楼上看法 这种调整还得从业务入手
就业务上，我们也分析了很久，原先是查17张业务表的，后来通过整合和计算，我们整合成了一张表。然后很多不同的统计和分析页面需求都从这一张表
里去实现的。最近我们就同样的SQL语句在ORACLE里作了测试，发现oracle的查询速度确实比mysql快，所以现在比较纠结。