数据库分库分表架构选型

「爱情、让人受尽委屈。」 2022-04-12 02:39 313阅读 0赞

### 文章目录 ###

*  0 数据库架构调整背景：
 *  1 终极版本
 *  2 为什么不使用mycat、Kingshard、Sharding-JDBC
 *  3 为什么不使用一主多从
 *  3 为什么不使用mysql触发器做实时同步

随着用户量的增加和历史数据的不断积累，导致公司系统越来越卡，稍微复杂的查询都是分钟级，甚至有前端请求超时报错的情况(2分钟),所以这段时间一直在研究公司的数据库架构。  
我是一个地道的java程序员，由于我们公司没有DBA，所以只能我来研究，这也是公司交给我的一个重要的任务，我利用做完手头项目的空余时间分析并研究了目前市场上很多的数据库架构，进行一次总结、体会。  
请谨记：  
**没有最好的数据库架构，只有适不适合的数据库架构。**

--------------------

# 0 数据库架构调整背景： #

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

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

# 1 终极版本 #

![终极版本][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RpYW5kaXh1YW53dWxpYW5n_size_16_color_FFFFFF_t_70_g_center]  
**首先，解释下上图的含义：**

*  图中的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 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注入的风险）

> 最终完美上线

# 2 为什么不使用mycat、Kingshard、Sharding-JDBC #

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

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

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

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

# 3 为什么不使用一主多从 #

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

# 3 为什么不使用mysql触发器做实时同步 #

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

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

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

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

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

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

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RpYW5kaXh1YW53dWxpYW5n_size_16_color_FFFFFF_t_70_g_center]: /images/20220412/b30a7a1c606d4fda937dd786cce74ff0.png
[mycat]: /images/20220412/6158536691e24075b5e795ab3633b523.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RpYW5kaXh1YW53dWxpYW5n_size_16_color_FFFFFF_t_70_g_center 1]: /images/20220412/0469f9821b4d4f65a9ba6c63beed3ab7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RpYW5kaXh1YW53dWxpYW5n_size_16_color_FFFFFF_t_70_g_center 2]: /images/20220412/870d7e5d597a4a87bab4a4e798222fde.png