Go to Qiita Advent Calendar 2024 Top
LoginSignup
3
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@hmatsu47

Amazon Aurora（MySQL-compatible edition）で新しくサポートされたAsynchronous Key Prefetchを試してみる

Last updated at Posted at 2017-11-01

R4インスタンスのベンチマークに続いて、新機能としてアナウンスされた**Asynchronous Key Prefetch（AKP）**を試してみます。

1. Asynchronous Key Prefetchとは

に、

この機能は、Batched Key Access(BKA)JoinアルゴリズムとMulti-Range Read(MRR)最適化を使用するクエリに適用され、データ・セットがbuffer poolやquery cacheにない場合のパフォーマンスを向上させます。 我々のテストでは、上記の条件を満たすクエリでコールドバッファプールを使用した場合、クエリのレイテンシが10倍以上向上しました。

（一部脱字を補いました。）

とある通り、Batched Key Access Joinアルゴリズム（BKAJ）とDisk-Sweep Multi-Range Read最適化（MRR）が有効な場合に、ストレージからキーを非同期に先読みして高速化する機能です。
※「ストレージから先読み」なので、すでにバッファプールにのっているデータには効果がありません。

こちらに使い方が書かれています（これを書いている現在、日本語の説明はありません）。

例えば、内部表が大きく、主キーではなくセカンダリインデックス（値の選択性が高いもの）で外部表（駆動表）と結合する場合に利用されるようです。

なお、MRRとBKAJについての説明は、以下を参照してください。

2. やってみる

Amazon Auroraのr4.largeインスタンスを使って、以下のような簡単なテーブルを作って試してみました。

テスト用DB・テーブル作成
mysql> CREATE DATABASE akptest;
Query OK, 1 row affected (0.02 sec)

mysql> USE akptest;
Database changed
mysql> CREATE TABLE in_tbl (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, second_key INT UNSIGNED NOT NULL, dummy_str1 VARCHAR(512) NOT NULL, dummy_str2 VARCHAR(512) NOT NULL, INDEX(second_key));
Query OK, 0 rows affected (0.03 sec)

（ここでデータを投入）

