MySQL5.5から5.6にしたらICP(Index Condition Pushdown)で困った

はじめに

ついにAdventCalenderも２日ですね。
MySQL Casual Advent Calendar24日目の担当の@gotyooooです。

昨日は@kuwa_twさんのシェルスクリプトだけでMySQLからMongoDBへの移行しちゃうでした。
Mongoのメモリ食いまくりな仕様が気になってます・・・。RDBさいこーｗ

MySQL界隈ではやっぱりMariaDBはRHEL7とかで標準になったりと、熱いですよね。適当なものでまずは試してみたいです。Galera clusterってそういばどうなったんだろ・・・。

本日の内容

標題の通りです。今更ながら5.5から5.6に移行したデータベースでICPにより問題が起こってしまいました。そのことを書こうと思います。

ICP(Index Condition Pushdown)とは？

• 5.6の新機能
• デフォルトON
• 簡単に機能説明
• マルチカラムインデクッスで、WHERE条件を定義されたキー順に指定しなくても使ってくれる
• マルチカラムインデクッスで、WEERE条件として指定するキーが足りなくても、そのインデックスを利用することが出来る**(今回はこれによる問題)**
• 詳細はnippondanjiさんのこちらの記事がとてもわかり易いです。ちょっと古いですが・・・。

起こったことについて

事象

• 以下の様な形のDELETE文がサービスにより同時に流れた。

sql1

DELETE
FROM
  user_item
WHERE
  user_id = 2
AND 
  item_mst_id = 2
AND
  item_count = 1
AND
  created_at = 1415891719 
AND
  id != 1

sql2

DELETE
FROM
  user_item
WHERE
  user_id = 1
AND
  item_mst_id = 2 
AND 
  item_count = 1
AND 
  created_at = 1415891935 
AND 
  id != 2

• WHERE条件の実行計画を確認するためにSELECT文にしてEXPLAINをかけてみると、使用しているINDEXが違う

sql1

explain select id FROM user_item WHERE user_id = 2 AND item_mst_id = 2 AND item_count = 1 AND created_at = 1415891719 AND id != 1;

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---:|:------------|:----------|:------|:--------------------------------|:--------|:--------+:-----|-----:|:-----------------------------------|
| 1 | SIMPLE | user_item | range | PRIMARY,user_id,user_item_idx02 | user_id | 8 | NULL | 305 | Using index condition; Using where |

sql2

explain select id FROM user_item WHERE user_profile_id = 1 AND item_mst_id = 2 AND item_count = 1 AND created_at = 1415891935 AND id != 2;

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---:|:------------|:----------|:-----|:--------------------------------|:----------------|:--------+:------|-----:|:-----------------------------------|
| 1 | SIMPLE | user_item | ref | PRIMARY,user_id,user_item_idx02 | user_item_idx02 | 4 | const | 520 | Using index condition; Using where |

• INDEXの情報を見てみる

INDEX情報

show index from user_item;

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type
user_item	0	PRIMARY	1	id	A	2248987	NULL	NULL		BTREE
user_item	1	user_id	1	user_id	A	70280	NULL	NULL		BTREE
user_item	1	user_item_idx02	1	user_id	A	44979	NULL	NULL		BTREE
user_item	1	user_item_idx02	2	receive_flg	A	97782	NULL	NULL		BTREE

• それぞれのクエリが更新のクエリのため、別々のINDEXがロックされる
• INDEXロック待ちによるデッドロックが発生

原因

• オプティマイザにより実行計画を立てる際に、sql1ではuser_id indexを利用したが、sql2ではreceive_flgの条件はないもののuser_item_idx02 indexのほうが抽出効率が良いと判断された。
• MySQL5.5ではIPCがないため、どちらのクエリでもuser_id indexが利用されていた。
• そもそもクエリが・・・いや何も言うまい。

対応

• 結局クエリをかえた＼(^o^)／
• SELECT文を副参照に入れて、DELETE文にはidだけ渡すようにした。
• 5.6から副参照も速くなったって言うし、それでいいんじゃね？的なノリ
• 結果解決しているので、これでよかろう。

DELETE
FROM
  user_item
WHERE
  id IN ( 
    SELECT 
      id
    FROM
      user_item
    WHERE
      user_id = 2
    AND 
      item_mst_id = 2
    AND
      item_count = 1
    AND
      created_at = 1415891719 
    AND
      id != 1
  )

まとめ

• IPCを利用すると更新で複雑な条件を入れると、データによっては利用するIndexが変わってしまうので注意が必要

最後に

• MySQL5.7が出ようとしているのに今更感が半端ない。申し訳ない・・・。
• MySQL Casual Advent Calendarの明日のトリがいない！！！