1、sql已经无法继续优化
2、数据库表结构设计已经无法继续优化
3、已经做了读写分离，但是性能还是低
4、单日数据量50W左右，不超过100W（超过100W不建议使用本文的做法，后面会讲到）
5、读压力远大于写压力
6、对最近的1到2个月的数据操作频繁、对最近半年的较频繁
7、偶尔会对很久的历史数据进行查询（历史数据不能删）
8、无法避免会进行关联查询
9、分页、排序等功能都必须正常使用
10、没有专门的DBA，或者公司不想运维过于复杂的数据库架构

如果你满足以上需要，那恭喜你，这篇文章应该值得你参考。
为了不浪费大家宝贵的时间，我这篇文章采用倒叙的方法，第一章直接介绍架构调整后的终极版本，第二章开始介绍有哪些其他的架构都被pass掉了，尽量让大家更加认同“终极版本”。当然如果大家有其他想法或者意见都欢迎评论留言。

首先，解释下上图的含义：

• 图中的master是mysql的写库、slave是mysql的读库；cti是数据库的名字，fact_call表存最近2个月的数据，fact_call_6存最近6个月的数据，fact_call_all表存所有数据。
• fact_call_6和fact_call_all的数据每天从fact_call表同步过来，同步完毕后需要删除fact_call表中超过2个月的数据，还需要删除fact_call_6表中超过6个月的数据.
• 注意：需要限制查询最近的超过2个月的数据（如果要查询超过2个月的数据，则不能查今天的数据，因为今天的需要到晚上才能同步到fact_call_6表中）。
• 当然也可以设置为1个月的、2个月的、6个月的、永久的。主要思路是：不分表不分库，做表的冗余存储

其次，mysql的脚本：

• mysql的存储过程脚本

-- 创建同步的存储过程
DELIMITER //
USE `cti`//
DROP PROCEDURE IF EXISTS pro_syn_data//
CREATE PROCEDURE pro_syn_data ()
BEGIN
INSERT INTO `fact_call_6` SELECT * FROM `fact_call` WHERE DATE(report_time) >= DATE( DATE_SUB(NOW(), INTERVAL 1 DAY) ) ;
INSERT INTO `fact_call_all` SELECT * FROM `fact_call` WHERE DATE(report_time) >= DATE( DATE_SUB(NOW(), INTERVAL 1 DAY) ) ;
END//
DELIMITER ;

-- 创建删除的存储过程
DELIMITER //
USE `cti`//
DROP PROCEDURE IF EXISTS pro_clear_data//
CREATE PROCEDURE pro_clear_data ()
BEGIN
DELETE FROM `fact_call` WHERE DATE(report_time) <= DATE( DATE_SUB(NOW(), INTERVAL 3 MONTH) ) ;
DELETE FROM `fact_call_6` WHERE DATE(report_time) <= DATE( DATE_SUB(NOW(), INTERVAL 6 MONTH) ) ;
END//
DELIMITER ;

• mysql的定时器脚本

-- 查询mysql事件是否开启
show variables like 'event_scheduler';
select @@event_scheduler;

-- 开启mysql事件
SET GLOBAL event_scheduler = 1;

-- 创建定时同步的事件
DROP EVENT IF EXISTS `e_pro_syn_data`;
CREATE EVENT `e_pro_syn_data` 
ON SCHEDULE EVERY 1 DAY STARTS '2018-11-12 00:00:01' 
ON COMPLETION NOT PRESERVE ENABLE DO CALL pro_syn_data ();
-- 创建定时删除的事件
DROP EVENT IF EXISTS `e_pro_clear_data`;
CREATE EVENT `e_pro_clear_data` 
ON SCHEDULE EVERY 1 DAY STARTS '2018-11-12 02:00:00' 
ON COMPLETION NOT PRESERVE ENABLE DO CALL pro_clear_data ();

再次，读取分表数据的java的示例代码：
主要思路：在查询fact_call等表前判断应该查哪个表查。
核心代码：
（1）通过时间范围确定表名

	/**
	 * 确定从哪张表中读取数据
	 * @param decisionTime 这是sql中最小的的report_time
	 *                     例如：select * from fact_call where report_time > '2018-10-06 17:32:59' and report_time < '2018-11-06 17:32:59'
	 *                     或者：select * from fact_call where report_time between '2018-10-06 17:32:59' and '2018-11-06 17:32:59'
	 *                     那么decisionTime应该是其中较小的值'2018-10-06 17:32:59'
	 *                     注意：必须限制一次查询的最大时间跨度不超过3个月
	 */
	public String decisionTableName(String decisionTime) {
		try {
			if (null!=decisionTime && !"".equals(decisionTime)) {
				long decision = sdf.parse(decisionTime).getTime();
				Calendar calendar = Calendar.getInstance();
				calendar.add(Calendar.MONTH, -3);
				long before_3 = calendar.getTimeInMillis();
				calendar.add(Calendar.MONTH, -3);
				long before_6 = calendar.getTimeInMillis();
				if (decision > before_3) {
					return "fact_call";
				} else if (decision > before_6) {
					return "fact_call_6";
				} else {
					return "fact_call_all";
				}
			}
		}catch (Exception e) {
			e.printStackTrace();
		}
		return "fact_call_all";
	}

