分库分表—1.简要概述一

大纲

1.单库版本到分库分表的演进介绍

2.订单系统项目模版

3.完成一次查询全过程

4.磁盘IO为什么这么慢

5.MySQL的索引是如何形成的

6.SQL优化

7.千万级数据优化之加缓存—理论

8.千万级数据优化之加缓存—实战

9.千万级数据优化之读写分离-理论

10.千万级数据优化之读写分离-实战

11.千万级数据优化-水平拆分和垂直拆分

12.分库分表多数据源实战

13.分库分表后订单路由策略和全局ID

14.MyCat和ShardingSphere介绍

15.创建库和表的小工具

16.ShardingSphere数据分片核心原理

17.C端ShardingSphere分片策略实现

18.C端ShardingSphere读写分离实现

19.异构订单实现

20.单库亿级数据怎么迁移到多库多表上

1.单库版本到分库分表的演进介绍

(1)单库版本的电商系统架构

(2)SQL优化阶段

(3)缓存优化阶段

(4)读写分离优化阶段

(5)垂直分库优化阶段

(6)分库分表版本的订单系统架构

2.订单系统项目模版

(1)创建订单的时序图

(2)查询订单列表的时序图

3.完成一次查询全过程

(1)用户发起查询请求到MySQL的流程

(2)MySQL收到查询语句的处理流程

(3)InnoDB引擎对查询语句的处理流程

4.磁盘IO为什么这么慢

(1)一次磁盘IO花费的时间

(2)一次内存IO花费的时间

(3)磁盘IO分为随机读写和顺序读写

(1)一次磁盘IO花费的时间

读写磁头在磁盘扇区上读取或者写入数据花费的时间，也就是一次完整的磁盘IO花费的时间，包括如下三个方面：

一.寻道时间

指的就是读写磁头移动到正确的半径上所需要的时间。寻道时间越短，磁盘IO操作越快。一般磁盘的寻道时间是3-15ms，主流的磁盘寻道时间是5ms。

二.旋转延迟时间

找到正确的磁道后，读写磁头移动到正确的位置上所消耗的时间。一般取磁盘旋转周期的一半作为旋转延迟的近似值：7200转/分 -> 120转/秒 -> 每转1/120秒 -> 每转的一半是1/240秒即4ms。

三.数据传输时间

指的是将数据从磁盘盘片读取或者写入的时间。一般是1ms以内，可以忽略不计。所以主流磁盘的一次磁盘IO的时间为：5ms + 4ms = 9ms。

(2)一次内存IO花费的时间

内存读取一次数据，一般是100ns以内，而1ms = 10^6ns = 100万ns。所以一次磁盘IO花费的时间是一次内存IO花费时间的约9万倍，几万倍。

(3)磁盘IO分为随机读写和顺序读写

一.顺序读写

就是读写磁头按照顺序读写磁盘盘片中的数据，速度还是很快的。MySQL里的binlog和redo日志就是顺序读写的。

二.随机读写

就是读写磁头会随机切换到不同的磁盘盘片的位置，速度比较缓慢耗时。

5.MySQL的索引是如何形成的

(1)数据页的结构

(2)数据区中存放的多个数据行组成单向链表

(3)多个数据页则组成双向链表

(4)索引页会存放数据页页号 + 其最小主键ID

(5)多个索引页的展示图

(1)数据页的结构

(2)数据区中存放的多个数据行组成单向链表

(3)多个数据页则组成双向链表

(4)索引页会存放数据页页号 + 其最小主键ID

(5)多个索引页的展示图

数据页和索引页组成的B+树中，叶子节点是数据页，非叶子节点是索引页。B+树的时间复杂度是log(n)。

6.SQL优化

(1)SQL优化流程

(2)SQL优化中的join优化原理算法

(1)SQL优化流程

判断join语句 -> 判断where条件 -> 判断聚合函数 -> 判断排序

(2)SQL优化中的join优化原理算法

SQL优化中不管是对where语句、聚合函数、还是排序操作的优化，优化起来相对而言会简单点，为对应的字段创建合适的索引即可。但是join语句这块的优化涉及到一些比较重要的如下原理了。

