MySQL分库分表与分区的入门指南

前言

关系型数据库比较容易成为系统瓶颈,单机存储容量、连接数、处理能力都有限,当数据量和并发量起来之后,就必须对数据库进行切分了。

数据切分(sharding)的手段就是分库分表。分库分表有两方面,可能是光分库不分表,也可能是光分表不分库。

数据库分布式的核心内容无非就是数据切分,以及切分后对数据的定位、整合。

为什么要分库分表

分表

单表数据量太大时,会严重影响sql执行的性能。一般单表到达几百万的时候,性能就会相对差一些了,这时就得分表了。

分表就是把一个表的数据放到多个表中,然后查询的时候就查一个表。比如按照项目id来分表:将固定数量的项目数据放在一个表中,这样就可以控制每个表的数据量在可控的范围内。

分库

根据经验来讲,一个库最多支持到并发2000时就需要扩容了,而且一个健康的单库并发值最好保持在1000左右。那么你可以将一个库的数据拆分到多个库中,访问的时候就访问一个库好了。

这就是所谓的分库分表,为啥要分库分表?

  • 提高并发支撑能力
  • 降低磁盘使用率
  • 提高SQL执行性能

如何分库分表

直接看图:

对于垂直拆分,建议最好在系统设计之初做好表设计,避免垂直分表。

水平拆分可以按照range来分,或是按照某个字段hash。按照range来分,好处在于扩容简单,准备好新的表或库就可以了。但是容易产生热点问题,实际使用时要结合业务场景来看。按照hash来分,好处在于可以平均分配每个库或表的请求压力,缺点是扩容麻烦,之前的数据要rehash,存在一个数据迁移的过程。

分库分表带来的问题

分库分表能有效地缓解单机和单库带来的网络IO、硬件资源、连接数的压力。但也带来了一些问题。

  • 事务一致性问题
    通过分布式事务或者保证最终一致性来解决。
  • 跨节点关联查询join问题
    全局表、字段冗余、数据组装、ER分片
  • 跨节点分页、排序、聚集函数问题
    首先在不同分片节点进行查询,最后要对结果进行汇总或归并
  • 全局主键避重问题
    各种分布式ID生成算法
  • 数据迁移、扩容问题
    如果是range分片,只需要添加节点就可以进行扩容了。
    如果是hash,一般做法是先读出历史数据,然后按指定的分片规则再将数据写入到各个分片节点中。

数据迁移

数据迁移介绍两种方案。

一个最low的方案,就是系统停机一段时间,用实现写好的导数据的工具跑一遍把单独单表的数据独出来,写到分库分表里面去。

第二个方案听起来就比较靠谱了,双写迁移方案。在线上系统里,之前所有写数据的地方,增删改操作,除了对旧库增删改,再加上对新库的增删改,这就是所谓的双写。然后系统部署之后,把方案一里的导数据工具跑起来,读老库写新库。写的时候要根据gmt_modified这类字段判断这条数据最后修改的时间,除非是读出来新库没有,或是比新库数据新才会写。简单来说就是不允许用老数据覆盖新数据。

写完一轮之后,有可能还是存在不一致,那么就程序自动新一轮校验,对比新老库每个表的每条数据,接着如果有不一样的,就针对那些不一样的,从老库读数据再次写。反复循环直到数据完全一致。

中间件

分库分表的中间件比较常见的有:

  • Cobar:阿里b2b团队开发和开源的,属于proxy层方案,介于应用服务器和数据库服务器之间。应用程序通过JDBC驱动访问Cobar集群,Cobar根据SQL和分库规则对SQL做分解,然后分发到MySQL集群不同的数据库实例上执行。不支持读写分离、存储过程、跨库join和分页等操作。最近几年都没更新了,也没啥人用了。
  • TDDL:淘宝团队开发的,属于client层方案。支持基本的crud语法和读写分离,但不支持join、多表查询等语法。目前只用也不多,因为还依赖淘宝的diamond配置管理系统。
  • Atlas:360开源的,属于proxy层方案。也是好几年没维护,现在用的公司基本也很少了。
  • Sharding-jdbc:当当开源的,属于client层方案,目前已更名为ShardingSphere。SQL语法支持的也比较多,没有太多限制,支持分库分表、读写分离、分布式id生成、柔性事务(最大努力送达型事务、TCC事务)。而且使用的公司比较多,社区活跃。
  • Mycat:基于Cobar改造,属于proxy层方案。支持的功能非常完善。相比Sharding-jdbc来说,年轻一些。