mysql> SELECT * FROM in_tbl ORDER BY id LIMIT 10;
+----+------------+----------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------+
| id | second_key | dummy_str1                                                                                                                       | dummy_str2                                               |
+----+------------+----------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------+
|  1 |      11543 | 27dd816369e81d8cbeb84c9f2843317562f7637635beb92e1bbbaa8302c7763e69a9cb24100a32c131d08a7e75d9b16b9f727d4c51d5aeed040e056ba6d83d2e | a9a35f19dbaccd6daef957c907e3a9175e1814956ff20c5aa04ea92b |
|  2 |      77796 | fb7d0a8c244d124e22ced2d4c562dd609225412eb5eac53f439c7531fa818d6bb0e024ffec17574d1228a8fdade6426aaec6aea1d01f6f955f3e9e76083b5757 | 10c6d91dc20cd7f3cbae23bbb0cc1a6025b66631bec3bf2759082e33 |
|  3 |      11985 | 9acef18b9bbf3d20e8be2a87251621597814a41bf421796333a2756a2a10bf91a95b569259251c22dbd77ed4b0cf5fceeefe469b33c8b8e54006e7ff9a868b38 | 9be137303a27ab7992b15317819a9bbf7ef61ad7892a24bcf14afb2c |
|  4 |      58312 | 5e124b41e7774a3dec6ffe8d01fa52d8430c03eec5c70767a3cb2ac9fb91baee0cd3eca1b79e77f98fc4dba9ce27d0357dc9e1b2484e955e9ea0083fc03fa8fc | 22f8e412ed0a429b7d8baf86bc6e88212c312df4beb9d1c6a72c5554 |
|  5 |      41368 | 278d90588aae7ab43e3d9787de05c03993a4d50deca37635d6e259301414d41dfc7038aca49e6fcba1804a43fabf2ff67a7040d8f43e31fe682514941c50f28b | d95b3735050c9cc9c19ff223a2b3971eff998b7fe52679e9b78323aa |
|  6 |      34999 | 369b4111710943b6fd42abaa265f98180244c1cbd4eeb9ab98632b542dda6d0da3cacbb1c9222ecfafeff887c0ad386927a36024fc78a4180d71360e718fbafc | 128210cd8ec6fec20944920b011d35265a02298861908bf2836b9cfd |
|  7 |      82949 | e3a09890718cab3ac14e89ae7d3ee2271dfeedb00c85ec3487d9d1a67e2ad9e689213e70e5fb4671a9df518f4f2219022f1d526d10c00a819a24431ad949c71b | 98b67e5701ecf774764234f58aad7dcb8ed97a9cfccefa278d3eed36 |
|  8 |      90949 | b2c7ef9bd34c5b8717c8b35da5f24a6379ebeaa85b74dbd85cc50f5a0344c777f215271e8d070f19af9144bdb65efa0daa34166b7cd2a10acc705b18d9288fa1 | 0627613c72cbf2d3169d88266b98ab3484881c548494276a6441ced2 |
|  9 |      36225 | 929f6208c2daba45eb6b7875c47982afe795fe9d3f65bb56d20aa54097aa421f20fafd82c0bfa8a3070a6e96ceb33a570633704aaf8dd6c66e3e3ce54c8248f8 | 39fb85064ba347d57c74257038067f15ad70ed1cb1e0105c59077aa1 |
| 10 |      42393 | 8f319ed1afa6b4cbbb454fc29004fcbd958da8c208542768aa1f880d80c50a2edbd60c6e357e2d95efee67de4cebfabe5fd86407191b620467e25fe2a3509529 | 4b37f95547557453e8fb72cb5fa3a61e4cc639e5211f0979cbeeb08b |
+----+------------+----------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM in_tbl ORDER BY id LIMIT 99991, 10;
+--------+------------+----------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------+
| id     | second_key | dummy_str1                                                                                                                       | dummy_str2                                               |
+--------+------------+----------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------+
|  99992 |      61662 | a852dfd8432590a59512a27bd5e93ebea9eb77229338f4538010956e36435885880874ee55662a376e922e3c2f20911663cd31ec7904fbf358ec4b1cc8924efa | 227cf1be09a223ee4210c5caa2ebcd447194f8fe06287724a335ad0d |
|  99993 |      49325 | 0ca01ca57c110c6d35e10c6a85d54ce24bda0576e762b07b93250bb7cc9762cb11bf94699329cae06fd52b2ff050f47fb3e51d277efcf58e7f3f72c998339545 | 726fa7a5d35e1e0f315578b3b0229f043dc3f00345d54dabb7d60717 |
|  99994 |      81115 | bed7ca48db97e5ba8bfd86fe3494e4feee59bf6c229f3ce6402b5e2256e64e274c92aa9c6ac9421f50283aa60665a803bdd3efd903f67ed0dae63d2a00679ca7 | 1c94eb5464e5aa36e00b8d0f885d6f9d93969e6e4588335ba3a4c322 |
|  99995 |      57368 | 1f19b898073fee1766343882836b13039d25d46021faec4b4a0567c60bf84cef6db3e4dfb0905f1bddf5a0ec0e474e134f6c3489d48b8dcb955d08001e2158dc | ef29e9395f1140d8c9b9d8187bc0b01a90223f904913ee74b80f72ec |
|  99996 |      99295 | 9f0f0874e556b0a28398555fd7e90eeecef2d946734da95d3ded9b17b5422bfb5d35e8fd840c229ed8a70c0c8e50394e628d2d1f2d33722776751c2ce7533ead | ccdb7889fb528b8ae362e22f481d1421c9f9816873faec641ada2fb4 |
|  99997 |      94684 | ae05a8284809db9486c94e1e3e819c6a5c26b0f5155629aef553f0e31d1f17d95e022b01473baed0f4a42d42f43a846a4ec88dad351d142af9f761dd5ecbbf1e | 31b4e2caeaad0896cd55e0db3b2f2b94fb64b5a12860eb627018fe88 |
|  99998 |      93726 | 114b780b8b2faba591c341828217b6481753a699320ddd86c07f7d626db3b07af93d88c8bc8b5c70a4ea68bb081b3020aef73af9771da0930b29e053d12b25b2 | 4f67886352c7bcf5d5cc0103da635e8c57bd15d90483fb85c284fb46 |
|  99999 |      41615 | 35721f1430ae6226f0053f057c3166e479f444bea288a1abffe3fc35f37a819a95e5534fa84486a157cc07300ecc1c8bb4aaf0ccff0e1adabaa349b6b839e5d4 | 5364acf2fbf41a393973beaa0ecf0bb902eb656c53487007d0e97c46 |
| 100000 |      98530 | 27430babdd01200f03d2be55facf4f67c751b6409e9241a6644a1aef7f13ebd3093e94ffe7fb9464459bc326776bc4384d33b152e97dc3c63930f492077c70e0 | ce743539ccae847f8e394c6696ffded101d741851c97fc6d7007f99d |
+--------+------------+----------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------+
9 rows in set (0.03 sec)

