MYSQLとパフォーマンス(INDEX)

インデックスをテーブルに対して付与すると、容易にパフォーマンスをしてくれますが
慣れてしまうと、あまり考えずに流れ作業のように作成してしまうので、
少し気にしながら作成した方がいいかもしれません。

一般的にSELECTオペレーションはインデックスを利用すると早くなりますが(Where句やOrderby利用時)、
INSERT,DELETE,UPDATEオペレーションは、インデックスも同時に更新しなければいけないので遅くなります。
UPDATE,DELETEの処理は

データを早く見つける事が出来るので早くなる事もありますが

データとインデックスの両方を更新しなければいけないので遅くなる場合もあります。
where句、Group by, Order byを利用しない場合や列のデータの種類が少ない場合はインデックスを付けても
パフォーマンスの向上は期待できない可能性があります。

「確認用テーブル」　

CREATE TABLE `T_index` (
`i1` char(10) NOT NULL default '',
`i2` char(10) NOT NULL default '',
KEY `i1` (`i1`(3)),
KEY `i2` (`i2`)
) ENGINE=MyISAM DEFAULT CHARSET=UTF8



mysql> CREATE TABLE `T_index` (
-> `i1` char(10) NOT NULL default '',
-> `i2` char(10) NOT NULL default '',
-> KEY `i1` (`i1`(3)),
-> KEY `i2` (`i2`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=UTF8;
Query OK, 0 rows affected (0.19 sec)


mysql> desc T_index;
+——-+———-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+———-+——+—–+———+——-+
| i1 | char(10) | NO | MUL | | |
| i2 | char(10) | NO | MUL | | |
+——-+———-+——+—–+———+——-+
2 rows in set (0.00 sec)

mysql> INSERT INTO T_index(i1,i2) values('abcdefg','abcdefg');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO T_index(i1,i2) values('hijklmn','hijklmn');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO T_index(i1,i2) values('opqrstu','opqrstu');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO T_index(i1,i2) values('vwxyz','vwxyz');
Query OK, 1 row affected (0.00 sec)

mysql> select * from T_index;
+———+———+
| i1 | i2 |
+———+———+
| abcdefg | abcdefg |
| hijklmn | hijklmn |
| opqrstu | opqrstu |
| vwxyz | vwxyz |
+———+———+
4 rows in set (0.00 sec)

mysql>

mysql> SELECT i1 FROM T_index WHERE i1 LIKE 'abc%';
+———+
| i1 |
+———+
| abcdefg |
+———+
1 row in set (0.00 sec)

mysql> SELECT i2 FROM T_index WHERE i2 LIKE 'abc%';
+———+
| i2 |
+———+
| abcdefg |
+———+
1 row in set (0.00 sec)

mysql> explain SELECT i1 FROM T_index WHERE i1 LIKE 'abc%';
+—-+————-+———+——-+—————+——+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———+——-+—————+——+———+——+——+————-+
| 1 | SIMPLE | T_index | range | i1 | i1 | 9 | NULL | 1 | Using where |
+—-+————-+———+——-+—————+——+———+——+——+————-+
1 row in set (0.05 sec)

mysql> explain SELECT i2 FROM T_index WHERE i2 LIKE 'abc%';
+—-+————-+———+——-+—————+——+———+——+——+————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———+——-+—————+——+———+——+——+————————–+
| 1 | SIMPLE | T_index | index | i2 | i2 | 30 | NULL | 4 | Using where; Using index |
+—-+————-+———+——-+—————+——+———+——+——+————————–+
1 row in set (0.00 sec)

mysql>

同じデータとｲﾝﾃﾞｯｸｽですが、ｲﾝﾃﾞｯｸｽ列に含まれるデータ量が違う事によりﾌﾟﾗﾝが変わっています。

ここでは、CityテーブルのName列の最初の5文字を利用して
インデックスをCreate→Drop→Createしてます。

mysql> create index idx_City_Name on City(Name (5));
Query OK, 4081 rows affected (0.15 sec)
Records: 4081 Duplicates: 0 Warnings: 0

mysql> drop index idx_City_Name on City;
Query OK, 4081 rows affected (0.10 sec)
Records: 4081 Duplicates: 0 Warnings: 0

mysql> alter table City add index idx_City_Name(Name (5));
Query OK, 4081 rows affected (0.14 sec)
Records: 4081 Duplicates: 0 Warnings: 0

参考サイト：
12.1.2. ALTER TABLE 構文
MySQLクイック・リファレンス