概要
この記事ではパフォーマンスについてまとめていく。
具体的には、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は以下である。
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 テーブル名 (対象カラム名)で付与できる。
実際のコマンドは以下である。
CREATE INDEX student_name_index
ON students (student_name);
indexが付与されているかを確認する。
SHOW INDEX FROM テーブル名;で確認できる。
実際のコマンドは以下である。
SHOW INDEX FROM students;
確認結果は以下である。
Table：テーブル名
Non_unique：indexが重複を含むならなら0, 含めないなら1。
Key_name：インデックス名
Seq_in_indexindexの番号。1から始まる。
Column_name：カラム名
Collation：indexでのソート方法。AはAscending(昇順)。
Cardinality：indexのユニークな値の数。低いほどカラム内で重複するデータが多いことを意味する。
Sub_part：カラムの一部のデータに対してindexを付与している場合に使用される。カラム全体に付与している場合はNULL。
Packed：キーがパックされる方法を示す。パックされない場合はNULL。
Index_type：indexの方法を示す。デフォルトはB-Tree。
あとは大量のデータを入れる。
MySQLで大量のデータを追加してみるを参考。
-- 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万件)
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コマンドを使って実行計画をみる。
EXPLAIN SELECT * FROM students INNER JOIN classes ON students.class_id = classes.class_id;
結果はこちら。
possible_keys : MySQL がこのテーブル内の行の検索に使用するために選択できる
key : MySQL が実際に使用することを決定したキー (インデックス)
studentsテーブルのclass_idにindexを付与して実行計画をみると以下の結果が得られた。
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