mysql> CREATE TABLE out_tbl (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, in_second_key INT UNSIGNED NOT NULL, INDEX(in_second_key));
Query OK, 0 rows affected (0.03 sec)

（ここでデータを投入）

mysql> SELECT * FROM out_tbl ORDER BY id LIMIT 10;
+----+---------------+
| id | in_second_key |
+----+---------------+
|  1 |         21895 |
|  2 |         16529 |
|  3 |         16962 |
|  4 |         35220 |
|  5 |         25216 |
|  6 |         20417 |
|  7 |         26437 |
|  8 |         70934 |
|  9 |         75358 |
| 10 |         63987 |
+----+---------------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM out_tbl ORDER BY id LIMIT 99991, 10;
+--------+---------------+
| id     | in_second_key |
+--------+---------------+
|  99992 |         76770 |
|  99993 |         78580 |
|  99994 |         62591 |
|  99995 |         77214 |
|  99996 |         98294 |
|  99997 |         59830 |
|  99998 |          4265 |
|  99999 |         41835 |
| 100000 |         96380 |
+--------+---------------+
9 rows in set (0.01 sec)

※しまった、LIMIT句のオフセットを間違えた（笑）。

まずは、AKPもBKAJも無効な状態のEXPLAINを出力してみます。

EXPLAIN（非AKP・非BKAJ）
mysql> EXPLAIN EXTENDED SELECT * FROM out_tbl o, in_tbl i WHERE o.in_second_key = i.second_key ORDER BY o.id LIMIT 20000;
+----+-------------+-------+-------+---------------+------------+---------+-------------------------+-------+----------+-------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref                     | rows  | filtered | Extra |
+----+-------------+-------+-------+---------------+------------+---------+-------------------------+-------+----------+-------+
|  1 | SIMPLE      | o     | index | in_second_key | PRIMARY    | 4       | NULL                    | 20000 |   502.86 | NULL  |
|  1 | SIMPLE      | i     | ref   | second_key    | second_key | 4       | akptest.o.in_second_key |     1 |   100.00 | NULL  |
+----+-------------+-------+-------+---------------+------------+---------+-------------------------+-------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                           |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `akptest`.`o`.`id` AS `id`,`akptest`.`o`.`in_second_key` AS `in_second_key`,`akptest`.`i`.`id` AS `id`,`akptest`.`i`.`second_key` AS `second_key`,`akptest`.`i`.`dummy_str1` AS `dummy_str1`,`akptest`.`i`.`dummy_str2` AS `dummy_str2` from `akptest`.`out_tbl` `o` join `akptest`.`in_tbl` `i` where (`akptest`.`i`.`second_key` = `akptest`.`o`.`in_second_key`) order by `akptest`.`o`.`id` limit 20000 |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

AKPとBKAJをONにしてみます。
なお、AKPはデフォルトでONになっていますが、有効にするためには、BKAJをONにするとともにMRRのコスト計算をOFFにする必要があります。

