阐明：以下内容均不思考数据库之外的解决方案
为什么要分表
mysql单表数据量下限
影响 Mysql 单表的最优最大数量的一个重要因素其实是索引。
存储引擎 InnoDB 采纳 B+树结构索引。
Mysql 的 B+树索引存储在磁盘上，Mysql 每次读取磁盘 Page 的大小是 16KB，为了保障每次查问的效率，须要保障每次查问拜访磁盘的次数，个别设计为 2-3 次磁盘拜访，再多性能将严重不足。Mysql B+树索引的每个节点须要存储一个指针（8Byte）和一个键值（8Byte）。因而计算16KB/(8B+8B)=1K 16KB 能够存储 1K 个节点，3 次磁盘拜访(即 B+树 3 的深度)能够存储 1K 1K 1K 即 10 亿数据（是不是感觉不堪设想？）。

  理论业务中，业务查问会依赖非主键索引，波及二级索引（回表查问），这样最大数据量将指数级减小，但反对个千万数据还是问题不大的，当然，查问中最好防止大宽表查问，以及防止索引重建过程。

既然mysql单表反对千万级的数据，那还须要分表么？答案是要看业务，通过简略计算就能够得出结论。例如，咱们须要存储车辆的gps定位信息，单辆车5秒上报一个点位，一天产生 246060/5 = 17280条数据，1000台车一天就产生1700万数据（理论业务对这类数据个别不会应用mysql存储）。此时就须要采纳分表策略，升高单表数据量。
如果数据量的增长一年都达不到千万级别，就别折腾了，我的项目能不能活够一年都不肯定。。。
分表的准则
分表的次要根据还是看业务。
个别看业务的查问需要，是以工夫为纬度还是业务主体的纬度。
例如，咱们次要查问一段时间范畴内的订单信息，就是按工夫纬度查问的业务需要，在这种业务需要下，按工夫进行表拆分是适合的。遇到跨表查问，咱们也可能依据查问工夫来确定关联的表，个别关联两张表就差不多满足业务需要了。
如果咱们是查问车辆的轨迹，此时应该以车辆id（vin）来进行分表，防止查问过程中过多的关联表，升高查问效率。
有时候咱们既要查一段时间范畴内的订单，又要查某个用户的全副订单，怎么办？能够思考冗余数据做双写解决，毕竟磁盘比内存便宜。
分表的工具箱
ShardingSphere-JDBC（sharding-jdbc）
官网文档 https://shardingsphere.apache…
逻辑表
⽔平拆分的数据库（表）的雷同逻辑和数据结构表的总称。
例：订单数据依据主键尾数拆分为10张表，别离是t_order_0到t_order_9，他们的逻辑表名为t_order。
实在表
在分⽚的数据库中实在存在的物理表。即上个示例中的t_order_0到t_order_9 。
数据节点
数据分⽚的最⼩单元。由数据源名称和数据表组成，例：ds_0.t_order_0
绑定表
指分⽚规定⼀致的主表和⼦表。例如： t_order 表和 t_order_item 表，均依照 order_id 分⽚，则
此两张表互为绑定表关系。绑定表之间的多表关联查问不会呈现笛卡尔积关联，关联查问效率将⼤⼤提
升。
举例说明，如果SQL为：
SELECT i.* FROM t_order o
JOIN t_order_item i ON o.order_id=i.order_i d
WHERE o.order_id in (10, 11);
在不配置绑定表关系时，假如分⽚键 order_id 将数值10路由⾄第0⽚，将数值11路由⾄第1⽚，那么路由后的SQL应该为4条，它们出现为笛卡尔积：
SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.ord er_id WHERE o.order_id in (10, 11);
SELECT i.* FROM t_order_0 o JOIN t_order_item_1 i ON o.order_id=i.ord er_id WHERE o.order_id in (10, 11);
SELECT i.* FROM t_order_1 o JOIN t_order_item_0 i ON o.order_id=i.ord er_id WHERE o.order_id in (10, 11);
SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.ord er_id WHERE o.order_id in (10, 11);
在配置绑定表关系后，路由的SQL应该为2条:
SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.ord er_id WHERE o.order_id in (10, 11);
SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.ord er_id WHERE o.order_id in (10, 11);
其中 t_order 在FROM的最左侧，ShardingSphere将会以它作为整个绑定表的主表。所有路由计算将会只使⽤主表的策略，那么t_order_item表的分⽚计算将会使⽤t_order的条件。故绑定表之间的分区键要完全相同。
如果存在多个关联查问，须要为每个关联查问设置绑定关系
例如有4张表 a,b,c,d，b、c、d别离和a表还有关联查问须要配置
sharding:

