MySQL 8.0 の降順インデックス

序文

インデックスが順序付けられていることは誰もが知っていると思いますが、MySQL の以前のバージョンでは昇順インデックスのみがサポートされ、降順インデックスはサポートされていなかったため、問題が発生していました。最新の MySQL 8.0 バージョンでは、降順インデックスがようやく導入されました。次に、降順インデックスについて見ていきます。

降順インデックス

単一列インデックス

（１）テストテーブル構造を見る

mysql> show テーブル sbtest1\G を作成します
************************** 1. 行 ****************************
    テーブル: sbtest1
テーブルの作成: CREATE TABLE `sbtest1` (
 `id` int unsigned NOT NULL AUTO_INCREMENT,
 `k` int unsigned NOT NULL デフォルト '0',
 `c` char(120) NOT NULL デフォルト ''
 `pad` char(60) NOT NULL DEFAULT ''
 主キー (`id`)、
 キー `k_1` (`k`)
) エンジン=InnoDB AUTO_INCREMENT=1000001 デフォルト文字セット=utf8mb4 COLLATE=utf8mb4_0900_ai_ci MAX_ROWS=1000000
セット内の 1 行 (0.00 秒)

（２）SQL文order by ... limit nを実行します。デフォルトは昇順で、インデックスが使用できます。

mysql> explain select * from sbtest1 order by k limit 10;
+----+-------------+----------+-----------+--------+---------------+-------+-------+------+------+------+------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+----------+-----------+--------+---------------+-------+-------+------+------+------+------+
| 1 | SIMPLE | sbtest1 | NULL | インデックス | NULL | k_1 | 4 | NULL | 10 | 100.00 | NULL |
+----+-------------+----------+-----------+--------+---------------+-------+-------+------+------+------+------+
セットに 1 行、警告 1 件 (0.00 秒)

（３）SQL文order by ... desc limit nを実行します。順序が降順の場合、インデックスは使用できません。逆順でもスキャンできますが、パフォーマンスに影響します。

mysql> explain select * from sbtest1 order by k desc limit 10;
+----+-------------+----------+-----------+---------+-------+-------+--------+---------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+----------+-----------+---------+-------+-------+--------+---------------------+
| 1 | SIMPLE | sbtest1 | NULL | インデックス | NULL | k_1 | 4 | NULL | 10 | 100.00 | 後方インデックススキャン |
+----+-------------+----------+-----------+---------+-------+-------+--------+---------------------+
セットに 1 行、警告 1 件 (0.00 秒)

（4）降順インデックスを作成する

mysql> テーブル sbtest1 を変更し、インデックス k_2(k desc) を追加します。
クエリは正常、影響を受けた行は 0 行 (6.45 秒)
レコード: 0 重複: 0 警告: 0

（５）SQL文order by ... desc limit nを再度実行すると、降順インデックスが使用できるようになります。

mysql> explain select * from sbtest1 order by k desc limit 10;
+----+-------------+----------+-----------+--------+---------------+-------+-------+------+------+------+------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+----------+-----------+--------+---------------+-------+-------+------+------+------+------+
| 1 | SIMPLE | sbtest1 | NULL | インデックス | NULL | k_2 | 4 | NULL | 10 | 100.00 | NULL |
+----+-------------+----------+-----------+--------+---------------+-------+-------+------+------+------+------+
セットに 1 行、警告 1 件 (0.00 秒)

複数列インデックス

（１）テストテーブル構造を見る

mysql> show テーブル sbtest1\G を作成します
************************** 1. 行 ****************************
    テーブル: sbtest1
テーブルの作成: CREATE TABLE `sbtest1` (
 `id` int unsigned NOT NULL AUTO_INCREMENT,
 `k` int unsigned NOT NULL デフォルト '0',
 `c` char(120) NOT NULL デフォルト ''
 `pad` char(60) NOT NULL DEFAULT ''
 主キー (`id`)、
 キー `k_1` (`k`)、
 キー `idx_c_pad_1` (`c`,`pad`)
) エンジン=InnoDB AUTO_INCREMENT=1000001 デフォルト文字セット=utf8mb4 COLLATE=utf8mb4_0900_ai_ci MAX_ROWS=1000000
セット内の 1 行 (0.00 秒)

（２）複数列インデックスの場合、降順インデックスがない場合、SQL 1のみがインデックスを使用でき、SQL 4は逆順にスキャンでき、他の２つのSQL文はフルテーブルスキャンしか実行できないため、非常に非効率的です。

SQL 1: select * from sbtest1 order by c,pad limit 10;

SQL 2: select * from sbtest1 order by c,pad desc limit 10;

SQL 3: select * from sbtest1 order by c desc, pad limit 10;

SQL 4: explain select * from sbtest1 order by c desc, pad desc limit 10;