简单来说，在MySQL中使用join语句关联2张表的话，比如执行这条SQL：

select t1.order_no, t2.product_id 
from order_info t1 
left join order_item_detail t2 
on t1.order_no = t2.order_no

这个时候，join关联查询的过程是什么样子的呢？其实，这个就取决于当前join语句用到的算法了，join语句一共有3种算法。

第一种：最基础的是Simple Nested Loop算法

简单嵌套循环算法，相当于双重for循环。

第二种：Block Nested Loop算法

MySQL提供了一个Join Buffer， 但是Join Buffer大小为256K，内存有限。当然我们也可以通过join buffer size参数调节Join Buffer的大小。

第三种：Index Nested Loop算法

原来的匹配次数为：驱动表行数 * 被驱动表行数，而现在变成了：驱动表行数 * 被驱动表索引的高度。这样就极大地减少了被驱动表的匹配次数，极大地提升了join的性能。

总结：如果join关联查询能使用到索引，MySQL就会使用Index Nested Loop算法，查询效率会比较高。如果无法使用Index Nested Loop算法，MYSQL默认会使用Block Nested Loop算法，查询效率会很慢。

7.千万级数据优化之加缓存—理论

(1)高峰期导致数据库压力很大

(2)加缓存进行流量削峰

(3)如何提高缓存命中率

(1)高峰期导致数据库压力很大

通过对SQL优化特别是添加索引，可以提升查询的速度。此时已将SQL的一次查询稳定在300ms以下，但有一天DBA告知有一条SQL偶尔会超过2s。

为什么SQL查询时间会偶尔激增超过2s呢？通过排查发现，这条SQL在平时是没有问题的，一般稳定在300ms以下。经过分析和发现，在高峰期这条SQL才偶尔会超过2s。原因是在高峰期的时候，这台服务器的资源占用非常高。

所以定位到问题的原因是：高峰期时，大量请求会跑到MySQL数据库，从而导致这台服务器的CPU和内存占用率迅速飙升，最终导致数据库查询非常慢。

(2)加缓存进行流量削峰

解决方案：通过加缓存解决问题。

缓存的目的：为了流量削峰，减轻MySQL的负载，让MySQL稳定的去提供读写。

虽然说缓存非常好用，但是需要注意缓存命中率。缓存命中率 = 命中缓存的结果数 / 请求的缓存数。缓存命中率是衡量缓存有效性的重要指标。也就是说，命中率越高缓存的使用率越高。

(3)如何提高缓存命中率

第一：选择合适的业务场景

缓存适合读多写少的场景，最好是高频访问的场景，这里以已经完成订单为例。

第二：合理设置缓存容量

缓存容量如果很小则会触发Redis内存淘汰机制，导致key被删除，从而没能起到缓存效果。

一般都是二八原则，总数据量的20%会放在Redis里面，通常会根据业务场景取总数据量的15%到30%放到Redis中。

第三：控制缓存的粒度

单个key的数据单位越小，这个缓存就越不容容易更改，粒度越小缓存率越高。

第四：灵活设置缓存过期时间

如果缓存过期的时间设置不好，则可能会导致key同时失效。从而导致所有的请求都同时到数据库当中，这就是缓存击穿。

第五：避免缓存的穿透

查询一条数据，先从Redis缓存里查。如果没有，再从数据库中查，数据库中也没有，就说明缓存穿透。如果大量的请求过来，Redis中没有，数据库中也没有，就会把数据打穿。缓存穿透会造成缓存雪崩，导致最后服务崩溃。

解决办法：可以在缓存中给这个查询的请求设置一个空对象，让这个请求拿着空对象返回，然后设置到Redis缓存里。

第六：做好缓存预热

我们可以提前将数据库的数据(热点数据)放入到Redis缓存当中，这样第一次查询时就可以直接走缓存了。

下面是各个硬件的执行时间和容量：

CPU：20-50ns，1-32M级别

内存：100ns，32-96GB级别

磁盘：3-5ms，TB或PB级别

8.千万级数据优化之加缓存—实战

(1)业务场景是对历史订单进行查询

(2)Redis缓存的代码

(1)业务场景是对历史订单进行查询

