分而治之：MSSQL数据库分表更高效

什么是MSSQL数据库分表

MSSQL数据库分表是将一张大表按照一定的规则，将数据分散到多张表中，以达到优化查询性能、降低锁竞争、扩展数据库容量等目的。在实际应用场景中，由于某些数据表容器过大或者数据查询操作较为频繁，这时候采用数据表分裂可以大幅提高整个应用系统的性能。

数据表容器过大的问题

当一张数据表中数据量达到一定程度时，就会面临各种性能问题：

查询性能下降：查询效率下降是因为数据库要扫描更大的数据量。

锁争用风险升高：数据表容器大了之后，相应地锁控制粒度也更大了，以至于影响了锁竞争程度。

备份恢复速度慢：备份已经成为数据库日常维护的必备流程，备份对整个数据结构的重构和存储都是有一定成本的。在数据表容器特别大的情况下，这些成本会无限放大。

如何进行MSSQL数据库分表

常见的分表规则有：

按时间分表：按时间分表是一种比较常见的分表方式。它适用于监控等需要按照时间片段收集，并且需要保留历史信息的场合。时间分表按天、按周、按月等方式进行。

按字段分表：按字段分表是将数据按某个确定的字段或字段集合进行拆分。这种方式可覆盖任意多种分表场景。最大的优点是：可以将不同的数据逻辑分别存储到不同的表中。

按数据量分表：根据数据量的多少来进行分表，这样做有助于平衡所有库的性能和容量限制，但是繁琐度稍微有点大。

按业务维度分表：按业务维度分表是一种非常灵活的分表方式。它适用于有多个独立的区块、每个区块又有自己的主干表、或者其他具有独立性要求的场合。

MSSQL数据库分表案例

案例一：按时间分表

我们有一个医院的挂号系统，系统中有一个名叫‘Doctor’的数据库表，记录了医生的所有挂号信息，表结构如下：

CREATE TABLE `Doctor` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `doctor_name` varchar(32) NOT NULL COMMENT '医生姓名',
  `department` varchar(32) NOT NULL COMMENT '所属科室',
  `hospital` varchar(32) NOT NULL COMMENT '所属医院',
  `patient_name` varchar(32) NOT NULL COMMENT '病人姓名',
  `registration_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '挂号时间',
  PRIMARY KEY (`id`),
  KEY `idx_doctor_name` (`doctor_name`)
);

现在数据表中的数据已经达到了1000W条。因为这是一个历史数据表，我们需要严格控制数据大小，而且历史数据只用于查询分析。因此我们要将它进行数据分表，将一年的数据按照每个月进行分表存储。

分表操作：

CREATE TABLE `Doctor_201801` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `doctor_name` varchar(32) NOT NULL COMMENT '医生姓名',
  `department` varchar(32) NOT NULL COMMENT '所属科室',
  `hospital` varchar(32) NOT NULL COMMENT '所属医院',
  `patient_name` varchar(32) NOT NULL COMMENT '病人姓名',
  `registration_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '挂号时间',
  PRIMARY KEY (`id`),
  KEY `idx_doctor_name` (`doctor_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `Doctor_201802` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `doctor_name` varchar(32) NOT NULL COMMENT '医生姓名',
  `department` varchar(32) NOT NULL COMMENT '所属科室',
  `hospital` varchar(32) NOT NULL COMMENT '所属医院',
  `patient_name` varchar(32) NOT NULL COMMENT '病人姓名',
  `registration_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '挂号时间',
  PRIMARY KEY (`id`),
  KEY `idx_doctor_name` (`doctor_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
......以此类推

分表操作后，我们的数据表可以在每个月的进行清理归档，保证查询的效率和数据的正常存储。

案例二：按业务维度分表

我们有一个BBS系统，现在活跃用户日益增加，系统在扩大用户量的同时也需要考虑如何在投资成本减少的情况下提高系统质量。数据表中有如下两个表：user和post。

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL COMMENT '用户名',
  `password` varchar(32) NOT NULL COMMENT '密码',
  `email` varchar(32) NOT NULL COMMENT '邮箱',
  `reg_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  PRIMARY KEY (`id`),
  KEY `idx_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `post` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL COMMENT '用户ID',
  `content` text NOT NULL COMMENT '帖子内容',
  `post_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发帖时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

在数据表的拆分中，User和Post是两个非常独立的模块。当用户量达到一定程度之后，需要考虑对数据进行拆分。我们可以采用以下分表方式：

按用户性别划分：用户性别是一种特定类型的业务场景，可以依此进行拆分。

按用户注册时间进行数据划分：注册时间是比较常见的分表方式，可以根据时间段进行拆分。

按用户状态（包括会员、普通用户等）进行拆分：如果系统中有用户等级、会员等分级，则可以根据分级信息进行数据拆分。

我们可以采用按用户性别拆分数据表的方式，进行如下拆表处理：

CREATE TABLE `user_female` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL COMMENT '用户名',
  `password` varchar(32) NOT NULL COMMENT '密码',
  `email` varchar(32) NOT NULL COMMENT '邮箱',
  `reg_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  PRIMARY KEY (`id`),
  KEY `idx_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `user_male` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL COMMENT '用户名',
  `password` varchar(32) NOT NULL COMMENT '密码',
  `email` varchar(32) NOT NULL COMMENT '邮箱',
  `reg_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  PRIMARY KEY (`id`),
  KEY `idx_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `post_female` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL COMMENT '用户ID',
  `content` text NOT NULL COMMENT '帖子内容',
  `post_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发帖时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `post_male` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL COMMENT '用户ID',
  `content` text NOT NULL COMMENT '帖子内容',
  `post_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发帖时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

拆表后，我们可以将不同性别的用户进行不同表管理，有效提高整个系统的质量。

总结

MSSQL数据库分表需要根据业务需求进行选择，依据一定的分表规则和拆表方式进行。

分表可以顺应不断扩张的业务需求，同时也可以提升系统性能，加快查询速度等，使系统变得更快、更安全和更可靠。