[转]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