猫型iPS細胞研究所

Windows、Linux、iOS、DB、Network

DataBase MySQL

MySQLでフラグの列でSELECTする。(複合インデックスの使用条件)

投稿日:

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では1回のSELECTで使用するインデックスは1つだけです。
そこで、主キーにフラグも追加して複合キーにしてやりました。
理由は、MySQLでは1回のSELECTで使用されるインデックスは1つだけなので、
このぐらい小さなテーブルなら、キーとして利用される可能性のある列をまとめておけば拡張性があり便利かな?
という愚かな発想からです。

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’という2つのインデックスが結合された条件が使用されていました。
id(連番)で絞り込んだ上で、ステータスを見るようです。
idでの絞り込みが小さくなればなるほど早くなりました。

いままでの結果より、PRIMARYを
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が2つのキーを利用して、つまり、連番としてのキー、フラグとしてのキーとして利用してくれ、高速に検索できるわけです。

では初めから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’を使用してくれました・・・!?
しかし、そもそもインデックスは1つしか使用しないのではなかったのでしょうか・・・。

実は5.1より’index_merge’なるものが出てきて、複数のインデックスを結合してくれるようです。

浅はかな私の結論としては、複合インデックスで大量の列を入れてしまうと、条件にすべての列を含めないといけなくなります。ですので多数の列の複合インデックスを作るよりは個別のインデックスを作成して’index_merge’した方がSQLの柔軟性は高くなるのではないかと思われました。

Gooleアドセンス用336

Gooleアドセンス用336

-DataBase, MySQL

執筆者:

関連記事

select insert

私は以前SQLの順番にinsert selectと呼んでいて、笑われたことがあります。 select insertだ!と当時の上司に言われました。 なるほど、副問い合わせのselectが実行されて、そ …

sqlserver

SQL Server Browser を起動する方法

SQL Server構成マネージャーより起動できるはずが、なぜか開始も停止もできない。 コントロール パネル > システムとセキュリティ > 管理ツール > サービス を見ると、SQL Server …

mysql

ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

ダンプをインポートしようとすると下記エラーが表示されてインポートできませんでした。 [root@centos]# mysql -u root -p reset master; するとインポートできまし …

mysql

utilitiesのmysqlfailoverを使う(2)

前回の utilitiesのmysqlfailoverを使う(1)で設定・起動は完了しています。 私の目的は2台のMySQLサーバーを定期的に入れ替えてメンテナンスすることです。 今回はマスターがダウ …

mysql

MySQLのexists

exists自体はほとんどのSQL自体に実装されていると思われます。 しかし、使用方法を誤るとレスポンスに大きな影響を与えてしまします。 副問合せであるexists内部で、集問い合わせのキー列と結合し …