かっこうのブログ

何かしら飲んでるエンジニア

MySQLの準結合戦略とサブクエリに対するロックの挙動

前置き

MySQL5.6から搭載された準結合戦略とサブクエリのロックの関係性ってどうなってるの？と気になったので 実際にクエリを動かしながら確認した記事になります。

実験環境

  • mysql8.0
  • Sequel Pro
  • MySQLのサンプルデータ(world) 導入方法はこちらを参照)

MySQL :: Other MySQL Documentation

要約

サブクエリをロックすると、サブクエリの対象のインデックスしかロックされないという挙動が実現できる。

準結合戦略でJOINとして評価されても、サブクエリと同様にサブクエリのインデックスしかロックされない。

1. サブクエリのロックについて

サブクエリでロックをした時の挙動についてご存知でしょうか？

サブクエリでロックをした際、ロックの対象をサブクエリの対象となったインデックスのみに限定することができます。

例えば、言語がJapaneseである国情報が欲しい場合を考えてみます。

公式のものをそのまま使っているので、countrylanguage.Languageとcountry.Codeは共にindexが効いています。

start transaction;
SELECT *
FROM `country` 
WHERE `Code` IN (
    SELECT `CountryCode`
    FROM `countrylanguage`
    WHERE `Language` = 'Japanese'
    FOR UPDATE
);

この時、別のタブを開いてレコードをいじってみるとcountrylanguageテーブルのLanguageがJapaneseのレコードのみがロックされており、countryのレコードは一切ロックされていません。

ちなみに、上記のクエリをJOINで実現させた場合、countryの一部のレコードもロックされてしまいます。

start transaction;
SELECT `country`.`*` 
FROM `country` 
INNER JOIN `countrylanguage` ON `country`.`Code` = `countrylanguage`.`CountryCode`
WHERE `countrylanguage`.`Language` = 'Japanese'
FOR UPDATE;

このように、サブクエリを使うことで親のテーブルはロックしたくないけど子のテーブルのみロックしたいといった場面を解決することができます。

2. セミジョインについて

ここで疑問が1つ。

セミジョインの時、このサブクエリへのロックはどのように扱われるのか

セミジョインとは、MySQL5.6から搭載された機能で、以下の条件を満たす時にサブクエリをJOINとして評価して実行するというものです(参照)。

  1. INまたは=ANYを使用したサブクエリであること
  2. 単一のSELECT文でUNIONを使用していないこと
  3. Group byなどの集約関数を含めないこと
  4. LIMITを使用したORDER BYがないこと
  5. 外部テーブルとおよび内部テーブルの合計数が結合で許可されている最大テーブル数より少ないこと

第43回　MySQLの準結合（セミジョイン）について

評価自体はサブクエリをJOIN句に変換するように見えているので、これはどう動くのでしょうか？実際にサブクエリの動作を見ながら見ていきます。

3. セミジョインの動作を確認する

まずは、optimizer_switchから確認していきます。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.8.5.2 切り替え可能な最適化の制御

1.の時は、semijoinをoffにして実行していました。

>SET optimizer_switch='semijoin=off';
>SELECT @@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=off
loosescan=on
firstmatch=on
duplicateweedout=on
subquery_materialization_cost_based=on
use_index_extensions=on
condition_fanout_filter=on
derived_merge=on

続いて、サブクエリをつかったクエリを実行してみます。

explain
    -> SELECT *
    -> FROM `country` 
    -> WHERE `Code` IN (
    -> SELECT `CountryCode`
    -> FROM `countrylanguage`
    -> WHERE `Language` = 'Japanese'
    -> );
