千万表级联查询优化

需求背景：
某大型物流公司CRM提供可视化营销功能，一线员工可以在界面中直接按条件搜索到本门店下属各个用户：潜在客户，大客户，散户等，并显示用户的各种数据：基本信息，来源信息，花费金额，花费频率，回访信息，统计报表等数据；
涉及到表 ：用户表基本信息表，用户附加信息表，订单表，用户来源表
表基数：用户表1000万，附加信息3000万，订单表5000万，用户来源表1000万
正式进入查询阶段：
第一阶段sql查询：在数据库中添加相应索引，通过条件减少sql执行时，搜索范围
优化结果：12秒完成一次搜索
第二阶段数据表结构优化：相应的表添加分区，以部门为分区单位进行range分区，
优化结果：5秒完成一次搜索
压测结果：30并发下，数据库CPU消耗达到85%
成本增加：多张表分区的维护成本：分区带来在线重定义工作，分区后全局索引重建，分区索引重建等工作量
第三阶段数据表结构优化： 在1，2基础上，增加统计数据部分的物化视图，中间表
优化结果：1.2秒完成一次搜索，
压测结果：通过50并发（300次请求每秒）压测，数据库CPU消耗在70%以内，其他指标正常
成本增加： 中间表的维护，增量更新脚本，全量恢复脚本；物化视图的维护成本，增量更新脚本

一个半月终于将这么看似庞大且操蛋的需求完成了，过程中基本天天泡在DBA哪里，优化sql，优化数据表，任何一个又可能的过程都不会放过，险些转行做了DBA；当时感觉是一个很屌的一个成果，但是现在看来，很多需要反思之处！！！
总结一下：对于千万级别的数据而已，说大不大说小不小，一般这个级别SQL本身的查询优化以及很乏力了，需要依靠数据表结构 数据库层的一些优化才能够到达预期的目标，而且会因为这些的增加，增加很多其他的维护成本：多出来很多中间表，中间表的维护脚本，增量更新的脚步等等，有时候也要考虑一下这些成本的增加是否值得或者适合，或者可以考虑一下新型的技术来解决比如spark/strom等新型的数据处理技术，但是这样成本会更高一点点，需要全面的权衡
--------------------------------------------------------
2016-8-29更新
对于业务类型的表的设计，建议使用关系型数据库，日志类的表可以用非关系型的数据来代替
关系型数据库，在使用时的关联操作，最好是同一个业务模型内的表，这样做的好处，为后面表的分库带来很好的可能，这个可能一个是指需要，一个指可行性，如果开始就这去注意，也不必担心上述各种分区，更新等成本的增加，因为使用分库 分表会更加方便；
那么怎么理解同一个业务模型：如用户表，用户附加信息表，用户基本信息表，这都是用户模型内的，这三种表之间的关联查询可能性很大，如果三张表都基于userid 做分表，其实就可以保证，某一个userid 传输过来，可以很确定的对应三张表的那些子表，直接进行关联查询，这样是没有问题；再比如有一张表，商品的领取记录，这个表分表的维度如果是userid，其实也好理解，现在有一个需求，查询某个用户兑换的商品的基本信息，这个时候涉及到两个问题，一个是用户的兑换记录，一个是商品基本信息，现在有商品业务模型（商品基本信息），用户基本信息，用户兑换表（用户模型），userid 传输过来可以定位到用户的兑换记录所在子表，查询出goodIdList,这个是一批商品id，但是这批id不能直接关联商品基本信息表，应该取出来，根据goodid定位分表并查询结果，这个时候，整个查询被分为两个部分：查询用户兑换的商品id，根据商品id 查询商品基本信息，项目伊始养成减少不必要的表关联，还是必要的，说到这里，项目中的DAL层也是必要的，可以做表库的路由，层次比较分明一点