由于历史订单的状态不会发生变化，符合读多写少的场景。所以可把用户的查询结果放入Redis缓存，并设置过期时间为1小时。只要缓存失效前再次查询，就会查Redis进行流量削峰来减轻数据库压力。

(2)Redis缓存的代码

缓存key的生成规则是：用户的ID+当前页+页数。

@RestController
@RequestMapping(value = "/user/order")
public class UserOrderController {
    @Autowired
    private UserOrderInfoService userOrderInfoService;
    ...
    // 查询订单列表
    @PostMapping("/queryOrderInfoList")
    public PageResponse queryOrderInfoList(@RequestBody OrderInfoQuery orderInfoQuery) {
        //开始计时
        long bTime = System.currentTimeMillis();
        try {
            Page<OrderInfoVO> orderInfoPage = userOrderInfoService.queryOrderInfoList(orderInfoQuery);
            //关闭分段计时
            long eTime = System.currentTimeMillis();
            //输出
            log.info("查询用户订单耗时：" + (eTime - bTime));
            return PageResponse.success(orderInfoPage);
        } catch (Exception e) {
            log.info(e.getMessage());
            return PageResponse.error(e.getMessage());
        }
    }
    ...
}

//用户订单服务实现
@Service
public class UserOrderInfoServiceImpl implements UserOrderInfoService {
    private final static Long FINISH = 50L;
    @Autowired
    private OrderInfoRepository orderInfoRepository;
    @Autowired
    private RedisUtils redisUtils;
    ...
    @Override
    public Page<OrderInfoVO> queryOrderInfoList(OrderInfoQuery orderInfoQuery) {
        OrderValidation.checkVerifyOrderQuery(orderInfoQuery);
        Page<OrderInfoVO> page = new Page<OrderInfoVO>();
        page.setCurrent(orderInfoQuery.getCurrent());
        page.setSize(orderInfoQuery.getSize());
        //查询已完成的订单
        if (FINISH.equals(orderInfoQuery.getOrderStatus())) {
            //组装redisKey
            String redisKey = orderInfoQuery.getUserId() + orderInfoQuery.getCurrent().toString() + orderInfoQuery.getSize().toString();
            //获取redis缓存
            Object redisObject = redisUtils.get(redisKey);
            //redis为空则从数据库中查询
            if (Objects.isNull(redisObject)) {
                Page<OrderInfoVO> userOrderInfoVOPage = orderInfoRepository.queryOrderInfoList(page, orderInfoQuery);
                //设置redis缓存，过期时间为一小时
                redisUtils.set(redisKey, userOrderInfoVOPage, 3600L, TimeUnit.SECONDS);
                return userOrderInfoVOPage;
            }
            log.info("从redis中获取数据, key: {}", redisKey);
            return (Page<OrderInfoVO>) redisObject;
        }
        return orderInfoRepository.queryOrderInfoList(page, orderInfoQuery);
    }
    ...
}

9.千万级数据优化之读写分离-理论

(1)读写分离的业务背景

(2)主从复制的原理是什么

(3)主从复制的几种模式

(1)读写分离的业务背景

营销系统那边做了一些活动，导致订单请求量突增，大量下单的用户可能会不断刷新订单来查询订单是否配送等信息。

此时大量的请求会打到MySQL上，而单库又抗不了这么多读请求。这就会导致数据库负载很高，从而严重降低MySQL的查询效率。现在我们缓存也加过了，但是数据库负载还是很高，此时该怎么办？

其实很简单，既然单个库扛不住，那就搞2个库一起来抗。因为这对于订单系统来说是典型的读多写少场景，所以在这个场景下可以搞个一主两从的架构来进行优化，就像如下这样：

也就是写数据走主库，而读数据走从库，并且多个从库可以一起来抗大量的读请求。关键的一点是，从库会通过主从复制，从主库中不断的同步数据，以此来保证从库的数据和主库一致。所以想要实现读写分离，那么就先要了解主从复制的具体原理。

(2)主从复制的原理是什么

我们以MySQL一主两从架构为例，也就是一个Master节点下有两个Slave节点。在这套架构下，写请求统一交给Master节点处理，而读请求交给Slave节点处理。