binding-tables: 
- a, b
- a, c 
- a, d

⼴播表
指所有的分⽚数据源中都存在的表，表构造和表中的数据在每个数据库中均齐全⼀致。适⽤于数据量不⼤且须要与海量数据的表进⾏关联查问的场景，例如：字典表
分⽚策略
共⽀持4中分⽚策略.
inline ⾏表达式分⽚
配置如下：

⾏表达式分⽚策略

spring:

shardingsphere: 
sharding: 
    tables: 
    <logic-table-name>: #逻辑表名称 
        actualDataNodes: #由数据源名 + 表名组成，以⼩数点分隔。多个表以逗号 分隔，⽀持inline表达式。缺省示意使⽤已知数据源与逻辑表名称⽣成数据节点。⽤于⼴播表 （即每个库中都须要⼀个同样的表⽤于关联查问，多为字典表）或只分库不分表且所有库的表结 构齐全⼀致的状况 
        database-strategy:
            inline: 
            sharding-column: order_id #分⽚列名称 
            algorithm-expression: t_order${order_id % 2} #分⽚算法,按2取模; 应用⾏表达式，需合乎groovy语法

这种分⽚策略⽐较简略，适宜简略的按id进⾏取模分⽚
standard 单分⽚键分⽚策略
配置如下：
spring:

shardingRule: 
tables: 
    <logic_table_name>: #逻辑表名称 
    actualDataNodes: #由数据源名 + 表名组成，以⼩数点分隔。多个表以逗号分 隔，⽀持inline表达式。缺省示意使⽤已知数据源与逻辑表名称⽣成数据节点。⽤于⼴播表 （即每个库中都须要⼀个同样的表⽤于关联查问，多为字典表）或只分库不分表且所有库的表结 构齐全⼀致的状况 
    databaseStrategy: #分库策略，缺省示意使⽤默认分库策略，以下的分⽚策略只能 选其⼀ 
        standard: #⽤于单分⽚键的标准分⽚场景 
        shardingColumn: #分⽚列名称 
        preciseAlgorithmClassName: #准确分⽚算法类名称，⽤于=和IN。。该类需实现PreciseShardingAlgorithm接⼝并提供⽆参数的结构器 
        rangeAlgorithmClassName: #范畴分⽚算法类名称，⽤于BETWEEN，可选。。该类需实现RangeShardingAlgorithm接⼝并提供⽆参数的结构器

单键分⽚须要⾃⼰实现两个分⽚算法
a: PreciseShardingAlgorithm ⽤于=和IN查问
b: RangeShardingAlgorithm ⽤于BETWEEN查问，可选，倡议还是实现
算法参考：
// 该算法通过工夫进⾏按⽉分⽚,次要⽤于=和IN查问，insert时也会使⽤该策略
public class IllegalResultIdShardingAlgorithm implements PreciseShar dingAlgorithm<Long> {

@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<Long> shardingValue) { 
    // 这⾥传⼊的shardingValue就是配置⽂件中配置的shardingColumn的值 
    Long id = shardingValue.getValue(); 
    // 通过雪花算法的id得出工夫
    LocalDate localDate = SnowFlakeService.parseDate(id); 
    // 获取数据属于哪个⽉ 
    int month = localDate.getMonth().getValue(); 
    // 数据库表命名从 01开始到12，所以须要对获取的⽉份进⾏前补0 
    String monthString = month > 10 ? String.valueOf(month) : "0".co ncat(String.valueOf(month)); 
    for (String each : collection) { 
        if (each.endsWith(monthString)) { 
            return each; 
        } 
    } 
    return null; 
}

}
public class IllegalResultTimeRangeShardingAlgorithm implements Rang eShardingAlgorithm<Long> {

@Override 
public Collection<String> doSharding(Collection<String> collection , RangeShardingValue<Long> shardingValue) { 
    Collection<String> collect = new ArrayList<>(); 
    Range<Long> valueRange = shardingValue.getValueRange(); 
    LocalDate start = SnowFlakeService.parseDate(valueRange.lowerEnd point());
    LocalDate end = SnowFlakeService.parseDate(valueRange.upperEndpo int()); 
    for (LocalDate d = start; d.isBefore(end) || d.isEqual(end); d.p lusMonths(1)) { 
        int month = d.getMonth().getValue(); 
        String monthString = month > 10 ? String.valueOf(month) : "0".concat(String.valueOf(month)); 
        for (String each : collection) { 
            if (each.endsWith(monthString)) { 
                collect.add(each); 
                break; 
            } 
        } 
    } 
    return collect; 
}

}
留神，这两个类是sharding jdbc间接new的，所以不能使⽤spring 注⼊spring bean
complex 多分⽚键组合的分⽚策略
配置如下：
spring:

