mysql海量数据作复杂查询时的速度问题

Question

 公司做了一个BI系统，以mysql5.6版为查询库，innodb数据引擎，数据库装在一台redhat5的云服务器上，云服务器目前配置8核/16G内存/500G硬盘。目前我们把10几个业务表的数据都整合在一张查询表上，现在一般的页面查询只能达到6-7秒。
客户对这一结果不是很满意，要求达到3-4秒。然后我们在硬件上已经提出要求达到16核/32G内存，同时在SQL语法上也作了优化，也建了索引，但索引已经没什么用了，因为我们的SQL语句很长，条件也比较复杂，无法命中索引，实在不知道 如何提升速度了，其实现在的单表数据量才120几万条。
    在这里想问一下达人们有没有好的建议和办法？网上查了不少内容：用高并发数据库、做数据库集群、用MYCAT或greenplum等分布式平台、换ORACLE数据库。
如果有达人做过这类项目，帮我们解决一下。或者留下您的联系方式，一旦解决我们会重谢！

Answer 1

因为客户要求我们做的系统，所有业务必须通过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;