Go to Qiita Advent Calendar Top
LoginSignup
26
21

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@umi

MySQL のインデックスを試してみる

Posted at

MySQL はindex 貼ると早くなるってくらいの知識しかないのですが、どうすれば早くなるのか試してみました。
試した時の手順をメモとして残しておきます。
（index は貼るものか作るものか付けるものかよくわかりませんが、以下は貼るで統一しておきます。）

基本操作の確認

index の貼り方

ALTER TABLE <テーブル名> ADD INDEX <インデックス名>(<カラム名>);
または
CREATE INDEX <テーブル名> ON <インデックス名>(<カラム名>)

index の削除の仕方

ALTER TABLE <テーブル名> DROP INDEX <インデックス名>;

index の確認方法

SHOW INDEX FROM <テーブル名>;

また、EXPLAIN <調べたいクエリ> とコマンドを打つことで、クエリの実行方法を調査してくれます。

試してみる

確認用の適当なテーブルを作ります。
通し番号、カテゴリー、適当な数字の3つの行を持つテーブルです。
10000000行ほどデータを突っ込みます。

mysql> create table itest3 (id int, category varchar(5), num int);
Query OK, 0 rows affected (0.02 sec)

mysql> load data infile "<PATH_TO_FILE>" into table itest3 fields terminated by ","
Query OK, 10000000 rows affected (30.01 sec)
Records: 10000000  Deleted: 0  Skipped: 0  Warnings: 0

データは以下のスクリプトで作りました。

create.py
# !/usr/bin/python
# -*- coding: utf-8 -*-

import random
row_number = 10000000
category = ["t1", "t2", "t3", "t4", "t5"]
for x in xrange(row_number):
    line = str(x) + "," + category[int(random.random()*5)] + "," + str(int(random.random()*100))
    print line

%  python create.py > test.csv

適当なクエリを試してみます。

mysql>  select count(*) from itest3 where category = "t2";
+----------+
| count(*) |
+----------+
|  1999825 |
+----------+
1 row in set (4.00 sec)

mysql> select count(*) from itest3 where category = "t2" and num > 80 ;
+----------+
| count(*) |
+----------+
|   380036 |
+----------+
1 row in set (4.32 sec)

category で検索、category とnum で検索どちらもだいたい4秒くらいかかりました。

index を貼ってみます。

mysql> alter table itest3 add index category_index(category);
Query OK, 0 rows affected (19.58 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from itest3;
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| itest3 |          1 | category_index |            1 | category    | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

category にindex を貼ってみました。
SHOW INDEX コマンドで現在のidnex が確認できます。

フィールド名 意味
Table テーブル名
Non_unique インデックスが重複を含まないなら 0
Key_name インデックス名
Seq_in_index インデックスのフィールド番号。1 から始まります。
Column_name index を貼っているカラムの名前
Collation インデックスでの ソート方法です。A (Ascending) か NULL (Not sorted) があります。
Cardinality インデックスのユニークな値の数。
Sub_part もしこのフィールドがインデックスに一部分だけ使用している場合、そのインデックスに使用しているキャラクター数をしめす。 もしキー全体がインデックスされているなら NULL 。
Comment Various remarks. For now, it tells whether index is FULLTEXT or not.

参考
SHOW構文：テーブル情報を表示する

mysql>  select count(*) from itest3 where category = "t2";
+----------+
| count(*) |
+----------+
|  1999825 |
+----------+
1 row in set (0.75 sec)

同じクエリが4秒->0.75 秒とかなり高速化されていることがわかります。

mysql> select count(*) from itest3 where category = "t2" and num > 80 ;
+----------+
| count(*) |
+----------+
|   380036 |
+----------+
1 row in set (1 min 20.11 sec)

でもこちらのクエリは4.32秒->1分20秒に。。。
こちらはとんでもなく遅くなっていました。

次にcategory とnum に複合index を貼ってみました。

mysql> alter table itest3 add index category_and_num_index(category,num);
Query OK, 0 rows affected (27.99 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from itest3;
+--------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| itest3 |          1 | category_index         |            1 | category    | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| itest3 |          1 | category_and_num_index |            1 | category    | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| itest3 |          1 | category_and_num_index |            2 | num         | A         |         501 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

先ほど激遅だったクエリを実行してみると……

mysql> select count(*) from itest3 where category = "t2" and num > 80 ;
+----------+
| count(*) |
+----------+
|   380036 |
+----------+
1 row in set (0.21 sec)

超高速化されていました（約80秒->0.21秒）

最初のindex で早くなったクエリを実行してみると、

mysql> select count(*) from itest3 where category = "t2";
+----------+
| count(*) |
+----------+
|  1999825 |
+----------+
1 row in set (0.73 sec)

速いままでした。

以下にindex を貼る前と後で、同じクエリに対してEXPLAIN を実行した結果を載せておきます。

はじめ

mysql> explain select count(*) from itest3 where category = "t2";
+----+-------------+--------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | itest3 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10777272 |    10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select count(*) from itest3 where category = "t2" and num > 80;
+----+-------------+--------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | itest3 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10777272 |     3.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

alter table itest3 add index category_index(category); の後

mysql> explain select count(*) from itest3 where category = "t2";
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+---------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys  | key            | key_len | ref   | rows    | filtered | Extra       |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+---------+----------+-------------+
|  1 | SIMPLE      | itest3 | NULL       | ref  | category_index | category_index | 18      | const | 3690834 |   100.00 | Using index |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select count(*) from itest3 where category = "t2" and num > 80;
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+---------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys  | key            | key_len | ref   | rows    | filtered | Extra       |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+---------+----------+-------------+
|  1 | SIMPLE      | itest3 | NULL       | ref  | category_index | category_index | 18      | const | 3690834 |    33.33 | Using where |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

alter table itest3 add index category_and_num_index(category,num); の後

mysql> explain select count(*) from itest3 where category = "t2";
+----+-------------+--------+------------+------+---------------------------------------+----------------+---------+-------+---------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys                         | key            | key_len | ref   | rows    | filtered | Extra       |
+----+-------------+--------+------------+------+---------------------------------------+----------------+---------+-------+---------+----------+-------------+
|  1 | SIMPLE      | itest3 | NULL       | ref  | category_index,category_and_num_index | category_index | 18      | const | 3690834 |   100.00 | Using index |
+----+-------------+--------+------------+------+---------------------------------------+----------------+---------+-------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select count(*) from itest3 where category = "t2" and num > 80;
+----+-------------+--------+------------+-------+---------------------------------------+------------------------+---------+------+--------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys                         | key                    | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------------------------------+------------------------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | itest3 | NULL       | range | category_index,category_and_num_index | category_and_num_index | 23      | NULL | 775046 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------------------------------+------------------------+---------+------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

(何故1つ目のindex で2番目のクエリがこんなに遅くなったのかがよく分からない……)

まとめ

  • 行いたい検索に合わせて使うカラムを指定したindex を貼ると早くなる。
  • 適当なindex では逆に遅くなるパターンも。
  • index を貼る前に、そのテーブルに対してどのような検索を行いたいかを考える。
  • 実際に使っているテーブルに対してindex を貼ったせいで遅くなったりしたら困るので、もうちょい勉強した方が良さげ。

参考

MySQLのインデックスの効果を理解する
MySQLコマンドやSQL文に関する各種メモ書き
MySQL初級者を脱するために勉強してること -INDEX編-
MySQLのExplainを確認する

26
21
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
Sign upLogin
26
21

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?