EXPLAIN（AKP・BKAJ）
mysql> SET @@session.aurora_use_key_prefetch=on;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @@session.optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN EXTENDED SELECT * FROM out_tbl o, in_tbl i WHERE o.in_second_key = i.second_key ORDER BY o.id LIMIT 20000;
+----+-------------+-------+-------+---------------+---------------+---------+-------------------------+--------+----------+-------------------------------------------------------------+
| id | select_type | table | type  | possible_keys | key           | key_len | ref                     | rows   | filtered | Extra                                                       |
+----+-------------+-------+-------+---------------+---------------+---------+-------------------------+--------+----------+-------------------------------------------------------------+
|  1 | SIMPLE      | o     | index | in_second_key | in_second_key | 4       | NULL                    | 100572 |   100.00 | Using index; Using temporary; Using filesort                |
|  1 | SIMPLE      | i     | ref   | second_key    | second_key    | 4       | akptest.o.in_second_key |      1 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+-------+-------+---------------+---------------+---------+-------------------------+--------+----------+-------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                           |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `akptest`.`o`.`id` AS `id`,`akptest`.`o`.`in_second_key` AS `in_second_key`,`akptest`.`i`.`id` AS `id`,`akptest`.`i`.`second_key` AS `second_key`,`akptest`.`i`.`dummy_str1` AS `dummy_str1`,`akptest`.`i`.`dummy_str2` AS `dummy_str2` from `akptest`.`out_tbl` `o` join `akptest`.`in_tbl` `i` where (`akptest`.`i`.`second_key` = `akptest`.`o`.`in_second_key`) order by `akptest`.`o`.`id` limit 20000 |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Extra列に**「Using join buffer (Batched Key Access with Key Prefetching)」**が現れました。

Auroraを再起動して、試してみます。

非AKP・非BKAJでSELECT
mysql> USE akptest;
No connection. Trying to reconnect...
Connection id:    4
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 @@session.optimizer_switch;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@session.optimizer_switch                                                                                                                                                                                                                                                                                                                       |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM out_tbl o, in_tbl i WHERE o.in_second_key = i.second_key ORDER BY o.id LIMIT 20000;

（中略）

| 19883 |         21145 | 29682 |      21145 | 1487c93a41431046ef6ad04f082caab10ad13a6605ebf929c430f55bef7eb2b4be8eff0969df3732b4f1a02d521143334576c1c1a72ed2610d442bbefd1605e2 | cd6c34bbe7a3bd16acaf7fe2db51192807402903b22d65305f8c3905 |
| 19885 |         35824 | 91239 |      35824 | 8e111ec20b0778cbaab25c5d0eca8d6d55cab14ba1553add0c82463ad4aafa84e0fef3e0b9d895691697f34ac74ebcf02bc52b0e60e818998fb32449e5aeaf46 | 7a9fdf164a773488ee24fb3a23bd6f1bed5447b387040529882cb08d |
| 19886 |         59565 | 55745 |      59565 | 7090fde6433734016fe6c23b6ed8ca6aae88a26f7dafe6b0fa818ce4490bc2366394d5fc1a928d27c2b5707c16ed6a8a5e47bc4a5c828f44469e9cdc92a81116 | 9a700ff29fdca6cfffc6ba8972883a17e7deec01d36eaf4eae0478e8 |
+-------+---------------+-------+------------+----------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------+
20000 rows in set (0.13 sec)

※Aurora再起動時にBKAJがOFF・MRRコスト計算がONになったので、AKPも働いていません。

続いて、AKP・BKAJを有効にして試してみます。

AKP・BKAJでSELECT
mysql> USE akptest;
No connection. Trying to reconnect...
Connection id:    4
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> SET @@session.aurora_use_key_prefetch=on;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @@session.optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@session.optimizer_switch;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@session.optimizer_switch                                                                                                                                                                                                                                                                                                                       |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=off,block_nested_loop=on,batched_key_access=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT * FROM out_tbl o, in_tbl i WHERE o.in_second_key = i.second_key ORDER BY o.id LIMIT 20000;

（中略）

| 19883 |         21145 | 29682 |      21145 | 1487c93a41431046ef6ad04f082caab10ad13a6605ebf929c430f55bef7eb2b4be8eff0969df3732b4f1a02d521143334576c1c1a72ed2610d442bbefd1605e2 | cd6c34bbe7a3bd16acaf7fe2db51192807402903b22d65305f8c3905 |
| 19885 |         35824 | 91239 |      35824 | 8e111ec20b0778cbaab25c5d0eca8d6d55cab14ba1553add0c82463ad4aafa84e0fef3e0b9d895691697f34ac74ebcf02bc52b0e60e818998fb32449e5aeaf46 | 7a9fdf164a773488ee24fb3a23bd6f1bed5447b387040529882cb08d |
| 19886 |         59565 | 55745 |      59565 | 7090fde6433734016fe6c23b6ed8ca6aae88a26f7dafe6b0fa818ce4490bc2366394d5fc1a928d27c2b5707c16ed6a8a5e47bc4a5c828f44469e9cdc92a81116 | 9a700ff29fdca6cfffc6ba8972883a17e7deec01d36eaf4eae0478e8 |
+-------+---------------+-------+------------+----------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------+
20000 rows in set (1.07 sec)

