MySQLのクラスタインデックスと、非クラスタインデックス（セカンダリインデックス）の速度検証

概要

クラスタインデックスと非クラスタインデックスで処理速度がどのように変わるのか、mysqlを利用して調べてみた

検証環境

• mysql 8.0.23

クラスタインデックスについて

• クラスタインデックスと、セカンダリインデックス
  • テーブル内のデータは、クラスタインデックスのデータ行の値に基づいて並べ替えられ格納される

テーブル上で PRIMARY KEY を定義すると、InnoDB ではそれがクラスタ化されたインデックスとして使用されます。 作成するテーブルごとに主キーを定義します。

• クラスタ化されたインデックスでのクエリーの挙動

クラスタ化されたインデックスから行にアクセスすると、インデックス検索がすべての行データを持つページで直接実行されるため、高速になります。

• セカンダリインデックスとクラスタ化されたインデックスとの関係

クラスタ化されたインデックス以外のインデックスは、すべてセカンダリインデックスと呼ばれます。 InnoDB では、セカンダリインデックス内の各レコードに、行の主キーカラム、およびセカンダリインデックスに指定されたカラムが含まれます。 InnoDB では、クラスタ化されたインデックス内で行を検索する際に、この主キー値が使用されます。

速度検証

テストデータの作成

• 速度検証を行うために、クラスタインデックスとして id（Primary key）と、非クラスタインデックスとして、number（indexのみ設定）を含んだテーブルを作成し、処理速度の比較を行ってみた

/* dbおよび、テーブルの作成 */
mysql> create database cluster_index_test character set utf8mb4;
mysql> create table users (id int auto_increment primary key, number int, index(number));

/* プロシージャを作成して、100万行のテストデータを挿入 */
mysql> delimiter //
mysql> create procedure create_test_data(in i int)
    -> begin
    ->   declare cnt int default 0;
    ->   while cnt < i do
    ->     set cnt = cnt + 1;
    ->     insert into users(number) values(cnt);
    ->   end while;
    -> end
    -> //
mysql> delimiter ;
mysql> call create_test_data(1000000); 

/* テストデータを確認 */
mysql> select count(*) from users;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.05 sec)

/* id と numberに同じ値が入ったレコードが100万件存在している */
mysql> select * from users limit 10;
+----+--------+
| id | number |
+----+--------+
|  1 |      1 |
|  2 |      2 |
|  3 |      3 |
|  4 |      4 |
|  5 |      5 |
|  6 |      6 |
|  7 |      7 |
|  8 |      8 |
|  9 |      9 |
| 10 |     10 |
+----+--------+
10 rows in set (0.00 sec)

mysql での実行速度検証方法

• show profileを利用して、queryの実行時間を測定する。（現在はパフォーマンススキーマの利用が推薦されている）
  • https://dev.mysql.com/doc/refman/8.0/ja/show-profile.html

mysql> SET profiling = 1;

クラスタインデックスの処理速度検証

• クエリを複数回実行して、処理速度を取得
• order by を id（クラスタインデックス） の昇順に設定
• 処理速度は0.04秒台

mysql> select * from users where id between 100000 AND 200000 order by id;
mysql> show profiles;
+----------+------------+--------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                              |
+----------+------------+--------------------------------------------------------------------+
|  1116865 | 0.04819600 | select * from users where id between 100000 AND 200000 order by id |
|  1116866 | 0.04485100 | select * from users where id between 100000 AND 200000 order by id |
|  1116867 | 0.04960100 | select * from users where id between 100000 AND 200000 order by id |
|  1116868 | 0.04490800 | select * from users where id between 100000 AND 200000 order by id |
|  1116869 | 0.04457800 | select * from users where id between 100000 AND 200000 order by id |
|  1116870 | 0.04597700 | select * from users where id between 100000 AND 200000 order by id |
|  1116871 | 0.04481600 | select * from users where id between 100000 AND 200000 order by id |
|  1116872 | 0.04475000 | select * from users where id between 100000 AND 200000 order by id |
|  1116873 | 0.04400700 | select * from users where id between 100000 AND 200000 order by id |
|  1116874 | 0.04724000 | select * from users where id between 100000 AND 200000 order by id |
|  1116875 | 0.04957500 | select * from users where id between 100000 AND 200000 order by id |
|  1116876 | 0.04591700 | select * from users where id between 100000 AND 200000 order by id |
|  1116877 | 0.04394200 | select * from users where id between 100000 AND 200000 order by id |
|  1116878 | 0.04451400 | select * from users where id between 100000 AND 200000 order by id |
|  1116879 | 0.05022800 | select * from users where id between 100000 AND 200000 order by id |
+----------+------------+--------------------------------------------------------------------+
15 rows in set, 1 warning (0.00 sec)

