[转]mysql分布式-分片篇
本文转自:https://www.jianshu.com/p/458f5d4f674c
需求分析
1,⽤户请求量太⼤, 会导致web应⽤⽆法及时响应 -> 分布式服务器(分散请求到多个服务器上)
2,单表太⼤, 会导致CRUD都成问题, 索引膨胀, 查询超时 -> 拆分表
3,单库太⼤, 会导致单库磁盘空间不⾜; 处理能⼒有限; 出现IO瓶颈 -> 拆分库
作用
分⽚也称为数据拆分 (Shareding), 其主要⼯作就是对单库单表进⾏拆分, 多库多表共同组成完整的数据集合,分⽚可以提⾼吞吐量, 同⼀时间数据的读写完成量更多, 扩充单机存储的容量/读写速度上限!
分类
垂直拆分
垂直拆分分为两种:
1,垂直分表
-
按字段将一张表拆分成多张表
-
对于字段较多的表, 每条记录占⽤的空间也会较多, 导致每次从硬盘中读取的记录以及查询缓存可
缓存的记录数量较少, 影响查询查询效率 -
针对字段多的表就可以采⽤垂直分表来进⾏拆分, 这样可以减少表体积, 提⾼查询效率
拆分规则
-- 可以将字段根据业务逻辑和使⽤的相关性 进⾏分表划分
-- 如: ⽤户名和密码经常配合使⽤, 将其分到⽤户认证表, ⽣⽇和邮箱等个⼈信息经常⼀起访问,将其分到⽤户信息表
使用频率
-- 可以将字段根据常⽤和不常⽤进⾏划分, 并进⾏分表处理
-- 如: 原始⽤户表中包含了多个字段, 其中有常⽤的昵称、 ⼿机号等字段, 也包含不常⽤的邮箱、
⽣⽇ 等字段, 可以根据使⽤频率将其分为两张表: ⽤户基础信息表 和 ⽤户其他信息表

