パーティションとは
1つのテーブルのデータの格納場所を分割する処理のことです。
1つのテーブルの範囲などで分割します。
パーティーションのおかげでDBのパフォーマンスが良くなります。
⚫︎補足
DBはデータベースの略で、これからDBで表現します。
なぜパーティションを使うのか
DBの検索を速くするためです。
そしてデータベースの中身が見やすくなります。
パーティションとはどんなものか。
こちらの記事から画像を引用させていただきます。
https://qiita.com/fuk101/items/603ddb445070aebbfcfa
パーティション化していないと年度別や日付ごとに分けないと
下のようにデータの中身がわかりづらくなってしまいます。
パーティション化していると
年度別に分割できたりするのでデータの中身がわかりやすくなります。
パーティションの仕組み
イベントのデータを元に説明します。
⚫︎イベントテーブル
| 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は２０２１年以降のデータが入ってきた時に備えて
);
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文のオーバーヘッドが増える。
複合インデックス
複数のカラムに１つのインデックスにする
⚫︎clubテーブル
| ID | クラブ名 | メンバー |
|---|---|---|
| 1 | 野球部 | 太郎くん |
| 2 | サッカー部 | 次郎くん |
この表を元に複合インデックスのやり方です。
SELECT * FROM clubテーブル WHERE クラブ名 = XXX AND メンバー= XXX
これをインデックスに変えると
CREATE INDEX IDX_clubname_member ON clubテーブル(クラブ,メンバー);
参考動画
参考資料