MySQL分库分表面试知识总结

场景分析

• Web开发工作，亦或是海量数据开发工作，学习分库、分表、分区等知识都是很有必要的 。
• 面试的时候，也有可能也会被问到。不过作为一个有经验的Coder，不熟悉分库、分表技术确实有些 low。

基础概念

• 分表，能够解决单表数据量过大带来的查询效率下降的问题；
• 分库，面对高并发的读写访问，当数据库master服务器无法承载写操作压力时，不管如何扩展slave服务器，此时都没有意义。此时，则需要通过数据分库策略，提高数据库并发访问能力。
• 优点，分库、分表技术优化了数据存储方式，有效减小数据库服务器的负担、缩短查询响应时间。
• 数据分库、分表存储场景条件

• 关系型数据库
• 主从架构(master-slave)
• 单表数据量在百万、千万级别
• 数据库面临极高的并发访问

• 分库、分表实现策略

• 关键字取模，实现对数据访问进行路由。

分库

• 举例

• 按功能分

• 用户类库、商品类库、订单类库、日志类库等

• 按地区分

• 每个城市或省市一个同样的库，如: db_click_bj、db_click_sh 等

• 主要解决问题：

• 单表过大造成的性能问题；
• 单表过大造成的单服务器空间问题。

• 按某个字段分

• 如：将用户资料附件表分成3个附件分表pre_forum_attachment_[0|1|2]，和1个附件索引表（存储tid和附件id关系），根据tid最后一位判断附件保存在哪个分表中。

• 按日期分表

• 日志类、统计类数据表按年、月、日、周分表。如：点击量统计click_201801、click_201802

• 通过MySQL的merge存储引擎实现

• 需要创建分表、总表，总表需要merge存储引擎。

• 示例代码

create table log_merge (
dt datetime not null,
info varchar (100) not null,
　 index (dt)
) engine = merge
union= (log_2017,log_2018) insert_method = last;      

• 纵向分表常见的方式有根据活跃度分表、根据重要性分表等。
• 主要解决问题：

• 表与表之间资源争用问题；
• 锁争用机率小；
• 实现核心与非核心的分级存储，如UDB登陆库拆分成一级二级三级库；
• 数据库同步压力问题。

• 具体策略

• 经常组合查询的列放在一个表，常用字段的表可考虑Memory引擎。
• 不经常使用的字段单独成表。
• 把text、blob等大字段拆分放在附表。如：把用户文章表分成主表news和从表news_data，主表存标题、关键字、浏览量等，从表存具体内容、模板等。

分库、分表注意事项

• 维度问题

• 针对用户购买记录数据，如果按照用户纬度分表，则每个用户的交易记录都保存在同一表中，所以很快很方便的查找到某用户的购买情况，但是某商品被购买的情况则可能分布在多张表中，查找起来比较麻烦。
• 若按照商品维度分表，方便查找商品购买情况，但查找个人交易记录比较麻烦。
• 常见解决方案：

• 通过扫表方式解决，效率太低，不可行。
• 记录两份数据，一份按照用户纬度分表，一份按照商品维度分表。
• 通过搜索引擎解决，但如果实时性要求很高，则牵涉到实时搜索问题。

• 避免分表join操作。关联的表有可能不在同一数据库中。
• 避免跨库事务

• 避免在一个事务中修改db0、db1中的表，不仅操作复杂，而且影响效率。

• 分表宜多不宜少；避免后期可能二次拆分。
• 尽量同组数据统一DB服务器。例如将卖家a的商品和交易信息都放到db0中，当db1挂了的时候，卖家a相关的东西可以正常使用。即避免多个数据库中的数据产生依赖。

References

• ​​Mysql快速插入千万条测试数据. 造数据.方式有利有弊​​
• ​​mysql中迅速插入百万条测试数据的方法. 造数据. 另3种方法总结​​
• ​​mysql数据库分表及实现. 简单示例​​
• ​​mysql分库分表实战及php代码操作完整实例. php代码实现​​
• ​​MySQL之——基于Amoeba实现读写分离​​