あれれ？遅くなりました。

…と、わざとらしく書きましたが、先に示したEXPLAINの結果を見れば原因は明らか。
BKAJがONになったことで、遅い実行計画が選択されてしまったのです。

非BKAJでは、外部表が主キーを使って（主キー順で）フェッチされたのに対し、BKAJでは「Using index; Using temporary; Using filesort」が示す通り、主キーではなくin_second_keyを使って主キーと異なる順番でフェッチされた後にソートされたのです。

※因みに、外部表のセカンダリインデックス「in_second_key」がなくても同様に非主キー順でフェッチされました。

念のため、AKPだけOFFにしてBKAJがONの状態で試してみます。

BKAJ（非AKP）でSELECT
mysql> SET @@session.aurora_use_key_prefetch=off;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @@session.optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN EXTENDED SELECT * FROM out_tbl o, in_tbl i WHERE o.in_second_key = i.second_key ORDER BY o.id LIMIT 20000;
+----+-------------+-------+-------+---------------+---------------+---------+-------------------------+--------+----------+----------------------------------------------+
| id | select_type | table | type  | possible_keys | key           | key_len | ref                     | rows   | filtered | Extra                                        |
+----+-------------+-------+-------+---------------+---------------+---------+-------------------------+--------+----------+----------------------------------------------+
|  1 | SIMPLE      | o     | index | in_second_key | in_second_key | 4       | NULL                    | 100572 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | i     | ref   | second_key    | second_key    | 4       | akptest.o.in_second_key |      1 |   100.00 | Using join buffer (Batched Key Access)       |
+----+-------------+-------+-------+---------------+---------------+---------+-------------------------+--------+----------+----------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                           |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `akptest`.`o`.`id` AS `id`,`akptest`.`o`.`in_second_key` AS `in_second_key`,`akptest`.`i`.`id` AS `id`,`akptest`.`i`.`second_key` AS `second_key`,`akptest`.`i`.`dummy_str1` AS `dummy_str1`,`akptest`.`i`.`dummy_str2` AS `dummy_str2` from `akptest`.`out_tbl` `o` join `akptest`.`in_tbl` `i` where (`akptest`.`i`.`second_key` = `akptest`.`o`.`in_second_key`) order by `akptest`.`o`.`id` limit 20000 |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

（ここで再起動）

mysql> USE akptest;
No connection. Trying to reconnect...
Connection id:    4
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> SET @@session.aurora_use_key_prefetch=off;
Query OK, 0 rows affected (0.01 sec)

mysql> SET @@session.optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM out_tbl o, in_tbl i WHERE o.in_second_key = i.second_key ORDER BY o.id LIMIT 20000;

（中略）

| 19883 |         21145 | 29682 |      21145 | 1487c93a41431046ef6ad04f082caab10ad13a6605ebf929c430f55bef7eb2b4be8eff0969df3732b4f1a02d521143334576c1c1a72ed2610d442bbefd1605e2 | cd6c34bbe7a3bd16acaf7fe2db51192807402903b22d65305f8c3905 |
| 19885 |         35824 | 91239 |      35824 | 8e111ec20b0778cbaab25c5d0eca8d6d55cab14ba1553add0c82463ad4aafa84e0fef3e0b9d895691697f34ac74ebcf02bc52b0e60e818998fb32449e5aeaf46 | 7a9fdf164a773488ee24fb3a23bd6f1bed5447b387040529882cb08d |
| 19886 |         59565 | 55745 |      59565 | 7090fde6433734016fe6c23b6ed8ca6aae88a26f7dafe6b0fa818ce4490bc2366394d5fc1a928d27c2b5707c16ed6a8a5e47bc4a5c828f44469e9cdc92a81116 | 9a700ff29fdca6cfffc6ba8972883a17e7deec01d36eaf4eae0478e8 |
+-------+---------------+-------+------------+----------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------+
20000 rows in set (0.75 sec)

