还是无法避免分库分表

为什么要分库分表

数据库慢了不是可以优化sql和索引，加缓存，memcached,redis，数据库读写分离来解决吗，当然可以，不过，数据量太大，这些方案就会不足以应对

一个表格对比

类别	分库分表前	分库分表后
并发情况	Mysql单机，支撑不了高并发	Mysql集群，并发加倍
磁盘情况	磁盘容量几乎撑满	拆分多个表，磁盘使用率降低
SQL性能	数据量加大，SQL越来越慢	单表数据量少，SQL效率提升明显

常用的分库分表中间件

sharding-jdbc：当当开源，属于client层，SQL语法支持比较多，支持分库分表、读写分离、分布式id生成、柔性事务（最大努力送达型事务、TCC事务），一直在维护，社区也比较活跃
mycat：基于proxy层方案，属于目前非常火且不断更新的数据库中间件，支持功能也非常完善
Zebra：Zebra是一个基于JDBC API协议上开发出的高可用、高性能的数据库访问层解决方案，是美团点评内部使用的数据库访问层中间件

sharding-jdbc使用案例

先从sharding-jdbc的使用开始。

1.准备数据库和表

2.创建一个springboot程序然后修改配置文件

# 数据源 db0,db1
sharding.jdbc.datasource.names=db0,db1
# 第一个数据库
sharding.jdbc.datasource.db0.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.db0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.db0.jdbc-url=jdbc:mysql://192.168.199.127:3316/db0?characterEncoding=utf-8
sharding.jdbc.datasource.db0.username=root
sharding.jdbc.datasource.db0.password=123456

# 第二个数据库
sharding.jdbc.datasource.db1.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.db1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.db1.jdbc-url=jdbc:mysql://192.168.199.127:3326/db1?characterEncoding=utf-8
sharding.jdbc.datasource.db1.username=root
sharding.jdbc.datasource.db1.password=123456

# 水平拆分的数据库（表） 配置分库 + 分表策略 行表达式分片策略
# 分库策略 指定分库的因子 这里使用id来分库
sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=id
# 这里是通过id来计算 应该操作那个数据库
sharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=db$->{id % 2}

# 分表策略 其中user为逻辑表 分表主要取决于age行
# 这里是在分库的基础上 添加了分表
sharding.jdbc.config.sharding.tables.user.actual-data-nodes=db$->{0..1}.user_$->{0..1}
sharding.jdbc.config.sharding.tables.user.table-strategy.inline.sharding-column=age
# 分片算法表达式
sharding.jdbc.config.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{age % 2}

# 如果不指定逻辑表 score 对应在db1中的score sharding.jdbc中间件会默认找db0 db1 中的score 但是db0中并没有创建这个表 所以这里就指定他 不找db0中的表
sharding.jdbc.config.sharding.tables.score.actual-data-nodes=db1.score


# 主键 UUID 18位数 如果是分布式还要进行一个设置 防止主键重复
#sharding.jdbc.config.sharding.tables.user.key-generator-column-name=id

# 打印执行的数据库以及语句
sharding.jdbc.config.props..sql.show=true
spring.main.allow-bean-definition-overriding=true

3.Model-User

@Data
@Table(name = "user")
@NoArgsConstructor
@AllArgsConstructor
public class User {

    @Id
    @KeySql(useGeneratedKeys = true)
    private Integer id;

    private String name;

    private Integer age;

}

4.Model-Score

@Data
@Table(name = "score")
@NoArgsConstructor
@AllArgsConstructor
public class Score {

    @Id
    @KeySql(useGeneratedKeys = true)
    private Integer id;

    private Integer userId;

    private Integer score;

}

5.dao

public interface ScoreDao extends BaseMapper<Score>, ExampleMapper<Score> {
}

public interface UserDao extends BaseMapper<User>, ExampleMapper<User> {

    @Select("SELECT \n" +
            "u.id as userId,\n" +
            "s.id as scoreId,\n" +
            "u.name as name,\n" +
            "s.score as score\n" +
            "FROM\n" +
            "user_0 u,score s\n" +
            "WHERE u.id = s.user_id AND u.id = ${userId}")
    UserScoreVo selectScoreByUserId(@Param("userId") Integer userId);

    @Insert("INSERT INTO user(id,name) VALUES (#{user.id},#{user.name})")
    void insert(@Param("user") User user)

}

6.Service

@Service
public class UserService {

    @Autowired
    private UserDao userDao;

    @Autowired
    private ScoreDao scoreDao;


    public void insertUser(User user) {
        userDao.insert(user);
    }


    public User selectUserById(Integer userId) {
        return userDao.selectByPrimaryKey(userId);
    }


    public List<User> selectUserList() {
        Example example = new Example(User.class);
        example.setOrderByClause(" id asc ");
        return userDao.selectByExample(example);
    }


    public List<User> selectByIdAndAge(Integer id, Integer age) {
        User user = new User();
        user.setAge(age);
        user.setId(id);
        return userDao.select(user);
    }

    public List<User> selectByAge(Integer age) {
        User user = new User();
        user.setAge(age);
        return userDao.select(user);
    }

    public List<User> selectByName(String name) {
        User user = new User();
        user.setName(name);
        return userDao.select(user);
    }

    public User selectByOneName(String name) {
        User user = new User();
        user.setName(name);
        return userDao.selectOne(user);
    }