mysql> explain select * from sbtest1 order by c,pad limit 10;
+----+-------------+----------+-----------+---------+---------------+-------------+-------+------+------+------+------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+----------+-----------+---------+---------------+-------------+-------+------+------+------+------+
| 1 | シンプル | sbtest1 | NULL | インデックス | NULL | idx_c_pad_1 | 720 | NULL | 10 | 100.00 | NULL |
+----+-------------+----------+-----------+---------+---------------+-------------+-------+------+------+------+------+
セットに 1 行、警告 1 件 (0.00 秒)

mysql> explain select * from sbtest1 order by c,pad desc limit 10;
+----+-------------+----------+-----------+--------+---------------+-----+---------+---------+----------+----------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+----------+-----------+--------+---------------+-----+---------+---------+----------+----------------+
| 1 | SIMPLE | sbtest1 | NULL | ALL | NULL | NULL | NULL | NULL | 950738 | 100.00 | filesort を使用 |
+----+-------------+----------+-----------+--------+---------------+-----+---------+---------+----------+----------------+
セットに 1 行、警告 1 件 (0.00 秒)

mysql> explain select * from sbtest1 order by c desc,pad limit 10;
+----+-------------+----------+-----------+--------+---------------+-----+---------+---------+----------+----------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+----------+-----------+--------+---------------+-----+---------+---------+----------+----------------+
| 1 | SIMPLE | sbtest1 | NULL | ALL | NULL | NULL | NULL | NULL | 950738 | 100.00 | filesort を使用 |
+----+-------------+----------+-----------+--------+---------------+-----+---------+---------+----------+----------------+
セットに 1 行、警告 1 回 (0.01 秒)

mysql> explain select * from sbtest1 order by c desc,pad desc limit 10;
+----+-------------+----------+-----------+---------+---------------+-------------+-------+-------+---------+---------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+----------+-----------+---------+---------------+-------------+-------+-------+---------+---------------------+
| 1 | SIMPLE | sbtest1 | NULL | インデックス | NULL | idx_c_pad_1 | 720 | NULL | 10 | 100.00 | 後方インデックススキャン |
+----+-------------+----------+-----------+---------+---------------+-------------+-------+-------+---------+---------------------+
セットに 1 行、警告 1 件 (0.00 秒)

（３）対応する降順インデックスを作成する

mysql> テーブル sbtest1 を変更し、インデックス idx_c_pad_2(c,pad desc) を追加します。
クエリは正常、影響を受けた行は 0 行 (1 分 11.27 秒)
レコード: 0 重複: 0 警告: 0

mysql> テーブル sbtest1 を変更し、インデックス idx_c_pad_3(c desc,pad) を追加します。
クエリは正常、影響を受けた行は 0 行 (1 分 14.22 秒)
レコード: 0 重複: 0 警告: 0

mysql> テーブル sbtest1 を変更し、インデックス idx_c_pad_4(c desc,pad desc) を追加します。
クエリは正常、影響を受けた行は 0 行 (1 分 8.70 秒)
レコード: 0 重複: 0 警告: 0

（４）SQLを再度実行すると降順インデックスが使えるようになり、効率が大幅に向上する

mysql> explain select * from sbtest1 order by c,pad desc limit 10;
+----+-------------+----------+-----------+---------+---------------+-------------+-------+------+------+------+------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+----------+-----------+---------+---------------+-------------+-------+------+------+------+------+
| 1 | シンプル | sbtest1 | NULL | インデックス | NULL | idx_c_pad_2 | 720 | NULL | 10 | 100.00 | NULL |
+----+-------------+----------+-----------+---------+---------------+-------------+-------+------+------+------+------+
セットに 1 行、警告 1 件 (0.00 秒)

mysql> explain select * from sbtest1 order by c desc,pad limit 10;
+----+-------------+----------+-----------+---------+---------------+-------------+-------+------+------+------+------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+----------+-----------+---------+---------------+-------------+-------+------+------+------+------+
| 1 | シンプル | sbtest1 | NULL | インデックス | NULL | idx_c_pad_3 | 720 | NULL | 10 | 100.00 | NULL |
+----+-------------+----------+-----------+---------+---------------+-------------+-------+------+------+------+------+
セットに 1 行、警告 1 件 (0.00 秒)

mysql> explain select * from sbtest1 order by c desc,pad desc limit 10;
+----+-------------+----------+-----------+---------+---------------+-------------+-------+------+------+------+------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+----------+-----------+---------+---------------+-------------+-------+------+------+------+------+
| 1 | シンプル | sbtest1 | NULL | インデックス | NULL | idx_c_pad_4 | 720 | NULL | 10 | 100.00 | NULL |
+----+-------------+----------+-----------+---------+---------------+-------------+-------+------+------+------+------+
セットに 1 行、警告 1 件 (0.00 秒)

要約する

MySQL 8.0 で導入された降順インデックスの最も重要な機能は、インデックスが複数列のソートに使用できないという問題を解決し、より多くのアプリケーションシナリオをカバーすることです。

上記は、MySQL 8.0 の降順インデックスの詳細です。MySQL 降順インデックスの詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

以下もご興味があるかもしれません: