ORDER BY句でのASC, DESCの混在
「最近自社のWebサイトの表示が遅い」ということでお問い合わせいただき調査した事例です。
以前から気になっていたがいよいよ限界を越えた、ということでお問い合わせいただきました。調べると、MySQLの負荷が高く、時間がかかっているクエリがいくつかあったので、それらの調整を行いました。今回は、調整した内容の一つを紹介させていただきます。
前提として、DBサーバーはMySQL8.0での話です。テーブルの作りなどは説明用のイメージで、実際のお客様の環境のものとは異なります。
まず、以下のような注文情報を格納するテーブルがあり、そこに注文日や価格が記録されていました。このテーブルに約10年分の大量のレコードが登録されている状況でした。
CREATE TABLE orders ( order_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE, comment TEXT NOT NULL, price INT(10) UNSIGNED NOT NULL, ordered_date DATE NOT NULL, INDEX date_price_idx (ordered_date, price)) ENGINE = InnoDB;
Webサイトでは最近の注文から順番に表示、同日の注文については価格が安いものから表示するということで、以下のようなSQLでレコードを取得していました。ORDER BY句でASCとDESCが混在しています。
SELECT * FROM orders ORDER BY ordered_date DESC,price LIMIT 10;
ordersテーブルにはインデックス(date_price_idx)が設定されているので、一見ソートは高速に行えそうですが、ORDER BY句ではASCとDESCが混在しており(ordered_date DESC,price ASC)、インデックス(ordered_date, price)によるデータの並びと異なるためソートにインデックスを使うことはできません。EXPLAINで実行計画を確認するとソートがfilesortになっているのがわかります。
mysql> EXPLAIN SELECT * FROM orders ORDER BY ordered_date DESC,price LIMIT 10 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 364874
filtered: 100.00
Extra: Using filesort
1 row in set, 1 warning (0.00 sec)
レコードは何十万もあったので、ソートに時間がかかっている状況でした。
このようなケースでは、降順インデックスを使います。インデックス指定でもDESCを指定することができるので、以下のようにORDER BYでの指定と同じ組み合わせでDESCを指定すれば、ソートにインデックスが使われるようになります。
降順インデックスを指定したテーブル
CREATE TABLE orders2 (
order_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
comment TEXT NOT NULL,
price INT(10) UNSIGNED NOT NULL,
ordered_date DATE NOT NULL,
INDEX recent_idx (ordered_date DESC, price)) ENGINE = InnoDB;
実行計画をみると、ソートにインデックスが使われるようになったのがわかります。
mysql> EXPLAIN SELECT * FROM orders2 ORDER BY ordered_date DESC,price LIMIT 10 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders2
partitions: NULL
type: index
possible_keys: NULL
key: recent_idx
key_len: 7
ref: NULL
rows: 10
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
ただし、降順インデックスが使えるのはMySQL8.0からになります。それ以前のバージョンではインデックスにDESC指定はできますが、実際には無視されるため、ソートは相変わらずfilesortになってしまいます。
A key_part specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.
key_partの指定はASCかDESCで終えることができる。これらのキーワードは、インデックス値の昇順／降順格納を指定するための将来の拡張用に許可されている。現在はこれらの指定は無視される。インデックス値は常に昇順で格納される。
https://dev.mysql.com/doc/refman/5.7/en/create-index.htmlより引用
MySQL5.7での実行計画
mysql> EXPLAIN SELECT * FROM orders2 ORDER BY ordered_date DESC,price LIMIT 10 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders2
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10195
filtered: 100.00
Extra: Using filesort ←ファイルソートのまま
1 row in set, 1 warning (0.00 sec)
MySQL8.0以前で降順インデックスが使えない場合でも、新たにinverted_priceカラムを追加し、そこにpriceの符号を反転させた値を格納するようにして同様のことを行うことはできます。 インデックスには反転値であるinverted_priceの方を含めます。
inverted_priceカラムを追加したテーブル
CREATE TABLE orders3 (
order_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
comment TEXT NOT NULL,
price INT(10) UNSIGNED NOT NULL,
inverted_price INT(10) NOT NULL, -- -priceの値を格納する
ordered_date DATE NOT NULL,
INDEX date_price_idx (ordered_date, inverted_price)) ENGINE = InnoDB;
実行計画をみると、インデックスが使われるようになったのがわかります。
mysql> EXPLAIN SELECT * FROM orders3 ORDER BY ordered_date DESC,inverted_price DESC LIMIT 10 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders3
partitions: NULL
type: index
possible_keys: NULL
key: date_price_idx
key_len: 7
ref: NULL
rows: 10
filtered: 100.00
Extra: Backward index scan
1 row in set, 1 warning (0.00 sec)
以上、ORDER BY句でASCとDESCを混在させたい場合に気をつけた方がいい点をまとめました。
お問い合わせいただく際、サーバーの管理があまり行われておらず、驚くほど古いバージョンのソフトウェアが使われていることも結構あるのですが、今回は、MySQL8が使われていたので助かりました。
投稿日:2022/05/18 17:12