condition fanout filterで爆死
これは MySQL Casual Advent Calendar 2017 の Day13 の記事です。
5.6から5.7にバージョンアップした際に実行計画絡みでトラブった事を書きます。 タイトルにあるcondition fanout filterとはMySQL5.7から追加されたOptimizer Switchで、条件句での絞り込みを実行計画生成の考慮に入れるものです。
資料
MySQL :: [MySQL 5.7入門セミナー講演資料] チューニング基礎編、SQLチューニング編 (※要Oracleアカウント登録(無料))
どれくらい絞りこめると判断したかどうかはExplainのfilterd列に表示されています（数字が少ないほど絞り込めている(と判断した)）。
下記のテーブル構成（FK以外のINDEXは無し）を元に説明します。
-- 約8,000行(一番少ない) CREATE TABLE `company` ( `company_id` bigint(20) NOT NULL AUTO_INCREMENT, `company_name` varchar(128) CHARACTER SET utf8 NOT NULL, PRIMARY KEY (`company_id`) ) ENGINE=InnoDB AUTO_INCREMENT=8056 DEFAULT CHARSET=utf8mb4 -- 約30,000行 CREATE TABLE `customer` ( `customer_id` bigint(20) NOT NULL AUTO_INCREMENT , `company_id` bigint(20) NOT NULL , `customer_name` varchar(256) CHARACTER SET utf8 NOT NULL, `flg` char(1) CHARACTER SET utf8 NOT NULL , `start_date` date NOT NULL , `end_date` date NOT NULL , PRIMARY KEY (`customer_id`), KEY `fk_company_id` (`company_id`), CONSTRAINT `fk_company_id` FOREIGN KEY (`company_id`) REFERENCES `company` (`company_id`) ) ENGINE=InnoDB AUTO_INCREMENT=31219 DEFAULT CHARSET=utf8mb4 --約60,000行 CREATE TABLE `customer_setting` ( `customer_setting_id` bigint(20) NOT NULL AUTO_INCREMENT , `customer_id` bigint(20) NOT NULL COMMENT , `setting_code` bigint(20) DEFAULT NULL , `value` bigint(20) NOT NULL , `flg` char(1) CHARACTER SET utf8 NOT NULL , PRIMARY KEY (`customer_setting_id`), KEY `fk_customer_id` (`customer_id`), CONSTRAINT `fk_customer_id` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ) ENGINE=InnoDB AUTO_INCREMENT=62512 DEFAULT CHARSET=utf8mb4
下記のSQLを実行します。
select * from company inner join customer on customer.company_id = company.company_id inner join customer_setting on customer_setting.customer_id = customer.customer_id where customer_setting.flg = 'n' and customer.flg = 'n' and customer.start_date <= '2017-01-01' and customer.end_date >= '2017-04-01'
5.6では下記のような実行計画で動いていました。一番レコード数が少ないcompany表を駆動表として各表をinner joinしています。
+----+-------------+------------------+------+-----------------------+----------------+---------+----------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------+------+-----------------------+----------------+---------+----------------------------+------+-------------+ | 1 | SIMPLE | company | ALL | PRIMARY | NULL | NULL | NULL | 7948 | NULL | | 1 | SIMPLE | customer | ref | PRIMARY,fk_company_id | fk_company_id | 8 | sampl.company.company_id | 1 | Using where | | 1 | SIMPLE | customer_setting | ref | fk_customer_id | fk_customer_id | 8 | sampl.customer.customer_id | 2 | Using where | +----+-------------+------------------+------+-----------------------+----------------+---------+----------------------------+------+-------------+
さて、同じSQLを5.7+condition_fanout_filter=ON(defaultはONです)の状態で実行すると、
+----+-------------+------------------+------------+--------+-----------------------+----------------+---------+----------------------------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------------+------------+--------+-----------------------+----------------+---------+----------------------------+-------+----------+-------------+ | 1 | SIMPLE | customer | NULL | ALL | PRIMARY,fk_company_id | NULL | NULL | NULL | 29913 | 1.11 | Using where | | 1 | SIMPLE | customer_setting | NULL | ref | fk_customer_id | fk_customer_id | 8 | sampl.customer.customer_id | 2 | 10.00 | Using where | | 1 | SIMPLE | company | NULL | eq_ref | PRIMARY | PRIMARY | 8 | sampl.customer.company_id | 1 | 100.00 | NULL | +----+-------------+------------------+------------+--------+-----------------------+----------------+---------+----------------------------+-------+----------+-------------+
こうなりました。WHERE句で絞り込まれている( and customer.flg = 'n' and customer.start_date <= 'YYYY-MM-DD' and customer.end_date >= 'YYYY-MM-DD') customer表が駆動表に入れ替わりましたが、INDEXが無いのでFULL SCAN。5.6の実行計画も駆動表はFULL SCANなので問題ないかと思いきや、元の表よりレコード数が多いので遅くなってしまい、爆死しました。
対策としては
1) INDEXを貼る。駆動表の条件句+結合句に貼ってあげるとおそらく一番妥当な実行計画になります。
sql > create index ix_customer_flg_enddate_customerid on customer(flg,end_date,customer_id);
+----+-------------+------------------+------------+--------+----------------------------------------------------------+------------------------------------+---------+----------------------------+------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------------+------------+--------+----------------------------------------------------------+------------------------------------+---------+----------------------------+------+----------+------------------------------------+ | 1 | SIMPLE | customer | NULL | range | PRIMARY,fk_company_id,ix_customer_flg_enddate_customerid | ix_customer_flg_enddate_customerid | 6 | NULL | 5374 | 33.33 | Using index condition; Using where | | 1 | SIMPLE | customer_setting | NULL | ref | fk_customer_id | fk_customer_id | 8 | sampl.customer.customer_id | 2 | 10.00 | Using where | | 1 | SIMPLE | company | NULL | eq_ref | PRIMARY | PRIMARY | 8 | sampl.customer.company_id | 1 | 100.00 | NULL | +----+-------------+------------------+------------+--------+----------------------------------------------------------+------------------------------------+---------+----------------------------+------+----------+------------------------------------+
2) 駆動表を固定する。とりあえず元の実行計画に戻したい場合はstraight join で結合順序を固定するのも手です。
sql > explain select * from company straight_join customer on customer.company_id = company.company_id inner join customer_setting on customer_setting.customer_id = customer.customer_id where customer_setting.flg = 'n' and customer.flg = 'n' and customer.start_date <= '2017-06-26' and customer.end_date >= '2017-06-26';
+----+-------------+------------------+------------+------+----------------------------------------------------------+----------------+---------+----------------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------------+------------+------+----------------------------------------------------------+----------------+---------+----------------------------+------+----------+-------------+ | 1 | SIMPLE | company | NULL | ALL | PRIMARY | NULL | NULL | NULL | 7948 | 100.00 | NULL | | 1 | SIMPLE | customer | NULL | ref | PRIMARY,fk_company_id,ix_customer_flg_enddate_customerid | fk_company_id | 8 | sampl.company.company_id | 3 | 5.99 | Using where | | 1 | SIMPLE | customer_setting | NULL | ref | fk_customer_id | fk_customer_id | 8 | sampl.customer.customer_id | 2 | 10.00 | Using where | +----+-------------+------------------+------------+------+----------------------------------------------------------+----------------+---------+----------------------------+------+----------+-------------+
3) condition_fanout_filterをOFFにする。そもそもfilterをoffにしてしまえば元に戻るのはという事でoffにしたら元に戻りました(filteredはすべて100になる)。
sql > set optimizer_swith='condition_fanout_filter=off'; sql > select @@optimizer_switch\G *************************** 1. row *************************** @@optimizer_switch: 〜,condition_fanout_filter=off,〜
+----+-------------+------------------+------------+------+-----------------------+----------------+---------+----------------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------------+------------+------+-----------------------+----------------+---------+----------------------------+------+----------+-------------+ | 1 | SIMPLE | company | NULL | ALL | PRIMARY | NULL | NULL | NULL | 7948 | 100.00 | NULL | | 1 | SIMPLE | customer | NULL | ref | PRIMARY,fk_company_id | fk_company_id | 8 | sampl.company.company_id | 1 | 100.00 | Using where | | 1 | SIMPLE | customer_setting | NULL | ref | fk_customer_id | fk_customer_id | 8 | sampl.customer.customer_id | 2 | 100.00 | Using where | +----+-------------+------------------+------------+------+-----------------------+----------------+---------+----------------------------+------+----------+-------------+
トラブル発生時はfilterオプションの存在を知らずINDEX追加で対処しようとしましたが、同じようにパフォーマンスが劣化したSQLが雨後の筍のように発生した為、同僚の人が見つけてくれた(3)condition_fanout_filter=OFFで対処しました。資料にも、パフォーマンス悪かったらoffにしろと書いてありますね。ただINDEXが適切に貼ってあればこのオプションが有効に働くケースの方が多いと思われるので、メジャーバージョンアップ時はSQLのパフォーマンステストは必須という事と、INDEXはちゃんと貼りましょうという教訓を得ました。
