什么是MySQL分区

什么是MySQL分区

MySQL分区是一种将表分割为更小的、更可管理的部分的技术。分区可以根据指定的条件拆分本来要占用大量I / O和CPU资源的表。例如,如果您有一个数十亿行的大表,您可以将其分割成几个分区,从而使在查询时只访问必要的分区,而不是搜查整个表,从而大大提高了性能。这使得MySQL将数据逻辑地拆分并轻松管理数据。

阅读更多:MySQL 教程

水平分区

水平分区是表格的分割方法之一,即将一个表格的行按照关键字划分到多个物理表格中。它们通常在不同的物理节点上存储,意味着该表的所有行将分布在一系列物理表格中。水平分区通常是分裂垂直分区的最终结果。它采取基于分区键值的所有查询,将它们发送到有关联的表.

例如:

CREATE TABLE t1 (
  id INT NOT NULL AUTO_INCREMENT,
  my_key INT NOT NULL,
  other_data VARCHAR(30),
  PRIMARY KEY (id, my_key)
)
PARTITION BY HASH(my_key) 
PARTITIONS 4;
SQL

上面的例子中,我们创建了一个名为t1的表,该表含有一个自增列id和一个my_key的列。PARTITION BY HASH(my_key)意味着使用my_key作为分区键值,PARTITIONS 4则将表分为了四个分区,每个分区中的行基于my_key哈希分配。

查询例如:

SELECT * FROM t1 WHERE my_key=1;
SQL

在此查询中,my_key的值为1,因此可以将查询发送到my_key的哈希分区上执行。

垂直分区

垂直分区是表格的分割方法之一,即将一个表格的列按照功能划分到多个物理表格中。这种分割可以为具有大量列或经常使用的列“轻松”带来节省空间和缩短查询时间的好处。在现实世界中,垂直分区通常可以通过基于访问频率或某些列的突出特征将“热”和“冷”数据拆分为不同的物理表格。

例如,在一个具有hundreds列的表中,70%的查询可能只涉及10%的列,我们不需要在我们的查询中涉及所有的列。使用垂直分区可以使查询更快、更有效。

下面是一个基于t1表,我们将my_key列和other_data列分别垂直分区。

CREATE TABLE t1a (
  id INT NOT NULL AUTO_INCREMENT,
  my_key INT NOT NULL,
  PRIMARY KEY (id, my_key)
);
CREATE TABLE t1b (
  id INT NOT NULL,
  other_data VARCHAR(30),
  PRIMARY KEY (id)
);
SQL

在上述例子中,我们将原来的t1表按功能分为了两个表t1at1bt1a包含了idmy_key列,而t1b包含了idother_data列。

当我们查询t1表时,我们需要在t1at1b两个表上各自查询。

在以上的两个MySQL一般分区技术中,我们用到了Hash PartitioningVertical Partitioning技术。现在,您可以将任何一个大的MySQL表分割成更小的,分片的部分,并将这些部分存储在 MySQL 的不同服务器上。

如何将MySQL分区到多个服务器?

在MySQL中实现将表分区到多个服务器的方法有几种,我们分别介绍如下:

1. MySQL分区复制

MySQL分区复制是分区到多个服务器的一种简单方法。它涉及将整个表复制到每个服务器,然后在每个服务器上的本地表上分区,因此每个表上的所有数据都相同。为了确保对单个分区键执行的查询只涉及到正确的表格副本,必须对每个服务器上的分区进行唯一分区键的验证。

例如:

CREATE TABLE t1 (
  id INT NOT NULL AUTO_INCREMENT,
  my_key INT NOT NULL,
  other_data VARCHAR(30),
  PRIMARY KEY (id, my_key)
) ENGINE=InnoDB;

-- 复制表到服务器1
CREATE TABLE t1_d1 (
  LIKE t1
) ENGINE=InnoDB;
INSERT INTO t1_d1 SELECT * FROM t1;

-- 在服务器1的本地表上分区
ALTER TABLE t1_d1
PARTITION BY HASH(my_key)
PARTITIONS 4;

-- 复制表到服务器2
CREATE TABLE t1_d2 (
  LIKE t1
) ENGINE=InnoDB;
INSERT INTO t1_d2 SELECT * FROM t1;