    public List<User> selectByUser(User user) {
        return userDao.select(user);
    }


    public PageInfo<User> pageUser(Integer pageNum, Integer pageSize) {
        PageHelper.startPage(pageNum, pageSize);
        Example example = new Example(User.class);
        example.setOrderByClause(" id asc ");
        example.createCriteria().andLike("name", "%1%");
        List<User> users = userDao.selectByExample(example);
        PageInfo<User> pageInfo = new PageInfo<>(users);
        PageHelper.clearPage();
        return pageInfo;
    }


    public UserScoreVo selectUserScoreByUserId(Integer userId) {

        User user = userDao.selectByPrimaryKey(userId);

        if (user != null) {
            Score score = new Score();
            score.setUserId(userId);
            Score score1 = scoreDao.selectOne(score);

            UserScoreVo userScoreVo = new UserScoreVo();
            userScoreVo.setUserId(user.getId());
            userScoreVo.setScoreId(score1.getId());
            userScoreVo.setName(user.getName());
            userScoreVo.setScore(score1.getScore());
            return userScoreVo;
        } else {
            return null;
        }


    }

    public List<Score> getUserScore(Integer userId) {
        Score score = new Score();
        score.setUserId(userId);
        return scoreDao.select(score);
    }
}

7.test

@Slf4j
@RunWith(SpringRunner.class)
@SpringBootTest
public class UserServiceTest {


    @Autowired
    private UserService userService;

    @Autowired
    private UserDao userDao;

    @Autowired
    private ScoreDao scoreDao;

    // 插入 方法。通过打印的sql执行日志 可以看出
    // id为基数  分配得到 db1 偶数分配到 db0
    // user 中 age 奇数分配到 user_1  偶数分配到user_0
    @Test
    public void insertUser() {


        for (int i = 1; i < 50;i++){
            User user = new User(i,"test"+i,i+11);
            userService.insertUser(user);
        }

        for (int i = 50; i < 100;i++){
            User user = new User(i,"test"+i,i+10);
            userService.insertUser(user);
        }
    }

    // 通过id查询数据 这里也是通过id 确定了数据库
    @Test
    public void selectUserById() {


        User user1 = userService.selectUserById(11);
        log.info(user1.toString());
        User user2 = userService.selectUserById(12);
        log.info(user2.toString());
    }

    // 查询列表 这里是每个数据库中每个表返回值的集合
    // 从结果看出 排序是生效的
    @Test
    public void selectUserList() {
        List<User> users = userService.selectUserList();
        log.info(users.toString());
    }


    // 这里通过 age确定了 是哪张表 但是不确定库 所以两个库都执行了
    @Test
    public void selectByAge(){
        List<User> users = userService.selectByAge(12);
        log.info(users.toString());
        List<User> users1 = userService.selectByAge(13);
        log.info(users1.toString());
    }

    //这里通过 id和age 确定了 库和表 所以就
    @Test
    public void  selectByIdAndAge(){
        List<User> users = userService.selectByIdAndAge(1, 12);
        log.info(users.toString());
    }


    //这里 因为没有指定 id 和 age 所以 是每个库的每个表中搜索结果的集合
    @Test
    public void  selectByName(){
        List<User> test33 = userService.selectByName("test33");
        log.info(test33.toString());
    }

    //这里 如果在数据库中每张表的数据不重复的情况下 可以正常执行
    //如果 任意两个表中 通过都可以通过name获取到数据 就会报错 跟不分库时一样
    @Test
    public void selectByOneName(){
        User user = userService.selectByOneName("test33");
        log.info(user.toString());
    }




    // 这里分页正常进行 条件查询正常进行
    @Test
    public void pageUser(){
        PageInfo<User> pageInfo = userService.pageUser(1, 10);
        for (User user : pageInfo.getList()) {
            log.info(user.getId()+"");
        }
        log.info(pageInfo.toString());
    }




    @Test
    public void selectUserScoreByUserId() {
//        UserScoreVo userScoreVo1 = userService.selectUserScoreByUserId(1);
//        log.info(userScoreVo1.toString());
        UserScoreVo userScoreVo2 = userService.selectUserScoreByUserId(2);
        log.info(userScoreVo2.toString());
    }

    @Test
    public void selectScoreByUserId(){
        List<Score> userScore = userService.getUserScore(1);
        log.info(userScore.toString());
    }


    //这里的事务在数据库中正常进行 如果抛出异常 数据会回滚数据，即使是不在一个数据库中也会分别进行
    @Test
    @Transactional(rollbackFor = Exception.class)
    public void testSingleTableTransactional() throws Exception{
        User user = new User(1,"test111111",2222);
        userDao.updateByPrimaryKey(user);
        throw  new Exception("异常");
    }



    @Test
    @Transactional(rollbackFor = Exception.class)
    public void testTwoTableTransactional() throws Exception{
        User user = new User(2,"test111111",2222);
        userDao.updateByPrimaryKey(user);
        Score score = new Score();
        score.setUserId(1);
        score.setId(1);
        score.setScore(10000);
        scoreDao.updateByPrimaryKey(score);
        throw  new Exception("异常");
    }
}

还是无法避免分库分表

为什么要分库分表

常用的分库分表中间件

sharding-jdbc使用案例

mycat使用案例

Zebra使用案例