为了保证Slave节点和Master节点的数据一致性：Master节点在写入数据后，同时会把数据复制一份到自己的各个Slave节点上。

在复制过程中一共会使用到三个线程：一个是Binlog Dump线程，位于Master节点上。另外两个线程分别是IO线程和SQL线程，它们都分别位于Slave节点上。如下图示：

主从复制的核心流程：

步骤一：首先，当Master节点接收到一个写请求时，这个写请求可能是增删改操作。此时会把写请求的操作都记录到BinLog日志中。

步骤二：然后，Master节点会把数据复制给Slave节点，这个过程首先要每个Slave节点连接到Master节点上。当Slave节点连接到Master节点上时，Master节点会为每一个Slave节点分别创建一个BinLog Dump线程。每个BinLog Dump线程用于向各个Slave节点发送BinLog日志。

步骤三：BinLog Dump线程会读取Master节点上的BinLog日志，并将BinLog日志发送给Slave节点上的IO线程。

步骤四：Slave节点上的IO线程接收到BinLog日志后，会将BinLog日志先写入到本地的RelayLog中。Slave节点的RelayLog中就保存了Master的BinLog Dump线程发送过来的BinLog日志。

步骤五：最后，Slave节点上的SQL线程就会来读取RelayLog中的BinLog日志，将其解析成具体的增删改操作。然后把这些在Master节点上进行过的操作，重新在Slave节点上也重做一遍，达到数据还原的效果。这样就可保证Master节点和Slave节点的数据一致性。

(3)主从复制的几种模式

MySQL的主从复制，分为全同步复制、异步复制、半同步复制和增强半同步复制这四种。

模式一：全同步复制

全同步复制就是当主库执行完一个事务后，所有从库也必须执行完该事务才可以返回结果给客户端。因此虽然全同步复制数据一致性得到保证，但主库完成一个事务需等待所有从库也完成，性能较低。

模式二：异步复制

异步复制就是当主库提交事务后，会通知BinLog Dump线程发送BinLog日志给从库。一旦BinLog Dump线程将BinLog日志发送给从库后，无需等从库也同步完事务，主库就会将处理结果返回给客户端。

因为主库只管自己执行完事务，就可以将处理结果返回给客户端，而不用关心从库是否执行完事务。这就可能导致短暂的主从数据不一致，比如刚在主库插入的新数据，如果马上在从库查询，就可能查询不到。

而且当主库提交事务后，如果宕机挂掉了，此时可能BinLog还没来得及同步给从库。这时如果为了恢复故障切换主从节点，就会出现数据丢失的问题。所以异步复制虽然性能高，但数据一致性上是较弱的。

不过MySQL主从复制，默认采用的就是异步复制这种复制策略。

模式三：半同步复制

半同步复制就是在同步和异步中做了折中选择，半同步主从复制的过程如下图示：

当主库提交事务后，至少还需要一个从库返回接收到BinLog日志，并成功写入到RelayLog的消息，这时主库才会将处理结果返回给客户端。相比前2种复制方式，半同步复制较好地兼顾了数据一致性以及性能损耗的问题。

但半同步复制也存在以下几个问题：

问题一：半同步复制的性能相比异步复制有所下降。因为异步复制是不需要等待任何从库是否接收到数据的响应，而半同步复制则需要等待至少一个从库确认接收到binlog日志的响应，性能上是损耗更大。

问题二：如果超过了配置的主库等待从库响应的最大时长，半同步复制就会变成异步复制，此时异步复制的问题同样会出现。

问题三：在MySQL 5.7.2之前的版本中，半同步复制存在着幻读问题。当主库成功提交事务并处于等待从库确认过程，这时从库都还没来得及返回处理结果给客户端，但因为主库存储引擎内部已经提交事务了，所以其他客户端是可以到从主库中读到数据的。但是如果下一秒主库突然挂了，那么下一次请求过来时，就只能把请求切换到从库中。而因为从库还没从主库同步完数据，所以从库就读不到这条数据了。和上一秒读取数据的结果对比，就造成了幻读的现象。注意这不是数据丢失，因为后续从库会同步完数据的。

模式四：增强半同步复制

