【MySQL】之分区、分库、分表

痛定思痛。 2022-12-07 12:29 233阅读 0赞

# 一、Mysql 分区 #

--------------------

### 1、什么是 Mysql 分区（PARTITION） ###

如果一张表的数据量太大的话，那么 myd，myi 就会变的很大，查找数据就会变的很慢，这个时候我们可以利用 mysql 的分区功能，在物理上将这一张表对应的三个文件，分割成许多个小块，这样呢，我们查找一条数据时，就不用全部查找了，只要知道这条数据在哪一块，然后在那一块找就行了。如果表的数据太大，可能一个磁盘放不下，这个时候，我们可以把数据分配到不同的磁盘里面去。

### 2、分区的作用 ###

*  逻辑数据分割
 *  提高单一的写和读应用速度
 *  提高分区范围读查询的速度
 *  分割数据能够有多个不同的物理文件路径
 *  高效的保存历史数据

### 3、分区类型及操作 ###

首先查看当前数据库是否支持分区：

mysql> SHOW VARIABLES LIKE '%partition%';
    mysql> show plugins;

#### RANGE 分区 ####

MySQL 将会根据指定的拆分策略，把数据放在不同的表文件上，相当于在文件上，被拆成了小块。但是，对外给客户的感觉还是一张表，透明的。

分区操作：

CREATE TABLE tbl_new(
      id INT NOT NULL PRIMARY KEY,
      title VARCHAR(20) NOT NULL DEFAULT  ''
    )ENGINE MYISAM CHARSET utf8
    PARTITION BY RANGE(id)(
      PARTITION t0 VALUES LESS THAN(10),
      PARTITION t1 VALUES LESS THAN(20),
      PARTITION t2 VALUES LESS THAN(MAXVALUE)
    );
    
    INSERT INTO tbl_new VALUES(1,'z3');
    INSERT INTO tbl_new VALUES(2,'z4');
    INSERT INTO tbl_new VALUES(3,'z5');
    INSERT INTO tbl_new VALUES(4,'z6');

这时查看数据库文件可以看到多出了几个带 \# 的文件：

ls /var/lib/mysql/dbtest
    
    tbl_new.frm
    tbl_new.par
    tbl_new#P#t0.MYD
    tbl_new#P#t0.MYI
    tbl_new#P#t1.MYD
    tbl_new#P#t1.MYI
    tbl_new#P#t2.MYD
    tbl_new#P#t2.MYI

#### List 分区 ####

MySQL 中的 LIST 分区在很多方面类似于 RANGE 分区。和按照 RANGE 分区一样，每个分区必须明确定义。

它们的主要区别在于：

*  LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值
 *  RANGE分区是从属于一个连续区间值的集合。

分区操作：

create table user (
      uid int not null,
      userName varchar(20),
      area_id int
    )engine myisam charset utf8
    partition by list(area_id) (
      partition bj values in (1),
      partition sh values in (2),
      partition gz values in (3),
      partition sz values in (4)
    );
    
    insert into user(uid,userName,area_id) values(1,'z3',1);
    insert into user(uid,userName,area_id) values(2,'z4',2);
    insert into user(uid,userName,area_id) values(3,'z5',3);

这时查看数据库文件可以看到多出了几个带 \# 的文件：

ls /var/lib/mysql/dbtest
    
    user.frm
    user.par
    user#P#bj.MYD
    user#P#bj.MYI
    user#P#sh.MYD
    user#P#sh.MYI
    user#P#gz.MYD
    user#P#gz.MYI
    user#P#sz.MYD
    user#P#sz.MYI

#### 其它分区 ####

*  Hash分区
 *  Key分区
 *  子分区

**注意**：MySQL中的分区在禁止空值NULL上没有进行处理，无论它是一个列值还是一个用户定义表达式的值，一般而言，在这种情况下MySQL把NULL当做零。如果你不希望出现类似情况，建议在设计表时声明该列"NOT NULL"。

# 二、Mysql 分库 #

--------------------

### 1、什么是 Mysql 分库 ###

一个库里表太多了，导致了海量数据，系统性能下降，把原本存储于一个库的表拆分存储到多个库上，通常是将表按照功能模块、关系密切程度划分出来，部署到不同库上。

比如，我们会建立定义数据库 workDB、商品数据库 payDB、用户数据库 userDB、日志数据库 logDB 等，分别用于存储项目数据定义表、商品定义表、用户数据表、日志数据表等。

### 2、为什么要分库 ###

