Mysql分表那些事

toby1024

**
说明:以下内容均不考虑数据库之外的解决方案

为什么要分表

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.org/document/current/cn/overview/#shardingsphere-jdbc

逻辑表

⽔平拆分的数据库(表)的相同逻辑和数据结构表的总称。
例:订单数据根据主键尾数拆分为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
20210408165759.jpg

resources/META-INF/services/org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator

cn.config.shardingrule.ShardingIdGenerator

其他中间件

MyCat

http://www.mycat.org.cn/

image.png
比较重,不推荐

阅读 2.3k

奔波~儿爸

15 声望
1 粉丝
0 条评论

奔波~儿爸

15 声望
1 粉丝
文章目录
宣传栏