-- 在服务器2的本地表上分区
ALTER TABLE t1_d2
PARTITION BY HASH(my_key)
PARTITIONS 4;

-- 确保每个分区键在所有表格中唯一
ALTER TABLE t1_d1 ADD UNIQUE KEY (my_key);
ALTER TABLE t1_d2 ADD UNIQUE KEY (my_key);
SQL

在这个例子中,我们使用LIKE来复制t1表到两个数据库。每个数据库之后各自进行哈希分区。每个表上都有唯一的分区键,以确保对正确表格的访问。

2. MySQL引用分区

MySQL引用分区是一种将单个物理表分区到多个服务器的方法。它涉及将一个表作为参考表在每个服务器上创建。随后,每个表都被分区,并在本地表上存储数据。

例如:

CREATE TABLE t1 (
  id INT NOT NULL AUTO_INCREMENT,
  my_key INT NOT NULL,
  other_data VARCHAR(30),
  PRIMARY KEY (id, my_key)
) ENGINE=InnoDB;

-- 引用表 t1_d 到服务器1
CREATE TABLE t1_d1 (
  LIKE t1
) ENGINE=FEDERATED 
CONNECTION='mysql://user:password@server1/db/t1_d';

-- 在服务器1的本地表上分区
ALTER TABLE t1_d1
PARTITION BY HASH(my_key)
PARTITIONS 4;

-- 引用表 t1_d 到服务器2
CREATE TABLE t1_d2 (
  LIKE t1
) ENGINE=FEDERATED 
CONNECTION='mysql://user:password@server2/db/t1_d';

-- 在服务器2的本地表上分区
ALTER TABLE t1_d2
PARTITION BY HASH(my_key)
PARTITIONS 4;

-- 确保每个分区键在所有表格中唯一
ALTER TABLE t1_d1 ADD UNIQUE KEY (my_key);
ALTER TABLE t1_d2 ADD UNIQUE KEY (my_key);
SQL

在这个例子中,我们创建了一个名为t1的表,并在每个服务器上用引用表t1_d作为参考。每个表在其本地表上进行哈希分区,并添加唯一的分区键以确保对正确表格的访问。

3. MySQL Sharding

MySQL Sharding是一种用于处理大型数据集的技术,它涉及将数据拆分为更小的片段,并将这些片段存储在不同的服务器上。

例如:

-- 前两个分区保存数据上限为100的数据
CREATE TABLE t1_100 (...)
PARTITION BY RANGE COLUMNS(id) (
  PARTITION p0 VALUES LESS THAN (100),
  PARTITION p1 VALUES LESS THAN (200)
);

-- 接下来三个分区保存数据上限为1,000的数据
CREATE TABLE t1_1000 (...)
PARTITION BY RANGE COLUMNS(id) (
  PARTITION p2 VALUES LESS THAN (1000),
  PARTITION p3 VALUES LESS THAN (2000),
  PARTITION p4 VALUES LESS THAN (3000)
);

-- 最后五个分区保存数据上限为10,000的数据
CREATE TABLE t1_10000 (...)
PARTITION BY RANGE COLUMNS(id) (
  PARTITION p5 VALUES LESS THAN (10000),
  PARTITION p6 VALUES LESS THAN (20000),
  PARTITION p7 VALUES LESS THAN (30000),
  PARTITION p8 VALUES LESS THAN (40000),
  PARTITION p9 VALUES LESS THAN (MAXVALUE)
);
SQL

在上面的例子中,我们将t1表分成了三个表t1_100t1_1000t1_10000,每个表都被按照ID分为多个分区。每个分区都保存了一个数据范围,并被存储在不同的服务器上。

您可以使用MySQL Sharding来水平切分数据,这样您就可以将每个分区分配到特定的物理服务器上,而无需复制您的整个数据集。

总结

MySQL的分区技术是一个非常有用的特性,它可以帮助我们扩展我们的数据库,并改善性能。这篇文章介绍了MySQL中的水平分区和垂直分区,以及如何将MySQL分区到多个服务器中,包括分区复制、引用分区和Sharding。通过使用这些分区技术,您可以轻松地将大型MySQL表分割成可管理的部分,并将这些部分存储在您的 MySQL 服务器上。

Python教程

Java教程

Web教程

数据库教程

图形图像教程

大数据教程

开发工具教程

计算机教程

登录

注册