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）解决：
①并行