[转]mysql分布式-分片篇

本文转自：https://www.jianshu.com/p/458f5d4f674c

需求分析

1，?户请求量太?, 会导致web应??法及时响应 -> 分布式服务器(分散请求到多个服务器上)

2，单表太?, 会导致CRUD都成问题, 索引膨胀, 查询超时 -> 拆分表

3，单库太?, 会导致单库磁盘空间不?; 处理能?有限; 出现IO瓶颈 -> 拆分库

作用

分?也称为数据拆分 (Shareding), 其主要?作就是对单库单表进?拆分, 多库多表共同组成完整的数据集合，分?可以提?吞吐量, 同?时间数据的读写完成量更多, 扩充单机存储的容量/读写速度上限！

分类

垂直拆分

垂直拆分分为两种：

1，垂直分表

• 按字段将一张表拆分成多张表

• 对于字段较多的表, 每条记录占?的空间也会较多, 导致每次从硬盘中读取的记录以及查询缓存可
  缓存的记录数量较少, 影响查询查询效率

• 针对字段多的表就可以采?垂直分表来进?拆分, 这样可以减少表体积, 提?查询效率

拆分规则

• 相关性
  -- 可以将字段根据业务逻辑和使?的相关性 进?分表划分
  -- 如: ?户名和密码经常配合使?, 将其分到?户认证表, ??和邮箱等个?信息经常?起访问,将其分到?户信息表

• 使用频率
  -- 可以将字段根据常?和不常?进?划分, 并进?分表处理
  -- 如: 原始?户表中包含了多个字段, 其中有常?的昵称、 ?机号等字段, 也包含不常?的邮箱、
  ?? 等字段, 可以根据使?频率将其分为两张表: ?户基础信息表 和 ?户其他信息表

  
    例子

2，垂直分库

• 将?个数据库中的多张表拆分到多个数据库(服务器节点)中
  **由于本地事务不?持跨库操作, 所以应该将 有相关联性的表放在同?个库中

分库访问：flask-sqlalchemy 通过配置 SQLALCHEMY_BINDS 允许设置多个数据库URI, 并且每个模型类可以 bind_key 属性 设置??对应访问的数据库

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:

• 基于状态/消息的最终?致性?案

  
    BASE

2，跨节点join/排序/分页

why：不?持的跨库操作包括join/分组/聚合/排序
解决方案1:

• 分两次查询进?, 在应?端合并

解决方案2:

• 花钱上第三方的分布式db，给您安排的明明白白的，比如阿里的PolarDB-X 云原生分布式数据库

注意点：

不要轻易分库分表， 因为分?会带来 诸多分布式问题, 让应?的复杂度?量增加

应避免"过度设计"和"过早优化", 先尽?去做其他优化， 例如： 升级硬件、 升级?络、 读写分离、索引优化、 缓存设计等等。

当数据量达到单表瓶颈时候(参考值: 单表记录1000W+/硬盘100G+)， 再考虑分库分表

如果需要进?分库分表, 优先考虑垂直拆分

作者：在下YM
链接：https://www.jianshu.com/p/458f5d4f674c
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。