增强半同步复制是MySQL 5.7.2后的版本对半同步复制做的一个改进。原理上几乎是一样的，主要是解决幻读的问题。主库配置了参数rpl_semi_sync_master_wait_point = AFTER_SYNC后，主库在提交事务前必须先收到从库数据同步完成的确认信息，才能提交事务，以此来解决幻读问题。增强半同步主从复制过程如下：

10.千万级数据优化之读写分离-实战

(1)读写分离配置核心组件流程图

(2)读写分离的实现步骤

(1)读写分离配置核心组件流程图

(2)读写分离的实现步骤

步骤一：配置文件中配置主从库连接信息

application.yaml配置文件

spring:
  datasource:
    masters:
    - url: jdbc:mysql://192.168.10.8:3307/order_db?autoReconnect=true&useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT
      username: root
      password: Sharding@Master#1990
      driver-class-name: com.mysql.cj.jdbc.Driver
    slaves:
    - url: jdbc:mysql://192.168.10.8:3308/order_db?autoReconnect=true&useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT
      username: root
      password: Sharding@Slave#1990
      driver-class-name: com.mysql.cj.jdbc.Driver

步骤二：注入数据源

DataSourceConfig类

//多数据源配置
@Configuration
@ConfigurationProperties(prefix = "spring.datasource")
public class DataSourceConfig {
    //主库数据源信息
    private Map<String, String> masters;

    //从库数据源信息
    private List<Map<String, String>> slaves;

    @Bean
    public DataSource masterDataSource() throws Exception {
        if (CollectionUtils.isEmpty(masters)) {
            throw new Exception("主库数据源不能为空");
        }
        return DruidDataSourceFactory.createDataSource(masters);
    }

    @Bean
    public List<DataSource> slaveDataSources() throws Exception {
        if (CollectionUtils.isEmpty(slaves)) {
            throw new Exception("从库数据源不能为空");
        }
        final List<DataSource> dataSources = new ArrayList<>();
        for (Map map : slaves) {
            dataSources.add(DruidDataSourceFactory.createDataSource(map));
        }
        return dataSources;
    }

    @Bean
    @Primary
    @DependsOn({"masterDataSource", "slaveDataSources"})
    public DataSourceRouter routingDataSource() throws Exception {
        final Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DataSourceContextHolder.MASTER, masterDataSource());
        for (int i = 0; i < slaveDataSources().size(); i++) {
            targetDataSources.put(DataSourceContextHolder.SLAVE + i, slaveDataSources().get(i));
        }
        final DataSourceRouter routingDataSource = new DataSourceRouter();
        routingDataSource.setTargetDataSources(targetDataSources);
        routingDataSource.setDefaultTargetDataSource(masterDataSource());
        return routingDataSource;
    }

    //设置事务，事务需要知道当前使用的是哪个数据源才能进行事务处理
    @Bean
    public DataSourceTransactionManager dataSourceTransactionManager() throws Exception {
        return new DataSourceTransactionManager(routingDataSource());
    }

    public Map<String, String> getMasters() {
        return masters;
    }

    public void setMasters(Map<String, String> masters) {
        this.masters = masters;
    }

    public List<Map<String, String>> getSlaves() {
        return slaves;
    }

    public void setSlaves(List<Map<String, String>> slaves) {
        this.slaves = slaves;
    }
}

步骤三：数据源切换上下文，其中使用了ThreadLocal保存当前线程的数据源

DataSourceContextHolder类

//数据源上下文
public class DataSourceContextHolder {
    public static final String MASTER = "MASTER";
    public static final String SLAVE = "SLAVE";
    private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();

    //默认写模式
    public static String getDataSourceType() {
        return CONTEXT_HOLDER.get() == null ? MASTER : CONTEXT_HOLDER.get();
    }

    public static void setDataSourceType(String dataSourceType) {
        if (dataSourceType == null) {
            log.error("dataSource为空");
            throw new NullPointerException();
        }
        log.info("设置dataSource：{}", dataSourceType);
        CONTEXT_HOLDER.set(dataSourceType);
    }

    public static void removeDataSourceType() {
        CONTEXT_HOLDER.remove();
    }
}

步骤四：继承AbstractRoutingDataSource类重写determineCurrentLookupKey方法实现数据源动态切换