综上,现在可以考虑使用的就是Sharding-jdbc和Mycat。

Sharding-jdbc这种client层方案的有点在于不用部署,运维成本低,不需要代理层的二次转发,性能高。缺点是有耦合性。
Mycat这种proxy层方案的缺点在于需要部署,自己运维一套中间件,运维成本高,但是好处在于对项目是透明的。

MySQL分区(不建议使用)

这里介绍分区主要是防止和切分、分库分表等概念混淆。
MySQL从5.1版本开始支持分区(partition)的功能。分区指根据一定的规则,数据库把一个表分解成多个更小的、更容易管理的部分。就访问数据库的应用而言,逻辑上只有一个表或一个索引,但是实际上这个表可能由多个物理分区组成,即对应用是透明的。
MySQL分区引入了分区键的概念,采取分治法,有利于管理非常大的表。分区键用于根据某个区间值、特定值列表或HASH函数执行数据的聚集,让数据根据规则分布在不同的分区中。MySQL 5.7中可用的分区类型主要有以下6种:

  • RANGE分区:基于一个给定连续区间范围,把数据分配到不同的分区。
  • LIST分区:类似RANGE分区,区别在LIST分区是基于枚举出的值列表分区,RANGE是基于给定的连续区间范围分区。
  • COLUMNS分区:类似于RANGE和LIST,区别在于分区键既可以是多列,又可以是非整数。
  • HASH分区:基于给定的分区个数,把数据取模分配到不同的分区。
  • KEY分区:类似于HASH分区,但使用MySQL提供的哈希函数。
  • 子分区:也叫做复合分区或者组合分区,即在主分区下再做一层分区,将数据再次分割。

这里举一LIST分区的例子:

CREATE TABLE orders_list (
  id INT AUTO_INCREMENT,
  customer_surname VARCHAR(30),
  store_id INT,
  salesperson_id INT,
  order_date DATE,
  note VARCHAR(500),
  INDEX idx (id)
) ENGINE = INNODB
  PARTITION BY LIST(store_id) (
  PARTITION p1
  VALUES IN (1, 3, 4, 17)
  INDEX DIRECTORY = '/var/orders/district1'
  DATA DIRECTORY = '/var/orders/district1',
  PARTITION p2
  VALUES IN (2, 12, 14)
  INDEX DIRECTORY = '/var/orders/district2'
  DATA DIRECTORY = '/var/orders/district2',
  PARTITION p3
  VALUES IN (6, 8, 20)
  INDEX DIRECTORY = '/var/orders/district3'
  DATA DIRECTORY = '/var/orders/district3',
  PARTITION p4
  VALUES IN (5, 7, 9, 11, 16)
  INDEX DIRECTORY = '/var/orders/district4'
  DATA DIRECTORY = '/var/orders/district4',
  PARTITION p5
  VALUES IN (10, 13, 15, 18)
  INDEX DIRECTORY = '/var/orders/district5'
  DATA DIRECTORY = '/var/orders/district5'
);

分区的优点:

  • 扩大存储容量。
  • 优化查询。在WHERE子句中包含分区条件时可以只扫描必要的分区来提高查询效率;同事在涉及SUM()和COUNT()这类聚合函数的查询时,可以在每个分区上并行处理。
  • 对于已经过期或不需要保存的数据分区,可以通过删除分区来快速删除数据。
  • 跨多磁盘来分散查询数据,获得更大的查询吞吐量。

总结

