REDOログ／UNDOログ暗号化やCTEと同様、MySQL 8.0.1 DMRで既にサポートされていた降順INDEXを試してみました。
【参考資料・記事】
- 8.3.13 Descending Indexes（MySQL 8.0 Reference Manual）
- MySQL 8.0 Labs – Descending Indexes in MySQL（MySQL Server Blog）
- MYSQL8.0 DESCENDING INDEXのサポート（Variable.jp）
1. 使い方
リファレンスマニュアルにあるように、
CREATE TABLE t (
c1 INT, c2 INT,
INDEX idx1 (c1 ASC, c2 ASC),
INDEX idx2 (c1 ASC, c2 DESC),
INDEX idx3 (c1 DESC, c2 ASC),
INDEX idx4 (c1 DESC, c2 DESC)
);
のように指定します。
「INDEX」ではなく「PRIMARY KEY」に指定して、主キー（を構成する列）を降順にすることもできます。
使いどころとしては、マニュアルに
Descending indexes also make it possible for the optimizer to use multiple-column indexes when the most efficient scan order mixes ascending order for some columns and descending order for others.
とあるように、複合インデックスに使うのがよさそうです。
2. ORDER BY＋LIMITでテストしてみる
100万行のテーブルから、2,000行抽出するのに使ってみます。
2-1. 昇順＋昇順のINDEX
まずは、これまで通りの昇順INDEXで確認してみます。
テーブル定義：昇順＋昇順
mysql> CREATE TABLE idxdtest.test_data (id INT UNSIGNED NOT NULL, val1 INT UNSIGNED NOT NULL, val2 INT UNSIGNED NOT NULL, str VARCHAR(100), PRIMARY KEY (id ASC), INDEX (val1 ASC, val2 ASC));
Query OK, 0 rows affected (0.02 sec)
データは以下の形でINSERTしています。
データ（昇順）
INSERT INTO idxdtest.test_data VALUES(0, 0, 0, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO idxdtest.test_data VALUES(1, 0, 1, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO idxdtest.test_data VALUES(2, 0, 2, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO idxdtest.test_data VALUES(3, 0, 3, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO idxdtest.test_data VALUES(4, 0, 4, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO idxdtest.test_data VALUES(5, 0, 5, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO idxdtest.test_data VALUES(6, 0, 6, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO idxdtest.test_data VALUES(7, 0, 7, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO idxdtest.test_data VALUES(8, 0, 8, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO idxdtest.test_data VALUES(9, 0, 9, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
（中略）
INSERT INTO idxdtest.test_data VALUES(995, 0, 995, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO idxdtest.test_data VALUES(996, 0, 996, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO idxdtest.test_data VALUES(997, 0, 997, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO idxdtest.test_data VALUES(998, 0, 998, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO idxdtest.test_data VALUES(999, 0, 999, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO idxdtest.test_data VALUES(1000, 1, 0, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO idxdtest.test_data VALUES(1001, 1, 1, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO idxdtest.test_data VALUES(1002, 1, 2, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO idxdtest.test_data VALUES(1003, 1, 3, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO idxdtest.test_data VALUES(1004, 1, 4, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
（中略）
INSERT INTO idxdtest.test_data VALUES(999990, 999, 990, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO idxdtest.test_data VALUES(999991, 999, 991, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO idxdtest.test_data VALUES(999992, 999, 992, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO idxdtest.test_data VALUES(999993, 999, 993, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO idxdtest.test_data VALUES(999994, 999, 994, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO idxdtest.test_data VALUES(999995, 999, 995, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO idxdtest.test_data VALUES(999996, 999, 996, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO idxdtest.test_data VALUES(999997, 999, 997, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO idxdtest.test_data VALUES(999998, 999, 998, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO idxdtest.test_data VALUES(999999, 999, 999, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INDEXと同じ順番でORDERを掛けてSELECTします。
※都度、MySQLを再起動しています。
INDEXと同じ順番でORDER
mysql> USE idxdtest;
No connection. Trying to reconnect...
Connection id: 7
Current database: *** NONE ***
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT * FROM test_data ORDER BY val1 ASC, val2 ASC LIMIT 0, 2000;
+------+------+------+------------------------------------------------------------------------------------------------------+
| id | val1 | val2 | str |
+------+------+------+------------------------------------------------------------------------------------------------------+
| 0 | 0 | 0 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 1 | 0 | 1 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 2 | 0 | 2 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
（中略）
| 1997 | 1 | 997 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 1998 | 1 | 998 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 1999 | 1 | 999 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
+------+------+------+------------------------------------------------------------------------------------------------------+
2000 rows in set (0.01 sec)
mysql> EXPLAIN SELECT * FROM test_data ORDER BY val1 ASC, val2 ASC LIMIT 0, 2000;
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | test_data | NULL | index | NULL | val1 | 8 | NULL | 2000 | 100.00 | NULL |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> USE idxdtest;
No connection. Trying to reconnect...
Connection id: 7
Current database: *** NONE ***
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT * FROM test_data ORDER BY val1 ASC, val2 ASC LIMIT 998000, 2000;
+--------+------+------+------------------------------------------------------------------------------------------------------+
| id | val1 | val2 | str |
+--------+------+------+------------------------------------------------------------------------------------------------------+
| 998000 | 998 | 0 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 998001 | 998 | 1 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 998002 | 998 | 2 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
（中略）
| 999997 | 999 | 997 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 999998 | 999 | 998 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 999999 | 999 | 999 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
+--------+------+------+------------------------------------------------------------------------------------------------------+
2000 rows in set (5.73 sec)
mysql> EXPLAIN SELECT * FROM test_data ORDER BY val1 ASC, val2 ASC LIMIT 998000, 2000;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| 1 | SIMPLE | test_data | NULL | ALL | NULL | NULL | NULL | NULL | 991349 | 100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.03 sec)
先頭のほうはINDEXが効いていますが、後ろのほうは効いていません。
2列目を逆の順番にしてORDERを掛けてみます。
2列目が違う順番でORDER
mysql> USE idxdtest;
No connection. Trying to reconnect...
Connection id: 7
Current database: *** NONE ***
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT * FROM test_data ORDER BY val1 ASC, val2 DESC LIMIT 0, 2000;
+------+------+------+------------------------------------------------------------------------------------------------------+
| id | val1 | val2 | str |
+------+------+------+------------------------------------------------------------------------------------------------------+
| 999 | 0 | 999 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 998 | 0 | 998 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 997 | 0 | 997 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
（中略）
| 1002 | 1 | 2 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 1001 | 1 | 1 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 1000 | 1 | 0 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
+------+------+------+------------------------------------------------------------------------------------------------------+
2000 rows in set (0.87 sec)
mysql> EXPLAIN SELECT * FROM test_data ORDER BY val1 ASC, val2 DESC LIMIT 0, 2000;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| 1 | SIMPLE | test_data | NULL | ALL | NULL | NULL | NULL | NULL | 991349 | 100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.02 sec)
INDEXは使われません。
2-2. 昇順＋降順のINDEX
そこで、複合インデックスの2列目を降順にしてみます。
INDEX2列目を降順に
mysql> DROP TABLE idxdtest.test_data;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE idxdtest.test_data (id INT UNSIGNED NOT NULL, val1 INT UNSIGNED NOT NULL, val2 INT UNSIGNED NOT NULL, str VARCHAR(100), PRIMARY KEY (id ASC), INDEX (val1 ASC, val2 DESC));
Query OK, 0 rows affected (0.04 sec)
※データは入れ直します。以降同様。
INDEX同順でORDER
mysql> USE idxdtest;
No connection. Trying to reconnect...
Connection id: 7
Current database: *** NONE ***
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT * FROM test_data ORDER BY val1 ASC, val2 DESC LIMIT 0, 2000;
+------+------+------+------------------------------------------------------------------------------------------------------+
| id | val1 | val2 | str |
+------+------+------+------------------------------------------------------------------------------------------------------+
| 999 | 0 | 999 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 998 | 0 | 998 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 997 | 0 | 997 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
（中略）
| 1002 | 1 | 2 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 1001 | 1 | 1 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 1000 | 1 | 0 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
+------+------+------+------------------------------------------------------------------------------------------------------+
2000 rows in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM test_data ORDER BY val1 ASC, val2 DESC LIMIT 0, 2000;
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | test_data | NULL | index | NULL | val1 | 8 | NULL | 2000 | 100.00 | NULL |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM test_data ORDER BY val1 ASC, val2 DESC LIMIT 998000, 2000;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| 1 | SIMPLE | test_data | NULL | ALL | NULL | NULL | NULL | NULL | 975339 | 100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
傾向は昇順INDEXの場合と同じようです。結果は載せていませんが、複合インデックス2列目と合わないORDERを指定した場合はINDEXが効きませんでした。
2-3. 降順＋昇順のINDEX
複合インデックス各列の昇順・降順を入れ替えてみます。
INDEXソート順入れ替え
mysql> DROP TABLE idxdtest.test_data;
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE TABLE idxdtest.test_data (id INT UNSIGNED NOT NULL, val1 INT UNSIGNED NOT NULL, val2 INT UNSIGNED NOT NULL, str VARCHAR(100), PRIMARY KEY (id ASC), INDEX (val1 DESC, val2 ASC));
Query OK, 0 rows affected (0.05 sec)
※ここでデータ投入
mysql> USE idxdtest;
No connection. Trying to reconnect...
Connection id: 7
Current database: *** NONE ***
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT * FROM test_data ORDER BY val1 DESC, val2 ASC LIMIT 0, 2000;
+--------+------+------+------------------------------------------------------------------------------------------------------+
| id | val1 | val2 | str |
+--------+------+------+------------------------------------------------------------------------------------------------------+
| 999000 | 999 | 0 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 999001 | 999 | 1 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 999002 | 999 | 2 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
（中略）
| 998997 | 998 | 997 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 998998 | 998 | 998 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 998999 | 998 | 999 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
+--------+------+------+------------------------------------------------------------------------------------------------------+
2000 rows in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM test_data ORDER BY val1 DESC, val2 ASC LIMIT 0, 2000;
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | test_data | NULL | index | NULL | val1 | 8 | NULL | 2000 | 100.00 | NULL |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
どこまでINDEXが使われるか試してみます。
INDEX利用確認
mysql> EXPLAIN SELECT * FROM test_data ORDER BY val1 DESC, val2 ASC LIMIT 2000, 2000;
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | test_data | NULL | index | NULL | val1 | 8 | NULL | 4000 | 100.00 | NULL |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM test_data ORDER BY val1 DESC, val2 ASC LIMIT 6000, 2000;
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | test_data | NULL | index | NULL | val1 | 8 | NULL | 8000 | 100.00 | NULL |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM test_data ORDER BY val1 DESC, val2 ASC LIMIT 7000, 2000;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| 1 | SIMPLE | test_data | NULL | ALL | NULL | NULL | NULL | NULL | 991349 | 100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM test_data ORDER BY val1 DESC, val2 ASC LIMIT 7000, 1000;
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | test_data | NULL | index | NULL | val1 | 8 | NULL | 8000 | 100.00 | NULL |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM test_data ORDER BY val1 DESC, val2 ASC LIMIT 8000, 1000;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| 1 | SIMPLE | test_data | NULL | ALL | NULL | NULL | NULL | NULL | 991349 | 100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
100万行のうちの8,000行、先頭から0.8%以内の絞り込みまでINDEXが使われました。
2-4. その他のパターン
この後、
- 主キーを降順に
- 投入するデータの数値をランダムに
の2通りを追加で試しましたが、傾向は全く同じでした。
なお、どの程度までINDEXが使われるか（昇順／降順が異なるINDEXが使われるかどうか）については、環境によって変わる可能性がありますので、参考程度に見てください。
2-5. カバリングINDEXの場合は
SELECTで抽出（射影）する列を、val1とval2だけに限定する（カバリングINDEXの状態）にすると、最後の行までINDEXが使われます。
※数値をランダムにしたデータで実行しています。
カバリングINDEX
mysql> USE idxdtest;
No connection. Trying to reconnect...
Connection id: 7
Current database: *** NONE ***
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT val1, val2 FROM test_data ORDER BY val1 DESC, val2 ASC LIMIT 998000, 2000;
+------+------+
| val1 | val2 |
+------+------+
| 49 | 799 |
| 49 | 800 |
| 49 | 800 |
（中略）
| 47 | 778 |
| 47 | 778 |
| 47 | 780 |
+------+------+
2000 rows in set (0.20 sec)
mysql> EXPLAIN SELECT val1, val2 FROM test_data ORDER BY val1 DESC, val2 ASC LIMIT 998000, 2000;
+----+-------------+-----------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | test_data | NULL | index | NULL | val1 | 8 | NULL | 999485 | 100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
後ろのほうまでINDEXが効いています。
なお、セカンダリインデックスは主キーの値も持っていますので、以下もカバリングインデックス扱いになります。
カバリングINDEX（主キー列込み）
mysql> USE idxdtest;
No connection. Trying to reconnect...
Connection id: 7
Current database: *** NONE ***
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT id, val1, val2 FROM test_data ORDER BY val1 DESC, val2 ASC LIMIT 998000, 2000;
+---------+------+------+
| id | val1 | val2 |
+---------+------+------+
| 503887 | 49 | 799 |
| 345441 | 49 | 800 |
| 486529 | 49 | 800 |
（中略）
| 25424 | 47 | 778 |
| 803023 | 47 | 778 |
| 226673 | 47 | 780 |
+---------+------+------+
2000 rows in set (0.20 sec)
mysql> EXPLAIN SELECT id, val1, val2 FROM test_data ORDER BY val1 DESC, val2 ASC LIMIT 998000, 2000;
+----+-------------+-----------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | test_data | NULL | index | NULL | val1 | 8 | NULL | 999485 | 100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
ソート順が合わない場合もINDEXは使われますが、「Using filesort」が入ります（ソート対象の行数が増えると遅くなります）。
ソート順違いのEXPLAIN
mysql> EXPLAIN SELECT id, val1, val2 FROM test_data ORDER BY val1 ASC, val2 ASC LIMIT 998000, 2000;
+----+-------------+-----------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------------+
| 1 | SIMPLE | test_data | NULL | index | NULL | val1 | 8 | NULL | 999485 | 100.00 | Using index; Using filesort |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
3. WHERE＋ORDER BY（＋LIMIT）で試してみる
まずは、全て昇順の複合インデックスで、WHERE＋ORDER（昇順，降順）を試してみます。
昇順INDEXでWHERE＋ORDER（昇順，降順）
mysql> DROP TABLE test_data;
Query OK, 0 rows affected (0.08 sec)
mysql> CREATE TABLE idxdtest.test_data (id INT UNSIGNED NOT NULL, val1 INT UNSIGNED NOT NULL, val2 INT UNSIGNED NOT NULL, val3 INT UNSIGNED NOT NULL, str VARCHAR(100), PRIMARY KEY (id ASC), INDEX (val1 ASC, val2 ASC, val3 ASC));
Query OK, 0 rows affected (0.02 sec)
mysql> USE idxdtest;
No connection. Trying to reconnect...
Connection id: 7
Current database: *** NONE ***
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT * FROM test_data WHERE val1 = 500 ORDER BY val2 ASC, val3 DESC;
+--------+------+------+------+------------------------------------------------------------------------------------------------------+
| id | val1 | val2 | val3 | str |
+--------+------+------+------+------------------------------------------------------------------------------------------------------+
| 85516 | 500 | 3 | 552 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 550181 | 500 | 3 | 74 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 253130 | 500 | 5 | 757 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 642382 | 500 | 5 | 265 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 865249 | 500 | 6 | 650 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
（中略）
| 150998 | 500 | 995 | 948 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 458133 | 500 | 995 | 788 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 201354 | 500 | 995 | 498 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 913687 | 500 | 998 | 358 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 649047 | 500 | 999 | 520 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
+--------+------+------+------+------------------------------------------------------------------------------------------------------+
1013 rows in set (0.20 sec)
mysql> EXPLAIN SELECT * FROM test_data WHERE val1 = 500 ORDER BY val2 ASC, val3 DESC;
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+---------------------------------------+
| 1 | SIMPLE | test_data | NULL | ref | val1 | val1 | 4 | const | 1013 | 100.00 | Using index condition; Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)
INDEXは使われますが、「Using filesort」が入ります。
複合インデックスを（昇順，降順）にしてみます。
降順INDEX込み
mysql> DROP TABLE test_data;
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE idxdtest.test_data (id INT UNSIGNED NOT NULL, val1 INT UNSIGNED NOT NULL, val2 INT UNSIGNED NOT NULL, val3 INT UNSIGNED NOT NULL, str VARCHAR(100), PRIMARY KEY (id ASC), INDEX (val1 ASC, val2 ASC, val3 DESC));
Query OK, 0 rows affected (0.02 sec)
mysql> USE idxdtest;
No connection. Trying to reconnect...
Connection id: 7
Current database: *** NONE ***
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT * FROM test_data WHERE val1 = 500 ORDER BY val2 ASC, val3 DESC;
+--------+------+------+------+------------------------------------------------------------------------------------------------------+
| id | val1 | val2 | val3 | str |
+--------+------+------+------+------------------------------------------------------------------------------------------------------+
| 85516 | 500 | 3 | 552 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 550181 | 500 | 3 | 74 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 253130 | 500 | 5 | 757 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
（中略）
| 201354 | 500 | 995 | 498 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 913687 | 500 | 998 | 358 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 649047 | 500 | 999 | 520 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
+--------+------+------+------+------------------------------------------------------------------------------------------------------+
1013 rows in set (0.21 sec)
mysql> EXPLAIN SELECT * FROM test_data WHERE val1 = 500 ORDER BY val2 ASC, val3 DESC;
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | test_data | NULL | ref | val1 | val1 | 4 | const | 1013 | 100.00 | Using index condition |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
「Using filesort」がなくなりました。
※この程度のデータ量であれば、「Using filesort」になるかどうかはあまり速度に影響がなさそうです。
LIMITで絞り込みしてみます。
LIMIT絞り込み
mysql> USE idxdtest;
No connection. Trying to reconnect...
Connection id: 7
Current database: *** NONE ***
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT * FROM test_data WHERE val1 = 500 ORDER BY val2 ASC, val3 DESC LIMIT 100;
+--------+------+------+------+------------------------------------------------------------------------------------------------------+
| id | val1 | val2 | val3 | str |
+--------+------+------+------+------------------------------------------------------------------------------------------------------+
| 85516 | 500 | 3 | 552 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 550181 | 500 | 3 | 74 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 253130 | 500 | 5 | 757 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
（中略）
| 301892 | 500 | 95 | 326 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 448090 | 500 | 95 | 14 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
| 510647 | 500 | 99 | 733 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |
+--------+------+------+------+------------------------------------------------------------------------------------------------------+
100 rows in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM test_data WHERE val1 = 500 ORDER BY val2 ASC, val3 DESC LIMIT 100;
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | test_data | NULL | ref | val1 | val1 | 4 | const | 1013 | 100.00 | Using index condition |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
WHERE句なしの場合と違い、rowsは「1013」のまま変わりませんでした。
【おまけ】
MySQL 8.0関連投稿記事へのリンクを集めました。