shardingRule: 
tables: #数据分⽚规定配置，可配置多个logic_table_name 
    <logic_table_name>: #逻辑表名称 
    actualDataNodes: #由数据源名 + 表名组成，以⼩数点分隔。多个表以逗号分 隔，⽀持inline表达式。缺省示意使⽤已知数据源与逻辑表名称⽣成数据节点。⽤于⼴播表 （即每个库中都须要⼀个同样的表⽤于关联查问，多为字典表）或只分库不分表且所有库的表结 构齐全⼀致的状况 
    databaseStrategy: #分库策略，缺省示意使⽤默认分库策略，以下的分⽚策略只能 选其⼀ 
    complex: #⽤于多分⽚键的复合分⽚场景 
    shardingColumns: #分⽚列名称，多个列以逗号分隔 
    algorithmClassName: #复合分⽚算法类名称。该类需实现ComplexKeysSh ardingAlgorithm接⼝并提供⽆参数的结构器

⽤法和单键分⽚根本⼀致，只是实现的算法不同
算法参考：
public class IllegalResultComplexRangeShardingAlgorithm implements C omplexKeysShardingAlgorithm {

private static final String TABLE_NAME = "table";
@Override 
public Collection<String> doSharding(Collection availableTargetNam es, ComplexKeysShardingValue shardingValue) { 
    Map map = shardingValue.getColumnNameAndShardingValuesMap(); 
    Long id = ((List<Long>) map.get("id")).stream().findFirst().orEl se(null); 
    Date date = ((List<Date>) map.get("created_at")).stream().findFi rst().orElse(null); 
    if (id == null || date == null) { 
        return null; 
    } 
    List<String> actualTable = Lists.newArrayList(); 
    String tableSuffix = ShardingUtils.getDateIndex(date); 
    for (Object each : availableTargetNames) { 
        String table = each.toString(); 
        // 先按⽉份分12份，而后按id分两份，最终表名为 table01_0 
        if (table.equals(TABLE_NAME.concat(tableSuffix).concat("_").co ncat(String.valueOf(id % 2)))) { 
            actualTable.add(table); 
        } 
    } 
    return actualTable; 
}

}
hint强制路由
配置⽂件：
spring:

shardingRule: 
tables: #数据分⽚规定配置，可配置多个logic_table_name 
    <logic_table_name>: #逻辑表名称 
    actualDataNodes: #由数据源名 + 表名组成，以⼩数点分隔。多个表以逗号分隔， ⽀持inline表达式。缺省示意使⽤已知数据源与逻辑表名称⽣成数据节点。⽤于⼴播表（即每个 库中都须要⼀个同样的表⽤于关联查问，多为字典表）或只分库不分表且所有库的表构造齐全⼀致 的状况
    databaseStrategy: #分库策略，缺省示意使⽤默认分库策略 
    hint: #Hint分⽚策略 
    algorithmClassName: #Hint分⽚算法类名称。该类需实现HintShardingA lgorithm接⼝并提供⽆参数的结构器