…AKPがOFFのほうが速かった…（誤差の範囲だとは思いますが）。
どうやら、この程度のデータ量／結合テーブル数／SELECT等の条件では、キーの非同期先読み効果はないようです。

なお、ここまでの例では、外部表の2列目（≠主キー順）と内部表のセカンダリインデックス列の等結合で試しました。
これを、外部表の主キーと内部表のセカンダリインデックス列の等結合にしてみると…

外部表が主キー順／準備
mysql> CREATE TABLE out_tbl2 (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, prod_name VARCHAR(32) NOT NULL);
Query OK, 0 rows affected (0.04 sec)

（ここでデータ投入）

mysql> SELECT * FROM out_tbl2 ORDER BY id LIMIT 3;
+----+----------------------------------+
| id | prod_name                        |
+----+----------------------------------+
|  1 | 44505bcc9fa6e767d22f6c5e7e9f3f54 |
|  2 | 1e875c61fb1d80acd112f13e00f334b6 |
|  3 | 010ec3880045d7604565a922ddbdcaba |
+----+----------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM out_tbl2 ORDER BY id LIMIT 99997, 3;
+--------+----------------------------------+
| id     | prod_name                        |
+--------+----------------------------------+
|  99998 | c7317e0f1afbc0c1e7ebd0630039125c |
|  99999 | a7acab583f0cbf398441bb3b4b4be04f |
| 100000 | dcf21a2cd3a06a1cd9ba89b20f6ad4dd |
+--------+----------------------------------+
3 rows in set (0.02 sec)

mysql> EXPLAIN EXTENDED SELECT * FROM out_tbl2 o, in_tbl i WHERE o.id = i.second_key ORDER BY o.id LIMIT 20000;
+----+-------------+-------+-------+---------------+------------+---------+--------------+-------+----------+-------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref          | rows  | filtered | Extra |
+----+-------------+-------+-------+---------------+------------+---------+--------------+-------+----------+-------+
|  1 | SIMPLE      | o     | index | PRIMARY       | PRIMARY    | 4       | NULL         | 20000 |   499.59 | NULL  |
|  1 | SIMPLE      | i     | ref   | second_key    | second_key | 4       | akptest.o.id |     1 |   100.00 | NULL  |
+----+-------------+-------+-------+---------------+------------+---------+--------------+-------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> SET @@session.aurora_use_key_prefetch=on;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @@session.optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN EXTENDED SELECT * FROM out_tbl2 o, in_tbl i WHERE o.id = i.second_key ORDER BY o.id LIMIT 20000;
+----+-------------+-------+------+---------------+------------+---------+--------------+-------+----------+-------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key        | key_len | ref          | rows  | filtered | Extra                                                       |
+----+-------------+-------+------+---------------+------------+---------+--------------+-------+----------+-------------------------------------------------------------+
|  1 | SIMPLE      | o     | ALL  | PRIMARY       | NULL       | NULL    | NULL         | 99918 |   100.00 | Using temporary; Using filesort                             |
|  1 | SIMPLE      | i     | ref  | second_key    | second_key | 4       | akptest.o.id |     1 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+-------+------+---------------+------------+---------+--------------+-------+----------+-------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

※内部表は同じ設計で、レコードを入れ直しています。

ちょっと嫌なキーワードが…。
Auroraを再起動してテスト開始。

外部表が主キー順／非AKP・非BKAJ
mysql> USE akptest;
No connection. Trying to reconnect...
Connection id:    4
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> SET @@session.aurora_use_key_prefetch=off;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @@session.optimizer_switch='mrr=on,mrr_cost_based=on,batched_key_access=off';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM out_tbl2 o, in_tbl i WHERE o.id = i.second_key ORDER BY o.id LIMIT 20000;

（中略）

| 20003 | 3106c58c1248e2e6930c15a196bce544 | 57365 |      20003 | 6c18e3978941a0a702451c7bbf45035e4eab446149a487cbe1e39f28ff6c1063066d60a3a8a43950e065e562bcd570c58adeb51953e909516dba49e716b3523d | 6ab794a493b8606595685663d3eef8541c3b8c4296a63733443fdbbc |
| 20004 | d512e3040bc22345d4a44ed56a6c33c9 | 98111 |      20004 | f1e30b5a00b80ecc2524d7292d33b955fbb48aa865b90e54cc945760e5b98c64845656a689ebccd06396ccb9799c526723fb9feeea8c3d0438a219e93ade260b | cd6afaa865c6763b89cddf26303e796484e2ab731e6c79af60d58619 |
+-------+----------------------------------+-------+------------+----------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------+
20000 rows in set (0.17 sec)

