mysql面试之分库分表总结

1.为什么要分库分表

（1）为什么要分库
①问题背景：
在业务量剧增的情况下：
a.磁盘容量被撑爆；
b.数据库的连接数有限，高并发场景下，会出现too many connections报错。
②好处：
a.解决了单库大数据，高并发的性能瓶颈；
b.降低单机硬件资源的瓶颈。
（2）为什么要分表
①问题背景：
a.单表数据量太大，做了很多优化仍然无法提升效率
b.索引一般是B+树存储结构，B+树高度增高，查询会过慢
②好处
a.优化单一表数据量过大而产生的性能问题
b.避免IO争抢并减少锁表的几率

2.分库分表有哪些中间件，不同的中间件都有什么优点和缺点？

（1）目前流行的分库分表中间件比较多：
Sharding-JDBC
cobar
Mycat
Atlas
TDDL（淘宝）
vitess
（2）不同的中间件都有什么优点和缺点
①sharding-jdbc：优点：不用部署，运维成本低； 缺点：耦合度高，各个系统都依赖sharding-jdbc，系统升级困难
②mycat：优点：耦合度低，系统升级容易 缺点：需要部署，运维成本高

3.分库分表的方式(水平分库,垂直分库,水平分表,垂直分表)

3.1 水平分库

（1）水平分库是什么？
把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。
（2）例子：将店铺ID为单数的和店铺ID为双数的商品信息分别放在两个库中

3.2 垂直分库

（1）垂直分库是什么？
将表按业务分类，然后分布在不同数据库，并且可以将这些数据库部署在不同服务器上，从而达到多个服务器共同分摊压力的效果
（2）例子：由于商品信息与商品描述业务耦合度较高，因此一起被存放在PRODUCT_DB(商品库)；而店铺信息相对独立，因此单独被存放在STORE_DB(店铺库)。

3.3 水平分表

（1）水平分表是什么？
是在同一个数据库内，把同一个表的数据按一定规则拆到多个表中

3.4 垂直分表

（1）垂直分表是什么？
将一个表按照字段分成多表，每个表存储其中一部分字段。
（2）通常我们按以下原则进行垂直拆分:
①把不常用的字段单独放在一张表;
②把text，blob等大字段拆分出来放在附表中;
③经常组合查询的列放在一张表中;

4.分库分表带来的问题

4.1 事务一致性问题

由于分库分表把数据分布在不同库甚至不同服务器，不可避免会带来分布式事务问题。

4.2 跨节点关联查询

（1）在没有分库前，我们检索商品时可以通过以下SQL对店铺信息进行关联查询

SELECT p.*,r.[地理区域名称],s.[店铺名称],s.[信誉]
 FROM [商品信息] p 
LEFT JOIN [地理区域] r ON p.[产地] = r.[地理区域编码]
 LEFT JOIN [店铺信息] s ON p.id = s.[所属店铺]
 WHERE...ORDER BY...LIMIT...

（2）但垂直分库后[商品信息]和[店铺信息]不在一个数据库，甚至不在一台服务器，无法进行关联查询。可将原关联查询分为两次查询，第一次查询的结果集中找出关联数据id，然后根据id发起第二次请求得到关联数据，最后将获得到的数据进行拼装。

4.3 跨节点分页、排序函数

跨节点多库进行查询时，limit分页、order by排序等问题，就变得比较复杂了。需要先在不同的分片节点中将数据进行排序并返回，然后将不同分片返回的结果集进行汇总和再次排序。

4.4 主键避重

在分库分表环境中，由于表中数据同时存在不同数据库中，主键值使用的自增长将无法使用，某个分区数据库生成的ID无法保证全局唯一。因此需要单独设计全局主键，以避免跨库主键重复问题。

5.现在有一个未分库分表的系统，未来要分库分表， 如何设计才可以让系统从未分库分表动态切换到分库分表上？

