かみぽわーる

kamipo's blog

MySQLのクエリの良し悪しはrows_examinedで判断する

仕事やらなんやらでMySQLのクエリの良し悪しを判断する必要があるとき、EXPLAINの内容だけだとどのぐらい良くなったり悪くなったのか分からないので SET long_query_time = 0; してrows_examined (そのクエリでrows_sent行の結果を返すために何行に触ったのか)も一緒に提示するようにしている(少なくともMySQL 5.7時点ではrows_examinedはslow_query_logでしか確認できないはずperformance_schemaが有効ならevents_statements_historyやその仲間たちで確認できるとのこと*1 MySQL :: MySQL 5.6 リファレンスマニュアル :: 22.9.6 パフォーマンススキーマステートメントイベントテーブル)。

例:

f:id:kamipo:20180322074906p:plain

上の例のBeforeは、もともとDBAが書いた温かみのあるSQLでORDER BY LIMIT最適化を効かせてLIMIT 20されたクエリをORDER BY狙いのインデックスを狙って20行の結果を返すために20行だけに触れている、つまり最も良い実行計画のクエリが、Afterでは深遠な理由により別のテーブルの条件で絞り込んだりしたりしなかったりするためのJOINで最適化の狙いが効かなくなって同じ20行の結果を返すために259454行に触れてしまうようになってしまったの図です。

f:id:kamipo:20180322074925p:plain

仕様を保ったまま最適化の狙いが効くようにクエリの書き変えを行うと、20行の結果を返すために駆動表と内部表それぞれ20行ずつだけ触るのが最も良い実行計画になるので、その修正案をrows_examinedと共に提示しているのが上の図です。

ゴルフで例えると、EXPLAINは大まかな方向性や飛距離を合わせるドライバーのような感じで、グリーンによせたあとカップを狙うパター的なのがrows_examinedという感じ。ドライバーでもパッティングできるけどパターも使いこなせるとスコアアップ間違い無し💪

ActiveRecordでINの中が一万個とかにならないようにする

この記事は MySQL Casual Advent Calendar 2017 の23日目の記事です。

みなさんORマッパーは使っていますか?

僕は仕事とか趣味でActiveRecordというORマッパーを使っているんですけど、こいつ例えば

Team.preload(players: :high_score).to_a

みたいなことをするとすぐ

SELECT `scores`.* FROM `scores` FROM `scores`.`id` IN (a, b, c, ...数千個続く...)

みたいなクエリを生成しよるんですけど、MySQL 5.7に上げたときに range_optimizer_max_mem_size の制限で実行計画がテーブルスキャンに落ちてえらい目にあったことがありました。MySQL側で range_optimizer_max_mem_size = 0 することでこの制限を無くすことができますが、MySQL側の設定によらずアプリケーションが動作できるようINの中の個数を制限する方法をここでは考えます。

MySQL以外のことはよくわからないんですが、どうやらOracleにはINの中の個数が1000個までしか入れられないという制限があるようで、これに対応するためにActiveRecordには in_clause_length の個数ずつにsliceしてクエリを投げる仕組みが元々存在します。Oracle以外には個数の制限はないんですけど、この値を上書きすると他のバックエンドでもINの中の個数を制限することができるわけです。例えばOracle同様1000個までに制限したい場合は以下のようにすればよいです。

ActiveSupport.on_load(:active_record) do
  module ActiveRecord
    module ConnectionAdapters
      module DatabaseLimitsExt
        def in_clause_length
          1000
        end
      end

      class AbstractAdapter
        prepend DatabaseLimitsExt
      end
    end
  end
end

もしみなさんのORマッパーがINの中が一万個とかになるクエリを生成しているなら range_optimizer_max_mem_size の制限に引っかかってないか確認してみるといいかもしれません。

MySQL 8.0ではデフォルトで濁点半濁点を区別しなくなる

4月にMySQL 8.0のUnicodeと日本語対応についてManyi Luさんとディスカッションする会があって、かなりいろいろ話してとてもよい会だった。その後いろいろ考えて感じてる懸念を端的に書き記しておく。

デフォルトのcollationがutf8mb4_0900_ai_ciになった

これに関して僕は強い懸念を持っている。MySQL 8.0以前において、ふつうのWebアプリケーションなどで日本語を扱う場合、実用上デフォルトのutf8mb4_general_ciかutf8mb4_binの2択であったと思う。デフォルトがutf8mb4_general_ciなので新しく作られるアプリケーションは通常は濁点半濁点が区別される状態で世に出てくることになる。けどMySQL 8.0.1のデフォルトのutf8mb4_0900_ai_ciは濁点半濁点を区別しないので、将来ユーザー名を登録するところでバイトさんが登録してたらハイドさんは登録できないみたいなことが今よりも多く起きるだろうと思われる。

