大表处理思路(单表大于50G)_云计算

发布时间:2024-07-11 19:34

阅读量:18

// 附录11我在公司内部一直奉承的重构思路:步子大了小心扯着蛋.

1.搜集sql,得出分库,分表或分区键2.如果要水平分表，需要寻找自增id的替代。这种情况可以先去掉数据库中自增ID，为分片和后面的数据迁移工作提前做准备。 // 小步前进3.双写4.mysql或polardb-x

2.冷热分离的方案

分库分表会导致多个业务线和统计业务的编码复杂化,所以不考虑分库分表,采用一种类似冷热分离的思路: 使用2个表。

每n天通过脚本将一段时间内的热点数据表的数据(insert|update|delete),增量的同步到全量表。

3.阿里云的drds

有了方案2,暂时不考虑这个。原理可参考附录2。

最终方案

方案2

效果

待我搞完才知道,应该能支持个3-5年。

经验总结

尽量避免分库分表,采用冷热分离的思路

LOG

发到一些技术交流群中,收到如下反馈

Q:如果要查冷数据,那怎么办？

A(我): 确实有一部分冷数据一直保留在热点表里面的，就是那种只能领取一次的券，需要校验用户只能领取一次。除此之外，只有统计需要查询其他的冷数据，业务上不需要查。所以删除的冷数据仅仅指的是，业务上不再需要查询的那部分冷数据，这部分数据是大头。业务上需要查询的冷数据，则保留在热点表中。

A(我): 估计分库分表我要重新考虑下,在多个群被质疑了

A(其他): 分库分表分场景,优惠券适合冷备吧

A(其他): 哦,有大量的优惠券没有领取

A(我): 恩,所以很多都没意义了，没有领取又失效了，那么业务上就没意义了，只有统计上需要。

Q: 云原生分布式数据库 PolarDB-X,这个方案如何?

A(我): 还在调研,暂未考虑。

Q: 全量表三年以后不是要有50亿,30亿数据还怎么查?

A: 估计分库分表我要重新考虑下,在多个群被质疑了

今年又进行了表数据的清理

因为今年升级了mysql8.0// 附录3

表碎片清理

附录4,5查看碎片率

SELECT CONCAT(table_schema, '.', table_name)                   AS  TABLE_NAME
      ,engine                                                  AS  TABLE_ENGINE 
      ,table_type                                              AS  TABLE_TYPE
      ,table_rows                                              AS  TABLE_ROWS
      ,CONCAT(ROUND(data_length  / ( 1024 * 1024), 2), 'M')    AS  TB_DATA_SIZE 
      ,CONCAT(ROUND(index_length / ( 1024 * 1024), 2), 'M')    AS  TB_IDX_SIZE 
      ,CONCAT(ROUND((data_length + index_length ) 
            / ( 1024 * 1024 ), 2), 'M')                        AS  TOTAL_SIZE
      ,CASE WHEN  data_length =0 THEN 0
            ELSE  ROUND(index_length / data_length, 2) END     AS  TB_INDX_RATE
    ,CONCAT(ROUND( data_free / 1024 / 1024,2), 'MB')           AS  TB_DATA_FREE 
    ,CASE WHEN (data_length + index_length) = 0 THEN 0
             ELSE ROUND(data_free/(data_length + index_length),2) 
     END                                                       AS  TB_FRAG_RATE
FROM information_schema.TABLES  
ORDER BY data_free DESC;

分库分表再提

// 附录6业务增长,数据量增长,准备做一些分库分表的事情

PolarDB-X如何解决不走分表键值对导致全表扫描的问题

// 附录8,9mysql单索引下二级索引的原理:

分布式二级索引原理:

大表DDL语句的执行

阿里云的DMS,无锁变更执行DDL语句

加索引。往中间表拷贝数据是用的如下语句:

INSERT IGNORE INTO `库`.`tp_2459945_ogt_t_user_voucher(临时表)` (`xxx`)
SELECT `xxx`
FROM `库`.`表` FORCE INDEX (`PRIMARY`)
WHERE `id` > ?
	AND (`id` < ?
		OR `id` = ?)
LOCK IN SHARE MODE;