MySQL メモ11 複合index
複合indexのメモ。よくありがちな開始日付と終了日付を持つテーブルを範囲検索する場合に、indexを貼る。
下記SQLでテーブルを作成。
CREATE TABLE schedule ( id int(11) NOT NULL, start_date datetime NOT NULL, end_date datetime NOT NULL, PRIMARY KEY (id) )
とりあえず60件のデータを登録。
id start_date end_date 1 2014/01/01 2014/04/01 2 2014/02/09 2014/05/09 3 2014/03/20 2014/06/16 4 2014/04/28 2014/07/24 5 2014/06/06 2014/08/31 6 2014/07/15 2014/10/08 7 2014/08/23 2014/11/15 8 2014/10/01 2014/12/23 9 2014/11/09 2015/01/30 10 2014/12/18 2015/03/09 11 2015/01/26 2015/04/16 12 2015/03/06 2015/05/24 13 2015/04/14 2015/07/01 14 2015/05/23 2015/08/08 15 2015/07/01 2015/09/15 16 2015/08/09 2015/10/23 17 2015/09/17 2015/11/30 18 2015/10/26 2016/01/07 19 2015/12/04 2016/02/14 20 2016/01/12 2016/03/23 21 2016/02/20 2016/04/30 22 2016/03/30 2016/06/07 23 2016/05/08 2016/07/15 24 2016/06/16 2016/08/22 25 2016/07/25 2016/09/29 26 2016/09/02 2016/11/06 27 2016/10/11 2016/12/14 28 2016/11/19 2017/01/21 29 2016/12/28 2017/02/28 30 2017/02/05 2017/04/07 31 2017/03/16 2017/05/15 32 2017/04/24 2017/06/22 33 2017/06/02 2017/07/30 34 2017/07/11 2017/09/06 35 2017/08/19 2017/10/14 36 2017/09/27 2017/11/21 37 2017/11/05 2017/12/29 38 2017/12/14 2018/02/05 39 2018/01/22 2018/03/15 40 2018/03/02 2018/04/22 41 2018/04/10 2018/05/30 42 2018/05/19 2018/07/07 43 2018/06/27 2018/08/14 44 2018/08/05 2018/09/21 45 2018/09/13 2018/10/29 46 2018/10/22 2018/12/06 47 2018/11/30 2019/01/13 48 2019/01/08 2019/02/20 49 2019/02/16 2019/03/30 50 2019/03/27 2019/05/07 51 2019/05/05 2019/06/14 52 2019/06/13 2019/07/22 53 2019/07/22 2019/08/29 54 2019/08/30 2019/10/06 55 2019/10/08 2019/11/13 56 2019/11/16 2019/12/21 57 2019/12/25 2020/01/28 58 2020/02/02 2020/03/06 59 2020/03/12 2020/04/13 60 2020/04/20 2020/05/21
indexを貼る前に範囲検索を実行。
mysql> explain select * from schedule where start_date >= '2018/3/1' AND end_date <= '2018/10/1'; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | schedule | NULL | ALL | NULL | NULL | NULL | NULL | 60 | 11.11 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
typeカラムが"ALL"になっているため、フルテーブルスキャンになっていることがわかる。
start_dateとend_dateの複合indexを貼る。
mysql> ALTER TABLE schedule ADD INDEX index_start_end(start_date, end_date); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
複合indexを貼った後に再度範囲検索の実行計画を実行。
mysql> explain select * from schedule where start_date >= '2018/3/1' AND end_date <= '2018/10/1'; +----+-------------+----------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | schedule | NULL | range | index_start_end | index_start_end | 5 | NULL | 21 | 33.33 | Using where; Using index | +----+-------------+----------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
typeカラムが"range"になっているため、インデックスを用いた範囲検索になっていることがわかる。
webシステムの一覧画面で検索条件に"開始日付フォーム"と"終了日付フォーム"があって、入力されている場合検索条件に追加して、一覧の絞り込むを行う。
みたいな場合を想定。
①開始日付と終了日付が入力されている場合。 ※冒頭のSQLと同じパターン
開始日付：2018/12/1
終了日付：2019/4/1
mysql> explain select * from schedule where start_date >= '2018/12/1' AND end_date <= '2019/4/1'; +----+-------------+----------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | schedule | NULL | range | index_start_end | index_start_end | 5 | NULL | 13 | 33.33 | Using where; Using index | +----+-------------+----------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
②開始日のみ入力されている場合。
開始日付：2018/12/1
終了日付：null
mysql> explain select * from schedule where start_date >= '2018/12/1'; +----+-------------+----------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | schedule | NULL | range | index_start_end | index_start_end | 5 | NULL | 13 | 100.00 | Using where; Using index | +----+-------------+----------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
この場合もtypeカラムは"range"になっているので、indexが使われいる。
③終了日のみ入力されている場合。
開始日付：null
終了日付：2018/12/1
mysql> explain select * from schedule where end_date <= '2018/12/1'; +----+-------------+----------+------------+-------+---------------+-----------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+-----------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | schedule | NULL | index | NULL | index_start_end | 10 | NULL | 60 | 33.33 | Using where; Using index | +----+-------------+----------+------------+-------+---------------+-----------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
typeカラムが"index"になっているため、フルインデックススキャンとなる。
複合indexの注意点としてindexの定義時のカラムの順番が重要となる。
今回の複合index（index_start_end ）は、"start_date"、"end_date"の順番に定義した。
③のSQLの場合、条件に"start_date"がないため、複合index（index_start_end ）が使われないことになった。
複合indexのカラム順番を逆にした複合indexを貼る。
mysql> ALTER TABLE schedule ADD INDEX index_end_start(end_date, start_date); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
再度③のSQLを実行。
mysql> explain select * from schedule where end_date <= '2018/12/1'; +----+-------------+----------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | schedule | NULL | range | index_end_start | index_end_start | 5 | NULL | 45 | 100.00 | Using where; Using index | +----+-------------+----------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
この場合もtypeカラムは"range"になっているので、追加した複合index(index_end_start )が使われいる。