どうなればいいと思っているか

MySQL 8.0以前のutf8mb4のデフォルトのcollationであるutf8mb4_general_ciにはSMP文字(絵文字とか)を0xFFFD (REPLACEMENT CHARACTER)として比較するという問題がある。この挙動はdocumentedなので仕様だけど、そもそも2010年にMySQL 5.5 GAが出たときにこの挙動でutf8mb4を世に出してしまったことはこの際バグであったこととして、utf8mb4_general_ciのASCII範囲外のBMP文字と同様文字コードで比較するように修正するか修正した新たなcollationを用意して、デフォルトのcollationは修正版utf8mb4_general_ciであるほうが困る人が少ない選択だと思う。既存のcollationの挙動を変えてしまうと互換性の問題があるのだと思うけど(たとえば既存のすでに作られているインデックスは0xFFFDとして作られているところを文字コードで探索はできない等)、個人的にはデフォルトのcollationをutf8mb4_0900_ai_ciに変えるのに比べて許容できる非互換だと思うしバグ修正といって差し支えないように思う。

といった感じでデフォルトのcollationが変わることに関しての懸念を書きましたが、用途に応じて適切に設定すればMySQL 8.0は21世紀最高のMySQLになることをプロミスします!💫

SEE ALSO

MySQLでORDER BYをつけないときの並び順

メリークリスマス!🎅🎄

このエントリはMySQL Casual Advent Calendar 2016の24日目です。

今日はこれの話です!

並び順が実装と実行計画に依存するとはどういうことか

MySQLでORDER BYがついていないときに返す結果セットの並び順は定められていない。なのでORDER BYがついてないときMySQLはとくに何もせず自然に返せる順序で結果セットを返します。MySQLはストレージエンジンがプラガブルになっていてストレージエンジン毎にデータ構造や実装が異なるので自然に返せる順序もストレージエンジン毎に異なりうる。それを確かめるために、InnoDBMyISAMでORDER BYをつけないときの並び順がどう違うのか見てみましょう。

CREATE TABLE myisam_casual_2016 (
 date date PRIMARY KEY,
 author varchar(250) NOT NULL,
 title varchar(250) NOT NULL,
 INDEX idx_author (author)
) ENGINE=MyISAM CHARSET=utf8mb4;

CREATE TABLE innodb_casual_2016 (
 date date PRIMARY KEY,
 author varchar(250) NOT NULL,
 title varchar(250) NOT NULL,
 INDEX idx_author (author)
) ENGINE=InnoDB CHARSET=utf8mb4;

INSERT INTO myisam_casual_2016 VALUES
 ('2016-12-24','kamipo','MySQLでORDER BYをつけないときの並び順'),
 ('2016-12-23','yoku0825','COUNTを速くする(?)SQL1本ノック その2'),
 ('2016-12-22','i_rethi','MySQL5.6と5.7のちょっとした違いとinnodb_thread_concurrencyの影響'),
 ('2016-12-21','atsuizo','お兄さんとの約束は守ろう!(tx_isolationとbinlogの話)'),
 ('2016-12-20','ogataka50','MySQL WorkbenchのVisual Explainでのみ出てくるaccess typeの話'),
 ('2016-12-19','yoku0825','COUNTを速くする(?)SQL1本ノック その1'),
 ('2016-12-18','atsuizo','MySQLのChar型のデータサイズについて小ネタ'),
 ('2016-12-17','yut148','upscaledb-mysqlを試す'),
 ('2016-12-16','yebihara','MySQLのMyRocksストレージエンジンの話を中の人から聞いた'),
 ('2016-12-15','eshimizu','ハゲたサンタがMySQLを覗きはじめたお話'),
 ('2016-12-14','mita2','MySQL 8.0 DMR と 5.7 パラメータ比較'),
 ('2016-12-13','RKajiyama','MySQL 5.7.17が出たのであのプラグインについて書いてみました'),
 ('2016-12-12','atsuizo','JOIN ON で絞り込み条件を入れるのと、JOIN ONの後WHERE句で絞り込み条件を入れるのとでは、結果が違う件'),
 ('2016-12-11','yoku0825','試される大地 YAPC::Hokkaido 2016 SapporoでMySQL 8.0の話をしてきた'),
 ('2016-12-10','yoku0825','MySQLのビルド環境にConoHaを選んでいる理由'),
 ('2016-12-09','meijik','MySQL 8.0 Lab版でCTE(Common Table Expression)'),
 ('2016-12-08','yoku0825','最近のMroongaさんの構成について'),
 ('2016-12-07','mita2','MySQL 8.0 新機能 Persisting configuration variables'),
 ('2016-12-06','atsuizo','SQLを繰り返し実行したら段階的に応答速度が上がった話'),
 ('2016-12-05','atsuizo','MySQLのトランザクション制御がキモい話'),
 ('2016-12-04','zurazurataicho','MySQL5.7でJSON型を試してみた'),
 ('2016-12-03','yoku0825','mysqlコマンドラインクライアントのコマンド集'),
 ('2016-12-02','kakuka4430','CentOS6.8にtpcc-mysqlを入れようとして失敗した話'),
 ('2016-12-01','yoku0825','MySQLのNOW関数はどのようにして安全にスレーブでリプレイされるのか');