+----+-------------+-----------------+------------+-------+---------------------+-------------+---------+------+------+----------+--------------------------+
| id | select_type | table           | partitions | type  | possible_keys       | key         | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------------+------------+-------+---------------------+-------------+---------+------+------+----------+--------------------------+
|  1 | PRIMARY     | country         | NULL       | ALL   | NULL                | NULL        | NULL    | NULL |  239 |   100.00 | Using where              |
|  2 | SUBQUERY    | countrylanguage | NULL       | index | PRIMARY,CountryCode | CountryCode | 12      | NULL |  984 |    10.00 | Using where; Using index |
+----+-------------+-----------------+------------+-------+---------------------+-------------+---------+------+------+----------+--------------------------+

> show warnings;
| Note  | 1003 | /* select#1 */ select `world`.`country`.`Code` AS `Code`,`world`.`country`.`Name` AS `Name`,`world`.`country`.`Continent` AS `Continent`,`world`.`country`.`Region` AS `Region`,`world`.`country`.`SurfaceArea` AS `SurfaceArea`,`world`.`country`.`IndepYear` AS `IndepYear`,`world`.`country`.`Population` AS `Population`,`world`.`country`.`LifeExpectancy` AS `LifeExpectancy`,`world`.`country`.`GNP` AS `GNP`,`world`.`country`.`GNPOld` AS `GNPOld`,`world`.`country`.`LocalName` AS `LocalName`,`world`.`country`.`GovernmentForm` AS `GovernmentForm`,`world`.`country`.`HeadOfState` AS `HeadOfState`,`world`.`country`.`Capital` AS `Capital`,`world`.`country`.`Code2` AS `Code2` from `world`.`country` where <in_optimizer>(`world`.`country`.`Code`,`world`.`country`.`Code` in ( <materialize> (/* select#2 */ select `world`.`countrylanguage`.`CountryCode` from `world`.`countrylanguage` where (`world`.`countrylanguage`.`Language` = 'Japanese') ), <primary_index_lookup>(`world`.`country`.`Code` in <temporary table> on <auto_key> where ((`world`.`country`.`Code` = `materialized-subquery`.`CountryCode`))))) |

explainを見てもsubqueryになっています。また、show warningsをみてもmaterializeが作られておりサブクエリとして動作していることがわかります。

続いて、セミジョインを有効にしてクエリを実行して確認してみます。

>SET optimizer_switch='semijoin=on';
>SELECT @@optimizer_switch;
index_merge=on
index_merge_union=onindex_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
duplicateweedout=on
subquery_materialization_cost_based=on
use_index_extensions=on
condition_fanout_filter=on
derived_merge=on

まずは、サブクエリから実行してみます。

explain含め色々変わっています。show warningsを使うとJOIN句が使われていることもわかります。これがセミジョインの効果です。次に本当にJOIN句として評価されているかをみるためにJOIN句を使ったクエリを見てみます。

explain
    -> SELECT *
    -> FROM `country` 
    -> WHERE `Code` IN (
    -> SELECT `CountryCode`
    -> FROM `countrylanguage`
    -> WHERE `Language` = 'Japanese'
    -> );
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------------------+------+----------+--------------------------+
| id | select_type | table           | partitions | type   | possible_keys       | key         | key_len | ref                               | rows | filtered | Extra                    |
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------------------+------+----------+--------------------------+
|  1 | SIMPLE      | countrylanguage | NULL       | index  | PRIMARY,CountryCode | CountryCode | 12      | NULL                              |  984 |    10.00 | Using where; Using index |
|  1 | SIMPLE      | country         | NULL       | eq_ref | PRIMARY             | PRIMARY     | 12      | world.countrylanguage.CountryCode |    1 |   100.00 | NULL                     |
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------------------+------+----------+--------------------------+
> show warnings;
| Note  | 1003 | /* select#1 */ select `world`.`country`.`Code` AS `Code`,`world`.`country`.`Name` AS `Name`,`world`.`country`.`Continent` AS `Continent`,`world`.`country`.`Region` AS `Region`,`world`.`country`.`SurfaceArea` AS `SurfaceArea`,`world`.`country`.`IndepYear` AS `IndepYear`,`world`.`country`.`Population` AS `Population`,`world`.`country`.`LifeExpectancy` AS `LifeExpectancy`,`world`.`country`.`GNP` AS `GNP`,`world`.`country`.`GNPOld` AS `GNPOld`,`world`.`country`.`LocalName` AS `LocalName`,`world`.`country`.`GovernmentForm` AS `GovernmentForm`,`world`.`country`.`HeadOfState` AS `HeadOfState`,`world`.`country`.`Capital` AS `Capital`,`world`.`country`.`Code2` AS `Code2` from `world`.`countrylanguage` join `world`.`country` where ((`world`.`country`.`Code` = `world`.`countrylanguage`.`CountryCode`) and (`world`.`countrylanguage`.`Language` = 'Japanese')) |

