Mysql学习笔记 - 分片和分区

2020-03-23

全文 ≈ 1.9k字 | 预计阅读时长 8分钟

之前在复习Mysql主从知识的同时，小伙伴考了我一个问题，分片和分区有什么差别 … 乍一听可能只是名词上的不同，但其实它们俩的确不是同一个东西。

1.什么是分片（Sharding）？

我们举个例子，一张文章表。
结构如下：

CREATE TABLE `mark`.`article`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `type` varchar(20) NOT NULL COMMENT '类型',
  `title` varchar(255) NOT NULL COMMENT '标题',
  `content` longtext NOT NULL COMMENT '内容',
  `author` varchar(20) NOT NULL COMMENT '作者',
  `date` datetime NOT NULL COMMENT '日期',
  PRIMARY KEY (`id`)
);

需求如下：

用户打开app会直接展示文章列表（类型，标题，作者，日期）
点击文章查看详情

那么这张表在数据量比较小的初期应对访问是没什么问题的。但是随着数据量日益膨胀，查询效率会越来越低，因为里面content字段非常巨大。

这时候要怎么优化呢？把content字段拆出去，因为访问最多的请求并不需要content，怎么拆呢？结构如下：

# 文章表
CREATE TABLE `mark`.`article`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `type` varchar(20) NOT NULL COMMENT '类型',
  `title` varchar(255) NOT NULL COMMENT '标题',
  `c_id` int NOT NULL COMMENT '文章ID',
  `author` varchar(20) NOT NULL COMMENT '作者',
  `created_at` datetime NOT NULL COMMENT '日期',
  PRIMARY KEY (`id`)
);
# 文章内容表
CREATE TABLE `mark`.`article_content`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `content` longtext NOT NULL COMMENT '内容',
  `created_at` datetime NOT NULL COMMENT '日期',
  PRIMARY KEY (`id`)
);

2.垂直（纵向）切分

上述这种情况就是分片的一种，垂直切分也被称为纵向切分，这种分片的方式不仅可以跨表也可以跨库。

优点

拆分规则简单。
数据模块清晰。
容易维护，容易定位问题。

缺点

如果分片表跨库，那么在SQL层无法进行连表查询，只能在程序层处理。
事务处理复杂度变高。
后期表结构的扩展性受限。

3.水平（横向）切分

说到了垂直切分，那就不得不提一下水平切分也被称之为横向切分。
同样举个例子，一张用户日志表。
结构如下：

CREATE TABLE `mark`.`user_log`  (
  `u_id` int NOT NULL COMMENT '用户ID',
  `content` longtext NOT NULL COMMENT '日志内容',
  `created_at` datetime NOT NULL COMMENT '创建日期'
);

需求如下：

根据用户ID快速检索日志信息

情况一样，在数据量比较小的时候功能响应速度应该还可以，但是随着数据量增长的比较厉害。查询效率会呈断崖式下降。

那么这时候要怎么优化呢？我们按照用户ID去拆分表，即每个用户存储的日志固定在一张表。结构如下：

CREATE TABLE `mark`.`user_log_{NUM}`  (
  `u_id` int NOT NULL COMMENT '用户ID',
  `content` longtext NOT NULL COMMENT '日志内容',
  `created_at` datetime NOT NULL COMMENT '创建日期'
);

为了省事，后面{NUM}是个数字哈，每个用户进来我们会利用摘要算法（比如：crc32）取个固定数值，然后固定把对应的数据存储到相应的表内。

优点

不会出现跨库无法连表查询的情况。
事务处理相对简单。
很难出现扩展性受限的问题。

缺点

数据分布不平均，可能一张表10W行，另外一张100W行。
难维护，定位问题需要前置算法查询。
后期数据迁移比较麻烦。

4.什么是分区（Partition）？

分片简单说了说，那么接下来要学习一下分区。分区和分片比较明显的一点是：分片多是利用程序配合来实现，分区则是数据库（不仅仅是Mysql，其它数据库也有）提供的机制。

首先，分区分为4种模式：

Range
List
Hash
Key

5.Range

Range（范围）大概的思想是将数据进行分段，比如按照日期将内购表进行拆分，分为不同的年份。

CREATE TABLE purchase (id INT, app_id INT, name VARCHAR(50), money NUMERIC(20,4),currency VARCHAR(10),created_at DATE)
PARTITION BY RANGE( YEAR(created_at) ) (
    PARTITION p0 VALUES LESS THAN (1990),
    PARTITION p1 VALUES LESS THAN (1995),
    PARTITION p2 VALUES LESS THAN (2000),
    PARTITION p3 VALUES LESS THAN (2005),
    PARTITION p4 VALUES LESS THAN (2010),
    PARTITION p5 VALUES LESS THAN (2015)
);
# 也可以按照金额来进行分区。
# PARTITION BY RANGE( money ) ();

写入以下数据

