MySQL5.7にバージョンアップしてスロークエリが頻発したときの対処メモ

経緯

社内のDBを MySQL5.6 -> MySQL5.7 へ移行後、今まで問題なく動いていたクエリが軒並みスロークエリになってしまいました。

テーブルのレコード数は約300万件
下記のような検索を行っていました

SELECT 
    `id` 
FROM 
    `table` 
WHERE 
    id IN ([3,000件ほどの`id`]) 
    AND col1 = '***' 
    AND col2 = '***' 
    AND col3 = '***' ;

調査

検出したスロークエリに対してEXPLAINを付けて、MySQL5.6環境とMySQL5.7環境で実行したところ、インデックスが使われていないことがわかりました

MySQL5.7

type	possible_keys	key
ALL	PRIMARY,col1,col2,col3	NULL

MySQL5.6

type	possible_keys	key
range	PRIMARY,col1,col2,col3	PRIMARY

原因

オプティマイザがおかしいのかなと思って調べてましたが
SHOW WARNINGS;
してみると

Warning 3170    Memory capacity of 8388608 bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query.

というWarningが出てました。

range_optimizer_max_mem_size

range_optimizer_max_mem_sizeというパラメータがMySQL5.7から追加されたそうで、範囲検索の際にこのメモリ上限をオーバーしてしまったためにインデックスが使われなかったようです。

対処

メモリの上限を無くす

SET GLOBAL range_optimizer_max_mem_size = 0;
もしくは

my.cnf

[mysqld]
range_optimizer_max_mem_size=0

を追記して再起動で以前同様に検索ができました。

indexの精査

複数のインデックスが候補に挙がっているときにメモリの上限を越えていたので、不要なインデックスを削除しました。
本来の原因はrange_optimizer_max_mem_sizeですが、これはこれで精査するいい機会となりました。