INSERT INTO innodb_casual_2016 SELECT * FROM myisam_casual_2016;

日付を主キーにして、日付の降順に初期データを投入しています。MyISAMは基本的に追記型のデータ構造なので実行計画がテーブルスキャンのときの自然に返せる結果セットの順序はデータの投入順ということになります。一方InnoDBのデータ構造はクラスタードインデックスなので主キーを持つテーブルの場合データは主キーのキー値でソート済みになっています。

> SELECT * FROM myisam_casual_2016 USE INDEX () WHERE author IN ('yoku0825','atsuizo') LIMIT 10;
+------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| date       | author   | title                                                                                                                                     |
+------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| 2016-12-23 | yoku0825 | COUNTを速くする(?)SQL1本ノック その2                                                                                                   |
| 2016-12-21 | atsuizo  | お兄さんとの約束は守ろう!(tx_isolationとbinlogの話)                                                                                    |
| 2016-12-19 | yoku0825 | COUNTを速くする(?)SQL1本ノック その1                                                                                                   |
| 2016-12-18 | atsuizo  | MySQLのChar型のデータサイズについて小ネタ                                                                                                 |
| 2016-12-12 | atsuizo  | JOIN ON で絞り込み条件を入れるのと、JOIN ONの後WHERE句で絞り込み条件を入れるのとでは、結果が違う件                                        |
| 2016-12-11 | yoku0825 | 試される大地 YAPC::Hokkaido 2016 SapporoでMySQL 8.0の話をしてきた                                                                         |
| 2016-12-10 | yoku0825 | MySQLのビルド環境にConoHaを選んでいる理由                                                                                                 |
| 2016-12-08 | yoku0825 | 最近のMroongaさんの構成について                                                                                                           |
| 2016-12-06 | atsuizo  | SQLを繰り返し実行したら段階的に応答速度が上がった話                                                                                       |
| 2016-12-05 | atsuizo  | MySQLのトランザクション制御がキモい話                                                                                                     |
+------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

> SELECT * FROM innodb_casual_2016 USE INDEX () WHERE author IN ('yoku0825','atsuizo') LIMIT 10;
+------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| date       | author   | title                                                                                                                                     |
+------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| 2016-12-01 | yoku0825 | MySQLのNOW関数はどのようにして安全にスレーブでリプレイされるのか                                                                          |
| 2016-12-03 | yoku0825 | mysqlコマンドラインクライアントのコマンド集                                                                                               |
| 2016-12-05 | atsuizo  | MySQLのトランザクション制御がキモい話                                                                                                     |
| 2016-12-06 | atsuizo  | SQLを繰り返し実行したら段階的に応答速度が上がった話                                                                                       |
| 2016-12-08 | yoku0825 | 最近のMroongaさんの構成について                                                                                                           |
| 2016-12-10 | yoku0825 | MySQLのビルド環境にConoHaを選んでいる理由                                                                                                 |
| 2016-12-11 | yoku0825 | 試される大地 YAPC::Hokkaido 2016 SapporoでMySQL 8.0の話をしてきた                                                                         |
| 2016-12-12 | atsuizo  | JOIN ON で絞り込み条件を入れるのと、JOIN ONの後WHERE句で絞り込み条件を入れるのとでは、結果が違う件                                        |
| 2016-12-18 | atsuizo  | MySQLのChar型のデータサイズについて小ネタ                                                                                                 |
| 2016-12-19 | yoku0825 | COUNTを速くする(?)SQL1本ノック その1                                                                                                   |
+------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

