More than 3 years have passed since last update.

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

Last updated at 2020-03-30Posted at 2016-06-01

経緯

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

SET GLOBAL range_optimizer_max_mem_size = 0;
もしくは

my.cnf

[mysqld]
range_optimizer_max_mem_size=0

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

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