2024.04.30
STAFF BLOG
スタッフブログ
TECHNICAL
テクログ
お久しぶりです。
JGです。
MySQL8の新機能といえばウィンドウ関数と思う方が多いと思われますが、今回は降順インデックスを簡単に紹介したいと思います。
概要
降順インデックスが導入される前は、インデックスを逆の順序でスキャンはできましたがパフォーマンスが低下する問題がありました。
降順インデックスを使うことで、降順する際に順方向にスキャンできるためより速度があがります。
細かい説明の部分は下記のドキュメントを読んで頂けると幸いです。
https://dev.mysql.com/doc/refman/8.0/ja/descending-indexes.html
https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html
単一インデックス
これからはMySQL8で実際に幾つか試してみたいと思います。
単一インデックスの場合ですが、下記のようなテーブルを作成し10件ほどデータを流し込みます。
CREATE TABLE `sample` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL COLLATE 'utf8mb4_general_ci',
`sort` INT NOT NULL,
PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB;
INSERT INTO sample (name, sort) VALUES ('dummy_a', 4);
INSERT INTO sample (name, sort) VALUES ('dummy_b', 6);
INSERT INTO sample (name, sort) VALUES ('dummy_c', 8);
INSERT INTO sample (name, sort) VALUES ('dummy_d', 2);
INSERT INTO sample (name, sort) VALUES ('dummy_e', 10);
INSERT INTO sample (name, sort) VALUES ('dummy_f', 1);
INSERT INTO sample (name, sort) VALUES ('dummy_g', 3);
INSERT INTO sample (name, sort) VALUES ('dummy_h', 5);
INSERT INTO sample (name, sort) VALUES ('dummy_i', 7);
INSERT INTO sample (name, sort) VALUES ('dummy_j', 9);まずは、下記のような昇順のインデックスを作成した例です。
sort ASCと書いてありますが、ASCを省略した場合も昇順のインデックスになります。
CREATE INDEX sort_asc ON sample (sort ASC);次に下記の2パターンのEXPLAINの結果です。
EXPLAIN SELECT id FROM sample ORDER BY sort ASC;
type→index
key→sort_asc
Extra→Using index
EXPLAIN SELECT id FROM sample ORDER BY sort DESC;
type→index
key→sort_asc
Extra→Backward index scan; Using index今回は昇順のインデックスなので、降順した場合は「Backward index scan」と表示されました。
これは昇順に並んでいるインデックスを逆から読んでいるため、このメッセージが表示されます。
次は先ほど作成した昇順のインデックスを削除し、降順インデックスを作成した例です。
DROP INDEX sort_asc ON sample;
CREATE INDEX sort_desc ON sample (sort DESC);
EXPLAIN SELECT id FROM sample ORDER BY sort DESC;
type→index
key→sort_desc
Extra→Using index
EXPLAIN SELECT id FROM sample ORDER BY sort ASC;
type→index
key→sort_desc
Extra→Backward index scan; Using index今回は降順インデックスなので、昇順した場合は「Backward index scan」と表示されました。
複合インデックス
次は複合インデックスの場合ですが、下記のようなテーブルを作成し10件ほどデータを流し込みます。
CREATE TABLE `sample2` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL COLLATE 'utf8mb4_general_ci',
`sort` INT NOT NULL,
`sort2` INT NOT NULL,
PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB;
INSERT INTO sample2 (name, sort, sort2) VALUES ('dummy_a', 4, 3);
INSERT INTO sample2 (name, sort, sort2) VALUES ('dummy_b', 6 ,9);
INSERT INTO sample2 (name, sort, sort2) VALUES ('dummy_c', 8, 1);
INSERT INTO sample2 (name, sort, sort2) VALUES ('dummy_d', 2, 7);
INSERT INTO sample2 (name, sort, sort2) VALUES ('dummy_e', 10, 5);
INSERT INTO sample2 (name, sort, sort2) VALUES ('dummy_f', 1, 6);
INSERT INTO sample2 (name, sort, sort2) VALUES ('dummy_g', 3, 10);
INSERT INTO sample2 (name, sort, sort2) VALUES ('dummy_h', 5, 8);
INSERT INTO sample2 (name, sort, sort2) VALUES ('dummy_i', 7, 4);
INSERT INTO sample2 (name, sort, sort2) VALUES ('dummy_j', 9, 2);まずは、下記のように両方とも昇順のインデックスを作成した例です。
CREATE INDEX sort_pattern1 ON sample2 (sort ASC, sort2 ASC);
EXPLAIN SELECT id FROM sample2 ORDER BY sort ASC, sort2 ASC;
type→index
key→sort_pattern1
Extra→Using index
EXPLAIN SELECT id FROM sample2 ORDER BY sort DESC, sort2 DESC;
type→index
key→sort_pattern1
Extra→Backward index scan; Using index
EXPLAIN SELECT id FROM sample2 ORDER BY sort ASC, sort2 DESC;
type→index
key→sort_pattern1
Extra→Using index; Using filesort
EXPLAIN SELECT id FROM sample2 ORDER BY sort DESC, sort2 ASC;
type→index
key→sort_pattern1
Extra→Using index; Using filesort今回は両方とも昇順のインデックスなので、両方とも昇順の場合はインデックスが問題なく使われ、両方とも降順の場合は「Backward index scan」と表示されました。
それ以外の場合はUsing filesortになりました。
次は先ほどのインデックスを削除し、両方とも降順インデックスを作成した例です。
DROP INDEX sort_pattern1 ON sample2;
CREATE INDEX sort_pattern2 ON sample2 (sort DESC, sort2 DESC);
EXPLAIN SELECT id FROM sample2 ORDER BY sort DESC, sort2 DESC;
type→index
key→sort_pattern2
Extra→Using index
EXPLAIN SELECT id FROM sample2 ORDER BY sort ASC, sort2 ASC;
type→index
key→sort_pattern2
Extra→Backward index scan; Using index
EXPLAIN SELECT id FROM sample2 ORDER BY sort ASC, sort2 DESC;
type→index
key→sort_pattern2
Extra→Using index; Using filesort
EXPLAIN SELECT id FROM sample2 ORDER BY sort DESC, sort2 ASC;
type→index
key→sort_pattern2
Extra→Using index; Using filesort今回は両方とも降順インデックスなので、両方とも降順の場合はインデックスが問題なく使われ、両方とも昇順の場合は「Backward index scan」と表示されました。
それ以外の場合はUsing filesortになりました。
次はsortを昇順、sort2を降順にした場合です。
DROP INDEX sort_pattern2 ON sample2;
CREATE INDEX sort_pattern3 ON sample2 (sort ASC, sort2 DESC);
EXPLAIN SELECT id FROM sample2 ORDER BY sort ASC, sort2 DESC;
type→index
key→sort_pattern3
Extra→Using index
EXPLAIN SELECT id FROM sample2 ORDER BY sort DESC, sort2 ASC;
type→index
key→sort_pattern3
Extra→Backward index scan; Using index
EXPLAIN SELECT id FROM sample2 ORDER BY sort ASC, sort2 ASC;
type→index
key→sort_pattern3
Extra→Using index; Using filesort
EXPLAIN SELECT id FROM sample2 ORDER BY sort DESC, sort2 DESC;
type→index
key→sort_pattern3
Extra→Using index; Using filesort今回はsortを昇順、sort2を降順にした場合なので、その場合はインデックスが問題なく使われ、両方とも作成したインデックスと逆の場合は「Backward index scan」と表示されました。
それ以外の場合はUsing filesortになりました。
最後はsortを降順、sort2を昇順にした場合です。
DROP INDEX sort_pattern3 ON sample2;
CREATE INDEX sort_pattern4 ON sample2 (sort DESC, sort2 ASC);
EXPLAIN SELECT id FROM sample2 ORDER BY sort DESC, sort2 ASC;
type→index
key→sort_pattern4
Extra→Using index
EXPLAIN SELECT id FROM sample2 ORDER BY sort ASC, sort2 DESC;
type→index
key→sort_pattern4
Extra→Backward index scan; Using index
EXPLAIN SELECT id FROM sample2 ORDER BY sort ASC, sort2 ASC;
type→index
key→sort_pattern4
Extra→Using index; Using filesort
EXPLAIN SELECT id FROM sample2 ORDER BY sort DESC, sort2 DESC;
type→index
key→sort_pattern4
Extra→Using index; Using filesort今回はsortを降順、sort2を昇順にした場合なので、その場合はインデックスが問題なく使われ、両方とも作成したインデックスと逆の場合は「Backward index scan」と表示されました。
それ以外の場合はUsing filesortになりました。
最後に
降順ソートを使うときですが、データを最新順に表示したいときや、複合インデックスの時にも使えるので並び順の条件が複数ある時等になると思います。
今回のブログを読んで、MySQL8で降順の際のパフォーマンスが悪いと感じたら是夫この記事を思い出して降順インデックスを使うことで改善されると幸いです。
本日はこれで失礼します。