到此这篇关于MySQL分库分表与分区的文章就介绍到这了,更多相关MySQL分库分表分区内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • MySql分表、分库、分片和分区知识点介绍

    一.前言 数据库的数据量达到一定程度之后,为避免带来系统性能上的瓶颈.需要进行数据的处理,采用的手段是分区.分片.分库.分表. 二.分片(类似分库) 分片是把数据库横向扩展(Scale Out)到多个物理节点上的一种有效的方式,其主要目的是为突破单节点数据库服务器的 I/O 能力限制,解决数据库扩展性问题.Shard这个词的意思是"碎片".如果将一个数据库当作一块大玻璃,将这块玻璃打碎,那么每一小块都称为数据库的碎片(DatabaseShard).将整个数据库打碎的过程就叫做分片,可以

  • MySql分表、分库、分片和分区知识深入详解

    一.前言 数据库的数据量达到一定程度之后,为避免带来系统性能上的瓶颈.需要进行数据的处理,采用的手段是分区.分片.分库.分表. 二.分片(类似分库) 分片是把数据库横向扩展(Scale Out)到多个物理节点上的一种有效的方式,其主要目的是为突破单节点数据库服务器的 I/O 能力限制,解决数据库扩展性问题.Shard这个词的意思是"碎片".如果将一个数据库当作一块大玻璃,将这块玻璃打碎,那么每一小块都称为数据库的碎片(DatabaseShard).将整个数据库打碎的过程就叫做分片,可以

  • MySQL分库分表与分区的入门指南

    前言 关系型数据库比较容易成为系统瓶颈,单机存储容量.连接数.处理能力都有限,当数据量和并发量起来之后,就必须对数据库进行切分了. 数据切分(sharding)的手段就是分库分表.分库分表有两方面,可能是光分库不分表,也可能是光分表不分库. 数据库分布式的核心内容无非就是数据切分,以及切分后对数据的定位.整合. 为什么要分库分表 分表 单表数据量太大时,会严重影响sql执行的性能.一般单表到达几百万的时候,性能就会相对差一些了,这时就得分表了. 分表就是把一个表的数据放到多个表中,然后查询的时候

  • 浅谈订单重构之 MySQL 分库分表实战篇

    目录 一.目标 二.环境准备 1.基本信息 2.数据库环境准备 3.建库 & 导入分表 三.配置&实践 1.pom文件 2.常量配置 3.yml 配置 4.分库分表策略 5.dao层编写 6.单元测试 四.总结 一.目标 本文将完成如下目标: 分表数量: 256    分库数量: 4 以用户ID(user_id) 为数据库分片Key 最后测试订单创建,更新,删除, 单订单号查询,根据user_id查询列表操作. 架构图: 表结构如下: CREATE TABLE `order_XXX` (

  • MySQL分库分表详情

    一.业务场景介绍 假设目前有一个电商系统使用的是MySQL,要设计大数据量存储.高并发.高性能可扩展的方案,数据库中有用户表.用户会非常多,并且要实现高扩展性,你会怎么去设计? OK咱们先看传统的分库分表方式 当然还有些小伙伴知道按照省份/地区或一定的业务关系进行数据库拆分 OK,问题来了,如何保证合理的让数据存储在不同的库不同的表里呢?让库减少并发压力?应该怎么去制定分库分表的规则?不用急,这不就来了 二.水平分库分表方法 1.RANGE 第一种方法们可以指定一个数据范围来进行分表,例如从1~

  • Mysql分库分表之后主键处理的几种方法

    目录 数据库自增 ID 设置数据库 sequence 或者表自增字段步长 UUID 系统当前时间戳+XXX Snowflake 算法 数据库自增 ID 搞一个数据库,什么也不干,就用于生成主键. 你的系统里每次得到一个 id,都需要往那个专门生成主键的数据库中通过插入获取一个自增的ID,拿到这个 id 之后再往对应的分库分表里去写入. 优点:方便简单. 缺点:单库生成自增 id,要是高并发的话,就会有瓶颈的:如果你硬是要改进一下,那么就专门开一个服务出来,这个服务每次就拿到当前 id 最大值,然

  • MySQL 分库分表的项目实践

    目录 一.为什么要分库分表 二.库表太大产生的问题 三.垂直拆分 1. 垂直分库 2. 垂直分表 四.水平分库分表 一.为什么要分库分表 数据库架构演变 刚开始多数项目用单机数据库就够了,随着服务器流量越来越大,面对的请求也越来越多,我们做了数据库读写分离, 使用多个从库副本(Slave)负责读,使用主库(Master)负责写,master和slave通过主从复制实现数据同步更新,保持数据一致.slave 从库可以水平扩展,所以更多的读请求不成问题 但是当用户量级上升,写请求越来越多,怎么保证数

  • MySQL分库分表的几种方式

    目录 一.为什么要分库分表 二.什么是分库分表 三.分库分表的几种方式 1.垂直拆分 2. 水平拆分 四.分库分表带来的问题 五.分库分表技术如何选型 一.为什么要分库分表 如果一个网站业务快速发展,那这个网站流量也会增加,数据的压力也会随之而来,比如电商系统来说双十一大促对订单数据压力很大,Tps十几万并发量,如果传统的架构(一主多从),主库容量肯定无法满足这么高的Tps,业务越来越大,单表数据超出了数据库支持的容量,持久化磁盘IO,传统的数据库性能瓶颈,产品经理业务·必须做,改变程序,数据库

  • MySQL分库分表后路由策略设计详情

    目录 概述 支持场景 路由策略 用户端路由key 商家路由key 概述 分库分表后设计到的第一个问题就是,如何选择路由key,应该如何对key进行路由.路由key应该在每个表中都存在而且唯一.路由策略应尽量保证数据能均匀进行分布. 如果是对大数据量进行归档类的业务可以选择时间作为路由key.比如按数据的创建时间作为路由key,每个月或者每个季度创建一个表.按时间作为分库分表后的路由策略可以做到数据归档,历史数据访问流量较小,流量都会打到最新的数据库表中. 也可以设计其与业务相关的路由key.这样

  • MySQL分库分表总结讲解

    项目开发中,我们的数据库数据越来越大,随之而来的是单个表中数据太多.以至于查询变慢,而且由于表的锁机制导致应用操作也受到严重影响,出现了数据库性能瓶颈. 当出现这种情况时,我们可以考虑分库分表,即将单个数据库或表进行拆分,拆分成多个库和多个数据表,然后用户访问的时候,根据一定的算法与逻辑,让用户访问不同的库.不同的表,这样数据分散到多个数据表中,减少了单个数据表的访问压力.提升了数据库访问性能. 下面是对项目中分库分表的一些总结: 单库单表 单库单表是最常见的数据库设计,例如,有一张用户(use

  • Mysql数据库分库分表全面瓦解

    目录 1 为什么要分库分表 2 垂直拆分(Scale Up 纵向扩展) 2.1 垂直分库 2.2 垂直分表 3 水平拆分(Scale Out 横向扩展) 3.1 库内分表 3.2 库内分表的实现策略 3.2.1 HASH(哈希) 3.2.2 RANGE(范围) 3.2.3 LIST(预定义列表) 3.2.4 KEY(键值) 3.2.5 Composite(复合模式) 3.3 分库分表 4 分库分表存在的问题 4.1 事务问题 4.2 跨库跨表的join问题 4.3 额外的数据管理负担和数据运算压

  • MySQL常用分库分表方案汇总

    目录 一.数据库瓶颈 二.分库分表 2.水平分表 3.垂直分库 4.垂直分表 三.分库分表工具 四.分库分表步骤 五.分库分表问题 1.非partition key的查询问题 2.非partition key跨库跨表分页查询问题 3.扩容问题 六.分库分表总结 一.数据库瓶颈 不管是IO瓶颈,还是CPU瓶颈,最终都会导致数据库的活跃连接数增加,进而逼近甚至达到数据库可承载活跃连接数的阈值.在业务Service来看就是,可用数据库连接少甚至无连接可用.接下来就可以想象了吧(并发量.吞吐量.崩溃).

随机推荐