status=’1’とかdelete=’0’といった条件はよく使用することでしょう。
そこでフラグにはどのデータ型がベストなのでしょうか。
フラグにベストなデータ型
考えられるのは
tinyint(1)
smallint(1)
といった数値型。
もしくは
char(1)
char(3)
といった文字型でしょう。
char(3)としたのは、マイナスだってありうるからです。
以下100万件のデータにランダムな10件のstatus=’1’のデータを作成して
select id,name,status from test where status=’1′
した結果です。
tinyint(1)の場合
DROP TABLE IF EXISTS `test`.`test`; CREATE TABLE `test`.`test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `status` tinyint(1) unsigned DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1; 回数=10 実行時間=3.088秒 平均=0.309秒 回数=10 実行時間=3.108秒 平均=0.311秒 回数=10 実行時間=3.112秒 平均=0.311秒
smallint(1)の場合
DROP TABLE IF EXISTS `test`.`test`; CREATE TABLE `test`.`test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `status` smallint(1) unsigned DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1; 回数=10 実行時間=3.083秒 平均=0.308秒 回数=10 実行時間=3.095秒 平均=0.309秒 回数=10 実行時間=3.115秒 平均=0.311秒
char(1)の場合
DROP TABLE IF EXISTS `test`.`test`; CREATE TABLE `test`.`test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `status` char(1) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1; 回数=10 実行時間=3.244秒 平均=0.324秒 回数=10 実行時間=3.202秒 平均=0.320秒 回数=10 実行時間=3.206秒 平均=0.321秒
char(3)の場合
DROP TABLE IF EXISTS `test`.`test`; CREATE TABLE `test`.`test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `status` char(3) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1; 回数=10 実行時間=3.279秒 平均=0.328秒 回数=10 実行時間=3.259秒 平均=0.326秒 回数=10 実行時間=3.287秒 平均=0.329秒
100万件程度ならフラグとして好きなデータ型を利用すればいいのではないでしょうか。
インデックスの考察
それにしても遅いのでインデックスを付けます…
tinyintの場合
DROP TABLE IF EXISTS `test`.`test`; CREATE TABLE `test`.`test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `status` tinyint(4) NOT NULL, PRIMARY KEY (`id`), KEY `Index_2` (`status`) ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1; 回数=10 実行時間=0.101秒 平均=0.010秒 回数=10 実行時間=0.031秒 平均=0.003秒 回数=10 実行時間=0.125秒 平均=0.012秒 explain id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra 1, 'SIMPLE', 'test', 'ref', 'Index_2', 'Index_2', '1', 'const', 10, ''
上記の結果を見ていただくと、indexを使用すれば爆速なのが分かります。
char(1)の場合
DROP TABLE IF EXISTS `test`.`test`; CREATE TABLE `test`.`test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `status` char(1) DEFAULT NULL, PRIMARY KEY (`id`), KEY `Index_2` (`status`) ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1; 回数=10 実行時間=0.021秒 平均=0.002秒 回数=10 実行時間=0.017秒 平均=0.002秒 回数=10 実行時間=0.006秒 平均=0.001秒 explain id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra 1, 'SIMPLE', 'test', 'ref', 'Index_2', 'Index_2', '2', 'const', 9, 'Using index condition'
char(3)の場合
DROP TABLE IF EXISTS `test`.`test`; CREATE TABLE `test`.`test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `status` char(3) DEFAULT NULL, PRIMARY KEY (`id`), KEY `Index_2` (`status`) ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1; 回数=10 実行時間=0.005秒 平均=0.001秒 回数=10 実行時間=0.020秒 平均=0.002秒 回数=10 実行時間=0.011秒 平均=0.001秒 id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra 1, 'SIMPLE', 'test', 'ref', 'Index_2', 'Index_2', '4', 'const', 9, 'Using index condition'
primaryキーの様にユニークなキー(1からの連番など)を使用すると、絞られた分だけ早くなります。
フラグのように0か1のデータであり、対象データが少ない場合はインデックスの効果は絶大です。
複合インデックスについて考える
MySQLでは１回のSELECTで使用するインデックスは１つだけです。
そこで、主キーにフラグも追加して複合キーにしてやりました。
理由は、MySQLでは１回のSELECTで使用されるインデックスは１つだけなので、
このぐらい小さなテーブルなら、キーとして利用される可能性のある列をまとめておけば拡張性があり便利かな？
という愚かな発想からです。
id(連番)とフラグの複合キーの場合
DROP TABLE IF EXISTS `test`.`test`; CREATE TABLE `test`.`test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `status` char(3) NOT NULL DEFAULT '', PRIMARY KEY (`id`,`status`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1; 回数=10 実行時間=3.174秒 平均=0.317秒 回数=10 実行時間=3.177秒 平均=0.318秒 回数=10 実行時間=3.211秒 平均=0.321秒 explain id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra 1, 'SIMPLE', 'test', 'ALL', '', '', '', '', 997372, 'Using where'
上記の結果をみるとお分かりのように、なぜかキーが使用されていません。
遅くなってしまいました。
use index , force index を使用しても結果は同じでした。
では早くなるパターの
KEY `Index_2` (`status`)
に、主キーを追加して
KEY `Index_2` (`id`,`status`)
としたらどうでしょうか。
DROP TABLE IF EXISTS `test`.`test`; CREATE TABLE `test`.`test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `status` char(3) NOT NULL DEFAULT '', PRIMARY KEY (`id`) USING BTREE, KEY `Index_2` (`id`,`status`) ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1; 回数=10 実行時間=3.059秒 平均=0.306秒 回数=10 実行時間=3.120秒 平均=0.312秒 回数=10 実行時間=3.115秒 平均=0.312秒 explain id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra 1, 'SIMPLE', 'test', 'ALL', '', '', '', '', 997372, 'Using where'
上記のようにインデックスにidを追加すると、
利用してくれなくなりました。
これもuse index , force index を使用しても結果は同じです。
そこで、強引にインデックスを利用する方法を思いつきました。
where区に id > 0 を入れるのです。
DROP TABLE IF EXISTS `test`.`test`; CREATE TABLE `test`.`test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `status` char(3) NOT NULL DEFAULT '', PRIMARY KEY (`id`,`status`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1; 回数=10 実行時間=3.618秒 平均=0.362秒 回数=10 実行時間=3.585秒 平均=0.359秒 回数=10 実行時間=3.562秒 平均=0.356秒 explain select id,name,status from test where status='1' and id > 0 id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra 1, 'SIMPLE', 'test', 'range', 'PRIMARY', 'PRIMARY', '4', '', 498686, 'Using where'
上記のように、ついにインデックスを利用してくれましたが、スピードは遅いままです。
補足ですが、and id > 0 の箇所を id > 500000 とすれば、それだけデータが絞られて早くなります。
しかし、それではフラグを条件として利用するという今回の目的からは外れてしまいます。
idは連番でありユニークですが、
フラグは基本的に0か1の値しかない重複データだからです。
・・・
ここまでをまとめると
・フラグを条件として利用する場合もインデックスを利用すべき。
しかし、
・キーもしくは主キーに、フラグの列とid(連番)列が含まれると利用してくれない。
インデックスの使用条件等を調べてはみたものの、答えは見つかりませんでした。
そして休日なので夜まで寝てしまいました…。
そうです、軽い気持ちで始めたMySQLの調査で私の土曜日は終わってしまっていたのです。
～～～～～～～～～～～～
以下私の推論です・・・
自分で何度も記述しているように、
id(主キー)：ユニーク
フラグ：重複あり
です。
これにヒントがあるのではないでしょうか。
主キーは連番なので、絞り込みには利用できます。
フラグは0か1なので、一部のデータが1なら検索は便利です。
つまり、100万件のデータの中で、id > 999999 という条件ならば主キーが便利です。
100万件のデータの中からランダムな10件のstatus=1という条件ならフラグにインデックスを持たせるべきです。
全く種類の違うインデックスなのではないでしょうか。
では、id(連番)で条件を絞りつつフラグ(0か1)の1のものを検索する場合はどうなるのでしょうか。
DROP TABLE IF EXISTS `test`.`test`; CREATE TABLE `test`.`test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `status` char(3) NOT NULL DEFAULT '', PRIMARY KEY (`id`) USING BTREE, KEY `Index_2` (`status`) ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1; //条件がwhere status='1' and id > 0の場合 explain select id,name,status from test where status='1' and id > 0 id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra 1, 'SIMPLE', 'test', 'index_merge', 'PRIMARY,Index_2', 'Index_2,PRIMARY', '7,4', '', 4973, 'Using intersect(Index_2,PRIMARY); Using where'explain select id,name,status from test where status='1' and id > 0 id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra 1, 'SIMPLE', 'test', 'index_merge', 'PRIMARY,Index_2', 'Index_2,PRIMARY', '7,4', '', 4973, 'Using intersect(Index_2,PRIMARY); Using where' 回数=10 実行時間=0.498秒 平均=0.050秒 回数=10 実行時間=0.466秒 平均=0.047秒 回数=10 実行時間=0.290秒 平均=0.029秒 //条件がwhere status='1' and id > 500000 explain select id,name,status from test where status='1' and id > 500000 id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra 1, 'SIMPLE', 'test', 'index_merge', 'PRIMARY,Index_2', 'Index_2,PRIMARY', '7,4', '', 2452, 'Using intersect(Index_2,PRIMARY); Using where' 回数=10 実行時間=0.170秒 平均=0.017秒 回数=10 実行時間=0.117秒 平均=0.012秒 回数=10 実行時間=0.123秒 平均=0.012秒 //条件がwhere status='1' and id > 999000の場合 explain select id,name,status from test where status='1' and id > 999000 id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra 1, 'SIMPLE', 'test', 'index_merge', 'PRIMARY,Index_2', 'Index_2,PRIMARY', '7,4', '', 1, 'Using intersect(Index_2,PRIMARY); Using where' 回数=10 実行時間=0.053秒 平均=0.005秒 回数=10 実行時間=0.028秒 平均=0.003秒 回数=10 実行時間=0.018秒 平均=0.002秒
結果として、’Index_2,PRIMARY’という２つのインデックスが結合された条件が使用されていました。
id(連番)で絞り込んだ上で、ステータスを見るようです。
idでの絞り込みが小さくなればなるほど早くなりました。
PRIMARY KEY (`id`,`status`)
とするより、
PRIMARY KEY (`id`) USING BTREE,
KEY `Index_2` (`status`)
として’Using intersect(Index_2,PRIMARY);
とした方が早いということです。
正確にはPRIMARY KEY (`id`,`status`) では連番の意味しかもたなくなり、
idでの絞り込み結果が多ければ遅く、インデックスにstatusを持たせた意味はないということです。
一方
PRIMARY KEY (`id`) USING BTREE,
KEY `Index_2` (`status`)
と分けた場合は、idでの絞り込み結果が大量に残っても、KEY `Index_2` (`status`)
が効いてインデックスの効果が見込めるということです。
つまり
連番とフラグを一緒にしたキーを作成しても、結局連番の意味での検索にしか利用できないということです。
しかし、連番とフラグを別キーとして設定していれば、MySQLが２つのキーを利用して、つまり、連番としてのキー、フラグとしてのキーとして利用してくれ、高速に検索できるわけです。
では初めからidとステータスを含んだインデックスを作成していればどうなるのでしょうか。
DROP TABLE IF EXISTS `test`.`test`; CREATE TABLE `test`.`test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `status` char(3) NOT NULL DEFAULT '', PRIMARY KEY (`id`) USING BTREE, KEY `Index_2` (`status`), KEY `Index_3` (`id`,`status`) ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1; explain select id,name,status from test where status='1' and id > 999000 id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra 1, 'SIMPLE', 'test', 'index_merge', 'PRIMARY,Index_2,Index_3', 'Index_2,PRIMARY', '7,4', '', 1, 'Using intersect(Index_2,PRIMARY); Using where' 回数=10 実行時間=0.052秒 平均=0.005秒 回数=10 実行時間=0.005秒 平均=0.001秒 回数=10 実行時間=0.005秒 平均=0.001秒
あえて作成したKEY `Index_3` (`id`,`status`)は使用されず、’Using intersect(Index_2,PRIMARY)となりました。
しかし、この場合は下記のように、use index、もしくはforce indexを使用すると利用してくれました。
DROP TABLE IF EXISTS `test`.`test`; CREATE TABLE `test`.`test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `status` char(3) NOT NULL DEFAULT '', PRIMARY KEY (`id`) USING BTREE, KEY `Index_2` (`status`), KEY `Index_3` (`id`,`status`) ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1; explain select id,name,status from test use index(index_3) where status='1' and id > 999000 id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra 1, 'SIMPLE', 'test', 'range', 'Index_3', 'Index_3', '4', '', 999, 'Using index condition' 回数=10 実行時間=0.043秒 平均=0.004秒 回数=10 実行時間=0.004秒 平均=0.000秒 回数=10 実行時間=0.021秒 平均=0.002秒
上記の結果は想定どおりですが、自動でPRIMARYとindex_2を結合してくれるので、index_3を無理に使用するのはやめるべきでしょう。なぜなら、キーが増える分だけindsetではコストがかかると思われるからです。
しかしここで重要な発見がありました。
以前KEY `index_2` (`id`,`status`)
というキーを作成してwhere status=’1′
という条件ではこのindex_2はどうやっても使用してくれませんでした。
しかし、同じように
`index_3` (`id`,`status`)
というキーを作成してwhere status=’1′ and id > 999000
という条件ではこのindex_3を利用することができました。
テスト結果より、
`index_3` (`id`,`status`)
という複合インデックスを利用するには、インデックスに利用されるすべての列が条件に指定されている必要がある！
ということが明確になりました。
ということは、うっかり、条件に利用されそうな列を全て含んだ複合インデックスを作成しても、条件に全ての列をいれなければその複合インデックスは利用できないということです。
これで今までのテストでの疑問が解決しました。
PRIMARY KEY (`id`,`status`)
というキーを作成しても利用できないのは、わたしが
whrer status=’1′
としていたからであり、
whrer status=’1′ and id > xxxx
とすれば利用できたのです。(結果は省略しますが実際にそうでした)
以下まとめ
・(100万件程度の場合)
tinyint(1)、smallint(1)といった数値型。char(1)、char(3)といった文字列で大差はなさそう。
・フラグもインデックスを利用すべき。
・複合インデックスを利用するには、インデックスに含まれる全ての列を条件(=where)に入れる必要がある。
・安易に多数の列を含めた複合インデックスを作成しても利用できない。
最後にもう一つフラグ(del_flg=削除フラグ)を入れて検証してみます。
DROP TABLE IF EXISTS `test`.`test`; CREATE TABLE `test`.`test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `status` char(3) NOT NULL DEFAULT '', `del_flg` char(3) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) USING BTREE, KEY `Index_2` (`id`,`status`), KEY `Index_3` (`del_flg`) ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1; explain select id,name,status from test where status='1' and id > 0 and del_flg='0' id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra 1, 'SIMPLE', 'test', 'ref', 'PRIMARY,Index_2,Index_3', 'Index_3', '3', 'const', 498572, 'Using index condition; Using where' 回数=10 実行時間=0.009秒 平均=0.001秒 回数=10 実行時間=0.024秒 平均=0.002秒 回数=10 実行時間=0.005秒 平均=0.001秒
PRIMARY,Index_2,Index_3という候補のインデックスの中からIndex_3を使用しているようです。
結果として高速ですが、この場合はIndex_2は利用されていません。
DROP TABLE IF EXISTS `test`.`test`; CREATE TABLE `test`.`test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `status` char(3) NOT NULL DEFAULT '', `del_flg` char(3) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) USING BTREE, KEY `Index_2` (`id`,`status`), KEY `Index_3` (`del_flg`), KEY `Index_4` (`status`,`del_flg`) ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1; explain select id,name,status from test where status='1' and id > 0 and del_flg='0' id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra 1, 'SIMPLE', 'test', 'index_merge', 'PRIMARY,Index_2,Index_3,Index_4', 'Index_4,PRIMARY', '10,4', '', 4927, 'Using intersect(Index_4,PRIMARY); Using where' 回数=10 実行時間=0.022秒 平均=0.002秒 回数=10 実行時間=0.023秒 平均=0.002秒 回数=10 実行時間=0.013秒 平均=0.001秒
全てのフラグを含んだKEY `Index_4` (`status`,`del_flg`)を作成すると、’Index_4,PRIMARY’を使用してくれました・・・!?
しかし、そもそもインデックスは１つしか使用しないのではなかったのでしょうか・・・。
実は5.1より’index_merge’なるものが出てきて、複数のインデックスを結合してくれるようです。
浅はかな私の結論としては、複合インデックスで大量の列を入れてしまうと、条件にすべての列を含めないといけなくなります。ですので多数の列の複合インデックスを作るよりは個別のインデックスを作成して’index_merge’した方がSQLの柔軟性は高くなるのではないかと思われました。