上記のサブクエリをINNER JOINに書き換えたものです。全く同じクエリになっています。

explain
SELECT `country`.`*` 
FROM `country` 
INNER JOIN `countrylanguage` ON `country`.`Code` = `countrylanguage`.`CountryCode`
WHERE `countrylanguage`.`Language` = 'Japanese';
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------------------+------+----------+--------------------------+
| id | select_type | table           | partitions | type   | possible_keys       | key         | key_len | ref                               | rows | filtered | Extra                    |
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------------------+------+----------+--------------------------+
|  1 | SIMPLE      | countrylanguage | NULL       | index  | PRIMARY,CountryCode | CountryCode | 12      | NULL                              |  984 |    10.00 | Using where; Using index |
|  1 | SIMPLE      | country         | NULL       | eq_ref | PRIMARY             | PRIMARY     | 12      | world.countrylanguage.CountryCode |    1 |   100.00 | NULL                     |
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------------------+------+----------+--------------------------+

> show warnings;
| Note  | 1003 | /* select#1 */ select `world`.`country`.`Code` AS `Code`,`world`.`country`.`Name` AS `Name`,`world`.`country`.`Continent` AS `Continent`,`world`.`country`.`Region` AS `Region`,`world`.`country`.`SurfaceArea` AS `SurfaceArea`,`world`.`country`.`IndepYear` AS `IndepYear`,`world`.`country`.`Population` AS `Population`,`world`.`country`.`LifeExpectancy` AS `LifeExpectancy`,`world`.`country`.`GNP` AS `GNP`,`world`.`country`.`GNPOld` AS `GNPOld`,`world`.`country`.`LocalName` AS `LocalName`,`world`.`country`.`GovernmentForm` AS `GovernmentForm`,`world`.`country`.`HeadOfState` AS `HeadOfState`,`world`.`country`.`Capital` AS `Capital`,`world`.`country`.`Code2` AS `Code2` from `world`.`country` join `world`.`countrylanguage` where ((`world`.`country`.`Code` = `world`.`countrylanguage`.`CountryCode`) and (`world`.`countrylanguage`.`Language` = 'Japanese')) |

では、最後にセミジョインが有効の状態でサブクエリにFOR UPDATEをつけて実行してみます。countrylanguageテーブルは綺麗にロックされていますね。 では、counrtyテーブルはというとこちらは通常のJOIN句と違いロックされていません。

すなわち、セミジョイン戦略でJOIN句として評価されていてもFOR UPDATEのロック自体は変わることがありません。

まとめ

準結合戦略でJOINとして評価されても、サブクエリと同様にサブクエリのインデックスしかロックされません。

裏でどのような処理がされているのかが気になりますが、うまく後方互換性が担保されているようです。これにより、サブクエリを使ったクエリが非常に改善されるのでMySQL5.6未満を使っている方は、バージョンアップの恩恵が多そうですね。

ちなみにセミジョイン戦略はデフォルトでONになっているので、サブクエリを使ったクエリのEXPLAINが予想と違うとなった方はぜひご確認ください。

MySQLでユーザーの一覧を確認する