INSERT INTO purchase(app_id,name,money,currency,created_at) VALUES
(600001,'desk organiser',6.0000,'CNY','2003-10-15'),
(600001,'alarm clock',12.0000,'CNY','1997-11-05'),
(600002,'chair',30.0000,'CNY','2009-03-10'),
(600002,'bookcase',68.0000,'CNY','1989-01-10'),
(600002,'exercise bike',128.0000,'CNY','2014-05-09'),
(600003,'sofa',258.0000,'CNY','1987-06-05'),
(600003,'espresso maker',648.0000,'CNY','2011-11-22'),
(600004,'aquarium',99.0000,'USD','1992-08-04'),
(600005,'study desk',129.0000,'USD','2006-09-16'),
(600006,'lava lamp',299.0000,'USD','1998-12-25');

然后我们观察一下，可能通过肉眼看没什么变化，执行这条查看分区状态的SQL

SELECT
	partition_name part,
	partition_expression expr,
	table_rows 
FROM
	information_schema.PARTITIONS 
WHERE
	table_schema = SCHEMA () 
	AND table_name = '{TABLE}';
# 结果如下
part    |   expr              |   descr |   table_rows
p0      |   YEAR(created_at)  |   1990  |   2
p1      |   YEAR(created_at)  |   1995  |   1
p2      |   YEAR(created_at)  |   2000  |   2
p3      |   YEAR(created_at)  |   2005  |   1
p4      |   YEAR(created_at)  |   2010  |   2
p5      |   YEAR(created_at)  |   2015  |   2

很明显，数据分别按照规则写进了分区表p0 ~ p5。

6.List

List（列表）模式，多用于对于指定字段的数值进行明确的数据拆分。

CREATE TABLE purchase (id INT, app_id INT, name VARCHAR(50), money NUMERIC(20,4),currency VARCHAR(10),created_at DATE)
PARTITION BY LIST( app_id ) (
    PARTITION p0 VALUES IN (600001),
    PARTITION p1 VALUES IN (600002),
    PARTITION p2 VALUES IN (600003),
    PARTITION p3 VALUES IN (600004),
    PARTITION p4 VALUES IN (600005),
    PARTITION p5 VALUES IN (600006)
);

写入同样的数据，之后执行查看分区状态的SQL。

part    |   expr    |   descr   |   table_rows
p0      |   app_id  |   600001  |   2
p1      |   app_id  |   600002  |   3
p2      |   app_id  |   600003  |   2
p3      |   app_id  |   600004  |   1
p4      |   app_id  |   600005  |   1
p5      |   app_id  |   600006  |   1

7.Hash

Hash（哈希）模式通过对一个或多个字段进行Hash计算，通过这个Hash值来进行分区（是不是感觉很像分片，其实分片的思想就是根据分区衍生而来）。

1
2
3

CREATE TABLE purchase (id INT, app_id INT, name VARCHAR(50), money NUMERIC(20,4),currency VARCHAR(10),created_at DATE)
PARTITION BY HASH( YEAR(created_at) )
PARTITIONS 4; # 为了区别，设4个分区。

然后写入同样的数据，之后执行查看分区状态的SQL。

part|   expr              |  descr |   table_rows
p0  |   YEAR(created_at)  |  NULL  |   1
p1  |   YEAR(created_at)  |  NULL  |   3
p2  |   YEAR(created_at)  |  NULL  |   3
p3  |   YEAR(created_at)  |  NULL  |   3

8.Key

Key（键）模式和Hash模式极其相似，可能有一点区别就是，Hash模式下是用户自定义规则进行Hash计算，而Key模式是Mysql使用自己的函数进行Hash计算。

1
2
3

CREATE TABLE purchase (id INT, app_id INT, name VARCHAR(50), money NUMERIC(20,4),currency VARCHAR(10),created_at DATE)
PARTITION BY KEY( created_at )
PARTITIONS 3; # 为了区别，设3个分区。

重复步骤，写入同样的数据，之后执行查看分区状态的SQL。

part|   expr          |  descr |   table_rows
p0  |   `created_at`  |  NULL  |   2
p1  |   `created_at`  |  NULL  |   3
p2  |   `created_at`  |  NULL  |   5

Key模式和Hash模式在expr上面有了直观的不同表现，Hash模式是：YEAR(created_at)，而Key模式是：created_at。这就是我们前面提到的Hash计算方式的区别。

9.最后

其实Mysql分区还有第5种模式，叫做Composite（复合）模式，比如：Range - Key

CREATE TABLE purchase (id INT, app_id INT, name VARCHAR(50), money NUMERIC(20,4),currency VARCHAR(10),created_at DATE)
PARTITION BY RANGE( YEAR( created_at ) ) 
SUBPARTITION BY KEY( created_at )
SUBPARTITIONS 3
(
    PARTITION p0 VALUES LESS THAN (1990),
    PARTITION p1 VALUES LESS THAN (1995),
    PARTITION p2 VALUES LESS THAN (2000),
    PARTITION p3 VALUES LESS THAN (2005),
    PARTITION p4 VALUES LESS THAN (2010),
    PARTITION p5 VALUES LESS THAN (2015)
);

这里就不展开Composite（复合）模式的学习了，最后通过以上的例子，可以看出分区也是有垂直分区和水平分区的说法的。

https://dev.mysql.com/doc/mysql-partitioning-excerpt/5.7/en/partitioning-management.html