MySQL千万级大表优化，看这一篇就忘不掉了!

千万级大表如何优化，这是一个很有技术含量的问题，通常我们的直觉思维都会跳转到拆分或者数据分区，在此我想做一些补充和梳理，想和大家做一些这方面的经验总结，也欢迎大家提出建议。

图片来自 Pexels

从一开始脑海里火光四现，到不断的自我批评，后来也参考了一些团队的经验，我整理了下面的大纲内容。

既然要吃透这个问题，我们势必要回到本源，我把这个问题分为三部分：“千万级”，“大表”，“优化”，也分别对应我们在图中标识的“数据量”，“对象”和“目标”。

我来逐步展开说明一下，从而给出一系列的解决方案。

数据量：千万级

千万级其实只是一个感官的数字，就是我们印象中的数据量大。

这里我们需要把这个概念细化，因为随着业务和时间的变化，数据量也会有变化，我们应该是带着一种动态思维来审视这个指标，从而对于不同的场景我们应该有不同的处理策略。

①数据量为千万级，可能达到亿级或者更高

通常是一些数据流水，日志记录的业务，里面的数据随着时间的增长会逐步增多，超过千万门槛是很容易的一件事情。

②数据量为千万级，是一个相对稳定的数据量

如果数据量相对稳定，通常是在一些偏向于状态的数据，比如有 1000 万用户，那么这些用户的信息在表中都有相应的一行数据记录，随着业务的增长，这个量级相对是比较稳定的。

③数据量为千万级，不应该有这么多的数据

这种情况是我们被动发现的居多，通常发现的时候已经晚了，比如你看到一个配置表，数据量上千万;或者说一些表里的数据已经存储了很久，99% 的数据都属于过期数据或者垃圾数据。

数据量是一个整体的认识，我们需要对数据做更近一层的理解，这就可以引出第二个部分的内容。

对象：数据表

数据操作的过程就好比数据库中存在着多条管道，这些管道中都流淌着要处理的数据，这些数据的用处和归属是不一样的。

一般根据业务类型把数据分为三种：

①流水型数据

流水型数据是无状态的，多笔业务之间没有关联，每次业务过来的时候都会产生新的单据。

比如交易流水、支付流水，只要能插入新单据就能完成业务，特点是后面的数据不依赖前面的数据，所有的数据按时间流水进入数据库。

②状态型数据

状态型数据是有状态的，多笔业务之间依赖于有状态的数据，而且要保证该数据的准确性，比如充值时必须要拿到原来的余额，才能支付成功。

③配置型数据

此类型数据数据量较小，而且结构简单，一般为静态数据，变化频率很低。

至此，我们可以对整体的背景有一个认识了，如果要做优化，其实要面对的是这样的 3*3 的矩阵，如果要考虑表的读写比例(读多写少，读少写多...)，那么就会是 3*3*4=24 种，显然做穷举是不显示的，而且也完全没有必要，可以针对不同的数据存储特性和业务特点来指定不同的业务策略。

对此我们采取抓住重点的方式，把常见的一些优化思路梳理出来，尤其是里面的核心思想，也是我们整个优化设计的一把尺子，而难度决定了我们做这件事情的动力和风险。

而对于优化方案，我想采用面向业务的维度来进行阐述。

目标：优化

在这个阶段，我们要说优化的方案了，总结的有点多，相对来说是比较全了。整体分为五个部分：

其实我们通常所说的分库分表等方案只是其中的一小部分，如果展开之后就比较丰富了。

不难理解，我们要支撑的表数据量是千万级别，相对来说是比较大了，DBA 要维护的表肯定不止一张，如何能够更好的管理，同时在业务发展中能够支撑扩展，同时保证性能，这是摆在我们面前的几座大山。

我们分别来说一下这五类改进方案：

规范设计

业务层优化

架构层优化

数据库优化

管理优化

规范设计

在此我们先提到的是规范设计，而不是其他高大上的设计方案。

黑格尔说：秩序是自由的第一条件。在分工协作的工作场景中尤其重要，否则团队之间互相牵制太多，问题多多。

我想提到如下的几个规范，其实只是属于开发规范的一部分内容，可以作为参考。

规范的本质不是解决问题，而是有效杜绝一些潜在问题，对于千万级大表要遵守的规范，我梳理了如下的一些细则，基本可以涵盖我们常见的一些设计和使用问题。

比如表的字段设计不管三七二十一，都是 varchar(500)，其实是很不规范的一种实现方式，我们来展开说一下这几个规范。

配置规范：

MySQL 数据库默认使用 InnoDB 存储引擎。

保证字符集设置统一，MySQL 数据库相关系统、数据库、表的字符集都使用 UTF8，应用程序连接、展示等可以设置字符集的地方也都统一设置为 UTF8 字符集。

注：UTF8 格式是存储不了表情类数据，需要使用 UTF8MB4，可在 MySQL 字符集里面设置。在 8.0 中已经默认为 UTF8MB4，可以根据公司的业务情况进行统一或者定制化设置。

MySQL 数据库的事务隔离级别默认为 RR(Repeatable-Read)，建议初始化时统一设置为 RC(Read-Committed)，对于 OLTP 业务更适合。

数据库中的表要合理规划，控制单表数据量，对于 MySQL 数据库来说，建议单表记录数控制在 2000W 以内。

MySQL 实例下，数据库、表数量尽可能少;数据库一般不超过 50 个，每个数据库下，数据表数量一般不超过 500 个(包括分区表)。

建表规范：

InnoDB 禁止使用外键约束，可以通过程序层面保证。

存储精确浮点数必须使用 DECIMAL 替代 FLOAT 和 DOUBLE。

整型定义中无需定义显示宽度，比如：使用 INT，而不是 INT(4)。

不建议使用 ENUM 类型，可使用 TINYINT 来代替。

尽可能不使用 TEXT、BLOB 类型，如果必须使用，建议将过大字段或是不常用的描述型较大字段拆分到其他表中;另外，禁止用数据库存储图片或文件。

存储年时使用 YEAR(4)，不使用 YEAR(2)。

建议字段定义为 NOT NULL。

建议 DBA 提供 SQL 审核工具，建表规范性需要通过审核工具审核后。

命名规范：

库、表、字段全部采用小写。

库名、表名、字段名、索引名称均使用小写字母，并以“_”分割。

库名、表名、字段名建议不超过 12 个字符。(库名、表名、字段名支持最多 64 个字符，但为了统一规范、易于辨识以及减少传输量，统一不超过 12 字符)

库名、表名、字段名见名知意，不需要添加注释。

对于对象命名规范的一个简要总结如下表所示，供参考：

（编辑：辽源站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!