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

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

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


# なぜパーティションを使うのか
***DBの検索を速くするため***です。
そしてデータベースの中身が見やすくなります。

# パーティションとはどんなものか。
***こちらの記事から画像を引用***させていただきます。
https://qiita.com/fuk101/items/603ddb445070aebbfcfa


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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1748789/b02486ed-2d23-e2aa-9276-cd8a9bd91ca7.png)


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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1748789/b67ac2b8-e73f-c889-10c6-eda9a28b4a53.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文***を書いてみます。

```sql
SELECT * FROM イベントテーブル
WHERE 都道府県　= 大阪　
```
大阪だけ検索すれば大阪のでデータが取れ上のように文が短くて済みます。

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

```sql
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文です。

```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日|

```sql
CREATE TABLE tbl name ..., PRIMARY KEY(ID,開催年月日) 
--(ID,開催年月日)が複合プライマリキー
```

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

# インデックスとは
***データベースの検索を速くするもののことです。***
言い換えると見出しのことです。

# インデックスの作成
下のようなデータがあったとします。

⚫︎会社テーブル

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

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

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

上の表に当てはめると

```sql
CREATE INDEX Company  ON　 会社テーブル(社員番号)
```
みたいな感じです。***インデックス名は名前が重複しないように自由に変えられます***。
これで***データベース内にインデックスを作成***できます。

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

⚫︎会社テーブル

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

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

```sql
DROP INDEX インデックス名 ON テーブル名
```
上の表に当てはめると

```sql
DROP INDEX Company ON 会社テーブル
```
***データベース内にインデックスを削除***できます。

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

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

```sql
SELECT * FROM 会社テーブル
WHERE 名前　= '田中さん'
```
会社テーブルから田中さんのデータを取得するみたいな意味です。

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

課外活動テーブル

|ID|クラブ番号|メンバー|
|--|--|--|
|1|3|中田|
|2|4|岡田|


```sql
SELECT * FROM 会社テーブル
JOIN 課外活動テーブル
ON 会社テーブル.社員番号 = 課外活動テーブル.クラブ番号
```
会社テーブルと課外活動テーブルを結合して
その中の社員番号とクラブ番号を結合する意味です。

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

```sql
SELECT * FROM 会社テーブル
ORDER BY 名前　社員番号
```
会社テーブルの名前と社員番号を取得するという意味です。

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

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

# 複合インデックス
***複数のカラムに１つのインデックス***にする

⚫︎clubテーブル

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

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

```sql
SELECT * FROM clubテーブル　WHERE クラブ名 = XXX  AND メンバー= XXX
```
これをインデックスに変えると

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


# 参考動画

https://m.youtube.com/watch?v=Aur62VsPrHg&list=PL-1KBX2gDRujQaRgEByueezHBiqHP8KDD&index=9

https://www.youtube.com/watch?v=iIoq-3veMYk&list=PL-1KBX2gDRujQaRgEByueezHBiqHP8KDD&index=8


# 参考資料
https://qiita.com/fuk101/items/603ddb445070aebbfcfa

https://www.google.com/search?q=%E3%82%B9%E3%83%83%E3%82%AD%E3%83%AA%E3%82%8F%E3%81%8B%E3%82%8Bsql%E5%85%A5%E9%96%80&rlz=1C5CHFA_enJP904JP905&oq=%E3%82%B9%E3%83%83%E3%82%AD%E3%83%AA&aqs=chrome.1.69i57j35i39j0i512l2j0i131i433i512j0i512l2j0i4i131i433i512l2j0i4i433i512.2639j0j15&sourceid=chrome&ie=UTF-8