DataSourceRouter类继承了SpringBoot的AbstractRoutingDataSource类，并且重写了determineCurrentLookupKey方法来实现数据源动态切换。

//动态主从数据源切换
public class DataSourceRouter extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        log.info("当前数据源为" + DataSourceContextHolder.getDataSourceType());
        //返回选择的数据源
        return DataSourceContextHolder.getDataSourceType();
    }
}

步骤五：创建读库的自定义注解

//自定义读库注解
//被这个注解的方法使用读库
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD, ElementType.TYPE})
@Inherited
public @interface ReadOnly {

}

步骤六：切面类DynamicDataSourceAsepct

//数据源切面
@Aspect
@Component
public class DynamicDataSourceAspect implements Ordered {
    //在Service层方法获取DataSource对象之前，在切面中指定当前线程数据源Slave
    @Before(value = "execution(* *(..)) && @annotation(readOnly)")
    public void before(JoinPoint point, ReadOnly readOnly) {
        log.info(point.getSignature().getName() + "走从库");
        DataSourceContextHolder.setDataSourceType(DataSourceContextHolder.SLAVE);
    }

    @After(value = "execution(* *(..)) && @annotation(readOnly)")
    public void restoreDataSource(JoinPoint point, ReadOnly readOnly) {
        log.info(point.getSignature().getName() + "清除数据源");
        //方法执行完后清除数据源
        DataSourceContextHolder.removeDataSourceType();
    }

    @Override
    public int getOrder() {
        return 0;
    }
}

步骤七：在需要走读库的业务方法上添加@ReadOnly注解

这样执行这些业务方法时就会被切面拦截修改数据源，从而走读库进行查询。

//用户订单服务实现
@Service
public class UserOrderInfoServiceImpl implements UserOrderInfoService {
    ...
    //获取订单详情
    @ReadOnly
    @Override
    public OrderDetailVO getOrderDetail(String orderNo) {
        return orderInfoRepository.getOrderDetail(orderNo);
    }
    ...
}

11.千万级数据优化-水平拆分和垂直拆分

(1)什么时候考虑水平拆分和垂直拆分

(2)什么是垂直拆分

(3)什么是水平拆分

(1)什么时候考虑水平拆分和垂直拆分

当数据库的数据量越来越大时：首先可以从SQL入手进行优化，比如通过加入索引。然后可以使用缓存来进行优化，适合读多写少的场景。接着可以通过主从复制和读写分离来实现优化。此时增删改已全部走主库，查询都走从库，已经大大提升了读数据的能力。但是没有办法提升主库写数据能力，于是可以考虑对数据进行水平拆分和垂直拆分了。

(2)什么是垂直拆分

原来很多模块共用一个数据库资源，经过垂直分库后，商品模块、订单模块、用户模块等使用上自己单独的数据资源，于是各模块的资源竞争就不存在了。

垂直拆分的好处：

一.减轻了数据库的压力

二.每个数据库分摊数据，提高了查询的效率

三.每个数据库访问的CPU、内存、网络压力变小

四.业务更加清晰

五.解耦

六.系统扩展也变得容易

垂直拆分带来的不足：

一.系统的复杂性增加了

二.增加了多个数据库数据表联查的复杂性

三.事务处理变得麻烦

四.垂直拆分也解决不了单表数据量很大的问题

(3)什么是水平拆分

单表数据量很大可能会引起接口查询超时的问题。

履约系统会通过Dubbo发起RPC请求调用订单系统。假如设置的超时间为1s，此时订单模块的查询时间就已经快1s了，很容易超时。而一旦延长超时时间成5s，那么履约系统的的线程池就很容易被打满，导致资源迅速被耗尽，甚至会导致履约系统的服务雪崩。所以垂直拆分解决不了单表数据量很大的问题，需要水平拆分。

水平拆分时表的结构不会发生改变，水平拆分分为：水平拆表、水平分库、水平分库分表。

一.水平拆表如下

二.水平拆库如下

三.水平分库分表

水平拆分带来的不足：

一.水平拆分过程比较复杂

二.事务处理变得复杂

三.多库多表联查难度加大

四.水平拆分之后单表的数据会分散到不同的数据源中(多数据源管理问题)