简单来说，就是在线上系统里面，之前所有写库的地方，增删改操作，都除了对老库增
删改，都加上对新库的增删改，这就是所谓双写，同时写俩库，老库和新库。
然后系统部署之后，新库数据差太远，用之前说的导数工具，跑起来读老库数据写新
库，写的时候要根据 gmt_modified 这类字段判断这条数据最后修改的时间，除非是读出来
的数据在新库里没有，或者是比新库的数据新才会写。
接着导万一轮之后，有可能数据还是存在不一致，那么就程序自动做一轮校验，比对
新老库每个表的每条数据，接着如果有不一样的，就针对那些不一样的，从老库读数据再次
写。反复循环，直到两个库每个表的数据都完全一致为止。
接着当数据完全一致了，就 ok 了，基于仅仅使用分库分表的最新代码，重新部署一次，
不就仅仅基于分库分表在操作了么，还没有几个小时的停机时间，很稳。所以现在基本玩儿
数据迁移之类的，都是这么干了。

6.如何设计可以动态扩容缩容的分库分表方案？

一开始上来就是 32 个库，每个库 32 个表，1024 张表
我可以告诉各位同学说，这个分法，第一，基本上国内的互联网肯定都是够用了，第
二，无论是并发支撑还是数据量支撑都没问题, 每个库正常承载的写入并发量是 1000，那么
32 个库就可以承载 32 * 1000 = 32000 的写并发，如果每个库承载 1500 的写并发，32 * 1500
= 48000 的写并发，接近 5 万/s 的写入并发，前面再加一个 MQ，削峰，每秒写入 MQ 8 万
条数据，每秒消费 5 万条数据。
有些除非是国内排名非常靠前的这些公司，他们的最核心的系统的数据库，可能会出
现几百台数据库的这么一个规模，128 个库，256 个库，512 个库 1024 张表，假设每个表放
500 万数据，在 MySQL 里可以放 50 亿条数据 每秒的 5 万写并发，总共 50 亿条数据，对于
国内大部分的互联网公司来说，其实一般来说都够了 谈分库分表的扩容，第一次分库分表，
就一次性给他分个够，32 个库，1024 张表，可能对大部分的中小型互联网公司来说，已经
可以支撑好几年了 一个实践是利用 32 * 32 来分库分表，即分为 32 个库，每个库里一个表
分为 32 张表。一共就是 1024 张表。根据某个 id 先根据 32 取模路由到库，再根据 32 取模
路由到库里的表。
刚开始的时候，这个库可能就是逻辑库，建在一个数据库上的，就是一个 mysql 服务器
可能建了 n 个库，比如 16 个库。后面如果要拆分，就是不断在库和 mysql 服务器之间做迁
移就可以了。然后系统配合改一下配置即可。

7.如何实现 mysql 的读写分离？MySQL 主从复制原理的是啥？

（1）如何实现 mysql 的读写分离？
基于主从复制架构，一个主库写，多个从库读
（2）MySQL 主从复制原理：
①主库将变更写入binlog日志
②从库连接到主库，通过IO线程将主库binlog日志拷贝到本地，写入到中继日志中
③从库有一个sql线程会从中继日志读取binlog，相当于在本地再执行一遍SQL
（3）存在两个问题：
①主库是并行执行，从库是串行执行，有概率出现刚写入主库的数据是读不到的
②主库突然宕机，恰好数据还没同步到从库，造成数据丢失
（4）解决：
①并行复制，解决主从同步延时问题：从库开启多个线程并行读取日志
②半同步复制，解决主库数据丢失问题：主库接收到从库ack后才认为操作成功

如何评估分库数量

（1）对于MySQL来说的话，一般单库超过5千万记录，DB的压力就非常大了。所以分库数量多少，需要看单库处理记录能力有关。
（2）如果分库数量少，达不到分散存储和减轻DB性能压力的目的；如果分库的数量多，对于跨多个库的访问，应用程序需要访问多个库。
（3）一般是建议分4~10个库，我们公司的企业客户信息，就分了10个库

分库分表之后，id 主键如何处理？(分布式ID)

（1）使用UUID或者雪花算法，
（2）好处:基于本地生成，不基于数据库
（3）缺点：太长，作为主键性能太差；UUID不具有有序性，会造成B+树有过多的随机写操作，频繁修改树结构，从而导致性能下降

