こまぶろ

技術のこととか仕事のこととか。

pom.xmlの特定の要素をxmllintで書き換える

CIからpom.xmlを自動で更新するような仕組みを入れようと思って、pom.xmlを書き換える方法を調べたのでメモ。

ポイントとしては、

  • 名前空間付きのもののときは *[local-name()='hoge'] を使う
  • 同名のタグが複数ある場合に特定のノードのものを選択するには and を使う

になる。

例題

以下のようなpom.xmlで、 maven-compiler-plugin のバージョンを変えたい。

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.example</groupId>
    <artifactId>junit5-jupiter-starter-maven</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>${maven.compiler.source}</maven.compiler.target>
    </properties>

    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.junit</groupId>
                <artifactId>junit-bom</artifactId>
                <version>5.9.1</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <dependencies>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
            </plugin>
            <plugin>
                <artifactId>maven-surefire-plugin</artifactId>
                <version>3.0.0-M6</version>
            </plugin>
        </plugins>
    </build>

</project>

(コードは https://github.com/junit-team/junit5-samples/blob/f7029d7b9e5c73a8f3615a761d51e6e756e3e09d/junit5-jupiter-starter-maven/pom.xml のもの)

対応方法

たとえば以下のように記述すればよい。

VERSION=3.9.0
xmllint --shell pom.xml << EOF
cd /*[local-name()='project']/*[local-name()='build']/*[local-name()='plugins']/*[local-name()='plugin' and *[local-name()='artifactId']/text() = 'maven-compiler-plugin']/*[local-name()='version']
set $VERSION
save
EOF

名前空間付きのタグに対しては local-name() を使う

タグ名は実際には名前空間付きなので、テキストファイル上の名前を使って /project/build/... のように指定することはできない。local-name() を使って対象のタグを指定する必要がある。

同名のタグが複数ある場合に特定のノードのものを選択するには and を使う

書き換えたい要素は <plugins> 配下に複数ある <plugin> のうちの特定のものなので、条件を追加して指定する必要がある。以下の部分。

*[local-name()='plugin' and *[local-name()='artifactId']/text() = 'maven-compiler-plugin']

<plugin> で、かつ <artifactId> の内容が maven-compiler-plugin であるものを選択するためにこのような記述をしている。

local-name() を何度も書きたくないときは

何回も local-name() を書くのは見た目的にもわかりづらくなるので、ルートノードからの厳密なパスを指定せずに // を使って指定することもできる。

VERSION=3.9.0
xmllint --shell pom.xml << EOF
cd //*[local-name()='plugin' and *[local-name()='artifactId']/text() = 'maven-compiler-plugin']/*[local-name()='version']
set $VERSION
save
EOF

参考

atmarkit.itmedia.co.jp

techblog.zozo.com

MySQLの実行計画で Using intersect (...)な index_merge には要注意

実行計画での typeカラムに index_mergeが出たら

MySQLで、WHERE句で複数の条件をANDやORで繋いでいるクエリの実行計画をとると、 type カラムに index_merge というのが出ることがある。これはインデックスマージ最適化というものが使われるということを示している。

dev.mysql.com

インデックスマージ最適化とは

インデックスマージ最適化とは、本来は単一のクエリではインデックスが1つしか利用できないところ、複数のインデックスを使ってそれらをマージすることで最終的な結果を得るというアルゴリズムを使う最適化だ。

これに対し、MySQL 5.0 以降のバージョンでは、クエリが両方のインデックスを使用でき、それらを同時にスキャンして結果をマージできる。OR条件の和集合、AND条件の積集合、2つの組み合わせの積集合の和集合という3種類のアルゴリズムがある。 (『実践ハイパフォーマンスMySQL 第3版』168ページ)

3種類のアルゴリズムのうちのどれが使われるかは、実行計画のExtra カラムで知ることができる。

  • Using union(...): 和集合アクセスアルゴリズム
  • Using intersect(...): 共通集合アクセスアルゴリズム
  • Using sort_union(...) : ソート和集合アクセスアルゴリズム

「最適化」だから安心していい?

これらは「最適化」なので、一見すると「ありがとうMySQL」となりそうなのだが、実際にはその名に反して、クエリに問題があることを示唆しているケースがある。

インデックスマージ戦略が非常にうまくいくこともあるが、実際には、テーブルがうまくインデックス付けされていないことの裏返しであることのほうが多い。

(『実践ハイパフォーマンスMySQL 第3版』169ページ)

たとえば、 Using intersect(...) であれば、これはWHERE句で指定しているすべてのカラムを含んだ複合インデックスが作られているべき場面に個々のカラムへのインデックスしかない場合に出ていることがある。具体例として、以下のようなクエリを考える。

SELECT *
FROM products
WHERE category_id = 1
AND publication_year = '202209';

このクエリの実行計画で、 Using intersect(...) となっているとしたら、それは category_idpublication_year のそれぞれのカラムにインデックスが付いている場合だろう。その場合、個々のインデックスをスキャンし、category_id のインデックスについては category_id = 1 だが publication_year が 202209 ではない部分、publication_year のインデックスについては publication_year = '202209' だが category_id が 1 ではない部分も、それぞれ一旦(インデックススキャンの結果として)取得され、その後で共通部分をとって(マージして )、テーブル本体にアクセスすることになる。

それぞれのインデックスの選択性が高ければ大きな問題にはならないが、選択性の低いインデックスの場合は、途中のインデックススキャンの段階で無駄なリソースを消費し、パフォーマンスも悪くなってしまう危険性がある。

このクエリの場合、category_id, publication_year のような複合インデックスがあれば、最初からインデックス上の category_id = 1 かつ publication_year = '202209' の部分のみを使ってテーブルにアクセスでき、パフォーマンスの改善につながるケースがある。

Using intersect(...) を見過ごしてしまいやすい理由

ここまで書いたことは、実行計画の typeカラムを見て index_merge になっていることに「おや?」と思えれば調べられるだろうが、あまり詳しくない人は見逃してしまう危険性がある。

実行計画を見て、 typeALL になっていたり、 rows に巨大な数値が出ていたりすれば、「このクエリはまずそうだ」と思いやすいが、 index_mergeUsing intersect(...) となる場合、 rows カラムに出る数値はあくまで「最終的に読み込まれる行数の見積り」なので、先述したインデックスマージの途中で読み取られるインデックスの範囲のうちマージ時に(共通部分ではなかったために)除外される部分については rows カラムの数値には含まれない。 rows カラムに小さな値が出ているからといって、パフォーマンスが良いとは限らないというわけだ。

www.percona.com

実行計画を見るというのは、ある程度DBを触っていると基本的な仕草になってくるが、どこまで細かく見られるかというのはかなり違いがある(自分もとても詳しいわけではない)と思われ、「あれ?このクエリ?」と思うことがあったら都度勉強しなおしていきたい。

S3へのソースコードの配置をトリガーにしてCodePipelineを動かす設定をTerraformで書く

Amazon S3 へのソースコードの配置をトリガーにして AWS CodePipeline を動かす方法はいくつかあります。

  • S3 を CodePipeline からポーリングして変更を検知する
  • S3 への操作を CloudTrail で検知してイベントを発行して EventBridge 経由で CodePipeline を動作させる
  • S3 からイベントを EventBridge に送って CodePipeline を動作させる

それぞれについて Terraform で書く場合の要点をまとめておきます。

なお、Terraform AWS Provider はv3を前提にした記述になっています(3番目の方法についてはv3.74.0以上が必要です)。v4などを使う場合は適宜読み替えてください。

また、CodePipeline を動かす部分に焦点を置いているため、

  • S3 にどうソースコードを配置するか
  • CodePipeline をどう設定するか(トリガーされたあとの挙動)

については省略するか簡易にしか記述しません。

S3 を CodePipeline からポーリングして変更を検知する

1番目の方法で、以前から利用できる方法です。勝手にポーリングしてくれるので簡単。

PollForSourceChanges を true にすることで有効化できます。

たとえば以下のように記述します。

resource "aws_codepipeline" "my_pipeline" {
  name     = "my-pipeline"
  role_arn = aws_iam_role.my_pipeline_role.arn

  artifact_store {
    location = aws_s3_bucket.my_source.bucket
    type     = "S3"
  }

  stage {
    name = "Source"
    action {
      category = "Source"
      configuration = {
        PollForSourceChanges = "true" // ここが肝
        S3Bucket             = aws_s3_bucket.my_source_s3.bucket
        S3ObjectKey          = "my_artifact.zip"
      }
      name             = "Source"
      output_artifacts = ["SourceArtifact"]
      owner            = "AWS"
      provider         = "S3"
      run_order        = "1"
      version          = "1"
    }
  }
  stage {
    name = "Deploy"
    action {
      category = "Deploy"
      configuration = {
        ApplicationName     = aws_codedeploy_app.my_deployment.name
        DeploymentGroupName = aws_codedeploy_deployment_group.my_deployment.deployment_group_name
      }
      input_artifacts = ["SourceArtifact"]
      name            = "Deploy"
      owner           = "AWS"
      provider        = "CodeDeploy"
      run_order       = "1"
      version         = "1"
    }
  }
}

S3 への操作を CloudTrail で検知してイベントを発行して EventBridge 経由で CodePipeline を動作させる

2番目のやり方です。前掲の公式ドキュメントで、1番目の方法よりも推奨されている方法です。イベントドリブンなので、ポーリングによる方法に比べて迅速に CodePipeline を起動することができます。

PollForSourceChanges

Required: No

PollForSourceChanges controls whether CodePipeline polls the Amazon S3 source bucket for source changes. We recommend that you use CloudWatch Events and CloudTrail to detect source changes instead. For more information about configuring CloudWatch Events, see Update pipelines for push events (Amazon S3 source) (CLI) or Update pipelines for push events (Amazon S3 source) (AWS CloudFormation template).

難点は設定が面倒なことで、AWS CloudTrail という典型的には監査などの目的で利用されるサービスを間に噛ませる必要があります。以下のような流れになります。

  • S3 上のオブジェクトへの操作
  • CloudTrail が検知
  • CloudTrail がイベントを発行
  • EventBridge で定義しておいたルールに基づいて CodePipeline を起動

こちらの方法を採用する場合は、二重でパイプラインが起動するのを避けるため、先述の PollForSourceChanges を false に設定しておく必要があります。

1番目の方法で用いるリソースにさらに追加で以下のリソースが必要になります。

  • CloudTrail aws_cloudtrail
  • CloudTrail のログ出力先となる S3 バケット aws_s3_bucket
    • バケットポリシーで CloudTrail からの読み書きを許可する必要があります
    • 説明は省略します
  • EventBridge のルール定義 aws_cloudwatch_event_rule
  • EventBridge のイベントターゲット定義 aws_cloudwatch_event_target
  • EventBridge が CodePipeline を起動するためのIAMロール aws_iam_role
    • 説明は省略します

CloudTrail はたとえば以下のように記述します。

resource "aws_cloudtrail" "start_my_pipeline" {
  name           = "start-my-pipeline"
  s3_bucket_name = aws_s3_bucket.start_codepipeline.id // CloudTrailがログを出力する先のS3バケットを指定

  event_selector {
    read_write_type = "WriteOnly"

    data_resource {
      type   = "AWS::S3::Object"
      values = ["arn:aws:s3:::my_source_s3"] // ソースコードを配置する S3 を指定する
    }
  }
}

EventBridge のルールとターゲットはそれぞれ以下のように記述します。

resource "aws_cloudwatch_event_rule" "start_my_pipeline" {
  name = "start-pipeline-from-s3-rule"
  event_pattern = <<EOF
{
  "source": ["aws.s3"],
  "detail-type": ["AWS API Call via CloudTrail"],
  "detail": {
    "eventSource": ["s3.amazonaws.com"],
    "eventName": ["PutObject", "CompleteMultipartUpload", "CopyObject"],
    "requestParameters": {
      "bucketName": ["${aws_s3_bucket.my_source_s3.bucket}"],
      "key": ["my_artifact.zip"]
    }
  }
}
EOF
}
resource "aws_cloudwatch_event_target" "start_pipeline" {
  rule     = aws_cloudwatch_event_rule.start_my_pipeline.name
  arn      = aws_codepipeline.my_pipeline.arn
  role_arn = aws_iam_role.my_pipeline_role_for_event_bridge.arn
}

S3からイベントを EventBridge に送って CodePipeline を動作させる

3番目のやり方です。2021年11月のAWSのアップデートでサポートされた方法です。こちらでも PollForSourceChanges は false に設定しておく必要があります。

S3 へのソースコードの配置のイベントを CloudTrail を介することなく直接 EventBridge に送ることができるようになりました。これにより、2番目の方法で必要だった CloudTrail 関係のリソースが不要になります。 CloudTrall は細かく設定ができるのですが、リソースの数などについての制約事項があるため、個人的にはあまりパイプラインのために利用するのは嬉しくないのでは?と感じています。とはいえ、 EventBridge への S3 からのイベント発行が CloudTrail でやる場合に比べて増えるというデメリットもこちらにはあります。

1番目の方法との差分として必要なリソースは、以下になります。

  • EventBridge のルール定義 aws_cloudwatch_event_rule
    • 少し設定の書き方が変わります
  • EventBridge のイベントターゲット定義 aws_cloudwatch_event_target
    • 2番目の方法と同じ
  • EventBridge が CodePipeline を起動するためのIAMロール aws_iam_role
    • 2番目の方法と同じ
    • 説明は省略します
  • S3 から EventBridge への通知設定 aws_s3_bucket_notification

aws_cloudwatch_event_rule はたとえば以下のように記述します。

resource "aws_cloudwatch_event_rule" "start_my_pipeline" {
  name = "start-pipeline-from-s3-rule"
  event_pattern = <<EOF
{
  "source": ["aws.s3"],
  "detail-type": ["Object Created"],
  "detail": {
    "eventSource": ["s3.amazonaws.com"],
    "eventName": ["PutObject", "CompleteMultipartUpload", "CopyObject"],
    "bucket": {
      "name": ["${aws_s3_bucket.my_source_s3.bucket}"]
    },
    "object": {
      "key": ["my_artifact.zip"]
    },
    "reason": ["PutObject", "CompleteMultipartUpload", "CopyObject"]
  }
}
EOF
}

また、 S3 から EventBridge へのイベント発行はデフォルトでは無効になっているので、 aws_s3_bucket_notification を追加する必要があります。

resource "aws_s3_bucket_notification" "bucket_notification" {
  bucket      = aws_s3_bucket.my_source_s3.id
  eventbridge = true
}

なお、 aws_s3_bucket_notification リソースの eventbridgeプロパティは、Terraform AWS provider の v3.74.0 以降でサポートされています。

Release v3.74.0 · hashicorp/terraform-provider-aws · GitHub

まとめ

この記事を執筆している2022年8月現在では、公式ドキュメント上は2番目の方法が推奨されているのですが、仕組みのシンプルさを欠いている印象が強く、ゆくゆくは3番目の方法や、それを更に発展させた方法が標準になっていくのではないかなと感じました。

参考資料

AWS Certified Solutions Architect - Associate (SAA-C02) を自宅受験(ピアソンVue)して合格した

掲題の通り、AWS Certified Solutions Architect - Associate (SAA-C02) に合格しました。ピアソンVueでの自宅受験が可能になっていたので、自宅で受験しました。

今記事では、これから受験しようかなと思っている人向けに、自分がどんな勉強をしてどうだったのかと、自宅受験がどうだったのかを書きます。

勉強を始める前の状況

AWSは業務で利用していて、IAM、EC2、RDS、S3、Lambda...などといったメインどころのサービスがどんな感じかというのはわかっている状態でした。ただ、管理者ではなく開発者という立場であり、また新規でアーキテクチャ設計をするような場面は少なかったので、知らないサービスもたくさんあるし、主なサービスについても詳細までわかっているというのからは程遠かったです。GCPやAzureといったAWS以外のパブリッククラウドについてはマトモに触ったことはありませんでした。

AWSの各種サービスを理解する前提となる一般的な知識については、普通にソフトウェア開発を仕事で数年やってきて得てきたものが役立ちましたねという程度で、(IT未経験だったり、Web系未経験だったりの人よりは流石に有利でしょうけど)Webの会社で仕事をしている人たちの中で自分が特別何か有利な立場だったというのは(AWSを業務で使っている以外だと)なかった気がします。

受験までにやったこと

これから受験する人の参考に、受験までにやったことを書いておきます。

その前に、以下の勉強をして何点取れたのか

得点の範囲は100~1000点で、合格ラインが720点のところ、873点でした。

オーバーキルはオーバーキルですが、完璧ではないという感じですね。本番65問中で「違うかも」と思ったのが19問、「これわかんねえ」と思ったのが10問弱あったでしょうか。とはいえ、完全に知識不足でわからないという感じだったのは5問なかったくらいで、あとは「問題文・選択肢がもっと丁寧に書いてあればわかるのに〜」という感じだったので、もっと勉強すれば安心して合格できたかというと微妙です。ちなみに、問題文・選択肢を日本語で一読してよくわからず、英語の方で確認したことで「ああ、そういうことね」と回答を選ぶことができた、という問題も数問ありました。

どういうふうに勉強をしていったか

受験を決めた翌日に公式の模擬試験(無料)を解き、まずはゴールとの距離を測ろうとしました。模擬試験は本番よりも難度が低いという話だったのですが、ここで45%しか取れなかったため、「こりゃあかんわ」と感じ、真面目に勉強することを決意しました。

基本は、Udemyの講義をまず(倍速で)1周してベースの知識を抑えて、あとはひたすら問題を解いていきました。公式ドキュメントやホワイトペーパーなどは問題の解説を読んでいてもう少し情報が欲しかったときにのみ参照する程度で、またハンズオンも特にやりませんでした(Udemyの講義にはハンズオンパートがあり、それを見て満足していた)。

受験を決めてから本番までの約1ヶ月半のうち、最初の10日ほどで講義を1周、その後25日ほどかけて問題集(全12回分)を解き、残りの期間で問題集の見直し(時間の都合で全部はできず、半分のみ)をしました。

懸念していたのは、解いている問題集の傾向が本番の傾向とは乖離していて本番で歯が立たないという展開だったので、問題集を2種類(どちらもUdemy)買ってリスクを抑えることにしました。情報処理技術者試験などのように本番の過去問が相当量手に入る試験なら方針は違っただろうなと思います。

途中から始めてよかったなと思うのは、Miroにマインドマップ形式でとにかく情報を書き込んでいったことです。動画講義を見るだけ&選択式の問題を解くだけだと、知識を整理する機会がないのですが、Miroに書いていくことで整理ができました。また、出来上がったMiroは直前に見直す際にも非常に役立ちました。

f:id:ky_yk_d:20220319205007p:plain
Miro に書いていったもの(※ズームしても中身は見えません)

使った教材

ちょうど受験を決めたタイミングでUdemyがセール中だったので、勢いで講義のものを1本、問題集を2種類(各6回分で、合計12回分)購入しました。それ以外には、書籍を1冊購入しましたが、これはざっと1周読んだ程度でした。それぞれの教材について個別にコメントします。

講義:「【世界で40万人が受講】AWS 認定ソリューションアーキテクト アソシエイト SAA-C02 対応 2022 最新版」

www.udemy.com

メインにした教材。Udemyだと他に日本人の講師オリジナルのものがあり、そちらの方が人気っぽかったのですが、サンプル動画の印象がこちらの方がよかったのでこちらを選択しました。

全部で25.5時間で、視聴しはじめたときはまだ20時間分くらいまでしか公開されていなかったのですが、受験当日までに24.5/25.5時間まで公開されていたので、困ることはありませんでした(ちなみに、3月末に全部公開される予定とのことです)。

オリジナルは英語なのですが、こちらはその日本語版ということで、スライドも講義もハンズオンも全て日本語になっています。講義パートとハンズオンパートが交互に繰り返されながら進んでいく形で、説明もわかりやすく、非常に良い内容だったと思います。

使い方としては、前述のように最初に1周してしまって特に2周目というのは取り組まなかったのですが、試験当日は試験が午後だったので、理解・記憶が曖昧な部分を見直して午前を過ごしました。

問題集A:「【世界で3万人が受講】演習テスト: AWS 認定 ソリューションアーキテクトアソシエイト」

www.udemy.com

前記の講義と同じシリーズの問題集。日本語化を担当している方は講義の方とは違うのですが、オリジナルの方は同じ講師の監修ということで講義内容と問題の内容にリンクがあるように感じました。

解いた日 問題番号 正答率
2/13 1 61%
2/15 2 67%
2/20 3 66%
3/5 4 72%
3/6 5 83%
3/12 6 73%

問題集B:「【2022年版】AWS 認定ソリューションアーキテクト アソシエイト模擬試験問題集(6回分390問)」

www.udemy.com

日本では一番人気があるようだった講義コースのシリーズの問題集。前述のように問題集が1種類では不安だったので購入しました。高難度とうたっており、特に後半は難しい問題で構成されていて、以下のように点数は伸びませんでした。講師の人も「この問題集で合格点とれなくても受かっている人はいる」と書かれていたので、こんなもんなのだと思われます。

解いた日 問題番号 正答率
2/13 1 75%
2/14 2 70%
2/18 3 78%
2/23 4 67%
3/5 5 67%
3/6 6 64%

書籍:「AWS認定資格試験テキスト AWS認定ソリューションアーキテクト - アソシエイト 改訂第2版」

受験が終わった後も参照できるように1冊ぐらいは持っておいてもよいだろうということで購入しました。1周はざっと読んで、最後の問題集も解きましたが、書籍だけで合格できるという類のものではない(この本にもそう書いてある)ですし、あくまで補う意味でのものでした。

Udemy の講義では言及されておらず、この本では言及されている項目というのもいくつかあったので、無意味ではありませんでしたが、なくてもよかったかなとは思います。

自宅受験どう?

自宅受験の手順・注意点については、既にたくさんレポート記事もあるので、自分も参考にさせてもらい、大きなトラブルなく予約〜受験することができました。以下に参考にした記事をいくつか貼ります。

blog.usize-tech.com

dev.classmethod.jp

事前に記事などを確認しておいたおかげで助かった点を挙げておくと、以下があります。

  • 書籍が手に届く範囲にあるのはNGだと早めにわかったこと。自室(本まみれ)での受験を早々に諦め、他の家族の使っている部屋を当日は使えるように前もって準備できた。
  • (ガイドラインでも避けろと言われているのですが)会社貸与PCは使ってはいけないということ。当初は「いやいや会社PCでも大丈夫でしょ〜」と甘く考えていたが、本当にトラブルが発生することを自分の少し前に受験しようとした知人も実証してくれていたので、ちゃんと私物PCで受験した。

自宅受験は非常に便利なのですが、自分は長時間じっとしていられず、体を動かしたくなってしまうので、余計な動きでカンニングの疑いを持たれないかという不安もあってやりづらい部分がありました(笑)

受験を終えての感想

というわけで、AWS Certified Solutions Architect - Associate (SAA-C02) を無事取得できました。受験しようと思ったのは、新しいシステムの構築や既にAWSで動いているシステムの改善のためにどのようにAWSのサービスを使っていけばいいのかを知っておきたかったからで、その目的は一定程度果たすことができたのではないかと思います(もちろん、これからどれだけ活かせるかが大事なのですが)。

とはいえ、試験勉強が終始楽しかったかというとそれは違います。最初に講義を視聴していたときは、新しい知識がどんどん得られている感覚があり非常に楽しかったのですが、問題を解き始めてからは「こんなん覚えなくても公式ドキュメント見れば書いてあるやん」などとブツブツ言いながら勉強していました(笑) 途中からは「落ちても絶対再受験しないわ」と決意していました。

認定資格には3年の有効期限がありますし、他のアソシエイト資格や、プロフェッショナル資格、専門知識資格などまだたくさんあり、それぞれに勉強するとそれなりに得るものはありそうだなという思いはあるものの、正直もう受験はいいかなぁというのが今の思いです。試験対策向けの教材には実務にも役立つものもあるというのがわかったので、そういう付き合い方をしていければなと思っています。

MySQL 8.0.20 以前でサブクエリの結果をINで受けるDELETE文を書いたらテーブルスキャンになった話:MySQL 8.0.21 で有効になった最適化の検証

この記事はなに?

MySQL 8.0.20 以前でサブクエリの結果をINで受けるDELETE文(後述)を書いたところ、意図に反して削除対象のテーブルが全行スキャンされてしまいました。この挙動が、MySQL 8.0.21 で有効になった最適化によって解消するという情報を得た*1ので、検証してみた結果を書きます。

題材は以下のようになっているテーブルです。親 parents テーブルの主キーを参照している子 children テーブルがあり、親テーブルには主キーとは別にインデックスが効くテーブルがある場面です。

上記のようなテーブルに対して、以下のようなDELETE文を発行したいとします。親テーブルの主キーでないカラム(インデックスを持つ)が特定の値(下の例では3)であるようなレコード、に紐づく子テーブルのレコードを削除する、というDELETEですね。今回は、このときの実行計画とロックの取り方を検証します。

ちなみに、以下のDELETE文ではサブクエリの結果を使って IN で比較していますが、ここが = 比較の場合は挙動がだいぶ変わってきます(バージョンの新旧、最適化の有無に関わらず type: range の比較的平和な実行計画になります)。そのケースについては本記事のスコープ外です。

DELETE FROM children WHERE parent_id IN
(SELECT parent_id FROM  parents WHERE parent_idx_col = 3);

使用する MySQL のバージョンは、以下の通りです。

  • MySQL 8.0.20
  • MySQL 8.0.21

まとめ

そこそこ長いので、まとめを先に書いておきます。

  • DELETE(UPDATE)にINで受けるサブクエリを使ったときの動きが MySQL 8.0.21 で変わったらしいので見てみたよ
  • MySQL 8.0.20 だとDELETE対象のテーブルが先に評価されて全行スキャン、サブクエリは相関サブクエリ扱いになったよ
    • パフォーマンスが悪いよ & ロックもめっちゃかかるよ
  • MySQL 8.0.21 だとサブクエリが先に評価されて、その結果を使ってDELETE対象のテーブルをインデックスで検索しにいけるよ
    • パフォーマンスがいいよ & ロックもだいぶ減っているよ
    • semijoin の最適化が効いている結果みたいだよ
  • MySQL 8.0.20 以前を使っているなら更新系 SQLにINで受けるサブクエリを書くときは注意!
    • JOIN を使った書き方にするなどしましょう

目次

実験用のテーブルのDDL

親テーブル

CREATE TABLE parents (
  `parent_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parent_idx_col` int(10) unsigned NOT NULL,
  PRIMARY KEY (`parent_id`),
  UNIQUE KEY `uk_parent_idx_col` (`parent_idx_col`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

子テーブル

CREATE TABLE children (
  `child_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`child_id`),
  CONSTRAINT `fk_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `parents` (`parent_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

データ

parents

parent_id parent_idx_col
1 1
2 2
3 3
4 4
5 5

children

child_id parent_id
1 1
2 1
3 2
4 2
5 3
6 3
7 4
8 4
9 5
10 5

実験の流れ

以下の設定にしておきます。

SET GLOBAL innodb_status_output=ON;
SET GLOBAL innodb_status_output_locks=ON;

トランザクションを貼ってから以下のDELETE文の(実行計画も取得)実行します。

EXPLAIN DELETE FROM children WHERE parent_id IN
(SELECT parent_id FROM  parents WHERE parent_idx_col = 3);
DELETE FROM children WHERE parent_id IN
(SELECT parent_id FROM  parents WHERE parent_idx_col = 3);

トランザクションを commit せずに、以下でロックの状態を確認します。

SHOW ENGINE INNODB STATUS;

この流れを、MySQLのバージョンを変えて実施します。

MySQL 8.0.20 の場合

まず、変更前の MySQL 8.0.20 で試します。

実行計画

いかにも遅そうな実行計画になりました。

  • 子テーブルが先に評価され、 type: ALL(テーブルスキャン)
  • 親テーブルは DEPENDENT SUBQUERY (相関サブクエリ)扱い
mysql> EXPLAIN DELETE FROM children WHERE parent_id IN
    -> (SELECT parent_id FROM  parents WHERE parent_idx_col = 3);
+----+--------------------+----------+------------+-------+---------------------------+-------------------+---------+-------+------+----------+-------------+
| id | select_type        | table    | partitions | type  | possible_keys             | key               | key_len | ref   | rows | filtered | Extra       |
+----+--------------------+----------+------------+-------+---------------------------+-------------------+---------+-------+------+----------+-------------+
|  1 | DELETE             | children | NULL       | ALL   | NULL                      | NULL              | NULL    | NULL  |   10 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | parents  | NULL       | const | PRIMARY,uk_parent_idx_col | uk_parent_idx_col | 4       | const |    1 |   100.00 | Using index |
+----+--------------------+----------+------------+-------+---------------------------+-------------------+---------+-------+------+----------+-------------+

SHOW ENGINE INNODB STATUS;

children テーブルは全行+ 上限行への排他のネクストキーロックがかかりました。

parent テーブルは検索対象の行だけが共有ロック対象になっています。

  • 12行のロック
  • children テーブルに IX ロック
    • 主キーのインデックスにネクストキーロックがかかる
    • 11行
      • supremum (上限を意味する仮想的な行)
      • 10行(=レコード行数)
        • 00000001:1
        • 0000000a : 10(=末尾のレコードのID)
  • parents テーブルに IS ロック
    • UNIQUEキーのインデックスにレコードロック locks rec but not gap がかかる
    • 1行
      • ID = 3 :検索対象のレコード

抜粋

トランザクションのロック全体

---TRANSACTION 2107, ACTIVE 79 sec
4 lock struct(s), heap size 1136, 12 row lock(s), undo log entries 2
MySQL thread id 9, OS thread handle 140137874208512, query id 50 localhost root starting

子テーブルへのロック

TABLE LOCK table `tmp`.`children` trx id 2107 lock mode IX
RECORD LOCKS space id 3 page no 4 n bits 80 index PRIMARY of table `tmp`.`children` trx id 2107 lock_mode X

親テーブルへのロック

TABLE LOCK table `tmp`.`parents` trx id 2107 lock mode IS
RECORD LOCKS space id 2 page no 5 n bits 72 index uk_parent_idx_col of table `tmp`.`parents` trx id 2107 lock mode S locks rec but not gap

全体

=====================================
2022-01-10 01:02:18 0x7f7464363700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 11 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 20 srv_active, 0 srv_shutdown, 2655 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 31
OS WAIT ARRAY INFO: signal count 24
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 2112
Purge done for trx's n:o < 2112 undo n:o < 0 state: running but idle
History list length 15
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421612870740520, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421612870739672, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 2107, ACTIVE 79 sec
4 lock struct(s), heap size 1136, 12 row lock(s), undo log entries 2
MySQL thread id 9, OS thread handle 140137874208512, query id 50 localhost root starting
show engine innodb status
TABLE LOCK table `tmp`.`parents` trx id 2107 lock mode IS
RECORD LOCKS space id 2 page no 5 n bits 72 index uk_parent_idx_col of table `tmp`.`parents` trx id 2107 lock mode S locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00000003; asc     ;;
 1: len 4; hex 00000003; asc     ;;

TABLE LOCK table `tmp`.`children` trx id 2107 lock mode IX
RECORD LOCKS space id 3 page no 4 n bits 80 index PRIMARY of table `tmp`.`children` trx id 2107 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000001; asc     ;;
 1: len 6; hex 000000000829; asc      );;
 2: len 7; hex 810000010f0110; asc        ;;
 3: len 4; hex 00000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000002; asc     ;;
 1: len 6; hex 00000000082a; asc      *;;
 2: len 7; hex 820000010f0110; asc        ;;
 3: len 4; hex 00000001; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000003; asc     ;;
 1: len 6; hex 00000000082f; asc      /;;
 2: len 7; hex 81000000850110; asc        ;;
 3: len 4; hex 00000002; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000004; asc     ;;
 1: len 6; hex 000000000830; asc      0;;
 2: len 7; hex 82000000860110; asc        ;;
 3: len 4; hex 00000002; asc     ;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 00000005; asc     ;;
 1: len 6; hex 00000000083b; asc      ;;;
 2: len 7; hex 010000011c0151; asc       Q;;
 3: len 4; hex 00000003; asc     ;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 00000006; asc     ;;
 1: len 6; hex 00000000083b; asc      ;;;
 2: len 7; hex 010000011c017b; asc       {;;
 3: len 4; hex 00000003; asc     ;;

Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000007; asc     ;;
 1: len 6; hex 000000000833; asc      3;;
 2: len 7; hex 81000000870110; asc        ;;
 3: len 4; hex 00000004; asc     ;;

Record lock, heap no 9 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000008; asc     ;;
 1: len 6; hex 000000000834; asc      4;;
 2: len 7; hex 82000000880110; asc        ;;
 3: len 4; hex 00000004; asc     ;;

Record lock, heap no 10 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000009; asc     ;;
 1: len 6; hex 000000000835; asc      5;;
 2: len 7; hex 81000000880110; asc        ;;
 3: len 4; hex 00000005; asc     ;;

Record lock, heap no 11 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 0000000a; asc     ;;
 1: len 6; hex 000000000836; asc      6;;
 2: len 7; hex 82000001100110; asc        ;;
 3: len 4; hex 00000005; asc     ;;

--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
852 OS file reads, 641 OS file writes, 357 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 2.27 writes/s, 1.64 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
0.00 hash searches/s, 2.45 non-hash searches/s
---
LOG
---
Log sequence number          28068318
Log buffer assigned up to    28068318
Log buffer completed up to   28068318
Log written up to            28068318
Log flushed up to            28068318
Added dirty pages up to      28068318
Pages flushed up to          28068318
Last checkpoint at           28068318
157 log i/o's done, 0.42 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 427323
Buffer pool size   8192
Free buffers       7194
Database pages     994
Old database pages 381
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 829, created 165, written 362
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 994, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1, Main thread ID=140137389229824 , state=sleeping
Number of rows inserted 15, updated 0, deleted 2, read 28
0.00 inserts/s, 0.00 updates/s, 0.18 deletes/s, 1.00 reads/s
Number of system rows inserted 37, updated 356, deleted 0, read 4702
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

MySQL 8.0.21 の場合

次に、MySQL 8.0.21 で試してみます。

実行計画

実行計画が変わっていることがわかります。サブクエリ部分が相関サブクエリでなくなり、 children テーブルもインデックスを使った参照に変わっています。

mysql> explain DELETE FROM children WHERE parent_id IN
    -> (SELECT parent_id FROM  parents WHERE parent_idx_col = 3);
+----+-------------+----------+------------+-------+---------------------------+-------------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys             | key               | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------------------+-------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | parents  | NULL       | const | PRIMARY,uk_parent_idx_col | uk_parent_idx_col | 4       | const |    1 |   100.00 | Using index |
|  1 | DELETE      | children | NULL       | ref   | fk_parent_id              | fk_parent_id      | 4       | const |    2 |   100.00 | NULL        |
+----+-------------+----------+------------+-------+---------------------------+-------------------+---------+-------+------+----------+-------------+

SHOW ENGINE INNODB STATUS;

ロックの取り方も変わっています。children テーブルでロック対象になっているレコードも減っています。

  • 6行のロック
  • children テーブルに IX ロック
    • 5行
      • 2行:外部キーのインデックス fk_parent_id にネクストキーロック
        • 00000003 :parent_id = 3
          • 00000005:child_id = 5
          • 00000006:child_id = 6
        • 検索該当(削除対象)レコード
      • 2行:主キーのインデックスにレコードロック locks rec but not gap
        • 00000005: child_id = 5
        • 00000006 : child_id = 6
      • 1行:外部キーのインデックス fk_parent_id にギャップロック locks gap before rec
        • 00000004 : parent_id = 4
          • 00000007:parent_id = 7
  • parents テーブルに IS ロック
    • UNIQUEキーのインデックス uk_parent_idx_col にレコードロック locks rec but not gap
    • 1行
      • 00000003:parent_idx_col = 3
        • 00000003 : parent_id = 3

抜粋

トランザクションのロック全体

---TRANSACTION 2108, ACTIVE 20 sec
6 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 2
MySQL thread id 18, OS thread handle 140669508515584, query id 116 localhost root starting

子テーブルへのロック

TABLE LOCK table `tmp`.`children` trx id 2108 lock mode IX
RECORD LOCKS space id 3 page no 5 n bits 80 index fk_parent_id of table `tmp`.`children` trx id 2108 lock_mode X
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000003; asc     ;;
 1: len 4; hex 00000005; asc     ;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000003; asc     ;;
 1: len 4; hex 00000006; asc     ;;

RECORD LOCKS space id 3 page no 4 n bits 80 index PRIMARY of table `tmp`.`children` trx id 2108 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 00000005; asc     ;;
 1: len 6; hex 00000000083c; asc      <;;
 2: len 7; hex 020000011e0151; asc       Q;;
 3: len 4; hex 00000003; asc     ;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 00000006; asc     ;;
 1: len 6; hex 00000000083c; asc      <;;
 2: len 7; hex 020000011e017b; asc       {;;
 3: len 4; hex 00000003; asc     ;;

RECORD LOCKS space id 3 page no 5 n bits 80 index fk_parent_id of table `tmp`.`children` trx id 2108 lock_mode X locks gap before rec
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00000004; asc     ;;
 1: len 4; hex 00000007; asc     ;;

親テーブルへのロック

TABLE LOCK table `tmp`.`parents` trx id 2108 lock mode IS
RECORD LOCKS space id 2 page no 5 n bits 72 index uk_parent_idx_col of table `tmp`.`parents` trx id 2108 lock mode S locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00000003; asc     ;;
 1: len 4; hex 00000003; asc     ;;

全体

=====================================
2022-01-10 01:05:07 0x7ff02c15f700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 18 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 23 srv_active, 0 srv_shutdown, 4092 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 18
OS WAIT ARRAY INFO: signal count 17
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 2113
Purge done for trx's n:o < 2113 undo n:o < 0 state: running but idle
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422144539163200, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422144539162344, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 2108, ACTIVE 20 sec
6 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 2
MySQL thread id 18, OS thread handle 140669508515584, query id 116 localhost root starting
SHOW ENGINE INNODB STATUS
TABLE LOCK table `tmp`.`parents` trx id 2108 lock mode IS
RECORD LOCKS space id 2 page no 5 n bits 72 index uk_parent_idx_col of table `tmp`.`parents` trx id 2108 lock mode S locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00000003; asc     ;;
 1: len 4; hex 00000003; asc     ;;

TABLE LOCK table `tmp`.`children` trx id 2108 lock mode IX
RECORD LOCKS space id 3 page no 5 n bits 80 index fk_parent_id of table `tmp`.`children` trx id 2108 lock_mode X
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000003; asc     ;;
 1: len 4; hex 00000005; asc     ;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000003; asc     ;;
 1: len 4; hex 00000006; asc     ;;

RECORD LOCKS space id 3 page no 4 n bits 80 index PRIMARY of table `tmp`.`children` trx id 2108 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 00000005; asc     ;;
 1: len 6; hex 00000000083c; asc      <;;
 2: len 7; hex 020000011e0151; asc       Q;;
 3: len 4; hex 00000003; asc     ;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 00000006; asc     ;;
 1: len 6; hex 00000000083c; asc      <;;
 2: len 7; hex 020000011e017b; asc       {;;
 3: len 4; hex 00000003; asc     ;;

RECORD LOCKS space id 3 page no 5 n bits 80 index fk_parent_id of table `tmp`.`children` trx id 2108 lock_mode X locks gap before rec
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00000004; asc     ;;
 1: len 4; hex 00000007; asc     ;;

--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
984 OS file reads, 734 OS file writes, 409 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.39 writes/s, 1.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
0.00 hash searches/s, 1.89 non-hash searches/s
---
LOG
---
Log sequence number          28221617
Log buffer assigned up to    28221617
Log buffer completed up to   28221617
Log written up to            28221617
Log flushed up to            28221617
Added dirty pages up to      28221617
Pages flushed up to          28221617
Last checkpoint at           28221617
179 log i/o's done, 0.26 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137363456
Dictionary memory allocated 453831
Buffer pool size   8192
Free buffers       7059
Database pages     1128
Old database pages 429
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 961, created 167, written 413
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1128, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1, Main thread ID=140669059286784 , state=sleeping
Number of rows inserted 15, updated 0, deleted 2, read 20
0.00 inserts/s, 0.00 updates/s, 0.11 deletes/s, 0.17 reads/s
Number of system rows inserted 38, updated 356, deleted 0, read 5052
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

MySQL 8.0.21 でオプティマイザスイッチを変えてみる

ここまでで、 MySQL 8.0.20 と 8.0.21 とで動きが変わることは確認できました。最後に、8.0.21 でどのような最適化が効いてこの結果になっているのかを調べてみます。

A single-table UPDATE or DELETE statement that uses a subquery having a [NOT] IN or [NOT] EXISTS predicate can now in many cases make use of a semijoin transformation or subquery materialization.

MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.21 (2020-07-13, General Availability)

という記載があったので、オプティマイザスイッチを変えて実験してみました。

対象の設定は以下の2つです。

  • subquery materialization
  • semijoin transformation

subquery materialization

まず、subquery materialization をオフにしてみます。

dev.mysql.com

SET SESSION optimizer_switch='materialization=off';

実行計画を取ります。

mysql> EXPLAIN DELETE FROM children WHERE parent_id IN
    -> (SELECT parent_id FROM  parents WHERE parent_idx_col = 3);
+----+-------------+----------+------------+-------+---------------------------+-------------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys             | key               | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------------------+-------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | parents  | NULL       | const | PRIMARY,uk_parent_idx_col | uk_parent_idx_col | 4       | const |    1 |   100.00 | Using index |
|  1 | DELETE      | children | NULL       | ref   | fk_parent_id              | fk_parent_id      | 4       | const |    2 |   100.00 | NULL        |
+----+-------------+----------+------------+-------+---------------------------+-------------------+---------+-------+------+----------+-------------+

オプティマイザスイッチを変える前と変わりませんでした。以下の記事では、こちらのオプティマイザスイッチを変更することで動きが変わっているので、サブクエリの形などの違いによってどちらの最適化が効くのかが違うようです。

qiita.com

semijoin transformation

materialization を on に戻して、今度はsemijoin transformationをオフにしてみます。

dev.mysql.com

SET SESSION optimizer_switch='semijoin=off';
mysql> EXPLAIN DELETE FROM children WHERE parent_id IN
    -> (SELECT parent_id FROM  parents WHERE parent_idx_col = 3);
+----+-------------+----------+------------+-------+---------------------------+-------------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys             | key               | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------------------+-------------------+---------+-------+------+----------+-------------+
|  1 | DELETE      | children | NULL       | ALL   | NULL                      | NULL              | NULL    | NULL  |    8 |   100.00 | Using where |
|  2 | SUBQUERY    | parents  | NULL       | const | PRIMARY,uk_parent_idx_col | uk_parent_idx_col | 4       | const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------------------+-------------------+---------+-------+------+----------+-------------+

8.0.20 の場合に似た実行計画になることが確認できました。MySQL 8.0.20 のものと比較すると、

  • select_typeDEPENDENT SUBQUERYからSUBQUERY なっている
  • rowsが 10 → 8 に変わっている

という違いはありますが、実行して SHOW ENGINE INNODB STATUS をとってみると、ロックのかかり方は(MySQL 8.0.20 と)変わらないように見えます。

ロックの詳細については以下。

=====================================
2022-01-10 07:49:33 0x7ff02c15f700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 12 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 25 srv_active, 0 srv_shutdown, 20947 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 24
OS WAIT ARRAY INFO: signal count 22
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 2119
Purge done for trx's n:o < 2119 undo n:o < 0 state: running but idle
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422144539163200, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422144539162344, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 2114, ACTIVE 18 sec
4 lock struct(s), heap size 1136, 12 row lock(s), undo log entries 2
MySQL thread id 18, OS thread handle 140669508515584, query id 134 localhost root starting
show engine innodb status
TABLE LOCK table `tmp`.`parents` trx id 2114 lock mode IS
RECORD LOCKS space id 2 page no 5 n bits 72 index uk_parent_idx_col of table `tmp`.`parents` trx id 2114 lock mode S locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00000003; asc     ;;
 1: len 4; hex 00000003; asc     ;;

TABLE LOCK table `tmp`.`children` trx id 2114 lock mode IX
RECORD LOCKS space id 3 page no 4 n bits 80 index PRIMARY of table `tmp`.`children` trx id 2114 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000001; asc     ;;
 1: len 6; hex 00000000082a; asc      *;;
 2: len 7; hex 82000001110110; asc        ;;
 3: len 4; hex 00000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000002; asc     ;;
 1: len 6; hex 00000000082b; asc      +;;
 2: len 7; hex 810000010e0110; asc        ;;
 3: len 4; hex 00000001; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000003; asc     ;;
 1: len 6; hex 000000000830; asc      0;;
 2: len 7; hex 82000000860110; asc        ;;
 3: len 4; hex 00000002; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000004; asc     ;;
 1: len 6; hex 000000000831; asc      1;;
 2: len 7; hex 81000000860110; asc        ;;
 3: len 4; hex 00000002; asc     ;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 00000005; asc     ;;
 1: len 6; hex 000000000842; asc      B;;
 2: len 7; hex 010000011d0151; asc       Q;;
 3: len 4; hex 00000003; asc     ;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 00000006; asc     ;;
 1: len 6; hex 000000000842; asc      B;;
 2: len 7; hex 010000011d017b; asc       {;;
 3: len 4; hex 00000003; asc     ;;

Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000007; asc     ;;
 1: len 6; hex 000000000834; asc      4;;
 2: len 7; hex 82000000880110; asc        ;;
 3: len 4; hex 00000004; asc     ;;

Record lock, heap no 9 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000008; asc     ;;
 1: len 6; hex 000000000835; asc      5;;
 2: len 7; hex 81000000880110; asc        ;;
 3: len 4; hex 00000004; asc     ;;

Record lock, heap no 10 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000009; asc     ;;
 1: len 6; hex 000000000836; asc      6;;
 2: len 7; hex 82000001120110; asc        ;;
 3: len 4; hex 00000005; asc     ;;

Record lock, heap no 11 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 0000000a; asc     ;;
 1: len 6; hex 000000000837; asc      7;;
 2: len 7; hex 810000010f0110; asc        ;;
 3: len 4; hex 00000005; asc     ;;

--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
984 OS file reads, 784 OS file writes, 445 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number          28226956
Log buffer assigned up to    28226956
Log buffer completed up to   28226956
Log written up to            28226956
Log flushed up to            28226956
Added dirty pages up to      28226956
Pages flushed up to          28226956
Last checkpoint at           28226956
191 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137363456
Dictionary memory allocated 453831
Buffer pool size   8192
Free buffers       7056
Database pages     1131
Old database pages 429
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 961, created 170, written 439
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1131, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1, Main thread ID=140669059286784 , state=sleeping
Number of rows inserted 15, updated 0, deleted 4, read 49
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 38, updated 356, deleted 0, read 5052
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

微妙な違いはありますが、設定変更前の MySQL 8.0.21 では semijoin の最適化が効いていたと言えそうです。semijoin についてのドキュメントの一番下をみると、以下のように記載されています。

In MySQL 8.0.21 and later, a semijoin transformation can also be applied to a single-table UPDATE or DELETE statement that uses a [NOT] IN or [NOT] EXISTS subquery predicate, provided that the statement does not use ORDER BY or LIMIT, and that semijoin transformations are allowed by an optimizer hint or by the optimizer_switch setting.

MySQL :: MySQL 8.0 Reference Manual :: 8.2.2.1 Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations

終わりに

MySQL 8.0.21 で新しい最適化が効くようになったよ!というお話でした。

なお、本記事の執筆にあたっては mysql-casual Slack の皆様(特に @yoku0825 さん @hmatsu47 さん)に教えていただいた内容を大いに参考にしました。ありがとうございました!

mysql-casual.connpass.com

参考

qiita.com

abicky.net

dev.mysql.com

dev.mysql.com

nippondanji.blogspot.com

yoku0825.blogspot.com

dev.classmethod.jp

*1:@hmatsu47 さんから情報を提供していただきました。