セカンダリキーを使う実行計画では当然セカンダリキーのキー値でソートされた結果セットを返しますが、同じキー値同士のレコードの順序はMyISAMInnoDBで異なります。ここでもMyISAMはデータの投入順、InnoDBは主キーのキー値の順序になります。

> SELECT * FROM myisam_casual_2016 USE INDEX (idx_author) WHERE author IN ('yoku0825','atsuizo') LIMIT 10;
+------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| date       | author   | title                                                                                                                                     |
+------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| 2016-12-21 | atsuizo  | お兄さんとの約束は守ろう!(tx_isolationとbinlogの話)                                                                                    |
| 2016-12-18 | atsuizo  | MySQLのChar型のデータサイズについて小ネタ                                                                                                 |
| 2016-12-12 | atsuizo  | JOIN ON で絞り込み条件を入れるのと、JOIN ONの後WHERE句で絞り込み条件を入れるのとでは、結果が違う件                                        |
| 2016-12-06 | atsuizo  | SQLを繰り返し実行したら段階的に応答速度が上がった話                                                                                       |
| 2016-12-05 | atsuizo  | MySQLのトランザクション制御がキモい話                                                                                                     |
| 2016-12-23 | yoku0825 | COUNTを速くする(?)SQL1本ノック その2                                                                                                   |
| 2016-12-19 | yoku0825 | COUNTを速くする(?)SQL1本ノック その1                                                                                                   |
| 2016-12-11 | yoku0825 | 試される大地 YAPC::Hokkaido 2016 SapporoでMySQL 8.0の話をしてきた                                                                         |
| 2016-12-10 | yoku0825 | MySQLのビルド環境にConoHaを選んでいる理由                                                                                                 |
| 2016-12-08 | yoku0825 | 最近のMroongaさんの構成について                                                                                                           |
+------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

> SELECT * FROM innodb_casual_2016 USE INDEX (idx_author) WHERE author IN ('yoku0825','atsuizo') LIMIT 10;
+------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| date       | author   | title                                                                                                                                     |
+------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| 2016-12-05 | atsuizo  | MySQLのトランザクション制御がキモい話                                                                                                     |
| 2016-12-06 | atsuizo  | SQLを繰り返し実行したら段階的に応答速度が上がった話                                                                                       |
| 2016-12-12 | atsuizo  | JOIN ON で絞り込み条件を入れるのと、JOIN ONの後WHERE句で絞り込み条件を入れるのとでは、結果が違う件                                        |
| 2016-12-18 | atsuizo  | MySQLのChar型のデータサイズについて小ネタ                                                                                                 |
| 2016-12-21 | atsuizo  | お兄さんとの約束は守ろう!(tx_isolationとbinlogの話)                                                                                    |
| 2016-12-01 | yoku0825 | MySQLのNOW関数はどのようにして安全にスレーブでリプレイされるのか                                                                          |
| 2016-12-03 | yoku0825 | mysqlコマンドラインクライアントのコマンド集                                                                                               |
| 2016-12-08 | yoku0825 | 最近のMroongaさんの構成について                                                                                                           |
| 2016-12-10 | yoku0825 | MySQLのビルド環境にConoHaを選んでいる理由                                                                                                 |
| 2016-12-11 | yoku0825 | 試される大地 YAPC::Hokkaido 2016 SapporoでMySQL 8.0の話をしてきた                                                                         |
+------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

ORDER BY狙いのキーとはなんだったのか

twitter.com

MySQLでOLTP系クエリのチューニングをすることはつまるところ No using temporary, No using filesort を狙いに行くことですが、 No using filesort を狙うというのは欲しい結果セットの順序とMySQLが自然に返せる結果セットの順序を合わせるように実行計画を選択するということに他ならないので、もしMySQLの気持ちが分からなくなったときは、結果セットの順序に思いを馳せてみるのも良いかもしれません。

ISUCON6予選にチーム「それぞれの椅子」で参加した

ISUCON6予選1日目にチーム「それぞれの椅子(kamipo, Yappo, kan)」で参加した(kanさんは予定があってリモートからの友情出演)。

結果からいうとスコア15万ぐらいで安定したとこでもう時間ないから触るのやめて再起動チェックだけやって終わろうって再起動したら3万ぐらいまでスコア下がって原因特定するには時間なさすぎて死んだ(最後6万ぐらいまでは回復したっぽい)。俺の屍を越えてゆく者へ言えることは、不測の事態にそなえて再起動チェックは時間に余裕をもって何度かやるべきということです。