2,垂直分库
- 将⼀个数据库中的多张表拆分到多个数据库(服务器节点)中
**由于本地事务不⽀持跨库操作, 所以应该将 有相关联性的表放在同⼀个库中
分库访问:flask-sqlalchemy 通过配置 SQLALCHEMY_BINDS 允许设置多个数据库URI, 并且每个模型类可以 bind_key 属性 设置⾃⼰对应访问的数据库
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
# 设置多个数据库地址 (⽤于数据操作)
app.config['SQLALCHEMY_BINDS'] = {
'db1': 'mysql://root:mysql@127.0.0.1:3306/db1',
'db2': 'mysql://root:mysql@127.0.0.1:3306/db2'
}
#其他配置
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
app.config['SQLALCHEMY_ECHO'] = True
# 创建组件对象
db = SQLAlchemy(app)
# ⽤户表 存储在db1中
class User(db.Model):
__tablename__ = 't_user'
__bind_key__ = 'db1' # 设置表所在的数据库URI
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(20))
# 地址表 存储在db2中
class Address(db.Model):
__tablename__ = 't_adr'
__bind_key__ = 'db2' # 设置表所在的数据库URI
id = db.Column(db.Integer, primary_key=True)
detail = db.Column(db.String(20), unique=True)
user_id = db.Column(db.Integer)
@app.route('/')
def index():
"""添加数据"""
user1 = User(name='张三')
db.session.add(user1)
db.session.flush()
adr1 = Address(detail='中关村3号', user_id=user1.id)
adr2 = Address(detail='华强北5号', user_id=user1.id)
db.session.add_all([adr1, adr2])
db.session.commit()
# 虽然只调⽤⼀次commit, 但由于需要到两个数据库进⾏操作, 其实是两个数据库分别创建⼀个事务并提交
return "index"
@app.route('/demo1')
def demo1():
"""查询多表数据"""
user1 = User.query.filter_by(name='张三').first()
adrs = Address.query.filter_by(user_id=user1.id).all()
for adr in adrs:
print(adr.detail)
return 'demo1'
if __name__ == '__main__':
# 重置所有继承⾃db.Model的表
db.drop_all()
db.create_all()
app.run(debug=True)
⽔平拆分
水平拆分同样分为两种:
1,水平分表
- 将⼀张表的记录 拆分到多张表中
- 对于记录较多的表, 会出现索引膨胀, 查询超时等问题, 影响⽤户体验
2,⽔平分库
- ⽔平分表后, 将分表分散放在多个数据库节点中
拆分规则
- 时间
按照时间切分, 就是将6个⽉ 前, 甚⾄⼀年前的数据切出去放到另外的⼀张表, 因为随着时间流逝, 这些表的数据 被查询的概率变⼩, 所以没必要和“热数据”放在⼀起, 这个也是“冷热数据分离”。 - 业务
按照业务将数据进⾏分类并拆分, 可以每个分类的数据拆分到⼀张表中。 - ID范围
从 0 到 100W ⼀个表, 100W+1 到 200W ⼀个表。 - HASH取模 离散化
取⽤户id, 然后hash取模, 分配到不同的数据库上。 这样可以同时向多个表中插⼊数据, 提⾼并发能⼒, 同时由于⽤户id进⾏了离散处理, 不会出现ID冲突的问题 - 地理区域
⽐如按照华东,华南,华北这样来区分业务, 部分云服务就是如此。
import random
from flask import Flask
from flask_sqlalchemy import SQLAlchemy, SignallingSession, get_state
from sqlalchemy import orm
app = Flask(__name__)
# 设置单个数据库URI (⽤于建表并添加测试数据)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:mysql@127.0.0.1:3306/db'
# 设置多个数据库的URI (⽤于数据操作)
app.config['SQLALCHEMY_BINDS'] = {
'db1': 'mysql://root:mysql@127.0.0.1:3306/db1',
'db2': 'mysql://root:mysql@127.0.0.1:3306/db2',
'master': 'mysql://root:mysql@127.0.0.1:3306/test',
'slave': 'mysql://root:mysql@127.0.0.1:3306/test'
}
#其他配置
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_ECHO'] = True
# 1. ⾃定义Session类, 继承SignallingSession, 并重写get_bind⽅法
class RoutingSession(SignallingSession):
def get_bind(self, mapper=None, clause=None):
"""每次数据库操作(增删改查及事务操作)都会调⽤该⽅法, 来获取对应的数据库引擎(访问的数据库)"""
state = get_state(self.app)
if self._bind: # 如果查询指定了访问的数据库, 则使⽤指定的数据库
print('查询数据库:', self._bind)
return state.db.get_engine(self.app, bind=self._bind)
elif mapper is not None: # 如果模型类已指定数据库, 使⽤指定的数据库
info = getattr(mapper.mapped_table, 'info', {})
bind_key = info.get('bind_key')
if bind_key is not None:
return state.db.get_engine(self.app, bind=bind_key)
if self._flushing: # 如果模型类未指定数据库, 判断是否为写操作
print('写操作')
return state.db.get_engine(self.app, bind='master')
else:
print('读操作')
return state.db.get_engine(self.app, bind='slave')
_bind = None # 定义类属性记录要访问的数据库
def using_bind(self, bind):
"""指定要访问的数据库"""
self._bind = bind
return self
# 2. ⾃定义SQLALchemy类, 重写create_session⽅法
class RoutingSQLAlchemy(SQLAlchemy):
def create_session(self, options):
return orm.sessionmaker(class_=RoutingSession, db=self, **options)
# 创建组件对象
db = RoutingSQLAlchemy(app)
# 构建模型类
class User(db.Model):
__tablename__ = 't_user'
id = db.Column(db.Integer, primary_key=True)
name = db.Column('username', db.String(20), unique=True)
age = db.Column(db.Integer, default=0, index=True)
@app.route('/')
def index():
for db_bind in ['db1', 'db2']: # 遍历各数据库节点, 查询⽤户数据
user = db.session().using_bind(db_bind).query(User).filter(User.name == 'zs').first()
print(user)
if user:
print(user.id, user.name, user.age)
return "index"
if __name__ == '__main__':
# 重置所有继承⾃db.Model的表
db.drop_all()
db.create_all()
# 添加测试数据 需要分别往db1和db2中添加⼀条数据
user1 = User(name='zs', age=20)
db.session.add(user1)
db.session.commit()
app.run(debug=True)
分布式问题
1,分布式事务问题
why:本地事物不支持跨库操作
解决方案1:
- 将有关联的表放在同一个数据库中
- 同一个库中可以操作一个事物
解决方案2:
Mysql从5.6开始⽀持分布式事务,核⼼是⼆阶段提交协议(简称 2PC协议 / XA协议),分布式事务会提供⼀个事务管理器来对各数据库的本地事务进⾏统⼀管理, 只有各本地事务都向管理器 预提交 成功后, 事务管理器才会统⼀执⾏提交处理, 否则统⼀进⾏回滚处理

# 创建组件对象 设置⼆阶段提交
db = SQLAlchemy(app, session_options={'twophase': True})
# 由于采⽤了分布式事务, 整个session的操作会被放⼊到⼀个分布式事务中, 并实现事务的原⼦性
db.session.commit()
解决方案3:
基于状态/消息的最终⼀致性⽅案

2,跨节点join/排序/分页
why:不⽀持的跨库操作包括join/分组/聚合/排序
解决方案1:
- 分两次查询进⾏, 在应⽤端合并
解决方案2:
- 花钱上第三方的分布式db,给您安排的明明白白的,比如阿里的PolarDB-X 云原生分布式数据库
注意点:
不要轻易分库分表, 因为分⽚会带来 诸多分布式问题, 让应⽤的复杂度⼤量增加
应避免"过度设计"和"过早优化", 先尽⼒去做其他优化, 例如: 升级硬件、 升级⽹络、 读写分离、索引优化、 缓存设计等等。
当数据量达到单表瓶颈时候(参考值: 单表记录1000W+/硬盘100G+), 再考虑分库分表
如果需要进⾏分库分表, 优先考虑垂直拆分
作者:在下YM
链接:https://www.jianshu.com/p/458f5d4f674c
来源:简书
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
posted on 2022-04-10 19:58 freeliver54 阅读(391) 评论(0) 编辑 收藏 举报
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步