SQLのindex, 実行計画について

# 概要
この記事ではパフォーマンスについてまとめていく。
具体的には、indexと実行計画についてコマンドベースでまとめていく。

# indexとは
本の索引のようなもので、どの行にどのデータが格納されているかを示すものである。
カラムにindexを付与することで、テーブルからデータを検索する際にindexを使用するようになり、パフォーマンスを向上することができる。ただ、場合によってはパフォーマンスが落ちることもあるのでindexを貼るカラムは慎重に選択する必要がある。

# indexの効果が高いカラムの例
- **WHERE句**, **ORDER BY句**を頻繁に使用するカラム
- **INNER JOIN句**などテーブルの結合条件によく使用するカラム

上記の句を使って、**大量のデータ**が格納されているテーブルで、**少量のレコード**を検索するような場合や、NULL値が多いカラムからNULLでない値を検索する場合にも有効である。

# indexをつけるとパフォーマンスが低下するカラムの特徴
- **WHERE句**, **ORDER BY句**, **INNER JOIN句**をあまり使用しないカラム

テーブルに格納されているデータが少量の場合、indexを使った検索をしてもあまりパフォーマンス向上にならず、むしろパフォーマンスが低下してしまう場合がある。

# データベース作成からindexの付与まで
データベース作成から手順を一つずつ見ていく。(MySQLを使用)
まず、データベース、テーブルを作成する。
今回はschoolデータベースを作成し、classテーブルとstudentテーブルを作成する。

- classesテーブル
    - id (ID)
    - number_of_students (生徒数)

- studentsテーブル
    - id (ID)
    - student_name (生徒の名前)
    - student_profile (生徒のプロフィール)
    - class_id(クラスID)

SQLは以下である。

```sql
CREATE DATABASE school;
USE school;
CREATE TABLE school.classes(
    id INT NOT NULL,
    number_of_students INT
);

CREATE TABLE school.students(
    id INT NOT NULL,
    student_name VARCHAR(100),
    student_profile TEXT,
    class_id INT NOT NULL
);
```

#### 補足
上記のSQLをひとつずつ見ていく。
`CREATE DATABASE school;` ： 元となるデータベースを作成
`USE school;`： 使用するデータベースを指定
`CREATE TABLE school.class()`: テーブルを作成。`データベース名.クラス名`とすることで特定のデータベースにテーブルを作成できる。`()`の中に作成するカラムを記述していく
`id INT NOT NULL PRIMARY KEY,student_name VARCHAR(100),student_profile TEXT,`: カラムを作成。(オプションの説明は省略)

続いて、indexを付与していく。
student_nameにindexを付与する。
`CREATE INDEX インデックス名 ON テーブル名 (対象カラム名)`で付与できる。
実際のコマンドは以下である。

```sql
CREATE INDEX student_name_index
    ON students (student_name);![スクリーンショット 2020-11-17 3.20.32.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/251807/d8dbcd5a-8420-8f0f-2889-bf6e7b4114a6.png)

```

indexが付与されているかを確認する。
`SHOW INDEX FROM テーブル名;`で確認できる。
実際のコマンドは以下である。

```sql
SHOW INDEX FROM students;
```

確認結果は以下である。
![スクリーンショット 2020-11-17 3.22.09.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/251807/baa8f896-d247-d5e9-0612-8aaf10a3e994.png)
`Table`：テーブル名
`Non_unique`：indexが重複を含むならなら0, 含めないなら1。
`Key_name`：インデックス名
`Seq_in_index`indexの番号。1から始まる。
`Column_name`：カラム名
`Collation`：indexでのソート方法。AはAscending(昇順)。
`Cardinality`：indexのユニークな値の数。低いほどカラム内で重複するデータが多いことを意味する。
`Sub_part`：カラムの一部のデータに対してindexを付与している場合に使用される。カラム全体に付与している場合はNULL。
`Packed`：キーがパックされる方法を示す。パックされない場合はNULL。
`Index_type`：indexの方法を示す。デフォルトはB-Tree。

あとは大量のデータを入れる。
[MySQLで大量のデータを追加してみる](https://yk5656.hatenadiary.org/entry/20140202/1391910335)を参考。

```sql
-- aa
INSERT INTO students(id, student_name, student_profile, class_id) values
(1, 'sato', 'test', 1),
(2, 'tanaka', 'test', 2),
(3, 'kimura', 'test', 3),
(4, 'ito', 'test', 1),
(5, 'nakamura', 'test', 2),
(6, 'kato', 'test', 3),
(7, 'kobayashi', 'test', 1),
(8, 'saito', 'test', 2),
(9, 'watanabe', 'test', 3),
(10, 'suzuki', 'test', 1);
insert into students (id, student_name, student_profile, class_id)
 select s1.id, s1.student_name, s1.student_profile, s1.class_id
 from students s1, students s2, students s3, students s4, students s5, students s6, students s7;
```

`order by`を使って検索してみる。(100万件)

```sql
SELECT * FROM students order by student_name limit 10000000;
```

結果は以下である。
indexがある場合とない場合で、２倍程度の差が見られた。

- indexなし
    - 10000000 rows in set (26 min 9.82 sec)
- indexあり
    - 10000000 rows in set (13 min 1.03 sec)

# 実行計画とは
実行計画とは、クエリ実行時にどのインデックスを使っているか、あるいは使ってないかをMySQLが判断した結果をまとめたものである。クエリの先頭に`EXPLAIN`コマンドをつけることでそのクエリでインデックスを使っているか、効率的にインデックスを使用できているかを確認することができる。
例として、上のjoinを使った検索において`EXPLAIN`コマンドを使って実行計画をみる。

```sql
EXPLAIN SELECT * FROM students INNER JOIN classes ON students.class_id = classes.class_id;
```
結果はこちら。
![スクリーンショット 2020-11-17 3.47.08.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/251807/32118934-3f35-fad9-29ab-120a77989660.png)

`possible_keys` : MySQL がこのテーブル内の行の検索に使用するために選択できる
`key` : MySQL が実際に使用することを決定したキー (インデックス)

`students`テーブルの`class_id`にindexを付与して実行計画をみると以下の結果が得られた。
![スクリーンショット 2020-11-17 3.48.56.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/251807/1ae562a6-8aa6-2cb0-cc0e-d12f55409e33.png)

`key`にindexが入り、`rows`の値が上と比べて半分程度になっていることがわかる。
※`rows`: SQLの処理によって返されるレコードの行数

# 参考
#### index
MySQLのIndexをはるコツ : https://qiita.com/katsukii/items/3409e3c3c96580d37c2b
【SQL】インデックスの基本知識まとめ : https://qiita.com/aberyotaro/items/ff3046ef12634e2791e3
MySQLでインデックスを貼る時に読みたいページまとめ(初心者向け）: https://qiita.com/C058/items/1c9c57f634ebf54d99bb
インデックスの意味とメリット・デメリット: https://www.dbonline.jp/sqlite/index/index1.html#section2
[DB初心者向け] インデックスの使い方についての解説: https://kirohi.com/index_for_beginer#i-3

#### 実行計画 
SQL実行計画の疑問解決には「とりあえずEXPLAIN」しよう: https://thinkit.co.jp/article/9658
MySQLの実行計画（EXPLAIN）に関する覚え書 : https://qiita.com/tsuyopon-md/items/9e0517297816f656b541