Auroraを再起動して、AKPをON。

外部表が主キー順／AKP・BKAJ
mysql> USE akptest;
No connection. Trying to reconnect...
Connection id:    4
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> SET @@session.aurora_use_key_prefetch=on;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @@session.optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM out_tbl2 o, in_tbl i WHERE o.id = i.second_key ORDER BY o.id LIMIT 20000;

（中略）

| 20003 | 3106c58c1248e2e6930c15a196bce544 | 57365 |      20003 | 6c18e3978941a0a702451c7bbf45035e4eab446149a487cbe1e39f28ff6c1063066d60a3a8a43950e065e562bcd570c58adeb51953e909516dba49e716b3523d | 6ab794a493b8606595685663d3eef8541c3b8c4296a63733443fdbbc |
| 20004 | d512e3040bc22345d4a44ed56a6c33c9 | 98111 |      20004 | f1e30b5a00b80ecc2524d7292d33b955fbb48aa865b90e54cc945760e5b98c64845656a689ebccd06396ccb9799c526723fb9feeea8c3d0438a219e93ade260b | cd6afaa865c6763b89cddf26303e796484e2ab731e6c79af60d58619 |
+-------+----------------------------------+-------+------------+----------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------+
20000 rows in set (1.36 sec)

うーん…。
Auroraを再起動し、BKAJのみもやってみます。

外部表が主キー順／BKAJのみ
mysql> USE akptest;
No connection. Trying to reconnect...
Connection id:    4
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> SET @@session.aurora_use_key_prefetch=off;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @@session.optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM out_tbl2 o, in_tbl i WHERE o.id = i.second_key ORDER BY o.id LIMIT 20000;

（中略）

| 20003 | 3106c58c1248e2e6930c15a196bce544 | 57365 |      20003 | 6c18e3978941a0a702451c7bbf45035e4eab446149a487cbe1e39f28ff6c1063066d60a3a8a43950e065e562bcd570c58adeb51953e909516dba49e716b3523d | 6ab794a493b8606595685663d3eef8541c3b8c4296a63733443fdbbc |
| 20004 | d512e3040bc22345d4a44ed56a6c33c9 | 98111 |      20004 | f1e30b5a00b80ecc2524d7292d33b955fbb48aa865b90e54cc945760e5b98c64845656a689ebccd06396ccb9799c526723fb9feeea8c3d0438a219e93ade260b | cd6afaa865c6763b89cddf26303e796484e2ab731e6c79af60d58619 |
+-------+----------------------------------+-------+------------+----------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------+
20000 rows in set (0.97 sec)

…やっぱりダメですね。速度が「BKAJのみ＞AKP有効」であることも変わりません。

3. まとめ

「機能をONにしておけば必ず速くなるものでもない」のは当然のこと、「状況によってはかえって遅くなることもある」ので、有効化するかどうかはよく考えたほうが良さそうです。

本家MySQL 5.6がデフォルトでBKAJをOFFにしているのもわかるような気がします。

特に、今どきのRDBは**「日常的に使うデータはほぼすべてメモリ（MySQLでいうところのバッファプール）に収まることを前提に使うもの」（バッファプールが温まってからサービス投入すべし）**と言っても過言ではないので、ストレージ（ディスク）からの読み込みを高速化する機能の優先度は低い場合が多いのでは？と思います。

なので、設定上はOFFにしておいて、「ここなら効く！」ということが明らかなところでセッション変数を書き替えて有効化する使い方が良いのではないかと思います。
※ヒント句（オプティマイザヒント）に対応すると良いのですが、MySQL 5.7ベースにならないと…。

2017/11/06追記：
どのようなケースで有効に働くのかさらに調査してみました（そして返り討ちに…）。

2017/11/25追記：
最終的には正しく動作していることが確認できました。

【おまけ】
Amazon Aurora関連投稿記事へのリンクを集めました。

3
5
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?