learn - 项目架构演变-03（分表分库篇）

创作背景

我负责的项目中，有很多是从0到1，也有很多是大型项目。

本篇主要是为了当项目初期未搭建或购买分布式数据库时，用小的成本暂时支撑当前项目发展而创作的。

提前规划好自己的存储结构分库分表，对后期业务增长技术支撑上也有很大帮助的：

1.对数量级O(1)的可以是单表，也可以是小规模的分表，例如一个面向C端的产品运营1-2年后用户量可达到千万；

2.对数量级O(n)的可以进行分表，一般划分16-128之间，例如用户与xx的关系存储；

3.对数量级O(n*n) 的一定要分表，一般开系数的大小，如果不确定暂时64张或128即可，例如个体与xx的多个属性之间的关系。

另外创建的所有数据库尽量提前指定默认字符集，否则会使用安装数据库时候给配置的默认字符集。

CREATE DATABASE 
IF NOT EXISTS open_test
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- CHARACTER SET utf8 COLLATE utf8_general_ci;
-- 必须用utf8mb4,要不然存不下[emj]的宽字符

如果不小心创建的表已经用于生产了，我们还可以补救回来：

-- 修改库默认字符集
ALTER SCHEMA `nexus_dev`  CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 修改表默认字符集
ALTER TABLE article CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 实在不行，那暂时停掉该业务几分钟，来拷贝数据到新表
-- 创建一个字符集正确的表 （因为每张表的单行总字节不超过一页数据，16k，如果字段varchar过多，可能转不过来，那就换一张表拷贝，或者更改列长度以后，再修改）
INSERT INTO `ai_test_task_2`（`id`...） SELECT `id`...  FROM ai_test_task ;
rename table ai_test_task to ai_test_task_back;
rename table ai_test_task_2 to ai_test_task;

使用存储过程来创建同类表(table)

案例：用户账单

--
-- 用户账单
--


DELIMITER $$
DROP PROCEDURE IF EXISTS `create_bill_table`$$
CREATE PROCEDURE `create_bill_table`()
BEGIN
    SET @i = 0;
    WHILE @i < 64
        DO
            SET @table_name = CONCAT('user_bill_', @i);
            SET @create_sql = CONCAT('CREATE TABLE ',  @table_name,
                                     '(`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT "自增主键",',
                                     '`user_id` bigint(20) UNSIGNED NOT NULL COMMENT "用户UID，分号器下发ID",',
                                     '`bill_type` int NOT NULL  DEFAULT 0 COMMENT "账单类型，1金币支出，2金币收入",',
                                     '`sub_type` int NOT NULL  DEFAULT 0 COMMENT "子类型，根据bill_type决定不同的枚举值",',
                                     '`amount`  INT UNSIGNED NOT NULL DEFAULT 0 COMMENT "交易数量金币数/美分",',
                                     ' `before_amount`  INT UNSIGNED NOT NULL DEFAULT 0 COMMENT "交易之前的数量，例如金币变动前",',
                                     '`create_time` datetime NOT NULL  DEFAULT CURRENT_TIMESTAMP COMMENT "创建时间",',
                                     ' `extra` varchar(256)  NOT NULL  DEFAULT "" COMMENT "其他扩展信息，根据bill_type定",',
                                     'PRIMARY KEY (`id`) USING BTREE,',
                                     'KEY `idx_user_bill_sub` (`user_id`, `bill_type` , `sub_type`) USING BTREE',
                                     ') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT="用户账单记录";'
                );
            PREPARE stmt from @create_sql;
            EXECUTE stmt;

            SET @i = @i + 1;

        END WHILE;

END $$

DELIMITER  ;

CALL create_bill_table();
DROP PROCEDURE IF EXISTS `create_bill_table`;

使用存储过程来创建视图(view)

当我们允许在后台使用时，由于数据分散到不同的表上面，我们可以创建一个合并的视图提供查询（虽然改方案会影响查询速度，但是给内部后台使用也足够了）

DELIMITER $$
DROP PROCEDURE IF EXISTS `create_view_user_bill`$$
CREATE PROCEDURE `create_view_user_bill`()
BEGIN
    SET @i = 0;
    SET @view_sql='CREATE OR REPLACE  VIEW view_user_bill AS  ';
    WHILE @i < 63
        DO
            SET @table_name = CONCAT('user_bill_', @i);
            SET @view_sql = CONCAT(  @view_sql , '  SELECT * FROM ',  @table_name,' union ');



            SET @i = @i + 1;

        END WHILE;
    SET @table_name = CONCAT('user_bill_', @i);
    SET @view_sql = CONCAT(  @view_sql , '  SELECT * FROM ',  @table_name);
    PREPARE stmt from @view_sql;
    EXECUTE stmt;
END $$

DELIMITER  ;

CALL create_view_user_bill();
DROP PROCEDURE IF EXISTS `create_view_user_bill`;

总结

在项目初期，当1、2个人参与布局话可能没时间直接上微服务的话（加班加点加机器加预算加薪水的话我觉得可以一步到位），但可以提前把地基给打牢固，省的以后留下对数据迁移时的风险。