MYSQLのパフォーマンスチューニングを考えていると、何処かオラクルやMS SQLのチューニング
をしていた時と違う違和感を憶える。おそらくまだMYSQLに慣れていないせいだと
思うので、早めに慣れたいと思う今日この頃。

列に付けるIndexの長さと、Sortオーダーで利用される列へのインデッスクの付与
による動作確認。

index_length

comment列の先頭の文字がC以上H以下の列を抽出。
OracleでもMS SQLでもこんな感じの検索をする時は、
Likeで検索してたのですがこんな方法もあるんだな….と初めて知りました。


mysql> select * from TABLE000 where comment
-> between 'C' and 'H'
-> order by comment;
+----+------------+
| id | comment |
+----+------------+
| 3 | CAAAAAAAAA |
| 4 | DAAAAAAAAA |
| 5 | EAAAAAAAAA |
| 6 | FAAAAAAAAA |
| 7 | GAAAAAAAAA |
+----+------------+
5 rows in set (0.00 sec)

mysql>

プランは以下のような感じでインデックスが無いのでファイルソートになってる。


mysql> explain
-> select * from TABLE000 where comment
-> between 'C' and 'H'
-> order by comment;
+----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+
| 1 | SIMPLE | TABLE000 | ALL | NULL | NULL | NULL | NULL | 12 | Using where; Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)
mysql>

インデックスを追加して処理が早くなるようにして見ます。
ただここではデータが無いので速度は変わりませんが、
データが増えたときに処理速度は大きく変わります。
尚where句で指定している部分では最初の一文字しか比較
で利用していないので、indexの長さは実際の列の長さより
短くしております。利用用途が明確な場合は、よりパフォーマンスを
向上してくれるようです。


mysql> desc TABLE000;
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| comment | varchar(45) | NO | | NULL | |
+---------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE TABLE000 ADD INDEX (comment(10));
Query OK, 12 rows affected (0.18 sec)
Records: 12 Duplicates: 0 Warnings: 0

index_add


mysql> select * from TABLE000 where comment
-> between 'C' and 'H'
-> order by comment;
+----+------------+
| id | comment |
+----+------------+
| 3 | CAAAAAAAAA |
| 4 | DAAAAAAAAA |
| 5 | EAAAAAAAAA |
| 6 | FAAAAAAAAA |
| 7 | GAAAAAAAAA |
+----+------------+
5 rows in set (0.00 sec)

mysql>

インデックス追加前とインデックス追加後のプラン比較。
データ量が少ないせいかMYSQLが統計情報で判断して
取り出すデータ量に違いが出てきていないので特にインデックス
を利用していない….. 明示的に指定すればインデックスを利用して
取り出すデータもやはり減る。データが増えればきっとパフォーマンスに
大きな差がでてくるはず。。

以下 インデックス追加前、追加後、インデックスを明示的に指定して
プランを確認した場合の実行プランと取り出すデータ量の違い比較。

plan