• SQLの実行計画を取得

mysql> explain select * from users where id between 100000 AND 200000 order by id;
+----+-------------+-------+------------+-------+----------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys  | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+----------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | range | PRIMARY,number | PRIMARY | 4       | NULL | 195732 |   100.00 | Using where |
+----+-------------+-------+------------+-------+----------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

• key に PRIMARY が選択されていることから、PRIMARY KEY の index を利用してインデックススキャンを行なっている
• Extra に Using filesort がないので、特にソート処理が行われていない
  • usersテーブルのデータは、クラスタインデックスの値の順番（ここでは id）の昇順で並んでいる、ソート処理が発生しない

降順に並び替えてみる

• 降順に並び替えを行うと、Extra に Backward index scan とあるので、元々のデータの並び順の逆順で処理が行われている模様

mysql> explain select * from users where id between 100000 AND 200000 order by id DESC;
+----+-------------+-------+------------+-------+----------------+---------+---------+------+--------+----------+----------------------------------+
| id | select_type | table | partitions | type  | possible_keys  | key     | key_len | ref  | rows   | filtered | Extra                            |
+----+-------------+-------+------------+-------+----------------+---------+---------+------+--------+----------+----------------------------------+
|  1 | SIMPLE      | users | NULL       | range | PRIMARY,number | PRIMARY | 4       | NULL | 195732 |   100.00 | Using where; Backward index scan |
+----+-------------+-------+------------+-------+----------------+---------+---------+------+--------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)

非クラスタインデックスの処理速度検証

• order by を number（非クラスタインデックス） の昇順に設定
  • id、number は同一レコードに同じ値が格納されている
• 処理速度は0.07秒台。PRIMARY KEY を利用した処理よりも処理時間がかかっている

mysql> select * from users where id between 100000 AND 200000 order by number;
mysql> SHOW PROFILES;
+----------+------------+------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                  |
+----------+------------+------------------------------------------------------------------------+
|  1116885 | 0.07490500 | select * from users where id between 100000 AND 200000 order by number |
|  1116886 | 0.07895600 | select * from users where id between 100000 AND 200000 order by number |
|  1116887 | 0.07719300 | select * from users where id between 100000 AND 200000 order by number |
|  1116888 | 0.07689800 | select * from users where id between 100000 AND 200000 order by number |
|  1116889 | 0.07640900 | select * from users where id between 100000 AND 200000 order by number |
|  1116890 | 0.07063900 | select * from users where id between 100000 AND 200000 order by number |
|  1116891 | 0.07628000 | select * from users where id between 100000 AND 200000 order by number |
|  1116892 | 0.07592100 | select * from users where id between 100000 AND 200000 order by number |
|  1116893 | 0.07825900 | select * from users where id between 100000 AND 200000 order by number |
|  1116894 | 0.07519200 | select * from users where id between 100000 AND 200000 order by number |
|  1116895 | 0.07810800 | select * from users where id between 100000 AND 200000 order by number |
|  1116896 | 0.07623300 | select * from users where id between 100000 AND 200000 order by number |
|  1116897 | 0.07581800 | select * from users where id between 100000 AND 200000 order by number |
|  1116898 | 0.07553000 | select * from users where id between 100000 AND 200000 order by number |
|  1116899 | 0.07491900 | select * from users where id between 100000 AND 200000 order by number |
+----------+------------+------------------------------------------------------------------------+
15 rows in set, 1 warning (0.00 sec)

• 実行計画を取得してみる

mysql> explain select * from users where id between 100000 AND 200000 order by number;
+----+-------------+-------+------------+-------+----------------+---------+---------+------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys  | key     | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+-------+------------+-------+----------------+---------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | users | NULL       | range | PRIMARY,number | PRIMARY | 4       | NULL | 195732 |   100.00 | Using where; Using filesort |
+----+-------------+-------+------------+-------+----------------+---------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

• number の検索に対して、number に設定された index を利用していることを確認
• Extra に Using filesort が存在しているので、ソート処理が行われていることが確認できる

参考

• https://engineer.namake-mono.xyz/development/410/
• https://yanor.net/wiki/?MySQL/%E3%83%81%E3%83%A5%E3%83%BC%E3%83%8B%E3%83%B3%E3%82%B0/SQL%E3%81%AE%E5%AE%9F%E8%A1%8C%E6%99%82%E9%96%93%E3%82%92%E8%AA%BF%E3%81%B9%E3%82%8B
• http://nippondanji.blogspot.com/2010/10/innodb.html
• http://nippondanji.blogspot.com/2009/03/using-filesort.html