该策略不仅要实现⾃定义分⽚算法，还须要在执⾏mybatis操作前后执⾏HintManager
HintManager.getInstance().addTableShardingValue(table_name, sharding_ condition);// 必须执⾏，否则不会按⾃定义算法进⾏分⽚，会将数据同步插⼊所有分⽚中
mybatisDao.save(object);
HintManager.clear();// 必须执⾏否则或造成分⽚策略抵触
这种策略侵入了业务代码，所以不不举荐使⽤该策略
配置数据库
单库多表
spring:

shardingsphere: 
# 数据库配置 
    datasource: 
    ds0: 
        driver-class-name: com.mysql.jdbc.Driver # jdbc驱动 
      type: com.alibaba.druid.pool.DruidDataSource # 连接池
      url: jdbc:mysql://${DB_URL}:3306/${DB_NAME} 
      username: ${DB_USERNAME} 
      password: ${DB_PASS} 
      names: ds0 # 数据源别名 
      # 分⽚策略 
    sharding: 
      binding-tables: <talbe_name> # 绑定的表，在mybaits中使⽤这个名称进 ⾏数据库操作 
      tables: 
        <table_name>: 
            actual-data-nodes: ds0.illegal_result0$->{1..5} # 真正执⾏sq l时的⽬标数据表，⽀持inline表达式 
          table-strategy: 
          # 分⽚策略⽀持4种，具体能够参考官⽅⽂档 
            inline: 
                sharding-column: batch_number 
              algorithm-expression: illegal_result0$->{(batch_number % 5) +1} 
            defaultDataSourceName: ds0 
            defaultKeyGenerator: 
                type: SNOWFLAKE # sharding-jdbc 主键策略雪花算法
              column: id

其余配置参考官网文档
注意事项
路由到多数据节点时,不⽀持嵌套⼦查问
— ⽀持
SELECT COUNT() FROM (SELECT FROM t_order o)
— 不⽀持
SELECT COUNT(*) FROM
(
SELECT * FROM t_order o WHERE o.id IN (SELECT i d FROM t_order WHERE status = ?)
)
笛卡尔乘积查问性能问题
后面曾经解释，不再赘述。
⾃定义主键策略
sharding-jdbc提供了内置的分布式主键策略
UUID：采⽤UUID.randomUUID()的⽅式产⽣主键
SNOWFLAKE：雪花算法，sharding jdbc的雪花算法worker-id是配置在配置文件中的，如果有多个实例，要留神id抵触
LEAF：实现leaf-segment算法
如果须要⾃⼰实现主键产⽣策略，须要实现ShardingKeyGenerator接⼝
public final class ShardingIdGenerator implements ShardingKeyGenerat or {

@Override 
public Comparable<?> generateKey() { 
    return UUID.randomUUID();// 算法 
} 
@Override 
public String getType() { 
    return "MY_UUID"; 
} 
@Override 
public Properties getProperties() { 
    return null;// 如果须要内部参数，通过prop配置进来 
}
@Override 
public void setProperties(Properties properties) { 
    
}

}
sharding:

tables: 
illegal_result: 
    key-generator: 
  column: id 
  type: MY_UUID //算法名称 
  props: 
    start: 
        id: 1 // 配置项 
    max: 
        milliseconds: 1 // 配置项

同时将该实现类注册为SPI
resources/META-INF/services/org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator
cn.config.shardingrule.ShardingIdGenerator
其余中间件
MyCat
http://www.mycat.org.cn/
比拟重，不举荐

关于mysql:Mysql分表那些事

⾏表达式分⽚策略

评论

发表回复取消回复

更多文章

DDN HPC 存储硬件架构设计深度分析

探秘IO500：从Lustre并行文件系统出发，开启HPC存储性能新征程

苹果iOS打包的ipa应用无法安装？一篇文章带你了解可能的原因及排查方法

图解Golang：从零开始实现简易版过期LRU缓存

关于mysql:Mysql分表那些事

⾏表达式分⽚策略

评论

发表回复 取消回复

更多文章

DDN HPC 存储硬件架构设计深度分析

探秘IO500：从Lustre并行文件系统出发，开启HPC存储性能新征程

苹果iOS打包的ipa应用无法安装？一篇文章带你了解可能的原因及排查方法

图解Golang：从零开始实现简易版过期LRU缓存

发表回复取消回复