LoginSignup
6
4

データベースのパーティションとインデックスについて

Last updated at Posted at 2022-01-21

パーティションとは

1つのテーブルのデータの格納場所を分割する処理のことです。
1つのテーブルの範囲などで分割します。
パーティーションのおかげでDBのパフォーマンスが良くなります。

⚫︎補足
DBはデータベースの略で、これからDBで表現します。

なぜパーティションを使うのか

DBの検索を速くするためです。
そしてデータベースの中身が見やすくなります。

パーティションとはどんなものか。

こちらの記事から画像を引用させていただきます。
https://qiita.com/fuk101/items/603ddb445070aebbfcfa

パーティション化していないと年度別や日付ごとに分けないと
下のようにデータの中身がわかりづらくなってしまいます。

image.png

パーティション化していると
年度別に分割できたりするのでデータの中身がわかりやすくなります。

image.png

パーティションの仕組み

イベントのデータを元に説明します。

 ⚫︎イベントテーブル

ID 都道府県 イベント名 開催年月日
1 東京 Aさんのライブ 2022年1月20日
2 大阪 Bさんの試写会 2021年12月26日
3 福岡 Cさんの講演会 2020年6月20日
4 東京 Dさん展示会 2021年4月20日
5 大阪 Eさんのサイン会 2021年4月20日

これを都道府県ごとに分割します。
東京なら東京、大阪なら大阪、福岡なら福岡にデータが格納されているイメージです。
何らかのグループに分けてみていきます。

|テーブル||
|--|--|--|
|都道府県|ID|
|東京|1,4|
|大阪|2,5|
|福岡|3|

大阪のデータを取るSQL文を書いてみます。

SELECT * FROM イベントテーブル
WHERE 都道府県 = 大阪 

大阪だけ検索すれば大阪のでデータが取れ上のように文が短くて済みます。

パーティションを使わないと

SELECT * FROM イベントテーブル
WHERE 都道府県 = 大阪 AND イベント名 = Dさん展示会

みたいに余計にSQL文を書くことになります。

#パーティションの種類
パーティションの種類について説明していきます。

1.リストパーティション
キーカラム固定値分割する方法です。
キーカラムは今回は都道府県です。

ID 都道府県 イベント名 開催年月日
1 東京 Aさんのライブ 2022年1月20日
2 大阪 Bさんの試写会 2021年12月26日
3 福岡 Cさんの講演会 2020年6月20日
4 東京 Dさん展示会 2021年4月20日
5 大阪 Eさんのサイン会 2021年4月20日

|テーブル||
|--|--|--|
|都道府県|ID|
|東京|1,4|
|大阪|2,5|
|福岡|3|

2.レンジパーティション
キーカラム値の範囲分割する方法です。
下のように格納されます。

ID 都道府県 イベント名 開催年月日
1 東京 Aさんのライブ 2022年1月20日
2 大阪 Bさんの試写会 2021年12月26日
3 福岡 Cさんの講演会 2020年6月20日
4 東京 Dさん展示会 2021年4月20日
5 大阪 Eさんのサイン会 2021年4月20日

|テーブル||
|--|--|--|
|開催年月日|ID|
|2020年1月1日~2020年12月31日|3|
|2021年1月1日~2021年12月31日|2,4,5|
|2022年1月1日~2022年12月31日|1|

3.ハッシュパーティション
均等に分割する方法です。

ID 都道府県 イベント名 開催年月日
1 東京 Aさんのライブ 2022年1月20日
2 大阪 Bさんの試写会 2021年12月26日
3 福岡 Cさんの講演会 2020年6月20日
4 東京 Dさん展示会 2021年4月20日
5 大阪 Eさんのサイン会 2021年4月20日

上のキーカラムをハッシュ化すると必ずA,B,Cのどれかに当てはまる。
1 A
2 B
3 C
4 D
5 A

|テーブル||
|--|--|--|
|パーティション|ID|
|パーティションA||
|パーティションB||
|パーティションC||
|パーティションD||

#パーティションの追加
パーティションの追加の説明です。

⚫︎イベントテーブル

ID 都道府県 イベント名 開催年月日
1 東京 Aさんのライブ 2022年1月20日
2 大阪 Bさんの試写会 2021年12月26日
3 福岡 Cさんの講演会 2020年6月20日
4 東京 Dさん展示会 2021年4月20日
5 大阪 Eさんのサイン会 2021年4月20日