数据库集群环境后都是多台 slave，基本满足了读取操作，但是写入或者说大数据、频繁的写入操作对 master 性能影响就比较大， 这个时候，单库并不能解决大规模并发写入的问题。

### 3、分库的优点 ###

*  减少增量数据写入时的锁对查询的影响
 *  由于单表数量下降，常见的查询操作由于减少了需要扫描的记录，使得单表单次查询所需的检索行数变少，减少了磁盘IO，时延变短

但是分库无法解决单表数据量太大的问题。

# 三、Mysql 分表 #

--------------------

### 1、Mysql 分表的方式 ###

**1）、垂直拆分**

通常是按照业务功能的使用频次，把主要的、热门的字段放在一起做为主要表；然后把不常用的，按照各自的业务属性进行聚集，拆分到不同的次要表中；主要表和次要表的关系一般都是一对一的。

**2）、水平拆分**（数据分片）

把该表的数据按照某种规则，例如userID散列，进行划分，然后存储到多个结构相同的表，和不同的库上（*MySQL 单表的容量不超过500W，否则建议水平拆分*）。

例如，我们的userDB中的用户数据表中，每一个表的数据量都很大，就可以把userDB切分为结构相同的多个userDB：part0DB、part1DB等，再将userDB上的用户数据表userTable，切分为很多userTable：userTable0、userTable1等，然后将这些表按照一定的规则存储到多个userDB上

### 2、分表的开源方案 ###

#### MySQL Fabric ####

网址：[http://www.mysql.com/products/enterprise/fabric.html][http_www.mysql.com_products_enterprise_fabric.html]

MySQL Fabric 是一个用于管理 MySQL 服务器群的可扩展框架。该框架实现了两个特性：高可用性 (HA) 以及使用数据分片的横向扩展。

官方推荐，但是2014年左右才推出，是真正的分表，不是代理的(不同于mysql-proxy)。

未来很有前景，目前属于测试阶段还没大规模运用于生产,期待它的升级。

#### 1）、Atlas ####

Atlas是由 Qihoo 360，Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。

它在MySQL官方推出的MySQL-Proxy 0.8.2版本的基础上，修复了大量bug，添加了很多功能特性。目前该项目在360公司内部得到了广泛应用，很多MySQL业务已经接入了Atlas平台，每天承载的读写请求数达几十亿条。

主要功能：

*  读写分离
 *  从库负载均衡
 *  IP过滤
 *  SQL语句黑白名单
 *  自动分表，只支持单库多表，不支持分布式分表，同理，该功能我们可以用分库来代替，多库多表搞不定。

网址： [https://github.com/Qihoo360/Atlas][https_github.com_Qihoo360_Atlas]

#### 2）、MySQL proxy ####

官网提供的，小巧精干型的，但是能力有限，对于大数据量的分库分表无能为力，适合中小型的互联网应用，基本上 mysql-proxy - master/slave 就可以构成一个简单版的读写分离和负载均衡。

# 总结 #

--------------------

### 1、分库分表演变过程 ###

单库多表 >> 读写分离主从复制 >> 垂直分库，每个库又可以带着 salve >> 继续垂直分库，极端情况单库单表 >> 分区(变相的水平拆分表，只不过是单库的) >> 水平分表后再放入多个数据库里，进行分布式部署

*  单库多表
 *  读写分离主从复制
 *  垂直分库（每个库又可以带salve）
 *  继续垂直分库，理论上可以到极端情况，单库单表
 *  分区（partition是变相的水平拆分，只不过是单库内进行）
 *  终于到水平分表，后续放入多个数据库里，进行分布式部署，终极method。
 *  但是理论上OK，实际上中间的各种通信、调度、维护和编码要求，更加高

### 2、分库分表后的难题 ###

*  分布式事务的问题，数据的完整性和一致性问题。
 *  数据操作维度问题：用户、交易、订单各个不同的维度，用户查询维度、产品数据分析维度的不同对比分析角度。
 *  跨库联合查询的问题，可能需要两次查询
 *  跨节点的count、order by、group by以及聚合函数问题，可能需要分别在各个节点上得到结果后在应用程序端进行合并
 *  额外的数据管理负担，如：访问数据表的导航定位
 *  额外的数据运算压力，如：需要在多个节点执行，然后再合并计算
 *  程序编码开发难度提升，没有太好的框架解决，更多依赖业务看如何分，如何合，是个难题。

**提示**：不到最后一步，轻易不用进行水平分表！

[http_www.mysql.com_products_enterprise_fabric.html]: http://www.mysql.com/products/enterprise/fabric.html
[https_github.com_Qihoo360_Atlas]: https://github.com/Qihoo360/Atlas