41から始めました

文字通り41歳から始めたブログです。DB(MySQL)を使ってお仕事してるので、DB周りの話を中心に最近始めたこととかをTwitterのノリで書いています。なお、本サイトにおいて示されている見解は私個人の見解であり、所属団体や組織を代表するものではありません。

インデックス指定時のカラムの桁数について

こんなテーブルがありました

CREATE TABLE `hogehoge` (
  `hoge_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'HOGEID',
  ・・・(略)・・・
  `hoge_text` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'ほげほげ',
  PRIMARY KEY (`hoge_id`),
  KEY `i_csv_output_bulk_request_1` (`hoge_text`(10))
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='ほげほげ情報';

良く見るとインデックスのところに指定されたカラムに(10)という数字が入っています。

どうやらカラムの長さを指定しているっぽい

文字列長のカラムですし、 https://dev.mysql.com/doc/refman/5.6/ja/create-index.html を読む限り、lengthと言っているので桁数で間違いないでしょう。

更に、

col_name(length) 構文を使用してインデックスプリフィクス長を指定することにより、カラム値の先頭の部分のみを使用するインデックスを作成できます。

と言っているので、どうやらカラムの先頭n文字だけをインデックスにすることができるようです。( )

※ちなみに、このカッコ「()」内の数字はバイナリ文字列の場合は桁数ではなく、バイト数になるようですので注意が必要です。

指定するとどうなるのか試してみよう

指定したインデックスと指定しない場合で試してみましょう。

①10文字で指定してみる

CREATE TABLE `test_index_sbstr` (
   `test_index_sbstr_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
   `test_index_text` varchar(255) NOT NULL DEFAULT '' COMMENT '部分的にインデックスを作る列',
   PRIMARY KEY (`test_index_sbstr_id`),
   KEY i_test_index_sbstr_1 (test_index_text(10))
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='部分的インデックステスト';

こんな255文字入力可能なカラムに対し、先頭10文字だけを対象とするインデックスを作ってみます。 そして、適当なデータを登録します。

insert into test_index_sbstr (test_index_text) values ('1111111111aiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukeko');
insert into test_index_sbstr (test_index_text) values ('1111111112aiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukeko');
insert into test_index_sbstr (test_index_text) values ('1111111113aiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukeko');
insert into test_index_sbstr (test_index_text) values ('1111111114aiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukeko');
insert into test_index_sbstr (test_index_text) values ('1111111115aiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukeko');
insert into test_index_sbstr (test_index_text) values ('1111111116aiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukeko');
insert into test_index_sbstr (test_index_text) values ('1111111117aiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukeko');
insert into test_index_sbstr (test_index_text) values ('1111111118aiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukeko');
insert into test_index_sbstr (test_index_text) values ('1111111119aiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukeko');
insert into test_index_sbstr (test_index_text) values ('1111111120aiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukeko');

EXPLAIN を取ってみましょう

mysql> EXPLAIN select * from test_index_sbstr where test_index_text = '1111111111aiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueo';
+----+-------------+------------------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
| id | select_type | table            | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test_index_sbstr | NULL       | ref  | i_test_index_sbstr_1 | i_test_index_sbstr_1 | 42      | const |    1 |   100.00 | Using where |
+----+-------------+------------------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

インデックス、使われていますね。(もちろん結果も帰ってきました)

②指定しないで作ってみます

インデックス作成時にカラムの桁数を指定しないで作ってみました

CREATE TABLE `test_index_sbstr` (
   `test_index_sbstr_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
   `test_index_text` varchar(255) NOT NULL DEFAULT '' COMMENT '部分的にインデックスを作る列',
   PRIMARY KEY (`test_index_sbstr_id`),
   KEY `i_test_index_sbstr_2` (`test_index_text`)
 ) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='部分的インデックステスト'

カラムがフルの長さでのEXPLAIN を取ってみましょう

mysql> EXPLAIN select * from test_index_sbstr where test_index_text = '1111111111aiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueokakikukekoaiueok
akikukeko
+----+-------------+------------------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
| id | select_type | table            | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test_index_sbstr | NULL       | ref  | i_test_index_sbstr_2 | i_test_index_sbstr_2 | 1022    | const |    1 |   100.00 | Using index |
+----+-------------+------------------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

前者がkey_len =42だったのに対し、今回は1022 と25倍近い数字になっています。 (桁数の倍率をほぼ同じですね)

③ちなみに、小さすぎるとどうなるのか?

 CREATE TABLE `test_index_sbstr` (
   `test_index_sbstr_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
   `test_index_text` varchar(255) NOT NULL DEFAULT '' COMMENT '部分的にインデックスを作る列',
   PRIMARY KEY (`test_index_sbstr_id`),
   KEY `i_test_index_sbstr_3` (`test_index_text`(3))
 ) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='部分的インデックステスト'

今回登録したデータは9桁目と10桁目の数字以外は全て同じ文字列です。 先頭3桁だけでは全く同じ値に見えます。

EXPLAINを取ると

mysql> EXPLAIN select * from test_index_sbstr where test_index_text like '1111111111%';
+----+-------------+------------------+------------+------+----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table            | partitions | type | possible_keys        | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------------+------------+------+----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test_index_sbstr | NULL       | ALL  | i_test_index_sbstr_3 | NULL | NULL    | NULL |  101 |   100.00 | Using where |
+----+-------------+------------------+------------+------+----------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

フルテーブルスキャンになってしまいました。

カラムの数字指定が何に影響する?

カラム内の名前が一般に最初の 10 文字と異なっている場合は、このインデックスが、name カラム全体から作成されたインデックスよりはるかに遅いことはないはずです。また、インデックスにカラムプリフィクスを使用するとインデックスファイルをはるかに小さくできるため、多くのディスク領域が節約されるだけでなく、INSERT 操作も高速化される可能性があります。

公式ドキュメントにもある通り、ディスク領域が節約されたり、INSERT時の速度改善にもつながる可能性があります。

バカでかいTEXT型やCHAR、VARCHAR型にインデックスが張られていて、WHERE句での条件指定時にカラム値全体を見る必要が無い(前半数文字で十分である)ことが自明であれば、インデックス作成時のカラムの長さを調整してみるとパフォーマンス改善につながるかもしれません。