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

# はじめに

ついにAdventCalenderも２日ですね。
[MySQL Casual Advent Calendar](http://qiita.com/advent-calendar/2014/mysql-casual)24日目の担当の[@gotyoooo](https://twitter.com/gotyoooo)です。

昨日は@kuwa_twさんの[シェルスクリプトだけでMySQLからMongoDBへの移行しちゃう](http://d.hatena.ne.jp/akuwano/20141223/1419342605)でした。
Mongoのメモリ食いまくりな仕様が気になってます・・・。RDBさいこーｗ

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

# 本日の内容

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

# ICP(Index Condition Pushdown)とは？

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

# 起こったことについて

## 事象

* 以下の様な形のDELETE文がサービスにより**同時に**流れた。
 
```sql: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
```
```sql: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が違う

```sql: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 |

```sql: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の情報を見てみる

```sql: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から副参照も速くなったって言うし、それでいいんじゃね？的なノリ
 * 結果解決しているので、これでよかろう。

```sql
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](http://qiita.com/advent-calendar/2014/mysql-casual)の明日のトリがいない！！！