結果は残念だったけど今回はとても楽しめた。これまでのISUCONではせっかく声をかけて集まってもらったのだからみんなのパフォーマンスを引き出さなければというプレッシャーがハンパなかったけど、みんな大人なんだから自分のパフォーマンスぐらい自分で発揮するやろって気持ちでやれたのがよかった。やっぽさん当日の朝6時ぐらいまで飲んでたけどみそ汁飲んだら復活してたし。

今回のお題ははてなダイアリーを模したキーワードリンクするWebアプリ(+はてなスターとスパム判定が別サービスで立ってるマイクロサービス構成)。キーワードリンクしたHTMLを生成する処理がボトルネックのほぼすべてといっていいお題で、この処理をいかに高速化するか(もしくはオフロードするか)という問題だと理解しました。キーワードリンクは登録されているエントリ名(keyword)の最長マッチが期待されていて、チェッカーは既存のエントリ名をプレフィックスにもつkeywordをPOSTしてきて最長マッチが正しく考慮されているかをチェックしているようでした。

まず我がチームのとった戦略はキーワードマッチのためのregexp生成をRegexp::Assembleを使う&キャッシュすること。これで3万ぐらいまではいったけど、ここからどう速くするかは結構悩んだ。他に多少無駄なところ(高速化の余地)があってもキーワードリンク生成を速くできる目処が立たない限り効果が誤差レベルなので。

このregexp生成のキャッシュ時に確認できたことは、キーワードの最長マッチは常に正確じゃなくてもチェッカーがPOSTしてくる既存のkeywordより長いものが考慮されていれば減点はないということ。そこで最終的には、初期状態からある既存のkeywordに対して置換の前処理(html_escapeの前)をキャッシュすることですべてのページのキーワードリンク処理を高速化し、チェッカーに怒られるkeywordは後で個別に対応するという戦略でキャッシュいれてみたら予想通りスコア上がって15万ぐらいで安定するようになった。残り時間がもう30分を切ってたので個別対応を入れるのは諦めて最後に再起動チェックだけしようって再起動したらスコア3万。原因特定をするにも残り10分切ってたのでenqueueガチャで最後少し回復させるのが限界でフィニッシュ。

再起動チェックする時間が足りなかったのは今後の教訓として、最後まで楽しく取り組めたのは自分の心構え以上に、ボトルネックはわかってるのに高速化する方法が簡単じゃない良い問題だったからだと思う。また、ISUCON5予選と違ってデータベースにボトルネックを寄せていないことは今回学生枠が多いISUCON6にとっても取り組みやすい問題だったのではないかと思います。

とりあえずいつでも飲みにいけます🍺🍶

f:id:kamipo:20160918181234p:plain

入れる時間がなかった人の手による温かみのある個別対応

Comparing master...exclude_keyword · kamipo/isucon6q · GitHub

MySQL 5.7のONLY_FULL_GROUP_BYはちょっと進化してた

このエントリはMySQL Casual Advent Calendar 2015の14日目です。

TL;DR

MySQL 5.7ではデフォルトONLY_FULL_GROUP_BYが有効である。MySQL 5.7.5からONLY_FULL_GROUP_BYが有効のとき

  • GROUP BY句のカラムと関数従属性のあるカラムはSELECT句に書けるようになった😤
  • ORDER BY句のカラムはDISTINCTのカラムリストに含めなければいけなくなった😣
  • ONLY_FULL_GROUP_BYを無効にしなくてもHAVING句のalias拡張が使えるようになった😆

GROUP BY句のカラムと関数従属性のあるカラムはSELECT句に書けるようになった

[mysqlcasual] > CREATE TABLE users (id int unsigned auto_increment primary key, name varchar(255));
Query OK, 0 rows affected (0.03 sec)

[mysqlcasual] > CREATE TABLE friends (user_id int unsigned, friend_id int unsigned);
Query OK, 0 rows affected (0.04 sec)

以下のクエリはu.nameがGROUP BY句に含まれてなくて集合関数にもかけられてないけどf.user_idに関数従属しているので5.7では実行できます!

[mysqlcasual] > SELECT f.user_id, u.name, COUNT(*) FROM friends f JOIN users u ON f.user_id = u.id GROUP BY f.user_id;
Empty set (0.00 sec)

