| 知乎专栏 |
分库和分表是架构必经之路，我想问问你是怎么分库和分表？
很多系统在设计之初就没有考虑过后期的分库与分表，甚至开发团队没有架构和DBA人员，开发团队也比较年轻，对于数据库的架构定义非常随意，满足当前需求即可。
实际上数据库结构等同于建筑里面的地基，地基没有打好，后面的优化都是徒劳的，最终不得不重构数据库结构。
那么你是怎样分库分表的？
数据的切分策略有两种方式，分别是：水平（横向）切分和垂直（纵向）切分。
技术手段也有四种方法，分别是：类别、范围、Hash和冷热数据。
一旦使用了分库分表技术，后面的很多技术就受到影响，技术上就要妥协。
将某个分类数据，某个范围内的数据或者符合Hash值的数据存储到不同的数据库或者表中。
垂直切分的优点：缩小单表结果集，提高查询速度
垂直切分的缺点：索引不连续，必须合并查询，很多SQL语句会受到限制，例如join（原本不需要）,count,order by，事务处理变得复杂。
你在网上看到关于分库，分表方案几乎都是从技术维度出发，例如解决大数据存储压力，提高查询性能等等。面试中我发现很多架构师对分库分表也只是停留在对分库分表中间件理解和应用上。
任何系统数据流都是漏斗形状的，数据库是漏斗末端，架构设计是尽量在前端计算，合并，拆分，分流，缓存，最终将有价值的数据写入数据库。数据库的访问是结果集越小越好。
基于这种认识，通常分库和分表，我们想到的就是首先垂直分表，这种方式简单易操作。
当前（本年度数据库）（热数据） 2019年数据 2018年数据 以此类推 或者按照月份分表 当前（热数据） 10月数据 9月数据 以此类推
这样分表可以缩小结果集，能快速解决查询瓶颈问题。但是新的挑战来了，由于分表后，索引是独立不连续的，历史数据的查询或遍历数据变的复杂了，要么使用联合查询，要么一张表一张表的遍历。
同理水平分表也是粗暴的将一些尺寸较大的列独立成新表，以降低单个表的容量尺寸。
如果是单纯的数据查找，还是能忍受，我们可能根据时间来选择查询的表，如果是复杂的SQL操作，就只能逐一查询，在程序中二次计算，合并等等操作。
这种分库或分表的思路，理论上属于数据归档。将热数据放在当前数据库中，将很少查询的冷数据放在另一个库中。但是对于 user 这种表就无能为力，你不知道那个用户什么时候会做登录操作。
分表需要从业务角度考虑，数据库服务于业务逻辑。
由于我即负责产品也负责架构，长期的工作中，总结出一套分库分表的策略。
我分表策略是：
用户表怎样水平拆分呢？ 用户登录的逻辑是这样的，第一个UI 输入用户名和密码，提交后验证密码，用户是否过期，记录登录时间，IP地址。第二个UI，载入用户资料，包括用户姓名，年龄，性别等等…..
数据库我是这样设计的：
+-----------------------------------------------+ | Member | +-----------------------------------------------+ | id | username | password | ip | ctime | mtime | +-----------------------------------------------+
就这么简单，会员表服务于第一UI
+-----------------------------------------------+ | Profile | +-----------------------------------------------+ | id | member_id | name | sex | age | mtime |…… | +-----------------------------------------------+
服务于第二个UI，通过 member_id 关联数据
+----------+ +-----------+ | member | | profile | |----------| |-----------| | id | <----- 1:1 -----> | member_id | | username | | name | | password | | sex | | nickname | | address | | status | | telphone | | ip | | mtime | | ctime | | ...... | +----------+ +-----------+
怎么样？用户登录过程并不会去访问 profile 表，只有登录成功才会访问。
用户表怎样垂直拆分呢？ 将 username 做crc32/md5/sha1 运算，使用哪种随你，取出第一个字符用于分表。
例如 neo, netkiller 两个用户被分到 member_n 表中，jerry,jam 被分到member_j 表中，配套的还有 profile_n, profile_j 等等。我们甚至还可以使用外键约束 member_n 和 profile_n 两个表。
当用户登录时，对用户ID做一次 hash 运算，就知道去哪个表中找到该用户的数据。
海量用户分库的思路是，用户被分配到指定数据库，该用户所有的数据都会产生在该数据库中，也可以理解为基于数据库隔离用户。
基于该思路分库，这样表名保持不变。
例如 neo, netkiller 被分配到 schema_n.member, schema_n.profile
例如 jerry, jam 被分配到 schema_j.member, schema_j.profile
一旦用户登录，便被锁定到指定的数据库，接下来所有操作，用户产生的数据，都被存储在该数据库中。索引连续，外键约束，触发器，存储过程，均不影响使用。
商品信息表数据量非常大，我们可以基于品类分库或分表，我们UI设计中，一般只有首页才会将不同分类的产品聚合到一起。进入品类分类页面后，只会访问该品类的数据表。
这就是从业务流的角度进行分表，用户操作是逐渐被引导至我们想呈现的页面。特定的页面只会访问特定的数据库和特定的表。
使用分区别将分区数据写入挂载的SSD盘上。例如 /opt/data/ 下面挂载了四块SSD，目录名是 partition1~4。
CREATE TABLE your_table (id INT, cdate DATE)
engine='InnoDB'
PARTITION BY LIST(YEAR(cdate))
(
PARTITION p2020 VALUES IN (2020)
DATA DIRECTORY = '/opt/data/partition1',
PARTITION p1999 VALUES IN (1999)
DATA DIRECTORY = '/opt/data/partition2',
PARTITION p1998 VALUES IN (1998)
DATA DIRECTORY = '/opt/data/partition3',
PARTITION p1997 VALUES IN (1997, 1996, 1995)
DATA DIRECTORY = '/opt/data/partition4'
);