CDA网校:数据科学、人工智能从业者的在线大学。
数据科学(Python/R/Julia)数据分析、机器学习、深度学习
千万级大表如何优化,这是一个很有技术含量的问题,通常我们的直觉思维都会跳转到拆分或者数据分区,在此我想做一些补充和梳理,想和大家做一些这方面的经验总结,也欢迎大家提出建议。
从一开始脑海里开始也是火光四现,到不断的自我批评,后来也参考了一些团队的经验,我整理了下面的大纲内容。
既然要吃透这个问题,我们势必要回到本源,我把这个问题分为三部分:
“千万级”,“大表”,“优化”,
也分别对应我们在图中标识的
“数据量”,“对象”和“目标”。
我来逐步展开说明一下,从而给出一系列的解决方案。
1.数据量:千万级
千万级其实只是一个感官的数字,就是我们印象中的数据量大。 这里我们需要把这个概念细化,因为随着业务和时间的变化,数据量也会有变化,我们应该是带着一种动态思维来审视这个指标,从而对于不同的场景我们应该有不同的处理策略。
1)数据量为千万级,可能达到亿级或者更高
通常是一些数据流水,日志记录的业务,里面的数据随着时间的增长会逐步增多,超过千万门槛是很容易的一件事情。
2)数据量为千万级,是一个相对稳定的数据量
如果数据量相对稳定,通常是在一些偏向于状态的数据,比如有1000万用户,那么这些用户的信息在表中都有相应的一行数据记录,随着业务的增长,这个量级相对是比较稳定的。
3)数据量为千万级,不应该有这么多的数据
这种情况是我们被动发现的居多,通常发现的时候已经晚了,比如你看到一个配置表,数据量上千万;或者说一些表里的数据已经存储了很久,99%的数据都属于过期数据或者垃圾数据。
数据量是一个整体的认识,我们需要对数据做更近一层的理解,这就可以引出第二个部分的内容。
2.对象:数据表
数据操作的过程就好比数据库中存在着多条管道,这些管道中都流淌着要处理的数据,这些数据的用处和归属是不一样的。
一般根据业务类型把数据分为三种:
(1)流水型数据
流水型数据是无状态的,多笔业务之间没有关联,每次业务过来的时候都会产生新的单据,比如交易流水、支付流水,只要能插入新单据就能完成业务,特点是后面的数据不依赖前面的数据,所有的数据按时间流水进入数据库。
(2)状态型数据
状态型数据是有状态的,多笔业务之间依赖于有状态的数据,而且要保证该数据的准确性,比如充值时必须要拿到原来的余额,才能支付成功。
(3)配置型数据
此类型数据数据量较小,而且结构简单,一般为静态数据,变化频率很低。
至此,我们可以对整体的背景有一个认识了,如果要做优化,其实要面对的是这样的3*3的矩阵,如果要考虑表的读写比例(读多写少,读少写多...),那么就会是3*3*4=24种,显然做穷举是不显示的,而且也完全没有必要,可以针对不同的数据存储特性和业务特点来指定不同的业务策略。
对此我们采取抓住重点的方式,把常见的一些优化思路梳理出来,尤其是里面的核心思想,也是我们整个优化设计的一把尺子,而难度决定了我们做这件事情的动力和风险。
数据量增长情况 | 数据表类型 | 业务特点 | 优化核心思想 | 优化难度 |
数据量为千万级,是一个相对稳定的数据量 | 状态表 | OLTP业务方向 | 能不拆就不拆读需求水平扩展 | **** |
数据量为千万级,可能达到亿级或者更高 | 流水表 | OLTP业务的历史记录 | 业务拆分,面向分布式存储设计 | **** |
OLAP业务统计数据源 | 设计数据统计需求存储的分布式扩展 | *** | ||
数据量为千万级,不应该有这么多的数据 | 配置表 | 通用业务 | 小而简,避免大一统 | * |
而对于优化方案,我想采用面向业务的维度来进行阐述。
3.目标:优化
在这个阶段,我们要说优化的方案了,总结的有点多,相对来说是比较全了。
整体分为五个部分:
其实我们通常所说的分库分表等方案只是其中的一小部分,如果展开之后就比较丰富了。
其实不难理解,我们要支撑的表数据量是千万级别,相对来说是比较大了,DBA要维护的表肯定不止一张,如何能够更好的管理,同时在业务发展中能够支撑扩展,同时保证性能,这是摆在我们面前的几座大山。
我们分别来说一下这五类改进方案:
优化设计方案1.规范设计
在此我们先提到的是规范设计,而不是其他高大上的设计方案。
黑格尔说:秩序是自由的第一条件。在分工协作的工作场景中尤其重要,否则团队之间互相牵制太多,问题多多。
规范设计我想提到如下的几个规范,其实只是属于开发规范的一部分内容,可以作为参考。
规范的本质不是解决问题,而是有效杜绝一些潜在问题,对于千万级大表要遵守的规范,我梳理了如下的一些细则,基本可以涵盖我们常见的一些设计和使用问题,比如表的字段设计不管三七二十一,都是varchar(500),其实是很不规范的一种实现方式,我们来展开说一下这几个规范。
1)配置规范
(1)MySQL数据库默认使用InnoDB存储引擎。(2)保证字符集设置统一,MySQL数据库相关系统、数据库、表的字符集使都用UTF8,应用程序连接、展示等可以设置字符集的地方也都统一设置为UTF8字符集。
注:UTF8格式是存储不了表情类数据,需要使用UTF8MB4,可在MySQL字符集里面设置。在8.0中已经默认为UTF8MB4,可以根据公司的业务情况进行统一或者定制化设置。(3)MySQL数据库的事务隔离级别默认为RR(Repeatable-Read),建议初始化时统一设置为RC(Read-Committed),对于OLTP业务更适合。(4)数据库中的表要合理规划,控制单表数据量,对于MySQL数据库来说,建议单表记录数控制在2000W以内。(5)MySQL实例下,数据库、表数量尽可能少;数据库一般不超过50个,每个数据库下,数据表数量一般不超过500个(包括分区表)。
2)建表规范
(1)InnoDB禁止使用外键约束,可以通过程序层面保证。
(2)存储精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE。
(3)整型定义中无需定义显示宽度,比如:使用INT,而不是INT(4)。
(4)不建议使用ENUM类型,可使用TINYINT来代替。
(5)尽可能不使用TEXT、BLOB类型,如果必须使用,建议将过大字段或是不常用的描述型较大字段拆分到其他表中;另外,禁止用数据库存储图片或文件。
(6)存储年时使用YEAR(4),不使用YEAR(2)。
(7)建议字段定义为NOT NULL。
(8)建议DBA提供SQL审核工具,建表规范性需要通过审核工具审核后
关注“AIU人工智能”公众号,回复“白皮书”获取数据分析、大数据、人工智能行业白皮书及更多精选学习资料!