分表要停服嘛？不停服怎么做？

不用停服，主要分五个步骤：
（1）编写代理层，加个开关（控制访问新的DAO还是老的DAO，或者是都访问），灰度期间，还是访问老的DAO。
（2）开启双写，既在旧表新增和修改，也在新表新增和修改。日志或者临时表记下新表ID起始值，旧表中小于这个值的数据就是存量数据，这批数据就是要迁移的。
（3）通过脚本把旧表的存量数据写入新表。
（4）停读旧表改读新表，此时新表已经承载了所有读写业务，但是这时候不要立刻停写旧表，需要保持双写一段时间。
（5）当读写新表一段时间之后，如果没有业务问题，就可以停写旧表啦

简单回答：
1.加开关，控制访问老的dao还是新的dao，或者都访问，灰度期间还是访问老的
2.开启双写，旧表和新表都进行新增和修改，日志记录新表id起始值，旧表中小于这个值就是要迁移的
3.通过脚本把旧表数据写入新表
4.停读旧表改读新表，此时不要立刻停写旧表，需要保持一段时间
5.当读写新表一段时间，如果业务没有问题，就可以停写新表了

分库分表后的分页问题

（1）在各个数据库节点查到对应结果，在代码端汇聚再分页。优点是业务无损，精准返回所需数据；缺点：返回过多数据，增大网络传输
（2）业务妥协，不允许跳页查询

order by,group by等聚合函数问题

跨节点的count,order by,group by以及聚合函数等问题，都是一类的问题，它们一般都需要基于全部数据集合进行计算。可以分别在各个节点上得到结果后，再在应用程序端进行合并。

MySQL分区

（1）分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，分区后，表还是一张表，但数据分散到各个分散的位置了。
（2）但是分区通常比较不建议使用，因为在mysql规范中写到对分区表的缺点：分区表对分区键有严格要求；分区表在表变大后，执⾏行DDL、SHARDING、单表恢复等都变得更加困难。因此禁止使用分区表，并建议业务端手动SHARDING。

Sharding-JDBC

Sharding-JDBC基础入门

Sharding-JDBC作用

Sharding-JDBC的核心功能为数据分片和读写分离，通过Sharding-JDBC，应用可以透明的使用jdbc访问已经分库分表、读写分离的多个数据源，而不用关心数据源的数量以及数据如何分布。

分片规则配置

分片规则配置是sharding-jdbc进行对分库分表操作的重要依据，配置内容包括：数据源、主键生成策略、分片策略等
（1）首先定义数据源m1，并对m1进行实际的参数配置。
（2）指定t_order表的数据分布情况，他分布在m1.t_order_1，m1.t_order_2
（3）指定t_order表的主键生成策略为SNOWFLAKE，SNOWFLAKE是一种分布式自增算法，保证id全局唯一
（4）定义t_order分片策略，order_id为偶数的数据落在t_order_1，为奇数的落在t_order_2，分表策略的表达式为
t_order_$->{order_id % 2 + 1}

# 以下是分片规则配置
# 定义数据源
spring.shardingsphere.datasource.names = m1
spring.shardingsphere.datasource.m1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver‐class‐name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url = jdbc:mysql://localhost:3306/order_db?useUnicode=true
spring.shardingsphere.datasource.m1.username = root
spring.shardingsphere.datasource.m1.password = root
# 指定t_order表的数据分布情况，配置数据节点
spring.shardingsphere.sharding.tables.t_order.actual‐data‐nodes = m1.t_order_$‐>{1..2}
# 指定t_order表的主键生成策略为SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.key‐generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key‐generator.type=SNOWFLAKE
# 指定t_order表的分片策略，分片策略包括分片键和分片算法 
spring.shardingsphere.sharding.tables.t_order.table‐strategy.inline.sharding‐column = order_id
spring.shardingsphere.sharding.tables.t_order.table‐strategy.inline.algorithm‐expression = 
t_order_$‐>{order_id % 2 + 1}

