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
);

ここで、元のテーブルを3つの小さなテーブルに分割しました。これにより、特定の列にだけ頻繁にアクセスする場合、パフォーマンスが向上する可能性があります。

縦分割を使用する理由は何ですか？

1. クエリのパフォーマンス向上
2. より良いデータ管理
3. セキュリティの強化（敏感データへのアクセスを制限できます）

新しい構造でデータを插入および检索する方法を見てみましょう：

-- データの插入
INSERT INTO students_main VALUES (1, 'アリス', 20);
INSERT INTO students_contact VALUES (1, '[email protected]', '123-456-7890');
INSERT INTO students_address VALUES (1, '123メインストリート、アニータウン、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';

このクエリは、各製品がどのパーティションに基づいてカテゴリとサブカテゴリに整理されたかを示します。

分割方法の比較

方法	使用ケース	利点	欠点
縦分割	大規模なテーブルで使用頻度の低い列がある場合	クエリのパフォーマンス向上、より良いデータ管理	全データの检索には複雑なJOINが必要
範囲列の分割	時系列データや数値範囲データ	範囲ベースのクエリに効率的、履歴データに適している	データの分布が不均等になる可能性
リスト列の分割	複数の属性を持つカテゴリデータ	データの配置に対する正確な制御、複雑なカテゴリ分けに適している	多くのカテゴリがあると管理が困難になる

そして、皆さん！私たちはMySQLの縦分割、範囲列の分割、リスト列の分割という土地を旅しました。お部屋を整理するのと同様に、データベース設計の鍵は、最も意味がある場所に物を置き、最も簡単に見つけられる場所に置くことです。練習を続けることで、すぐにデータベース整理の達人になるでしょう！

Credits: Image by storyset