关于mysql:Mysql分表那些事

阐明：以下内容均不思考数据库之外的解决方案
为什么要分表
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/
比拟重，不举荐

⾏表达式分⽚策略