[mysql] ORDER BY が効かない
mysql を使っていて、条件によって ORDER BY が効かない状況があった。こんな記事 (MySQL - select文でorder by句が正しく効かない場合がある - Qiita)もあったけど今回の事象とは違う感じなので、自分なりに調べたことを備忘。
とりあえず、先に調べたことのまとめ
・InnoDB で、セカンダリインデックスを使用する ORDER BY クラスタインデックス (プライマリーキー) の SQL は正しく動作しないことがある
・ただし、Covering Index であれば正しく挙動する。
・Covering Index を使えない場合 (SELECT * で実行したい場合) は、セカンダリインデックスをプライマリーキーとの複合インデックスにすれば OK
・ただ、InnoDB のセカンダリインデックスはリーフノードにプライマリーキーの値を保持しているので、どうしてこのような挙動になるかは謎
環境
mysql バージョン
mysql> SELECT version(); +-----------+ | version() | +-----------+ | 5.0.67 | +-----------+ 1 row in set (0.00 sec)
テーブル構成
mysql> DESC guitarhythm; +-------------+---------------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------------+------+-----+-------------------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | | valid_flag | tinyint(3) unsigned | NO | MUL | 0 | | | type | tinyint(3) unsigned | NO | MUL | 0 | | | insert_time | datetime | NO | | NULL | | | update_time | timestamp | NO | | CURRENT_TIMESTAMP | | +-------------+---------------------+------+-----+-------------------+----------------+ 6 rows in set (0.00 sec)
インデックス情報
mysql> SHOW INDEX FROM guitarhythm; +-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | guitarhythm | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | guitarhythm | 1 | valid_flag | 1 | valid_flag | A | 2 | NULL | NULL | | BTREE | | | guitarhythm | 1 | type | 1 | type | A | 2 | NULL | NULL | | BTREE | | +-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 3 rows in set (0.00 sec)
レコード内容
mysql> SELECT * FROM guitarhythm WHERE 1; +----+----------+------------+------+---------------------+---------------------+ | id | name | valid_flag | type | insert_time | update_time | +----+----------+------------+------+---------------------+---------------------+ | 1 | tele | 1 | 1 | 2015-01-09 12:06:25 | 2015-01-09 12:27:57 | | 2 | strat | 1 | 3 | 2015-01-09 12:06:26 | 2015-01-09 13:45:24 | | 3 | les | 0 | 7 | 2015-01-09 12:06:27 | 2015-01-09 13:33:45 | | 4 | Mocking | 1 | 2 | 2015-01-09 12:06:28 | 2015-01-09 13:45:24 | | 5 | fire | 0 | 5 | 2015-01-09 12:06:29 | 2015-01-09 13:31:58 | | 6 | zemaitis | 1 | 6 | 2015-01-09 12:06:30 | 2015-01-09 13:31:58 | +----+----------+------------+------+---------------------+---------------------+ 6 rows in set (0.00 sec)
ストレージエンジン
InnoDB を使用。
ORDER BY が効かないクエリを発見
[Case1] id で order。limit なし ... OK
mysql> SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY id DESC; +----+----------+------------+------+---------------------+---------------------+ | id | name | valid_flag | type | insert_time | update_time | +----+----------+------------+------+---------------------+---------------------+ | 6 | zemaitis | 1 | 6 | 2015-01-09 12:06:30 | 2015-01-09 13:31:58 | | 4 | Mocking | 1 | 2 | 2015-01-09 12:06:28 | 2015-01-09 13:45:24 | | 2 | strat | 1 | 3 | 2015-01-09 12:06:26 | 2015-01-09 13:45:24 | | 1 | tele | 1 | 1 | 2015-01-09 12:06:25 | 2015-01-09 12:27:57 | +----+----------+------------+------+---------------------+---------------------+ 4 rows in set (0.00 sec) mysql> explain SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY id DESC; +----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | guitarhythm | index | valid_flag | PRIMARY | 4 | NULL | 5 | Using WHERE | +----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec)
[Case2] id で order。limit 100 ... OK
mysql> SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY id DESC LIMIT 100; +----+----------+------------+------+---------------------+---------------------+ | id | name | valid_flag | type | insert_time | update_time | +----+----------+------------+------+---------------------+---------------------+ | 6 | zemaitis | 1 | 6 | 2015-01-09 12:06:30 | 2015-01-09 13:31:58 | | 4 | Mocking | 1 | 2 | 2015-01-09 12:06:28 | 2015-01-09 13:45:24 | | 2 | strat | 1 | 3 | 2015-01-09 12:06:26 | 2015-01-09 13:45:24 | | 1 | tele | 1 | 1 | 2015-01-09 12:06:25 | 2015-01-09 12:27:57 | +----+----------+------------+------+---------------------+---------------------+ 4 rows in set (0.00 sec) mysql> explain SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY id DESC LIMIT 100; +----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | guitarhythm | index | valid_flag | PRIMARY | 4 | NULL | 5 | Using WHERE | +----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec)
[Case3] id で order。limit 3 ... NG
mysql> SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY id DESC LIMIT 3; +----+---------+------------+------+---------------------+---------------------+ | id | name | valid_flag | type | insert_time | update_time | +----+---------+------------+------+---------------------+---------------------+ | 1 | tele | 1 | 1 | 2015-01-09 12:06:25 | 2015-01-09 12:27:57 | | 2 | strat | 1 | 3 | 2015-01-09 12:06:26 | 2015-01-09 13:45:24 | | 4 | Mocking | 1 | 2 | 2015-01-09 12:06:28 | 2015-01-09 13:45:24 | +----+---------+------------+------+---------------------+---------------------+ 3 rows in set (0.02 sec) mysql> explain SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY id DESC LIMIT 3; +----+-------------+-------------+-------+---------------+------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------+------------+---------+------+------+-------------+ | 1 | SIMPLE | guitarhythm | range | valid_flag | valid_flag | 1 | NULL | 3 | Using WHERE | +----+-------------+-------------+-------+---------------+------------+---------+------+------+-------------+ 1 row in set (0.00 sec)
パターン3 がおかしい！なんで？？？全然検討がつかない。。。
id (primary key) 以外のカラムを指定してソートしてみる
インデックスを設定していないカラム (insert_time)
[Case4] insert_time で order。limit なし ... OK
mysql> SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY insert_time DESC; +----+----------+------------+------+---------------------+---------------------+ | id | name | valid_flag | type | insert_time | update_time | +----+----------+------------+------+---------------------+---------------------+ | 6 | zemaitis | 1 | 6 | 2015-01-09 12:06:30 | 2015-01-09 13:31:58 | | 4 | Mocking | 1 | 2 | 2015-01-09 12:06:28 | 2015-01-09 13:45:24 | | 2 | strat | 1 | 3 | 2015-01-09 12:06:26 | 2015-01-09 13:45:24 | | 1 | tele | 1 | 1 | 2015-01-09 12:06:25 | 2015-01-09 12:27:57 | +----+----------+------------+------+---------------------+---------------------+ 4 rows in set (0.01 sec) mysql> explain SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY insert_time DESC; +----+-------------+-------------+------+---------------+------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------+---------+------+------+-----------------------------+ | 1 | SIMPLE | guitarhythm | ALL | valid_flag | NULL | NULL | NULL | 5 | Using WHERE; Using filesort | +----+-------------+-------------+------+---------------+------+---------+------+------+-----------------------------+ 1 row in set (0.00 sec)
[Case5] insert_time で order。limit 100 ... OK
mysql> SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY insert_time DESC LIMIT 100; +----+----------+------------+------+---------------------+---------------------+ | id | name | valid_flag | type | insert_time | update_time | +----+----------+------------+------+---------------------+---------------------+ | 6 | zemaitis | 1 | 6 | 2015-01-09 12:06:30 | 2015-01-09 13:31:58 | | 4 | Mocking | 1 | 2 | 2015-01-09 12:06:28 | 2015-01-09 13:45:24 | | 2 | strat | 1 | 3 | 2015-01-09 12:06:26 | 2015-01-09 13:45:24 | | 1 | tele | 1 | 1 | 2015-01-09 12:06:25 | 2015-01-09 12:27:57 | +----+----------+------------+------+---------------------+---------------------+ 4 rows in set (0.00 sec) mysql> explain SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY insert_time DESC LIMIT 100; +----+-------------+-------------+------+---------------+------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------+---------+------+------+-----------------------------+ | 1 | SIMPLE | guitarhythm | ALL | valid_flag | NULL | NULL | NULL | 5 | Using WHERE; Using filesort | +----+-------------+-------------+------+---------------+------+---------+------+------+-----------------------------+ 1 row in set (0.00 sec)
[Case6] insert_time で order。limit 3 ... OK
mysql> SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY insert_time DESC LIMIT 3; +----+----------+------------+------+---------------------+---------------------+ | id | name | valid_flag | type | insert_time | update_time | +----+----------+------------+------+---------------------+---------------------+ | 6 | zemaitis | 1 | 6 | 2015-01-09 12:06:30 | 2015-01-09 13:31:58 | | 4 | Mocking | 1 | 2 | 2015-01-09 12:06:28 | 2015-01-09 13:45:24 | | 2 | strat | 1 | 3 | 2015-01-09 12:06:26 | 2015-01-09 13:45:24 | +----+----------+------------+------+---------------------+---------------------+ 3 rows in set (0.00 sec) mysql> explain SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY insert_time DESC LIMIT 3; +----+-------------+-------------+-------+---------------+------------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------+------------+---------+------+------+-----------------------------+ | 1 | SIMPLE | guitarhythm | range | valid_flag | valid_flag | 1 | NULL | 3 | Using WHERE; Using filesort | +----+-------------+-------------+-------+---------------+------------+---------+------+------+-----------------------------+ 1 row in set (0.00 sec)
これは、全部期待どおりの動作。
セカンダリインデックスのカラム (type)
[Case7] type で order。limit なし ... OK
mysql> SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY type DESC; +----+----------+------------+------+---------------------+---------------------+ | id | name | valid_flag | type | insert_time | update_time | +----+----------+------------+------+---------------------+---------------------+ | 6 | zemaitis | 1 | 6 | 2015-01-09 12:06:30 | 2015-01-09 13:31:58 | | 2 | strat | 1 | 3 | 2015-01-09 12:06:26 | 2015-01-09 13:45:24 | | 4 | Mocking | 1 | 2 | 2015-01-09 12:06:28 | 2015-01-09 13:45:24 | | 1 | tele | 1 | 1 | 2015-01-09 12:06:25 | 2015-01-09 12:27:57 | +----+----------+------------+------+---------------------+---------------------+ 4 rows in set (0.00 sec) mysql> explain SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY type DESC; +----+-------------+-------------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | guitarhythm | index | valid_flag | type | 1 | NULL | 5 | Using WHERE | +----+-------------+-------------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
[Case8] type で order。limit 100 ... OK
mysql> SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY type DESC LIMIT 100; +----+----------+------------+------+---------------------+---------------------+ | id | name | valid_flag | type | insert_time | update_time | +----+----------+------------+------+---------------------+---------------------+ | 6 | zemaitis | 1 | 6 | 2015-01-09 12:06:30 | 2015-01-09 13:31:58 | | 2 | strat | 1 | 3 | 2015-01-09 12:06:26 | 2015-01-09 13:45:24 | | 4 | Mocking | 1 | 2 | 2015-01-09 12:06:28 | 2015-01-09 13:45:24 | | 1 | tele | 1 | 1 | 2015-01-09 12:06:25 | 2015-01-09 12:27:57 | +----+----------+------------+------+---------------------+---------------------+ 4 rows in set (0.00 sec) mysql> explain SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY type DESC LIMIT 100; +----+-------------+-------------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | guitarhythm | index | valid_flag | type | 1 | NULL | 5 | Using WHERE | +----+-------------+-------------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
[Case9] type で order。limit 3 ... OK
mysql> SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY type DESC LIMIT 3; +----+----------+------------+------+---------------------+---------------------+ | id | name | valid_flag | type | insert_time | update_time | +----+----------+------------+------+---------------------+---------------------+ | 6 | zemaitis | 1 | 6 | 2015-01-09 12:06:30 | 2015-01-09 13:31:58 | | 2 | strat | 1 | 3 | 2015-01-09 12:06:26 | 2015-01-09 13:45:24 | | 4 | Mocking | 1 | 2 | 2015-01-09 12:06:28 | 2015-01-09 13:45:24 | +----+----------+------------+------+---------------------+---------------------+ 3 rows in set (0.00 sec) mysql> explain SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY type DESC LIMIT 3; +----+-------------+-------------+-------+---------------+------------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------+------------+---------+------+------+-----------------------------+ | 1 | SIMPLE | guitarhythm | range | valid_flag | valid_flag | 1 | NULL | 3 | Using WHERE; Using filesort | +----+-------------+-------------+-------+---------------+------------+---------+------+------+-----------------------------+ 1 row in set (0.00 sec)
この場合も全部期待どおりの動作。てことは、プライマリーキーで ORDER BY したときに変な動きになることがあるってこと？？どういう条件でこうなるんだろう・・・・
件数ちょっと増やしてみた（6件→10件）
変更後のレコード内容
mysql> SELECT * FROM guitarhythm WHERE 1; +----+----------+------------+------+---------------------+---------------------+ | id | name | valid_flag | type | insert_time | update_time | +----+----------+------------+------+---------------------+---------------------+ | 1 | tele | 1 | 1 | 2015-01-09 12:06:25 | 2015-01-09 12:27:57 | | 2 | strat | 1 | 2 | 2015-01-09 12:06:26 | 2015-01-09 13:31:58 | | 3 | les | 0 | 7 | 2015-01-09 12:06:27 | 2015-01-09 13:33:45 | | 4 | Mocking | 1 | 2 | 2015-01-09 12:06:28 | 2015-01-09 13:33:45 | | 5 | fire | 0 | 5 | 2015-01-09 12:06:29 | 2015-01-09 13:31:58 | | 6 | zemaitis | 1 | 6 | 2015-01-09 12:06:30 | 2015-01-09 13:31:58 | | 7 | hotei | 1 | 7 | 2015-01-09 12:06:31 | 2015-01-09 13:31:58 | | 8 | hide | 0 | 9 | 2015-01-09 12:06:32 | 2015-01-09 13:33:45 | | 9 | himuro | 0 | 1 | 2015-01-09 12:06:33 | 2015-01-09 13:33:45 | | 10 | ore | 0 | 3 | 2015-01-09 12:06:34 | 2015-01-09 13:33:56 | +----+----------+------------+------+---------------------+---------------------+ 10 rows in set (0.00 sec)
この状態で、上記 9 パターンを全て試してみる
[Case1] id で order。limit なし ... OK
mysql> SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY id DESC; +----+----------+------------+------+---------------------+---------------------+ | id | name | valid_flag | type | insert_time | update_time | +----+----------+------------+------+---------------------+---------------------+ | 7 | hotei | 1 | 7 | 2015-01-09 12:06:31 | 2015-01-09 13:31:58 | | 6 | zemaitis | 1 | 6 | 2015-01-09 12:06:30 | 2015-01-09 13:31:58 | | 4 | Mocking | 1 | 2 | 2015-01-09 12:06:28 | 2015-01-09 13:33:45 | | 2 | strat | 1 | 2 | 2015-01-09 12:06:26 | 2015-01-09 13:31:58 | | 1 | tele | 1 | 1 | 2015-01-09 12:06:25 | 2015-01-09 12:27:57 | +----+----------+------------+------+---------------------+---------------------+ 5 rows in set (0.00 sec) mysql> explain SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY id DESC; +----+-------------+-------------+------+---------------+------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------------+---------+-------+------+-------------+ | 1 | SIMPLE | guitarhythm | ref | valid_flag | valid_flag | 1 | const | 5 | Using WHERE | +----+-------------+-------------+------+---------------+------------+---------+-------+------+-------------+ 1 row in set (0.00 sec)
[Case2] id で order。limit 100 ... OK
mysql> SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY id DESC LIMIT 100; +----+----------+------------+------+---------------------+---------------------+ | id | name | valid_flag | type | insert_time | update_time | +----+----------+------------+------+---------------------+---------------------+ | 7 | hotei | 1 | 7 | 2015-01-09 12:06:31 | 2015-01-09 13:31:58 | | 6 | zemaitis | 1 | 6 | 2015-01-09 12:06:30 | 2015-01-09 13:31:58 | | 4 | Mocking | 1 | 2 | 2015-01-09 12:06:28 | 2015-01-09 13:33:45 | | 2 | strat | 1 | 2 | 2015-01-09 12:06:26 | 2015-01-09 13:31:58 | | 1 | tele | 1 | 1 | 2015-01-09 12:06:25 | 2015-01-09 12:27:57 | +----+----------+------------+------+---------------------+---------------------+ 5 rows in set (0.00 sec) mysql> explain SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY id DESC LIMIT 100; +----+-------------+-------------+------+---------------+------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------------+---------+-------+------+-------------+ | 1 | SIMPLE | guitarhythm | ref | valid_flag | valid_flag | 1 | const | 5 | Using WHERE | +----+-------------+-------------+------+---------------+------------+---------+-------+------+-------------+ 1 row in set (0.00 sec)
[Case3] id で order。limit 3 ... OK
mysql> SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY id DESC LIMIT 3; +----+----------+------------+------+---------------------+---------------------+ | id | name | valid_flag | type | insert_time | update_time | +----+----------+------------+------+---------------------+---------------------+ | 7 | hotei | 1 | 7 | 2015-01-09 12:06:31 | 2015-01-09 13:31:58 | | 6 | zemaitis | 1 | 6 | 2015-01-09 12:06:30 | 2015-01-09 13:31:58 | | 4 | Mocking | 1 | 2 | 2015-01-09 12:06:28 | 2015-01-09 13:33:45 | +----+----------+------------+------+---------------------+---------------------+ 3 rows in set (0.00 sec) mysql> explain SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY id DESC LIMIT 3; +----+-------------+-------------+------+---------------+------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------------+---------+-------+------+-------------+ | 1 | SIMPLE | guitarhythm | ref | valid_flag | valid_flag | 1 | const | 5 | Using WHERE | +----+-------------+-------------+------+---------------+------------+---------+-------+------+-------------+ 1 row in set (0.00 sec)
[Case4] insert_time で order。limit なし ... OK
mysql> SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY insert_time DESC; +----+----------+------------+------+---------------------+---------------------+ | id | name | valid_flag | type | insert_time | update_time | +----+----------+------------+------+---------------------+---------------------+ | 7 | hotei | 1 | 7 | 2015-01-09 12:06:31 | 2015-01-09 13:31:58 | | 6 | zemaitis | 1 | 6 | 2015-01-09 12:06:30 | 2015-01-09 13:31:58 | | 4 | Mocking | 1 | 2 | 2015-01-09 12:06:28 | 2015-01-09 13:33:45 | | 2 | strat | 1 | 2 | 2015-01-09 12:06:26 | 2015-01-09 13:31:58 | | 1 | tele | 1 | 1 | 2015-01-09 12:06:25 | 2015-01-09 12:27:57 | +----+----------+------------+------+---------------------+---------------------+ 5 rows in set (0.00 sec) mysql> explain SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY insert_time DESC; +----+-------------+-------------+------+---------------+------------+---------+-------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------------+---------+-------+------+-----------------------------+ | 1 | SIMPLE | guitarhythm | ref | valid_flag | valid_flag | 1 | const | 5 | Using WHERE; Using filesort | +----+-------------+-------------+------+---------------+------------+---------+-------+------+-----------------------------+ 1 row in set (0.00 sec)
[Case5] insert_time で order。limit 100 ... OK
mysql> SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY insert_time DESC LIMIT 100; +----+----------+------------+------+---------------------+---------------------+ | id | name | valid_flag | type | insert_time | update_time | +----+----------+------------+------+---------------------+---------------------+ | 7 | hotei | 1 | 7 | 2015-01-09 12:06:31 | 2015-01-09 13:31:58 | | 6 | zemaitis | 1 | 6 | 2015-01-09 12:06:30 | 2015-01-09 13:31:58 | | 4 | Mocking | 1 | 2 | 2015-01-09 12:06:28 | 2015-01-09 13:33:45 | | 2 | strat | 1 | 2 | 2015-01-09 12:06:26 | 2015-01-09 13:31:58 | | 1 | tele | 1 | 1 | 2015-01-09 12:06:25 | 2015-01-09 12:27:57 | +----+----------+------------+------+---------------------+---------------------+ 5 rows in set (0.01 sec) mysql> explain SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY insert_time DESC LIMIT 100; +----+-------------+-------------+------+---------------+------------+---------+-------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------------+---------+-------+------+-----------------------------+ | 1 | SIMPLE | guitarhythm | ref | valid_flag | valid_flag | 1 | const | 5 | Using WHERE; Using filesort | +----+-------------+-------------+------+---------------+------------+---------+-------+------+-----------------------------+ 1 row in set (0.00 sec)
[Case6] insert_time で order。limit 3 ... OK
mysql> SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY insert_time DESC LIMIT 3; +----+----------+------------+------+---------------------+---------------------+ | id | name | valid_flag | type | insert_time | update_time | +----+----------+------------+------+---------------------+---------------------+ | 7 | hotei | 1 | 7 | 2015-01-09 12:06:31 | 2015-01-09 13:31:58 | | 6 | zemaitis | 1 | 6 | 2015-01-09 12:06:30 | 2015-01-09 13:31:58 | | 4 | Mocking | 1 | 2 | 2015-01-09 12:06:28 | 2015-01-09 13:33:45 | +----+----------+------------+------+---------------------+---------------------+ 3 rows in set (0.00 sec) mysql> explain SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY insert_time DESC LIMIT 3; +----+-------------+-------------+------+---------------+------------+---------+-------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------------+---------+-------+------+-----------------------------+ | 1 | SIMPLE | guitarhythm | ref | valid_flag | valid_flag | 1 | const | 5 | Using WHERE; Using filesort | +----+-------------+-------------+------+---------------+------------+---------+-------+------+-----------------------------+ 1 row in set (0.00 sec)
[Case7] type で order。limit なし ... OK
mysql> SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY type DESC; +----+----------+------------+------+---------------------+---------------------+ | id | name | valid_flag | type | insert_time | update_time | +----+----------+------------+------+---------------------+---------------------+ | 7 | hotei | 1 | 7 | 2015-01-09 12:06:31 | 2015-01-09 13:31:58 | | 6 | zemaitis | 1 | 6 | 2015-01-09 12:06:30 | 2015-01-09 13:31:58 | | 2 | strat | 1 | 2 | 2015-01-09 12:06:26 | 2015-01-09 13:31:58 | | 4 | Mocking | 1 | 2 | 2015-01-09 12:06:28 | 2015-01-09 13:33:45 | | 1 | tele | 1 | 1 | 2015-01-09 12:06:25 | 2015-01-09 12:27:57 | +----+----------+------------+------+---------------------+---------------------+ 5 rows in set (0.00 sec) mysql> explain SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY type DESC; +----+-------------+-------------+------+---------------+------------+---------+-------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------------+---------+-------+------+-----------------------------+ | 1 | SIMPLE | guitarhythm | ref | valid_flag | valid_flag | 1 | const | 5 | Using WHERE; Using filesort | +----+-------------+-------------+------+---------------+------------+---------+-------+------+-----------------------------+ 1 row in set (0.00 sec)
[Case8] type で order。limit 100 ... OK
mysql> SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY type DESC LIMIT 100; +----+----------+------------+------+---------------------+---------------------+ | id | name | valid_flag | type | insert_time | update_time | +----+----------+------------+------+---------------------+---------------------+ | 7 | hotei | 1 | 7 | 2015-01-09 12:06:31 | 2015-01-09 13:31:58 | | 6 | zemaitis | 1 | 6 | 2015-01-09 12:06:30 | 2015-01-09 13:31:58 | | 2 | strat | 1 | 2 | 2015-01-09 12:06:26 | 2015-01-09 13:31:58 | | 4 | Mocking | 1 | 2 | 2015-01-09 12:06:28 | 2015-01-09 13:33:45 | | 1 | tele | 1 | 1 | 2015-01-09 12:06:25 | 2015-01-09 12:27:57 | +----+----------+------------+------+---------------------+---------------------+ 5 rows in set (0.00 sec) mysql> explain SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY type DESC LIMIT 100; +----+-------------+-------------+------+---------------+------------+---------+-------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------------+---------+-------+------+-----------------------------+ | 1 | SIMPLE | guitarhythm | ref | valid_flag | valid_flag | 1 | const | 5 | Using WHERE; Using filesort | +----+-------------+-------------+------+---------------+------------+---------+-------+------+-----------------------------+ 1 row in set (0.00 sec)
[Case9] type で order。limit 3 ... OK
mysql> SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY type DESC LIMIT 3; +----+----------+------------+------+---------------------+---------------------+ | id | name | valid_flag | type | insert_time | update_time | +----+----------+------------+------+---------------------+---------------------+ | 7 | hotei | 1 | 7 | 2015-01-09 12:06:31 | 2015-01-09 13:31:58 | | 6 | zemaitis | 1 | 6 | 2015-01-09 12:06:30 | 2015-01-09 13:31:58 | | 2 | strat | 1 | 2 | 2015-01-09 12:06:26 | 2015-01-09 13:31:58 | +----+----------+------------+------+---------------------+---------------------+ 3 rows in set (0.00 sec) mysql> explain SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY type DESC LIMIT 3; +----+-------------+-------------+------+---------------+------------+---------+-------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------------+---------+-------+------+-----------------------------+ | 1 | SIMPLE | guitarhythm | ref | valid_flag | valid_flag | 1 | const | 5 | Using WHERE; Using filesort | +----+-------------+-------------+------+---------------+------------+---------+-------+------+-----------------------------+ 1 row in set (0.00 sec)
全部期待通りの動き。とりあえずこれらの結果から、「プライマリーキーでソートするとき、ある条件を満たすと ORDER BY が正しく動かない」だろうという仮説を立てる。
改めてレコードを増やす前と後の結果に注目してみる
見るのは、結果に差が出た [Case3] のもの。
レコード数が6のときの結果 ... NG
mysql> SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY id DESC LIMIT 3; +----+---------+------------+------+---------------------+---------------------+ | id | name | valid_flag | type | insert_time | update_time | +----+---------+------------+------+---------------------+---------------------+ | 1 | tele | 1 | 1 | 2015-01-09 12:06:25 | 2015-01-09 12:27:57 | | 2 | strat | 1 | 3 | 2015-01-09 12:06:26 | 2015-01-09 13:45:24 | | 4 | Mocking | 1 | 2 | 2015-01-09 12:06:28 | 2015-01-09 13:45:24 | +----+---------+------------+------+---------------------+---------------------+ 3 rows in set (0.02 sec) mysql> explain SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY id DESC LIMIT 3; +----+-------------+-------------+-------+---------------+------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------+------------+---------+------+------+-------------+ | 1 | SIMPLE | guitarhythm | range | valid_flag | valid_flag | 1 | NULL | 3 | Using WHERE | +----+-------------+-------------+-------+---------------+------------+---------+------+------+-------------+ 1 row in set (0.00 sec)
レコード数が 10 のときの結果 ... OK
mysql> SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY id DESC LIMIT 3; +----+----------+------------+------+---------------------+---------------------+ | id | name | valid_flag | type | insert_time | update_time | +----+----------+------------+------+---------------------+---------------------+ | 7 | hotei | 1 | 7 | 2015-01-09 12:06:31 | 2015-01-09 13:31:58 | | 6 | zemaitis | 1 | 6 | 2015-01-09 12:06:30 | 2015-01-09 13:31:58 | | 4 | Mocking | 1 | 2 | 2015-01-09 12:06:28 | 2015-01-09 13:33:45 | +----+----------+------------+------+---------------------+---------------------+ 3 rows in set (0.00 sec) mysql> explain SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY id DESC LIMIT 3; +----+-------------+-------------+------+---------------+------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------------+---------+-------+------+-------------+ | 1 | SIMPLE | guitarhythm | ref | valid_flag | valid_flag | 1 | const | 5 | Using WHERE | +----+-------------+-------------+------+---------------+------------+---------+-------+------+-------------+ 1 row in set (0.00 sec)
EXPLAIN を見ると、同じインデックスキーが使われてるけど、typeが違う。うまくいかないときは range になっている。ん？そもそも、なんで type が range なんだろう。range は範囲指定のはず。
参考:漢(オトコ)のコンピュータ道: MySQLのEXPLAINを徹底解説!!
いや、そうは言ってもユニークインデックスではない限り、オプティマイザが range でスキャンする可能性はあるのか。
参考:INDEX RANGE SCAN とは？ 【OKWave】
今回の原因とは関係なさそう。
ためしに、カラムを指定して SELECT してみる
[Case3] でうまくいかなかった 6 件の状態に戻す。
mysql> SELECT * FROM guitarhythm WHERE 1; +----+----------+------------+------+---------------------+---------------------+ | id | name | valid_flag | type | insert_time | update_time | +----+----------+------------+------+---------------------+---------------------+ | 1 | tele | 1 | 1 | 2015-01-09 12:06:25 | 2015-01-09 12:27:57 | | 2 | strat | 1 | 3 | 2015-01-09 12:06:26 | 2015-01-09 13:45:24 | | 3 | les | 0 | 7 | 2015-01-09 12:06:27 | 2015-01-09 13:33:45 | | 4 | Mocking | 1 | 2 | 2015-01-09 12:06:28 | 2015-01-09 13:45:24 | | 5 | fire | 0 | 5 | 2015-01-09 12:06:29 | 2015-01-09 13:31:58 | | 6 | zemaitis | 1 | 6 | 2015-01-09 12:06:30 | 2015-01-09 13:31:58 | +----+----------+------------+------+---------------------+---------------------+ 6 rows in set (0.00 sec)
この状態で、SELECT するカラムを指定して [Case3] の条件でクエリを発行してみる。
プライマリーキーの id で検索 ... OK
mysql> SELECT id FROM guitarhythm WHERE valid_flag = 1 ORDER BY id DESC LIMIT 3; +----+ | id | +----+ | 6 | | 4 | | 2 | +----+ 3 rows in set (0.00 sec) mysql> EXPLAIN SELECT id FROM guitarhythm WHERE valid_flag = 1 ORDER BY id DESC LIMIT 3; +----+-------------+-------------+------+---------------+------------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------------+---------+-------+------+--------------------------+ | 1 | SIMPLE | guitarhythm | ref | valid_flag | valid_flag | 1 | const | 3 | Using where; Using index | +----+-------------+-------------+------+---------------+------------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec)
期待通りの動作。valid_flag インデックスを使っていて、Extra が Using index になっているところをみると、Covering Index が使われていることが分かる。これは、クエリがインデックスだけを用いて解決できていることを示している(Covering Index について: http://nippondanji.blogspot.jp/2010/10/innodb.html)
セカンダリインデックスの valid_flag で検索 (ソートが分かるように id もくっつける) ... OK
mysql> SELECT valid_flag, id FROM guitarhythm WHERE valid_flag = 1 ORDER BY id DESC LIMIT 3; +------------+----+ | valid_flag | id | +------------+----+ | 1 | 6 | | 1 | 4 | | 1 | 2 | +------------+----+ 3 rows in set (0.00 sec) mysql> EXPLAIN SELECT valid_flag, id FROM guitarhythm WHERE valid_flag = 1 ORDER BY id DESC LIMIT 3; +----+-------------+-------------+------+---------------+------------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------------+---------+-------+------+--------------------------+ | 1 | SIMPLE | guitarhythm | ref | valid_flag | valid_flag | 1 | const | 3 | Using where; Using index | +----+-------------+-------------+------+---------------+------------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec)
期待通りの動作。こちらも Covering Index が使われていることが分かる。
セカンダリインデックスの type で検索 (ソートが分かるように id もくっつける) ... NG
mysql> SELECT type, id FROM guitarhythm WHERE valid_flag = 1 ORDER BY id DESC LIMIT 3; +------+----+ | type | id | +------+----+ | 1 | 1 | | 2 | 2 | | 2 | 4 | +------+----+ 3 rows in set (0.00 sec) mysql> EXPLAIN SELECT type, id FROM guitarhythm WHERE valid_flag = 1 ORDER BY id DESC LIMIT 3; +----+-------------+-------------+-------+---------------+------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------+------------+---------+------+------+-------------+ | 1 | SIMPLE | guitarhythm | range | valid_flag | valid_flag | 1 | NULL | 3 | Using where | +----+-------------+-------------+-------+---------------+------------+---------+------+------+-------------+ 1 row in set (0.00 sec)
期待していない動作。
インデックスを貼っていない insert_time で検索 (ソートが分かるように id もくっつける) ... NG
mysql> SELECT insert_time, id FROM guitarhythm WHERE valid_flag = 1 ORDER BY id DESC LIMIT 3; +---------------------+----+ | insert_time | id | +---------------------+----+ | 2015-01-09 12:06:25 | 1 | | 2015-01-09 12:06:26 | 2 | | 2015-01-09 12:06:28 | 4 | +---------------------+----+ 3 rows in set (0.00 sec) mysql> EXPLAIN SELECT insert_time, id FROM guitarhythm WHERE valid_flag = 1 ORDER BY id DESC LIMIT 3; +----+-------------+-------------+-------+---------------+------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------+------------+---------+------+------+-------------+ | 1 | SIMPLE | guitarhythm | range | valid_flag | valid_flag | 1 | NULL | 3 | Using where | +----+-------------+-------------+-------+---------------+------------+---------+------+------+-------------+ 1 row in set (0.01 sec)
こちらも期待していない動作。うむむむ。見た感じ、Covering Index が使用されていないときにエラーになっているみたい。
なんとなく valid_flag の単独インデックスを、valid_flag と id の複合インデックスにしてみた
こんな感じに設定。
mysql> SHOW INDEX FROM guitarhythm; +-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | guitarhythm | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | guitarhythm | 1 | type | 1 | type | A | 2 | NULL | NULL | | BTREE | | | guitarhythm | 1 | valid_flag | 1 | valid_flag | A | 2 | NULL | NULL | | BTREE | | | guitarhythm | 1 | valid_flag | 2 | id | A | 2 | NULL | NULL | | BTREE | | +-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 4 rows in set (0.00 sec)
レコードが6件の状態で、[Case3] のクエリを実行 ... OK
mysql> SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY id DESC LIMIT 3; +----+----------+------------+------+---------------------+---------------------+ | id | name | valid_flag | type | insert_time | update_time | +----+----------+------------+------+---------------------+---------------------+ | 6 | zemaitis | 1 | 6 | 2015-01-09 12:06:30 | 2015-01-09 13:31:58 | | 4 | Mocking | 1 | 2 | 2015-01-09 12:06:28 | 2015-01-09 13:33:45 | | 2 | strat | 1 | 2 | 2015-01-09 12:06:26 | 2015-01-09 13:31:58 | +----+----------+------------+------+---------------------+---------------------+ 3 rows in set (0.00 sec) mysql> explain SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY id DESC LIMIT 3; +----+-------------+-------------+-------+---------------+------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------+------------+---------+------+------+-------------+ | 1 | SIMPLE | guitarhythm | range | valid_flag | valid_flag | 1 | NULL | 3 | Using WHERE | +----+-------------+-------------+-------+---------------+------------+---------+------+------+-------------+ 1 row in set (0.00 sec)
想定どおりの動作。なんでだろう。しかも、EXPLAIN 結果が、単独インデックスで正しく動かなかったときとまったく同じ・・・InnoDB のセカンダリインデックスのリーフノードにはプライマリーキーの値が保持されているから、valid_flag のインデックスと valid_flag, id の複合インデックスは同じで、動作もまったく同じという認識だったんだけど・・・
参考:
漢(オトコ)のコンピュータ道: 知って得するInnoDBセカンダリインデックス活用術！
でも、実際に返ってきている結果が変わっているということは、何かしら違いがあるということか。よく分からないけど、とりあえずインデックスが今回の事象を紐解くヒントがあるのでは？？というあたりはなんとなく付いてきた。
InnoDB とはインデックスの仕様が異なる MyISAM を試してみる
ストレージエンジンを MyISAM に変更し、InnoDB でうまくいかなった状態（件数は 6 件で、valid_flag は単独インデックス）にし、[Case3] をやってみる ... OK
mysql> SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY id DESC LIMIT 3; +----+----------+------------+------+---------------------+---------------------+ | id | name | valid_flag | type | insert_time | update_time | +----+----------+------------+------+---------------------+---------------------+ | 6 | zemaitis | 1 | 6 | 2015-01-09 12:06:30 | 2015-01-09 13:31:58 | | 4 | Mocking | 1 | 2 | 2015-01-09 12:06:28 | 2015-01-09 13:33:45 | | 2 | strat | 1 | 2 | 2015-01-09 12:06:26 | 2015-01-09 13:31:58 | +----+----------+------------+------+---------------------+---------------------+ 3 rows in set (0.00 sec) mysql> explain SELECT * FROM guitarhythm WHERE valid_flag = 1 ORDER BY id DESC LIMIT 3; +----+-------------+-------------+------+---------------+------------+---------+-------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------------+---------+-------+------+-----------------------------+ | 1 | SIMPLE | guitarhythm | ref | valid_flag | valid_flag | 1 | const | 3 | Using WHERE; Using filesort | +----+-------------+-------------+------+---------------+------------+---------+-------+------+-----------------------------+ 1 row in set (0.00 sec)
期待どおりの動作！ということは、やっぱり InnoDB のセカンダリインデックス周りに原因があるみたい。