流程分析

（1）解析sql，获取片键值，在本例中是order_id
（2）Sharding-JDBC通过规则配置 t_order_$->{order_id % 2 + 1}，知道了当order_id为偶数时，应该往t_order_1表插数据，为奇数时，往t_order_2插数据。
（3）于是Sharding-JDBC根据order_id的值改写sql语句，改写后的SQL语句是真实所要执行的SQL语句。
（4）执行改写后的真实sql语句
（5）将所有真正执行sql的结果进行汇总合并，返回。

Sharding-JDBC集成方式

（1）Spring Boot Yaml 配置

 server:
  port: 56081
  servlet:
    context‐path: /sharding‐jdbc‐simple‐demo
 spring:
  application:
    name: sharding‐jdbc‐simple‐demo
  http:
    encoding:
      enabled: true
      charset: utf‐8
      force: true
  main:
    allow‐bean‐definition‐overriding: true
  shardingsphere:
    datasource:
      names: m1
      m1:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/order_db?useUnicode=true
        username: root
        password: mysql
    sharding:
      tables:
        t_order:
          actualDataNodes: m1.t_order_$‐>{1..2}
          tableStrategy:
            inline:
              shardingColumn: order_id
              algorithmExpression: t_order_$‐>{order_id % 2 + 1}
          keyGenerator:
            type: SNOWFLAKE
            column: order_id
    props:
      sql:
        show: true
 mybatis:
  configuration:
    map‐underscore‐to‐camel‐case: true
 swagger:
  enable: true

（2）Java 配置类

 @Configuration
 public class ShardingJdbcConfig {
 
    // 定义数据源
    Map<String, DataSource> createDataSourceMap() {
        DruidDataSource dataSource1 = new DruidDataSource();
        dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource1.setUrl("jdbc:mysql://localhost:3306/order_db?useUnicode=true");
        dataSource1.setUsername("root");
        dataSource1.setPassword("root");
        Map<String, DataSource> result = new HashMap<>();
        result.put("m1", dataSource1);
        return result;
    }
    // 定义主键生成策略
    private static KeyGeneratorConfiguration getKeyGeneratorConfiguration() {
        KeyGeneratorConfiguration result = new 
KeyGeneratorConfiguration("SNOWFLAKE","order_id");
        return result;
    }
 
    // 定义t_order表的分片策略
    TableRuleConfiguration getOrderTableRuleConfiguration() {
        TableRuleConfiguration result = new TableRuleConfiguration("t_order","m1.t_order_$‐>
 {1..2}");
        result.setTableShardingStrategyConfig(new 
InlineShardingStrategyConfiguration("order_id", "t_order_$‐>{order_id % 2 + 1}"));
        result.setKeyGeneratorConfig(getKeyGeneratorConfiguration());
 
        return result;
    }
    // 定义sharding‐Jdbc数据源
    @Bean
    DataSource getShardingDataSource() throws SQLException {
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration());
        //spring.shardingsphere.props.sql.show = true
        Properties properties = new Properties();
        properties.put("sql.show","true");
        return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), 
	shardingRuleConfig,properties);
    }
 } 

（3）Spring Boot properties配置

# 定义数据源
spring.shardingsphere.datasource.names = m1
 
spring.shardingsphere.datasource.m1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver‐class‐name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url = jdbc:mysql://localhost:3306/order_db?useUnicode=true
spring.shardingsphere.datasource.m1.username = root
spring.shardingsphere.datasource.m1.password = root 
 
# 指定t_order表的主键生成策略为SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.key‐generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key‐generator.type=SNOWFLAKE
 
# 指定t_order表的数据分布情况
spring.shardingsphere.sharding.tables.t_order.actual‐data‐nodes = m1.t_order_$‐>{1..2}
 
# 指定t_order表的分表策略
spring.shardingsphere.sharding.tables.t_order.table‐strategy.inline.sharding‐column = order_id
spring.shardingsphere.sharding.tables.t_order.table‐strategy.inline.algorithm‐expression = t_order_$‐>{order_id % 2 + 1}