RAKSUL TechBlog

ラクスルグループのエンジニアが技術トピックを発信するブログです

過去の負債に立ち向かったDBパフォーマンス改善の話

Technology

はじめに

こんにちは。ラクスルのECチームでサーバサイドエンジニアをしている市島です。

ECチームではトップページや商品の料金表、購入導線、弊社のCS(カスタマーサポート)の方々が使う管理画面の開発等を担っています。

EC部分はラクスルの玄関口で、会社的に歴史のある部分であり、サービスとしても長く稼働してきました。

そのため長年の負債が最も溜まっている部分でもあります。

初期に作られあまり使われることがないまま現在に至り、現在になって偶然アクセスされ、激重クエリが発行されてアラートが飛んできたりすることがありました。

今回はそういったクエリの原因、及びどう対応したかについて述べたいと思います。

前提

以下で記載されているクエリが発行されるのはユーザが見る画面ではなく、基本的に弊社のCSが見る裏側の管理画面です。

また、記載されているDDL等はかなり簡略化しており、出てくる3つのテーブルは数百万以上のレコード数があります。

その１ indexが貼られていない

CREATE TABLE `order` (
  `id` int(10) unsigned NOT NULL
  `total_price` int(11) NOT NULL COMMENT '注文の合計金額',
  `paid_amount` int(11) NOT NULL COMMENT '注文の支払済金額',
  `status` enum('注文済み','キャンセル','支払済','完了') CHARACTER SET utf8mb4 NOT NULL COMMENT '注文ステータス',
  `payment_type` enum('クレカ','銀行振込','コンビニ支払い',...) CHARACTER SET utf8mb4 NOT NULL COMMENT '支払い方法',
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
)

上記のような注文に関するテーブルがありました。

このテーブルに対して以下のようなクエリを発行していました。

SELECT
    *
FROM
    `order`
WHERE
    `status` = '完了'
AND `payment_type` = '銀行振込'
AND `total_price` > `paid_amount`
;

つまり、既に完了しているのに何かしらの理由できちんと全額支払われていない銀行振込の注文を抽出するクエリです。

ラクスルでは注文レコードは消されることなく増えていくので、当然ながらレコード数が多くなればなるほど走査するレコード数も比例して増え、上記のクエリは重くなっていきます。

このクエリを発行するコードが書かれたのは6年前であり、その当時はレコード数も少なくフルスキャンでも問題なく動いたのでしょうが、今となっては重すぎてまともに動かなくなっていました。

ほぼ発行されることはなかったのですが、偶然何かの拍子にこのクエリを発行するページが踏まれてアラートが飛んできました。

そもそもの問題点としてindexが貼られていないということが挙げられます。

なのでstatus及びpayment_typeにindexを貼れば改善する可能性があります。

ただ、ここで気をつけたいのが、注文レコードという特性上、statusはカーディナリティが低いという点です。

statusが完了である注文レコードが大半を占めており今後も増えていきます。

また、ここで考えたいのは、そもそも注文レコードを全て見る必要があるのかという点です。

実際、業務上見る必要があったのは作成されてから2ヶ月分の注文レコードだけでした。

そこでクエリを以下のようにしたところ、激重クエリがさくっと返ってくるようになりました。

SELECT
    *
FROM
    `order`
WHERE
    `status` = '完了'
AND `payment_type` = '銀行振込'
AND `total_price` > `paid_amount`
AND `created_at` > current_date() - interval 2 month
;

まとめ

  • indexはレコードの特性を見て貼るかどうか考えるべき(何でもindexで解決しようとしない)
  • そもそもユースケース的にどういうレコードが必要なのかを考えるべき

その２ 効かないindex

その１のorderテーブルに加えて、以下のようなテーブルがありました。

CREATE TABLE `order_item` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` int(10) unsigned NOT NULL,
  `status` enum('注文済み','入稿済み','印刷中','発送済み','キャンセル'...) NOT NULL DEFAULT '注文済み',
  `sell_price` int(11) NOT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE `payment_order` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` char(10) NOT NULL COMMENT 'orderテーブルのid',
  `status` enum('登録前','登録済み','完了','拒否','キャンセル') NOT NULL,
  PRIMARY KEY (`id`),
  KEY `order_id_status_idx` (`order_id`,`status`)
)

ラクスルでは支払い方法によっては、外部の決済サービスと連携し、都度その決済状態をモニタリングすることがあります。上記のpayment_orderテーブルはその決済状態を保持しておくためのテーブルだとお考えください。

order(注文)とorder_item(注文された商品)は1:n、order(注文)とpayment_orderは1:1の関係にあります。

ある日、またアラートが飛んできました。

かなり省略していますが以下のようなクエリが発行されていました。

SELECT
    *
FROM
    `order_item`
    INNER JOIN
        `order`
    ON  `order`.`id` = `order_item`.`order_id`
    LEFT OUTER JOIN
        `payment_order`
    ON  `payment_order`.`order_id` = `order`.`id`
WHERE 
    `order`.`payment_type` = '支払い方法A'
AND `payment_order`.`status` IN('登録前', '登録済み')
ORDER BY
    `order_item`.`order_id` DESC,
    `order_item`.`id` DESC
LIMIT 101 OFFSET 0
;

LIMITも指定してindexも効きそうなのにどうして...と思って調べていると、なんとpayment_orderがフルスキャンになっていました。

そこで冒頭に書いたDDLをよく見てみると、payment_orderのorder_idがcharになっているではありませんか！

MySQLのドキュメントを覗いてみると、

For comparisons of a string column with a number, MySQL cannot use an index on the column to look up the value quickly.

とあり、文字列と数字で比較している(内部的にキャストされてしまっている)ためindexが効いていなかったようです。

根本的にはpayment_orderのorder_idをintにするのが正しいのですが、あまり踏まれないクエリのためにサイトをメンテナンスモードに入れてまで型変更をするのはかなり高コストだと感じました。

なので今回の対応として、クエリを分けました。

  • アプリケーション側でAND `payment_order`.`status` IN('登録前', '登録済み')の部分を切り出すことでpayment_order.order_idを先にselectする
  • selectしたpayment_order.order_idをアプリケーション側でintにキャストして、後続のクエリに渡す

レコードの特性上、AND payment_order.status IN('登録前', '登録済み')の部分は130件くらいしか引っかからないため、無理にjoinの条件をActiveRecordで書くより、クエリを分解してしまう方が可読性が高いという理由もありました。

まとめ

  • indexが効いていない場合は何かしらの理由がある
  • CREATE TABLE時にちゃんと型は確認すべき
    • レコードが増えてからの型変更は地獄

余談

パフォーマンス改善活動の一環として、APM(datadog)を使ってスロークエリの改善などをしています。

重いクエリの原因は主にindexの貼り忘れが多かったのですが、きちんとindexを貼ると劇的にパフォーマンスが改善されます。

 

データベースパフォーマンス改善の結果1

データベースパフォーマンス改善の結果2

ここまで劇的に改善されると、非常に気持ちいいものです。

indexはパフォーマンスを上げるための非常に良い手段なので、オプティマイザ等も気にしながら適切に利用していきたいところです。