MySQL - 垂直分区

你好，有抱负的数据库爱好者们！今天，我们将深入探讨MySQL垂直分区的迷人世界。作为你友好的计算机老师邻居，我很兴奋能引导你开启这段旅程，即使你完全是个编程新手。别担心；我们会一步步来，在你意识到之前，你就会像一个专业人士一样分区数据！

MySQL 垂直分区

想象你有一个装满玩具的大箱子（这就是我们的数据库），你想更好地组织它们。垂直分区就像根据它们的特性把玩具分到不同的更小的箱子里。在数据库术语中，它是将表列分成单独表的过程。

让我们从一个简单的例子开始：

CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
email VARCHAR(100),
phone VARCHAR(20),
address TEXT
);

这是我们原始的students表。现在，让我们应用垂直分区：

CREATE TABLE students_main (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT
);

CREATE TABLE students_contact (
id INT PRIMARY KEY,
email VARCHAR(100),
phone VARCHAR(20)
);

CREATE TABLE students_address (
id INT PRIMARY KEY,
address TEXT
);

在这里，我们将原始表分成了三个更小的表。这可以提高性能，尤其是当你经常只访问某些列时。

为什么使用垂直分区？

1. 提高查询性能
2. 更好的数据管理
3. 增强安全性（你可以限制对敏感数据的访问）

现在，让我们看看如何使用这种新结构插入和检索数据：

-- 插入数据
INSERT INTO students_main VALUES (1, 'Alice', 20);
INSERT INTO students_contact VALUES (1, '[email protected]', '123-456-7890');
INSERT INTO students_address VALUES (1, '123 Main St, Anytown, USA');

-- 检索数据
SELECT m.id, m.name, c.email, a.address
FROM students_main m
JOIN students_contact c ON m.id = c.id
JOIN students_address a ON m.id = a.id
WHERE m.id = 1;

这样，你仍然可以获取关于一个学生的所有信息，但是存储得更高效。

范围列分区

现在，让我们升级一下，来谈谈范围列分区。这就像根据它们出版的年份来组织你的书籍，但是针对多个列。

以下是一个使用sales表的例子：

CREATE TABLE sales (
id INT NOT NULL,
sale_date DATE NOT NULL,
amount DECIMAL(10,2) NOT NULL
)
PARTITION BY RANGE COLUMNS(sale_date, amount) (
PARTITION p0 VALUES LESS THAN ('2023-01-01', 1000),
PARTITION p1 VALUES LESS THAN ('2023-01-01', 5000),
PARTITION p2 VALUES LESS THAN ('2023-07-01', 1000),
PARTITION p3 VALUES LESS THAN ('2023-07-01', 5000),
PARTITION p4 VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

在这个例子中，我们根据sale_date和amount进行分区。这允许对数据的分布进行更细粒度的控制。

让我们插入一些数据，看看它是如何工作的：

INSERT INTO sales VALUES
(1, '2022-12-15', 500),
(2, '2022-12-20', 2000),
(3, '2023-03-10', 800),
(4, '2023-03-15', 3000),
(5, '2023-08-01', 1500);

-- 让我们检查每条记录被分配到哪个分区
SELECT *, PARTITION_NAME FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'sales';

这个查询将显示每条记录根据我们的范围条件被分配到哪个分区。

列表列分区

最后但同样重要的是，让我们探索列表列分区。这就像根据颜色和类型来分类你的衣服——你可以有一个抽屉放红色衬衫，另一个抽屉放蓝色裤子，等等。

以下是一个使用products表的例子：

CREATE TABLE products (
id INT NOT NULL,
name VARCHAR(50),
category VARCHAR(20),
sub_category VARCHAR(20)
)
PARTITION BY LIST COLUMNS(category, sub_category) (
PARTITION p_electronics VALUES IN (('Electronics', 'Phones'), ('Electronics', 'Laptops')),
PARTITION p_clothing VALUES IN (('Clothing', 'Shirts'), ('Clothing', 'Pants')),
PARTITION p_other VALUES IN (('Books', 'Fiction'), ('Books', 'Non-fiction'))
);

现在，让我们添加一些产品：

INSERT INTO products VALUES
(1, 'iPhone 12', 'Electronics', 'Phones'),
(2, 'MacBook Pro', 'Electronics', 'Laptops'),
(3, 'Levi\'s Jeans', 'Clothing', 'Pants'),
(4, 'Harry Potter', 'Books', 'Fiction');

-- 让我们检查每个产品被分配到哪个分区
SELECT *, PARTITION_NAME FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'products';

这个查询将显示每个产品根据其类别和子类别被分类到相应的分区。

分区方法比较

方法	使用场景	优点	缺点
垂直分区	大表中有不常使用的列	提高查询性能，更好的数据管理	为获取完整数据需要更复杂的连接
范围列分区	基于时间或数值范围的数据	对范围查询高效，适合历史数据	可能导致数据分布不均
列表列分区	具有多个属性的类别数据	对数据位置有精确控制，适合复杂的分类	当类别很多时可能会变得难以管理

就这样，伙计们！我们已经穿越了MySQL垂直分区的领域，探索了范围列分区的领域，并且进入了列表列分区的领土。记住，就像组织你的房间一样，数据库设计的关键是将东西放在最合理、最容易找到的地方。继续练习，很快你将成为一个数据库组织大师！

Credits: Image by storyset