残念ながら5.6ではONLY_FULL_GROUP_BYが有効だとエラーです。

[mysqlcasual] > SELECT f.user_id, u.name, COUNT(*) FROM friends f JOIN users u ON f.user_id = u.id GROUP BY f.user_id;
ERROR 1055 (42000): 'mysqlcasual.u.name' isn't in GROUP BY

ORDER BY句のカラムはDISTINCTのカラムリストに含めなければいけなくなった

とにかくそういうことらしいです。他のが便利になる系なのに対してこれは既存のクエリに影響が出る系なので5.7にアップグレードして既存のクエリに影響があるか確認しておく必要があります。ちなみにActiveRecordというORマッパーは余裕でDISTINCTついてるクエリにORDER BYつくので滅茶苦茶影響がある。とにかくこれを一日も早くマージしてほしい。

github.com

ONLY_FULL_GROUP_BYを無効にしなくてもHAVING句のalias拡張が使えるようになった

これもとにかくそういうことです!

[mysqlcasual] > SELECT user_id, COUNT(*) c FROM friends GROUP BY user_id HAVING c = 1;
Empty set (0.00 sec)

以上、MySQL 5.7のONLY_FULL_GROUP_BYはちょっと進化してました!

MySQL 5.7のoptimizer_switch、derived_mergeとは何ぞや

このエントリはMySQL Casual Advent Calendar 2015の8日目です。

MySQL 5.7.6からoptimizer_switchderived_mergeが追加されデフォルトで有効になっている。基本的にこれはほっといたらだいたいサブクエリが速くなるやつなので気にしなくてもいいんですが、ちょっと非互換があるのでさくっと説明します。

root@localhost [mysqlcasual] > CREATE TABLE t1 (a int);
Query OK, 0 rows affected (0.03 sec)

root@localhost [mysqlcasual] > CREATE TABLE t2 (b int);
Query OK, 0 rows affected (0.03 sec)

root@localhost [mysqlcasual] > INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

root@localhost [mysqlcasual] > INSERT INTO t2 VALUES (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

これまでFROM句のサブクエリは一旦マテリアライズされてから外側のクエリとかWHERE句とかと結合されていた。

root@localhost [mysqlcasual] > SET optimizer_switch = 'derived_merge=off';
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysqlcasual] > EXPLAIN SELECT * FROM t1 JOIN (SELECT * FROM t2) dt;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
|  1 | PRIMARY     | t1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL                                  |
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using join buffer (Block Nested Loop) |
|  2 | DERIVED     | t2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL                                  |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
3 rows in set, 1 warning (0.00 sec)

これがderived_merge=onだとマテリアライズせずに外側の条件とマージできそうなときはマージされるようになるのだ!

root@localhost [mysqlcasual] > SET optimizer_switch = 'derived_merge=on';
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysqlcasual] > EXPLAIN SELECT * FROM t1 JOIN (SELECT * FROM t2) dt;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL                                  |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
2 rows in set, 1 warning (0.00 sec)

これでサブクエリが多い日も安心!!

しかしその代償に以下の更新クエリが通らなくなった。

root@localhost [mysqlcasual] > UPDATE t2 SET b=1 WHERE b IN (SELECT b FROM (SELECT * FROM t2) dt WHERE b=1);
ERROR 1093 (HY000): You can't specify target table 't2' for update in FROM clause

どうもMySQLにはひとつの更新系クエリ(UPDATE, DELETE)で更新するテーブルと同じテーブルをFROM句の中で両方同時に参照できないという制約があるらしく、いままでマテリアライズされて別テーブルになってたから通ってたクエリが最適化によってマージされてそのまま参照されることでこの制約に引っかかるようになるみたいです。

回避策としては、derived_merge=offにするか、サブクエリをマージできない(マテリアライズされる)クエリに書き換えるとよいです(DISTINCTLIMITをつけるとマージできなくなる)。

root@localhost [mysqlcasual] > UPDATE t2 SET b=1 WHERE b IN (SELECT b FROM (SELECT DISTINCT * FROM t2) dt WHERE b=1);
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

root@localhost [mysqlcasual] > SET optimizer_switch = 'derived_merge=off';
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysqlcasual] > UPDATE t2 SET b=1 WHERE b IN (SELECT b FROM (SELECT * FROM t2) dt WHERE b=1);
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

じつはVIEWもderived tableの仲間なので、ALGORITHM=MERGEなVIEWもこの制約に引っかかるので注意です。