mysql 分表模糊_闲谈数据库分表

数据量千万级一下的数据表不一定需要上来就要分库分表，一旦表被拆分，开发、统计、运维的复杂度会直线上升。

MySQL中几百万级别的表，先考虑优化(除非并发太大，单表单库扛不住)。

表优化

表分区

MySQL在5.1之后才有的，可以看做是水平拆分，分区表需要在建表的需要加上分区参数，用户需要在建表的时候加上分区参数。

分区表底层由多个物理子表组成，但是对于代码来说，分区表是透明的。SQL中的条件中最好能带上分区条件的列，这样可以定位到少量的分区上，否则就会扫描全部分区。

字段单表不要有太多字段

VARCHAR的长度尽量只分配真正需要的空间

尽量使用TIMESTAMP而非DATETIME

避免使用NULL，可以通过设置默认值解决

索引优化索引不是越多越好，索引会加速查询，但是对新增、修改、删除会造成一定的影响；

值域很少的字段不适合建索引，比如性别

尽量不用UNIQUE和设置外键

索引优化

尽量使用索引，不要因为错误的sql写法导致索引失效。使用explain验证是否使用索引。避免前导模糊查询(like %abc%不会走索引，like abc%会走索引)

避免隐式转换

避免等号左边做函数运算

in中的元素不宜过多

NoSQL

有一些场景统计类、日志类、弱结构化的数据，可以抛弃MySQL等关系型数据库。

表拆分

垂直拆分

把一个字段较多的表，拆分成多个字段较少的表。单表的字段不宜过多，一般来说，MySQL单表的字段最好不要超过二三十个。

水平拆分

分表，解决了单表数据过大的问题，但是毕竟还在同一台数据库服务器上，所以IO、CPU、网络方面的压力，并不会得到彻底的缓解，这个可以通过分库来解决。

分库可以利用多台数据库服务器的资源，提高了系统的负载能力。当然也会让逻辑会变得复杂，跨节点的数据关联性能差，维护难度大(特别是扩容的时候)。

全局ID

自动增长列

优点：数据库自带功能，有序，性能佳。

缺点：单库单表无妨，分库分表时如果没有规划，ID可能重复。可通过设置自增偏移和步长解决。

## 假设总共有 10 个分表 ## 级别可选: SESSION(会话级), GLOBAL(全局) SET @@SESSION.auto_increment_offset = 1; ## 起始值, 分别取值为 1~10 SET @@SESSION.auto_increment_increment = 10; ## 步长增量

全局ID映射表

在全局 Redis 中为每张数据表创建一个 ID 的键，记录该表当前最大 ID；

每次申请 ID 时，都自增 1 并返回给应用；

Redis 要定期持久至全局数据库。

UUID

UUID 由4个连字号(-)将32个字节长的字符串分隔后生成的字符串，总共36个字节长。

UUID 的计算因子包括：以太网卡地址、纳秒级时间、芯片ID码和许多可能的数字。

UUID 是个标准，其实现有几种，最常用的是微软的 GUID(Globals Unique Identifiers)。

优点：简单，全球唯一；

缺点：存储和传输空间大，无序，性能欠佳。

COMB(组合)

组合 GUID(10字节) 和时间(6字节)，达到有序的效果，提高索引性能。

Snowflake(雪花) 算法

Snowflake 是 Twitter 开源的分布式 ID 生成算法，其结果为 long(64bit) 的数值。1bit: 符号位，总是 0(为了保证数值是正数)。

41bit: 毫秒数(可用 69 年)；

10bit: 节点ID(5bit数据中心 + 5bit节点ID，支持 32 * 32 = 1024 个节点)

12bit: 流水号(每个节点每毫秒内支持 4096 个 ID，相当于 409万的 QPS，相同时间内如 ID 遇翻转，则等待至下一毫秒)

分片策略

连续分片

根据特定字段(比如用户ID、订单时间)的范围，值在该区间的，划分到特定节点。

优点：集群扩容后，指定新的范围落在新节点即可，无需进行数据迁移。

缺点：如果按时间划分，数据热点分布不均(历史数冷当前数据热)，导致节点负荷不均。

ID取模分片

缺点：扩容后需要迁移数据。

一致性Hash算法

优点：扩容后无需迁移数据。

Snowflake 分片

优点：扩容后无需迁移数据。