SQLの改善をCodeIgniterのActiveRecordを使ってやってみた

株式会社オズビジョンの@terra_yuccoです。

業務開発で、SQLの改善を行いつつ、平文で書かれていたSQLをActiveRecordを利用してリファクタリングしました。
参考にしたサイトやStackOverFlowを添えて対応記録を残しておこうと思います。
お困りの皆さんの参考になれば幸いです！

元の処理

SQLっていうかコード

function getGiftCodeStatus()
{
    $gift = $this->load->database('dummy', true);
    $sql = "SELECT type, value,
            SUM(CASE WHEN user_id IS NULL THEN value ELSE 0 END) AS total,
            MIN(CASE WHEN user_id IS NULL THEN gift_limit ELSE NULL END) AS min_gift_limit
            FROM gift_code
            GROUP BY type, value
            ORDER BY type, value";
    return $gift->query($sql)->result();
}

テーブル構造

• 実際のものをある程度ぼかしているので、不整合は目を瞑ってください

mysql> desc gift_code;
+----------------+--------------+------+-----+---------------------+-----------------------------+
| Field          | Type         | Null | Key | Default             | Extra                       |
+----------------+--------------+------+-----+---------------------+-----------------------------+
| id             | bigint(20)   | NO   | PRI | NULL                | auto_increment              |
| gift_code      | text         | NO   |     | NULL                |                             |
| type           | varchar(32)  | NO   | MUL | NULL                |                             |
| value          | int(11)      | NO   |     | NULL                |                             |
| gift_limit     | varchar(16)  | YES  |     | NULL                |                             |
| note_id        | bigint(20)   | YES  | MUL | NULL                |                             |
| multiple_count | int(11)      | NO   |     | NULL                |                             |
| user_id        | int(11)      | YES  | MUL | NULL                |                             |
| sys_ins_date   | timestamp    | NO   |     | 0000-00-00 00:00:00 |                             |
| sys_upd_date   | timestamp    | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
+----------------+--------------+------+-----+---------------------+-----------------------------+
11 rows in set (0.00 sec)

発行SQLを以下のように変更したい

• before

SELECT
  type,
  value,
  SUM(CASE WHEN user_id IS NULL THEN value ELSE 0 END) AS total,
  MIN(CASE WHEN user_id IS NULL THEN gift_limit ELSE NULL END) AS min_gift_limit
FROM
  gift_code
GROUP BY
  type, value
ORDER BY
  type, value

• after

SELECT
  type,
  value,
  SUM(value) AS total,
  MIN(gift_limit) AS min_gift_limit
FROM
  gift_code
WHERE
  user_id is null
GROUP BY
  type, value
ORDER BY
  type, value

explainの結果 (before)

mysql> explain SELECT type, value, SUM(CASE WHEN user_id IS NULL THEN value ELSE 0 END) AS total, MIN(CASE WHEN user_id IS NULL THEN gift_limit ELSE NULL END) AS min_gift_limit FROM gift_code GROUP BY type,value ORDER BY type,value;

+----+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
+----+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+
|  1 | SIMPLE      | gift_code | ALL  | NULL          | NULL | NULL    | NULL | 965718 | Using temporary; Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+
1 row in set (0.00 sec)

explainの結果 (after)

mysql> explain SELECT type, value, SUM(value) AS total, MIN(gift_limit) AS min_gift_limit FROM gift_code WHERE user_id is null GROUP BY type, value ORDER BY type, value;

+----+-------------+-----------+------+---------------+-----------+---------+-------+-------+---------------------------------------------------------------------+
| id | select_type | table     | type | possible_keys | key       | key_len | ref   | rows  | Extra                                                               |
+----+-------------+-----------+------+---------------+-----------+---------+-------+-------+---------------------------------------------------------------------+
|  1 | SIMPLE      | gift_code | ref  | user_id       | user_id   | 5       | const | 48244 | Using index condition; Using where; Using temporary; Using filesort |
+----+-------------+-----------+------+---------------+-----------+---------+-------+-------+---------------------------------------------------------------------+
1 row in set (0.01 sec)

改善後のコード

参考にしたリンク

• ActiveRecordのselectでSUMを取りたい
  • http://stackoverflow.com/questions/22219709/get-the-result-of-sum-and-group-field-from-active-record-in-codeigniter
• ActiveRecordのwhereでIS NULLをやりたい
  • http://kihon-no-ki.com/codeigniter-select-null-or-is-not-null
• ActiveRecordのgroup byを複数フィールドで行う
  • http://stackoverflow.com/questions/34012566/group-by-three-columns-doesnt-work-in-codeigniter
• ActiveRecordのorder byを複数フィールドで行う
  • これだけ良い結果が見つからなかったので、selectの検索結果のアドバイスを取り入れ、カスタムセンテンスにしています。
  • 「Setting FALSE as second parameter, 'select' allows to write a custom sentence.」

/**
 * ギフトコードの残高を種別、券種ごとに取得する
 *
 * @return array
 */
public function getGiftCodeStatus()
{
    $gift = $this->load->database('dummy', true);

    $gift->select('type, value, SUM(value) AS total, MIN(gift_limit) AS min_gift_limit', false);
    $gift->where('user_id', null);
    $gift->group_by(array('type', 'value'));
    $gift->order_by('type, value', false);
    return $gift->get('gift_code')->result();
}

おわりに

上記の改善前にINDEXを追加した対応があり(そもそも上記tableにはPKしかindexありませんでした)、
それだけでも処理の応答速度は2s程度改善されたのですが(9s→7s)、
まだまだ遅いということで行おうとしている対応になります。
どれだけ速くできるかが今から楽しみです！

この機能は弊社の提供サービスの、管理画面側の機能になります。
オズビジョンでは華やかな表サイト側だけでなく、管理画面側を改善していけるエンジニアも全力で募集しています！
ぜひ、気になりましたら、@terra_yucco までお気軽にお声掛けください！

それではまた次の投稿でお会いしましょう。
おしまい。