（2）mybatis的映射文件，对表名做判断：

    <select id="findAll" resultMap="base_result_map" parameterType="java.util.Map">
    SELECT
        *
    FROM
        <choose>
            <when test="tableName=='fact_call'">
                fact_call f
            </when>
            <when test="tableName=='fact_call_6'">
                fact_call_6 f
            </when>
            <otherwise>
                fact_call_all f
            </otherwise>
        </choose>
    WHERE
        1=1
        <!-- and f.report_time &lt; #{begin_time} and f.report_time &gt; #{end_time}-->
        and f.report_time between #{begin_time} and #{end_time}
        limit #{index} , #{size}
    </select>

最后，解释为什么这么做：
优点：
1、不需要分库（后面会介绍分库的架构）
2、不依赖第三方程序（后面会介绍数据库中间件的架构）
3、数据冗余尽量少（后面会介绍一主多从的架构）
4、可靠性更高（后面会介绍使用mysql触发器做实时同步的架构）
缺点：
1、数据冗余为8个月数据（以空间换时间）
2、需要开启mysql定时器功能（影响的性能很小，可忽略）
3、对程序员不透明（但是程序员自己代码判断去哪个表中查，也很简单）
4、单台数据库存在服务器io限制（我们公司的数据库查询慢的问题不在于服务器，在于单表过大）

（PS：当然，也可以直接在mapper文件中，把表明当作变量，使用${}即可。由于表名参数不由前端传递，故不存在sql注入的风险）

最终完美上线

1、为什么不使用mycat

(注：上图来源网络)
mycat功能很强大，即支持分库也支持分表。支持取模、hash等不同的划分策略。但是存在三点问题：

• 集群搭建过于复杂，运维成本高，如果不搭建mycat集群又会带来单点故障问题；
• 我的需求对于最近数据和历史数据是不同的，历史数据可能1年也就查几次，慢点无所谓
• 我必须要使用分页、排序等功能

注：
但是大后期，也就是过了很多年后，当我们公司有了自己的DBA，当我们的日数据量超过100W，应该还是会采用分库分表的办法。（为什么是100W呢？因为mysql innodb处理5000W数据量的单表速度勉强可以接受，5000W/60天，约等于100W/天）。我们公司还处于早中期阶段，没有DBA，公司想尽快提高数据库性能，又不想搞得太复杂。

至于不使用Kingshard、Sharding-JDBC原因很多，一方面太麻烦了，Kingshard使用Go语言开发，难以维护。Sharding-JDBC对程序员不透明，我每个程序都要做一遍复杂的分库分表。
另外，从需求的角度分析，我已经手动分表了，也不需要使用第三方来做读写分离。完全就没必要使用数据库中间件！

一主多从的架构，如下图：

（上图的最近3月，改为最近2月。）
上图是分库的，之前准备使用mybatis的多源数据库自动切换的办法，这样可以避免使用数据库中间件，也蛮简单的，在每次查询之前根据分库的key来切换数据源即可（感兴趣的可以自己搜索，我是已经实现过）。上图的好处是可以把数据库放在不同的服务器上，但是缺点是违反了主从原则，运维管理也很麻烦。后来被pass掉了。

mysql触发器实时同步的版本：

大家应该看出来了，终极版本中“需要限制查询最近的超过2个月的数据（如果要查询超过2个月的数据，则不能查今天的数据，因为今天的需要到晚上才能同步到fact_call_6表中）”，这个原因是因为，我做的不是实时同步，那么为什么不使用mysql触发器做实时同步，原因很简单，当数据库操作过于频繁时，mysql触发器不可靠！

经过很久的while(true){分析、开会、讨论}，最终得出来了我们的“终极版本”。最终版本虽说看起来很简单，也没有使用什么复杂的技术，但是能满足我们的需求，最快的提升数据库性能。就像：

以前：
客户：我想查下我今天的数据，为什么查不出来数据了，一查就报错。
产品经理：没办法数据库量太大了。
客户：什么？那也就是说我以后每天的数据都不能查了？
产品经理说：那我把两年前的数据删了吧？
客户：删数据？那怎么行，万一我们领导哪天要检查、核实、取证怎么办，不能删（其实他们永远不会去查很久以前的数据，他们只是一听到删他们的数据他们就很慌）。

上线完毕，改动相对较小。只是多加了两张表、对于要查fact_call表的程序稍微做了修改。

现在：
客户：我今天的数据可以查，但是查以前的数据很慢，为什么？
产品经理：谁叫你查以前的数据，查很久以前的数据，本来就慢…
客户：哦，好吧…
我：（哈哈哈哈）

注意：mycat、Kingshard、Sharding-JDBC各有优势，都是非常优秀的开源产品，本文仅仅是个人看法。

本文地址：https://blog.csdn.net/tiandixuanwuliang/article/details/84650061