レンジパーティション作成のSQL文です。

ALTER TABLE イベントテーブル PARTION BY RANGE (YEAR(開催年月日))(
PARTITION  p2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION] p2020 VALUES LESS THAN (2020) ENGINE = InnoDB,
PARTITION  p2021 VALUES LESS THAN (2021) ENGINE = InnoDB,
PARTITION  pmax  VALUES MAXVALUE --pmaxは2021年以降のデータが入ってきた時に備えて
);

MySQLを使う時の注意点

パーティションのキーカラムは
プライマリキー or ユニークキーにします。

⚫︎イベントテーブル

ID 都道府県 イベント名 開催年月日
1 東京 Aさんのライブ 2022年1月20日
2 大阪 Bさんの試写会 2021年12月26日
3 福岡 Cさんの講演会 2020年6月20日
4 東京 Dさん展示会 2021年4月20日
5 大阪 Eさんのサイン会 2021年4月20日
CREATE TABLE tbl name ..., PRIMARY KEY(ID,開催年月日) 
--(ID,開催年月日)が複合プライマリキー

注意点

・パーティションよりインデックスを優先的に使う
・WHERE句にはキーカラムを含める
・外部キー制約が使えない(My SQL)

インデックスとは

データベースの検索を速くするもののことです。
言い換えると見出しのことです。

インデックスの作成

下のようなデータがあったとします。

⚫︎会社テーブル

ID 社員番号 名前
1 1459 田中さん
2 4689 鈴木さん
3 5698 佐藤さん

SQLのインデックスの作成の文です。

CREATE INDEX インデックス名  ON テーブル名(列名)

上の表に当てはめると

CREATE INDEX Company  ON  会社テーブル(社員番号)

みたいな感じです。インデックス名は名前が重複しないように自由に変えられます
これでデータベース内にインデックスを作成できます。

インデックスの削除

インデックスを削除する文も見ていきましょう。
同じ会社テーブルがあるとします。

⚫︎会社テーブル

ID 社員番号 名前
1 1459 田中さん
2 4689 鈴木さん
3 5698 佐藤さん

SQLのインデックスの削除文です。

DROP INDEX インデックス名 ON テーブル名

上の表に当てはめると

DROP INDEX Company ON 会社テーブル

データベース内にインデックスを削除できます。

インデックス設定の効果が高い列

⚫︎WHERE
WHERE句の絞り込み条件でインデックスを利用します。

SELECT * FROM 会社テーブル
WHERE 名前 = '田中さん'

会社テーブルから田中さんのデータを取得するみたいな意味です。

⚫︎JOIN
テーブル同士の結合はインデックスを使うと高速化します。
上記の会社テーブルと課外活動テーブルを想定します。

課外活動テーブル

ID クラブ番号 メンバー
1 3 中田
2 4 岡田
SELECT * FROM 会社テーブル
JOIN 課外活動テーブル
ON 会社テーブル.社員番号 = 課外活動テーブル.クラブ番号

会社テーブルと課外活動テーブルを結合して
その中の社員番号とクラブ番号を結合する意味です。

⚫︎ORDER BY
インデックスの並び替えを高速にします。

SELECT * FROM 会社テーブル
ORDER BY 名前 社員番号

会社テーブルの名前と社員番号を取得するという意味です。

⚫︎補足
主キー、外部キーには自動でインデックスが与えられます。

インデックス作成のデメリット

・検索のための情報を保存するためにディスクの容量が消費し更新処理が遅くなります。
・テーブルのデータが変更されインデックスを書き換える必要がある。
INSERT文、UPDATE文、DELETE文のオーバーヘッドが増える。

複合インデックス

複数のカラムに1つのインデックスにする

⚫︎clubテーブル

ID クラブ名 メンバー
1 野球部 太郎くん
2 サッカー部 次郎くん

この表を元に複合インデックスのやり方です。

SELECT * FROM clubテーブル WHERE クラブ名 = XXX  AND メンバー= XXX

これをインデックスに変えると

CREATE INDEX IDX_clubname_member ON clubテーブル(クラブ,メンバー);

参考動画

参考資料

6
4
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
6
4